Create DB table
Create tables ..
Workshop - Create DB table
Create a table from a stream definition. Load rows into that table in the same run.
What you’ll do
Read a delimited file into a stream
Map stream fields to table columns
Generate and run
CREATE TABLESQL from Table OutputInsert rows with commit and batch settings
Prerequisites
A working database connection. See Database Connections.
Basic understanding of tables and SQL data types
Estimated time: 30 minutes
Workshop files
Download the following files.
Keep the filenames unchanged.
Save them in your workshop folder.
Create a new transformation
Use any of these options to open a new transformation tab:
Select File > New > Transformation
Use
Ctrl+N(Windows/Linux) orCmd+N(macOS)
Load Sales Data
Load sales data from a CSV file into STG_SALES_DATA.
Adjust field lengths to avoid truncation.
Follow the steps outlined below:
CSV File Input
Read a delimited file into a stream. Use Text File Input if you need more format options.
Start Pentaho Data Integration.
Start Spoon
Drag the CSV file input step onto the canvas.
Open the CSV file input properties dialog box.
Configure these key fields:
Step name:
csvi-sales_dataFile name:
${Internal.Transformation.Filename.Directory}/sales_data.csvDelimiter:
,(comma)Lazy conversion: clear
Header row present: select
If ${Internal.Transformation.Filename.Directory} is empty, save the transformation first.
CSV File Input infers field lengths from a sample. Increase string lengths before you generate table DDL.
Ensure the following details are configured, as outlined below:
Click on the Get Fields button.
Select OK.
Table Output
Load rows into a database table.
This step uses SQL INSERT.
Drag the Table Output step onto the canvas.
Open the Table Output properties dialog box. Ensure the following details are configured, as outlined below:
Click on the Database fields.
Click on the ‘Get Fields’ button.
Confirm the mappings between Table fields and Stream fields.
Click on the SQL button.
Select Execute.
Select OK to close all dialogs.
Checkpoint: STG_SALES_DATA exists in the database.
RUN and validate
Use a database tool to verify results (DBeaver, Workbench, or your IDE). In production, you usually manage DDL with migrations or scripts.
Click the Run button in the Canvas Toolbar.
Confirm the table exists in your
sampledatadatabase.
Load Orders
Load orders data from a delimited file into STG_ORDERS_MERGED.
Adjust field lengths to avoid truncation.
Follow the steps outlined below:
CSV File Input
Same setup as Workflow 1, but point to your orders file.
Drag the CSV file input step onto the canvas.
Open the CSV file input properties dialog box.
Ensure the following details are configured, as outlined below:
Table Output
Load rows into a database table.
This step uses SQL INSERT.
Drag the Table Output step onto the canvas.
Open the Table Output properties dialog box. Ensure the following details are configured, as outlined below:
Click on the Database fields.
Click on the ‘Get Fields’ button.
Click on the SQL button.
Click Execute.
Select OK to close all dialogs.
Checkpoint: STG_ORDERS_MERGED exists in the database.
RUN and validate
Use a database tool to verify results.
Click the Run button in the Canvas Toolbar.
Confirm the table exists in your
sampledatadatabase.
Last updated
Was this helpful?