22.3. Queries to Proxy Tables #
This section explains how to retrieve proxy table and pgpro_metastore object names. This can be useful when performing different tasks, for example, when auditing privileges.
22.3.1. Retrieving All Proxy Table and pgpro_metastore Object Names #
Execute the following command:
SELECT
pt.proxy_table_name,
CASE
WHEN pt.proxy_table_name LIKE 't_%' THEN 'table'
WHEN pt.proxy_table_name LIKE 'f_%' THEN 'folder'
WHEN pt.proxy_table_name LIKE 's_%' THEN 'storage'
WHEN pt.proxy_table_name LIKE 'h_%' THEN 'schema'
END AS object_type,
COALESCE(tbl.table_name, fld.folder_name, stg.storage_name, sch.schema_name) AS object_name
FROM axe_catalog.pga_proxy_table pt
LEFT JOIN axe_catalog.pga_table tbl
ON tbl.proxy_table_id = pt.proxy_table_id AND tbl.end_snapshot IS NULL
LEFT JOIN axe_catalog.pga_folder fld
ON fld.proxy_table_id = pt.proxy_table_id AND fld.end_snapshot IS NULL
LEFT JOIN axe_catalog.pga_storage stg
ON stg.proxy_table_id = pt.proxy_table_id AND stg.end_snapshot IS NULL
LEFT JOIN axe_catalog.pga_schema sch
ON sch.proxy_table_id = pt.proxy_table_id AND sch.end_snapshot IS NULL;
22.3.2. Retrieving a Proxy Table Name Using a pgpro_metastore Object Name #
You can retrieve names of proxy tables using the names of associated pgpro_metastore objects from metadata tables.
Example 22.3.
The following query retrieves the name of the proxy table associated with the my_table analytical table in the my_schema schema:
SELECT pt.proxy_table_name
FROM axe_catalog.pga_proxy_table pt
JOIN axe_catalog.pga_table t ON t.proxy_table_id = pt.proxy_table_id
JOIN axe_catalog.pga_schema s ON t.schema_id = s.schema_id
WHERE t.table_name = 'my_table'
AND s.schema_name = 'my_schema'
AND t.end_snapshot IS NULL;
22.3.3. Retrieving a pgpro_metastore Object Name Using a Proxy Table Name #
You can retrieve names of pgpro_metastore objects from metadata tables using the names of associated proxy tables.
Example 22.4.
The following query retrieves the name of the analytical table associated with the t_my_table_00001 proxy table:
SELECT t.table_name
FROM axe_catalog.pga_table t
JOIN axe_catalog.pga_proxy_table pt ON t.proxy_table_id = pt.proxy_table_id
WHERE pt.proxy_table_name = 't_my_table_00001'
AND t.end_snapshot IS NULL;