20.2. Copying the Data from Heap Tables to an Analytical Table (metastore.copy_table) #
You can use an SQL command to copy the data from heap tables to an analytical table.
Required privileges:
INSERTprivilege on the analytical table.The privilege required to execute the SQL command passed as the second parameter.
For more information about stored procedures and privileges, refer to Section 12.1.
Execute the following command on the Postgres Pro AXE server:
SELECT metastore.copy_table('table_name', SQL_command, 'path_to_JSON') ON CONFLICT DO NOTHING;
Where:
table_name: The name of the analytical table to which the data is copied.SQL_command: The SQL command that retrieves the data from heap tables (e.g.,SELECT * FROM my_pgtable).Functions outside the
pg_catalogschema are not supported.path_to_JSON: The path to a JSON file with Parquet file storage parameters.These parameters apply when creating new Parquet files. In the
metastore.add_filesstored procedure, parameters are ignored for non-partitioned tables since Parquet files are added as is but apply for partitioned tables where Parquet files are split into multiple files. In themetastore.copy_tablestored procedure, parameters always apply because new Parquet files are created from the SQL command results.For more information about partitioning, refer to Chapter 27.
Optional parameter.
Postgres Pro AXE performs the following actions:
Checks input parameters and user privileges.
Executes the SQL command and saves its results as Parquet files in a new subdirectory of the temporary storage directory. The subdirectory name is the new snapshot ID. The temporary storage directory is specified using the
metastore.add_storagestored procedure.Ensures metadata compatibility between Parquet files and the analytical table: the number, order, names, and types of columns must match.
Creates new entries in the
pga_snapshotandpga_data_filemetadata tables.Copies Parquet files into a new subdirectory in the storage directory of the analytical table. The subdirectory name is the new snapshot ID.
If Parquet files are added to a partitioned analytical table, they are split into multiple files based on partition columns, and a directory tree is created for these files.
Updates statistics in the
pga_table_stats,pga_table_column_stats, andpga_file_column_statisticsmetadata tables.
Example 20.2. Executing the metastore.copy_table Stored Procedure
SELECT metastore.copy_table('my_metastore_table', 'SELECT * FROM my_pgtable', 'folder/options.json') ON CONFLICT DO NOTHING;