Sign inTry Free

OBSERVE_DATABASE_OFFSETS

On this page

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. Refer to Change Data Capture for more information.

information_schema.OBSERVE_DATABASE_OFFSETS 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:

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:

    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.

    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;

Last modified: October 3, 2024

Was this article helpful?

On this page

Was this article helpful?