metastore.copy_table) #You can use an SQL command to copy the OLAP data from heap tables to an analytical table.
Required privileges:
INSERT privilege 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 22.1.
Execute the following command:
SELECT metastore.copy_table('table_name', SQL_command, 'path_to_JSON');
Where:
table_name: The name of the
analytical table
to which the OLAP data is copied.
SQL_command: The SQL command that retrieves
the OLAP data from heap tables, for example,
SELECT * FROM my_pgtable.
Functions outside the
pg_catalog
schema 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_files stored 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 the metastore.copy_table stored procedure, parameters always apply because
new Parquet files are created from the SQL command results.
For more information about partitioning, refer to Chapter 30.
Optional parameter.
Postgres Pro AXE performs the following actions:
Verifies input parameters and user privileges.
Creates a temporary storage directory for the SQL command results.
Executes the SQL command and creates Parquet files with its results.
Ensures metadata compatibility between Parquet files and the analytical table: the number, order, names, and types of columns must match.
Creates new entries in
pga_snapshot
and pga_data_file
metadata tables.
Copies Parquet files to the storage directory of the analytical table, to a new subdirectory with the snapshot ID as the name.
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
pga_table_stats,
pga_table_column_stats,
and pga_file_column_statistics
metadata tables.
Example 29.2. Executing the metastore.copy_table stored procedure
SELECT metastore.copy_table('my_metastore_table', $$select * from my_pgtable$$, 'folder/options.json');