Query metadata from a database

Last updated
Save as PDF

You can use the Query metadata from a database step to retrieve metadata from six different JDBC metadata discovery types. This is most useful with metadata injection.

JDBC Metadata Discovery Types	Description
Catalog	Gets a list of catalogs from the database servers. For databases that do not support catalogs, this step type will output no rows.
Schemas	Gets a list of schemas from the database servers. For databases that do not support schemas, this step type will output no rows.
Tables	Gets a list of tables from the database.
Columns	Gets a list of columns from the database.
Primary keys	Gets a list of primary keys from the database.
Foreign keys	Gets a list of foreign keys from the database.

NoteThis step makes use of the standard JDBC API to collect metadata. Some database JDBC drivers do not fully implement the JDBC API. For databases that do not fully implement the JDBC API, it is possible that these steps may return unexpected results including no data or partial results. If this occurs, include a % in additional fields and/or upgrade the installed JDBC driver. If upgrading the driver does not resolve the issue, you may have to contact your database vendor.

General

Enter the following information in the transformation step fields:

Step name: Specify the unique name of the Query metadata from Database step on the canvas. You can customize the name or leave it as the default.

Options

The Query metadata from Database step features three options using the Connection, Input, and Fields tabs. Each tab is described below.

Connection tab

You use the Connection tab to connect to a JDBC database. You have two options when selecting a connection in the Query metadata from Database step.

Option	Description
Select connection	Select a Connection from the dropdown menu. You can use the following options to perform additional actions: New Create a new database connection that becomes available in the dropdown. Edit Edit a selected connection. Wizard Open the database connection wizard.
From field	Select a Connection from a display of available fields in the datastream.
Remove input fields	In the From field option, click Remove input fields to omit the fields from prior steps in the transformation data stream.

Input tab

You use the Input tab to filter the results using specific values for each argument presented in the tab. There are two options.

Option	Description
Enter Arguments	Enter a value for a specific argument.
From Fields	Select any fields that are currently available in the transformation stream.

Enter Arguments

You use this option to add values to an argument to use to filter the results. See the following table for the argument options for each JDBC Metadata Discovery Type.

Discovery type	Argument options	Description
Catalog	No options	N/A
Schemas	Catalog	Specify the name of the catalog to limit the results. If left empty, all schemas from all catalogs are returned.
Schemas	Schema Pattern	Specify a pattern to limit the schemas to return. If empty, the tables from all schemas are returned. % matches 0 or more characters and _ matches exactly 1 character.
Tables	Catalog	Specify the name of the catalog to limit the results. If left empty, all schemas from all catalogs are returned.
	Schema Pattern	Specify a pattern to limit the schemas to return. If empty, the tables from all schemas are returned. % matches 0 or more characters and _ matches exactly 1 character.
	Table Pattern	A pattern limiting the tables to return. If empty, all tables are returned. % matches 0 or more characters and _ matches exactly 1 character.
	Table Types	Specify a comma delimited list of table types to limit the results.
Columns	Catalog	Specify the name of the catalog to limit the results. If left empty, all columns from all catalogs are returned.
	Schema Pattern	Specify a pattern to limit the schemas to return. If empty, the columns from all schemas are returned. % matches 0 or more characters and _ matches exactly 1 character.
	Table Pattern	Specify a pattern to limit the columns returned. This usually follows the database's LIKE syntax, but may vary depending on the JDBC driver used.
	Column Pattern	Specify a pattern to limit the columns to return. If empty, all tables are returned. % matches 0 or more characters and _ matches exactly 1 character.
Primary Keys	Catalog	Specify the name of the catalog to limit the results. If left empty, all schemas from all catalogs are returned.
	Schema	Specify the name of the schema to limit the results.
	Table	Specify the name of the table to limit the results.
Foreign Keys	Catalog	Specify the name of the catalog to limit the results. If left empty, all schemas from all catalogs are returned.
	Schema	Specify the name of the schema to limit the results
	Table	Specify the name of the table to limit the results.

From Fields

You use this option to specify fields within the transformation stream to filter the results. Select a value in one or more of the dropdown menus.

Fields tab

You use the Fields tab to display all the fields within the JDBC discovery type. Click the Get fields button to display all the fields associated with each discovery type in the Field name column.

Optional: Use the Rename to column to rename the field names. The new names will be available in any downstream transformation step.

JDBC Metadata Discovery Type	Field name	Description
Catalog	`catalog`	Name of the database catalog
Schemas	`catalog`	Name of the database catalog
Schemas	`schema`	Name of the database schema
Tables	`catalog`	Name of the database catalog
	`schema`	Name of the database schema
	`ref_generation`	Reference generation
	`remarks`	Remarks
	`self_referencing_column_name`	A column that refers to another column in the same table
	`table_name`	Name of the database table
	`table_type`	Type of the database table
	`type_catalog`	Type catalog
	`type_name`	Type name
	`type_schema`	Type schema
Columns	`buffer_length`	Length of the buffer for specific data types
	`char_octet_length`	Maximum length, in bytes, for binary data, character data, or text and image data.
	`column_default`	Default value of the column
	`column_name`	Name of the column
	`column_size`	Number of characters or digits of the data
	`data_type`	Data type of the column
	`decimal_digits`	Number of digits after the decimal point
	`is_autoincrement`	Whether the column implements automatic increments
	`is_generated_column`	Whether the column is generated
	`is_nullable`	Whether the column allows null values.
	`nullable`	Column is nullable
	`number_precision_radix`	The base sytsem used for numeric precision (usually base 2 or 10)
	`ordinal_position`	The location of the column ordering within a table or query output
	`pentaho_data_type`	Maps to the custom Pentaho data type
	`pentaho_data_type_int`	Maps to the custom Pentaho integer data type
	`remarks`	Remarks
	`schema`	Name of the database schema
	`scope_catalog`	Catalog that lies within a scope
	`scope_schema`	Schema that lies within a scope
	`scope_table`	Table that lies within a scope.
	`source_data_type`	Data type within a source
	`sql_data_type`	SQL data type for a column
	`sql_datetime_sub`	SQL date/time sub
	`table_name`	Name of a table in the database
	`type_name`	Name of a type in a database
Primary Keys	`catalog`	Name of the database catalog
	`column_name`	Name of the column
	`key_sequence`	Sequence of the primary key
	`pk_name`	Name of the primary key
	`schema`	Name of the database schema
	`table_name`	Name of a table in the database
Foreign Keys	`deferrability`	Whether the primary key have deferred constraints
	`delete_rule`	Delete rule for primary key
	`fk_column_name`	Name of the foreign key column
	`fk_name`	Name of the foreign key
	`fk_table_schema`	Schema in the foreign key table
	`key_sequence`	Sequence of the foreign key
	`pk_column_name`	Name of the column in the primary key
	`pk_name`	Name of the primary key
	`pk_table_catalog`	Name of the catalog to which the primary key's table belongs
	`pk_table_name`	Name of the table in the primary key
	`pk_table_schema`	Schema in the primary key table
	`update_rule`	Update rule for primary key

Metadata injection support

All fields of this step support metadata injection. You can use this step with ETL metadata injection to pass metadata to your transformation at runtime.

Pentaho+ documentation has moved!

The new product documentation portal is here. Check it out now at docs.hitachivantara.com.