Add comment columns to information_schema.tables

[hovaesco]

Currently, there is no way to get comments from tables and columns tables from information_schema. For instance, Snowflake exposes these comment columns in respective tables in information_schema. It would be very useful for integrations like dbt which requires it to fully support persisting docs functionality.

[hashhar]

cc: @martint

We do expose this under system.jdbc.tables it seems. Not sure why there is a difference.

[nineinchnick]

There are no comments in system.jdbc.tables: https://github.com/trinodb/trino/blob/master/core/trino-main/src/main/java/io/trino/connector/system/jdbc/TableJdbcTable.java#L123
There are comments for columns though, as they're read from the metadata: https://github.com/trinodb/trino/blob/master/core/trino-main/src/main/java/io/trino/connector/system/jdbc/ColumnJdbcTable.java#L263

[findepi]

Column comments are available

trino/core/trino-main/src/main/java/io/trino/connector/informationschema/InformationSchemaTable.java

Line 41 in 03d8e5a

.hiddenColumn("comment", createUnboundedVarcharType()) // non-standard

trino/core/trino-main/src/main/java/io/trino/connector/informationschema/InformationSchemaPageSource.java

Line 268 in 7f954b5

column.getComment(),

Table comments (remarks) are not available in information_schema.tables.
Updating issue description to reflect this.

[findepi]

The reason why information_schema.tables has table_comment column is some tools compatibility

trino/core/trino-main/src/main/java/io/trino/connector/informationschema/InformationSchemaTable.java

Line 50 in 03d8e5a

.hiddenColumn("table_comment", createUnboundedVarcharType()) // MySQL compatible

It would be very expensive to populate this fields, hence it's not populated (all NULLs).

That's why we have them in a separate table system.metadata.table_comments.

It would be very useful for integrations like dbt which requires it to fully support persisting docs functionality.

What's the use-case which requires dbt to list tables along with the comments?
Can it join to system.metadata.table_comments to get them?
Note that in general this is going to be prohibitively expensive, so this should be done for one table (or at most for a handful of tables) at a time.

[martint]

We do expose this under system.jdbc.tables it seems. Not sure why there is a difference.

The schema of information_schema tables and views is defined by the SQL specification. For extensions, we have dedicated tables under the system catalog. system.jdbc.tables, in particular, is designed to expose all the information needed by the JDBC driver.

[hovaesco]

Thanks for pointing out on hidden comment columns from information_schema.columns.

Use case is a functionality called persist_docs which saves table-level and column-level comments which are then used in dbt docs.

Technically speaking, it's feasible to join system.metadata.table_comments with information_schema.tables. However, I'm afraid about backwards compatibility since system catalog could require additional privileges. A query to get all details to generate docs currently uses only information_schema.

From the description above it looks like that it's going to be a costly operation but the best solution from dbt-trino perspective would be to populate a table_comment column from information_schema.tables.

[findepi]

populate a table_comment column from information_schema.tables.

This is no-go from perf perspective.

For example, listing tables in Hive is O(1) operation (counting the HMS calls, likely the dominant factor).
Listing tables with comments is a O(n) operation (where n is number of tables).
In the past we've seen that O(n) for information_schema.tables is prohibitively expensive.

[hovaesco]

Thanks for detailed explanation. I'm good to close the issue.