26.6. Reading Metadata #
- 26.6.1. Retrieving a Snapshot
- 26.6.2. Retrieving Analytical Schemas
- 26.6.3. Retrieving Analytical Tables
- 26.6.4. Retrieving Columns of an Analytical Table
- 26.6.5. Retrieving Filtered Parquet Files
- 26.6.6. Retrieving Analytical Table Statistics
- 26.6.7. Retrieving Analytical Table Column Statistics
- 26.6.8. Retrieving Analytical Table Statistics for a Specific Time Interval
- 26.6.2. Retrieving Analytical Schemas
26.6.1. Retrieving a Snapshot #
On the server with the metadata catalog, execute the following command:
SELECT * FROM axe_catalog.pga_snapshot WHERE snapshot_id = snapshot_ID;
Where snapshot_ID is the ID of the snapshot from the pga_snapshot metadata table.
To retrieve the latest snapshot, execute the following command:
SELECT * FROM axe_catalog.pga_snapshot WHERE snapshot_id = (SELECT max(snapshot_id) FROM axe_catalog.pga_snapshot);
26.6.2. Retrieving Analytical Schemas #
On the server with the metadata catalog, execute the following command:
SELECT schema_id, schema_name FROM axe_catalog.pga_schema WHEREsnapshot_ID>= begin_snapshot AND (snapshot_ID< end_snapshot OR end_snapshot IS NULL);
Where snapshot_ID is the ID of the snapshot from the pga_snapshot metadata table.
26.6.3. Retrieving Analytical Tables #
On the server with the metadata catalog, execute the following command:
SELECT table_id, table_name FROM axe_catalog.pga_table WHERE schema_id =schema_IDANDsnapshot_ID>= begin_snapshot AND (snapshot_ID< end_snapshot OR end_snapshot IS NULL);
Where:
schema_ID: The ID of the analytical schema from thepga_schemametadata table that contains analytical tables.snapshot_ID: The ID of the snapshot from thepga_snapshotmetadata table.
26.6.4. Retrieving Columns of an Analytical Table #
On the server with the metadata catalog, execute the following command:
SELECT column_id, column_name, column_type
FROM axe_catalog.pga_column
WHERE
table_id = table_ID AND
snapshot_ID >= begin_snapshot AND
(snapshot_ID < end_snapshot OR end_snapshot IS NULL)
ORDER BY column_order;
Where:
table_ID: The ID of the analytical table from thepga_tablemetadata table that contains the column.snapshot_ID: The ID of the snapshot from thepga_snapshotmetadata table.
26.6.5. Retrieving Filtered Parquet Files #
You can retrieve Parquet files after retrieving columns of an analytical table. Retrieved Parquet files can be filtered using statistics from the pga_file_column_statistics metadata table.
On the server with the metadata catalog, execute the following command:
SELECT data_file_id
FROM axe_catalog.pga_file_column_statistics
WHERE
table_id = table_ID AND
column_id = column_ID AND
(SCALAR >= min_value OR min_value IS NULL) AND
(SCALAR <= max_value OR max_value IS NULL);
Where:
table_ID: The ID of the analytical table that contains Parquet files, from thepga_tablemetadata table.column_ID: The ID of the column whose values are used for filtering Parquet files, from thepga_columnmetadata table.Only Parquet files whose range of column values includes the specified scalar value are retrieved.
You can filter column values using different conditions, such as "greater than (>)", by updating the command accordingly. The minimum and maximum values of each column are stored as arrays and must be converted to integers.
26.6.6. Retrieving Analytical Table Statistics #
Analytical table statistics are contained in the pga_table_stats metadata table.
On the server with the metadata catalog, execute the following command:
SELECT s1.record_count, s1.file_size_bytes FROM axe_catalog.pga_table_stats s1, axe_catalog.pga_table t1 WHERE s1.table_id = t1.table_id
AND t1.table_name='table_name';
Where table_name is the name of the analytical table from the pga_table metadata table.
Postgres Pro AXE outputs the number of analytical table rows and the total size of all its Parquet files.
26.6.7. Retrieving Analytical Table Column Statistics #
Analytical table column statistics are contained in the pga_table_column_stats and pga_column metadata tables.
On the server with the metadata catalog, execute the following command:
SELECT c1.column_name, s1.contains_null, s1.min_value, s1.max_value
FROM axe_catalog.pga_column c1, axe_catalog.pga_table_column_stats s1, axe_catalog.pga_table t1
WHERE c1.table_id = t1.table_id AND t1.table_name = 'table_name' AND s1.column_id = c1.column_id;
Where table_name is the name of the analytical table from the pga_table metadata table.
26.6.8. Retrieving Analytical Table Statistics for a Specific Time Interval #
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 thepga_tablemetadata table.start_date_and_timeandend_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 VIEW
view_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: The name of the view to create.path_to_file_1,path_to_file_2, andpath_to_file_N: Paths to the retrieved Parquet files.
Example 26.10.
You can query the created view to retrieve the required analytical table statistics:
SELECT COUNT(*) FROM view_example;