Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases now! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Pentaho Data Integration 4 Cookbook

You're reading from   Pentaho Data Integration 4 Cookbook Over 70 recipes to solve ETL problems using Pentaho Kettle

Arrow left icon
Product type Paperback
Published in Jun 2011
Publisher Packt
ISBN-13 9781849515245
Length 352 pages
Edition 1st Edition
Tools
Arrow right icon
Toc

Table of Contents (17) Chapters Close

Pentaho Data Integration 4 Cookbook
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface
1. Working with Databases FREE CHAPTER 2. Reading and Writing Files 3. Manipulating XML Structures 4. File Management 5. Looking for Data 6. Understanding Data Flows 7. Executing and Reusing Jobs and Transformations 8. Integrating Kettle and the Pentaho Suite 9. Getting the Most Out of Kettle Data Structures Index

Creating or altering a database table from PDI (runtime)


When you are developing with PDI, you know (or have the means to find out) if the tables you need exist or not, and if they have all the columns you will read or update. If they don't exist or don't meet your requirements, you can create or modify them, and then proceed. Assume the following scenarios:

  • You need to load some data into a temporary table. The table exists but you need to add some new columns to it before proceeding.

  • You have to load a dimension table. This task is part of a new requirement, so this table doesn't exist.

While you are creating the transformations and jobs, you have the chance to create or modify those tables. But if these transformations and jobs are to be run in batch mode in a different environment, nobody will be there to do these verifications or create or modify the tables. You need to adapt your work so these things are done automatically.

Suppose that you need to do some calculations and store the results in a temporary table that will be used later in another process. As this is a new requirement, it is likely that the table doesn't exist in the target database. You can create a job that takes care of this.

How to do it...

  1. Create a job, and add a Start job entry.

  2. From the Conditions category, drag and drop a Table exists entry, an SQL entry from Scripting, and a Dummy entry from General.

  3. Link all the entries as shown:

  4. Double-click the Table exists entry, choose a database connection, and as Table name type my_tmp_table.

  5. Double-click the SQL entry, choose the same database connection as above, and in the SQL Script: type:

    CREATE TABLE my_tmp_table (
    CALC_1 NUMERIC(10,2),
    CALC_2 NUMERIC(10,2),
    CALC_3 NUMERIC(10,2)
    );

    Note

    The statement above is written with MySQL syntax. Please review and fix it if needed because you are using a different DBMS.

  6. Save the job and run it.

  7. The table my_tmp_table should have been created.

  8. Run the job again.

  9. Nothing should happen.

How it works...

The Table exists entry, as implied by its name, verifies if a table exists in your database. As with any job entry, this entry either succeeds or fails. If it fails, the job creates the table with an SQL entry. If it succeeds, the job does nothing.

There's more...

The SQL entry is very useful not only for creating tables as you did in the recipe, but also for executing very simple statements, as for example setting a flag before or after running a transformation. Its main use, however, is executing DDL statements.

On the other side, in order to decide if it was necessary to create the table or not, you used a Table exists entry. In addition to this entry and before verifying the existence of the table, you could have used the Check Db connections. This entry allows you to see if the database is available.

Now, let's suppose the table exists, but it is an old version that doesn't have all the columns you need. In this case you can use an extra useful entry: Columns exist in a table. If you can detect that a column is not present, you can alter the table by adding that column, also with an SQL job entry.

Note

Creating or altering tables is not a task that should be done as part of an ETL process. Kettle allows you to do it but you should be careful when using these features.

See also

  • Creating or altering a database table from PDI (design time). Instead of doing these operations at runtime, you can do it from Spoon while you are designing the jobs and transformations. This recipe explains how to do this.

You have been reading a chapter from
Pentaho Data Integration 4 Cookbook
Published in: Jun 2011
Publisher: Packt
ISBN-13: 9781849515245
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime