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_
: A comma-separated list of columns to return.filter Currently only wildcards are supported for this argument. The output also contains a few auxiliary columns. Refer to Auxiliary Columns for more information. -
table_
: Name of the table.filter -
format
: The required serialization format for the column data.The default is SQL
. -
BEGIN AT offset_
: The offset from which theconfig 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 theOBSERVE
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, theOBSERVE
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 theobserve_
engine variable.agg_ timeout_ secs 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_
is set toagg_ timeout_ secs 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 theobserve_
engine variable.leaf_ timeout_ secs 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.
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 | 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.OBSERVE
query may not be displayed while the query is running.OBSERVE
query and view the rows in the result set as they are returned by the query, use the --quick
option with the clients.
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 testAS SQLBEGIN AT('0000000000000088000000000000000E000000000000E06E',NULL,'0000000000000088000000000000000E000000000000E053',NULL)END AT TAIL;
Last modified: October 4, 2024