OBSERVE

Syntax

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

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.
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.
Although DDL changes are not supported, the OBSERVE query does not block DDL operations from completing. The OBSERVE query stops once a DDL event is observed on all the partitions, effectively aligning all partition streams within the database.
If the database is dropped, the OBSERVE query is terminated.

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
    END AT TAIL
    BEGIN AT 
        ('0000000000000088000000000000000E000000000000E06E', 
        NULL,
        '0000000000000088000000000000000E000000000000E053', 
        NULL);

OBSERVE

On this page

Syntax

Arguments

Remarks

Example

Example 1

Example 2

Was this article helpful?

On this page

Was this article helpful?