While we have cautioned that database design tasks should not normally be performed using PDI, sometimes there are certain tasks that are very repetitive and it can save a lot of time by creating a simple transformation to execute such tasks. For instance, maybe there are some new auditing columns that need to be added to all the tables of a given database. With PDI, and using a database's metadata repository, it is very straightforward to add the columns.
In this recipe we will be creating a transformation that will read a list of tables from the books database in the database's metadata repository and build dynamic queries for each table.
For this recipe you will need the database which can be created and filled by running the script books.sql. This can be found in the code for this chapter.
As the recipe will read metadata from the books database, before proceeding, explore the database's metadata repository to see what is inside. In particular, run the following statement to see what tables are available in the books database:
SELECT TABLE_NAME , TABLE_TYPE , ENGINE , VERSION , ROW_FORMAT , TABLE_ROWS , AVG_ROW_LENGTH , DATA_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'books'; +--------------+------------+--------+---------+------------+------------+----------------+-------------+ | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | +--------------+------------+--------+---------+------------+------------+----------------+-------------+ | authors | BASE TABLE | InnoDB | 10 | Compact | 13 | 1260 | 16384 | | books | BASE TABLE | InnoDB | 10 | Compact | 34 | 481 | 16384 | +--------------+------------+--------+---------+------------+------------+----------------+-------------+ 2 rows in set (0.00 sec)
Create a transformation. Add a Table Input step that reads the
TABLE_NAMEfrom theTABLEStable from MySQL'sinformation_schemadatabase.From the Scripting category, add an Execute SQL script step. Check the option Execute for each row? and add
TABLE_NAMEto the Parameters: section. For the SQL script to execute, use the following:ALTER TABLE ? ADD COLUMN create_date DATETIME DEFAULT '1900-01-01 00:00:00', ADD COLUMN update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Create a hop between the Table Input and Execute SQL script steps. Save and run the transformation. When completed, check the tables in the
booksdatabase. All of them should now have acreate_dateand anupdate_datecolumn added.
Using a database's metadata repository is a very powerful and effective way to know the structure of a given source database. With this transformation we took advantage of that by reading out a list of all the tables from the books database and ran a variable-based query that added two columns to each table based on the table name.
Try adding additional filters to specify certain tables from the books database. MySQL's information_schema database also has a table that details the columns of each table (aptly named COLUMNS). For larger databases, you may want to filter just a subset of tables based on given columns or types.
Getting data from a database by running a query built at runtime
The Executing part of a job once for every row in a dataset recipe in Chapter 8, Executing and Re-using Jobs and Transformations
Building SQL queries based on database metadata