Database Metadata Views

In this section, we will introduce various database metadata views to help you better understand and query your data. These views provide detailed information about databases, tables, columns, views, users, roles, and job history. Through these views, you can easily manage and monitor your data.

SCHEMAS View

The SCHEMAS view provides detailed information about the database, including the WORKSPACE name, SCHEMA name, creator, type, etc.

Field Details

COLUMN NAME	DATA TYPE	Description
CATALOG_NAME	STRING	The name of the current WORKSPACE
SCHEMA_NAME	STRING	The name of the database
SCHEMA_CREATOR	STRING	The account name of the database owner
TYPE	STRING	Enum values: EXTERNAL (external), INTERNAL (internal)
COMMENT	STRING	Comment information when creating the database
CREATE_TIME	TIMESTAMP	Database creation time
LAST_MODIFY_TIME	TIMESTAMP	Database modification time
PROPERTIES	MAP	PROPERTIES specified at creation, reserved field

TABLES View

The TABLES view shows detailed information about each table under the current WORKSPACE.

Field Details

COLUMN NAME	DATA TYPE	Description
TABLE_CATALOG	STRING	The name of the current WORKSPACE
TABLE_SCHEMA	STRING	The SCHEMA to which the current TABLE belongs
TABLE_NAME	STRING	The name of the table
TABLE_TYPE	STRING	Enum values: EXTERNAL (external table), VIEW (view), MATERIALIZED VIEW (materialized view), BASE TABLE (standard table), SNAPSHOT (snapshot table)
ROW_COUNT	BIGINT	Number of rows in the table (NULL for VIEW, corresponding number for MATERIALIZED VIEW)
BYTES	BIGINT	Size of the table (NULL for VIEW, corresponding size for MATERIALIZED VIEW)
CREATE_TIME	TIMESTAMP	Table creation time
LAST_MODIFY_TIME	TIMESTAMP	Table modification time
TABLE_CREATOR	STRING	The account name of the table owner
IS_PARTITIONED	BOOLEAN	Whether it is a partitioned table (NULL for VIEW)
IS_CLUSTERED	BOOLEAN	Whether it is a clustered table (NULL for VIEW)
COMMENT	STRING	Table comment information
DATA_LIFECYCLE	BIGINT	Lifecycle (in days)
PROPERTIES	MAP	PROPERTIES specified at creation, reserved field

COLUMNS View

The COLUMNS view shows detailed information about each field in the table.

Field Details

COLUMN NAME	DATA TYPE	Description
TABLE_CATALOG	STRING	The name of the current WORKSPACE
TABLE_SCHEMA	STRING	The SCHEMA to which the current TABLE belongs
TABLE_NAME	STRING	The name of the table
COLUMN_NAME	STRING	The name of the field
COLUMN_DEFAULT	STRING	Default value of the field
IS_NULLABLE	BOOLEAN	Whether it can be NULL
DATA_TYPE	STRING	Field type
CREATE_TIME	TIMESTAMP_LTZ	Table creation time
IS_CLUSTERING_COLUMN	BOOLEAN	Whether it is a CLUSTER field
IS_PRIMARY_KEY	BOOLEAN	Whether it is a primary key
COMMENT	STRING	Field comment information

VIEWS View

The VIEWS view shows all views and their detailed information under the current WORKSPACE.

Field Details

COLUMN NAME	DATA TYPE	DESCRIPTION
TABLE_CATALOG	STRING	Name of the current WORKSPACE
TABLE_SCHEMA	STRING	SCHEMA to which the current VIEW belongs
TABLE_NAME	STRING	Name of the view
TABLE_CREATOR	STRING	Account name of the view owner
VIEW_DEFINITION	STRING	Statement to create the view
CREATE_TIME	TIMESTAMP	View creation time
LAST_MODIFY_TIME	TIMESTAMP	View modification time
COMMENT	STRING	View comment information

USERS View

Each user is displayed in a row, containing all users of the current WORKSPACE

COLUMN NAME	DATA TYPE	DESCRIPTION
WORKSPACE_NAME	STRING	Name of the current space
USER_NAME	STRING	User name
ROLE_NAME	STRING	Roles owned by the current user, multiple roles separated by commas
CREATE_TIME	TIMESTAMP	User join time
EMAIL	STRING	User email
TELEPHONE	STRING	User phone number
COMMENT	STRING	Description of user information
PROPERTIES	MAP	PROPERTIES specified at creation, reserved field

ROLES View

COLUMN NAME	DATA TYPE	DESCRIPTION
WORKSPACE_NAME	STRING	Name of the current space
ROLE_NAME	STRING	All roles in the space
USER_NAMES	STRING	User names granted this role, multiple users separated by commas
CREATE_TIME	TIMESTAMP	View creation time
COMMENT	STRING	Description of role information
PROPERTIES	MAP	PROPERTIES specified at creation, reserved field

JOB_HISTORY View

COLUMN NAME	DATA TYPE	DESCRIPTION
WORKSPACE_NAME	STRING	Space where the JOB is run
JOB_ID	STRING	Job ID
JOB_NAME	STRING	Job name
JOB_CREATOR	STRING	User running the job
STATUS	STRING	SCHEDULE, PROCESS, SUCCEEDED, FAILED, CANCELLED
CRU	DECIMAL	Computing resources consumed by the task
ERROR_MESSAGE	STRING	This information is available if an error occurs
JOB_TYPE	STRING	Job type COPY SQL DATALAKE (file operation commands)
JOB_TEXT	STRING	Statement executing the JOB
QUERY_TAG	STRING	User-set TAG for identifying the QUERY
START_TIME	TIMESTAMP	JOB start time
END_TIME	TIMESTAMP	JOB end time
EXECUTION_TIME	DOUBLE	Execution time in seconds, accurate to milliseconds
INPUT_BYTES	BIGINT	Actual scanned data volume.
OUTPUT_BYTES	BIGINT	Output bytes.
INPUT_OBJECTS	STRING	Input table names
OUTPUT_OBJECTS	STRING	Output table names
CLIENT_INFO	STRING	Client information, from JDBC, client, web page
VIRTUAL_CLUSTER	STRING	Computing resources used
ROW_PRODUCED	BIGINT	Total records processed, input data
ROW_INSERTED	BIGINT	Should have a value if it is an insert action
ROW_UPDATED	BIGINT	Should have a value if it is an update action
ROW_DELETED	BIGINT	Should have a value if it is a delete action
JOB_CONFIG	STRING	Parameter information set when submitting the job
CACHE_HIT	BIGINT	Data read from cache
JOB_PRIORITY	STRING	Job priority
INPUT_TABLES	STRING	Input table names
OUTPUT_TABLES	STRING	Output table name

Materialized View Refresh History

COLUMN_NAME	DATA_TYPE	DESCRIPTION
WORKSPACE_NAME	STRING	Project workspace name
SCHEMA_NAME	STRING	SCHEMA name
MATERIALIZED_VIEW_NAME	STRING	Materialized view name
CRU	DECIMAL	Cost for refreshing the materialized view
VIRTUAL_CLUSTER_NAME	STRING	Materialized view name, this information is available for automatic refresh
STATUS	STRING	PENDING\RUNNING\FINISHED\FAILED
SCHEDULED_START_TIME	TIMESTAMP_LTZ	Scheduled refresh time
START_TIME	TIMESTAMP_LTZ	Materialized view start time
END_TIME	TIMESTAMP_LTZ	Materialized view end time
ERROR_CODE	STRING
ERROR_MESSAGE	STRING	Refresh failure information, if failed it will be here

AUTOMV_REFRESH_HISTORY Refresh View

COLUMN_NAME	DATA_TYPE	DESCRIPTION
WORKSPACE_NAME	STRING	Project workspace name SYS
SCHEMA_NAME	STRING	SCHEMA name, SCHEMA where AUTOMV is located
MATERIALIZED_VIEW_NAME	STRING	Materialized view name
CRU	DECIMAL	Cost for refreshing the materialized view
STATUS	STRING	PROCESS: Refreshing. SUCCEEDED: Refresh completed successfully. FAILED: Refresh failed during execution. CANCELLED: Refresh was cancelled before execution.
MV_PROCESS_TYPE	STRING	BUILD: Build MV. REFRESH: Refresh
START_TIME	TIMESTAMP_LTZ	Start time of the materialized view
END_TIME	TIMESTAMP_LTZ	End time of the materialized view
BUILD_FROM_WORKSPACE	STRING	Source workspace for building MV
JOB_ID	SRING	Job ID for building MV
ERROR_MESSAGE	STRING	Error message if the refresh fails

VOLUMES View

column_name	data_type	description
VOLUME_CATALOG	STRING	Name of the associated Workspace
VOLUME_SCHEMA	STRING	Name of the associated Schema
VOLUME_NAME	STRING	Name of the Volume
VOLUME_URL	STRING	URL bound to the Volume
VOLUME_REGION	STRING	Region to which the Volume belongs
VOLUME_TYPE	STRING	Type of Volume (internal means no need to specify a third-party cloud provider address when creating the volume, or external)
VOLUME_CREATOR	STRING	Owner of the Volume
CONNECTION_NAME	STRING	Name of the referenced connection
COMMENT	STRING	Comment
PROPERTIES	map<string,string>
CREATE_TIME	TIMESTAMP	Creation time
LAST_MODIFY_TIME	TIMESTAMP	Modification time

CONNECTIONS View

column_name	data type	description
WORKSPACE_NAME	STRING	The workspace where the object is located
CONNECTION_NAME	STRING	Connection object name
CONNECTION_KIND	STRING	Enum value supporting connection types, STORAGE CONNECTION, API CONNECTION
TYPE	STRING	Specifies the type of data source connection. storage connection supports FILE_SYSTEM, api connection supports CLOUD_FUNCTION
PROVIDER	STRING	When TYPE is FILE_SYSTEM, it is OSS / COS. When TYPE is CLOUD_FUNCTION, it is aliyun / tencent
REGION	STRING	The region of the connection, such as ap-shanghai / cn-beijing
SOURCE_CREATOR	STRING	Creator
CREATE_TIME	TIMESTAMP	Creation time
LAST_MODIFY_TIME	TIMESTAMP	Last modification time
COMMENT	STRING	Comment information
PROPERTIES	map<string,string>