information_schema improvements

What type of enhancement is this?

API improvement, Configuration, User experience, Other

What does the enhancement do?

Information Schema provides an ANSI-standard way of viewing system metadata. But we only have two internal tables: tables and columns, that's not enough for compatibility or such as query functions in #2921 .

TiDB has a great doc that describes its information schema: https://docs.pingcap.com/tidb/stable/information-schema

Tasks

So I created this tracking issue to add more tables into information_schema and welcome all developers who are interested in GreptimeDB to contribute:

• CHARACTER_SETS, we only supports utf-8, so we can just return one row.
• CHECK_CONSTRAINTS, we don't support check constrais.
• COLLATIONS and COLLATION_CHARACTER_SET_APPLICABILITY, we don't support them yet
• COLUMN_PRIVILEGES, not implemented.
• COLUMN_STATISTICS, not implemented.
• ENGINES, we have mito, file engines etc.
• EVENTS not implemented.
• FILES not implemented. https://github.com/GreptimeTeam/greptimedb/pull/3054
• GLOBAL_STATUS not implemented.
• GLOBAL_VARIABLES not implemented. An idea is that we keep all configurations in this table.
• KEY_COLUMN_USAGE, need to implement it. https://github.com/GreptimeTeam/greptimedb/pull/3057
• OPTIMIZER_TRACE not implemented.
• PARAMETERS not implemented.
• PARTITIONS, we have the partitions info. https://github.com/GreptimeTeam/greptimedb/pull/3278
• PROFILING not implemented.
• REFERENTIAL_CONSTRAINTS, we don't support it, returns zero rows instead.
• ROUTINES not implemented.
• SCHEMATA, need to implement it https://github.com/GreptimeTeam/greptimedb/pull/3051
• SCHEMA_PRIVILEGES not implemented.
• SESSION_STATUS not implemented.
• SESSION_VARIABLES
• STATISTICS, https://github.com/GreptimeTeam/greptimedb/pull/8253
• TABLESPACES not implemented.
• TABLE_CONSTRAINTS, we need it https://github.com/GreptimeTeam/greptimedb/pull/3698
• TABLE_PRIVILEGES not implemented.
• TRIGGERS, we don't support it.
• USER_ATTRIBUTES
• USER_PRIVILEGES
• VARIABLES_INFO, it's better to have it
• VIEWS https://github.com/GreptimeTeam/greptimedb/pull/4342/files, it can be implemented

All the tables that are not implemented, we can just return zero rows.

Of course, we will have some extension tables:

• cluster_info to provide the current cluster info, such as topology etc. https://github.com/GreptimeTeam/greptimedb/pull/3832
• build_info to provide the database build info. #2909
• slow_query to record all slow queries.
• region_peers to provide region route info. https://github.com/GreptimeTeam/greptimedb/pull/3278
• runtime_metrics to retrieve the greptimedb runtime metrics https://github.com/GreptimeTeam/greptimedb/pull/3127

The following tasks:

• Docs to describe all these tables
• Adds docs to database administrator document.

Reference info

Someone who are interested in contributing can refer to these PRs:
https://github.com/GreptimeTeam/greptimedb/pull/2969 https://github.com/GreptimeTeam/greptimedb/pull/2935

Implementation challenges

No response