Skip to main content
Hitachi Vantara Lumada and Pentaho Documentation

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 TypesDescription
CatalogGets a list of catalogs from the database servers. For databases that do not support catalogs, this step type will output no rows.
SchemasGets a list of schemas from the database servers. For databases that do not support schemas, this step type will output no rows.
TablesGets a list of tables from the database.
ColumnsGets a list of columns from the database.
Primary keysGets a list of primary keys from the database.
Foreign keysGets 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

Query metadata from Database step 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.

OptionDescription
Select connectionSelect 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 fieldSelect 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

Query metadata from Database step 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.

OptionDescription
Enter ArgumentsEnter a value for a specific argument.
From FieldsSelect 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 typeArgument optionsDescription
CatalogNo optionsN/A
SchemasCatalogSpecify the name of the catalog to limit the results. If left empty, all schemas from all catalogs are returned.
Schema PatternSpecify 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.
TablesCatalogSpecify the name of the catalog to limit the results. If left empty, all schemas from all catalogs are returned.
Schema PatternSpecify 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 PatternA 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.
ColumnsCatalogSpecify 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 KeysCatalogSpecify the name of the catalog to limit the results. If left empty, all schemas from all catalogs are returned.
SchemaSpecify the name of the schema to limit the results.
TableSpecify the name of the table to limit the results.
Foreign KeysCatalogSpecify the name of the catalog to limit the results. If left empty, all schemas from all catalogs are returned.
SchemaSpecify the name of the schema to limit the results
TableSpecify 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

Query metadata from Database step 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 TypeField nameDescription
CatalogcatalogName of the database catalog
SchemascatalogName of the database catalog
schemaName of the database schema
TablescatalogName of the database catalog
schemaName of the database schema
ref_generationReference generation
remarksRemarks
self_referencing_column_name

A column that refers to another column in the same table

table_nameName of the database table
table_typeType of the database table
type_catalogType catalog
type_nameType name
type_schemaType schema
Columnsbuffer_lengthLength of the buffer for specific data types
char_octet_lengthMaximum length, in bytes, for binary data, character data, or text and image data.
column_defaultDefault value of the column
column_nameName of the column
column_sizeNumber of characters or digits of the data
data_typeData type of the column
decimal_digitsNumber of digits after the decimal point
is_autoincrementWhether the column implements automatic increments
is_generated_columnWhether the column is generated
is_nullableWhether the column allows null values.
nullableColumn is nullable
number_precision_radixThe base sytsem used for numeric precision (usually base 2 or 10)
ordinal_positionThe location of the column ordering within a table or query output
pentaho_data_typeMaps to the custom Pentaho data type
pentaho_data_type_intMaps to the custom Pentaho integer data type
remarksRemarks
schemaName of the database schema
scope_catalogCatalog that lies within a scope
scope_schemaSchema that lies within a scope
scope_tableTable that lies within a scope.
source_data_typeData type within a source
sql_data_typeSQL data type for a column
sql_datetime_subSQL date/time sub
table_nameName of a table in the database
type_nameName of a type in a database
Primary KeyscatalogName of the database catalog
column_nameName of the column
key_sequenceSequence of the primary key
pk_nameName of the primary key
schemaName of the database schema
table_nameName of a table in the database
Foreign KeysdeferrabilityWhether the primary key have deferred constraints
delete_ruleDelete rule for primary key
fk_column_nameName of the foreign key column
fk_nameName of the foreign key
fk_table_schemaSchema in the foreign key table
key_sequenceSequence of the foreign key
pk_column_nameName of the column in the primary key
pk_nameName of the primary key
pk_table_catalogName of the catalog to which the primary key's table belongs
pk_table_nameName of the table in the primary key
pk_table_schemaSchema in the primary key table
update_ruleUpdate 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.