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.
- Create a job, and add a Start job entry.
- From the Conditions category, drag and drop a Table exists entry, an SQL entry from Scripting, and a Dummy entry from General.
- Link all the entries as shown:
- Double-click the Table exists entry, choose a database connection, and as Table name type
my_tmp_table. - 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) );
The statement above is written with MySQL syntax. Please review and fix it if needed because you are using a different DBMS.
- Save the job and run it.
- The table
my_tmp_tableshould have been created. - Run the job again.
- Nothing should happen.
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.
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.