# Information Schema in Confluent Cloud for Apache Flink
An *information schema*, or data dictionary, is a standard SQL schema with a
collection of predefined views that enable accessing metadata about objects in
Confluent Cloud for Apache Flink®. The Confluent INFORMATION_SCHEMA is based on the
[SQL-92 ANSI Information Schema](https://datacadamia.com/data/type/relation/sql/information_schema),
with the addition of views and functions that are specific to Confluent Cloud for Apache Flink.
The ANSI standard uses “catalog” to refer to a database. In Confluent Cloud,
“schema” refers to a database. Conceptually, the terms are equivalent.
The views in the INFORMATION_SCHEMA provide information about database objects,
such as tables, columns, and constraints. The views are organized into tables
that you can query by using standard SQL statements.
For example, you can use the INFORMATION_SCHEMA.COLUMNS table to get details
about the columns in a table, like the column name, data type, and whether it
allows null values.
Similarly, you can use the INFORMATION_SCHEMA.TABLES table to get singular,
static [configuration details](#flink-sql-information-schema-tables) of
the relation. For example, you can query for details like
[watermark definition](statements/create-table.md#flink-sql-watermark-clause) and the number of
[partitions](statements/create-table.md#flink-sql-distributed-by) in the topic.
In the information schema, *relation* is the general term for any object that
you can query: a table, an external table, a view, or a system table.
Despite its name, the INFORMATION_SCHEMA.TABLES view describes all relation
types, not only tables. Use the `TABLE_TYPE` column to distinguish them:
its value is `BASE TABLE`, `EXTERNAL TABLE`, `SYSTEM TABLE`, or
`VIEW`.
Every Flink catalog has a corresponding INFORMATION_SCHEMA, so you can always
run a statement like
`SELECT (...) FROM .INFORMATION_SCHEMA.TABLES WHERE (...)`.
Global views are available in every INFORMATION_SCHEMA, which means that you
can query for information across catalogs. For example, you can query the
global INFORMATION_SCHEMA.CATALOGS view to list all catalogs.
The information schema is a powerful tool for querying metadata about your
Flink catalogs and databases, and you can use it for a variety of purposes, such
as generating reports, documenting a schema, and troubleshooting performance
issues.
The following views are supported in the Confluent INFORMATION_SCHEMA:
- [Catalogs and databases](#flink-sql-information-schema-catalogs-and-databases)
- [CATALOGS](#flink-sql-information-schema-catalogs)
- [INFORMATION_SCHEMA_CATALOG_NAME](#flink-sql-information-schema-catalog-name)
- [SCHEMATA / DATABASES](#flink-sql-information-schema-schemata)
- [Functions](#flink-sql-information-schema-functions)
- [PARAMETERS](#flink-sql-information-schema-functions-parameters)
- [ROUTINES](#flink-sql-information-schema-functions-routines)
- [Models](#flink-sql-information-schema-models)
- [MODELS](#flink-sql-information-schema-models-table)
- [MODEL_OPTIONS](#flink-sql-information-schema-model-options)
- [MODEL_COLUMNS](#flink-sql-information-schema-model-columns)
- [Connections](#flink-sql-information-schema-connections)
- [CONNECTIONS](#flink-sql-information-schema-connections-table)
- [CONNECTION_OPTIONS](#flink-sql-information-schema-connection-options)
- [Tools](#flink-sql-information-schema-tools)
- [TOOLS](#flink-sql-information-schema-tools-table)
- [TOOL_OPTIONS](#flink-sql-information-schema-tool-options)
- [Agents](#flink-sql-information-schema-agents)
- [AGENTS](#flink-sql-information-schema-agents-table)
- [AGENT_OPTIONS](#flink-sql-information-schema-agent-options)
- [AGENT_TOOLS](#flink-sql-information-schema-agent-tools)
- [TABLES](#flink-sql-information-schema-tables)
- [COLUMNS](#flink-sql-information-schema-columns)
- [KEY_COLUMN_USAGE](#flink-sql-information-schema-key-column-usage)
- [MATERIALIZED_TABLES](#flink-sql-information-schema-materialized-tables)
- [SYSTEM_TABLES](#flink-sql-information-schema-system-tables)
- [TABLE_CONSTRAINTS](#flink-sql-information-schema-table-constraints)
- [TABLE_OPTIONS](#flink-sql-information-schema-table-options)
- [VIEWS](#flink-sql-information-schema-views)
## Query syntax in INFORMATION_SCHEMA
Metadata queries on the INFORMATION_SCHEMA tables support the following syntax.
Supported data types:
- INT
- STRING
Supported operators:
- SELECT
- WHERE
- UNION ALL
Supported expressions:
- CAST(NULL AS dt), CAST(x as dt)
- UNION ALL (see [this example](#flink-sql-information-schema-union-all))
- AND, OR
- = , <>, IS NULL, IS NOT NULL
- AS
- STRING and INT literals
The following limitations apply to INFORMATION_SCHEMA:
- You can use INFORMATION_SCHEMA views only in SELECT statements, not in INSERT
INTO statements.
- You can’t use INFORMATION_SCHEMA in joins with real tables.
- Only the previously listed equality and basic expressions are supported.
## Catalogs and databases
### CATALOGS
The global catalogs view.
The rows returned are limited to the schemas that you have permission to
interact with.
This view is an extension to the SQL standard.
| Column Name | Data Type | Standard | Description |
|---------------|-----------------|------------|-----------------------------------------------------------------------------|
| CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment, for example, `env-xmzdkk`. |
| CATALOG_NAME | STRING NOT NULL | No | The human readable name of the catalog/environment, for example, `default`. |
Example
: Run the following code to query for all catalogs across environments.
```sql
SELECT
`CATALOG_ID`,
`CATALOG_NAME`
FROM `INFORMATION_SCHEMA`.`CATALOGS`;
```
### INFORMATION_SCHEMA_CATALOG_NAME
Local catalog view. Returns the name of the current information schema’s
catalog.
| Column Name | Data Type | Standard | Description |
|---------------|-----------------|------------|-----------------------------------------------------------------------------|
| CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment, for example, `env-xmzdkk`. |
| CATALOG_NAME | STRING NOT NULL | Yes | The human readable name of the catalog/environment, for example, `default`. |
Example
: Run the following code to query for the name of this information schema’s
catalog.
```sql
SELECT
`CATALOG_ID`,
`CATALOG_NAME`
FROM `INFORMATION_SCHEMA`.`INFORMATION_SCHEMA_CATALOG_NAME`
```
### SCHEMATA / DATABASES
Describes databases within the catalog.
For convenience, DATABASES is an alias for SCHEMATA.
The rows returned are limited to the schemas that you have permission to
interact with.
| Column Name | Data Type | Standard | Description |
|---------------|-----------------|------------|-----------------------------------------------------------------------------|
| CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment, for example, `env-xmzdkk`. |
| CATALOG_NAME | STRING NOT NULL | Yes | The human readable name of the catalog/environment, for example, `default`. |
| SCHEMA_ID | STRING NOT NULL | No | The ID of the database/cluster, for example, `lkc-kgjwwv`. |
| SCHEMA_NAME | STRING NOT NULL | Yes | The human readable name of the database/cluster, for example, MyCluster. |
Example
: Run the following code to list all Flink databases within a catalog,
(Kafka clusters within an environment), excluding information schema.
```sql
SELECT
`SCHEMA_ID`,
`SCHEMA_NAME`
FROM `INFORMATION_SCHEMA`.`SCHEMATA`
WHERE `SCHEMA_NAME` <> 'INFORMATION_SCHEMA';
```
### COLUMNS
Describes columns of tables and virtual tables (views) in the catalog.
| Column Name | Data Type | Standard | Description |
|-------------------------------|-----------------|---------------------------------------|------------------------------------------------------------------------------------------------------------------------|
| COLUMN_NAME | STRING NOT NULL | Yes | Column reference. |
| COMMENT | STRING NULL | No (Like Databricks, Snowflake) | An optional comment that describes the column. [1](#id2) |
| DATA_TYPE | STRING NOT NULL | Yes | Type root, for example, VARCHAR or ROW. |
| DISTRIBUTION_ORDINAL_POSITION | INT NULL | No (Like BigQuery for clustering key) | If the table IS_DISTRIBUTED, contains the position of the key in a DISTRIBUTED BY clause. |
| FULL_DATA_TYPE | STRING NOT NULL | No (Like Databricks) | Fully qualified data type. for example, VARCHAR(32) or ROW<…>. |
| GENERATION_EXPRESSION | STRING NULL | Yes (Like BigQuery and Databricks) | For computed columns. |
| IS_GENERATED | STRING NOT NULL | Yes | Indicates whether column is a computed column. Values are YES or NO. |
| IS_HIDDEN | STRING NOT NULL | No (Like BigQuery) | Indicates whether a column is a system column. Values are YES or NO. |
| IS_METADATA | STRING NOT NULL | No | Indicates whether column is a metadata column. Values are YES or NO. |
| IS_NULLABLE | STRING NOT NULL | No | Indicates whether the column is nullable. Values are YES or NO. |
| IS_PERSISTED | STRING NOT NULL | No | Indicates whether a metadata column is stored during INSERT INTO. Also YES if a physical column. Values are YES or NO. |
| METADATA_KEY | STRING NULL | No | For metadata columns. |
| ORDINAL_POSITION | INT NOT NULL | Yes | Position of the column, starting at 1. |
| TABLE_CATALOG | STRING NOT NULL | Yes | The human readable name of the catalog. |
| TABLE_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog. |
| TABLE_NAME | STRING NOT NULL | Yes | The name of the relation. |
| TABLE_SCHEMA | STRING NOT NULL | Yes | The human-readable name of the database. |
| TABLE_SCHEMA_ID | STRING NOT NULL | No | The ID of the database. |
* **[1]** Column comments are defined either in the corresponding topic schema definition, for example, in an Avro `doc` element annotating the field, or in the CREATE TABLE or ALTER TABLE … ADD COLUMN statement.
Examples
: This example shows a complex query. The complexity comes from reducing the
number of requests. Because the views are in normal form, instead of issuing
three requests, you can batch them into single one by using UNION ALL. UNION
ALL avoids the need for various inner/outer joins. The result is a sparse
table that contains different “sections”.
The overall schema looks like this:
```none
(
section,
column_name,
column_pos,
column_type,
constraint_name,
constraint_type,
constraint_enforced
)
```
Run the following code to list columns, like name, position, data type,
and their primary key characteristics.
```sql
(
SELECT
'COLUMNS' AS `section`,
`COLUMN_NAME` AS `column_name`,
`ORDINAL_POSITION` AS `column_pos`,
`FULL_DATA_TYPE` AS `column_type`,
CAST(NULL AS STRING) AS `constraint_name`,
CAST(NULL AS STRING) AS `constraint_type`,
CAST(NULL AS STRING) AS `constraint_enforced`
FROM
``.`INFORMATION_SCHEMA`.`COLUMNS`
WHERE
`TABLE_CATALOG` = '' AND
`TABLE_SCHEMA` = '' AND
`TABLE_NAME` = '' AND
`IS_HIDDEN` = 'NO'
)
UNION ALL
(
SELECT
'TABLE_CONSTRAINTS' AS `section`,
CAST(NULL AS STRING) AS `column_name`,
CAST(NULL AS INT) AS `column_pos`,
CAST(NULL AS STRING) AS `column_type`,
`CONSTRAINT_NAME` AS `constraint_name`,
`CONSTRAINT_TYPE` AS `constraint_type`,
`ENFORCED` AS `constraint_enforced`
FROM
`<>`.`INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS`
WHERE
`CONSTRAINT_CATALOG` = '' AND
`CONSTRAINT_SCHEMA` = '' AND
`TABLE_CATALOG` = '' AND
`TABLE_SCHEMA` = '' AND
`TABLE_NAME` = ''
)
UNION ALL
(
SELECT
'KEY_COLUMN_USAGE' AS `section`,
`COLUMN_NAME` AS `column_name`,
`ORDINAL_POSITION` AS `column_pos`,
CAST(NULL AS STRING) AS `column_type`,
`CONSTRAINT_NAME` AS `constraint_name`,
CAST(NULL AS STRING) AS `constraint_type`,
CAST(NULL AS STRING) AS `constraint_enforced`
FROM
`<>`.`INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
WHERE
`TABLE_CATALOG` = '' AND
`TABLE_SCHEMA` = '' AND
`TABLE_NAME` = ''
);
```
### KEY_COLUMN_USAGE
Side view of TABLE_CONSTRAINTS for key columns.
| Column Name | Data Type | Standard | Description |
|-----------------------|-----------------|------------|-------------------------------------------------------------------------------|
| COLUMN_NAME | STRING NOT NULL | Yes | The name of the constrained column. |
| CONSTRAINT_CATALOG | STRING NOT NULL | Yes | Catalog name containing the constraint. |
| CONSTRAINT_CATALOG_ID | STRING NOT NULL | No | Catalog ID containing the constraint. |
| CONSTRAINT_SCHEMA | STRING NOT NULL | Yes | Schema name containing the constraint. |
| CONSTRAINT_SCHEMA_ID | STRING NOT NULL | No | Schema ID containing the constraint. |
| CONSTRAINT_NAME | STRING NOT NULL | Yes | Name of the constraint. |
| ORDINAL_POSITION | INT NOT NULL | Yes | The ordinal position of the column within the constraint key (starting at 1). |
| TABLE_CATALOG | STRING NOT NULL | Yes | The human readable name of the catalog. |
| TABLE_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog. |
| TABLE_NAME | STRING NOT NULL | Yes | The name of the relation. |
| TABLE_SCHEMA | STRING NOT NULL | Yes | The human readable name of the database. |
| TABLE_SCHEMA_ID | STRING NOT NULL | No | The ID of the database. |
Example
: Run the following code to query for a side view of TABLE_CONSTRAINTS for key columns.
```sql
SELECT *
FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
```
### MATERIALIZED_TABLES
Contains the status and expanded query definition for materialized tables
within the catalog.
Extension to the SQL Standard Information Schema.
The rows returned are limited to the schemas that you have permission to
interact with.
| Column Name | Data Type | Description |
|-------------------------------|-----------------|--------------------------------------------------------------------------------------------------------------------|
| TABLE_CATALOG_ID | STRING NOT NULL | The ID of the catalog/environment. |
| TABLE_CATALOG | STRING NOT NULL | The human-readable name of the catalog/environment. |
| TABLE_SCHEMA_ID | STRING NOT NULL | The ID of the database/cluster. |
| TABLE_SCHEMA | STRING NOT NULL | The human-readable name of the database/cluster. |
| TABLE_NAME | STRING NOT NULL | The name of the materialized table. |
| STATUS | STRING NOT NULL | The current refresh phase of the materialized table, for example, `RUNNING`, `DEGRADED`, `FAILED`, or `COMPLETED`. |
| MATERIALIZED_TABLE_DEFINITION | STRING NULL | The expanded query that defines the materialized table. |
Example
: Run the following code to list all materialized tables within a catalog,
along with their current status.
```sql
SELECT
`TABLE_NAME`,
`TABLE_SCHEMA`,
`STATUS`
FROM `INFORMATION_SCHEMA`.`MATERIALIZED_TABLES`
WHERE `TABLE_SCHEMA` = '';
```
### SYSTEM_TABLES
Contains the object-level metadata for virtual tables within the catalog.
Virtual tables do not represent physical data. Instead, they expose specific
metadata.
The rows returned are limited to the schemas the user has permission to
interact with.
| Column Name | Data Type | Description |
|------------------|-----------------|-----------------------------------------------------------------|
| BASE_TABLE_NAME | STRING NULL | The name of the relation to which this system view corresponds. |
| COMMENT | STRING NULL | An optional comment that describes the system view. |
| TABLE_CATALOG | STRING NOT NULL | The human-readable name of the catalog. |
| TABLE_CATALOG_ID | STRING NOT NULL | The ID of the catalog. |
| TABLE_NAME | STRING NOT NULL | The name of the relation. |
| TABLE_SCHEMA | STRING NOT NULL | The human-readable name of the database. |
| TABLE_SCHEMA_ID | STRING NOT NULL | The ID of the database. |
### TABLES
Contains the object level metadata for tables and virtual tables (views)
within the catalog.
The rows returned are limited to the schemas that you have permission to
interact with.
| Column Name | Data Type | Standard | Description |
|------------------------|-----------------|----------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| COMMENT | STRING NULL | No (Like Databricks and Snowflake) | An optional comment that describes the relation. [2](#id6) |
| DISTRIBUTION_ALGORITHM | STRING NULL | No | Only `HASH` is supported. |
| DISTRIBUTION_BUCKETS | INT NULL | No | Number of buckets, if defined. |
| IS_DISTRIBUTED | STRING NOT NULL | No (Like Snowflake for clustering key) | Indicates whether the table is bucketed using the DISTRIBUTED BY clause. Values are `YES` or `NO`. |
| IS_MATERIALIZED | STRING NOT NULL | No | Indicates whether the relation is a materialized table. Values are `YES` or `NO`. Because a materialized table also has a `TABLE_TYPE` of `BASE TABLE` [3](#id7), this is the only column that distinguishes it from a regular Confluent-native table. Filtering on this column is optimized at the source, so queries are efficient. |
| IS_WATERMARKED | STRING NOT NULL | No | Indicates whether the table has a [watermark](../../_glossary.md#term-watermark) from the WATERMARK FOR clause. Values are `YES` or `NO`. |
| TABLE_CATALOG | STRING NOT NULL | Yes | The human-readable name of the catalog. |
| TABLE_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog. |
| TABLE_NAME | STRING NOT NULL | Yes | The name of the relation. |
| TABLE_SCHEMA | STRING NOT NULL | Yes | The human-readable name of the database. |
| TABLE_SCHEMA_ID | STRING NOT NULL | No | The ID of the database. |
| TABLE_TYPE | STRING NOT NULL | Yes | Values are `BASE TABLE`, `EXTERNAL TABLE`, `SYSTEM TABLE`, or `VIEW` [3](#id7). |
| WATERMARK_COLUMN | STRING NULL | No | Time attribute column for which the watermark is defined. |
| WATERMARK_EXPRESSION | STRING NULL | No | Watermark expression. |
| WATERMARK_IS_HIDDEN | STRING NULL | No | Indicates whether the watermark is the default, system-provided one. |
* **[2]** Table comments are defined either in the corresponding topic schema definition, for example, in an Avro `doc` element annotation, or in the CREATE TABLE or ALTER TABLE … COMMENT clause.
* **[3]** These are the valid values for the TABLE_TYPE column. - BASE TABLE: For Confluent-native tables that can be used conceptually for reading and writing, like a regular database table. This includes both regular tables and materialized tables. Use the `IS_MATERIALIZED` column to distinguish between them. - EXTERNAL TABLE: For non-native Confluent table, for example non-Kafka and Tableflow. Usually, those tables are read-only. - SYSTEM TABLE: For tables that the system creates, either with a BASE TABLE or on its own. Only `$error` is supported. Compared with BASE TABLE, these tables are read-only. - VIEW: For SQL views.
Examples
: Run the following code to list all tables within a catalog (Kafka topics
within an environment), excluding the information schema.
```sql
SELECT
`TABLE_CATALOG`,
`TABLE_SCHEMA`,
`TABLE_NAME`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA` <> 'INFORMATION_SCHEMA'
AND `TABLE_TYPE` = 'BASE TABLE';
```
Run the following code to list all tables within a database (Kafka topics
within a cluster).
```sql
SELECT
`TABLE_CATALOG`,
`TABLE_SCHEMA`,
`TABLE_NAME`
FROM ``.`INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA` = ''
AND `TABLE_TYPE` = 'BASE TABLE';
```
Both queries filter on `TABLE_TYPE` so they return only Kafka topics. Omit
the `TABLE_TYPE` predicate to list every relation in scope, which includes
external tables and views.
### TABLE_CONSTRAINTS
Side view of TABLES for all primary key constraints within the catalog.
| Column Name | Data Type | Standard | Description |
|-----------------------|-----------------|------------|----------------------------------------------|
| CONSTRAINT_CATALOG | STRING NOT NULL | Yes | Catalog name containing the constraint. |
| CONSTRAINT_CATALOG_ID | STRING NOT NULL | No | Catalog ID containing the constraint. |
| CONSTRAINT_SCHEMA | STRING NOT NULL | Yes | Schema name containing the constraint. |
| CONSTRAINT_SCHEMA_ID | STRING NOT NULL | No | Schema ID containing the constraint. |
| CONSTRAINT_NAME | STRING NOT NULL | Yes | Name of the constraint. |
| CONSTRAINT_TYPE | STRING NOT NULL | Yes | Currently, only PRIMARY KEY. |
| ENFORCED | STRING NOT NULL | Yes | YES if constraint is enforced, otherwise NO. |
| TABLE_CATALOG | STRING NOT NULL | Yes | The human readable name of the catalog. |
| TABLE_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog. |
| TABLE_NAME | STRING NOT NULL | Yes | The name of the relation. |
| TABLE_SCHEMA | STRING NOT NULL | Yes | The human readable name of the database. |
| TABLE_SCHEMA_ID | STRING NOT NULL | No | The ID of the database. |
Examples
: Run the following code to query for a side view of TABLES for all primary
key constraints within the catalog.
```sql
SELECT *
FROM `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS`;
```
### TABLE_OPTIONS
Side view of TABLES for WITH.
Extension to the SQL Standard Information Schema.
| Column Name | Data Type | Description |
|------------------|-----------------|------------------------------------------|
| TABLE_CATALOG | STRING NOT NULL | The human readable name of the catalog. |
| TABLE_CATALOG_ID | STRING NOT NULL | The ID of the catalog. |
| TABLE_NAME | STRING NOT NULL | The name of the relation. |
| TABLE_SCHEMA | STRING NOT NULL | The human readable name of the database. |
| TABLE_SCHEMA_ID | STRING NOT NULL | The ID of the database. |
| OPTION_KEY | STRING NOT NULL | Option key. |
| OPTION_VALUE | STRING NOT NULL | Option value. |
Examples
: Run the following code to query for a side view of TABLES for WITH.
```sql
SELECT *
FROM `INFORMATION_SCHEMA`.`TABLE_OPTIONS`;
```
### VIEWS
Contains the object-level metadata for views within the catalog.
The rows returned are limited to the schemas the user has permission to
interact with.
| Column Name | Data Type | Standard | Description |
|------------------|-----------------|------------------------------------|--------------------------------------------------------------------------------------------------------|
| COMMENT | STRING NULL | No (Like Databricks and Snowflake) | An optional comment that describes the relation. |
| TABLE_CATALOG | STRING NOT NULL | Yes | The human-readable name of the catalog. |
| TABLE_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog. |
| TABLE_NAME | STRING NOT NULL | Yes | The name of the relation. |
| TABLE_SCHEMA | STRING NOT NULL | Yes | The human-readable name of the database. |
| TABLE_SCHEMA_ID | STRING NOT NULL | No | The ID of the database. |
| VIEW_DEFINITION | STRING NULL | Yes | Text of the view’s expanded query expression. Like Databricks, NULL if the user does not own the view. |
## Functions
Confluent Cloud for Apache Flink supports a number of features for routines.
- overloading
- structured types, that is, Java POJOs
- var-args
- procedures and process table functions (PTFs)
- table, model arguments with traits for PTFs
- input type strategies and return type strategies
These special cases are considered in the INFORMATION_SCHEMA design:
- overloading: SPECIFIC_NAME with `_1`, `_2` behavior, similar to Databricks
- structured types, return type strategies: `FULL_DATA_TYPE` and
`DATA_TYPE = NULL`
- var-args, input type strategies: at least indicated with `IS_STATIC = NO`
- procedures and PTFs, table, model arguments with traits for PTFs: special
columns `TRAITS` and `DATA_TYPE = NULL`
### ROUTINES
Contains the object-level metadata for functions within the catalog.
| Column Name | Data Type | Standard | Description |
|-----------------------|---------------------------|------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| CREATED | TIMESTAMP_LTZ(9) NOT NULL | Yes | Creation time of the function. |
| DATA_TYPE | STRING NULL | Yes | Type root, for example, VARCHAR or ROW. NULL is not standard but is reserved for user-defined functions with type strategies instead of a static return type or procedures. Table-valued functions always return a ROW type: it is either an automatic wrapper in case of a non-row type or a ROW returned by the function. |
| EXTERNAL_ARTIFACTS | STRING NULL | No | Specifies the JAR artifact content. Set this field to `null` for internal artifacts. For external artifacts, use the format `confluent-artifact://`. For multiple artifacts, use a semicolon-separated list. The system derives JAR file information from the `EXTERNAL_LANGUAGE` field. |
| EXTERNAL_LANGUAGE | STRING NULL | Yes | `JAVA` or `PYTHON`. NULL if not external. |
| EXTERNAL_NAME | STRING NULL | Yes | Identifier in the external language. For example, for Java, it’s the fully qualified class path. NULL is for non-external functions, that is, functions implemented in SQL. Contains the content of the AS clause, for example, ‘’: CREATE FUNCTION … AS ‘’ |
| FULL_DATA_TYPE | STRING NULL | No | Fully qualified data type, for example, VARCHAR(32) or ROW<…>. NULL is not standard but reserved for user-defined functions with type strategies instead of a static return type or procedures. Table-valued functions always return a ROW type: it is either an automatic wrapper in case of a non-row type or a ROW returned by the function. |
| FUNCTION_KIND | STRING NULL | No | Defines a more specific function kind, corresponding to Flink’s FunctionKind. Values are `SCALAR`, `TABLE`, or `PROCESS TABLE`. Set-returning, or table-valued, functions have a FUNCTION_KIND of `TABLE`, so query for them with `WHERE FUNCTION_KIND = 'TABLE'`. Null is reserved for PROCEDURES. |
| FUNCTION_REQUIREMENTS | STRING NULL | No | Semicolon separated list of requirements for the routine. Corresponds to Flink’s FunctionRequirement. Values are: `OVER_WINDOW_ONLY`. Null if there are no calling requirements. |
| IS_DETERMINISTIC | STRING NOT NULL | Yes | `YES` or `NO`. |
| IS_DYNAMIC | STRING NOT NULL | No | `YES` or `NO`. Whether the signature has static arguments or uses a strategy. In the latter case, PARAMETERS doesn’t contain information about the given specific routine. |
| ROUTINE_BODY | STRING NOT NULL | Yes | `EXTERNAL` for Java and Python. Deviates from standard because PTFs are also `EXTERNAL`. |
| ROUTINE_CATALOG | STRING NOT NULL | Yes | Matches SPECIFIC_CATALOG. |
| ROUTINE_CATALOG_ID | STRING NOT NULL | No | Matches SPECIFIC_CATALOG_ID. |
| ROUTINE_NAME | STRING NOT NULL | Yes | Name of the routine. |
| ROUTINE_SCHEMA | STRING NOT NULL | Yes | Matches SPECIFIC_SCHEMA. |
| ROUTINE_SCHEMA_ID | STRING NOT NULL | No | Matches SPECIFIC_SCHEMA_ID. |
| ROUTINE_TYPE | STRING NOT NULL | Yes | Always `FUNCTION`, including for process table functions. To distinguish a process table function, use the FUNCTION_KIND column, whose value is `PROCESS TABLE`. |
| SPECIFIC_CATALOG | STRING NOT NULL | Yes | The human-readable name of the catalog. |
| SPECIFIC_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment. |
| SPECIFIC_NAME | STRING NOT NULL | Yes | Uniquely identifies a potentially overloaded routine signature. For example, a function `f` takes both `f(INT)` and `f(STRING)`. Each overload gets a specific name such as `f_1` or `f_2`. The specific name is not callable in SQL but is used for references by other INFORMATION_SCHEMA views such as PARAMETERS. |
| SPECIFIC_SCHEMA | STRING NOT NULL | Yes | The human-readable name of the database. |
| SPECIFIC_SCHEMA_ID | STRING NOT NULL | No | The ID of the database. |
### PARAMETERS
The parameters view supports only functions with static arguments.
| Column Name | Data Type | Standard | Description |
|---------------------|-----------------|------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| DATA_TYPE | STRING NULL | Yes | Type root, for example, VARCHAR or ROW. NULL is not standard but is reserved for PTFs with untyped table arguments. |
| FULL_DATA_TYPE | STRING NULL | No | Fully qualified data type, for example, VARCHAR(32) or ROW<…>. |
| IS_OPTIONAL | STRING NOT NULL | No | `YES` or `NO` whether this parameter is optional. |
| ORDINAL_POSITION | INT NOT NULL | Yes | Position (1-based) of the parameter in the signature. |
| PARAMETER_MODE | STRING NOT NULL | Yes | Always `IN`. Reserved for future use. |
| PARAMETER_NAME | STRING NOT NULL | Yes | Name of the parameter. |
| ROUTINE_NAME | STRING NOT NULL | No | Name of the routine. |
| SPECIFIC_CATALOG | STRING NOT NULL | Yes | The human-readable name of the catalog. |
| SPECIFIC_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment. |
| SPECIFIC_NAME | STRING NOT NULL | Yes | Uniquely identifies a potentially overloaded routine signature. For example, a function f takes both f(INT) and f(STRING). Then each overload gets a specific name such as: f_1 or f_2. The specific name is not callable in SQL but is used for references by other INFORMATION_SCHEMA views such as ROUTINES. |
| SPECIFIC_SCHEMA | STRING NOT NULL | Yes | The human-readable name of the database. |
| SPECIFIC_SCHEMA_ID | STRING NOT NULL | No | The ID of the database. |
| TRAITS | STRING NOT NULL | No | Semicolon-separated list of traits. By default, `SCALAR` only. |
## Models
### MODELS
Contains the object-level metadata for models within the catalog.
The rows returned are limited to the schemas that you have permission to
interact with.
| Column Name | Data Type | Standard | Description |
|------------------|-----------------|------------|-----------------------------------------------------|
| MODEL_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment. |
| MODEL_CATALOG | STRING NOT NULL | No | The human-readable name of the catalog/environment. |
| MODEL_SCHEMA_ID | STRING NOT NULL | No | The ID of the database/cluster. |
| MODEL_SCHEMA | STRING NOT NULL | No | The human-readable name of the database/cluster. |
| MODEL_NAME | STRING NOT NULL | No | The name of the model. |
| DEFAULT_VERSION | STRING NOT NULL | No | The default version of the model. |
| VERSION_COUNT | INT NOT NULL | No | The total number of versions for this model. |
| COMMENT | STRING NULL | No | An optional comment that describes the model. |
Example
: Run the following code to list all models within a catalog.
```sql
SELECT
`MODEL_NAME`,
`MODEL_SCHEMA`,
`DEFAULT_VERSION`,
`VERSION_COUNT`,
`COMMENT`
FROM `INFORMATION_SCHEMA`.`MODELS`
WHERE `MODEL_SCHEMA` = '';
```
### MODEL_OPTIONS
Side view of MODELS for model configuration options (WITH clause).
Extension to the SQL Standard Information Schema.
| Column Name | Data Type | Standard | Description |
|------------------|-----------------|------------|-----------------------------------------------------|
| MODEL_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment. |
| MODEL_CATALOG | STRING NOT NULL | No | The human-readable name of the catalog/environment. |
| MODEL_SCHEMA_ID | STRING NOT NULL | No | The ID of the database/cluster. |
| MODEL_SCHEMA | STRING NOT NULL | No | The human-readable name of the database/cluster. |
| MODEL_NAME | STRING NOT NULL | No | The name of the model. |
| OPTION_KEY | STRING NOT NULL | No | Option key. |
| OPTION_VALUE | STRING NOT NULL | No | Option value. |
| VERSION | STRING NOT NULL | No | The version of the model this option applies to. |
Example
: Run the following code to query for model configuration options.
```sql
SELECT
`MODEL_NAME`,
`VERSION`,
`OPTION_KEY`,
`OPTION_VALUE`
FROM `INFORMATION_SCHEMA`.`MODEL_OPTIONS`
WHERE `MODEL_NAME` = 'my_model';
```
### MODEL_COLUMNS
Contains the column-level metadata for model input and output schemas.
The rows returned are limited to the schemas that you have permission to
interact with.
| Column Name | Data Type | Standard | Description |
|------------------|-----------------|------------|----------------------------------------------------------------------------|
| MODEL_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment. |
| MODEL_CATALOG | STRING NOT NULL | No | The human-readable name of the catalog/environment. |
| MODEL_SCHEMA_ID | STRING NOT NULL | No | The ID of the database/cluster. |
| MODEL_SCHEMA | STRING NOT NULL | No | The human-readable name of the database/cluster. |
| MODEL_NAME | STRING NOT NULL | No | The name of the model. |
| COLUMN_NAME | STRING NOT NULL | No | The name of the column. |
| ORDINAL_POSITION | INT NOT NULL | No | The ordinal position of the column (starting at 1). |
| IS_NULLABLE | STRING NOT NULL | No | Indicates whether the column is nullable. Values are `YES` or `NO`. |
| DATA_TYPE | STRING NOT NULL | No | Type root, for example, VARCHAR or ROW. |
| FULL_DATA_TYPE | STRING NOT NULL | No | Fully qualified data type, for example, VARCHAR(32) or ROW<…>. |
| IS_INPUT | STRING NOT NULL | No | Indicates whether the column is an input column. Values are `YES` or `NO`. |
| COMMENT | STRING NULL | No | An optional comment that describes the column. |
Example
: Run the following code to list all columns for a model, showing input and
output columns separately.
```sql
SELECT
`COLUMN_NAME`,
`ORDINAL_POSITION`,
`IS_INPUT`,
`DATA_TYPE`,
`IS_NULLABLE`,
`COMMENT`
FROM `INFORMATION_SCHEMA`.`MODEL_COLUMNS`
WHERE `MODEL_NAME` = 'my_model'
ORDER BY `IS_INPUT` DESC, `ORDINAL_POSITION`;
```
## Connections
### CONNECTIONS
Contains the object-level metadata for connections within the catalog.
The rows returned are limited to the schemas that you have permission to
interact with.
| Column Name | Data Type | Standard | Description |
|-----------------------|-----------------|------------|-----------------------------------------------------|
| CONNECTION_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment. |
| CONNECTION_CATALOG | STRING NOT NULL | No | The human-readable name of the catalog/environment. |
| CONNECTION_SCHEMA_ID | STRING NOT NULL | No | The ID of the database/cluster. |
| CONNECTION_SCHEMA | STRING NOT NULL | No | The human-readable name of the database/cluster. |
| CONNECTION_NAME | STRING NOT NULL | No | The name of the connection. |
| COMMENT | STRING NULL | No | An optional comment that describes the connection. |
Example
: Run the following code to list all connections within a catalog.
```sql
SELECT
`CONNECTION_NAME`,
`CONNECTION_SCHEMA`,
`COMMENT`
FROM `INFORMATION_SCHEMA`.`CONNECTIONS`
WHERE `CONNECTION_SCHEMA` = '';
```
### CONNECTION_OPTIONS
Side view of CONNECTIONS for connection configuration options (WITH clause).
Extension to the SQL Standard Information Schema.
#### NOTE
Connection options containing sensitive credentials are always masked as
`******` for security purposes.
| Column Name | Data Type | Standard | Description |
|-----------------------|-----------------|------------|--------------------------------------------------------|
| CONNECTION_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment. |
| CONNECTION_CATALOG | STRING NOT NULL | No | The human-readable name of the catalog/environment. |
| CONNECTION_SCHEMA_ID | STRING NOT NULL | No | The ID of the database/cluster. |
| CONNECTION_SCHEMA | STRING NOT NULL | No | The human-readable name of the database/cluster. |
| CONNECTION_NAME | STRING NOT NULL | No | The name of the connection. |
| OPTION_KEY | STRING NOT NULL | No | Option key. |
| OPTION_VALUE | STRING NOT NULL | No | Option value. Sensitive values are masked as `******`. |
Example
: Run the following code to query for connection configuration options.
```sql
SELECT
`CONNECTION_NAME`,
`OPTION_KEY`,
`OPTION_VALUE`
FROM `INFORMATION_SCHEMA`.`CONNECTION_OPTIONS`
WHERE `CONNECTION_NAME` = 'my_connection';
```
## Tools
### TOOLS
Contains the object-level metadata for tools within the catalog.
The rows returned are limited to the schemas that you have permission to
interact with.
| Column Name | Data Type | Standard | Description |
|-----------------------|-----------------|------------|-------------------------------------------------------------------------------------------|
| TOOL_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment. |
| TOOL_CATALOG | STRING NOT NULL | No | The human-readable name of the catalog/environment. |
| TOOL_SCHEMA_ID | STRING NOT NULL | No | The ID of the database/cluster. |
| TOOL_SCHEMA | STRING NOT NULL | No | The human-readable name of the database/cluster. |
| TOOL_NAME | STRING NOT NULL | No | The name of the tool. |
| FUNCTION_CATALOG_ID | STRING NULL | No | The ID of the catalog containing the referenced function (for function-based tools). |
| FUNCTION_SCHEMA_ID | STRING NULL | No | The ID of the database containing the referenced function (for function-based tools). |
| FUNCTION_NAME | STRING NULL | No | The name of the referenced function (for function-based tools). |
| CONNECTION_CATALOG_ID | STRING NULL | No | The ID of the catalog containing the referenced connection (for connection-based tools). |
| CONNECTION_SCHEMA_ID | STRING NULL | No | The ID of the database containing the referenced connection (for connection-based tools). |
| CONNECTION_NAME | STRING NULL | No | The name of the referenced connection (for connection-based tools). |
| COMMENT | STRING NULL | No | An optional comment that describes the tool. |
Example
: Run the following code to list all tools within a catalog.
```sql
SELECT
`TOOL_NAME`,
`TOOL_SCHEMA`,
`FUNCTION_NAME`,
`CONNECTION_NAME`,
`COMMENT`
FROM `INFORMATION_SCHEMA`.`TOOLS`
WHERE `TOOL_SCHEMA` = '';
```
### TOOL_OPTIONS
Side view of TOOLS for tool configuration options (WITH clause).
Extension to the SQL Standard Information Schema.
| Column Name | Data Type | Standard | Description |
|-----------------|-----------------|------------|-----------------------------------------------------|
| TOOL_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment. |
| TOOL_CATALOG | STRING NOT NULL | No | The human-readable name of the catalog/environment. |
| TOOL_SCHEMA_ID | STRING NOT NULL | No | The ID of the database/cluster. |
| TOOL_SCHEMA | STRING NOT NULL | No | The human-readable name of the database/cluster. |
| TOOL_NAME | STRING NOT NULL | No | The name of the tool. |
| OPTION_KEY | STRING NOT NULL | No | Option key. |
| OPTION_VALUE | STRING NOT NULL | No | Option value. |
Example
: Run the following code to query for tool configuration options.
```sql
SELECT
`TOOL_NAME`,
`OPTION_KEY`,
`OPTION_VALUE`
FROM `INFORMATION_SCHEMA`.`TOOL_OPTIONS`
WHERE `TOOL_NAME` = 'my_tool';
```
## Agents
### AGENTS
Contains the object-level metadata for agents within the catalog.
The rows returned are limited to the schemas that you have permission to
interact with.
| Column Name | Data Type | Standard | Description |
|------------------|-----------------|------------|---------------------------------------------------------|
| AGENT_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment. |
| AGENT_CATALOG | STRING NOT NULL | No | The human-readable name of the catalog/environment. |
| AGENT_SCHEMA_ID | STRING NOT NULL | No | The ID of the database/cluster. |
| AGENT_SCHEMA | STRING NOT NULL | No | The human-readable name of the database/cluster. |
| AGENT_NAME | STRING NOT NULL | No | The name of the agent. |
| MODEL_CATALOG_ID | STRING NULL | No | The ID of the catalog containing the referenced model. |
| MODEL_SCHEMA_ID | STRING NULL | No | The ID of the database containing the referenced model. |
| MODEL_NAME | STRING NULL | No | The name of the referenced model. |
| PROMPT | STRING NOT NULL | No | The system prompt for the agent. |
| COMMENT | STRING NULL | No | An optional comment that describes the agent. |
Example
: Run the following code to list all agents within a catalog.
```sql
SELECT
`AGENT_NAME`,
`AGENT_SCHEMA`,
`MODEL_NAME`,
`PROMPT`,
`COMMENT`
FROM `INFORMATION_SCHEMA`.`AGENTS`
WHERE `AGENT_SCHEMA` = '';
```
### AGENT_OPTIONS
Side view of AGENTS for agent configuration options (WITH clause).
Extension to the SQL Standard Information Schema.
| Column Name | Data Type | Standard | Description |
|------------------|-----------------|------------|-----------------------------------------------------|
| AGENT_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment. |
| AGENT_CATALOG | STRING NOT NULL | No | The human-readable name of the catalog/environment. |
| AGENT_SCHEMA_ID | STRING NOT NULL | No | The ID of the database/cluster. |
| AGENT_SCHEMA | STRING NOT NULL | No | The human-readable name of the database/cluster. |
| AGENT_NAME | STRING NOT NULL | No | The name of the agent. |
| OPTION_KEY | STRING NOT NULL | No | Option key. |
| OPTION_VALUE | STRING NOT NULL | No | Option value. |
Example
: Run the following code to query for agent configuration options.
```sql
SELECT
`AGENT_NAME`,
`OPTION_KEY`,
`OPTION_VALUE`
FROM `INFORMATION_SCHEMA`.`AGENT_OPTIONS`
WHERE `AGENT_NAME` = 'my_agent';
```
### AGENT_TOOLS
Contains the relationship between agents and their associated tools, including
the ordinal position of each tool.
The rows returned are limited to the schemas that you have permission to
interact with.
| Column Name | Data Type | Standard | Description |
|------------------|-----------------|------------|----------------------------------------------------------------------------|
| AGENT_CATALOG_ID | STRING NOT NULL | No | The ID of the catalog/environment. |
| AGENT_CATALOG | STRING NOT NULL | No | The human-readable name of the catalog/environment. |
| AGENT_SCHEMA_ID | STRING NOT NULL | No | The ID of the database/cluster. |
| AGENT_SCHEMA | STRING NOT NULL | No | The human-readable name of the database/cluster. |
| AGENT_NAME | STRING NOT NULL | No | The name of the agent. |
| TOOL_CATALOG_ID | STRING NULL | No | The ID of the catalog containing the referenced tool. |
| TOOL_SCHEMA_ID | STRING NULL | No | The ID of the database containing the referenced tool. |
| TOOL_NAME | STRING NULL | No | The name of the referenced tool. |
| ORDINAL_POSITION | INT NOT NULL | No | The ordinal position of the tool in the agent’s tool list (starting at 1). |
Example
: Run the following code to list all tools associated with an agent, ordered
by their position.
```sql
SELECT
`AGENT_NAME`,
`TOOL_NAME`,
`ORDINAL_POSITION`
FROM `INFORMATION_SCHEMA`.`AGENT_TOOLS`
WHERE `AGENT_NAME` = 'my_agent'
ORDER BY `ORDINAL_POSITION`;
```
## System tables
System tables provide access to filtered data streams without creating
additional physical storage. They are virtual views on existing Kafka topics.
### `$late` system tables
When a table is configured with `late-handling.mode = 'filter'`, a system
table with the `$late` suffix becomes available. This table contains events
that arrived after the watermark had advanced past their event timestamp.
Naming convention
: For a table named `Orders`, the system table is `Orders$late` (note the
`$` prefix before `late`).
Schema
: System tables inherit the complete schema from their source table, including:
- All columns with their data types
- Metadata columns (if defined)
- Computed columns are NOT included (they are re-evaluated on query)
Querying
: System tables support standard SQL operations:
```sql
-- View late data
SELECT * FROM `Orders$late`;
-- Merge with main stream for reconciliation
SELECT * FROM Orders
UNION ALL
SELECT * FROM `Orders$late`;
-- Persist late data for extended retention
INSERT INTO OrdersLateArchive
SELECT * FROM `Orders$late`;
```
#### NOTE
- System tables do not create new Kafka topics or physical storage.
- Data retention in `$late` tables follows the source topic’s retention
policy.
- System tables are only available when `late-handling.mode = 'filter'`.
- Use backticks when querying tables with `$` in the name.
For configuration details, see [Handle Late-Arriving Data](../how-to-guides/handle-late-arriving-data.md#handle-late-arriving-data).
## Related content
- [View Schema Metadata in the Catalog Browser](../how-to-guides/catalog-browser.md#flink-sql-catalog-browser)
- [Data Types](datatypes.md#flink-sql-datatypes)
- [Queries](queries/overview.md#flink-sql-queries)
- [Reserved Keywords](keywords.md#flink-sql-keywords)
- [Statements](statements/overview.md#flink-sql-statements-overview)
#### NOTE
This website includes content developed at the [Apache Software Foundation](https://www.apache.org/)
under the terms of the [Apache License v2](https://www.apache.org/licenses/LICENSE-2.0.html).