[Tracking] Expose Doris metadata SHOW results as information_schema tables

[morningman]

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

• showConstraint: SHOW CONSTRAINTS
• 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
• showConfig: SHOW FRONTEND/BACKEND CONFIG
• showClusters: SHOW CLUSTERS / SHOW COMPUTE GROUPS
• showStorageVault: SHOW STORAGE VAULT(S)
• 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
• showWhitelist: SHOW WHITELIST
• showStages: SHOW STAGES

Jobs, load, backup, restore, and transaction metadata

• showRoutineLoad: SHOW ROUTINE LOAD
• showRoutineLoadTask: SHOW ROUTINE LOAD TASK
• showExport: SHOW EXPORT
• showDelete: SHOW DELETE
• showTransaction: SHOW TRANSACTION
• showWarmUpJob: SHOW WARM UP JOB

Query, process, variable, status, and statistics metadata

• showVariables: SHOW VARIABLES
• 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

Privilege, user, role, policy, and security metadata

• showGrants: SHOW GRANTS
• showGrantsForUser: SHOW GRANTS FOR <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

Feature-specific metadata

• showWorkloadGroups: SHOW WORKLOAD GROUPS
• showStreams: SHOW STREAMS

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?

[morningman]

Design note: prevent expensive FE-side metadata scans

One important risk in this migration is that some information_schema tables are populated by FE metadata, but the SQL plan still runs through normal BE execution.

For example, the old command:

SHOW PARTITIONS FROM table1;

has a natural table context. FE only needs to read partition metadata for table1.

After replacing it with a system table query, users may write:

SELECT * FROM information_schema.partitions WHERE table_name = 'table1';

If the predicate is only evaluated in BE, the BE must first fetch partition metadata for many tables from FE, then apply the filter locally. For high-cardinality metadata such as partitions, tablets, rowsets, or table properties, this can put avoidable pressure on FE.

Current code shape

Doris already has part of the required infrastructure:

• PushDownFilterIntoSchemaScan extracts fixed predicates on TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME from LogicalSchemaScan filters.
• LogicalSchemaScan and PhysicalSchemaScan already carry schemaCatalog, schemaDatabase, schemaTable, and frontendConjuncts.
• SchemaScanNode already sends db, table, and frontend_conjuncts to BE.
• However, for information_schema.partitions, SchemaPartitionsScanner currently sends only catalog and dbId back to FE in TSchemaTableRequestParams.
• MetadataGenerator.partitionsMetadataResult then calls database.getTables() and scans all tables in the database.

So the existing TABLE_NAME = 'table1' filter can be seen by the planner, but it is not yet used to reduce the data generated by FE for information_schema.partitions.

Recommended approach

The safest design is not only to require predicates. We should combine two mechanisms:

1. Add a planner-side guard for high-cardinality system tables.
2. Push the extracted constraints all the way to FE so FE can generate less metadata.

The guard protects FE from accidental full scans. The pushdown preserves the value of SQL access by making filtered queries efficient.

Planner-side guard

Add a validation step after schema-scan predicate extraction. This step should reject full scans for selected high-cardinality system tables unless the query contains a required, pushdown-capable constraint.

For example:

information_schema.partitions:
  require one of:
    - TABLE_SCHEMA + TABLE_NAME
    - TABLE_CATALOG + TABLE_SCHEMA + TABLE_NAME

information_schema.tablets / rowsets:
  require one of:
    - TABLE_SCHEMA + TABLE_NAME
    - TABLE_ID
    - TABLET_ID, if the table supports it

small metadata tables:
  allow full scan

This should be table-specific. We should avoid hard-coding everything in one rule. A small registry is easier to maintain, for example:

partitions:
  risk: HIGH
  requiredAny:
    - [TABLE_SCHEMA, TABLE_NAME]
  pushdownKeys:
    - TABLE_CATALOG
    - TABLE_SCHEMA
    - TABLE_NAME
    - PARTITION_NAME

workload_groups:
  risk: LOW
  fullScanAllowed: true

When a new metadata table is added under information_schema, the developer should define whether full scan is allowed and which predicates can be pushed to FE.

Predicate extraction

Start with simple, reliable predicates:

• TABLE_CATALOG = literal
• TABLE_SCHEMA = literal
• TABLE_NAME = literal
• literal = TABLE_NAME
• optionally TABLE_NAME IN (...), with a limit on the number of values

More complex predicates such as lower(table_name) = 't1' or join-derived predicates should not be required for the first version. They can still be evaluated in BE after FE returns the reduced row set.

FE-side pushdown

For information_schema.partitions, the minimal end-to-end change should be:

1. Let PushDownFilterIntoSchemaScan extract TABLE_SCHEMA and TABLE_NAME.
2. Pass the extracted table value from SchemaScanNode to SchemaPartitionsScanner.
3. Extend TSchemaTableRequestParams with a structured table filter, for example optional string table, instead of relying on serialized frontend_conjuncts for this core path.
4. In MetadataGenerator.partitionsMetadataResult, if the table filter is set, fetch only that table instead of calling database.getTables().
5. Keep the remaining SQL predicate in BE so behavior stays correct even if only part of the predicate is pushed down.

This gives us a small and testable first step: SELECT * FROM information_schema.partitions WHERE table_schema = 'db1' AND table_name = 'table1' should make FE inspect only table1.

Why not only reject full scans?

Rejecting full scans is useful, but it is not enough by itself. If the query is accepted but the predicate is not pushed into FE metadata generation, BE still fetches too much data from FE. The real requirement is: high-cardinality metadata tables must have both a required constraint and an FE-side implementation that uses that constraint.

Summary

For high-cardinality information_schema metadata tables, the migration should use this contract:

• The planner extracts supported constraints from SQL predicates.
• A validation rule rejects unsafe full scans for selected tables.
• BE forwards structured constraints to FE.
• FE uses those constraints to reduce metadata enumeration.
• BE still evaluates the original SQL predicate for correctness.

information_schema.partitions is a good first target because it already exposes the problem clearly and has a natural table-level filter equivalent to SHOW PARTITIONS FROM <table>.

[heguanhui]

@morningman Hi PMC, after carefully reading the design note and deeply analyzing the current codebase (specifically MetadataGenerator.java, TSchemaTableRequestParams.thrift, PhysicalSchemaScan, and the FE RPC entry fetchSchemaTableData), I completely agree with the identified performance risks and architectural technical debts. I would like to claim the following batch of tasks to establish the foundational architecture and migrate the core SHOW commands.

I will start by fixing the existing high-risk tables to establish the pattern, and then apply this unified pattern to the low-risk SHOW commands:

Phase 1: High-Risk POC (Establishing the framework)

• information_schema.partitions
• information_schema.backend_tablets (and related tablet metadata)

Phase 2: Low-Risk SHOW Commands Migration (Eliminating logic redundancy)

• SHOW FRONTENDS / frontends() → Create information_schema.frontends
• SHOW BACKENDS / backends() → Create information_schema.backends
• SHOW CLUSTERS / SHOW COMPUTE GROUPS → Create information_schema.clusters
• SHOW TRASH → Create information_schema.trash
• SHOW CATALOG RECYCLE BIN → Create information_schema.catalog_recycle_bin
• SHOW STORAGE VAULT(S) → Create information_schema.storage_vaults
• SHOW STORAGE POLICY → Create information_schema.storage_policies
• SHOW RESOURCES → Create information_schema.resources
• SHOW TRANSACTION → Create information_schema.transactions
• SHOW ROLES → Create information_schema.roles
• SHOW (ALL) GRANTS → Create information_schema.grants

Implementation Plan based on your design:

• Thrift Extension: Extend TSchemaTableRequestParams to carry structured constraints (e.g., table_name, schema_name) explicitly, instead of relying solely on serialized frontend_conjuncts for this critical path.

• Execution Path & Result Generation Unification: Currently, the FE-side fetchSchemaTableData RPC router splits queries into getSchemaTableData (which does full scans) and getMetadataTable (which is precise). We plan to unify the execution logic by routing SHOW commands through PhysicalSchemaScan (replacing the split ShowExecutor). Concurrently, we will consolidate the split logic in MetadataGenerator to ensure all metadata queries share the same structured predicate extraction and precise execution path.

• Planner-side Guard: Introduce a registry mechanism in SchemaTable declaring risk levels and RequiredAny predicates (e.g., TABLE_SCHEMA, TABLE_NAME). Reject unsafe full scans for high-risk tables, while allowing low-risk tables to be fully scanned.

• Predicate Extraction & Pushdown: Ensure the planner extracts predicates and pushes down structured constraints so FE can fetch precisely (e.g., calling database.getTable(tableName)), avoiding full database scans.

• BE-side Filtering: The original SQL predicates will remain in BE to ensure complete correctness.

This will give us a testable, end-to-end POC for the high-risk path, followed by the migration of the SHOW commands using the established pattern.

Please let me know if this scope and approach aligns with your expectations!