Generate test data in batches

This topic describes how to generate test data in batches by using a shell script, a stored procedure, and OceanBase Developer Center (ODC).

Prerequisites

• You have deployed an OceanBase cluster and created an Oracle tenant. For more information about how to deploy an OceanBase cluster, see Overview.
• You have the CREATE TABLE, 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 the required privileges, contact the administrator to obtain the privileges. For more information, see Grant direct privileges.

Use a shell script to generate test data in batches

You can compile a shell script to generate an SQL script that inserts test data in batches. This way, you do not need to manually write complex SQL statements. This method can generate a large amount of test data as required, improving efficiency and reducing manual work.

Procedure

1. Create a test table.
2. Create a shell script.
3. Run the SQL script.
4. View the data.

Step 1: Create a test table

Use a database management tool, such as a command-line tool or a GUI-based tool, to create a database for storing test data and create a test table in the database.

1. Connect to the prepared Oracle tenant.

   Here is an example:

   obclient -hxxx.xxx.xxx.xxx -P2881 -usys@oracle001 -p****** -A
   

2. Create a test table.

   Here is an example:

   Create a test table named test_sql_file_tbl1.

   obclient [SYS]> CREATE TABLE test_sql_file_tbl1 (id NUMBER, name VARCHAR2(20), email VARCHAR2(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 the output redirection symbol (> or >>) to write the generated test data into an SQL script file. During the loop or traversal, the generated data is written as 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:

   Write a shell script named generate_sql.sh.

   vi generate_sql.sh
   

3. Enter the insert mode.

   Press the i or Insert key to enter the insert mode of vi or vim to edit the content of the file.

4. Write the shell script logic.

   In insert mode, write the logic and commands for the shell script. These commands may be shell commands, conditional statements, loops, and functions.

   Here is an example:

   Content of the generate_sql.sh script is as follows:

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

   Note

   • The script generates an SQL file named insert_test_sql_file_tbl1.sql and inserts 100,000 user records into it. You can modify the SQL statement and the number of user records generated as needed.
   • When you insert a large amount of data, pay attention to the resource usage of the relevant server in advance to avoid data insertion failures or performance degradation caused by insufficient resources.

5. Save the file.

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

6. Run the shell script file.

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

   Here is an example:

   Run the created shell script. The following command generates an SQL script file named insert_test_sql_file_tbl1.sql in the current directory. The file contains 100,000 INSERT statements.

   sudo bash generate_sql.sh
   

Step 3: Run the SQL script

Run the following command to import the data in the SQL script file to the database.

1. Open the terminal or CLI, connect to the Oracle tenant of OceanBase Database.

   Here is an example:

   obclient -hxxx.xxx.xxx.xxx -P2881 -usys@oracle001 -p****** -A
   

2. Run the source command to run the SQL script.

   Here is an example:

   obclient [SYS]> source /home/admin/test_data/insert_test_sql_file_tbl1.sql
   

   Note

   To run the SQL script, use the absolute path of the SQL script file.

   The return result is as follows:

   Query OK, 100000 rows affected
   Records: 100000  Duplicates: 0  Warnings: 0
   

Step 4: View the data

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

obclient [SYS]> SELECT count(*) FROM test_sql_file_tbl1;

The return result is as follows:

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

Use a stored procedure to generate test data in batches

You can use a stored procedure to automatically generate test data in batches in an effective manner.

Procedure

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

Step 1: Create a test table

Use a database management tool, such as a command-line tool or a GUI-based tool, to create a database for storing test data and create a test table in the database.

1. Connect to the prepared Oracle tenant.

   Here is an example:

   obclient -hxxx.xxx.xxx.xxx -P2881 -usys@oracle001 -p****** -A
   

2. Create a test table.

   Here is an example:

   Execute the following SQL statement to create a table named test_pro_tbl1 with three fields:

   • id: an integer field.
   • name: a character field that supports a maximum of 50 characters.
   • enrollment_date: a date field used to store date data.

   obclient [SYS]> CREATE TABLE test_pro_tbl1 (
     id NUMBER,
     name VARCHAR2(50),
     enrollment_date DATE);
   

Step 2: Create a stored procedure

1. Specify a custom delimiter.

   Here is an example:

   Use DELIMITER to specify the custom delimiter //.

   obclient [SYS]> DELIMITER //
   

2. Create a stored procedure.

   Here is an example:

   Execute the following SQL statement to create a stored procedure named pro_generate_data with an input parameter n to specify the number of rows to be inserted. Use a loop statement and the INSERT statement to generate and insert data. Here, test_pro_tbl1 is the table into which data will be inserted, id, name, and enrollment_date are the fields to be inserted, i is the loop counter, the CONCAT function is used to generate names, and the DATE_ADD function is used to generate dates.

   obclient [SYS]> CREATE OR REPLACE PROCEDURE pro_generate_data(n IN INT) IS
   i INT := 1;
   BEGIN
   WHILE i <= n LOOP
   INSERT INTO test_pro_tbl1 (id, name, enrollment_date) VALUES (i, 'Name' || i, TO_DATE('2022-01-01', 'YYYY-MM-DD') + i);
   i := i + 1;
   END LOOP;
   END;
   //
   

   For more information about how to create a stored procedure, see Stored procedures.

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

   obclient [SYS]> DELIMITER ;
   

Step 3: Call the stored procedure

Use the CALL statement to call the stored procedure to execute the logic that generates the test data. You can pass a parameter to the stored procedure to specify the amount of data to be generated.

Here is an example:

Execute the following SQL statement to call the stored procedure pro_generate_data and pass the parameter value 100,000, specifying to insert 100,000 data records.

obclient [SYS]> CALL pro_generate_data(100000);

Step 4: View the data

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

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

The return result is as follows:

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

Use ODC to generate test data in batches

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.
• For more information about how to drop a table, see Drop a table.
• For more information about how to delete data, see Delete data.