Generate test data in batches

This topic describes how to generate test data in batches by using shell scripts, stored procedures, and ODC.

Prerequisites

• You have deployed an OceanBase cluster and created an Oracle tenant. For more information, see Deployment 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 these privileges, contact the administrator to request them. For more information, see Modify user privileges.

Generate test data in batches by using a shell script

You can write a shell script to generate SQL scripts for inserting a large amount of test data. This approach simplifies the process of inserting the test data and avoids the need to manually write numerous SQL statements. You can generate a large amount of test data based on your requirements, improving efficiency and reducing manual effort.

Procedure

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

Step 1: Create a test table

Use a database management tool (such as the CLI or GUI tool) to create a database for storing test data, and create a corresponding test table schema 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 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

Create a shell script file using a text editor. You can use .sh as the file extension. In the shell script, use the output redirection symbols (> or >>) to write the generated test data to an SQL script file. Write the generated data as SQL (INSERT) statements to the SQL script file during the loop or traversal.

1. Open the terminal.

2. Create a shell script file.

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

   **Here is an example: **

   Run the following command to create 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 the vi or vim editor. You can edit the file content in the insert mode.

4. Write the logic of the shell script.

   Write the logic and commands of the shell script in the insert mode. These commands can be shell commands, conditional statements, loop structures, and functions.

   **Here is an example: **

   The content of 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.
   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 generates an SQL file named insert_test_sql_file_tbl1.sql and inserts 100,000 user records into the file. You can modify the SQL statements and the number of user records generated by 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 run the created shell script. This command will generate an 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

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

1. Open the terminal or command line interface and connect to an Oracle tenant of OceanBase Database.

   Here is an example:

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

2. On the database CLI, use the source command to execute the SQL script.

   Here is an example:

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

   Note

   When you execute the SQL script file, use the absolute path to specify the path of the 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

Generate test data by using a stored procedure

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

Procedure

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

Step 1: Create a test table

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

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, which contains three fields:

   • The id field is of the integer type.
   • The name field is of the character type, with a maximum length of 50 characters.
   • The enrollment_date field is of the date type and is 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

Execute the following SQL statement to create a stored procedure named pro_generate_data. The input parameter n specifies the number of data records to be inserted. Use a loop statement and the INSERT statement to generate and insert data. In this example, test_pro_tbl1 is the name of the table to which the data is to be inserted, and id, name, and enrollment_date are the names of the fields to which the data is to be inserted. The i is a loop counter, and the CONCAT function is used to generate names, while the DATE_ADD function is used to generate dates.

1. Specify a custom delimiter.

   Here is an example:

   Use the DELIMITER statement to specify a 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. The input parameter n specifies the number of data records to be inserted. Use a loop statement and the INSERT statement to generate and insert data.

   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 the default semicolon delimiter.

   obclient [SYS]> DELIMITER ;
   

Step 3: Call the stored procedure

Execute the CALL statement to call the stored procedure and run the logic for generating test data. You can pass parameters to the stored procedure to specify the number of data records to generate.

Here is an example:

Execute the following SQL statement to call the pro_generate_data stored procedure and pass the parameter value of 100,000 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 data 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

Generate test data in batches by using ODC

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

ODC allows you to quickly generate data based on the field types in a table when you need a large amount of simulated data to test the performance of or verify the functionality of a database. For more information about the simulated data feature of 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.