Tables

The tables system table includes information about the tables in your organization.

From a worksheet within Upsolver, run the following query:

SELECT * 
FROM system.information_schema.tables;
-- If you have a lot of tables, you can use LIMIT to reduce the result set 
-- LIMIT 50; 

The system.information_schema.tables table includes the following columns:

ColumnTypeDescription

catalog

string

The name of the catalog used to store the table.

comment

string

An optional comment to describe the table.

created_at

datetime

The date and time when the table was created.

created_by

string

The name of the user who created the table.

format

string

The format of the table, e.g. hive, iceberg.

id

string

The unique identifier for the table.

is_external

Boolean

When this column returns TRUE, this is an Iceberg table managed by Upsolver, but which is not a target of an Iceberg ingestion job.

modified_at

datetime

The date and time when the table was last modified.

modified_by

string

The name of the user who modified the table.

name

string

The name of the table.

properties

string

See the Properties table below.

schema

string

The schema in which the table resides.

status

string

The status of the table, e.g. running.

Properties

The properties column comprises information on the options specified when the table was created:

ColumnTypeDescription

COMMENT

string

An optional comment to describe the table

COMPACTION_PROCESSES

integer

The number of compaction processes the table can do in parallel when it periodically compacts the data.

COMPRESSION

string

The type of compression for the table data.

COMPUTE_CLUSTER

string

The name of the compute cluster that processes the table.

DISABLE_COMPACTION

Boolean

Returns TRUE or FALSE. If TRUE, compaction is disabled.

EXPOSE_IN_CATALOG

Boolean

Returns TRUE or FALSE to determine if the table is exposed in the AWS Glue Data Catalog so it can be queried from data lake query engines.

GLOBALLY_UNIQUE_KEYS

Boolean

Returns TRUE or FALSE to define whether primary keys should be unique across partitions.

STORAGE_CONNECTION

string

The default storage connection configured for the metastore connection where the table is created.

STORAGE_LOCATION

string

The default storage location configured for the metastore connection where the table is created.

TABLE_DATA_RETENTION

string

The number of days that the data will be retained prior to deletion.

For more information, please refer to the Tables SQL command reference.

Last updated