Dynamic Database Table creation based on Source data
In this example, we will walk-through a data integration example of pulling data files from source location such as SFTP, LAN, HTTPS, JMS, API, Email and then parsing the data and loading the data into a table. Table is created on-the-fly based on the flat structure of the source data.
For example, if the source data is a CSV file and has header columns then the resulting table is created with the field names derived from the source file header and all the data is loaded into the table. When subsequent files arrive that are of the same type, the process flow appends the data into the existing table created earlier with the initialfile.
Using this example, there is no need to pre-configure a table or create the related Database Target anddynamic Database Schema activities in Adeptia Solution, nor is it required tomap the source datato the tableschema via a data mapper. All of these steps are removed by using thisdynamic database table approach.
Let’s discuss how to use the dynamic database tableapproach. The activities used for this flow are:
- Custom Plugin (see attached): The “DynamicTable” plugin takes the Database Info activity name and creates the table under the specific database selected in the DB Info. In its Activity Properties make the Consume Stream to “true” and Generate Stream to “false”. Modify the custom plugin and enter the name of the DB Info Activity forString databaseInfoName = “operations”variable. As an example, here I’m using “operations” as the name of my DB Info activity.
- Process flow variable: The variable “tablename” is set in the process flow. You can provide any table name and when the process runs, it will name the table based on the value provided in this variable.
- Source Activity: This is the location from where the source files are being pulled. The flow can be tied to a trigger and this can be a placeholder activity. Refer to Support Forum on how to use triggers.
- Source Schema: This activity is used for parsing the file data and then passing the contents of the file to the custom plugin.
Refer to the video on how to design thedynamic databaseprocess flow.
Process Flow design:
Click on the Process Flow Variable tab in the Process Designer and add a variable “tablename” with a value as shown below. Table name value can be any name you want to give to the table that iscreated by the flow.
Refer to the attached DynamicTable script needed for Custom Plugin.