OBSERVE

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

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: July 11, 2024

Was this article helpful?