morningman opened a new issue, #65479: URL: https://github.com/apache/doris/issues/65479
## Background Doris has many `SHOW` statements and several metadata TVFs. Recent PRs such as #63860, #60209, #63635, and #63864 show that metadata output is still maintained through many independent SHOW paths. This makes filtering, sorting, joining, permission handling, and compatibility harder than necessary. This tracking issue aims to expose most Doris metadata currently returned by `SHOW` statements, and selected Doris metadata TVFs, as queryable tables under `information_schema`. Users should be able to query these metadata tables with normal SQL, for example with arbitrary `WHERE`, `ORDER BY`, `JOIN`, projection, and aggregation. The existing `SHOW` syntax can remain for compatibility, but should eventually be implemented by querying or sharing the same provider as the corresponding `information_schema` table. ## Goals - Add `information_schema` tables for Doris metadata currently exposed only through `SHOW`. - Add `information_schema` tables for Doris metadata TVFs such as `frontends()`, `backends()`, and `partitions()`. - Keep column definitions, privileges, and filtering behavior consistent between `SHOW`, TVF, and `information_schema`. - Prefer one shared metadata provider per metadata object, instead of duplicating logic in each command. - Add regression tests for both direct `information_schema` queries and compatibility with existing `SHOW` statements. ## Non-goals - Do not replace external data TVFs such as `s3()`, `hdfs()`, `local()`, `file()`, `http()`, or `cdc_stream()`. - Do not include file/lake-format metadata TVFs such as `parquet_meta()` or `hudi_meta()` in this first pass unless we explicitly decide to track external metadata in `information_schema`. - Do not remove existing `SHOW` syntax in this tracking issue. ## Acceptance criteria for each item - A stable `information_schema` table name and schema are defined. - Authorization matches the existing `SHOW` or TVF behavior. - Existing `SHOW` behavior remains compatible. - Regression tests cover normal query, filtering, ordering where useful, and privilege behavior where applicable. - Documentation is updated if the metadata table is user-visible. ## SHOW statements to migrate or back with information_schema ### Core metadata and schema objects - [ ] `showDatabases`: `SHOW DATABASES` / `SHOW SCHEMAS` - [ ] `showTables`: `SHOW TABLES` - [ ] `showViews`: `SHOW VIEWS` - [ ] `showColumns`: `SHOW COLUMNS` / `SHOW FIELDS` - [ ] `showTableStatus`: `SHOW TABLE STATUS` - [ ] `showCreateTable`: `SHOW CREATE TABLE` - [ ] `showCreateView`: `SHOW CREATE VIEW` - [ ] `showCreateDatabase`: `SHOW CREATE DATABASE` / `SHOW CREATE SCHEMA` - [ ] `showCreateMTMV`: `SHOW CREATE MATERIALIZED VIEW <mv>` - [ ] `showCreateMaterializedView`: `SHOW CREATE MATERIALIZED VIEW <mv> ON <table>` - [ ] `showView`: `SHOW VIEW FROM <table>` - [ ] `showIndex`: `SHOW INDEX` / `SHOW KEYS` - [ ] `showConstraint`: `SHOW CONSTRAINTS` - [ ] `showPartitions`: `SHOW PARTITIONS` - [ ] `showPartitionId`: `SHOW PARTITION <id>` - [ ] `showTableId`: `SHOW TABLE <id>` - [ ] `showDatabaseId`: `SHOW DATABASE <id>` - [ ] `showTableCreation`: `SHOW TABLE CREATION` - [ ] `showDictionaries`: `SHOW DICTIONARIES` - [ ] `showDataTypes`: `SHOW DATA TYPES` - [ ] `showData`: `SHOW DATA` - [ ] `showDataSkew`: `SHOW DATA SKEW` ### Cluster, node, storage, and resource metadata - [ ] `showFrontends`: `SHOW FRONTENDS` - [ ] `showBackends`: `SHOW BACKENDS` - [ ] `showBroker`: `SHOW BROKER` - [ ] `showConfig`: `SHOW FRONTEND/BACKEND CONFIG` - [ ] `showClusters`: `SHOW CLUSTERS` / `SHOW COMPUTE GROUPS` - [ ] `showStorageVault`: `SHOW STORAGE VAULT(S)` - [ ] `showCreateStorageVault`: `SHOW CREATE STORAGE VAULT` - [ ] `showStoragePolicy`: `SHOW STORAGE POLICY` - [ ] `showStorageEngines`: `SHOW STORAGE ENGINES` - [ ] `showResources`: `SHOW RESOURCES` - [ ] `showTrash`: `SHOW TRASH` - [ ] `showTabletStorageFormat`: `SHOW TABLET STORAGE FORMAT` - [ ] `adminShowTabletStorageFormat`: `ADMIN SHOW TABLET STORAGE FORMAT` - [ ] `showCatalogRecycleBin`: `SHOW CATALOG RECYCLE BIN` - [ ] `showCatalogs`: `SHOW CATALOGS` - [ ] `showCatalog`: `SHOW CATALOG` - [ ] `showCreateCatalog`: `SHOW CREATE CATALOG` - [ ] `showConvertLsc`: `SHOW CONVERT_LSC` - [ ] `showWhitelist`: `SHOW WHITELIST` - [ ] `showStages`: `SHOW STAGES` ### Tablet, replica, and distribution metadata - [ ] `showTabletsFromTable`: `SHOW TABLETS FROM <table>` - [ ] `showTabletId`: `SHOW TABLET <id>` - [ ] `showTabletsBelong`: `SHOW TABLETS BELONG` - [ ] `showDiagnoseTablet`: `SHOW TABLET DIAGNOSIS` - [ ] `showReplicaDistribution`: `SHOW REPLICA DISTRIBUTION` - [ ] `adminShowReplicaDistribution`: `ADMIN SHOW REPLICA DISTRIBUTION` - [ ] `showReplicaStatus`: `SHOW REPLICA STATUS` - [ ] `adminShowReplicaStatus`: `ADMIN SHOW REPLICA STATUS` ### Jobs, load, backup, restore, and transaction metadata - [ ] `showLoad`: `SHOW LOAD` / `SHOW STREAM LOAD` - [ ] `showLoadWarings`: `SHOW LOAD WARNINGS` - [ ] `showLoadProfile`: `SHOW LOAD PROFILE` - [ ] `showCreateLoad`: `SHOW CREATE LOAD` - [ ] `showRoutineLoad`: `SHOW ROUTINE LOAD` - [ ] `showRoutineLoadTask`: `SHOW ROUTINE LOAD TASK` - [ ] `showCreateRoutineLoad`: `SHOW CREATE ROUTINE LOAD` - [ ] `showExport`: `SHOW EXPORT` - [ ] `showBackup`: `SHOW BACKUP` - [ ] `showRestore`: `SHOW RESTORE` - [ ] `showSnapshot`: `SHOW SNAPSHOT` - [ ] `showCreateRepository`: `SHOW CREATE REPOSITORY` - [ ] `showRepositories`: `SHOW REPOSITORIES` - [ ] `showBuildIndex`: `SHOW BUILD INDEX` - [ ] `showAlterTable`: `SHOW ALTER TABLE` - [ ] `showDelete`: `SHOW DELETE` - [ ] `showTransaction`: `SHOW TRANSACTION` - [ ] `showCopy`: `SHOW COPY` - [ ] `showWarmUpJob`: `SHOW WARM UP JOB` ### Query, process, variable, status, and statistics metadata - [ ] `showProcessList`: `SHOW PROCESSLIST` - [ ] `showVariables`: `SHOW VARIABLES` - [ ] `showStatus`: `SHOW STATUS` - [ ] `showQueryProfile`: `SHOW QUERY PROFILE` - [ ] `showQueryStats`: `SHOW QUERY STATS` - [ ] `showAnalyze`: `SHOW ANALYZE` - [ ] `showQueuedAnalyzeJobs`: `SHOW QUEUED ANALYZE JOBS` - [ ] `showAnalyzeTask`: `SHOW ANALYZE TASK STATUS` - [ ] `showTableStats`: `SHOW TABLE STATS` by table name and by table id - [ ] `showColumnStats`: `SHOW COLUMN STATS` - [ ] `showColumnHistogramStats`: `SHOW COLUMN HISTOGRAM` - [ ] `showIndexStats`: `SHOW INDEX STATS` - [ ] `showWarningErrors`: `SHOW WARNINGS` / `SHOW ERRORS` - [ ] `showWarningErrorCount`: `SHOW COUNT(*) WARNINGS/ERRORS` - [ ] `showLastInsert`: `SHOW LAST INSERT` ### Privilege, user, role, policy, and security metadata - [ ] `showGrants`: `SHOW GRANTS` - [ ] `showGrantsForUser`: `SHOW GRANTS FOR <user>` - [ ] `showCreateUser`: `SHOW CREATE USER` - [ ] `showRoles`: `SHOW ROLES` - [ ] `showPrivileges`: `SHOW PRIVILEGES` - [ ] `showUserProperties`: `SHOW PROPERTY` - [ ] `showAllProperties`: `SHOW ALL PROPERTIES` - [ ] `showRowPolicy`: `SHOW ROW POLICY` - [ ] `showSqlBlockRule`: `SHOW SQL_BLOCK_RULE` - [ ] `showEncryptKeys`: `SHOW ENCRYPTKEYS` ### Compatibility and MySQL-style metadata - [ ] `showAuthors`: `SHOW AUTHORS` - [ ] `showCharset`: `SHOW CHARSET` - [ ] `showCollation`: `SHOW COLLATION` - [ ] `showEvents`: `SHOW EVENTS` - [ ] `showTriggers`: `SHOW TRIGGERS` - [ ] `showOpenTables`: `SHOW OPEN TABLES` - [ ] `showPlugins`: `SHOW PLUGINS` - [ ] `showSmallFiles`: `SHOW FILE` - [ ] `showTypeCast`: `SHOW TYPECAST` ### Feature-specific metadata - [ ] `showDynamicPartition`: `SHOW DYNAMIC PARTITION TABLES` - [ ] `showWorkloadGroups`: `SHOW WORKLOAD GROUPS` - [ ] `showStreams`: `SHOW STREAMS` - [ ] `showCreateStream`: `SHOW CREATE STREAM` - [ ] `showPythonVersions`: `SHOW PYTHON VERSIONS` - [ ] `showPythonPackages`: `SHOW PYTHON PACKAGES` - [ ] `showIndexAnalyzer`: `SHOW INVERTED INDEX ANALYZER` - [ ] `showIndexTokenizer`: `SHOW INVERTED INDEX TOKENIZER` - [ ] `showIndexTokenFilter`: `SHOW INVERTED INDEX TOKEN_FILTER` - [ ] `showIndexCharFilter`: `SHOW INVERTED INDEX CHAR_FILTER` - [ ] `showIndexNormalizer`: `SHOW INVERTED INDEX NORMALIZER` - [ ] `showProc`: `SHOW PROC` ## Doris metadata TVFs to replace or mirror under information_schema These TVFs are currently exposed as table-valued functions and return Doris metadata rather than external file data. - [ ] `backends()`: backend node metadata - [ ] `frontends()`: frontend node metadata - [ ] `frontends_disks()`: frontend disk metadata - [ ] `catalogs()`: catalog metadata - [ ] `mv_infos()`: materialized view metadata - [ ] `partitions()`: table partition metadata - [ ] `partition_values()`: partition value metadata - [ ] `jobs()`: job metadata - [ ] `tasks()`: task metadata ## TVFs intentionally excluded from this tracking issue These are not Doris metadata tables in the same sense, so they should not be part of the first `information_schema` migration. - `s3()`, `hdfs()`, `local()`, `file()`, `http()`, `http_stream()`: external file/data access - `group_commit()`: load/write path helper - `numbers()`: data generator - `query()`: external catalog passthrough query - `cdc_stream()`: CDC stream source - `binlog()`: debug-only row binlog reader - `hudi_meta()`: external Hudi metadata - `parquet_meta()`, `parquet_file_metadata()`, `parquet_kv_metadata()`, `parquet_bloom_probe()`: external Parquet/file metadata ## Suggested implementation order 1. Start with metadata that already has an `information_schema` equivalent or near-equivalent: databases, tables, views, columns, partitions, processlist, variables, status, routines/functions, privileges. 2. Move cluster and node metadata next: frontends, backends, frontend disks, brokers, configs, resources, storage vaults, workload groups. 3. Move operational metadata: load, routine load, export, backup, restore, copy, transactions, snapshots, repositories. 4. Move tablet and replica metadata: tablets, tablet diagnosis, replica status, replica distribution, tablet storage format. 5. Move statistics and optimizer metadata: analyze jobs, table stats, column stats, histograms, query stats. 6. Move feature-specific metadata and compatibility-only SHOW commands last. ## Open questions - Should every migrated `SHOW` command be rewritten as a query against `information_schema`, or should both paths share a lower-level provider? - Should we put Doris-specific tables directly under `information_schema`, or use a separate namespace for non-standard tables? - How should we version or document columns whose names currently come from SHOW result titles? - Should external metadata TVFs such as `parquet_meta()` and `hudi_meta()` get a separate tracking issue? -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
