Hologres:Subscribe to Hologres binary logs

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, 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.

• 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 enable binary logging for existing tables based on your business requirements.

• For Hologres instances of minor versions earlier than V1.3.14 or Hologres instances of all versions earlier than V1.1.82, only a superuser can consume Hologres binary logs. If you use an account with lower permissions to consume Hologres binary logs, the error message "permission denied for table hg_replication_slot_properties" is returned. In Hologres V1.3.14 and all later versions or V1.1.82 and later minor versions, if Flink is used to consume Hologres binary logs, you can use accounts with query permissions. If you use the Java Database Connectivity (JDBC) driver to consume Hologres binary logs, you must use accounts that have the permissions of the replication role.

• In Hologres, you can subscribe to binary logs of a single row-oriented or column-oriented table. The following table describes the support for different subscription modes.

  Flink type	Subscription to binary logs of a Hologres row-oriented table	Subscription to binary logs of a Hologres column-oriented table	Subscription to binary logs of a Hologres row-column hybrid table (supported in Hologres V1.1 and later)
  Blink-based Realtime Compute	Supported	Supported	Supported
  Fully managed Flink	Supported	Supported	Supported
  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

• 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 child table or a non-partitioned table. 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 explicitly specify the TTL for the binary logs of a child table, the TTL for the binary logs of the parent table is used as that for the binary logs of the child table. The TTL of binary logs is not accurate. The system does not ensure that binary logs are deleted immediately after they expire, but deletes binary logs at a specific time after they expire.

• 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.

Fields in binary logs consist of system fields and user-defined table fields. The following table describes the fields.

By default, binary logging is disabled for Hologres tables. You can enable this feature for a Hologres table by configuring the table properties binlog.level and binlog.ttl. The following sample code provides an example of how to enable binary logging. For more information about the parameters for creating a table, see Overview.

• Syntax supported in Hologres V2.1 and later:

  In Hologres V2.1 and later, the binlog.level and binlog.ttl properties are changed to the binlog_level and binlog_ttl properties respectively.

  CREATE TABLE test_message_src (
      id int PRIMARY KEY,
      title text NOT NULL,
      body text
  )
  WITH (
      orientation = 'row',
      clustering_key = 'id',
      binlog_level = 'replica',
      binlog_ttl = '86400' -- Specify the TTL of binary logs. Unit: seconds.
  );

• Syntax supported in all Hologres versions:

  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'); -- Configure the id column as the clustering key.
  call set_table_property('test_message_src', 'binlog.level', 'replica'); -- Configure the binlog.level property to enable binary logging.
  call set_table_property('test_message_src', 'binlog.ttl', '86400'); -- Specify the TTL of binary logs. Unit: seconds.
  commit;

The following table describes the properties.

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. You can also specify the TTL of binary logs to retain binary logs for a specific period of time. This meets the requirements for a limited retention period in specific business scenarios.

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. In addition, Hologres provides functions for you to query the latest or earliest binary log, or query information about binary logs based on the log sequence number (LSN) or timestamp.

Query binary logs based on special built-in fields

You can use a combination of the additional fields in the binary logs and the fields in the table to perform a query. Sample statement:

SELECT hg_binlog_lsn,hg_binlog_event_type,hg_binlog_timestamp_us,* FROM test_message_src;

The following figure shows the query result.

Query the earliest or latest binary log of a shard in a table

You can use the hg_get_binlog_cursor function to perform the query. Syntax:

-- Query the earliest binary log of a shard.
SELECT * FROM hg_get_binlog_cursor('<table_name>','OLDEST',<shard_id>);

-- Query the latest binary log of a shard.
SELECT * FROM hg_get_binlog_cursor('<table_name>','LATEST',<shard_id>);

The following statement shows an example.

SELECT * FROM hg_get_binlog_cursor('test_message_src','OLDEST',0);

The following figure shows the query result.

Query the timestamp of a binary log based on the LSN of the binary log

You can use the hg_get_binlog_cursor_by_lsn function to query the timestamp of a binary log. This function returns the timestamp of the first binary log whose LSN is greater than or equal to the value specified in the query. If the LSN is invalid, this function returns the current timestamp as the value of the hg_binlog_timestamp_us field. Syntax:

SELECT * FROM hg_get_binlog_cursor_by_lsn('<table_name>',<lsn>,<shard_id>);-- Specify an LSN of the BIGINT type.

The following statement shows an example.

SELECT * FROM hg_get_binlog_cursor_by_lsn('test_message_src',152,0);

The following figure shows the query result.

Query the LSN of a binary log based on the timestamp of the binary log

You can use the hg_get_binlog_cursor_by_timestamp function to query the LSN of a binary log. This function returns the LSN of the first binary log whose timestamp value is greater than or equal to the value specified in the query. If the timestamp is the latest, this function returns the current timestamp as the value of the hg_binlog_timestamp_us field and the LSN to be assigned to the next data record as the value of the hg_binlog_lsn field. Syntax:

SELECT * FROM hg_get_binlog_cursor_by_timestamp('<table_name>',<timestamp>,<shard_id>);

Sample statement:

SELECT *,to_timestamp(hg_binlog_timestamp_us/1000000.0) FROM hg_get_binlog_cursor_by_timestamp('test_message_src','2024-05-20 19:34:53.791+08',0);

The following figure shows the query result.

You can use Flink, Blink, JDBC, or Holo Client to consume Hologres binary logs. For more information, see the following topics:

• Use Realtime Compute for Apache Flink or Blink to consume Hologres binary logs in real time

• Use JDBC to consume Hologres binary logs

You can execute the following SQL statement to check the tables for which binary logging is enabled:

SELECT
    *
FROM
    hologres.hg_table_properties
WHERE
    property_key = 'binlog.level'
    AND property_value = 'replica';

The following figure shows the returned result.