Skip to content

Catalog Metadata

Every table makes metadata about itself available through special virtual tables that you can query through Hydrolix's SQL API or query UI. These include catalogs of the files and directories that hold tables' data and indexes, and descriptions of the columns and time-intervals held by each table's constituent partitions.

Catalog⚓︎

A catalog virtual table contains one row for every partition used by its associated fluid table. The row's data include that partition's filesystem path, the span of time-series data it contains, and the amount of storage space it takes up.

Catalog columns⚓︎

The catalog holds one row for each partition of a table's data. Each partition has these attributes:

Column Type Purpose
partition String Filesystem path to the partition, relative to the table's cloud-storage location.
min_timestamp DateTime Earliest timestamp of the partition's contiguous time-series data.
max_timestamp DateTime Latest timestamp of the partition's contiguous time-series data.
manifest_size UInt64 Size of the partition's manifest file, in bytes.
data_size UInt64 Size of the partition's data file, in bytes.
index_size UInt64 Size of the partition's index file, in bytes.
rows UInt64 Number of rows in the partition.
mem_size UInt64 Memory required to fully load the partition, in bytes.
root_path String Storage root path that the partition path is relative to.
shard_key String Shard key value assigned to the partition, if the table uses a shard key.
uncompressed_mem_size UInt64 Memory needed to hold the decoded, non-indexed data during decompression, in bytes.
raw_size UInt64 Raw customer data size, in bytes.
billing_byte UInt64 Size of the raw customer data, in bytes, with no compression, container, or indexing overhead. This is the per-byte billing measure.
storage_id String UUID of the Hydrolix storage location holding the partition data.

Query a table's catalog⚓︎

Query a table's catalog using the special view name .catalog.

Concatenate the table and view with # and enclose the combined table and view name inside backticks.

The sample queries demonstrate how to query the catalog of the cluster's table hydro.logs.

1
SELECT * FROM hydro.`logs#.catalog` LIMIT 10

To view the total table size in bytes, use

1
2
3
SELECT
  sum(manifest_size + data_size + index_size) as total_size
FROM hydro.`logs#.catalog`

Use catalog access to analyze effects of configuration changes, especially in the ingestion software. Hydrolix recommends new partitions are created between 1 GB and 3 GB in size. The merge system compacts partitions to reach optimal storage size of 2 GB and 4 GB per partition.

Visualize the distribution of partition sizes per day for the last ten days with this query.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
WITH 1048576 as mb, 1073741824 as gb
SELECT
  toStartOfDay(min_timestamp) as time,
  count(rows) as partitions,
  countIf(mem_size < 1 * mb) as "< 1MB",
  countIf(mem_size >= 1 * mb and mem_size < 5 * mb) as  "< 5MB",
  countIf(mem_size >= 5 * mb and mem_size < 50 * mb) as " < 50MB",
  countIf(mem_size >= 50 * mb and mem_size < 500 * mb) as " < 500MB",
  countIf(mem_size >= 500 * mb and mem_size < gb) as " < 1 GB",
  countIf(mem_size >= gb and mem_size < 2 * gb) as " < 2 GB",
  countIf(mem_size >= 2 * gb and mem_size < 3 * gb) as " < 3 GB",
  countIf(mem_size >= 3 * gb and mem_size < 4 * gb) as " < 4 GB"
FROM hydro."logs#.catalog"
where min_timestamp >= now() - INTERVAL 10 DAY
GROUP BY time
ORDER BY time DESC