This topic describes how to generate test data in batches by using shell scripts, stored procedures, and OceanBase Developer Center (ODC).
Prerequisites
- You have deployed OceanBase AI Database and created a user tenant. For more information about deploying OceanBase AI Database, see Choose an appropriate deployment plan.
- You have the
CREATE,INSERT, andSELECTprivileges. For more information about checking current user privileges, see View user privileges. If you do not have these privileges, contact your administrator to grant them. For more information about user authorization, see Grant privileges directly.
Generate test data in batches using shell scripts
Writing Shell scripts to generate SQL scripts in batches can significantly simplify the process of inserting large amounts of test data, avoiding the need to manually write tedious SQL statements. This method allows you to generate large volumes of test data as needed, improving efficiency and reducing manual workload.
Procedure
- Create a test database and a test table.
- Create a Shell script.
- Run the SQL script.
- View the data.
Step 1: Create a test database and a test table
Use a database management tool (such as the command line or a graphical tool) to create a database for storing test data, and create the corresponding test table structure within the database.
Connect to the prepared user tenant.
Example:
obclient -hxxx.xxx.xxx.xxx -P2881 -uroot@mysql001 -p****** -ACreate a test database.
Example:
Execute the following SQL statement to create the test database
test_sql_file_db.CREATE DATABASE test_sql_file_db;For more information about how to create a database, see Create a database.
Create a test table.
Example:
Execute the following SQL statement to create the test table
test_sql_file_db.test_sql_file_tbl1.CREATE TABLE test_sql_file_db.test_sql_file_tbl1 (id INT, name VARCHAR(50), email VARCHAR(50));For more information about how to create a table, see Create a table.
Step 2: Create a Shell script
Use a text editor to create a Shell script file, with the .sh extension. In the Shell script, use output redirection symbols (> or >>) to write the generated test data to an SQL script file. During loops or iterations, write the generated data into the SQL script file as INSERT statements.
Open a terminal.
Create a shell script file.
Use the
viorvimeditor to create a new shell script file.Example:
Run the following command to create a shell script named
generate_sql.sh.vi generate_sql.shEnter edit mode.
Press the i key or the Insert key to enter insert mode in the
viorvimeditor, where you can edit the file content.Write the shell script logic.
In edit mode, write the logic and commands for the shell script. These commands can include shell commands, conditional statements, loop structures, functions, and more.
Example:
The content of the
generate_sql.shscript is as follows:#!/bin/bash # Define SQL File Name SQL_FILE="insert_test_sql_file_tbl1.sql" # Create SQL File touch $SQL_FILE # Define SQL statements INSERT_SQL="INSERT INTO test_sql_file_tbl1 (id, name, email) VALUES " # Loop to generate 100,000 user records for ((i=1; i<=100000; i++)) do user_id=$i user_name="user_$i" user_email="user_$i@example.com" values="($user_id, '$user_name', '$user_email')" if (($i == 100000)) then INSERT_SQL="$INSERT_SQL$values;" else INSERT_SQL="$INSERT_SQL$values, " fi done # Write SQL statements to an SQL file echo $INSERT_SQL >> $SQL_FILENote
- This script generates an SQL file named
insert_test_sql_file_tbl1.sqland inserts 100,000 user records into it. You can modify the SQL statements and the number of user records generated by the loop as needed. - When inserting large amounts of data, monitor the resource usage of the relevant servers in advance to avoid data insertion failures or performance degradation due to resource constraints.
- This script generates an SQL file named
Save the file.
Press the Esc key to exit insert mode, then enter the
:wqcommand to save the file and exit theviorvimeditor.Run the shell script file.
Execute the created shell script in the terminal to generate an SQL script.
Example:
Run the following command to execute the created shell script. This command generates an SQL script file named
insert_test_sql_file_tbl1.sqlin the current directory, containing 100,000INSERTstatements.sudo bash generate_sql.sh
Step 3: Run the SQL script
You can use the command-line interface to run the following command to import the data from the SQL script file.
Note
For more information about executing SQL scripts, see Import data from an SQL file to OceanBase AI Database.
obclient -h$host -u$user_name -P$port -p$password -D$database_name < $sql_file
The parameters are described as follows:
$host: The IP address for connecting to OceanBase AI Database. For connections through OceanBase Database Proxy (ODP), use an ODP address; for direct connections, use the IP address of an OBServer node.$port: The port for connecting to OceanBase AI Database. For ODP connections, the default port is2883, which can be customized during ODP deployment. For direct connections, the default port is2881, which can be customized during OceanBase AI Database deployment.$database_name: The name of the database to access.Notice
The user connecting to the tenant must have the
CREATE,INSERT, andSELECTprivileges on this database. For more information about user privileges, see Privilege types.$user_name: The connection account for the tenant. For ODP connections, the common format isusername@tenant_name#cluster_nameorcluster_name:tenant_name:username. For direct connections, the format isusername@tenant_name.$password: The account password.$sql_file: The name of the SQL script file.Note
When executing an SQL script file, use an absolute path to specify its location.
Example:
Run the following command to connect to the specified OceanBase AI Database server and import all INSERT statements from the SQL script file into the database named test_sql_file_db, inserting 100,000 rows of data into the insert_test_sql_file_tbl1 table.
obclient -hxxx.xxx.xxx.xxx -uroot@mysql001 -P2881 -p****** -Dtest_sql_file_db < /home/admin/test_data/insert_test_sql_file_tbl1.sql
Step 4: View data
Run the following SQL statement to view the number of data rows in test_sql_file_db.test_sql_file_tbl1.
obclient [(none)]> SELECT count(*) FROM test_sql_file_db.test_sql_file_tbl1;
The query result is as follows:
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set
Generate test data in batches using a stored procedure
Using a stored procedure to generate test data in batches is an effective method. You can write a stored procedure to automatically generate large volumes of test data.
Procedure
- Create a test database and a test table.
- Create a stored procedure.
- Call the stored procedure.
- View the data.
Step 1: Create a test database and a test table
Use a database management tool (such as the command line or a graphical tool) to create a database for storing test data, and create the corresponding test table structure within the database.
Connect to the prepared user tenant.
Example:
obclient -hxxx.xxx.xxx.xxx -P2881 -uroot@mysql001 -p****** -ACreate a test database.
Example:
Execute the following SQL statement to create the test database
test_db.obclient [(none)]> CREATE DATABASE test_db;Execute the following SQL statement to switch to the
test_dbdatabase.obclient [(none)]> use test_db;The result is as follows:
Database changed obclient [test_db]>Create a test table.
Example:
Execute the following SQL statement to create the
test_pro_tbl1table, which contains four fields:idis an integer field defined as the primary key and auto-incrementing.create_timeis a datetime field representing the creation time of the row. It uses DEFAULT CURRENT_TIMESTAMP to set the default value to the current time.nameis a character field with a maximum length of 50 characters.enrollment_dateis a date field used to store date data.
obclient [test_db]> CREATE TABLE test_pro_tbl1 ( id INT NOT NULL AUTO_INCREMENT, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, name VARCHAR(50), enrollment_date DATE, PRIMARY KEY(id));
Step 2: Create a stored procedure
Specify a custom delimiter.
Example:
Use the
DELIMITERstatement to specify a custom delimiter//.DELIMITER //Create a stored procedure.
Example:
Execute the following SQL statement to create a stored procedure named
pro_generate_data. The input parameternspecifies the number of data records to be inserted. Use loop statements andINSERTstatements to generate and insert data. Here,test_pro_tbl1is the table name for inserting data,nameandenrollment_dateare the field names for inserting data,iis the loop counter, theCONCATfunction is used to generate names, and theDATE_ADDfunction is used to generate dates.CREATE PROCEDURE pro_generate_data(IN n INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i <= n DO INSERT INTO test_pro_tbl1 (name, enrollment_date) VALUES (CONCAT('Name', i), DATE_ADD('2022-01-01', INTERVAL i DAY)); SET i = i + 1; END WHILE; END; //For more information about creating stored procedures, see Stored procedure.
Restore the default semicolon delimiter.
DELIMITER ;
Step 3: Call the stored procedure
Use the CALL statement to call the stored procedure to execute the logic for generating test data. You can pass parameters to the stored procedure to specify the number of data records to generate.
Example:
Execute the following SQL statement to call the stored procedure pro_generate_data and pass a parameter value of 100,000, indicating that 100,000 data records need to be inserted.
obclient [test_db]> CALL pro_generate_data(100000);
Note
You can increase or decrease the size of the input parameters to control the number of test data records. When adjusting the parameter sizes, consider database performance and storage space limitations to avoid generating too much data, which could cause the database to crash or run out of storage space.
Step 4: View the data
Execute the following SQL statement to view the number of data rows in test_pro_tbl1.
obclient [test_db]> SELECT count(*) FROM test_pro_tbl1;
The query result is as follows:
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set
Generate test data in batches using ODC
OceanBase Developer Center (ODC) is an enterprise-level database development platform tailored for OceanBase AI Database.
ODC provides a data mocking feature that allows you to quickly generate data based on the field types in a table. This is useful for scenarios that require large amounts of simulated data, such as testing database performance or verifying features.
References
- For more information about connecting to a database, see Overview of connection methods.
- For more information about dropping a table, see Drop a table.
- For more information about deleting data, see Delete data.