Information Schema

The StarRocks information_schema is a database within each StarRocks instance. information_schema contains several read-only, system-defined tables which store extensive metadata information of all objects that the StarRocks instance maintains.

View metadata via Information Schema

You can view the metadata information within a StarRocks instance by querying the content of tables in information_schema.

The following example views metadata information about a table named sr_member in StarRocks by querying the table tables.

mysql> SELECT * FROM information_schema.tables WHERE TABLE_NAME like 'sr_member'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: sr_hub
     TABLE_NAME: sr_member
     TABLE_TYPE: BASE TABLE
         ENGINE: StarRocks
        VERSION: NULL
     ROW_FORMAT: NULL
     TABLE_ROWS: 6
 AVG_ROW_LENGTH: 542
    DATA_LENGTH: 3255
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2022-11-17 14:32:30
    UPDATE_TIME: 2022-11-17 14:32:55
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: NULL
  TABLE_COMMENT: OLAP
1 row in set (1.04 sec)

Information Schema tables

StarRocks has optimized the metadata information provided by the tables tables, tables_config, and load_tracking_logs and has provided the loads table from v3.1 onwards in information_schema:

Information Schema table name	Description
tables	Provides general metadata information of tables.
tables_config	Provides additional table metadata information that is unique to StarRocks.
load_tracking_logs	Provides error information (if any) of load jobs.
loads	Provides the results of load jobs. This table is supported from v3.1 onwards. Currently, you can only view the results of Broker Load and Insert jobs from this table.

loads

The following fields are provided in loads:

Field	Description
JOB_ID	The unique ID assigned by StarRocks to identify the load job.
LABEL	The label of the load job.
DATABASE_NAME	The name of the database to which the destination StarRocks tables belong.
STATE	The state of the load job. Valid values: `PENDING`: The load job is created. `QUEUEING`: The load job is in the queue waiting to be scheduled. `LOADING`: The load job is running. `PREPARED`: The transaction has been committed. `FINISHED`: The load job succeeded. `CANCELLED`: The load job failed. For more information, see Asynchronous loading.
PROGRESS	The progress of the ETL stage and LOADING stage of the load job.
TYPE	The type of the load job. For Broker Load, the return value is `BROKER`. For INSERT, the return value is `INSERT`.
PRIORITY	The priority of the load job. Valid values: `HIGHEST`, `HIGH`, `NORMAL`, `LOW`, and `LOWEST`.
SCAN_ROWS	The number of data rows that are scanned.
FILTERED_ROWS	The number of data rows that are filtered out due to inadequate data quality.
UNSELECTED_ROWS	The number of data rows that are filtered out due to the conditions specified in the WHERE clause.
SINK_ROWS	The number of data rows that are loaded.
ETL_INFO	The ETL details of the load job. A non-empty value is returned only for Spark Load. For any other types of load jobs, an empty value is returned.
TASK_INFO	The task execution details of the load job, such as the `timeout` and `max_filter_ratio` settings.
CREATE_TIME	The time at which the load job was created. Format: `yyyy-MM-dd HH:mm:ss`. Example: `2023-07-24 14:58:58`.
ETL_START_TIME	The start time of the ETL stage of the load job. Format: `yyyy-MM-dd HH:mm:ss`. Example: `2023-07-24 14:58:58`.
ETL_FINISH_TIME	The end time of the ETL stage of the load job. Format: `yyyy-MM-dd HH:mm:ss`. Example: `2023-07-24 14:58:58`.
LOAD_START_TIME	The start time of the LOADING stage of the load job. Format: `yyyy-MM-dd HH:mm:ss`. Example: `2023-07-24 14:58:58`.
LOAD_FINISH_TIME	The end time of the LOADING stage of the load job. Format: `yyyy-MM-dd HH:mm:ss`. Example: `2023-07-24 14:58:58`.
JOB_DETAILS	The details about the data loaded, such as the number of bytes and the number of files.
ERROR_MSG	The error message of the load job. If the load job did not encounter any error, `NULL` is returned.
TRACKING_URL	The URL from which you can access the unqualified data row samples detected in the load job. You can use the `curl` or `wget` command to access the URL and obtain the unqualified data row samples. If no unqualified data is detected, `NULL` is returned.
TRACKING_SQL	The SQL statement that can be used to query the tracking log of the load job. A SQL statement is returned only when the load job involves unqualified data rows. If the load job does not involve any unqualified data rows, `NULL` is returned.
REJECTED_RECORD_PATH	The path from which you can access all the unqualified data rows that are filtered out in the load job. The number of unqualified data rows logged is determined by the `log_rejected_record_num` parameter configured in the load job. You can use the `wget` command to access the path. If the load job does not involve any unqualified data rows, `NULL` is returned.

tables

The following fields are provided in tables:

Field	Description
TABLE_CATALOG	Name of the catalog that stores the table.
TABLE_SCHEMA	Name of the database that stores the table.
TABLE_NAME	Name of the table.
TABLE_TYPE	Type of the table. Valid values: "BASE TABLE" or "VIEW".
ENGINE	Engine type of the table. Valid values: "StarRocks", "MySQL", "MEMORY" or an empty string.
VERSION	Applies to a feature not available in StarRocks.
ROW_FORMAT	Applies to a feature not available in StarRocks.
TABLE_ROWS	Row count of the table.
AVG_ROW_LENGTH	Average row length (size) of the table. It is equivalent to `DATA_LENGTH` / `TABLE_ROWS`. Unit: Byte.
DATA_LENGTH	Data length (size) of the table. Unit: Byte.
MAX_DATA_LENGTH	Applies to a feature not available in StarRocks.
INDEX_LENGTH	Applies to a feature not available in StarRocks.
DATA_FREE	Applies to a feature not available in StarRocks.
AUTO_INCREMENT	Applies to a feature not available in StarRocks.
CREATE_TIME	The time when the table is created.
UPDATE_TIME	The last time when the table is updated.
CHECK_TIME	The last time when a consistency check is performed on the table.
TABLE_COLLATION	The default collation of the table.
CHECKSUM	Applies to a feature not available in StarRocks.
CREATE_OPTIONS	Applies to a feature not available in StarRocks.
TABLE_COMMENT	Comment on the table.

tables_config

The following fields are provided in tables_config:

Field	Description
TABLE_SCHEMA	Name of the database that stores the table.
TABLE_NAME	Name of the table.
TABLE_ENGINE	Engine type of the table.
TABLE_MODEL	Table type. Valid values: "DUP_KEYS", "AGG_KEYS", "UNQ_KEYS" or "PRI_KEYS".
PRIMARY_KEY	The primary key of a Primary Key table or a Unique Key table. An empty string is returned if the table is not a Primary Key table or a Unique Key table.
PARTITION_KEY	The partitioning columns of the table.
DISTRIBUTE_KEY	The bucketing columns of the table.
DISTRIBUTE_TYPE	The data distribution method of the table.
DISTRIBUTE_BUCKET	Number of buckets in the table.
SORT_KEY	Sort keys of the table.
PROPERTIES	Properties of the table.
TABLE_ID	ID of the table.

load_tracking_logs

This feature is supported since StarRocks v3.0.

The following fields are provided in load_tracking_logs:

Field	Description
JOB_ID	The ID of the load job.
LABEL	The label of the load job.
DATABASE_NAME	The database that the load job belongs to.
TRACKING_LOG	Error logs (if any) of the load job.
Type	The type of the load job. Valid values: BROKER, INSERT, ROUTINE_LOAD and STREAM_LOAD.

materialized_views

The following fields are provided in materialized_views:

Field	Description
MATERIALIZED_VIEW_ID	ID of the materialized view
TABLE_SCHEMA	Database in which the materialized view resides
TABLE_NAME	Name of the materialized view
REFRESH_TYPE	Refresh type of the materialized view, including `ROLLUP`, `ASYNC`, and `MANUAL`
IS_ACTIVE	Indicates whether the materialized view is active. Inactive materialized views can not be refreshed or queried.
INACTIVE_REASON	The reason that the materialized view is inactive
PARTITION_TYPE	Type of partitioning strategy for the materialized view
TASK_ID	ID of the task responsible for refreshing the materialized view
TASK_NAME	Name of the task responsible for refreshing the materialized view
LAST_REFRESH_START_TIME	Start time of the most recent refresh task
LAST_REFRESH_FINISHED_TIME	End time of the most recent refresh task
LAST_REFRESH_DURATION	Duration of the most recent refresh task
LAST_REFRESH_STATE	State of the most recent refresh task
LAST_REFRESH_FORCE_REFRESH	Indicates whether the most recent refresh task was a force refresh
LAST_REFRESH_START_PARTITION	Starting partition for the most recent refresh task
LAST_REFRESH_END_PARTITION	Ending partition for the most recent refresh task
LAST_REFRESH_BASE_REFRESH_PARTITIONS	Base table partitions involved in the most recent refresh task
LAST_REFRESH_MV_REFRESH_PARTITIONS	Materialized view partitions refreshed in the most recent refresh task
LAST_REFRESH_ERROR_CODE	Error code of the most recent refresh task
LAST_REFRESH_ERROR_MESSAGE	Error message of the most recent refresh task
TABLE_ROWS	Number of data rows in the materialized view, based on approximate background statistics
MATERIALIZED_VIEW_DEFINITION	SQL definition of the materialized view

Edit

Feedback

In this article

Information Schema