Generate test data in batches

This topic describes how to generate test data in batches by using a Shell script, a stored procedure, and an ODC.

Prerequisites

• You have deployed seekdb. For more information about how to deploy seekdb, see Overview.
• You have the CREATE, INSERT, and SELECT privileges. For more information about how to view the current user privileges, see View user privileges. If you do not have the required privileges, contact the administrator to grant them. For more information about how to directly grant privileges, see Directly grant privileges.

Use a shell script to generate test data in batches

You can use a shell script to generate SQL scripts in batches to simplify the process of inserting large amounts of test data and avoid the need to manually write tedious SQL statements. This method allows you to generate a large volume of test data as needed, improving efficiency and reducing the workload of manual operations.

Procedure

1. Create a test database and test table.
2. Create a Shell script.
3. Run the SQL script.
4. View the data.

Step 1: Create a test database and test table

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

1. Connect to seekdb.

   Example:

   mysql -hxxx.xxx.xxx.xxx -P2881 -uroot -p****** -A

2. Create a test database.

   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.

   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 use .sh as the file 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 iteration, write the generated data as SQL (INSERT) statements into 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.

   Example:

   Execute the following command to write a Shell script named generate_sql.sh.

   vi generate_sql.sh

3. Enter the editing mode.

   Press i or Insert to enter the insertion mode of the vi or vim editor, where you can edit the file content.

4. Write the logic of the Shell script.

   In the editing mode, write the logic and commands of the Shell script. These commands can be Shell commands, conditional statements, loop structures, and functions.

   Example:

   The content of the generate_sql.sh script is as follows:

   #!/bin/bash
   
   # Define the SQL file name
   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 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

   info

   • This 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 in the loop as needed.

   • Before inserting a large amount of data, monitor the resource usage of the relevant server to avoid data insertion failure or performance degradation due to resource constraints.

5. Save the file.

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

6. Run the Shell script.

   In the terminal, execute the created Shell script to generate an SQL script.

   Example:

   Execute the following command to run the created Shell script. The command generates 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

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

mysql -h$host -u$user_name -P$port -p$password -D$database_name < $sql_file

Parameter description:

• $host: the IP address for connecting to seekdb.

• $port: the port for connecting to seekdb. The default value is 2881, which can be customized during deployment.

• $database_name: the name of the database to be accessed.

  tip

  The user for connecting to seekdb must have the CREATE, INSERT, and SELECT permissions on the database. For more information about user permissions, see Permission types.

• $user_name: the username. Format: username.

• $password: the password of the account.

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

  info

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

Example:

Execute the following command to connect to the specified seekdb server and import all INSERT statements in the SQL script file into the test_sql_file_db database to insert 100,000 records into the insert_test_sql_file_tbl1 table.

mysql -h127.0.0.1 -uroot -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 data rows in the test_sql_file_db.test_sql_file_tbl1 table.

SELECT count(*) FROM test_sql_file_db.test_sql_file_tbl1;

The returned result is as follows:

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

Use a stored procedure to generate test data in batches

Using a stored procedure to generate test data in batches 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 test table.
2. Create a stored procedure.
3. Call the stored procedure.
4. View the data.

Step 1: Create a test database and test table

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

1. Connect to seekdb.

   Here is an example:

   mysql -hxxx.xxx.xxx.xxx -P2881 -uroot -p****** -A

2. Create a test database.

   Here is an example:

   Execute the following SQL statement to create the test_db database.

   CREATE DATABASE test_db;

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

   use test_db;

   The return result is as follows:

   Database changed

4. Create a test table.

   Here is an example:

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

   • The id field is an integer field that is defined as the primary key and is automatically incremented.
   • The create_time field is a datetime field that indicates the creation time of the row of data. The default value is set to the current time by using the DEFAULT CURRENT_TIMESTAMP option.
   • The name field is a character field with a maximum length of 50 characters.
   • The enrollment_date field is a date field for storing date data.

   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 command to specify a custom delimiter //.

   DELIMITER //

2. Create a stored procedure.

   Here is an example:

   Execute the following SQL statement to create the pro_generate_data stored procedure. The input parameter n specifies the number of data rows to be inserted. The stored procedure uses a loop statement and an INSERT statement to generate and insert data. In this example, the test_pro_tbl1 table is used to store the data. The name and enrollment_date fields are used to store the data. The i variable is the loop counter. The CONCAT function is used to generate the name, and the DATE_ADD function is used to generate the date.

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

   DELIMITER ;

Step 3: Call the stored procedure

Use the CALL statement to call the stored procedure and execute the logic for generating test data. You can pass parameters to the stored procedure to specify the number of data rows to be generated.

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, indicating that 100,000 data rows are to be inserted.

CALL pro_generate_data(100000);

info

You can increase or decrease the size of the input parameter to control the number of test data rows. When you change the parameter size, consider the database performance and storage space limitations to avoid generating excessive data that causes the database to crash or the storage space to be insufficient.

Step 4: View the data

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

SELECT count(*) FROM test_pro_tbl1;

The return result is as follows:

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

References

• For more information about how to connect to a database, see Connection 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.