Description
The GenericSQLInsertThreadReceiver works very much like the GenericSQLInsertV2Receiver in that both are used to insert generated data into a database table via JDBC-prepared statements. The difference is that GenericSQLInsertV2Receiver only produces batches of data on a single thread and waits for a response from the database. In contrast, GenericSQLInsertThreadReceiver can be configured with multiple DataCollectors, each with many buckets and each bucket containing many values. When a DataCollector's buckets are full, they are prepared simultaneously across multiple threads while the next DataCollector is being loaded.
How Fast is the GenericSQLInsertThreadReceiver?
To compare the speed of GenericSQLInsertThreadReceiver and GenericSQLInsertV2Receiver, refer to 'GenericSQLInsertThreadReceiver vs GenericSQLInsertV2Receiver'.
For visualization purposes, the diagram below shows the Receiver's internal data structure, which has two DataCollectors, each with three DataCollectorBuckets (buckets), each with three DataCollectorDomains (values).
Parameters
The following parameters can be defined for the MySQLInsertV2Receiver. Items with an asterisk (*) are required.
- numberOfCollectors* - Defines the number of Data Collectors the Receiver will use to maintain multiple prepared batches in memory for fast delivery. A Data Collector contains a list of Buckets. Each Bucket contains its own PreparedStatement and manages a list of values that will be prepared in a single batch and sent to the target database table. The default is 3 Data Controllers.
- bucketsPerCollector* - Defines the number of Buckets each Data Collector will manage. Each Bucket contains its own PreparedStatement and manages a list of values that will be prepared in a single batch and sent to the target database table. After the batch is successfully sent, the list is cleared, and the process is repeated. The default is 10 Buckets.
- valuesPerBucket* - Defines the number of generated values each Bucket will hold. For example, a Data Controller containing 10 Buckets, each able to hold 1000 values, has 10,000 rows in memory of 10 prepared batches. The default is 1000 values.
- resourcePath* - Defines the path where the resource file for JDBC connection properties exists.
- resourceSubDir - Defines an optional subdirectory under the resourcePath where the resource file for JDBC connection properties exists.
- resourceName* - Defines the name of the resource that holds database connection information on a user's local machine.
- databaseName* - Defines the name of the database to access.
- tableName* - Defines the name of the database table to insert data into.
- uniqueAttribute - Defines a Domain Attribute that the Receiver uses to identify a unique value that has already been inserted into the table and prevents it from being inserted twice.
- truncate* - Defines whether a table will be truncated or deleted prior to data insertion.
- WARNING! The truncate parameter allows users to truncate or delete all data from the table to be populated prior to data generation and insertion. Use this option very carefully; data truncation or deletion can not be reversed once it has been executed. The options are the following:
- notApplicable (default) - no rows are deleted from a database table.
- truncate - Deletes all rows from a table. Deletion is fast, does not use a where clause, no transaction, and no rollback.
- delete - Deletes one or more rows from a table, with optional where clause or transaction.
- WARNING! The truncate parameter allows users to truncate or delete all data from the table to be populated prior to data generation and insertion. Use this option very carefully; data truncation or deletion can not be reversed once it has been executed. The options are the following:
- statementSeparator* - Defines when a SQL statement has ended.
- rowDelimiter* - Defines the bytes that delimit each row.
- booleanValue* - Defines the format for a Boolean object with the selected mask.
- nullValue* - Defies the constant that represents the Null value for a given database.
- quoteTextData* - Defines the character to use when quoting text data.
- generatedKeysFileName - Defines the file name in which generated keys will be stored.
- escapeCharForIndentifiers - Defines the escape character used to wrap identifiers (e.g., schema name, table name, and column name) containing special characters (e.g., ?, ', ", etc.).
- ignoreDBName - By default, the insert query appends the database name unless ignoreDBName is set to True.
- debug* - When true, the Receiver will report statistics on how long it takes to create prepared statements and the response time after sending a prepared statement. The option should used sparingly as it will dump a lot of information to the console and also slow down the Receiver process. The options are ON and OFF. The default is OFF.
Receiver Attribute Property Keys
The Receiver defines three property keys that can be modified on any of its associated Domain Attributes:
- columnName - Defines the name of the column as it is in the actual database table.
- columnType - Defines the column data type (click here for a detailed help article).
- include - Defines if the column should be included or excluded from the insert statement.
JDBC Configuration File
This Receiver utilizes a configuration file stored locally on the user's computer or a shared test server to obtain the necessary information to connect to a specific database via JDBC. For detailed information, refer to 'What is a JDBC Config File?'
PostgreSQL Insert Errors
There are special considerations when inserting data into a PostgreSQL database table. For detailed information, refer to 'Why Are There Errors When Inserting Data into PostgreSQL?'