[Go] Feature Request: Comprehensive Metadata Discovery for Data Development Tools

[mullinsms]

Summary

The ADBC Trino driver currently supports discovery of catalogs, schemas, tables, views, and columns via information_schema. However, Trino exposes rich metadata through SHOW commands and system.metadata.* tables that data development tools need — functions, materialized views, DDL, table properties, statistics, and table comments. This issue requests expanding metadata discovery to cover these gaps.

We build a collaborative data operations platform and currently query Trino using SHOW commands, information_schema, and system.metadata tables for metadata extraction. We are evaluating the ADBC driver as a modern Arrow-native replacement but cannot adopt it without coverage for the object categories listed below.

Each section describes what we need, why it matters, what metadata properties are required, and the exact Trino queries to obtain it.

---

Table of Contents

1. Functions (Scalar, Aggregate, Window)
2. Function Arguments
3. Materialized Views
4. DDL / CREATE Statement Retrieval
5. Table Properties
6. Table Statistics
7. Table & Column Comments
8. Partition Information
9. Connector Metadata
10. System Object Distinction

---

1. Functions (Scalar, Aggregate, Window)

Motivation

Trino provides hundreds of built-in functions and connectors can add their own. Functions are essential for SQL authoring — tools need to enumerate them for autocomplete, signature help, inline documentation, and catalog browsing.

Required Metadata

Property	Description	Source Column
Function name	Function identifier	Function Name
Return type	Return data type	Return Type
Argument types	Ordered list of input types	Argument Types
Function type	scalar, aggregate, or window	Function Type
Deterministic	Whether the function is deterministic	Deterministic
Description	What the function does	Description

How to Obtain

SHOW FUNCTIONS;

Returns columns: Function Name, Return Type, Argument Types, Function Type, Deterministic, Description.

Example rows:

abs          | bigint   | bigint           | scalar    | true  | Absolute value
array_agg    | array(T) | T                | aggregate | true  | ...
row_number   | bigint   |                  | window    | true  | ...

Overload Handling

Trino functions can have multiple overloads (same name, different argument types). Each overload appears as a separate row in SHOW FUNCTIONS. The driver should:

1. Group results by function name
2. Expose each overload with its distinct signature
3. Use (name, argument_types) as the unique identifier

Suggested Approach

Functions could be exposed as:

1. A new method GetFunctions() returning an Arrow RecordBatch
2. An extension to GetObjects at a new depth level alongside tables
3. A sibling list to db_schema_tables in the schema struct (e.g., db_schema_functions)

Since Trino functions are global (not scoped to catalog/schema), they would sit at the connection level.

---

2. Function Arguments

Motivation

Function arguments with names and types are needed for signature help and call template generation. SHOW FUNCTIONS provides argument types as a comma-separated string but not argument names.

Required Metadata

Property	Description
Function name	Parent function
Argument position	1-based ordinal
Argument type	Trino data type
Argument name	Parameter name (if available)

How to Obtain

SHOW FUNCTIONS provides Argument Types as a comma-separated string:

varchar, varchar    → two varchar arguments
T, T                → two generic type arguments
                    → no arguments (empty string)

For built-in functions, argument names are not available from metadata queries. However, Trino's system.metadata tables or function documentation may provide names in future versions.

Current Practical Approach

Parse the Argument Types string from SHOW FUNCTIONS:

"bigint, varchar" → [{position: 1, type: "bigint"}, {position: 2, type: "varchar"}]

---

3. Materialized Views

Motivation

Materialized views are a distinct object type in Trino, supported by connectors like Iceberg and Hive. They have refresh semantics and backing storage that differ from regular views. Tools need to distinguish them from views and display their properties.

Current Gap

The driver queries information_schema.tables which returns materialized views as table_type = 'VIEW'. There is no way to distinguish them from regular views in GetObjects results.

Required Metadata

Property	Description	Source Column
Catalog name	Containing catalog	catalog_name
Schema name	Containing schema	schema_name
Name	Materialized view name	name
Definition	The defining SQL query	definition
Comment	User-provided description	comment
Freshness	Whether the MV is fresh or stale	is_fresh (if available)
Storage catalog	Where the backing table lives	storage_catalog
Storage schema	Backing table schema	storage_schema
Storage table	Backing table name	storage_table

How to Obtain

SELECT
    catalog_name,
    schema_name,
    name,
    definition,
    comment,
    storage_catalog,
    storage_schema,
    storage_table
FROM system.metadata.materialized_views
WHERE catalog_name = ?
    AND schema_name = ?;

Suggested Approach

Either:

1. Add "MATERIALIZED VIEW" to GetTableTypes() results and distinguish MVs using the system.metadata.materialized_views table (similar to what we do — LEFT JOIN on the table query)
2. Extend TABLE_SCHEMA with an optional table_definition field for views/MVs

---

4. DDL / CREATE Statement Retrieval

Motivation

DDL captures the complete definition of a Trino object including connector-specific properties (storage format, partitioning, bucketing, location, etc.). It is essential for migration, documentation, and understanding object configuration.

Required Metadata

Property	Description
Object name	Fully qualified object name
Object type	TABLE, VIEW, or MATERIALIZED VIEW
DDL text	Complete CREATE statement

How to Obtain

-- Tables
SHOW CREATE TABLE "catalog"."schema"."table";

-- Views
SHOW CREATE VIEW "catalog"."schema"."view";

-- Materialized Views
SHOW CREATE MATERIALIZED VIEW "catalog"."schema"."mv";

Each returns a single column Create Table / Create View / Create Materialized View containing the full DDL.

Example DDL Output

CREATE TABLE iceberg.db.orders (
   order_id bigint,
   customer_id bigint,
   order_date date,
   total decimal(10, 2)
)
WITH (
   format = 'PARQUET',
   partitioning = ARRAY['order_date']
)

This reveals connector-specific properties (format, partitioning) that aren't available through other metadata queries.

Suggested Approach

Add an optional table_ddl or table_definition field to TABLE_SCHEMA in GetObjects, or provide a separate GetObjectDDL(catalog, schema, name) method.

---

5. Table Properties

Motivation

Trino tables have connector-specific properties that define storage format, partitioning, bucketing, sorting, location, and more. These are critical for understanding table configuration and for creating similar tables.

Required Metadata

Table properties vary by connector but commonly include:

Property	Description	Connectors
format	Storage format (ORC, PARQUET, AVRO, etc.)	Hive, Iceberg, Delta
partitioning / partitioned_by	Partition columns/transforms	Hive, Iceberg, Delta
bucketed_by / bucket_count	Bucketing configuration	Hive
sorted_by	Sort order within buckets	Hive
location	External storage path	Hive, Iceberg, Delta
external_location	External table location	Hive
orc_bloom_filter_columns	ORC bloom filter columns	Hive
format_version	Table format version	Iceberg
partitioning	Iceberg partition transforms	Iceberg
sorted_by	Iceberg sort order	Iceberg

How to Obtain

Available properties for a connector:

SELECT * FROM system.metadata.table_properties
WHERE catalog_name = ?;

Returns: catalog_name, property_name, default_value, type, description.

Actual property values for a table are embedded in DDL output from SHOW CREATE TABLE or accessible via connector-specific system tables.

Suggested Approach

Include table properties as a key-value map in TABLE_SCHEMA, or provide them via DDL retrieval (section 4).

---

6. Table Statistics

Motivation

Trino's cost-based optimizer relies on table and column statistics. Tools need statistics for displaying table sizes, row counts, and for query planning assistance.

Required Metadata

Statistic	Description
Row count	Estimated number of rows
Data size	Estimated data size in bytes
Column NDV	Number of distinct values per column
Column null fraction	Fraction of null values per column
Column min/max	Min and max values per column
Column avg size	Average column value size in bytes

How to Obtain

SHOW STATS FOR "catalog"."schema"."table";

Returns columns: column_name, data_size, distinct_values_count, nulls_fraction, row_count, low_value, high_value.

• Table-level row: column_name = NULL, row_count populated
• Column-level rows: column_name populated, per-column statistics

Example:

column_name | data_size | distinct_values_count | nulls_fraction | row_count | low_value | high_value
NULL        | NULL      | NULL                  | NULL           | 1000000   | NULL      | NULL
order_id    | NULL      | 1000000               | 0.0            | NULL      | 1         | 1000000
order_date  | NULL      | 365                   | 0.01           | NULL      | 2024-01-01| 2024-12-31

ADBC Spec Alignment

Maps directly to the ADBC GetStatistics() API:

• row_count → StatisticRowCountKey (6)
• distinct_values_count → StatisticDistinctCountKey (1)
• nulls_fraction × row_count → StatisticNullCountKey (5)
• low_value → StatisticMinValueKey (4)
• high_value → StatisticMaxValueKey (3)
• data_size → StatisticAverageByteWidthKey (0) or StatisticMaxByteWidthKey (2)

Suggested Approach

Implement GetStatistics() using SHOW STATS FOR for each table matching the filter pattern.

---

7. Table & Column Comments

Motivation

Comments provide human-readable descriptions of tables and columns. They are set via COMMENT ON TABLE and COMMENT ON COLUMN in Trino and are important for documentation and data discovery.

Current Gap

The ADBC driver already extracts column comments from information_schema.columns.comment into the remarks field — this is good. However, table-level comments are not extracted.

Required Metadata

Property	Description	Source
Table comment	Table-level description	system.metadata.table_comments

How to Obtain

SELECT
    catalog_name,
    schema_name,
    table_name,
    comment
FROM system.metadata.table_comments
WHERE catalog_name = ?
    AND schema_name = ?;

Suggested Approach

Add an optional table_comment or remarks field to TABLE_SCHEMA in GetObjects, populated from system.metadata.table_comments.

---

8. Partition Information

Motivation

Many Trino connectors (Hive, Iceberg, Delta) support partitioned tables. Users need to know:

• Which columns a table is partitioned by
• What partition values exist
• Partition-level statistics

Required Metadata

Partition Columns

Property	Description
Column name	Partition column name
Transform	Partition transform (e.g., identity, year, month, day, bucket, truncate) — Iceberg-specific

Partition Values

Property	Description
Partition spec	Key-value pairs of partition column values
Row count	Rows in the partition (if available from stats)

How to Obtain

Partition columns — best obtained from DDL:

SHOW CREATE TABLE "catalog"."schema"."table";
-- Contains: partitioned_by = ARRAY['col1', 'col2'] (Hive)
-- Or: partitioning = ARRAY['year(order_date)'] (Iceberg)

Partition values — connector-specific:

-- Hive connector
SELECT * FROM "catalog"."schema"."table$partitions";

-- Iceberg connector
SELECT * FROM "catalog"."schema"."table$partitions";

Note: The $partitions metadata table syntax is connector-specific and may not be available for all connectors.

---

9. Connector Metadata

Motivation

Trino catalogs are backed by connectors (Hive, Iceberg, Delta Lake, PostgreSQL, MySQL, Kafka, Elasticsearch, etc.). Knowing which connector backs a catalog helps tools adapt their behavior — e.g., offering partition management for Hive but not for PostgreSQL pass-through.

Required Metadata

Property	Description	Source
Catalog name	Catalog identifier	system.metadata.catalogs.catalog_name
Connector ID	Connector type identifier	system.metadata.catalogs.connector_id
Connector name	Human-readable connector name	system.metadata.catalogs.connector_name

How to Obtain

SELECT catalog_name, connector_id, connector_name
FROM system.metadata.catalogs;

Example:

catalog_name | connector_id | connector_name
hive_prod    | hive         | hive
iceberg_dw   | iceberg      | iceberg
pg_analytics | postgresql   | postgresql

Suggested Approach

Include connector_id or connector_name as metadata on the catalog level in GetObjects, or provide it via GetInfo().

---

10. System Object Distinction

Motivation

Trino has system schemas (information_schema) and system tables within the system catalog. Tools should distinguish system objects from user objects for cleaner catalog browsing.

Current Gap

The driver filters out schemas matching pg_* and information_schema (PostgreSQL conventions), but Trino's system schemas are different:

• Each catalog has an information_schema schema
• The system catalog contains runtime and metadata schemas

Required Behavior

Schema/Catalog	Should Be	Current Behavior
{catalog}.information_schema	System schema	May be filtered or not
system.metadata	System schema	Treated as regular
system.runtime	System schema	Treated as regular
system.jdbc	System schema	Treated as regular
User schemas	Regular schema	Correct

Suggested Approach

Either:

1. Add a is_system boolean to the schema struct
2. Provide a mechanism for callers to filter system schemas
3. Follow the Trino convention where the system catalog and information_schema schemas are treated as system objects

---

Summary Priority Table

#	Category	Trino Source	Impact
1	Functions (scalar/aggregate/window)	SHOW FUNCTIONS	High — autocomplete, signature help
2	Function arguments	SHOW FUNCTIONS argument types column	High — signature help
3	Materialized views	system.metadata.materialized_views	High — distinct object type
4	DDL retrieval	SHOW CREATE TABLE/VIEW/MATERIALIZED VIEW	High — migration, documentation
5	Table properties	system.metadata.table_properties + DDL	Medium — connector-specific config
6	Table statistics	SHOW STATS FOR {table}	Medium — query planning, cost display
7	Table & column comments	system.metadata.table_comments	Medium — data discovery
8	Partition information	DDL + $partitions metadata tables	Medium — connector-dependent
9	Connector metadata	system.metadata.catalogs (connector_id)	Low — adaptive tool behavior
10	System object distinction	Schema naming conventions	Low — cleaner browsing

We're happy to provide further details on any of these categories.

[lidavidm]

Thanks! Everything here has been turned into a feature request (see issues mentioning this issue above)