25.1. Creating an Analytical Table (metastore.add_table) #
25.1.1. Creating an Analytical Table from a Parquet File #
Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 22.1.
Execute the following command:
SELECT metastore.add_table('schema_name.table_name', 'storage_name', 'Parquet_file_URI', 'columns_for_partitioning');
Where:
schema_name.table_name: The name of the analytical schema where the analytical table is created, followed by the table name itself.You can omit the schema name and simply specify the table name (without a dot). In this case, the default
mainanalytical schema is used.Two tables with the same name cannot be created in the same schema. Analytical schema names are contained in the
schema_namecolumn of thepga_schemametadata table. Analytical table names are contained in thetable_namecolumn of thepga_tablemetadata table.storage_name: The name of the storage where Parquet files of the analytical table 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://.columns_for_partitioning: A comma-separated list of columns by which the analytical table is partitioned.Optional parameter.
Postgres Pro AXE performs the following actions:
Verifies input parameters and user privileges.
Ensures that the Parquet file exists.
Extracts information about columns from the Parquet file.
Associates the data type of each column from the Parquet file with a pgpro_metastore data type.
Creates new entries in
pga_snapshot,pga_table, andpga_columnmetadata tables.Creates new entries in
pga_partition_info,pga_partition_column, andpga_partition_valuemetadata tables if the analytical table is partitioned.
Example 25.1. Executing the metastore.add_table stored procedure
SELECT metastore.add_table('table_example', 'storage_example', 'file:///home/user/tmp/parquet_data/t.parquet', 'column_1,column_2,column_3');
25.1.2. Creating an Analytical Table From a Heap Table #
Required privileges: Postgres Pro AXE administrator only. For more information about stored procedures and privileges, refer to Section 22.1.
Execute the following command:
SELECT metastore.add_table('schema_name.table_name', 'storage_name', 'heap_table_name', 'columns_for_partitioning');
Where:
schema_name.table_name: The name of the analytical schema where the analytical table is created, followed by the table name itself.You can omit the schema name and simply specify the table name (without a dot). In this case, the default
mainanalytical schema is used.Two tables with the same name cannot be created in the same schema. Analytical schema names are contained in the
schema_namecolumn of thepga_schemametadata table. Analytical table names are contained in thetable_namecolumn of thepga_tablemetadata table.storage_name: The name of the storage where Parquet files of the analytical table are located.heap_table_name: The fully qualified name of the heap table from which the analytical table is created, starting with the schema name.columns_for_partitioning: A comma-separated list of columns by which the analytical table is partitioned.Optional parameter.
Postgres Pro AXE performs the following actions:
Verifies input parameters and user privileges.
Ensures that the heap table exists.
Extracts information about columns 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.Creates new entries in
pga_partition_info,pga_partition_column, andpga_partition_valuemetadata tables if the analytical table is partitioned.
Example 25.2. Executing the metastore.add_table stored procedure
SELECT metastore.add_table('table_example', 'storage_example', 'public.heap_table_example', 'column_1,column_2,column_3');