Sign inTry Free

OBSERVE

On this page

Note

This is a Preview feature.

Returns the CDC stream for a table.

Refer to Change Data Capture for more information.

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.

Example

Example 1

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

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;
OBSERVE * FROM tblEx;
+----------------------------------------------------+-------------+--------------------+--------+--------------------------------------------------------------------------------------+--------------+----------------------+------+------+
| Offset                                             | PartitionId | Type               | Table  | TxId                                                                                 | TxPartitions | InternalId           | ID   | Code |
+----------------------------------------------------+-------------+--------------------+--------+--------------------------------------------------------------------------------------+--------------+----------------------+------+------+
| 0x0000000000000077000000000000000E000000000000E06E |           4 | BeginTransaction   |        | 0x0100000000000000040000000000000077000000000000000E000000000000E06E0000000000000000 |            1 |                    0 | NULL | NULL |
| 0x0000000000000077000000000000000E000000000000E087 |           4 | Insert             | tblEx  | 0x0100000000000000040000000000000077000000000000000E000000000000E06E0000000000000000 |            1 |  1152921504606846977 |    1 | KorE |
| 0x0000000000000077000000000000000E000000000000E088 |           4 | CommitTransaction  |        | 0x0100000000000000040000000000000077000000000000000E000000000000E06E0000000000000000 |            1 |                    0 |.NULL | NULL |
| 0x0000000000000077000000000000000F000000000000F039 |           4 | BeginTransaction   |        | 0x0100000000000000040000000000000077000000000000000F000000000000F0390000000000000000 |            1 |                    0 | NULL | NULL |
| 0x0000000000000077000000000000000F000000000000F052 |           4 | Insert             | tblEx  | 0x0100000000000000040000000000000077000000000000000F000000000000F0390000000000000000 |            1 |  1152921504606846978 |    3 | TabK |
| 0x0000000000000077000000000000000F000000000000F053 |           4 | CommitTransaction  |        | 0x0100000000000000040000000000000077000000000000000F000000000000F0390000000000000000 |            1 |                    0 | NULL | NULL |
| 0x0000000000000078000000000000000C000000000000C06E |           3 | BeginTransaction   |        | 0x0100000000000000030000000000000078000000000000000C000000000000C06E0000000000000000 |            1 |                    0 | NULL | NULL |
| 0x0000000000000078000000000000000C000000000000C087 |           3 | Insert             | tblEx  | 0x0100000000000000030000000000000078000000000000000C000000000000C06E0000000000000000 |            1 |  1152921504606846977 |    2 | PamY |
| 0x0000000000000078000000000000000C000000000000C088 |           3 | CommitTransaction  |        | 0x0100000000000000030000000000000078000000000000000C000000000000C06E0000000000000000 |            1 |                    0 | NULL | NULL |
| 0x0000000000000078000000000000000E000000000000E039 |           3 | BeginTransaction   |        | 0x0100000000000000030000000000000078000000000000000E000000000000E0390000000000000000 |            1 |                    0 | NULL | NULL |
| 0x0000000000000078000000000000000E000000000000E052 |           3 | Update             | tblEx  | 0x0100000000000000030000000000000078000000000000000E000000000000E0390000000000000000 |            1 |  1152921504606846977 |    2 | JonA |
| 0x0000000000000078000000000000000E000000000000E053 |           3 | CommitTransaction  |        | 0x0100000000000000030000000000000078000000000000000E000000000000E0390000000000000000 |            1 |                    0 | 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.

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

Last modified: August 2, 2024

Was this article helpful?

On this page

Was this article helpful?