Create tables ..
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 TABLE SQL from Table Output
CREATE TABLE
Insert 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.
Use any of these options to open a new transformation tab:
Select File > New > Transformation
Use Ctrl+N (Windows/Linux) or Cmd+N (macOS)
Ctrl+N
Cmd+N
Load sales data from a CSV file into STG_SALES_DATA.
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_data
csvi-sales_data
File name: ${Internal.Transformation.Filename.Directory}/sales_data.csv
${Internal.Transformation.Filename.Directory}/sales_data.csv
Delimiter: , (comma)
,
Lazy conversion: clear
Header row present: select
If ${Internal.Transformation.Filename.Directory} is empty, save the transformation first.
${Internal.Transformation.Filename.Directory}
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.
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 sampledata database.
sampledata
Load orders data from a delimited file into STG_ORDERS_MERGED.
STG_ORDERS_MERGED
Same setup as Workflow 1, but point to your orders file.
Click Execute.
Checkpoint: STG_ORDERS_MERGED exists in the database.
Use a database tool to verify results.
“Table already exists” Disable table creation in Table Output, or drop the table first.
String truncation / data too long Increase field lengths in CSV File Input before you generate SQL.
SQL window is empty Select Get fields on the Database fields tab first.
Last updated 5 months ago
Was this helpful?
Set-Location C:\Pentaho\design-tools\data-integration .\spoon.bat
cd ~/Pentaho/design-tools/data-integration ./spoon.sh
