Creating DDL scripts from Excel Model using Pentaho Data Integration
What this post is about
In this post, I will walk through an ETL transformation that takes a data model defined in spreadsheets into a DDL script that can be executed to create tables on PostgreSQL. I am using Pentaho Data Integration Community Edition for this exercise.
Creating Data Models in Spreadsheets
While creating data models, it is usually easier to create a data model using spreadsheets. I typically use multiple sheets in the spreadsheet for each table, list the column names and data types as descriptions.
The structure I use is as follows, with one sample row of data
| ID | AttributeName | DataType | DataWidth | Description | Dimension | SourceColumns | ExtractionMethod |
| 1 | W_ATTRIB_1 | Numeric | 4 |
|
|
|
|
Here is a sample fact table shown in Excel
Here is a Dimension table (its the same structure except the name ends in "_D" instead of "_F" based on naming conventions.
The problem with Spreadsheets
The problem with spreadsheets is of course creating the final SQL script is not straightforward. For this reason, I am creating the transformation in an ETL tool, so that it can be maintained and used over and over again.
Lets walk through the high level steps for the transformation.
High level steps
The high level steps in the ETL transformation are as follows.
1. Read all the spreadsheet(s) sitting in one folder, and read all the sheets in the spreadsheet.
2. Filter the sheets to be processed.
3. Add constants
4. Concatenate Fields
5. Sorting the rows
6. Denormalizing the rows
7. Concatenating the final SQL
8. Outputting to a text file
Here is a picture of the transformation
Reading all the spreadsheets in the folder
The first step is to read all the spreadsheets in a folder, as well as all sheets in each spreadsheet file. Since this step is an important and big enough step, I have posted it as a separate post on my blog.
Filter the sheets to be processed by the transformation
Since I may have multiple sheets in each spreadsheet, and only some may be relevant in the model, I added a step to Filter Rows. Filter Rows is available under the Flow group of transformers on the Design tab.
Click on Edit Step to configure this step. As you can see, I have added two filters to only allow sheets in each file that either end with _D or _F.
Adding some constants
Next step was to add some constants that I need at multiple places in the transformation to fully form the SQL. The Add Constants step can be found under the "Transform" group of steps under the Design tab.
The constants I added were the following:
| # | Constant Name | Value |
| 1. | TableColumnKey | TabColKey |
| 2. | OpenParanthesis | ( |
| 3. | ClosedParanthesis | ) |
| 4. | CreateTable | CREATE TABLE |
| 5. | Semicolon | ; |
| 6. | Space | |
Click Ok to continue. Next step is to concatenate fields to form column definitions.
Concatenating Fields to form Column Definitions
I used the "Concat Fields" step under the Transform group of steps on the Design tab as shown below.
After dragging and connecting it to the previous step, right click and select Edit Step. Enter the following details on the dialog box
1. TargetFieldName: TableColumnDefinition
2. Separator:
or blankHere are the input columns I concatenated, in the following order.
1. AttributeName
2. Space
3. DataType
4. OpenParanthesis
5. DataWidth
6. ClosedParanthesis
This will create a column called TableColumnDefinition concatenating all the above fields in listed order.
Next we need to prepare to flatten the rows. Prior to feeding the rows into the Denormalizer, we need to sort these.
Sorting the definitions by Table name
The Sort Rows step is also present under the Transform group of steps. Drag and connect it to the previous step.
Right click and Edit step to make the following entries. The fields need to be in the order listed below.
1. TableName
2. ID
This will sort the input rows accordingly. Click Ok to continue.
Denormalizing the rows.
This is the most important step in this entire transformation. Drag and connect the Row Denormalizer step present under Transform steps, to the previous steps.
Right-Click and select Edit Step to continue.
On this dialog box, we will enter the following details
1. The Key Field: TableColumnKey (This is the constant we introduced in previous steps and is same for all input rows)
2. GroupField: TableName (We want to select all rows having the same TableName to be merged into a single row)
3. Target Fields: (We will enter only one target field with the following values)
a. TargetFieldName: AllColumns (Each output row will now have this additional column)
b. ValueFieldName: TableColumnDefinition (This is the concatenated column we created in a previous step)
c. Length: 5000 (We need space for long table statements)
d. Aggregation: Concatenate strings separated by , (This will take individual column definitions for a given table name, and concatenate them with comma separations, creating a list of column definitions)
Click Ok to continue.
Constructing the Full SQL
The last step before writing to file is to construct the full SQL. We will concatenate the following with a space between them. Enter the following details
1. TargetFieldName: FullSQL
2. Length of Target Field: 5000
3. Separator: " " (Single Space)
4. Fields:
a. CreateTable
b. TableName
c. OpenParanthesis
d. AllColumns
e. ClosedParanthesis
f. SemiColon
Writing to file
Last step in this transformation is writing to file. Drag and connect a Text File Output step from the Output group of steps.
Enter the following
1. Name of file: Your choice
2. Extension: sql
On the Content tab, I made no changes.
Finally on the Fields tab, we only need one output column with following details
1. Name: FullSQL
2. Length: 5000
Thats it. Save the tranformation and execute.
When you surf through web you may have seen some websites which doesn�t allow their content to be copied. In order to protect their data (text) they use images, flash or any other method. Here I am giving you some methods to extract/copy text from images. This can also be used copy text from games and printed notes. Also known as OCR (Optical Character Recognition). I don�t know whether it can copy text from Hand written. Check it out yourself. Here are the steps�