Difference between revisions of "CSV parsing and inserting the records in database using Stardust camel features"

Latest revision as of 04:58, 13 August 2015

Overview

In this article we are going to explore how to use Stardust camel component to monitor the CSV files, process it and insert the records in the database.

Use Case:

• From third party party system CSV files are arrived at certain directory or FTP location.
• Start the process on arrival of the CSV file.
• Parse the CSV file and populate the process data.
• Use the certain fields of the populated data and persist them into database(Bulk inserts)

Approach

To implement the use case we will be:

• Define 1 Process, will be started by camel trigger.
• Define camel trigger of type file, will be responsbile to monitor the certain CSV file, and start the process and pass the parsed data to process.
• Define 1 SQL invocation application this application will insert the record into the database table.
• We will use MultiInstance pattern to insert the multiple records in the database.

How to do

As we will be inserting the records in the database table below is the sample table structure which we will be using in this example:

CREATE TABLE `idi_in_data_in` (

 `idi_cntr` INT(11) NOT NULL,
 `idi_fundid_int` INT(11) NOT NULL,
 `idi_clsid_txt` VARCHAR(3) DEFAULT NULL,
 `idi_prd_to_dt` DATETIME NOT NULL,
 `idi_acctno_txt` VARCHAR(30) NOT NULL,
 `idi_srcid_txt` VARCHAR(20) NOT NULL,
 `idi_credit_amt` FLOAT DEFAULT NULL,
 `idi_debit_amt` FLOAT DEFAULT NULL,
 `idi_org_amt` FLOAT DEFAULT NULL,
 `idi_acctdesc_txt` VARCHAR(250) DEFAULT NULL,
 `idi_acct_type_txt` VARCHAR(50) DEFAULT NULL,
 `idi_acct_crdr_txt` VARCHAR(1) DEFAULT NULL,
 `idi_imported_ts` TIMESTAMP NULL DEFAULT NULL,
 `idi_uid_txt` VARCHAR(30) DEFAULT NULL,
 `idi_ts` TIMESTAMP NULL DEFAULT NULL,
 `idi_Processingstatus_int` SMALLINT(6) NOT NULL,
 `idi_Batchid_int` BIGINT(20) DEFAULT NULL

)

The process flow will be something similar to below:

Defining the Camel File Event:

Configuration:

Parameter Mapping:

Converter:

Defining SQL Application: 1. Define the input parameter

2. Insert query

INSERT INTO idi_in_data_in (idi_cntr,idi_srcid_txt,idi_fundid_int,idi_clsid_txt,idi_prd_to_dt,idi_acctdesc_txt,idi_acctno_txt,idi_acct_type_txt,idi_org_amt,idi_uid_txt,idi_Processingstatus_int) VALUES(:#REPORT_ID,'IPP',:#FUND_ACCOUNT_NO,:#MCS_CLASS,:#RUN_DATE,:#GENERAL_LEDGER_ACCOUNT,:#GENERAL_LEDGER_ACCOUNT_NO,:#Z_RULE,:#ENDING_BALANCE,'TEST',0)

In above query :#REPORT_ID ...etc will be replaced from the value present in data passed to this application. And it will form query at runtime.

3. Defining datasource configuration:

NOTE: You can also use configuration variables for password field. And make sure you provide the database driver jar file as per your setup/configurations.

Defining the multiinstance activity:

1. Choose below property for converting activity to multiinstnace activity:

2. Data mapping:

Above will ensure that each record in data sql application will be invoked.

Execution

Deploy the above created process and supply csv file with below contents:

REPORT_ID,REPORT_NAME,FUND_ACCOUNT_NO,ACCOUNT_SHORT_NAME,RUN_DATE,CAPITAL_INCOME_INDICATOR,GENERAL_LEDGER_CATEGORY,GENERAL_LEDGER_ACCOUNT_NO,MCS_CLASS,GENERAL_LEDGER_ACCOUNT,CURRENCY,FROM_DATE,TO_DATE,BEGINNING_BALANCE,TOTAL_DEBITS,TOTAL_CREDITS,ENDING_BALANCE,GL_TEMPLATE,GL_PREFIX,P_RULE,Z_RULE,USER_ACCOUNT,USER_NAME,ACCOUNT_VIEW 111,R092,6262,SUNGARD GLOBAL GROWTH ,2014-01-01, ,,1100-0000-0000-0000,1,SECURITIES AT VALUE ,USD ,12/1/2014,12/31/2014,327832033.3,21258642.99,40610338.79,308480337.5,A010,1100,0,I, ,SECURITIES AT VALUE ,FA 222,R092,6262,SUNGARD GLOBAL GROWTH ,2014-01-01, ,,1101-0000-0000-0000,1,UNREAL - SECURITIES AT VALUE ,USD ,12/1/2014,12/31/2014,-5795218.22,21628462.55,31119407.24,-15286162.91,A110,1101,0,I, ,UNREAL - SECURITIES AT VA,FA 333,R092,6262,SUNGARD GLOBAL GROWTH ,2014-01-01, ,,1110-0000-0001-0000,1,CURRENCY ,USD ,12/1/2014,12/31/2014,469721252.8,418574747.6,393807009.8,494488990.6,A015,1110, ,E, ,CURRENCY ,FA 444,R092,6262,SUNGARD GLOBAL GROWTH ,2014-01-01, ,,1120-0000-0000-0000,1,CURRENCY ALLOWANCE ,USD ,12/1/2014,12/31/2014,0,432769461.5,432769461.5,0,A016,1120, ,E, ,CURRENCY ALLOWANCE ,FA 555,R092,6262,SUNGARD GLOBAL GROWTH ,2014-01-01, ,,1130-0000-0000-0000,1,OTHER ASSETS ,USD ,12/1/2014,12/31/2014,0,0,0,0,A017,1130,0,I, ,OTHER ASSETS ,FA

Process will be started and csv data will be parsed into process data and then same will be passed to SQL application which will insert all the records into the database table:

Artifacts

All the artifacts used/discussed in this article could be found here File:CSVDBInserts.zip