Applies to VAST Cluster 5.5.
The Catalog and the audit log are VAST DataBase tables, so the VAST connectors for Trino and Spark read them like any other table. Use SQL when you want the engine to do the grouping and sorting, or when a BI tool or dashboard needs the data.
This page gives the table names and example queries. Setting up the connector is the same as for your own tables: see Trino and Spark. The connector's key needs access to the Catalog or the audit log; see access.
Table names
| Table | Python SDK | Trino | Spark |
|---|---|---|---|
| Catalog | tx.catalog() |
vast."vast-big-catalog-bucket/vast_big_catalog_schema".vast_big_catalog_table |
ndb.`vast-big-catalog-bucket`.vast_big_catalog_schema.vast_big_catalog_table |
| Audit log | tx.audit_log() |
vast."vast-audit-log-bucket/vast_audit_log_schema".vast_audit_log_table |
ndb.`vast-audit-log-bucket`.vast_audit_log_schema.vast_audit_log_table |
The Trino names assume your catalog is called vast, and the Spark names that the connector's catalog is ndb, as on the DataBase pages. Catalog snapshots are read through the Python SDK; see change detection.
Trino
Look at the Catalog's columns:
SHOW COLUMNS FROM vast."vast-big-catalog-bucket/vast_big_catalog_schema".vast_big_catalog_table;
Column | Type | Extra | Comment
---------------------+-----------------------------------------+-------+---------
phandle | row(clone_id integer, handle_id bigint) | |
creation_time | timestamp(9) | |
uid | integer | |
owner_sid | varchar | |
owner_name | varchar | |
gid | integer | |
group_owner_sid | varchar | |
group_owner_name | varchar | |
atime | timestamp(9) | |
mtime | timestamp(9) | |
ctime | timestamp(9) | |
nlinks | bigint | |
element_type | varchar | |
size | bigint | |
used | bigint | |
tenant_id | integer | |
name | varchar | |
extension | varchar | |
parent_path | varchar | |
symlink_path | varchar | |
major_device | integer | |
minor_device | integer | |
s3_locks_retention | row(mode tinyint, timeout time(6)) | |
nfs_mode_bits | integer | |
name_aces_exist | boolean | |
s3_locks_legal_hold | boolean | |
user_tags_count | smallint | |
user_metadata | map(varchar, varchar) | |
user_tags | map(varchar, varchar) | |
login_name | varchar | |
search_path | varchar | |
abac_tags | array(varchar) | |
nfs4_xattrs | map(varchar, varchar) | |
metadata_project | varchar | |
tag_processed | varchar | |
(35 rows)
Count files and bytes by extension under a path. Keep the search_path filter in every query: it restricts the query to that subtree on the cluster. Replace /cat-data/projects/ with a path your key can read:
SELECT extension, count(*) AS files, sum(size) AS bytes
FROM vast."vast-big-catalog-bucket/vast_big_catalog_schema".vast_big_catalog_table
WHERE search_path = '/cat-data/projects/' AND element_type = 'FILE'
GROUP BY extension
ORDER BY bytes DESC
LIMIT 10;
extension | files | bytes
-----------+-------+----------
parquet | 8 | 16777216
bin | 1 | 12582912
csv | 5 | 769024
(3 rows)
Rank owners by capacity used:
SELECT owner_name, count(*) AS files, sum(used) AS used_bytes
FROM vast."vast-big-catalog-bucket/vast_big_catalog_schema".vast_big_catalog_table
WHERE search_path = '/cat-data/projects/' AND element_type = 'FILE'
GROUP BY owner_name
ORDER BY used_bytes DESC
LIMIT 10;
owner_name | files | used_bytes
------------+-------+------------
cat-app | 14 | 30129152
(1 row)
Find objects by a tag. element_at returns null when the object doesn't have the key:
SELECT parent_path || name AS path, user_tags
FROM vast."vast-big-catalog-bucket/vast_big_catalog_schema".vast_big_catalog_table
WHERE search_path = '/cat-data/projects/' AND user_tags_count > 0
AND element_at(user_tags, 'processed') = 'false'
LIMIT 10;
path | user_tags
--------------------------------------------+-----------------------------------
/cat-data/projects/apollo/part-008.parquet | {processed=false, project=apollo}
/cat-data/projects/apollo/part-007.parquet | {processed=false, project=apollo}
/cat-data/projects/apollo/part-006.parquet | {processed=false, project=apollo}
/cat-data/projects/apollo/model.bin | {processed=false, project=apollo}
(4 rows)
Count the audit log's operations of the last hour:
SELECT protocol, rpc_type, count(*) AS operations
FROM vast."vast-audit-log-bucket/vast_audit_log_schema".vast_audit_log_table
WHERE time >= current_timestamp - INTERVAL '1' HOUR
GROUP BY protocol, rpc_type
ORDER BY operations DESC
LIMIT 15;
protocol | rpc_type | operations
----------+-----------------+------------
S3 | PUT_OBJECT | 21
S3 | LIST_OBJECTS | 9
S3 | GET_OBJECT | 4
S3 | DELETE_OBJECT | 1
S3 | LIST_OBJECTS_V2 | 1
(5 rows)
Which filters the connector pushes down to the cluster is described in pushdowns. Check a query's plan with EXPLAIN, as in Trino.
Spark
The same queries in Spark SQL, with the ndb catalog:
SELECT extension, count(*) AS files, sum(size) AS bytes
FROM ndb.`vast-big-catalog-bucket`.vast_big_catalog_schema.vast_big_catalog_table
WHERE search_path = '/cat-data/projects/' AND element_type = 'FILE'
GROUP BY extension
ORDER BY bytes DESC
LIMIT 10;
SELECT protocol, rpc_type, count(*) AS operations
FROM ndb.`vast-audit-log-bucket`.vast_audit_log_schema.vast_audit_log_table
WHERE time >= current_timestamp() - INTERVAL 1 HOUR
GROUP BY protocol, rpc_type
ORDER BY operations DESC
LIMIT 15;
Spark is a good fit for joining the Catalog with your own tables, for example file metadata with a table of project owners, and for writing report results to a table of your own.
See also
- Trino and Spark: connector setup, names and version pairing.
- Grafana: dashboards on Trino.
- Find files and capacity reports: the same questions in Python.