Generate CSV Files from JDBC Data via ETL

[farhana99]

Overview

This document outlines the guide to create a chain with the purpose of reading a hardcoded array of JSON objects and write the data into a CSV file that uses a pipe (|) as the delimiter and includes a header row.

---

Step 1: Create a New ETL Container

Before creating the chain, you will first need to create the main ETL container that will hold it.

1. Select the option to add a new ETL.
2. The Create ETL dialog box will appear.
   
   
3. Enter a descriptive Name for your ETL process (e.g., GenerateCSV_JDBC).
4. Leave the Copy From field set to Blank ETL.
5. Click OK.

---

Step 2: Add a New Chain

Now that you have an ETL container, you must add a chain to it. The chain will contain the actual processing steps.

1. Inside your newly created ETL, select the option to add a new chain.
2. The Add Chain dialog box will appear.
   
   
3. Enter a Name for your chain (e.g., GenerateCSVfile).
4. Leave the Copy From field set to Blank Chain.
5. Click OK.

---

Step 3: Add and Configure the JDBC Step (Your Data Source)

Next, you need a step to provide the data. We will use the JSON Record step to input a fixed set of data.

1. Add a new step to your chain.
2. In the Category list on the left, select JDBC.
3. From the Action list that appears in the middle, choose JDBC Definition.
   
   
4. Click the Add button to add the step to your chain.
5. In the configuration area for this step, configure the following:
   • Database: Select the appropriate pool (e.g., mssql)
   • SQL Query: Enter the query to retrieve your data
     
     
6. Save the step.

---

Step 4: Add and Configure the JDBC Reader Step

This step executes the query and passes the results downstream.

1. Add a second step to your chain.
2. In the Category list, select JDBC.
3. From the Action list, choose JDBC Reader.
   
   
4. Click the Add button to add the step to your chain.
5. Ensure the step is linked to the previous JDBC Definition step.

---

Step 5: (Optional) Add a Restructure Step

If your column names contain lowercase letters or need renaming, add a Restructure step to ensure compatibility with the CSV File Writer.

1. Add a new step after the JDBC Reader.
2. In the Category list, select Structure.
3. From the Action list, choose Restructure.
   
   
4. Click the Add button to add the step to your chain.
5. In the New Structure configuration panel, you need to define the mapping from new column name to the source database name (prefixed with a $), e.g:

   {
     "Model ID" : "$ModelID",
     "Name" : "$ModelName",
     "Manufacturer ID" : "$ManufacturerID",
     "Year" : "$Year",
     "Type Of Body" : "$BodyType",
     "Engine" : "$EngineType"
   }
   

   
6. Save the step.

---

Step 6: Add and Configure the CSV File Writer Step

This step writes the final output file.

1. Add a new step to your chain.
2. In the Category list, select CSV.
3. From the Action list, choose CSV File Writer.
   
   
4. Click the Add button to add the step to your chain.
5. In the configuration area for this step, configure the following:
   • Columns: List the column names exactly as defined in the Restructure step, e.g:

   Model ID
   Name
   Manufacturer ID
   Year
   Type Of Body
   Engine
   

   • Filename: Specify the path and name for your output file, for example:

   ./data/out/JDBC_CsvTest.csv
   

   
6. Save the step.

---

Step 7: Verify the Output

Run the chain and verify the output file in the specified directory (./data/out/).

---

Attachments

• Sample Chainset
• Output File

GenerateCSV_JDBC.chainset.zip (1.2 KB)