This topic describes how to subscribe to Hologres binlogs.

Usage notes

Before you subscribe to binlogs in Hologres, take note of the following items:
  • Only Hologres V0.9 and later support binlog subscription. To upgrade a Hologres instance from a version earlier than V0.9 to V0.9 or later, submit a ticket or join the Hologres DingTalk group for technical support. For more information about how to join the Hologres DingTalk group, see Obtain online support for Hologres.
  • Hologres allows you to separately subscribe to binlogs of each row-oriented table or column-oriented table. The following table describes the types of Hologres binlog subscription that are supported.
    Flink version Subscription to binlogs of Hologres row-oriented tables Subscription to binlogs of Hologres column-oriented tables Subscription to binlogs of Hologres tables that are both row-oriented and column-oriented (supported in Hologres V1.1 and later)
    Blink-based Realtime Compute Supported Supported Supported
    Fully managed Flink Supported from VVP 2.4 Supported from VVP 2.4 Supported from VVP 2.4
    Open source Flink Not supported Not supported Not supported
    JDBC Supported from Hologres V1.1 Supported from Hologres V1.1 Supported from Hologres V1.1

Enable binary logging

  • Overview

    By default, binary logging is disabled for Hologres tables. You can enable this feature for a Hologres table by setting the table properties binlog.level and binlog.ttl. Theoretically, if your tables are frequently updated, the cost of binary logging for column-oriented tables is greater than the cost of binary logging for row-oriented tables. Therefore, we recommend that you enable binary logging for row-oriented tables.

  • Limits
    • In Hologres V0.9 and V0.10, you cannot enable binary logging by modifying the table properties of an existing table. To enable binary logging for a table, you must enable the feature when you create the table. In Hologres V1.1 and later, you can enable binary logging for all tables as needed.
    • Blink-based Realtime Compute does not support Hologres binlogs that contain data of the TIMESTAMP type. When you create a table in Hologres, specify the TIMESTAMPTZ data type for related fields. Blink-based Realtime Compute also does not support Hologres binlogs that contain data of special types such as the SMALLINT type.
    • You cannot subscribe to binlogs of a parent partitioned table. You can subscribe to only the binlogs of a non-partitioned table.
    • Theoretically, if your tables are frequently updated, the cost of binary logging for column-oriented tables is greater than the cost of binary logging for row-oriented tables. Therefore, we recommend that you enable binary logging for row-oriented tables.
  • Examples

    When you create a table, you can set the table properties binlog.level and binlog.ttl to enable binary logging. For more information about the parameters that you can use to create a table, see CREATE TABLE.

    begin;
    create table test_message_src(
      id int primary key, 
      title text not null, 
      body text);
    call set_table_property('test_message_src', 'orientation', 'row'); -- Create a row-oriented table named test_message_src.
    call set_table_property('test_message_src', 'clustering_key', 'id'); -- Create a clustered index for the id column.
    call set_table_property('test_message_src', 'binlog.level', 'replica'); -- Set the binlog.level property to enable binary logging.
    call set_table_property('test_message_src', 'binlog.ttl', '86400'); -- Set the binlog.ttl property to specify the time to live (TTL) of binlogs. Unit: seconds.
    commit;
    Property Description
    binlog.level Specifies whether to enable binary logging. Valid values:
    • replica: enables binary logging.
    • none: disables binary logging.
    binlog.ttl The TTL of binlogs. Unit: seconds. Default value: 2592000, which indicates 30 days.

Enable binary logging as needed

In Hologres V1.1 and later, you can choose to enable or disable binary logging based on your business requirements. You can also specify the TTL of binlogs to set an appropriate binlog retention time based on your business requirements.
Note This feature is available only in Hologres V1.1 and later. To upgrade a Hologres instance from a version earlier than V1.1 to V1.1 or later,submit a ticket or join the Hologres DingTalk group for technical support.
  • Enable binary logging
    You can execute the following statements to enable binary logging for an existing table and specify the TTL of binlogs:
    -- Set the binlog.level property to enable binary logging.
    begin;
    call set_table_property('<table_name>', 'binlog.level', 'replica');
    commit;
    
    -- Set the binlog.ttl property to specify the TTL of binlogs. Unit: seconds.
    begin;
    call set_table_property('<table_name>', 'binlog.ttl', '2592000');
    commit;
    The table_name property specifies the name of the table for which binary logging is to be enabled.
  • Disable binary logging
    You can execute the following statements to disable binary logging for a table:
    -- Set the binlog.level property to disable binary logging.
    begin; 
    call set_table_property('<table_name>', 'binlog.level', 'none'); 
    commit; 
    The table_name property specifies the name of the table for which binary logging is to be disabled.
  • Change the TTL of binlogs
    You can execute the following statements to change the TTL of binlogs for a table. This way, you can set an appropriate binlog retention time based on your business requirements.
    begin; 
    call set_table_property('<table_name>', 'binlog.ttl', '8640000'); -- Unit: seconds.
    commit;
    The table_name property specifies the name of the table for which the TTL of binlogs is to be changed.

Binlog format

Fields in binlogs consist of binlog system fields and user-defined table fields. The following table describes specific fields.
Field Data type Description
hg_binlog_lsn BIGINT The ordinal number of the current binlog. This field is a binlog system field. For binlogs in the same shard, the values of this field monotonically increase and may be discontinuous. For binlogs in different shards, the values of this field may be recurrent and out of order.
hg_binlog_event_type BIGINT The type of operation in the current record. This field is a binlog system field.
hg_binlog_timestamp_us BIGINT The timestamp of the system. Unit: microseconds. This field is a binlog system field.
user_table_column_1 User-defined A user-defined table field.
... ... ...
user_table_column_n User-defined A user-defined table field.
  • The hg_binlog_event_type field has four valid values:
    • INSERT=5, which indicates that a row or column is inserted.
    • DELETE=2, which indicates that a row or column is deleted.
    • BEFORE_UPDATE=3, which indicates that a row or column is saved before it is updated.
    • AFTER_UPDATE=7, which indicates that a row or column is saved after it is updated.
  • An UPDATE operation generates two binlog records, which indicate that the row or column is saved before and after the update. Binary logging ensures that the ordinal numbers of the two records are continuous and in the right order. The record that is generated after the update comes right after the record that is generated before the update.
  • The order of user-defined fields is consistent with the order that is defined in a DDL statement.

Query log backup files

Hologres binlogs use a strong schema. To query binlogs of a table, you can use a combination of the additional fields in the binlogs and the fields in the table to perform the query. The following statement is executed to query the binlogs of the table test_message_src:
select hg_binlog_lsn,hg_binlog_event_type,hg_binlog_timestamp_us,* from test_message_src;

Consume Hologres binlogs in real time

You can use Flink, Blink, Java Database Connectivity (JDBC), and Holo Client to consume Hologres binlogs in real time. For more information, see the following topics: