Ctrlk
For the complete documentation index, see llms.txt. This page is also available as Markdown.

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 TABLE SQL from Table Output

  • 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.

sales_data.csv
504KB
Open
orders.txt
3KB
Open
tr_write_database.ktr
34KB
Open
Create databases

Create a new transformation

Use any of these options to open a new transformation tab:

  • Select File > New > Transformation

  • Use Ctrl+N (Windows/Linux) or Cmd+N (macOS)

Load Sales Data

Load sales data from a CSV file into STG_SALES_DATA.

Adjust field lengths to avoid truncation.

Load sales data

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.

  1. Start Pentaho Data Integration.

Start Spoon

  1. Drag the CSV file input step onto the canvas.

  2. Open the CSV file input properties dialog box.

Configure these key fields:

  • Step name: csvi-sales_data

  • File name: ${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.

CSV File Input infers field lengths from a sample. Increase string lengths before you generate table DDL.

  1. Ensure the following details are configured, as outlined below:

CSV file input

  1. Click on the Get Fields button.

  2. Select OK.

Table Output

Load rows into a database table. This step uses SQL INSERT.

  1. Drag the Table Output step onto the canvas.

  2. Open the Table Output properties dialog box. Ensure the following details are configured, as outlined below:

Table output - options

  1. Click on the Database fields.

  2. Click on the ‘Get Fields’ button.

Table output - fields

Confirm the mappings between Table fields and Stream fields.

  1. Click on the SQL button.

SQL editor

  1. Select Execute.

  2. 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.

  1. Click the Run button in the Canvas Toolbar.

  2. Confirm the table exists in your sampledata database.

STG_SALES_DATA

Load Orders

Load orders data from a delimited file into STG_ORDERS_MERGED.

Adjust field lengths to avoid truncation.

Load orders data

Follow the steps outlined below:

CSV File Input

Same setup as Workflow 1, but point to your orders file.

  1. Drag the CSV file input step onto the canvas.

  2. Open the CSV file input properties dialog box.

Ensure the following details are configured, as outlined below:

Add path to orders.txt
Set Content
Get Fields

Table Output

Load rows into a database table. This step uses SQL INSERT.

  1. Drag the Table Output step onto the canvas.

  2. Open the Table Output properties dialog box. Ensure the following details are configured, as outlined below:

Table output - options

  1. Click on the Database fields.

  2. Click on the ‘Get Fields’ button.

Table output -fields

  1. Click on the SQL button.

SQL editor

  1. Click Execute.

  2. Select OK to close all dialogs.

Checkpoint: STG_ORDERS_MERGED exists in the database.

RUN and validate

Use a database tool to verify results.

  1. Click the Run button in the Canvas Toolbar.

  2. Confirm the table exists in your sampledata database.

STG_ORDERS_MERGED
Troubleshooting

“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.

PreviousDatabase Connections
NextRead DB table

Last updated

Was this helpful?