Tables

Use this reference to learn about the metadata available for Firebolt tables using the information schema.

You can use the information_schema.tables view to return information about each table in a database. The view is available for each database and contains one row for each table in the database. You can use a SELECT query to return information about each table as shown in the example below.

To view table information, you must have USAGE privileges on both the schema and the database. You also need ownership of the table or the necessary table-level privileges required for the intended action.

SELECT
  *
FROM
  information_schema.tables;

Columns in information_schema.tables

Each row has the following columns with information about each table.

Column Name	Data Type	Description
table_catalog	TEXT	The name of the database.
table_schema	TEXT	The name of the schema.
table_name	TEXT	The name of the table.
table_type	TEXT	The table’s type, such as BASE TABLE, EXTERNAL VIEW.
table_owner	TEXT	The owner of the table, or NULL if there is no owner.
created	TIMESTAMPTZ	The time that the table or view was created.
last_altered	TIMESTAMPTZ	Not applicable for Firebolt.
last_altered_by	TEXT	Not applicable for Firebolt.
primary_index	TEXT	An ordered array of the column names that comprise the primary index definition, if applicable.
number_of_rows	BIGINT	The number of rows in the table.
compressed_bytes	BIGINT	The compressed size of the table in bytes.
uncompressed_bytes	BIGINT	The uncompressed size of the table in bytes.
compression_ratio	NUMERIC	The compression ratio (<uncompressed_bytes>/<compressed_bytes>).
number_of_tablets	INTEGER	The number of tablets that comprise the table.
number_of_shredded_columns	INTEGER	The number of internal columns after STRUCT and ARRAY types are decomposed for storage.
number_of_column_streams	INTEGER	The number of storage streams required to store the table’s column data, including streams for nullability and array sizes.
fragmentation	DECIMAL	The table fragmentation percentage (between 0-100).
type	TEXT	The table’s type.
location_name	TEXT	The name of the location object used in the table definition, if any.
ddl	TEXT	The text of the SQL statement that created the table.
self_referencing_column_name	NULL	Not applicable for Firebolt.
reference_generation	NULL	Not applicable for Firebolt.
user_defined_type_catalog	NULL	Not applicable for Firebolt.
user_defined_type_schema	NULL	Not applicable for Firebolt.
user_defined_type_name	NULL	Not applicable for Firebolt.
is_insertable_into	TEXT	YES if the table is insertable, NO otherwise.
is_typed	TEXT	Always NO.
commit_action	NULL	Not applicable for Firebolt.
description	TEXT	The description of the table.

The following example retrieves the name, type, and row count for all base tables:

SELECT table_name, table_type, number_of_rows
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY table_name;

table_name text	table_type text	number_of_rows long null
levels	BASE TABLE	10
players	BASE TABLE	5420
playstats	BASE TABLE	10000
tournaments	BASE TABLE	157

Rows: 4Execution time: 2.99ms