To capture events from the ByteHouse Cloud Data Warehouse (CDW) Database for Data Copy and Sync between Data, or to forward these Events as Message streams to different consumers for Processing, ByteHouse provides the Binlog Function. Consuming ByteHouse CDW Binlog can enhance data reuse capabilities and allows detection of insert, delete, and update operations on related ByteHouse CDW tables. This article will explain how to subscribe to the Binlog and related operations in ByteHouse.

ByteHouse CDW Binlog is similar to MySQL Binlog and supports recording all data change event logs in the Database. With ByteHouse CDW Binlog, Data Copy and Sync can be achieved very conveniently and flexibly. However, ByteHouse CDW Binlog is typically only used for Data synchronization, while MySQL Binlog is also used for primary and secondary instance synchronization, data recovery, and other high-availability scenarios. Therefore, there are certain differences in their implementation, mainly reflected in the following aspects:

• ByteHouse CDW Binlog does not record DDL operations.
• ByteHouse CDW Binlog is more flexible, operates at the table level, can be enabled as needed, and allows different Binlog TTL (time-to-live) settings for different tables.
• ByteHouse CDW Binlog can be queried very easily.

Meanwhile, in big data scenarios, Flink can directly consume ByteHouse CDW Binlog. Compared to traditional data warehouse layering, Flink + ByteHouse CDW Binlog enables complete event-driven processing, achieving real-time processing from the operational data storage layer (ODS) to the data warehouse dimension layer (DWD), from DWD to the data service layer (DWS), and so on. This meets the requirements of layered governance, achieves unified storage, enhances data reuse, shortens end-to-end data processing latency, and provides users with a one-stop real-time data warehouse solution.
ByteHouse CDW Binlog recommended use cases are as follows:

• Target scenarios: support downstream subscription, for example, ByteHouse ELT calculation results flowing back to the TP Database, together with Flink supporting complete event-driven processing.
• Non-target scenarios: ByteHouse's own data synchronization/cross-region replication.

1. Please ensure that the ByteHouse CDW engine version you are using is v2.3.1 or above. You can log in to the ByteHouse CDW console, click the top Tenant management tab, and view the engine version in Basic info. Launching the Binlog function with an engine version lower than this will result in an error. To upgrade, please submit a ticket.

2. Currently, only CnchMergeTree is supported. For new tables, the ENGINE must be set to CnchMergeTree when creating; for existing tables, you can use the following command to query the table engine in use.

   SHOW CREATE TABLE your_table_name;
   

3. ByteHouse CDW Binlog is at the table level, and DDL actions are not recorded.

4. After ByteHouse CDW Binlog is activated, it cannot be terminated. Please evaluate business requirements before activating. To terminate after activation, please submit a ticket to contact the ByteHouse team.

5. Function list of supported (✅) and not supported (❌) Binlog output.

   Statement	Non-unique key table	Unique key table
   INSERT	✅
   UPSERT, partial column update	❌	✅
   UPDATE、DELETE	❌	✅
   DDL CREATE TABLE DROP/UNDROP TABLE ADD/DROP/MODIFY COLUMN TRUNCATE ALTER PARTITION / PART (ATTACH/DETACH/DROP)	❌
   TTL expression	❌ tip Data deleted by TTL expressions does not generate Binlog.

6. Currently, ByteHouse CDW Binlog only supports incremental data consumption. Unified full incremental consumption scenarios are not supported yet.

Binlog publishing and data storage #

After activating Binlog, when executing Data Manipulation Language (DML), ByteHouse will perform write, delete, or update operations on the original table, and simultaneously write to Binlog, converting the generated data into Binlog and assigning a BSN (Binlog Sequence Number).

• Data written to ByteHouse forms parts (data fragments), each fragment contains part of the data. Binlog data is also stored as parts, and the data of the original table and Binlog each have independent TTL and GC (garbage collection).
• Binlog publishing runs asynchronously. Background tasks periodically convert table data updates into Binlog and publish them (assigning BSN). After a table data change, the corresponding Binlog message is generally visible after 500ms to 5s.

Binlog field description #

Binlog records consist of Binlog system fields and user table fields. The specific field definitions are shown in the following table.

Parameter description (table-level configuration) #

ByteHouse Binlog supports configuration of the following parameters:

Enable Binlog #

• Example 1: Enable Binlog when creating a new table.

  CREATE TABLE binlog_test(
      `id` Int32,
      `a` Int64,
      `b` String
  )
  ENGINE = CnchMergeTree
  ORDER BY a
  UNIQUE KEY id
  SETTINGS
  enable_binlog = 1, -- Required, enable Binlog
  binlog_lifetime = 86400, -- Optional
  binlog_shard_num = 4, -- Optional
  binlog_row_deduplicate = false; -- Optional
  

• Example 2: Enable Binlog for existing tables.

  ALTER TABLE binlog_test MODIFY SETTING binlog_lifetime = 3600; -- Optional, adjust TTL
  ALTER TABLE binlog_test MODIFY SETTING binlog_shard_num = 4; -- Optional, adjust the number of shards
  ALTER TABLE binlog_test MODIFY SETTING enable_binlog = 1; -- Required, enable Binlog
  

After enabling Binlog, you can use ALTER TABLE to modify Binlog configuration.

Adjust Binlog TTL #

ALTER TABLE binlog_test MODIFY SETTING binlog_lifetime = 7200; 

Records with identical values before and after update do not output Binlog. #

ALTER TABLE binlog_test MODIFY SETTING binlog_row_deduplicate = true; 

ByteHouse supports viewing all Binlog data for a table, and also supports querying Binlog for a specified BSN range only. If you are not sure about the BSN range, you can first query the system table system.cnch_table_info to determine the BSN range to query. You can also query system tables system.cnch_binlogs to view detailed information about the corresponding binlog part. For example, you can query the number of shard records in the current binlog part in advance and customize your reading behavior accordingly to improve query efficiency.
After enabling Binlog, you can query the Binlog of a table by adding $binlog after the table name. For example, you can query the Binlog of db_test.binlog_test using the following SQL statement.

SELECT * FROM db_test.binlog_test$binlog; 

ByteHouse supports querying Binlog within a specified BSN range. When querying, you need to add a hint in the form bsn(table_name$binlog, A, B), indicating that you are querying the Binlog whose BSN range in the table is within (A,B]. For example, you can query the Binlog in db_test.binlog_test where BSN is 2 or 3 using the following command.

SELECT /*+ bsn(db_test.binlog_test$binlog, 1, 3) */ * FROM db_test.binlog_test$binlog; 

The following examples demonstrate how to query Binlog in several common scenarios.
Suppose we have already executed some DML operations on the table db_test.binlog_test:

INSERT INTO binlog_test VALUES (1,2,'string1-1'),(2,1,'string2-1'),(3,3,'string3-1');
INSERT INTO binlog_test VALUES (1,1,'string1-1'),(2,1,'string2-2'),(4,2,'string4-1');
DELETE FROM binlog_test WHERE id < 3; 

Example 1: Query all Binlog in the table #

Query all Binlog in db_test.binlog_test.

SELECT
    _binlog_bsn,
    _part_row_number,
    *
FROM `db_test.binlog_test$binlog`
ORDER BY _binlog_bsn ASC 

The query result is as follows:

┌─_binlog_bsn─┬─_part_row_number─┬─binlog_shard_id─┬─binlog_event_type─┬─id─┬─a─┬─b─────┐
│           1  │                0  │               1   │ I                  │  1   │ 2  │ string1-1 │
│           1  │                1  │               2   │ I                  │  2   │ 1  │ string2-1 │
│           1  │                2  │               3   │ I                  │  3   │ 3  │ string3-1 │
└────────┴────────────┴───────────┴────────────┴────┴──┴───────┘
┌─_binlog_bsn─┬─_part_row_number─┬─binlog_shard_id─┬─binlog_event_type─┬─id─┬─a─┬─b─────┐
│           2  │                0   │               1  │ U-                 │  1   │ 2  │ string1-1 │
│           2  │                1   │               1  │ U+                 │  1   │ 1  │ string1-1 │
│           2  │                2   │               1  │ I                  │  4   │ 2  │ string4-1 │
│           2  │                3   │               2  │ U-                 │  2   │ 1  │ string2-1 │
│           2  │                4   │               2  │ U+                 │  2   │ 1  │ string2-2 │
└────────┴─────────────┴──────────┴────────────┴────┴──┴───────┘
┌─_binlog_bsn─┬─_part_row_number─┬─binlog_shard_id─┬─binlog_event_type─┬─id─┬─a─┬─b─────┐
│           3  │                0  │               1   │ D                  │  1   │ 1  │ string1-1 │
│           3  │                1  │               2   │ D                  │  2   │ 1  │ string2-2 │
└────────┴────────────┴───────────┴────────────┴────┴──┴───────┘

Example 2: Query Binlog within a specified BSN range in the table #

Query the Binlog in db_test.binlog_test where BSN is 2 or 3.

SELECT /*+ bsn(db_test.binlog_test$binlog, 1, 3) */
    _binlog_bsn,
    _part_row_number,
    *
FROM `db_test.binlog_test$binlog`
ORDER BY _binlog_bsn ASC 

The query result is as follows:

┌─_binlog_bsn─┬─_part_row_number─┬─binlog_shard_id─┬─binlog_event_type─┬─id─┬─a─┬─b─────┐
│           2  │                0   │               1  │ U-                  │  1  │ 2  │ string1-1 │
│           2  │                1   │               1  │ U+                  │  1  │ 1  │ string1-1 │
│           2  │                2   │               1  │ I                   │  4  │ 2  │ string4-1 │
│           2  │                3   │               2  │ U-                  │  2  │ 1  │ string2-1 │
│           2  │                4   │               2  │ U+                  │  2  │ 1  │ string2-2 │
└────────┴─────────────┴──────────┴─────────────┴───┴──┴───────┘
┌─_binlog_bsn─┬─_part_row_number─┬─binlog_shard_id─┬─binlog_event_type─┬─id─┬─a─┬─b─────┐
│           3  │                0   │               1  │ D                  │  1   │ 1  │ string1-1 │
│           3  │                1   │               2  │ D                  │  2   │ 1  │ string2-2 │
└────────┴────────────┴───────────┴────────────┴────┴──┴───────┘

Example 3: Query Binlog within a specified BSN range and shard ID in the table #

Query the Binlog in db_test.binlog_test where BSN is 2 or 3 and binlog_shard_id is 2.

SELECT /*+ bsn(db_test.binlog_test$binlog, 1, 3) */
    _binlog_bsn,
    _part_row_number,
    *
FROM `db_test.binlog_test$binlog`
WHERE binlog_shard_id = 2
ORDER BY _binlog_bsn ASC 

The query result is as follows:

┌─_binlog_bsn─┬─_part_row_number─┬─binlog_shard_id─┬─binlog_event_type─┬─id─┬─a─┬─b─────┐
│           2  │                3  │               2   │ U-                 │  2  │ 1   │ string2-1 │
│           2  │                4  │               2   │ U+                 │  2  │ 1   │ string2-2 │
└────────┴────────────┴───────────┴────────────┴───┴───┴───────┘
┌─_binlog_bsn─┬─_part_row_number─┬─binlog_shard_id─┬─binlog_event_type─┬─id─┬─a─┬─b─────┐
│           3  │                1   │               2  │ D                   │  2  │ 1  │ string2-2 │
└────────┴────────────┴───────────┴─────────────┴───┴──┴───────┘

Example 4: Query Binlog of newly inserted data #

Query all newly inserted data in db_test.binlog_test (binlog_event_type is 'I'), and do not output Binlog system fields.

SELECT * EXCEPT (binlog_event_type, binlog_shard_id)
FROM `db_test.binlog_test$binlog`
WHERE binlog_event_type = 'I'

The query result is as follows:

┌─id─┬─a─┬─b─────┐
│  1  │ 2  │ string1-1 │
│  2  │ 1  │ string2-1 │
│  3  │ 3  │ string3-1 │
└───┴──┴───────┘
┌─id─┬─a─┬─b─────┐
│  4  │ 2   │ string4-1 │
└───┴───┴───────┘

ByteHouse CDW currently supports consuming ByteHouse CDW Binlog via Flink. For operation details, please refer to Real-time consumption of ByteHouse CDW Binlog with Flink.

ByteHouse provides two system tables, system.cnch_table_info and system.cnch_binlogs, to display the status information of a table's Binlog.

system.cnch_table_info #

Displays the current maximum and minimum BSN of a table.

Example: Query the maximum and minimum BSN of db_test.binlog_test.

SELECT
    database,
    table,
    min_bsn,
    max_bsn 
FROM system.cnch_table_info
WHERE (database = 'db_test') AND (table = 'binlog_test')

The query result is as follows:

┌─database─┬─table───┬─min_bsn─┬─max_bsn─┐
│ db_test   │ binlog_test│       10 │    20     │
└──────┴────────┴──────┴──────┘

system.cnch_binlogs #

Displays detailed information about each Binlog part of a table. When querying this system table, you must use equality filter conditions for database and table.

Example: Query information about all Binlog parts of db_test.binlog_test.

SELECT
    database,
    table,
    bsn,
    rows,
    bytes,
    active,
    shard_stats
FROM system.cnch_binlogs
WHERE (database = 'db_test') AND (table = 'binlog_test') 

The query result is as follows:

┌─database─┬─table───┬─bsn─┬─rows─┬─bytes─┬─active─┬─shard_stats────┐
│ db_test   │ binlog_test │   1 │    3  │  1416   │      1 │ [ 0, 1, 1, 1 ]     │
│ db_test   │ binlog_test │   2 │    5  │  1437   │      1 │ [ 0, 3, 2, 0 ]     │
│ db_test   │ binlog_test │   3 │    2  │  1403   │      1 │ [ 0, 1, 1, 0 ]     │
└──────┴────────┴───┴────┴──────┴─────┴────────────┘