OBSERVE

Syntax

OBSERVE field_filter 
    FROM table_filter 
    [ AS format]
    [ BEGIN AT offset_config ]
    [ END AT TAIL ]


field_filter: [table.]field, ...
format: SQL
offset_config: [offset | NULL], ...  // number of partitions

Arguments

field_filter: A comma-separated list of columns to return. Currently only wildcards are supported for this argument. The output also contains a few auxiliary columns. Refer to Auxiliary Columns for more information.
table_filter: Name of the table.
format: The required serialization format for the column data. The default is SQL.
BEGIN AT offset_config: The offset from which the OBSERVE query must resume. Specify one offset per partition. If NULL is specified for a partition, the observation is resumed from the last observed offset.
END AT TAIL: The offset at which the OBSERVE query must stop observation. If END AT TAIL is specified, the observation stops once the offset reaches the end of database write-ahead log (WAL) for each partition. If the END AT clause is not specified, the OBSERVE query runs indefinitely.

Remarks

The OBSERVE command only streams committed data. Rolled back (aborted) and failed transactions are filtered and excluded.
To manually stop an OBSERVE query, use the KILL QUERY command.
To prevent the aggregator from terminating OBSERVE queries that are idle, use the observe_agg_timeout_secs engine variable. This engine variable specifies the maximum time (in seconds) that an OBSERVE query can remain idle on an aggregator node before the query is terminated. By default, observe_agg_timeout_secs is set to 600 seconds.

Note

OBSERVE queries do not block DDL operations on the aggregator. Hence, the timeout can be set to larger values.
To prevent long-running OBSERVE queries from blocking DDL operations, use the observe_leaf_timeout_secs engine variable. This engine variable specifies the maximum time an OBSERVE query can remain idle on leaf nodes before closing the connection. This timeout is only applicable to leaf partitions.
If the primary key in a columnstore table is not defined using the the PRIMARY KEY(column_name) clause or the PRIMARY KEY keyword in the CREATE TABLE statement, the OBSERVE query will not return the primary key as expected in the CDC stream. Instead the InternalId column is used.

Example

Example 1

The following example shows the output returned by an OBSERVE query for a sample dataset.

SQL

CREATE DATABASE dbTest;
USE dbTest;

CREATE TABLE tblEx(
    ID INT NOT NULL PRIMARY KEY,
    Code VARCHAR(4));

INSERT INTO tblEx(ID, Code) VALUES (1, "KorE");
INSERT INTO tblEx(ID, Code) VALUES (2, "PamY");
INSERT INTO tblEx(ID, Code) VALUES (3, "TabK");
UPDATE tblEx SET Code = "JonA" WHERE ID = 2;

SQL

OBSERVE * FROM tblEx;

+------------------------------------------------------------+-------------+-------------------+------------------------------------------------------------------------------------------------------+-------------+------------+-------+-------+
| Offset                                                     | PartitionId | Type              | TxId                                                                                                 | TxTimestamp | InternalId | ID    | Code  |
+------------------------------------------------------------+-------------+-------------------+------------------------------------------------------------------------------------------------------+-------------+------------+-------+-------+
| 0x00000000000000010000000000000000000000000000000000000001 |           0 | BeginSnapshot     | 0x00000000000000000000000000000000010000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000 |           1 | NULL       |  NULL | NULL  |
| 0x00000000000000010000000000000000000000000000104B00000001 |           0 | CommitSnapshot    | 0x00000000000000000000000000000000010000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000 |           1 | NULL       |  NULL | NULL  |
| 0x00000000000000040000000000000008000000000000803900000000 |           0 | BeginTransaction  | 0x01000000000000000000000000000000040000000000000008000000000000803900000000000000000000000000000000 |     1048596 | NULL       |  NULL | NULL  |
| 0x00000000000000040000000000000008000000000000804600000000 |           0 | Insert            | 0x01000000000000000000000000000000040000000000000008000000000000803900000000000000000000000000000000 |     1048596 | NULL       |     2 | PamY  |
| 0x00000000000000040000000000000008000000000000804700000000 |           0 | CommitTransaction | 0x01000000000000000000000000000000040000000000000008000000000000803900000000000000000000000000000000 |     1048596 | NULL       |  NULL | NULL  |
| 0x00000000000000010000000000000000000000000000000000000001 |           1 | BeginSnapshot     | 0x00000000000000000100000000000000010000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000 |           1 | NULL       |  NULL | NULL  |
| 0x00000000000000010000000000000000000000000000104B00000001 |           1 | CommitSnapshot    | 0x00000000000000000100000000000000010000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000 |           1 | NULL       |  NULL | NULL  |
| 0x00000000000000050000000000000005000000000000503900000000 |           1 | BeginTransaction  | 0x01000000000000000100000000000000050000000000000005000000000000503900000000000000000000000000000000 |     1048595 | NULL       |  NULL | NULL  |
| 0x00000000000000050000000000000005000000000000504600000000 |           1 | Insert            | 0x01000000000000000100000000000000050000000000000005000000000000503900000000000000000000000000000000 |     1048595 | NULL       |     1 | KorE  |
| 0x00000000000000050000000000000005000000000000504700000000 |           1 | CommitTransaction | 0x01000000000000000100000000000000050000000000000005000000000000503900000000000000000000000000000000 |     1048595 | NULL       |  NULL | NULL  |
| 0x00000000000000010000000000000000000000000000000000000001 |           4 | BeginSnapshot     | 0x00000000000000000400000000000000010000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000 |           1 | NULL       |  NULL | NULL  |
| 0x00000000000000010000000000000000000000000000104B00000001 |           4 | CommitSnapshot    | 0x00000000000000000400000000000000010000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000 |           1 | NULL       |  NULL | NULL  |
| 0x00000000000000010000000000000000000000000000000000000001 |           3 | BeginSnapshot     | 0x00000000000000000300000000000000010000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000 |           1 | NULL       |  NULL | NULL  |
| 0x00000000000000010000000000000000000000000000104B00000001 |           3 | CommitSnapshot    | 0x00000000000000000300000000000000010000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000 |           1 | NULL       |  NULL | NULL  |
| 0x00000000000000070000000000000008000000000000803900000000 |           3 | BeginTransaction  | 0x01000000000000000300000000000000070000000000000008000000000000803900000000000000000000000000000000 |     1048597 | NULL       |  NULL | NULL  |
| 0x00000000000000070000000000000008000000000000804600000000 |           3 | Insert            | 0x01000000000000000300000000000000070000000000000008000000000000803900000000000000000000000000000000 |     1048597 | NULL       |     3 | TabK  |
| 0x00000000000000070000000000000008000000000000804700000000 |           3 | CommitTransaction | 0x01000000000000000300000000000000070000000000000008000000000000803900000000000000000000000000000000 |     1048597 | NULL       |  NULL | NULL  |
| 0x00000000000000010000000000000000000000000000000000000001 |           2 | BeginSnapshot     | 0x00000000000000000200000000000000010000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000 |           1 | NULL       |  NULL | NULL  |
| 0x00000000000000010000000000000000000000000000104B00000001 |           2 | CommitSnapshot    | 0x00000000000000000200000000000000010000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000 |           1 | NULL       |  NULL | NULL  |
| 0x00000000000000040000000000000009000000000000903900000000 |           0 | BeginTransaction  | 0x01000000000000000000000000000000040000000000000009000000000000903900000000000000000000000000000000 |     1048598 | NULL       |  NULL | NULL  |
| 0x00000000000000040000000000000009000000000000904600000000 |           0 | Update            | 0x01000000000000000000000000000000040000000000000009000000000000903900000000000000000000000000000000 |     1048598 | NULL       |     2 | JonA  |
| 0x00000000000000040000000000000009000000000000904700000000 |           0 | CommitTransaction | 0x01000000000000000000000000000000040000000000000009000000000000903900000000000000000000000000000000 |     1048598 | NULL       |  NULL | NULL  |
| 0x00000000000000010000000000000000000000000000000000000001 |           5 | BeginSnapshot     | 0x00000000000000000500000000000000010000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000 |           1 | NULL       |  NULL | NULL  |
| 0x00000000000000010000000000000000000000000000104B00000001 |           5 | CommitSnapshot    | 0x00000000000000000500000000000000010000000000000000FFFFFFFFFFFFFFFF00000000000000000000000000000000 |           1 | NULL       |  NULL | NULL  |
+------------------------------------------------------------+-------------+-------------------+------------------------------------------------------------------------------------------------------+-------------+------------+-------+-------+

Note

By default, the singlestore and mysql command-line SQL clients buffer their output until a query completes. Therefore, the output of the OBSERVE query may not be displayed while the query is running. To view the unbuffered output of the OBSERVE query and view the rows in the result set as they are returned by the query, use the --quick option with the clients. Refer to mysql Client Options for more information.

Example 2

The following example starts observing from the specified offset for each partition and ends the observation once the offset reaches the end of database write-ahead log (WAL) for each partition in the SQL format.

SQL

OBSERVE * FROM test 
    AS SQL
    BEGIN AT 
        ('0000000000000088000000000000000E000000000000E06E', 
        NULL,
        '0000000000000088000000000000000E000000000000E053', 
        NULL)
    END AT TAIL;

OBSERVE

On this page

Syntax

Arguments

Remarks

Example

Example 1

Example 2

Was this article helpful?

On this page

Was this article helpful?