16.1. Creating an Analytical Table #
16.1.1. Creating an Analytical Table from a Parquet File #
Execute the following query:
SELECT metastore.add_table('table_name', 'storage_name', 'Parquet_file_URI', '');
Where:
table_name: The unique analytical table name.The fully qualified name of the analytical table consists of the analytical schema and table name. It is generated in
analytical_schema_name.analytical_table_nameformat.storage_name: The name of the storage where Parquet files are located.Parquet_file_URI: The URI of the Parquet file from which the analytical table is created.The URI must either start with
file://ors3://.
Important
Do not omit the last empty parameter — ''. It is reserved for future use, and without it the query does not work.
Example 16.1.
SELECT metastore.add_table('table_example', 'storage_example', 'file:///home/user/tmp/parquet_data/t.parquet', '');
Once the query is executed, pgpro_metastore performs the following actions:
Verifies user privileges.
Ensures that the source of metadata of the analytical table exists.
Uses the Apache Arrow library to export information about columns from the Parquet file.
Associates the data type of each column of the analytical table with a pgpro_metastore data type.
Creates new entries in
pga_snapshot,pga_table, andpga_columnmetadata tables.
16.1.2. Creating an Analytical Table From a Heap Table #
Execute the following query:
SELECT metastore.add_table('table_name', 'storage_name', 'heap_table_name', '');
Where:
table_name: The unique analytical table name.The fully qualified name of the analytical table consists of the analytical schema and table name. It is generated in
analytical_schema_name.analytical_table_nameformat.storage_name: The name of the storage where Parquet files are located.heap_table_name: The fully qualified heap table name starting with the schema name.
Important
Do not omit the last empty parameter — ''. It is reserved for future use, and without it the query does not work.
Example 16.2.
SELECT metastore.add_table('table_example', 'storage_example', 'public.heap_table_example', '');
Once the query is executed, pgpro_metastore performs the following actions:
Verifies user privileges.
Ensures that the source of metadata of the analytical table exists.
Exports information about columns of the analytical table from the heap table by querying Postgres Pro service tables.
Associates the data type of each column with a pgpro_metastore data type.
Creates new entries in
pga_snapshot,pga_table, andpga_columnmetadata tables.