This topic describes how to subscribe to Hologres binary logs.

Limits

Before you subscribe to binary logs in Hologres, take note of the following items:
  • Only Hologres V0.9 and later support binary log subscription. If the version of your Hologres instance is earlier than V0.9, submit a ticket or join the online DingTalk group to apply for an instance update. For more information about how to join the Hologres DingTalk group, see Obtain online support for Hologres.
  • In Hologres V0.9 and V0.10, you cannot enable binary logging for an existing table by modifying table properties. 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 configure the binary logging feature for existing tables based on your business requirements.
  • In Hologres V1.3 (earlier than Hologres V1.3.14) and Hologres V1.1 (earlier than Hologres V1.1.82), only superusers can consume Hologres binary logs. If an account with fewer permissions is used to consume Hologres binary logs, the error message permission denied for table hg_replication_slot_properties is returned. In Hologres V1.3 (Hologres V1.3.14 and later) and Hologres V1.1 (Hologres V1.1.82 and later), if Realtime Compute for Apache Flink is used to consume Hologres binary logs, accounts can have only the permissions to query tables. If you use Java Database Connectivity (JDBC) to consume Hologres binary logs, your account must have the permissions of the replication role.
  • Hologres allows you to subscribe to binary logs of each row-oriented table or column-oriented table. The following table describes the support for different subscription modes.
    Flink versionSubscription to the binary logs of Hologres row-oriented tablesSubscription to the binary logs of Hologres column-oriented tablesSubscription to the binary logs of Hologres row-column hybrid tables (supported in Hologres V1.1 and later)
    Blink-based Realtime ComputeSupportedSupportedSupported
    Fully managed FlinkSupportedSupportedSupported
    Apache FlinkNot supportedNot supportedNot supported
    JDBCSupported from Hologres V1.1Supported from Hologres V1.1Supported from Hologres V1.1
  • Blink-based Realtime Compute does not support Hologres binary logs that contain data of the TIMESTAMP type. When you create a table in Hologres, specify the TIMESTAMPTZ data type for related fields. In addition, Blink-based Realtime Compute does not support Hologres binary logs that contain data of special types such as SMALLINT.
  • You cannot subscribe to the binary logs of a parent table. You can subscribe to the binary logs of a non-partitioned table, such as a child table or a standard table. In Hologres V1.3.24 and later, you can configure the time to live (TTL) of binary logs for child tables based on your business requirements. If you do not specify the TTL for binary logs of a child table, the TTL of binary logs of the parent table is used as that of binary logs of the child table by default.
  • Theoretically, the overhead of binary logging for column-oriented tables is higher than that for row-oriented tables. If your tables are frequently updated, we recommend that you enable binary logging for row-oriented tables.
  • You can enable binary logging only for Hologres internal tables. You cannot enable binary logging for foreign tables.

Enable binary logging

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. The following sample code provides an example on how to enable binary logging. For more information about the parameters for creating a table, see Overview.
Note Theoretically, the overhead of binary logging for column-oriented tables is higher than that for row-oriented tables. If your tables are frequently updated, we recommend that you enable binary logging for row-oriented tables. 
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 TTL of binary logs. Unit: seconds.
commit;
The following table describes the properties.
PropertyDescription
binlog.levelSpecifies whether to enable binary logging. Valid values:
  • replica: enables binary logging.
  • none: disables binary logging.
binlog.ttlThe TTL of binary logs. Unit: seconds. Default value: 2592000, which indicates 30 days.

Configure the binary logging feature for existing tables based on your business requirements

In Hologres V1.1 and later, you can enable or disable binary logging for existing tables based on your business requirements. To enable binary logging for an existing table, you do not need to create the table again in Hologres V1.1 and later. You can also specify the TTL of binary logs to retain binary logs only for a specific period of time. This meets the requirements for a limited retention period in specific business scenarios.
Note The operations described in this section apply only to Hologres V1.1 and later. If the version of your Hologres instance is earlier than V1.1, submit a ticket or join the online DingTalk group to apply for an instance update.
  • Enable binary logging.
    You can execute the following statements to enable binary logging for an existing table and specify the TTL of binary logs: 
    -- 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 binary logs. Unit: seconds.
begin;
call set_table_property('<table_name>', 'binlog.ttl', '2592000');
commit;
    The table_name parameter specifies the name of the table for which you want to enable binary logging.
  • 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 parameter specifies the name of the table for which you want to disable binary logging.
  • Change the TTL of binary logs.
    You can execute the following statements to change the TTL of binary logs for a table. This way, you can set an appropriate log retention period based on your business requirements.
    Note In Hologres V1.3.24 and later, you can configure the TTL of binary logs for child tables based on your business requirements. If you do not specify the TTL for binary logs of a child table, the TTL of binary logs of the parent table is used as that of binary logs of the child table by default. 
    begin; 
call set_table_property('<table_name>', 'binlog.ttl', '8640000'); -- Unit: seconds.
commit;
    The table_name parameter specifies the name of the table for which you want to change the TTL of binary logs.

Binary log format

Fields in binary logs consist of binary log system fields and user-defined table fields. The following table describes specific fields.
FieldTypeDescription
hg_binlog_lsnBIGINTThe ordinal number of the current binary log. This field is a binary log system field. The values of this field in binary logs in the same shard monotonically increase and may be discontinuous. The values of this field in binary logs in different shards may be identical and out of order.
hg_binlog_event_typeBIGINTThe type of operation recorded in the current binary log. This field is a binary log system field.
hg_binlog_timestamp_usBIGINTThe timestamp of the system. Unit: microseconds. This field is a binary log system field.
user_table_column_1User-definedA user-defined table field.
.........
user_table_column_nUser-definedA user-defined table field.
  • The hg_binlog_event_type field has the following 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.
    • HEARTBEAT_LOG_EVENT=27, which indicates that the binary logs generated before or at the timestamp specified by the hg_binlog_timestamp_us field in this shard have been consumed. This value takes effect only if binary logs are consumed by using JDBC or Holo Client and the Grand Unified Configuration (GUC) parameter hg_experimental_enable_binlog_heartbeat_record is set to on. For more information, see Use JDBC to consume Hologres binary logs (Beta).
  • An UPDATE operation generates two binary logs, which indicate that a row or column is saved before and after the update. Binary logging ensures that the ordinal numbers of the two binary logs are continuous and in the right order. The log that is generated after the update comes right after the log 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 binary logs

Hologres binary logs use a strong schema. To query the binary logs of a table, you can use a combination of the additional fields in the binary logs and the fields in the table to perform the query. The following statement provides an example on how to query the binary logs of the table named test_message_src: 
select hg_binlog_lsn,hg_binlog_event_type,hg_binlog_timestamp_us,* from test_message_src;
The following figure shows the query results. Query results

Consume Hologres binary logs in real time

You can use Realtime Compute for Apache Flink, Blink, JDBC, or Holo Client to consume Hologres binary logs in real time. For more information, see the following topics:

Check the tables for which binary logging was enabled

You can execute the following SQL statement to check the tables for which binary logging was enabled: 
SELECT
    *
FROM
    hologres.hg_table_properties
WHERE
    property_key = 'binlog.level'
    AND property_value = 'replica';
The following figure shows the returned result. Tables for which binary logging was enabled