Generate test data in batches

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 an OceanBase cluster and created a MySQL tenant. For more information about how to deploy an OceanBase cluster, see Deployment overview.
• You have the CREATE, INSERT, and SELECT privileges. For more information about how to view the privileges of the current user, see View user privileges. If you do not have these privileges, contact the administrator to obtain them. For more information about how to grant privileges to a user, see Grant direct privileges.

Generate test data in batches by using shell scripts

You can write a shell script to generate SQL scripts in batches, which effectively simplifies the process of inserting a large amount of test data and avoids the need to manually write complex SQL statements. This method allows you to generate a large amount of test data as needed, improving efficiency and reducing the workload of manual operations.

Procedure

1. Create a test database and test tables.
2. Create a shell script.
3. Run an SQL script.
4. View the data.

Step 1: Create a test database and a test table

Use a database management tool (such as a CLI tool or a GUI tool) to create a database for storing test data and create corresponding test table schemas in the database.

1. Connect to the prepared MySQL tenant.

   Here is an example:

   obclient -hxxx.xxx.xxx.xxx -P2881 -uroot@mysql001 -p****** -A
   

2. Create a test database.

   Here is an example:

   Execute the following SQL statement to create a test database named test_sql_file_db.

   CREATE DATABASE test_sql_file_db;
   

   For more information about how to create a database, see Create a database.

3. Create a test table.

   Here is an example:

   Execute the following SQL statement to create a test table named 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. You can add the .sh extension to the file name. In the shell script, use the output redirection symbols (> or >>) to write the generated test data to an SQL script file. During a loop or traversal, write the data in the form of SQL statements (INSERT) to the SQL script file.

1. Open the terminal.

2. Create a shell script file.

   Use the vi or vim editor to create a new shell script file.

   Here is an example:

   Run the following command to write a shell script named generate_sql.sh.

   vi generate_sql.sh
   

3. Enter the edit mode.

   Press the i or Insert key to enter insert mode in the vi or vim editor. You can edit the file content in the insert mode.

4. Write the logic of the shell script.

   In edit mode, write the logic and commands for the shell script. These commands can include shell commands, conditional statements, loop structures, and functions.

   Here is an example:

   The generate_sql.sh script is as follows:

   #!/bin/bash
   
   # Define the name of the SQL file.
   SQL_FILE="insert_test_sql_file_tbl1.sql"
   
   # Create an SQL file.
   touch $SQL_FILE
   
   # Define an SQL statement.
   INSERT_SQL="INSERT INTO test_sql_file_tbl1 (id, name, email) VALUES "
   
   # Generate 100,000 user records in a loop.
   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 the SQL statement to the SQL file.
   echo $INSERT_SQL >> $SQL_FILE
   

   Note

   • The script will generate an SQL file named insert_test_sql_file_tbl1.sql and insert 100,000 user records into the file. You can modify the SQL statements and the number of user records generated in a loop as needed.
   • Before you insert a large amount of data, check the resource usage of the relevant servers to avoid data insertion failures or performance degradation due to high resource usage.

5. Save the file.

   Press the Esc key to exit the insert mode and enter the :wq command to save the file and exit the vi or vim editor.

6. Run the shell script file.

   Execute the created shell script to generate an SQL script in the terminal.

   Here is an example:

   Run the following command to execute the created shell script. This command will generate a SQL script named insert_test_sql_file_tbl1.sql in the current directory, which contains 100,000 INSERT statements.

   sudo bash generate_sql.sh
   

Step 3: Run the SQL script

You can use the CLI to import the data from the SQL script file.

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 Database. For connection through OceanBase Database Proxy (ODP), use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.

• $port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

• $database_name: the name of the database to access.

  Notice

  The user used to connect to the tenant must have the CREATE, INSERT, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name: the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.

• $password: the password of the account.

• $sql_file: the name of the SQL script file.

  Note

  When you execute an SQL script file, specify the absolute path of the SQL script file.

Here is an example:

Run the following command to connect to a specified OceanBase Database server and import all INSERT statements in the SQL script file to a database named test_sql_file_db one by one, so as to insert 100,000 records 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 the data

Execute the following SQL statement to view the number of rows in the test_sql_file_db.test_sql_file_tbl1 table.

obclient [(none)]> SELECT count(*) FROM test_sql_file_db.test_sql_file_tbl1;

The return result is as follows:

+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set

Generate test data in batches by using a stored procedure

Generating test data in batches by using a stored procedure is an effective method. You can write a stored procedure to automatically generate a large amount of test data.

Procedure

1. Create a test database and a test table.
2. Create a stored procedure.
3. Call the stored procedure.
4. View the data.

Step 1: Create a test database and test tables

You can use a database management tool such as the CLI tool or a GUI-based tool to create a database for storing test data and create corresponding test table schemas in the database.

1. Connect to the prepared MySQL tenant.

   Here is an example:

   obclient -hxxx.xxx.xxx.xxx -P2881 -uroot@mysql001 -p****** -A
   

2. Create a test database.

   Here is an example:

   Execute the following SQL statement to create a test database named test_db.

   obclient [(none)]> CREATE DATABASE test_db;
   

3. Execute the following SQL statement to switch to the test_db database.

   obclient [(none)]> use test_db;
   

   The return result is as follows:

   Database changed
   obclient [test_db]>
   

4. Create a test table.

   Here is an example:

   Execute the following SQL statement to create a table named test_pro_tbl1, which contains four fields:

   • id is an integer field defined as the primary key and uses auto-increment for numbering.
   • create_time is a datetime field that represents the creation time of the row. It is set to the current time by default using DEFAULT CURRENT_TIMESTAMP.
   • name is a character field with a maximum length of 50 characters.
   • enrollment_date is a date field that is 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

1. Specify a custom delimiter.

   Here is an example:

   Use the DELIMITER statement to specify // as the custom delimiter.

   DELIMITER //
   

2. Create a stored procedure.

   Here is an example:

   Execute the following SQL statement to create a stored procedure named pro_generate_data. The input parameter n specifies the number of records to insert. A loop and INSERT statements are used to generate and insert data. In this example, test_pro_tbl1 is the target table, while name and enrollment_date are the fields where the data is inserted. The loop counter i is used to generate values, with the CONCAT function creating names and the DATE_ADD function generating 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 how to create a stored procedure, see Stored procedures.

3. Restore to the default delimiter, which is semicolon (;).

   DELIMITER ;
   

Step 3: Call the stored procedure

Use the CALL statement to call the stored procedure to execute the test data generation logic. You can pass parameters to the stored procedure to specify the number of data records to be generated.

Here is an example:

Execute the following SQL statement to call the pro_generate_data stored procedure and set the value of the input parameter to 100,000, indicating that 100,000 data records need to be inserted.

obclient [test_db]> CALL pro_generate_data(100000);

Step 4: View the data

Execute the following SQL statement to view the number of data rows in the test_pro_tbl1 table.

obclient [test_db]> SELECT count(*) FROM test_pro_tbl1;

The return result is as follows:

+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set

Generate test data in batches by using ODC

ODC is an enterprise-level database development platform tailored for OceanBase Database. For more information about ODC, see What is ODC?

ODC provides the data mocking feature that can generate data based on field types in a table. This can meet your requirement for generating a large amount of data during database performance tests or feature verification. For more information about the data mocking feature provided by ODC, see Data mocking.

References

• For more information about how to connect to a database, see Overview of connection methods.
• For more information about how to drop a table, see Drop a table.
• For more information about how to delete data, see Delete data.