information_schema improvements

[killme2008]

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. feat: add information_schema.files #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. feat: add information_schema.key_column_usage #3057
• OPTIMIZER_TRACE not implemented.
• PARAMETERS not implemented.
• PARTITIONS, we have the partitions info. feat: impl partitions and region_peers information schema #3278
• PROFILING not implemented.
• REFERENTIAL_CONSTRAINTS, we don't support it, returns zero rows instead.
• ROUTINES not implemented.
• SCHEMATA, need to implement it feat: adds information_schema.schemata #3051
• SCHEMA_PRIVILEGES not implemented.
• SESSION_STATUS not implemented.
• SESSION_VARIABLES
• STATISTICS, we need it
• TABLESPACES not implemented.
• TABLE_CONSTRAINTS, we need it feat: impl table_constraints table for information_schema #3698
• TABLE_PRIVILEGES not implemented.
• TRIGGERS, we don't support it.
• USER_ATTRIBUTES
• USER_PRIVILEGES
• VARIABLES_INFO, it's better to have it
• VIEWS, 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.
• build_info to provide the database build info. build() function to return the database build info #2909
• slow_query to record all slow queries.
• greptime_region_peers to provide region route info. feat: impl partitions and region_peers information schema #3278
• runtime_metrics to retrieve the greptimedb runtime metrics feat: adds runtime_metrics #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:
#2969
#2935

Implementation challenges

No response