Query metadata from a database

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 type	Description
Catalog	Gets a list of catalogs from the database server. If the database does not support catalogs, this discovery type returns no rows.
Schemas	Gets a list of schemas from the database server. If the database does not support schemas, this discovery type returns 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.

This step uses the standard JDBC API to collect metadata. Some database JDBC drivers do not fully implement the JDBC API. If you get unexpected results (including no data or partial results), try including a % in relevant pattern fields and/or upgrading your JDBC driver.

General

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

Connection tab

Query metadata from a database step Connection tab

Use the Connection tab to connect to a JDBC database.

Option	Description
Select connection	Select a database connection from the list. You can also select New (create a connection), Edit (edit the selected connection), or Wizard (open the database connection wizard).
From field	Select a connection from fields available in the transformation data stream.
Remove input fields	If you use From field, select Remove input fields to omit the incoming connection fields from the output stream.

Input tab

Query metadata from a database step Input tab

Use the Input tab to filter results with values for each argument.

You can specify argument values in either of these ways:

• Enter arguments: Enter a value for an argument.

• From fields: Select an argument value from fields available in the transformation stream.

Enter arguments

Use this option to add values that filter the results.

• % matches 0 or more characters.

• _ matches exactly 1 character.

Discovery type	Argument	Description
Schemas	Catalog	Name of the catalog to limit results. If empty, schemas from all catalogs are returned.
Schemas	Schema pattern	Pattern to limit schemas. If empty, schemas from all catalogs are returned.
Tables	Catalog	Name of the catalog to limit results. If empty, schemas from all catalogs are returned.
Tables	Schema pattern	Pattern to limit schemas. If empty, tables from all schemas are returned.
Tables	Table pattern	Pattern to limit tables. If empty, all tables are returned.
Tables	Table types	Comma-delimited list of table types to limit results.
Columns	Catalog	Name of the catalog to limit results. If empty, columns from all catalogs are returned.
Columns	Schema pattern	Pattern to limit schemas. If empty, columns from all schemas are returned.
Columns	Table pattern	Pattern to limit tables. This usually follows the database LIKE syntax (may vary by JDBC driver).
Columns	Column pattern	Pattern to limit columns. If empty, all columns are returned.
Primary keys	Catalog	Name of the catalog to limit results. If empty, schemas from all catalogs are returned.
Primary keys	Schema	Name of the schema to limit results.
Primary keys	Table	Name of the table to limit results.
Foreign keys	Catalog	Name of the catalog to limit results. If empty, schemas from all catalogs are returned.
Foreign keys	Schema	Name of the schema to limit results.
Foreign keys	Table	Name of the table to limit results.

From fields

Use this option to filter results with values from the transformation stream. Select a value in one or more of the drop-down lists.

Fields tab

Query metadata from a database step Fields tab

Use the Fields tab to display the output fields for the selected discovery type.

1. Select the discovery type.

2. Select Get fields.

3. (Optional) Use Rename to to rename fields. Renamed fields are available to downstream steps.

Field names by discovery type

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