Skip to navigation
SQL ReferenceSystem Catalog Reference

Discover Insights From System Catalog Tables

View as Markdown

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.

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.

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.

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

|  "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.

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

| "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.

SELECT *
FROM sys.storage_spaces;

Output

|                  "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.

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.

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

|               "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.

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

Output

|    "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.

Database Sizes

Check the total size of all databases on your system.

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

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

Table Sizes

Check the total size of all tables on the system.

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

| "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.

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

|       "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.

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 = 'chicago_docsis_arr_idx' ) AS r
WHERE r.id = c.table_id
    AND r.ordinal = c.ordinal;

Output

|      "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.

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

| "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.

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

|                 "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 running this query on your system, replace table_name in the example.

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

|    "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.

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

| "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.

Nodes Present on a System

Check the status of all nodes in the system.

SELECT name,
    status
FROM sys.nodes;

Output

|   "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.

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

|   "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.

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

|    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.

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

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

Drive Status

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

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

| "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.

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

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

User Roles

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

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

|       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 running on your system, replace username in the example.

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

|  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.

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

| 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.

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

Output

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

Service Class Settings

Check for the settings for the specified service class. Before running 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.

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

Output

| 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 running on your system, replace the jmack@test username in this example with the specific username that you want to query.

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

|  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.

SELECT *
FROM sys.pipeline_events
ORDER BY event_timestamp;

Output

+--------------------------------------+--------------------------------------+--------------------------------------+------------------------+-----------------------------------------------------------------------+----------------------------+
| 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 transformed record bytes, you can execute this query. The value increases over time as the pipeline runs.

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

Output

+--------------------------------------+--------------------------------------+----------------+--------------+--------------------------------+-------------+----------------------------+
| pipeline_id                          | extractor_task_id                    | partition_id   | sink_index   | name                           |       value | updated_at                 |
|--------------------------------------+--------------------------------------+----------------+--------------+--------------------------------+-------------+----------------------------|
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed |  7774214710 | 2024-02-07 22:35:27.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed | 16529202619 | 2024-02-07 22:35:37.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed | 25077088214 | 2024-02-07 22:35:47.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed | 33425410618 | 2024-02-07 22:35:57.546000 |
| 0f51f1c0-f251-4407-8393-0f9bcbf18f28 | 034cf49d-364b-42b5-ad0a-37b7c237f16e |                |              | count.record.bytes.transformed | 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 partition_id and sink_index columns are NULL for the metric count.record.bytes.transformed, its scope applies to the pipeline indicated by the pipeline_id and extractor_task_id across all sinks and partitions.

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.

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

+--------------------------------------+--------------------------------+---------------+
| pipeline_id                          | name                           |   total_value |
|--------------------------------------+--------------------------------+---------------|
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.file.total               |            64 |
| 0c7b32f1-37ca-43f7-96b6-69c266c978a5 | count.record.bytes.transformed |      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.

System Catalog

Data Integrity and Storage

Query Analysis

Data Definition Language (DDL) Statement Reference

Cluster and Node Management

Users, Groups, and Service Classes