On the server with the metadata catalog, retrieve Parquet files added to the analytical table within a specific time interval:
SELECT array_agg(t1.path) FROM axe_catalog.pga_data_file t1, axe_catalog.pga_table t2, axe_catalog.pga_snapshot t3 WHERE t1.end_snapshot IS NULL AND t1.table_id = t2.table_id AND t2.end_snapshot IS NULL AND t2.table_name = 'table_name' AND t1.begin_snapshot = t3.snapshot_id AND t3.is_visible = true AND t3.snapshot_time > timestamptz 'start_date_and_time' AND t3.snapshot_time < timestamptz 'end_date_and_time';
Where:
table_name: The name of the analytical
table from the pga_table metadata table.
start_date_and_time and
end_date_and_time: The start and end
date and time for retrieving Parquet files.
On the server with the Postgres Pro DBMS, create a view for the retrieved Parquet files:
CREATE VIEWview_nameAS SELECT * FROM read_parquet('path_to_file_1') UNION ALL SELECT * FROM read_parquet('path_to_file_2') UNION ALL SELECT * FROM read_parquet('path_to_file_N');
Where:
view_name is the name of the view to create.
path_to_file_1,
path_to_file_2, and
path_to_file_N: Paths to the
retrieved Parquet files.
Example 39.1.
You can query the created view to retrieve the required analytical table statistics:
SELECT COUNT(*) FROM view_example;