Discover Insights From System Catalog Tables

You can query the Ocient® System catalog tables to discover important information on system objects such as databases, segments, tables, and indexes. These statistics can be important for monitoring your system to ensure it operates as intended. However, querying these tables can be challenging because generating insights about your system can involve writing complex queries that involve merging, grouping, and filtering multiple system catalog tables. Here, you can find examples of useful queries that you can use as templates for discovering insights about your system.

Many of the metrics on this page are also available in the Ocient Management UI, which provides a visual interface for monitoring queries, databases, storage, and node health without writing SQL.

Most of these example queries use the data_type_coverage table available with the Ocient Simulator. For details, see Ocient Simulator.

Segment System Catalog Tables

For specific column definitions of the system catalog tables referenced in these examples, see the Storage section of the System Catalog page.

The Management UI Storage page displays segment counts, table sizes, and storage space utilization without SQL queries. The Analysis tab breaks this down further by table and by TimeKey® segment.

Segment Size

Check the size of segments and return insights about the segment storage allocation and the number of table rows contained in each segment.

SQL

SELECT current_timestamp,
    sg.segment_type,
    t.name,
    (COUNT(*)) AS num_segments,
    (SUM(s.row_count)) AS total_rows,
    (AVG(s.row_count)) AS avg_rows_per_segment,
    (DOUBLE(SUM(s.segment_size)) / 1000000000) AS total_size_gb,
    (MAX(s.segment_size / 1000000)) AS max_size,
    (MIN(s.segment_size / 1000000)) AS min_size,
    (AVG(s.segment_size) / 1000000) AS avg_size_mb,
    (STDEV(DOUBLE(s.segment_size) / 1000000)) AS stdev_size_mb
FROM
    sys.segment_groups AS sg
    JOIN sys.tables t ON t.id = sg.table_id
    JOIN sys.segments s ON s.segment_group_id = sg.id
GROUP BY
    sg.segment_type, t.name
ORDER BY
    total_size_gb DESC,
    total_rows DESC,
    sg.segment_type,
    t.name;

Output

Text

|  "current_timestamp()"  | "segment_type" |       "name"       | "num_segments" | "total_rows" | "avg_rows_per_segment" | "total_size_gb" | "max_size" | "min_size" |   "avg_size_mb"    |  "stdev_size_mb"  |
|-------------------------|----------------|--------------------|----------------|--------------|------------------------|-----------------|------------|------------|--------------------|-------------------|
| 2024-07-31 16:23:22.863 | TKT_SEGMENT    | data_type_coverage |              3 |       100000 |     33333.333333333336 |     0.189542692 |        113 |         37 | 63.180897333333334 | 43.73338035824336 |

For information on the system catalog tables queried in this example, see sys.segments and sys.tables.

Page and Segment Information

Check the details for pages and segments in the system.

SQL

WITH cte AS (
SELECT
	COALESCE(v.schema, t.schema) AS schema,
	COALESCE(v.name, t.name) AS name,
	stored_segments.status,
	stored_segments.kind,
	SUM(CASE WHEN segments.segment_type = 'PAGE' THEN 1 ELSE 0 END) AS pagecount,
	SUM(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN 1 ELSE 0 END) AS segcount,
	SUM(CASE WHEN segments.segment_type = 'PAGE' THEN segments.row_count ELSE 0 END) AS page_rows,
	SUM(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN segments.row_count ELSE 0 END) AS seg_rows,
	MIN(CASE WHEN segments.segment_type = 'PAGE' THEN sg.begin_time ELSE NULL END) AS minpagetime,
	MAX(CASE WHEN segments.segment_type = 'PAGE' THEN sg.end_time ELSE NULL END) AS maxpagetime,
	MIN(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN sg.begin_time ELSE NULL END) AS minsegtime,
	MAX(CASE WHEN segments.segment_type = 'TKT_SEGMENT' THEN sg.end_time ELSE NULL END) AS maxsegtime
FROM
	sys.segments
JOIN sys.stored_segments ON
	segments.segment_group_id = stored_segments.segment_group_id
	AND segments.ida_offset = stored_segments.ida_offset
JOIN sys.segment_groups sg ON
	sg.id = segments.segment_group_id
JOIN sys.tables AS t ON
	t.id = sg.table_id
LEFT JOIN sys.views AS v ON
	v.global_dictionary_compression_table_id = t.id
WHERE
	stored_segments.end_osn = 'OSN_INFINITY'
GROUP BY
	1,
	2,
	stored_segments.status,
	stored_segments.kind )
SELECT
	schema,
	name,
	status,
	kind,
	pagecount,
	segcount,
	page_rows,
	seg_rows,
	page_rows + seg_rows AS tot_rows,
	CEILING(page_rows /(pagecount +.000001)) AS rows_per_page,
	CEILING(seg_rows /(segcount +.000001)) AS rows_per_seg,
	to_timestamp(minpagetime / 1000000000) AS minpagetime,
	to_timestamp(maxpagetime / 1000000000) AS maxpagetime,
	to_timestamp(minsegtime / 1000000000) AS minsegtime,
	to_timestamp(maxsegtime / 1000000000) AS maxsegtime,
	NOW()
FROM
	cte
ORDER BY
	schema,
	name;

Output

Text

| "schema" |       "name"       | "status" | "kind" | "pagecount" | "segcount" | "page_rows" | "seg_rows" | "tot_rows" | "rows_per_page" | "rows_per_seg" | "minpagetime" | "maxpagetime" |      "minsegtime"       |      "maxsegtime"       |  "current_timestamp()"  |
|----------|--------------------|----------|--------|-------------|------------|-------------|------------|------------|-----------------|----------------|---------------|---------------|-------------------------|-------------------------|-------------------------|
| loading  | data_type_coverage | INTACT   | DISK   |           0 |          3 |           0 |     100000 |     100000 |             0.0 |        33334.0 |               |               | 1970-01-01 00:00:00.000 | 1970-01-01 00:00:00.000 | 2024-08-07 21:41:36.800 |

Storage Spaces on a System

Check all storage spaces on your system.

SQL

SELECT *
FROM sys.storage_spaces;

Output

Text

|                  "id"                  |       "name"       | "is_system_storage_space" | "block_size" | "total_width" | "parity_width" | "parity_type" | "parity_cycles" | "page_replication" |
|----------------------------------------|--------------------|---------------------------|--------------|---------------|----------------|---------------|-----------------|--------------------|
| ce9f9c2e-d3fe-4101-a2b2-6f7f2580327b   | ss0                | false                     |         4096 |             3 |              1 | XOR           |               1 |                  2 |
| "7277656e-6465-6c20-7761-732068657265" | systemstoragespace | true                      |         4096 |             3 |              2 | REPLICATION   |               1 |                  3 |

Query Management System Catalog Tables

For specific column definitions of the system catalog tables referenced in these examples, see the Monitoring section of the System Catalog page.

The Management UI Queries page displays running and completed queries with filtering by status, user, service class, and execution time.

Running Queries

Check all queries currently running on the system. The output of the sys.queries table depends on what database you are logged into and your system role. For details, see Query Visibility.

SQL

SELECT
	queries.query_id,
    queries.initial_priority,
    queries.effective_priority,
	queries.user,
	queries.total_time,
	queries.status,
	queries.rows_returned,
	queries.bytes_returned
FROM
	sys.queries
WHERE
	queries.sql NOT LIKE '%sys.queries%';

SELECT
	q.query_id,
	q.user,
	q.sql,
	q.status,
	"database_name",
	q.bytes_returned
FROM
	sys.queries q;

Output

Text

|               "query_id"               |    "user"    |      "sql"       | "status" | "database_name" | "bytes_returned" |
|----------------------------------------|--------------|------------------|----------|-----------------|------------------|
| "66b27dde-17df-403a-82d7-65e34b6fd07f" | admin@system |  "example_query" | RUNNING  | test            |                0 |

Recent Queries

Check the most recent queries completed by the system. The output of the sys.completed_queries table depends on what database you are logged into and your system role. For details, see Query Visibility.

SQL

SELECT user,
    timestamp_start,
    total_time,
    state,
    reason,
    sql
FROM sys.completed_queries
ORDER BY timestamp_start DESC
LIMIT 10;

Output

Text

|    "user"    |       "time_start"        | "total_time" | "state" |                                                             "reason"                                                              |                                                                 "sql"                                                                 |             |                                       |                                                                                                                                    |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
|--------------|---------------------------|--------------|---------|-----------------------------------------------------------------------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------|-------------|---------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------|------------|---------|------------------------------------|----------|------------|---------|--------------------------------------------------------------------|
| admin@system | "2024-08-06T21:51:29.284" |           84 | "00000" | The operation completed successfully                                                                                              | "with the_user_query_to_add_a_limit_to as (select user                                                                                |  time_start |  total_time                           |  state                                                                                                                             |  reason                                                                                                                            |  sql from sys.completed_queries order by time_start desc limit 100) select * from the_user_query_to_add_a_limit_to limit 200"                                                                                                  |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:45:33.952" |           58 | "00000" | The operation completed successfully                                                                                              | "with the_user_query_to_add_a_limit_to as (select q.query_id                                                                          |  q.user     |  q.sql                                |  q.status                                                                                                                          |  ""database_name""                                                                                                                 |  q.bytes_returned from sys.queries q) select * from the_user_query_to_add_a_limit_to limit 200"                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:41:04.521" |          205 | "00000" | The operation completed successfully                                                                                              | "with the_user_query_to_add_a_limit_to as (select sg.segment_type                                                                     |  ss.kind    |  ss.status                            |  t.name                                                                                                                            |  (ss.end_osn = 'OSN_INFINITY') active                                                                                              |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss join sys.segment_groups sg on sg.id = ss.segment_group_id join sys.tables t on t.id = sg.table_id where ss.status <> 'INTACT' group by sg.segment_type |  ss.kind |  ss.status |  t.name |  active order by num_segments desc |  ss.kind |  ss.status |  t.name |  active) select * from the_user_query_to_add_a_limit_to limit 200" |
| admin@system | "2024-08-06T21:38:19.740" |           90 | "00000" | The operation completed successfully                                                                                              | with the_user_query_to_add_a_limit_to as (select * from sys.stored_segments) select * from the_user_query_to_add_a_limit_to limit 200 |             |                                       |                                                                                                                                    |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:37:35.181" |            5 | "42703" | The reference to referenced column is not valid (The column 'ss.kind' is being referenced incorrectly. Maybe a missing GROUP BY?) | "with the_user_query_to_add_a_limit_to as (select ss.kind                                                                             |  ss.status  |  (ss.end_osn = 'OSN_INFINITY') active |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:37:33.710" |            5 | "42703" | The reference to referenced column is not valid (The column 'ss.kind' is being referenced incorrectly. Maybe a missing GROUP BY?) | "with the_user_query_to_add_a_limit_to as (select ss.kind                                                                             |  ss.status  |  (ss.end_osn = 'OSN_INFINITY') active |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:36:03.692" |           11 | "42703" | The reference to referenced column is not valid (The column 'ss.kind' is being referenced incorrectly. Maybe a missing GROUP BY?) | "with the_user_query_to_add_a_limit_to as (select ss.kind                                                                             |  ss.status  |  (ss.end_osn = 'OSN_INFINITY') active |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" |                                                                                                                                    |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:35:51.301" |            4 | "42703" | The reference to column 't.name' is not valid                                                                                     | "with the_user_query_to_add_a_limit_to as (select ss.kind                                                                             |  ss.status  |  t.name                               |  (ss.end_osn = 'OSN_INFINITY') active                                                                                              |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200" |                                                                                                                                                                                                                                |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:35:10.331" |            5 | "42703" | The reference to column 'sg.segment_type' is not valid                                                                            | "with the_user_query_to_add_a_limit_to as (select sg.segment_type                                                                     |  ss.kind    |  ss.status                            |  t.name                                                                                                                            |  (ss.end_osn = 'OSN_INFINITY') active                                                                                              |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss) select * from the_user_query_to_add_a_limit_to limit 200"                                                                                             |          |            |         |                                    |          |            |         |                                                                    |
| admin@system | "2024-08-06T21:34:42.970" |          208 | "00000" | The operation completed successfully                                                                                              | "with the_user_query_to_add_a_limit_to as (select sg.segment_type                                                                     |  ss.kind    |  ss.status                            |  t.name                                                                                                                            |  (ss.end_osn = 'OSN_INFINITY') active                                                                                              |  count(distinct ss.storage_id) num_segments from sys.stored_segments ss join sys.segment_groups sg on sg.id = ss.segment_group_id join sys.tables t on t.id = sg.table_id where ss.status <> 'INTACT' group by sg.segment_type |  ss.kind |  ss.status |  t.name |  active order by num_segments desc |  ss.kind |  ss.status |  t.name |  active) select * from the_user_query_to_add_a_limit_to limit 200" |

Database and Table System Catalog Tables

For specific column definitions of the system catalog tables used in these examples, see the Databases section of the System Catalog page.

The Management UI Databases page shows database sizes, table counts, row counts, and compression ratios. Select a database to view its schemas, select a schema to view its tables, and select a table to view its columns, indexes, and constraints.

Database Sizes

Check the total size of all databases on your system.

SQL

SELECT TO_CHAR(SUM(sp.size), '999,999,999,999,999') AS totsize,
    d.name
    FROM sys.segment_parts AS sp
    JOIN sys.segment_groups AS sg
        ON sp.segment_group_id = sg.id
    JOIN sys.tables AS t
        ON sg.table_id = t.id
    JOIN sys.databases AS d
        ON t.database_id = d.id
 GROUP BY d.name
 ORDER BY 1 DESC;

Output

Text

| "totsize" | "name" |
|-----------|--------|
| 204276004 | test   |

Table Sizes

Check the total size of all tables on the system.

SQL

WITH
cte AS (
    SELECT
	    sg.id AS segment_group_id,
	    sp.name AS segment_name,
	    sp.part_type AS partition_type,
	    sp.size AS segment_size,
	    sg.segment_type,
	    sg.status,
	    t.schema,
	    t.name
    FROM sys.segment_parts AS sp
    INNER JOIN sys.segment_groups AS sg ON
	    sp.segment_group_id = sg.id
    INNER JOIN sys.tables AS t ON
	    sg.table_id = t.id ),
ctefinal AS (
    SELECT schema, name, SUM(segment_size) AS total_table_size
    FROM cte
    GROUP BY schema, name )
SELECT SCHEMA, name, total_table_size, SUM(total_table_size)
    OVER( ORDER BY total_table_size DESC ) AS running_total
    FROM ctefinal
    ORDER BY total_table_size DESC;

Output

Text

| "schema" |       "name"       | "total_table_size" | "running_total" |
|----------|--------------------|--------------------|-----------------|
| loading  | data_type_coverage |          204276004 |       204276004 |

Table Statistics

This query provides a breakdown of table statistics and partition information.

SQL

WITH
cte1 AS (
	SELECT
		sg.id AS segment_group_id,
		sp.name AS segment_name,
		sp.part_type AS partition_type,
		sp.size AS segment_size,
		sg.segment_type,
		sg.status,
		t.schema,
		t.name
	FROM sys.segment_parts AS sp
	INNER JOIN sys.segment_groups AS sg ON
		sp.segment_group_id = sg.id
	INNER JOIN sys.tables AS t ON
		sg.table_id = t.id ),
cte AS (
	SELECT
		COUNT(*) AS num_segments,
		SUM(segment_size) AS partition_size,
		partition_type,
		SUM(SUM(segment_size)) OVER(PARTITION BY schema, name) AS total_table_size,
		SUM(COUNT(*)) OVER(PARTITION BY schema, name) AS total_table_segments,
		schema,
		name
	FROM cte1
	GROUP BY
		partition_type,
		schema,
		name )
SELECT
    name,
    num_segments,
    partition_size,
    partition_type,
    CAST(FLOAT(partition_size)/(total_table_size + 1) * 100
        AS DECIMAL(6,3)) AS percent_of_total,
    total_table_size,
    total_table_segments
FROM cte
ORDER BY
    name,
    partition_size DESC;

Output

Text

|       "name"       | "num_segments" | "partition_size" |    "partition_type"    | "percent_of_total" | "total_table_size" | "total_table_segments" |
|--------------------|----------------|------------------|------------------------|--------------------|--------------------|------------------------|
| data_type_coverage |              3 |        113397760 | parity_data            |             59.827 |          189542692 |                     42 |
| data_type_coverage |              3 |         75599872 | data                   |             39.885 |          189542692 |                     42 |
| data_type_coverage |              3 |           172032 | skip_lists             |              0.091 |          189542692 |                     42 |
| data_type_coverage |              3 |           172032 | copy_skip_lists        |              0.091 |          189542692 |                     42 |
| data_type_coverage |              3 |            90112 | parity_stats           |              0.048 |          189542692 |                     42 |
| data_type_coverage |              3 |            59210 | stats                  |              0.031 |          189542692 |                     42 |
| data_type_coverage |              3 |            12288 | table_of_contents      |              0.006 |          189542692 |                     42 |
| data_type_coverage |              3 |            12288 | parity_index           |              0.006 |          189542692 |                     42 |
| data_type_coverage |              3 |            12288 | copy_table_of_contents |              0.006 |          189542692 |                     42 |
| data_type_coverage |              3 |             8192 | index                  |              0.004 |          189542692 |                     42 |
| data_type_coverage |              3 |             4096 | parity_summary_stats   |              0.002 |          189542692 |                     42 |
| data_type_coverage |              3 |              906 | column_metadata        |              0.000 |          189542692 |                     42 |
| data_type_coverage |              3 |              906 | copy_column_metadata   |              0.000 |          189542692 |                     42 |
| data_type_coverage |              3 |              710 | summary_stats          |              0.000 |          189542692 |                     42 |

For information on the system catalog tables queried in this example, see sys.segment_groups and sys.segment_parts.

Compression

Check the information on the compression for each column in the specified table.

SQL

SELECT r."table",
    c.NAME,
	r.is_rle_enabled,
	r.is_ne_enabled,
	r.raw_size,
	r.compressed_size,
	r.is_deltadelta_enabled,
	r.num_deltadelta_blocks,
	r.num_rle_blocks,
	r.num_nerle_blocks,
	r.num_uncompressed_blocks,
	r.num_total_blocks
FROM sys.columns c,
	( SELECT t.name AS "table",
		t.id,
		cci.is_deltadelta_enabled,
		cci.is_rle_enabled,
		cci.is_ne_enabled,
		cci.raw_size,
		cci.compressed_size,
		cci.num_deltadelta_blocks,
		cci.num_rle_blocks,
		cci.num_nerle_blocks,
		cci.num_uncompressed_blocks,
		cci.num_total_blocks,
		cci.ordinal
	    FROM sys.tables AS t,
		    sys.columns_compression_info AS cci
		WHERE t.id = cci.table_id
		    AND t.name = 'my_table' ) AS r
WHERE r.id = c.table_id
    AND r.ordinal = c.ordinal;

Output

Text

|      "table"       |    "name"     | "is_rle_enabled" | "is_ne_enabled" | "raw_size" | "compressed_size" | "is_deltadelta_enabled" | "num_deltadelta_blocks" | "num_rle_blocks" | "num_nerle_blocks" | "num_uncompressed_blocks" | "num_total_blocks" |
|--------------------|---------------|------------------|-----------------|------------|-------------------|-------------------------|-------------------------|------------------|--------------------|---------------------------|--------------------|
| data_type_coverage | col_double    | true             | true            |     900000 |            905216 | false                   |                       0 |                0 |                  0 |                       220 |                220 |
| data_type_coverage | col_int       | true             | true            |     500000 |            507904 | true                    |                       0 |                0 |                  0 |                       123 |                123 |
| data_type_coverage | col_tinyint   | true             | true            |     200000 |            204800 | true                    |                       0 |                0 |                  0 |                        49 |                 49 |
| data_type_coverage | col_date      | true             | true            |     500000 |            294912 | true                    |                      71 |                0 |                  0 |                         0 |                 71 |
| data_type_coverage | col_decimal   | true             | true            |    1100000 |           1105920 | false                   |                       0 |                0 |                  0 |                       269 |                269 |
| data_type_coverage | col_ip        | true             | true            |    1700000 |           1712128 | false                   |                       0 |                0 |                  0 |                       417 |                417 |
| data_type_coverage | col_point     | true             | true            |    1700000 |           1712128 | false                   |                       0 |                0 |                  0 |                       417 |                417 |
| data_type_coverage | col_timestamp | true             | true            |     900000 |            905216 | true                    |                       0 |                0 |                  0 |                       220 |                220 |
| data_type_coverage | col_bigint    | true             | true            |     900000 |            905216 | true                    |                       0 |                0 |                  0 |                       220 |                220 |
| data_type_coverage | col_binary    | true             | true            |     300000 |            307200 | false                   |                       0 |                0 |                  0 |                        74 |                 74 |
| data_type_coverage | col_boolean   | true             | true            |     200000 |            106496 | true                    |                      19 |                0 |                  0 |                         6 |                 25 |
| data_type_coverage | col_ipv4      | true             | true            |     500000 |            507904 | true                    |                       0 |                0 |                  0 |                       123 |                123 |
| data_type_coverage | col_time      | true             | true            |     900000 |            905216 | true                    |                       0 |                0 |                  0 |                       220 |                220 |
| data_type_coverage | col_float     | true             | true            |     500000 |            507904 | false                   |                       0 |                0 |                  0 |                       123 |                123 |
| data_type_coverage | col_uuid      | true             | true            |    1700000 |           1712128 | false                   |                       0 |                0 |                  0 |                       417 |                417 |
| data_type_coverage | col_smallint  | true             | true            |     300000 |            307200 | true                    |                       0 |                0 |                  0 |                        74 |                 74 |

For information on the system catalog table queried in this example, see sys.columns_compression_info.

Compression for the Entire Table

This query summarizes the total compression of the specified table. In the example, replace the table name and schema name accordingly.

SQL

SELECT SUM(raw_size),
    SUM(compressed_size)
FROM (
    SELECT t.name,
        c.name AS colname,
        co.raw_size,
        co.compressed_size,
        SUM(co.compressed_size) OVER(PARTITION BY t.schema, t.name) AS table_size,
        100 -((100 * co.compressed_size) / co.raw_size) AS col_compress_percent,
        DECIMAL(
           FLOAT(co.compressed_size) * 100 / SUM(co.compressed_size)
           OVER(PARTITION BY t.schema, t.name),
           3,
           1
           ) AS percent_of_table
    FROM (
        SELECT table_id,
            ordinal,
            raw_size,
            compressed_size
        FROM sys.columns_compression_info
        UNION ALL
        SELECT table_id,
            ordinal,
            raw_size,
            compressed_size
        FROM sys.vl_columns_compression_info
      ) AS co
    JOIN sys.tables AS t ON co.table_id = t.id
    JOIN sys.columns AS c ON c.table_id = t.id
        AND co.ordinal = c.ordinal
    WHERE t.name = 'data_type_coverage'
        AND t.schema = 'loading'
    ORDER BY co.ordinal
    ) AS abc;

Output

Text

| "sum(raw_size)" | "sum(compressed_size)" |
|-----------------|------------------------|
|        73788914 |               73592306 |

For information on the system catalog table queried in this example, see sys.columns_compression_info.

Index Size

Check the total size of each index across the different segment groups. In this example, the table has three secondary indexes added before loading data.

SQL

SELECT p.name AS uuid,
    p.part_type,
    i.name AS index_name,
    SUM(p.size) AS sum_size
FROM (
    SELECT *
    FROM sys.segment_parts
    WHERE part_type LIKE 'index'
    ) AS p
    INNER JOIN sys.indexes i ON p.name = i.id
GROUP BY 1,2,3
ORDER BY 1,2;

Output

Text

|                 "uuid"                 | "part_type"  | "index_name" | "sum_size" |
|----------------------------------------|--------------|--------------|------------|
| "669cbcdf-772f-4c97-b250-0cc9e573cd7b" | index        | idx01        |    2527232 |
| cccf6f5e-96a8-4621-b947-f318d120f6a0   | index        | idx03        |     180224 |
| d75ae71a-207e-4723-a86c-ecffc798c7d0   | index        | idx02        |    3190784 |

For information on the system catalog tables queried in this example, see sys.indexes and sys.segment_parts.

Column Sizes

This query finds the size of all columns for the specified table. Before executing this query on your system, replace table_name in the example.

SQL

WITH cte AS (
    SELECT
	    table_id,
	    ordinal,
	    compressed_size
    FROM
	    sys.columns_compression_info
    UNION ALL
    SELECT
	    table_id,
	    ordinal,
	    compressed_size
    FROM
	    sys.vl_columns_compression_info )
SELECT
	( SELECT t.name
	    FROM sys.tables AS t
	    WHERE t.id = cte.table_id ) AS table_name,
	ordinal,
	compressed_size,
	( SELECT name
	    FROM sys.columns AS c
	    WHERE c.table_id = cte.table_id
		    AND c.ordinal = cte.ordinal ) AS name,
	DOUBLE(compressed_size) / SUM(compressed_size) OVER() AS percent_of_total
FROM cte
WHERE
	table_id IN (
	    SELECT id
	    FROM sys.tables
	    WHERE name LIKE 'table_name' )
ORDER BY compressed_size;

Output

Text

|    "table_name"    | "ordinal" | "compressed_size" |     "name"     |  "percent_of_total"   |
|--------------------|-----------|-------------------|----------------|-----------------------|
| data_type_coverage |        25 |              8192 |                |    0.0001082748327179 |
| data_type_coverage |        21 |              8192 |                |    0.0001082748327179 |
| data_type_coverage |         2 |            102400 | col_boolean    | 0.0013534354089736837 |
| data_type_coverage |        18 |            204800 | col_tinyint    | 0.0027068708179473675 |
| data_type_coverage |        19 |            266240 |                |  0.003518932063331578 |
| data_type_coverage |         4 |            294912 | col_date       | 0.0038978939778442096 |
| data_type_coverage |        12 |            307200 | col_smallint   |  0.004060306226921052 |
| data_type_coverage |         1 |            307200 | col_binary     |  0.004060306226921052 |
| data_type_coverage |        10 |            507904 | col_ipv4       | 0.0067130396285094715 |
| data_type_coverage |         7 |            507904 | col_float      | 0.0067130396285094715 |
| data_type_coverage |         8 |            507904 | col_int        | 0.0067130396285094715 |
| data_type_coverage |         3 |            699370 | col_char       |  0.009243673066151615 |
| data_type_coverage |        23 |            783147 | col_varbinary  |  0.010350965627260875 |
| data_type_coverage |         6 |            905216 | col_double     |  0.011964369015327365 |
| data_type_coverage |        17 |            905216 | col_timestamp  |  0.011964369015327365 |
| data_type_coverage |        16 |            905216 | col_time       |  0.011964369015327365 |
| data_type_coverage |         0 |            905216 | col_bigint     |  0.011964369015327365 |
| data_type_coverage |         5 |           1105920 | col_decimal    |  0.014617102416915785 |
| data_type_coverage |        13 |           1712128 | col_point      |  0.022629440038039995 |
| data_type_coverage |        22 |           1712128 | col_uuid       |  0.022629440038039995 |
| data_type_coverage |        11 |           1712128 | col_ip         |  0.022629440038039995 |
| data_type_coverage |        20 |           1784388 |                |   0.02358451076706771 |
| data_type_coverage |        24 |           1788957 | col_varchar    |  0.023644899891907562 |
| data_type_coverage |        14 |           8500000 | col_linestring |   0.11234571265894837 |
| data_type_coverage |         9 |          21477958 | col_int_array  |   0.28387723505517193 |
| data_type_coverage |        15 |          27739482 | col_polygon    |    0.3666366910682436 |

This output is based on a query using the table data_type_coverage.

Column Cardinality

This query finds the cardinality of each column in the specified table. Knowing the cardinality is useful for determining which compression scheme to use for a column.

SQL

SELECT t.schema,
    t.name,
    c.name,
    c.ordinal,
    cc.cardinality
FROM sys.column_cardinalities AS cc
    LEFT JOIN sys.tables AS t ON cc.table_id = t.id
    LEFT JOIN sys.columns AS c ON cc.column_id = c.id
WHERE t.name LIKE 'data_type_coverage'
ORDER BY c.ordinal;

Output

Text

| "schema" |       "name"       |    "name_1"    | "ordinal" | "cardinality" |
|----------|--------------------|----------------|-----------|---------------|
| loading  | data_type_coverage | col_bigint     |         0 |         98551 |
| loading  | data_type_coverage | col_binary     |         1 |         48048 |
| loading  | data_type_coverage | col_boolean    |         2 |             2 |
| loading  | data_type_coverage | col_char       |         3 |         95206 |
| loading  | data_type_coverage | col_date       |         4 |         29634 |
| loading  | data_type_coverage | col_decimal    |         5 |         20059 |
| loading  | data_type_coverage | col_double     |         6 |         53546 |
| loading  | data_type_coverage | col_float      |         7 |         75680 |
| loading  | data_type_coverage | col_int        |         8 |         99319 |
| loading  | data_type_coverage | col_int_array  |         9 |       5000855 |
| loading  | data_type_coverage | col_int_array  |         9 |         99899 |
| loading  | data_type_coverage | col_ipv4       |        10 |         99908 |
| loading  | data_type_coverage | col_ip         |        11 |         99875 |
| loading  | data_type_coverage | col_smallint   |        12 |         51227 |
| loading  | data_type_coverage | col_point      |        13 |        100000 |
| loading  | data_type_coverage | col_linestring |        14 |        100000 |
| loading  | data_type_coverage | col_polygon    |        15 |         99153 |
| loading  | data_type_coverage | col_time       |        16 |         99902 |
| loading  | data_type_coverage | col_timestamp  |        17 |         99829 |
| loading  | data_type_coverage | col_tinyint    |        18 |           256 |
| loading  | data_type_coverage | col_uuid       |        22 |         99892 |
| loading  | data_type_coverage | col_varbinary  |        23 |         68834 |
| loading  | data_type_coverage | col_varchar    |        24 |         93258 |
| loading  | data_type_coverage |                |           |         10410 |
| loading  | data_type_coverage |                |           |             2 |
| loading  | data_type_coverage |                |           |             1 |
| loading  | data_type_coverage |                |           |         93333 |
| loading  | data_type_coverage |                |           |             1 |

For information on the system catalog table queried in this example, see sys.column_cardinalities.

Cluster and Node System Catalog Tables

For specific column definitions of the system catalog tables referenced in these examples, see the System section of the System Catalog page.

The Management UI Nodes page shows health, status, type, storage utilization, uptime, and software version for every node in the system. Select a node (from this page or from the Ocient Dashboard page) to view its details and configuration.

Nodes Present on a System

Check the status of all nodes in the system.

SQL

SELECT name,
    status
FROM sys.nodes;

Output

Text

|   "name"    | "status" |
|-------------|----------|
| foundation2 | ACCEPTED |
| sql         | ACCEPTED |
| loader      | ACCEPTED |
| foundation1 | ACCEPTED |
| foundation0 | ACCEPTED |

Nodes Roles on a System

Check the roles of each node in the system.

SQL

SELECT
	n.name, n.status,
	STRING_AGG(DISTINCT c.name, ', ' ORDER BY c.name) in_clusters,
	STRING_AGG(DISTINCT sr.service_role_type, ', ' ORDER BY sr.service_role_type) with_roles
FROM
	sys.nodes n
	LEFT JOIN sys.node_clusters nc ON nc.node_id = n.id
	LEFT JOIN sys.clusters c ON c.id = nc.cluster_id
	LEFT JOIN sys.service_roles sr ON sr.node_id = n.id
GROUP BY
	n.name, n.status
ORDER BY
	n.name;

Output

Text

|   "name"    | "status" |    "in_clusters"    |      "with_roles"       |                |             |              |
|-------------|----------|---------------------|-------------------------|----------------|-------------|--------------|
| foundation0 | ACCEPTED | "foundation_cluster |  foundation_cluster-vm" | "health        |  lts        |  operatorvm" |
| foundation1 | ACCEPTED | "foundation_cluster |  foundation_cluster-vm" | "health        |  lts        |  operatorvm" |
| foundation2 | ACCEPTED | "foundation_cluster |  foundation_cluster-vm" | "health        |  lts        |  operatorvm" |
| loader      | ACCEPTED |                     | "health                 |  streamloader" |             |              |
| sql         | ACCEPTED | initial-vm          | "admin                  |  health        |  operatorvm |  sql"        |

Node Status

Check the operational status, software versions, and assigned roles of your nodes. Possible status values include ACTIVE, STARTING, STOPPING, ERROR, UNKNOWN, or UNREACHABLE.

SQL

SELECT name,
    operational_status,
    software_version,
    ARRAY_AGG(service_role_type)
FROM sys.node_status AS ns
    LEFT JOIN sys.nodes AS n ON ns.node_id = id
    LEFT JOIN sys.service_roles AS sr ON sr.node_id = n.id
GROUP BY name,
    operational_status,
    software_version;

Output

Text

|    name     | operational_status | software_version | array_agg(service_role_type:) |
|-------------|--------------------|------------------|-------------------------------|
| loader      | Active             |           24.0.0 | ['health','streamloader']     |
| foundation2 | Active             |           24.0.0 | ['health','lts','operatorvm'] |

Foundation Nodes on a Specific Cluster

Check for all Foundation Nodes on your system clusters.

SQL

SELECT n.name,
    r.name
FROM sys.nodes AS n,
    ( SELECT *
        FROM sys.clusters AS c,
            sys.node_clusters AS nc
        WHERE c.cluster_type = 'Foundation'
            AND c.id = nc.cluster_id ) AS r
WHERE r.node_id = n.id;

Output

Text

| foundation2 | foundation_cluster |
|-------------|--------------------|
| foundation0 | foundation_cluster |
| foundation1 | foundation_cluster |

Drive Status

Check the status of all drives assigned to nodes in the system.

SQL

SELECT
    n.name AS node_name,
    s.node_id,
    s.id AS serial_number,
    s.pci_address,
    s.device_status,
    s.device_model
FROM
    sys.nodes AS n
JOIN sys.storage_device_status AS s
    ON n.id = s.node_id;

Output

Text

| "node_name" |               "node_id"                |            "serial_number"             |                      "pci_address"                       | "device_status" | "device_model" |
|-------------|----------------------------------------|----------------------------------------|----------------------------------------------------------|-----------------|----------------|
| foundation1 | "6a96b307-57bc-477f-952c-e6cab38a7922" | "72062649-396c-4424-9da5-c90cf0589e00" | /var/opt/ocient/72062649-396c-4424-9da5-c90cf0589e00.dat | ""              | NON-NVME DRIVE |
| foundation1 | "6a96b307-57bc-477f-952c-e6cab38a7922" | ee2f0013-c7b7-4750-833e-3d717af85001   | /var/opt/ocient/ee2f0013-c7b7-4750-833e-3d717af85001.dat | ""              | NON-NVME DRIVE |
| loader      | "83f0ecf9-9a31-4a56-85b9-7eb610447185" | ff3186fb-502f-417c-bcab-8b7aa3de8800   | /var/opt/ocient/ff3186fb-502f-417c-bcab-8b7aa3de8800.dat | ""              | NON-NVME DRIVE |
| loader      | "83f0ecf9-9a31-4a56-85b9-7eb610447185" | "11ff09e2-a6c1-4f76-ae96-3a97eaf02a01" | /var/opt/ocient/11ff09e2-a6c1-4f76-ae96-3a97eaf02a01.dat | ""              | NON-NVME DRIVE |
| foundation0 | "9330a0b3-b3b7-4503-949c-043b196c0cc4" | "6156962f-fcf6-4299-bbd7-2618fc6f1d00" | /var/opt/ocient/6156962f-fcf6-4299-bbd7-2618fc6f1d00.dat | ""              | NON-NVME DRIVE |
| foundation0 | "9330a0b3-b3b7-4503-949c-043b196c0cc4" | e6a4b24f-0d49-4704-b7ce-18af163c0701   | /var/opt/ocient/e6a4b24f-0d49-4704-b7ce-18af163c0701.dat | ""              | NON-NVME DRIVE |
| sql         | "1198a86f-549d-4736-ac31-1ba54a2b02e7" | "37203a6c-9deb-47cf-8194-95c4eecd7300" | /var/opt/ocient/37203a6c-9deb-47cf-8194-95c4eecd7300.dat | ""              | NON-NVME DRIVE |
| sql         | "1198a86f-549d-4736-ac31-1ba54a2b02e7" | "0921d62f-a31c-4681-9a56-82cb1450a401" | /var/opt/ocient/0921d62f-a31c-4681-9a56-82cb1450a401.dat | ""              | NON-NVME DRIVE |
| foundation2 | babadc9c-f2a3-4512-ab9a-bab3fd88a544   | "842881df-b71c-4cfc-847b-19517e8b2800" | /var/opt/ocient/842881df-b71c-4cfc-847b-19517e8b2800.dat | ""              | NON-NVME DRIVE |
| foundation2 | babadc9c-f2a3-4512-ab9a-bab3fd88a544   | fc0e490a-a295-491d-b6a6-8b3c153a7301   | /var/opt/ocient/fc0e490a-a295-491d-b6a6-8b3c153a7301.dat | ""              | NON-NVME DRIVE |

Roles, Privileges, and Service Class System Catalog Tables

For specific column definitions of the system catalog tables referenced in these examples, see the User Management section of the System Catalog page.

Group Assignment for Users

Check for all users on the system and their corresponding assigned group.

SQL

SELECT g.name AS group_name,
    r.user_name
FROM sys.groups AS g,
    ( SELECT u.user_name,
        ug.group_id
        FROM sys.users AS u,
            sys.user_groups AS ug
        WHERE u.id = ug.user_id ) AS r
ORDER BY g.name ASC;

Output

Text

| group_name | user_name |
|------------|-----------|
| DBA        | Humphrey  |

User Roles

Check for all users on the system and their assigned role.

SQL

SELECT rl.name AS role_name,
    r.user_name
FROM sys.roles AS rl,
    ( SELECT u.user_name,
        ur.role_id
        FROM sys.users AS u,
            sys.user_roles AS ur
        WHERE u.id = ur.user_id ) AS r
ORDER BY rl.name ASC;

Output

Text

|       role_name        | user_name |
|------------------------|-----------|
| system administrator   | Humphrey  |
| system analyst         | Grimey    |
| database administrator | Charles   |
| database analyst       | Edna      |

User Privileges

Check the privileges for the specific table name. Before executing this query on your system, replace username in the example.

SQL

SELECT t.schema AS db_name,
    t.name AS table_name,
    r.privilege
FROM sys.tables AS t,
    ( SELECT object_type,
        object_id,
        privilege
        FROM sys.PRIVILEGES
        WHERE grantee = 'username' ) AS r
WHERE r.object_id = t.id
ORDER BY t.schema, t.name ASC;

Output

Text

|  db_name |     table_name     |  privilege  |
|----------|--------------------|-------------|
| loading  | data_type_coverage | VIEW        |
| loading  | data_type_coverage | SELECT      |

Privileges for the Specific Table

Check the granted privileges across all database objects of the specific type, such as GROUP, TABLE, or DATABASE. In this example, the query checks for TABLE privileges.

SQL

SELECT p.grantee,
    t.schema,
    t.name,
    p.privilege
FROM sys.tables AS t,
    sys.privileges AS p
WHERE object_type = 'TABLE'
    AND p.object_id = t.id;

Output

Text

| admin@system | admin@system |   example_table    | DELETE  |
|--------------|--------------|--------------------|---------|
| admin@system | admin@system | example_table      | DROP    |
| admin@system | admin@system | example_table      | SYSAUTH |
| admin@system | admin@system | example_table      | LOAD    |
| admin@system | admin@system | example_table      | INSERT  |
| admin@system | admin@system | example_table      | ALTER   |
| admin@system | admin@system | example_table      | SELECT  |
| admin@system | admin@system | example_table      | VIEW    |

Groups Assigned to Service Classes

Check for the service class assignment for all groups.

SQL

SELECT g.name,
    sc.name
FROM sys.groups AS g,
    sys.service_classes AS sc
WHERE g.service_class_id = sc.id;

Output

Text

|  group  |    sc_name    |
|---------|---------------|
| analyst | high_priority |
| dbadmin | low_priority  |

Service Class Settings

Check for the settings for the specified service class. Before executing this query on your system, replace the high priority service class name in the example. For details about service class settings, see Workload Management and Service Classes.

SQL

SELECT *
FROM sys.groups AS g,
    sys.service_classes AS sc
WHERE g.service_class_id = sc.id
    AND sc.name = 'high_priority';

Output

Text

| id                                   | name    | database_id                          | service_class_id                     | id_1                                 | database_id_1                        | name_1        | max_temp_disk_usage | max_elapsed_time | max_concurrent_queries | max_rows_returned | scheduling_priority | cache_max_bytes | cache_max_time | max_elapsed_time_for_caching | max_columns_in_result_set | priority_adjustment_factor | priority_adjustment_time | min_priority | max_priority | statement_text | statement_text_matcher_type | half_parallelism | load_balance_shuffle | parallelism | memory_optimal_strategy |
|--------------------------------------|---------|--------------------------------------|--------------------------------------|--------------------------------------|--------------------------------------|---------------|---------------------|------------------|------------------------|-------------------|---------------------|-----------------|----------------|------------------------------|---------------------------|----------------------------|--------------------------|--------------|--------------|----------------|-----------------------------|------------------|----------------------|-------------|-------------------------|
| e9c92a1b-32a8-4f88-ab72-485ff8b24f53 | analyst | e80010d7-6f26-438c-8461-11af309ed8a3 | 83b76f87-6634-4cc9-8252-9300caeefdf1 | 83b76f87-6634-4cc9-8252-9300caeefdf1 | e80010d7-6f26-438c-8461-11af309ed8a3 | high_priority | 80                  | 100              | 10                     | 100               | 5                   | 1,000           | 25             | 50                           | -1                        | 0                          | 0                        | 0            | -1           |                |                             |                  |                      |             | false                   |

Service Classes for the Specific User

Check for the service class of the specific user. Before executing this query on your system, replace the jmack@test username in this example with the specific username that you want to query.

SQL

SELECT r2.name,
    sc.name,
    r2.user_name
FROM sys.service_classes AS sc,
    ( SELECT g.name,
        g.service_class_id,
        r.user_name
        FROM sys.groups AS g,
            ( SELECT u.user_name,
                ug.group_id
                FROM sys.users AS u,
                    sys.user_groups AS ug
                WHERE u.id = ug.user_id
                    AND u.user_name = 'jmack@test' ) AS r ) AS r2;

Output

Text

|  group  |  service_class  |  user_name  |
|---------|-----------------|-------------|
| analyst | Default         | jmack@test  |
| analyst | high_priority   | jmack@test  |

Data Pipelines

Pipeline Events

While your pipeline is running, the pipeline generates events in the sys.pipeline_events system catalog table to mark significant checkpoints when something has occurred. In this example, you execute the CREATE PIPELINE and START PIPELINE SQL statements on the pipeline named my_pipeline and let the pipeline complete. As expected, the CREATED, STARTED, and COMPLETED events appear. For details about the lists of files, see the sys.pipeline_files system catalog table. Pipeline events include messages from many different tasks. These events are the background processes that execute across different Loader Nodes during pipeline operation. For details about tasks, you can query the sys.tasks and sys.subtasks system catalog tables.

SQL

SELECT *
FROM sys.pipeline_events
ORDER BY event_timestamp;

Output

SQL

+--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------+
| pipeline_id                          | task_id                              | user_id                              | event_type             | event_message                                                         | event_timestamp            |
|--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------|
| d8848467-9262-4bd6-84a7-1380c95f8b8b |                                      | dde90d1b-bfcf-4b48-a251-b9ea812a7b26 | CREATED                | Created pipeline my_pipeline                                          | 2024-02-07 18:04:32.822690 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b |                                      | dde90d1b-bfcf-4b48-a251-b9ea812a7b26 | STARTED                | Started processing pipeline my_pipeline                               | 2024-02-07 18:39:52.682276 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b |                                      |                                      | FILE_LISTING_STARTED   | File listing started for pipeline my_pipeline. This may take a while. | 2024-02-07 18:39:52.848000 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b |                                      |                                      | FILE_LISTING_COMPLETED | File listing completed for pipeline my_pipeline. 1 files were listed. | 2024-02-07 18:39:52.863000 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b | 61007c85-6744-42e7-9401-66e1a594e990 |                                      | EXTRACTION_STARTED     | Extraction started                                                    | 2024-02-07 18:39:58.367000 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b | 61007c85-6744-42e7-9401-66e1a594e990 |                                      | EXTRACTION_COMPLETED   | Extraction completed                                                  | 2024-02-07 18:39:59.417000 |
| d8848467-9262-4bd6-84a7-1380c95f8b8b | 49c17f79-0108-4b05-aae5-9ae9a13abaca |                                      | COMPLETED              | Completed processing pipeline my_pipeline                             | 2024-02-07 18:40:03.808543 |
+--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------+
Fetched 7 rows

Pipeline Metrics

Find metrics that measure the performance and activity of a pipeline in the sys.pipeline_metrics system catalog table. This table contains one row for each metric at a specified point in time. A common use of this data is to plot metric values over time to examine the behavior of a pipeline. Metric Types Metrics are either instantaneous or incremental:

Instantaneous metrics reflect the current value of a counter at the time of the metric collection. These values can increase or decrease based on the current state or rate of the metric.
Incremental metrics increment over time and reflect the cumulative value for a specified counter.

Example The first snapshot of a metric appears when it is initialized. Afterward, the metric is updated every 10 seconds. The updated_at column shows the metric collection time. For example, if you are interested in the number of bytes of raw source data that the pipeline has ingested, you can execute this query. The value increases over time as the pipeline runs.

SQL

SELECT *
FROM sys.pipeline_metrics
WHERE name = 'count.source.bytes.ingested'
ORDER BY updated_at;

Output

SQL

+--------------------------------------+--------------------------------------+----------------+--------------+-----------------------------+-------------+----------------------------+
| pipeline_id                          | extractor_task_id                    | partition_id   | sink_index   | name                        |       value | updated_at                 |
|--------------------------------------+--------------------------------------+----------------+--------------+-----------------------------+-------------+----------------------------|
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.source.bytes.ingested |  7774214710 | 2024-02-07 22:35:27.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.source.bytes.ingested | 16529202619 | 2024-02-07 22:35:37.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.source.bytes.ingested | 25077088214 | 2024-02-07 22:35:47.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.source.bytes.ingested | 33425410618 | 2024-02-07 22:35:57.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.source.bytes.ingested | 41818859671 | 2024-02-07 22:36:07.546000 |
+--------------------------------------+--------------------------------------+----------------+--------------+-----------------------------+-------------+----------------------------+
Fetched 5 rows

Metric Scope Each metric has a scope that defines its uniqueness. Be careful not to aggregate metrics across different scopes. The scope columns are pipeline_id, extractor_task_id, partition_id, and sink_index. If the value of any of these columns is NULL, the scope applies to all values in that dimension. For example, because the sink_index column is NULL for the metric count.source.bytes.ingested, the metric applies to the pipeline and file specified by the pipeline_id, extractor_task_id, partition_id, and file_index columns across all sinks. An extractor_task_id is the internal identifier used when the Ocient System executes a pipeline process on a Loader Node. A pipeline might have many of these processes across time and loaders. Example Retrieve the most recent snapshot for each metric and aggregate the values to produce a single value for a pipeline.

SQL

SELECT pipeline_id, name, SUM(most_recent_value) AS total_value
FROM (
    SELECT DISTINCT * FROM (
        SELECT pipeline_id, extractor_task_id, partition_id, name,
        first_value (value) OVER
            (PARTITION BY pipeline_id, extractor_task_id, partition_id, name
             ORDER BY updated_at DESC) AS most_recent_value
        FROM sys.pipeline_metrics
        WHERE name LIKE 'count%'
    )
)
GROUP BY pipeline_id, name;

Output

SQL

+--------------------------------------+--------------------------------+---------------+
| pipeline_id                          | name                           |   total_value |
|--------------------------------------+--------------------------------+---------------|
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.file.total               |            64 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.source.bytes.ingested    |      33644961 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.file.processed           |            64 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.record.sent              |        100000 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.record.durable           |        100000 |
+--------------------------------------+--------------------------------+---------------+
Fetched 5 rows

This output contains a single row for each metric for each pipeline. If you run a pipeline with 64 files and 100,000 records, the output might look like this. This table explains each column in the output.

Transactional Data Pipelines

When you load data with transactional data pipelines, the Ocient System records the transaction identifier of each SQL statement and pipeline in the transaction_id column of the sys.queries, sys.completed_queries, and sys.pipelines system catalog tables, and the information_schema.pipelines view. After a transactional pipeline completes, the system retains its rows in historical objects such as the information_schema.pipelines_historical view and sys.pipeline_metrics_historical system catalog table. You can join these objects to monitor the progress of active transactions and review the history of committed and rolled-back transactions. A parent transaction groups the SQL statements that execute inside it. Use the UUID_PREFIX function with a prefix length of 64 on the transaction_id column to find the parent transaction of any statement. Rows where the transaction_prefix_len column is 64 belong to the parent transaction, such as COMMIT and ROLLBACK statements. Rows where the value is 96 are child statements that execute inside the transaction, such as CREATE TRANSACTIONAL PIPELINE statements. For details about transactional pipelines, see Transactional Data Pipelines. Monitor Progress of Active Transactions Track the aggregate progress of each active transaction for both parent and child transactions. This query counts the pipelines in each transaction, sums the records that the pipelines have loaded and failed, and calculates the elapsed time. The query excludes transactions that have already committed or rolled back. If you execute the query every few seconds, you can watch the number of pipelines and loaded records increase.

SQL

WITH
    txn_queries AS (
        SELECT timestamp_start,
            transaction_id,
            transaction_prefix_len,
            sql
        FROM sys.queries
        WHERE transaction_id IS NOT NULL
        UNION ALL
        SELECT timestamp_start,
            transaction_id,
            transaction_prefix_len,
            sql
        FROM sys.completed_queries
        WHERE transaction_id IS NOT NULL
    ),
    terminated_parents AS (
        SELECT DISTINCT
            UUID_PREFIX (transaction_id, 64) AS parent_txn
        FROM txn_queries
        WHERE transaction_prefix_len = 64
        AND ( LOWER(sql) LIKE 'commit%'
            OR LOWER(sql) LIKE 'rollback%' )
    ),
    txn_pipelines AS (
        SELECT id,
            status,
            loading_mode,
            transaction_id,
            loaded_files_count,
            loaded_with_errors_files_count,
            skipped_files_count,
            failed_files_count,
            pending_files_count,
            queued_files_count,
            loading_files_count
        FROM sys.pipelines
        WHERE transaction_id IS NOT NULL
        UNION ALL
        SELECT pipeline_id,
            status,
            loading_mode,
            transaction_id,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL,
            NULL
        FROM information_schema.pipelines_historical
        WHERE transaction_id IS NOT NULL
    ),
    pipeline_records AS (
        SELECT pipeline_id,
            MAX( 
				CASE
                WHEN name = 'count.record.durable' THEN total_value
                ELSE 0
                END ) AS records_loaded,
            MAX(
                CASE
                WHEN name = 'count.record.error' THEN total_value
                ELSE 0
                END ) AS records_failed
        FROM (
            SELECT pipeline_id,
                name,
                SUM(most_recent_value) AS total_value
            FROM (
                SELECT DISTINCT pipeline_id,
                    extractor_task_id,
                    partition_id,
                    sink_index,
                    name,
                    FIRST_VALUE(value) OVER (
                        PARTITION BY pipeline_id,
                            extractor_task_id,
                            partition_id,
                            sink_index,
                            name
                        ORDER BY updated_at DESC ) AS most_recent_value
                FROM (
                    SELECT pipeline_id,
                        extractor_task_id,
                        partition_id,
                        sink_index,
                        name,
                        value,
                        updated_at
                    FROM sys.pipeline_metrics
                    WHERE name IN ('count.record.durable', 'count.record.error')
                    UNION ALL
                    SELECT pipeline_id,
                        extractor_task_id,
                        partition_id,
                        sink_index,
                        name,
                        value,
                        updated_at
                    FROM sys.pipeline_metrics_historical
                    WHERE name IN ('count.record.durable', 'count.record.error')
                ) all_metrics
            ) most_recent_metrics
            GROUP BY pipeline_id,
                name
        ) pipeline_metrics
        GROUP BY pipeline_id
    )
    SELECT q.timestamp_start,
        UUID_PREFIX (q.transaction_id, 64) AS parent_txn,
        CASE q.transaction_prefix_len
            WHEN 64 THEN 'parent'
            WHEN 96 THEN 'child'
            ELSE CAST(q.transaction_prefix_len AS VARCHAR)
        END AS txn_type,
        q.sql,
        p.status,
        CASE
            WHEN p.loading_mode = 'BATCH'
            AND (
                p.loaded_files_count + p.loaded_with_errors_files_count + p.skipped_files_count + p.failed_files_count + p.pending_files_count + p.queued_files_count + p.loading_files_count
            ) > 0 THEN FLOAT(
                p.loaded_files_count + p.loaded_with_errors_files_count + p.skipped_files_count + p.failed_files_count
                ) / (
                p.loaded_files_count + p.loaded_with_errors_files_count + p.skipped_files_count + p.failed_files_count + p.pending_files_count + p.queued_files_count + p.loading_files_count
                )
            ELSE NULL
        END AS fraction_complete,
        m.records_loaded,
        m.records_failed
    FROM txn_queries q
        LEFT JOIN txn_pipelines p ON p.transaction_id = q.transaction_id
        LEFT JOIN pipeline_records m ON m.pipeline_id = p.id
    WHERE
        UUID_PREFIX (q.transaction_id, 64) NOT IN (
            SELECT parent_txn
            FROM terminated_parents
        )
    ORDER BY parent_txn,
        q.timestamp_start DESC;

Output

SQL

+-----------------------------+--------------------------------------+----------+------------------------------------------------------+----------+-------------------+----------------+----------------+ | timestamp_start             | parent_txn                           | txn_type | sql                                                  | status   | fraction_complete | records_loaded | records_failed | |-----------------------------+--------------------------------------+----------+------------------------------------------------------+----------+-------------------+----------------+----------------| | 2026-08-04 15:02:10.113000  | 3f2504e0-4f89-41d3-9a0c-000000000000 | child    | create pipeline p_250 ... into sales.orders select   | STARTING |                   |                |                | | 2026-08-04 15:01:58.407000  | 3f2504e0-4f89-41d3-9a0c-000000000000 | child    | create pipeline p_249 ... into sales.orders select   | ACTIVE   | 0.42              | 98214          | 0              | | 2026-08-04 15:01:42.882000  | 3f2504e0-4f89-41d3-9a0c-000000000000 | child    | create pipeline p_248 ... into sales.orders select   | ACTIVE   | 0.67              | 155320         | 2              | | 2026-08-04 15:01:20.554000  | 3f2504e0-4f89-41d3-9a0c-000000000000 | child    | create pipeline p_247 ... into sales.orders select   | ACTIVE   | 1.0               | 100000         | 0              | | 2026-08-04 14:40:55.201000  | 3f2504e0-4f89-41d3-9a0c-000000000000 | parent   | start transaction                                    |          |                   |                |                | +-----------------------------+--------------------------------------+----------+------------------------------------------------------+----------+-------------------+----------------+----------------+ Fetched 254 rows

Summarize Transaction History Summarize all transactions on the system by state. This query classifies each parent transaction as committed, rolled_back, or active based on whether a COMMIT or ROLLBACK statement was executed in the transaction, and reports the number of parent transactions, the total number of statements, and the earliest and latest statement timestamps for each state.

SQL

WITH txn_queries AS (
    SELECT timestamp_start, transaction_id, transaction_prefix_len, sql
    FROM sys.queries
    WHERE transaction_id IS NOT NULL
    UNION ALL
    SELECT timestamp_start, transaction_id, transaction_prefix_len, sql
    FROM sys.completed_queries
    WHERE transaction_id IS NOT NULL
),
parent_status AS (
    SELECT UUID_PREFIX(transaction_id, 64) AS parent_txn,
        MAX(CASE WHEN transaction_prefix_len = 64
            AND LOWER(sql) LIKE 'commit%'
            THEN 1 ELSE 0 END) AS has_commit,
        MAX(CASE WHEN transaction_prefix_len = 64
            AND LOWER(sql) LIKE 'rollback%'
            THEN 1 ELSE 0 END) AS has_rollback,
        COUNT(*) AS stmts,
        MIN(timestamp_start) AS first_seen,
        MAX(timestamp_start) AS last_seen
    FROM txn_queries
    GROUP BY UUID_PREFIX(transaction_id, 64)
)
SELECT CASE
        WHEN has_commit = 1 THEN 'committed'
        WHEN has_rollback = 1 THEN 'rolled_back'
        ELSE 'active'
    END AS state,
    COUNT(*) AS parents,
    SUM(stmts) AS total_stmts,
    MIN(first_seen) AS earliest,
    MAX(last_seen) AS latest
FROM parent_status
GROUP BY 1
ORDER BY 1;

Output

SQL

+-------------+---------+-------------+-------------------------------+-------------------------------+
| state       | parents | total_stmts | earliest                      | latest                        |
|-------------+---------+-------------+-------------------------------+-------------------------------|
| active      |      32 |      141547 | 2026-06-01 15:01:51.548000000 | 2026-06-16 16:54:56.546000000 |
| committed   |    1157 |     1639722 | 2026-05-28 21:47:06.906000000 | 2026-07-22 15:02:23.716000000 |
| rolled_back |     435 |     2287969 | 2026-05-28 23:52:42.364000000 | 2026-07-22 14:01:38.669000000 |
+-------------+---------+-------------+-------------------------------+-------------------------------+
Fetched 3 rows

System Catalog Data Integrity and Storage Query Analysis Data Definition Language (DDL) Statement Reference Cluster and Node Management Users, Groups, and Service Classes

​Segment System Catalog Tables

​Segment Size

​Page and Segment Information

​Storage Spaces on a System

​Query Management System Catalog Tables

​Running Queries

​Recent Queries

​Database and Table System Catalog Tables

​Database Sizes

​Table Sizes

​Table Statistics

​Compression

​Compression for the Entire Table

​Index Size

​Column Sizes

​Column Cardinality

​Cluster and Node System Catalog Tables

​Nodes Present on a System

​Nodes Roles on a System

​Node Status

​Foundation Nodes on a Specific Cluster

​Drive Status

​Roles, Privileges, and Service Class System Catalog Tables

​Group Assignment for Users

​User Roles

​User Privileges

​Privileges for the Specific Table

​Groups Assigned to Service Classes

​Service Class Settings

​Service Classes for the Specific User

​Data Pipelines

​Pipeline Events

​Pipeline Metrics

​Transactional Data Pipelines

​Related Links

Segment System Catalog Tables

Segment Size

Page and Segment Information

Storage Spaces on a System

Query Management System Catalog Tables

Running Queries

Recent Queries

Database and Table System Catalog Tables

Database Sizes

Table Sizes

Table Statistics

Compression

Compression for the Entire Table

Index Size

Column Sizes

Column Cardinality

Cluster and Node System Catalog Tables

Nodes Present on a System

Nodes Roles on a System

Node Status

Foundation Nodes on a Specific Cluster

Drive Status

Roles, Privileges, and Service Class System Catalog Tables

Group Assignment for Users

User Roles

User Privileges

Privileges for the Specific Table

Groups Assigned to Service Classes

Service Class Settings

Service Classes for the Specific User

Data Pipelines

Pipeline Events

Pipeline Metrics

Transactional Data Pipelines

Related Links