JDBC Metadata

Abstract: Call the attached transformation to obtain database metadata from an arbitrary JDBC connection.

Authors: Roland Bouman

License: LGPL

Kettle versions: 3.2.0 and up (see Attachments section for more detailed info)

Deprecation info

The functionality of this transformation is also available in the community contributed JDBC Metadata step plugin. This step plugin is available here on github https://github.com/rpbouman/pentaho-pdi-plugin-jdbc-metadata. It can be installed directly from within spoon from the marketplace.

Attachments

• get_jdbc_metadata.ktr - Works in Kettle 3.2.0 and up

Background

All JDBC drivers offer metadata as defined in java.sql.DatabaseMetaData. For instance, there's catalog metadata, general driver metadata and lot more. This metadata can be useful to generate schema migrations, or alternative schema representations (like XML Schema) or even code generation.

The get_jdbc_metadata.ktr transformation expects an input stream containing these fields:

• jdbc_driver
• jdbc_url
• jdbc_user
• jdbc_password

The transformation uses this data in a number of javascript steps to instantiate an actual JDBC connection to get resultsets with metadata. These resultsets are then converted to kettle output streams. The following output streams are defined:

• data types output (see: java.sql.DatabaseMetaData.getTypeInfo())
• tables output (see: java.sql.DatabaseMetaData.getTables())
• columns output (see: java.sql.DatabaseMetaData.getTables())
• primary key output (see: java.sql.DatabaseMetaData.getPrimaryKeys())
• imported key output (see: java.sql.DatabaseMetaData.getImportedKeys())
• index info output (see: java.sql.DatabaseMetaData.getIndexInfo())

See Also:

• [http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html|http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html]