Description

This solution article shows how to generate the JSON files and insert them into a database table using the BlobTextGen. To do so, you will need to: 

  1. Generate JSON files into a directory, or you can use existing JSON files in a directory. Data can be generated in one of the following ways: 
  2. Link generators to reference the file names in the BlobTextGen.
  3. Use the BlobTextGen to read it as a BLOB or CBLOB in an Attribute (column).
  4. Then, use the GenericSQLInsertV2Receiver to insert it into the database.


Note: A MySQL Database Table (version 5.7) is used in the example shown in this article.


Generated Files for This Example

Each file is a flat file containing an id and the user's first name, last name, and middle initial. Sample contents of the file: 



Data Loaded into the Database Table

The file type shows BLOB.


The data appears as shown below for the user_data column:
 


Initial Project Setup

A UserDetails Domain has been created with an id and a userData Attribute.


userData Attribute

The required linked Generators for this example will be added to this Attribute.


Step 1 - Setup Required Generators for the Target JSON File Names

This step will depend on how your file names are set up. The linked Generators will be added to the userData Attribute. Examples are provided below: 


Example 1 - File Name follows a Pattern

For this example, the file names are identical except for an increasing number value: test1.json. This can be accomplished with a ConstantGen and a RangeGen.



RangeGen Generator


ConcatGen Generator


Example 2 - File Name Varies

When the file name does not follow a pattern, you can reference a ListFileNameGen, which loads a list of file names present within a specified directory. 



Step 2 - Add BlobTextGen and Reference the File Name Generator 

The parameter configuration is shown below. The fileName parameter references the generator for the file name. This allows the file name to change and match the one in the directory for each pass.  


Example 1 - File Name with Pattern

The parameter references the ConcatGen Generator that forms the patterned file name.


Example 2 - File Name Varies

The parameter references the ListFileNameGen to load the list of files.


Example 3 - No File Name

Alternatively, you can leave the fileName in the BlobTextGen empty. You just need to make sure that the path is correct, and the subdirectory is defined (when applicable). A warning will appear, but you can still download and run the Scenario to insert the JSON payload.


Note: A warning will appear when the Scenario is run if the maxSize is greater than the available heap size, which can result in a low heap memory exception. 



For this example, the Attribute size has been set to match the database table column size in the database table field.



Step 3 - Adjust the loopCount Accordingly

The Domain loopCount will need to match the number of files. It will be 5 for this example. 



Step 4 - Assign GenericSQLInsertV2Receiver to the Domain

The GenericSQLInsertV2Receiver inserts the JSON File payload into the database table location. For this example, the following parameters have been altered: 

  • resourceSubDir - The JDBC Config File is stored in a 'config' subdirectory.
  • resourceName - The JDBC Config File name is 'sample_db.properties'.
  • databaseName - sampledb
  • tableName - user_details



Step 5 - Configure Attribute Property Keys

For the Attribute, you will need to ensure the columnName matches the column name in the database table. The appropriate dataType must also be selected. 


When viewing the Receiver, select the Attributes Property Keys tab and then select the Attribute.


For this example, 'blob' will be selected. To learn more about available data types, please see this article: What are the available Column Types for SQL Insert Receivers?



Step 6 - Create, Download, and Run the Scenario 

Create a Scenario if this has not been done already. Then download the Scenario (not required when using G-Repository). Enter the appropriate command at the command line and run it to insert the data into the database table. 


Remember this command varies depending on the features you are using. For this example, we are using the basic command with no other features. 



Here, you can see how the JSON files are inserted into the Attribute.




Example 2 - Sample Insert for Nested JSON Files

As with Example 1 above, 5 nested files containing data similar to what is shown below will be inserted into the database table. The setup is the same as in Example 1, with a small variation for a different file name.