This topic provides the DDL syntax that is used to create a Hologres dimension table, describes the parameters in the WITH and CACHE clauses, and provides sample code.

Note Hologres connectors do not allow you to write result data to Hologres external tables. For more information about Hologres external tables, see Manage a foreign table.

What is Hologres?

Hologres is a real-time interactive analytics service that is developed by Alibaba Cloud. Hologres is compatible with the PostgreSQL protocol and is seamlessly integrated with the big data ecosystem. Hologres allows you to analyze and process up to petabytes of data with high concurrency and low latency. Hologres allows you to use existing business intelligence (BI) tools to perform multidimensional analysis and explore your business in an efficient manner.

Prerequisites

A Hologres dimension table is created. For more information, see Manage an internal table.

Limits

  • Only Flink that uses Ververica Runtime (VVR) 2.0.0 or later versions supports Hologres connectors.
  • We recommend that you use row-oriented storage to create a Hologres dimension table. Column-oriented storage consumes a large number of performance overheads for point queries.
    When you use row-oriented storage to create a Hologres dimension table, you must set the primary key to clustering key. The following statements show an example:
    begin;
    create table test(a int primary key, b text, c text, d float8, e int8);
    call set_table_property('test', 'orientation', 'row');
    call set_table_property('test', 'clustering_key', 'a');
    commit;
  • When you join a Hologres dimension table with another table, you must specify all the fields in the primary key of the dimension table in the ON clause.
  • Only Hologres dimension table connectors of Flink that use VVR 4.0 or later versions support one-to-many data output.

DDL syntax

CREATE TABLE hologres_dim(
  id INT,
  len INT,
  content VARCHAR
) with (
  'connector'='hologres',
  'dbname'='<yourDbname>',
  'tablename'='<yourTablename>',
  'username'='<yourUsername>',
  'password'='<yourPassword>',
  'endpoint'='<yourEndpoint>'
);

Parameters in the WITH clause

Parameter Description Required Remarks
connector The type of the dimension table. Yes Set the value to hologres.
endPoint The endpoint of Hologres. Yes For more information, see Endpoints for connecting to Hologres.
tablename The name of the table.
Note If the public schema is not used, you must set tableName to schema.tableName.
Yes N/A.
dbname The name of the database. Yes N/A.
username The username that is used to log on to the database. You must enter the AccessKey ID of your Alibaba Cloud account. Yes N/A.
password The password that is used to access the database. You must enter the AccessKey secret of your Alibaba Cloud account. No N/A.
useRpcMode Ververica Platform (VVP) 2.4.0 and later versions connect to Hologres by using a Java Database Connectivity (JDBC) driver. VVP earlier than 2.4.0 connects to Hologres by using the remote procedure call (RPC). No JDBC drivers require SQL connections. This increases the number of JDBC connections. If you want to reduce the number of SQL connections, you can set this parameter to true.
Notice Only VVP 2.4.0 and later versions support this parameter.
connectionSize The size of the JDBC connection pool that is created by a Flink job.

If the job has poor performance, we recommend that you increase the size of the connection pool. The size of the JDBC connection pool is proportional to data throughput.

No Default value: 3.
Notice Only VVP 2.4.0 and later versions support this parameter.

Parameters in the CACHE clause

Parameter Description Required Remarks
cache The cache policy. No Valid values:
  • LRU: indicates that only some data in the dimension table is cached. Each time the system receives a data record, the system searches the cache. If the system does not find the record in the cache, the system searches for the data record in the physical dimension table.

    If this cache policy is used, you must configure the cacheSize and cacheTTLMs parameters.

  • None: indicates that data is not cached.
Note The default value of the cache parameter varies based on the VVR version:
  • VVR 4.x and later versions: None.
  • VVR versions earlier than 4.x: LRU.
cacheSize The maximum number of data records that can be cached. No This parameter is available only if you set the cache parameter to LRU. Default value: 10000.
cacheTTLMs The interval at which the system refreshes the cache. No The cacheTTLMs parameter applies configurations based on the value of the cache parameter.
  • If you set the cache parameter to LRU, the cacheTTLMs parameter specifies the cache timeout period. By default, cache entries do not expire.
  • If you set the cache parameter to None, the cacheTTLMs parameter can be left empty. This indicates that cache entries do not expire.
async Specifies whether to enable data synchronization in asynchronous mode. No Valid values:
  • true: Data synchronization in asynchronous mode is enabled.
  • false: Data synchronization in asynchronous mode is disabled. Default value: false.

Data type mapping

Data type of Hologres Data type of Flink
INT INT
INT[] ARRAY<INT>
BIGINT BIGINT
BIGINT[] ARRAY<BIGINT>
REAL FLOAT
REAL[] ARRAY<REAL>
DOUBLE PRECISION DOUBLE
DOUBLE PRECISION[] ARRAY<DOUBLE PRECISION>
BOOLEAN BOOLEAN
BOOLEAN[] ARRAY<BOOLEAN>
TEXT VARCHAR
TEXT[] ARRAY<TEXT>
NUMERIC DECIMAL
DATE DATE
TIMESTAMP WITH TIMEZONE TIMESTAMP
Note Hologres connectors support only the conversion of the Hologres data types that are listed in the table.

Sample code

CREATE TEMPORARY TABLE datagen_source (
  a INT,
  b BIGINT,
  c STRING,
  proctime AS PROCTIME()
) WITH (
  'connector' = 'datagen'
);

CREATE TEMPORARY TABLE hologres_dim (
  a INT, 
  b VARCHAR, 
  c VARCHAR
) WITH (
  'connector' = 'hologres',
   ...
);

CREATE TEMPORARY TABLE blackhole_sink (
  a INT,
  b STRING
) WITH (
  'connector' = 'blackhole'
);

insert into blackhole_sink select T.a,H.b
FROM datagen_source AS T JOIN hologres_dim FOR SYSTEM_TIME AS OF T.proctime AS H ON T.a = H.a;