OBSERVE_DATABASE_OFFSETS

This view contains information on offsets for starting an OBSERVE query. It contains offsets for each partition (ordinal) of a database observed by an OBSERVE query. This view enables users to start OBSERVE queries from offsets that have not been processed (observed). Refer to Change Data Capture for more information.

Schema

Column Name	Description
`DATABASE_ID`	ID of a database
`DATABASE_NAME`	Name of the database.
`ORDINAL`	ID of the partition on the leaf node.
`OFFSET`	Offset of the `OBSERVE` query.
`OFFSET_TYPE`	Offset type. It has one of the following values: `log_tail`: Identifies the tail offset of the log for a database. This value is unique for a database for each partition. `snapshot_begin`: Identifies the beginning offset of a snapshot. `snapshot_end`: Identifies the end offset of a snapshot.

For example:

SQL

SELECT * FROM information_schema.OBSERVE_DATABASE_OFFSETS;

+-------------+---------------+---------+------------------------------------------------------------+----------------+
| DATABASE_ID | DATABASE_NAME | ORDINAL | OFFSET                                                     | OFFSET_TYPE    |
+-------------+---------------+---------+------------------------------------------------------------+----------------+
|           1 | x_db          |       0 | 0x00000000000000040000000000000002000000000000000000000000 | log_tail       |
|           1 | x_db          |       0 | 0x00000000000000010000000000000000000000000000104B00000000 | snapshot_end   |
|           1 | x_db          |       0 | 0x00000000000000010000000000000000000000000000000000000000 | snapshot_begin |
|           1 | x_db          |       1 | 0x00000000000000050000000000000002000000000000000000000000 | log_tail       |
|           1 | x_db          |       1 | 0x00000000000000010000000000000000000000000000104B00000000 | snapshot_end   |
|           1 | x_db          |       1 | 0x00000000000000010000000000000000000000000000000000000000 | snapshot_begin |
+-------------+---------------+---------+------------------------------------------------------------+----------------+

Examples

Query the tail offset for each partition, ordered by ORDINAL:

SQL

SELECT OFFSET 
FROM information_schema.OBSERVE_DATABASE_OFFSETS 
WHERE DATABASE_NAME='<database>' and OFFSET_TYPE='log_tail' 
ORDER BY ORDINAL;

Query the latest snapshot end offsets for each partition. Replace OFFSET_TYPE with snapshot_begin to get the latest snapshot begin offsets.

SQL

SELECT OFFSET FROM (
   SELECT *, ROW_NUMBER()
   OVER (PARTITION BY DATABASE_NAME, ORDINAL, OFFSET_TYPE 
   ORDER BY OFFSET DESC) AS Version
   FROM information_schema.OBSERVE_DATABASE_OFFSETS
   WHERE DATABASE_NAME='<database>' AND OFFSET_TYPE='snapshot_end')
WHERE Version=1 
ORDER BY ORDINAL;

OBSERVE_DATABASE_OFFSETS

On this page

Schema

Examples

Was this article helpful?

On this page

Was this article helpful?