MaxCompute:Hologres external tables

Before you create a Hologres external table, make sure that the following conditions are met:

• A Hologres database and a Hologres table are created.

  For more information about Hologres databases, see Create a database.

  For more information about how to create a Hologres table, see CREATE TABLE.

  Information of the sample Hologres instance in this topic:

  • Name of the Hologres database: mc_test.

  • Schema of the Hologres database: public.

  • Endpoint of the Hologres instance in the classic network: hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80.

  • Name of the Hologres table: holo. The following figure shows the data in the table.

• The MaxCompute project for which you want to create a Hologres external table is created.

  For more information about how to create a MaxCompute project, see Create a MaxCompute project.

• The MaxCompute client is installed.

  For more information about how to install the MaxCompute client, see Install and configure the MaxCompute client.

When you use Hologres external tables, take note of the following items:

• If the IP whitelist feature is enabled in Hologres, users must use the dual-signature mode to create Hologres external tables. If you create Hologres external tables by using the STS mode, MaxCompute is intercepted by the IP whitelist when accessing Hologres.

• The names of the parent and child tables in a Hologres database are specified in Hologres external tables. SQL statements can be executed on the Hologres external tables. Parent and child tables can be mapped to Hologres external tables. However, you can only read data from parent tables.

• You cannot use the INSERT ON CONFLICT statement to write data to Hologres external tables. For more information, see INSERT ON CONFLICT(UPSERT). If the Hologres source table contains a primary key, you must ensure that the primary key of the data that you want to write to the Hologres external table is not the same as the primary key of the Hologres source table.

• When you create an external table, the table name and field names are not case-sensitive. When you query external tables or fields, the table names and field names are not case-sensitive, and forcible uppercase and lowercase conversions are not supported.

When you create a Hologres external table by using the CREATE EXTERNAL TABLE statement, you must specify a storage handler, STS authentication information or the dual-signature mode, and the JDBC URL of a Hologres data source in the statement. Syntax for creating a Hologres external table:

• Create a Hologres external table in STS mode.

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <table_name>(
    <col1_name> <data_type>,
    <col2_name> <data_type>,
    ......
  )
  stored BY '<com.aliyun.odps.jdbc.JdbcStorageHandler>'
  WITH serdeproperties (
    'odps.properties.rolearn'='<ram_arn>')
  location '<jdbc:postgresql://<endpoint>:<port>/<database>?ApplicationName=MaxCompute&[currentSchema=<schema>&][useSSL={true|false}&]table=<holo_table_name>/>' 
  tblproperties (
    'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 
    'odps.federation.jdbc.target.db.type'='holo',
    ['odps.federation.jdbc.colmapping'='<col1:column1,col2:column2,col3:column3,...>']
  );

• Create a Hologres external table in dual-signature mode.

  The dual-signature mode allows you to use the same RAM user to access the Hologres external table that you create in MaxCompute and the Hologres source table only if the RAM user has permissions on the tables. This way, manual authorization is not required. You can also configure an IP address whitelist to allow access to Hologres from IP addresses in the whitelist.

  -- Enable the dual-signature mode.
  SET odps.sql.common.table.planner.ext.hive.bridge=true;
  -- Create an external table.
  CREATE EXTERNAL TABLE [IF NOT EXISTS]  <table_name>(
    <col1_name> <data_type>,
    <col2_name> <data_type>,
    ......
  )
  stored BY '<com.aliyun.odps.jdbc.JdbcStorageHandler>'
  location'<jdbc:postgresql://<endpoint>:<port>/<database>?ApplicationName=MaxCompute&[currentSchema=<schema>&][useSSL={true|false}&]table=<holo_table_name>/>' 
  tblproperties (
    'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 
    'odps.federation.jdbc.target.db.type'='holo',
    ['odps.federation.jdbc.colmapping'='<col1:column1,col2:column2,col3:column3,...>']
  );

• odps.sql.common.table.planner.ext.hive.bridge: Configure this parameter only when you create a Hologres external table in dual-signature mode. This parameter specifies whether to enable the dual-signature mode. If you set this parameter to true, a Hologres external table is created in dual signature mode.

• if not exists: optional. If you create a Hologres external table by using the name of an existing table but do not specify the if not exists parameter, an error is returned. If you configure the if not exists parameter, a success message is returned no matter whether a table with the same name already exists. The success message is returned even if the schema of the existing table with the same name is different from that of the table you want to create. The metadata of the existing table remains unchanged.

• table_name: required. The name of the Hologres external table that you want to create in MaxCompute.

• col_name: required. The names of columns in the Hologres external table that you want to create in MaxCompute.

• data_type: required. The data types of columns in the Hologres external table.

• stored by: required. A storage handler, which defines the method that you want to use to access the Hologres external table. Set the value to Hologres. The value indicates that the Hologres external table is accessed by using JdbcStorageHandler.

• ram_arn: Configure this parameter only when you create a Hologres external table in STS mode. This parameter specifies the Alibaba Cloud Resource Name (ARN) of the specified RAM role. The ARN is used as the STS authentication information. To obtain the ARN of the specified RAM role, you can perform the following steps: Log on to the Resource Access Management (RAM) console. On the Roles page, find the RAM role whose ARN you want to query and click the RAM role in the Role Name column. On the page that appears, view the ARN in the Basic Information section.

• location: required. The JDBC URL of the Hologres instance. Description of the fields in this parameter:

  • endpoint: required. The endpoint of the Hologres instance in the classic network. For more information about how to obtain endpoints, see Instance configurations.

  • port: required. The port number of the Hologres instance. For more information about how to obtain port numbers, see Instance configurations.

  • database: required. The name of the Hologres database that you want to access. For more information about Hologres databases, see CREATE DATABASE.

  • ApplicationName: required. The default value is MaxCompute and no modification is required.

  • schema: optional. If the name of the source table is unique in the Hologres database or the source table is a table in the default schema, you do not need to specify this parameter. For more information about schemas, see CREATE SCHEMA.

  • holo_table_name: required. The name of the Hologres source table. For more information about Hologres source tables, see CREATE TABLE.

• tblproperties:

  • mcfed.mapreduce.jdbc.driver.class: required. The JDBC driver that is used to access the Hologres database. Set the value to org.postgresql.Driver.

  • odps.federation.jdbc.target.db.type: required. The type of the database that you want to access. Set the value to holo.

  • odps.federation.jdbc.colmapping: optional. If you want to map some columns of the Hologres source table to the Hologres external table, you must configure this parameter. This parameter specifies the mappings between the fields of the Hologres source table and the fields of the Hologres external table.

    • If this parameter is not configured, the field names in the source table are mapped to the corresponding same-named fields in the Hologres external table.

    • If this parameter is configured but only specifies the mapping for some columns of the MaxCompute external table, the remaining columns are mapped based on the source table field names to the corresponding same-named columns in the Hologres external table. If the column names or types do not match, an error is reported.

    • If this parameter is configured, and the name of a field in Hologres contains uppercase letters, enclose the field name in a pair of double quotation marks (""). The value of this parameter is in the MaxCompute field 1 : "Hologres field 1" [,MaxCompute field 2 : "Hologres field 2" ,...] format.

      Note

      The Hologres source table fields are c bool, map_B string, and a bigint. The MaxCompute external table fields are a bigint, x string, and c bool. If the value ofcolmapping is 'x: "map_B"', the mapping is successful, and the Hologres data can be queried.

  • mcfed.mapreduce.jdbc.input.query: optional. If you want to read data from the Hologres source table, you must configure this parameter, and the columns, column names, and data types of the external table must be consistent with those of the Hologres source table being queried directly. If aliases are used, they must also be consistent with the aliases. The select_sentence format is : SELECT xxx FROM <holo_database_name>.<holo_schema_name>.<holo_table_name>.

MaxCompute reads data from Hologres external tables in Java Database Connectivity (JDBC) mode. MaxCompute of the latest version allows you to directly read data from Hologres storage layers and provides the following benefits:

• Greatly reduces the latency of table reading and significantly improves the data query speed.

• Greatly reduces the number of connections to FE in Hologres. For most queries, only one connection is required.

If you enable the direct read mode of Hologres, the following limits apply. If the conditions are not met, the JDBC mode is used.

• The version of the Hologres instance must be V1.3.34 or later.

  If the version of your Hologres instance is earlier than V1.3.34, the direct read feature is not supported.

• Due to network connectivity, only MaxCompute projects in the same region can access Hologres instances.

  Currently, cross-region access is not supported. The following error message is reported for cross-region access: FAILED: ODPS-0010000:System internal error - fuxi job failed, caused by: Pangu request failed with error code 3.

• The direct read feature does not support Hologres cold storage tables.

• The direct read feature does not support Hologres row-oriented tables.

• If you configure primary and secondary Hologres instances for the Hologres database, you can use only the URL of the primary Hologres instance to connect to MaxCompute. You cannot use the URL of the secondary instance to connect to MaxCompute.

• The direct read feature verifies the data types of columns in MaxCompute and Hologres. If the data types do not match, MaxCompute automatically reads data in JDBC mode. When you create a Hologres external table in MaxCompute, you must comply with the following limits on data types:

  • The following data types are not supported in JDBC mode and MaxCompute direct read mode:

    • Complex data types, such as ARRAY, MAP, and STRUCT

    • Data types, such as JSON and MONEY

  • The following data types are supported in JDBC mode but are not supported in MaxCompute direct read mode:

    • BINARY type

  • The following data types are supported in MaxCompute direct read mode but are not supported in JDBC mode:

    • JSONB type

  • If the TIMESTAMP data type is mapped to the TIMESTAMP WITH TIME ZONE data type of Hologres in MaxCompute direct read mode, the following time errors may occur:

    • If the time in the TIMESTAMP WITH TIME ZONE column of a Hologres external table is earlier than 1900-12-31 15:54:15, the time that is obtained in direct read mode is 344 seconds later than the time in the TIMESTAMP WITH TIME ZONE column of the Hologres external table.

    • If the time in the TIMESTAMP WITH TIME ZONE column of a Hologres external table is between 1900-12-31 15:54:16 and 1969-12-31 23:59:58, the time that is obtained in direct read mode is one second later than the time in the TIMESTAMP WITH TIME ZONE column of the Hologres external table.

    • If the time in the TIMESTAMP WITH TIME ZONE column of a Hologres external table is later than 1969-12-31 23:59:59, the time that is obtained in direct read mode is the same as the time in the TIMESTAMP WITH TIME ZONE column of the Hologres external table.

      Note

      If the TIMESTAMP data type is mapped to the TIMESTAMP WITH TIME ZONE data type of Hologres in MaxCompute direct read mode, the following time zone and time errors may occur:

      • For example, the time zone of MaxCompute is UTC+8. If the time in the TIMESTAMP WITH TIME ZONE column of a Hologres external table is 2000-01-01 00:00:00, the time that is obtained in direct read mode is 2000-01-01 08:00:00.

      • For example, the time zone of MaxCompute is UTC+8. If the time in the TIMESTAMP WITH TIME ZONE column of a Hologres external table is 1969-01-01 00:00:00, the time that is obtained in direct read mode is 1969-01-01 08:00:01.

  • The following table describes the data type mappings between Hologres and MaxCompute.

    Data type supported by Hologres	Data type supported by MaxCompute	Description
    TEXT	STRING VARCHAR	N/A.
    SMALLINT	SMALLINT	N/A.
    INT INT4 INTEGER	INT	N/A.
    INT8 BIGINT	BIGINT	N/A.
    FLOAT4 REAL	FLOAT	N/A.
    FLOAT FLOAT8	DOUBLE	N/A.
    BOOL BOOLEAN	BOOLEAN	N/A.
    TIMESTAMP	TIMESTAMP	Timestamp is displayed in the unit of microsecond. Time error exists due to different time zones.
    TIMESTAMP WITH TIME ZONE	TIMESTAMP	The precision and scale are automatically converted between MaxCompute and Hologres at the underlying layer. MaxCompute provides data of the TIMESTAMP type without a time zone specified.
    NUMERIC	DECIMAL	If no precision or scale is specified for the DECIMAL data type in MaxCompute, the default value DECIMAL (38,18) is used. The precision and the scale are automatically converted when you create an external table by executing the IMPORT FOREIGN SCHEMA statement.
    CHAR(n)	CHAR(n)	Entries of the CHAR(n) data type in MaxCompute are character strings with a fixed length that cannot exceed n characters. The maximum value of n is 255. If you insert a character string that is shorter than the required length, Hologres adds spaces to the character string to increase the length.
    VARCHAR(n)	VARCHAR(n)	Entries of the VARCHAR(n) data type in MaxCompute are character strings with a variable length that cannot exceed n characters. Valid values of n: 1 to 65535.
    DATE	DATE	N/A.

• In foreign server mode, you must enable the three-layer model for your MaxCompute project.

When you query data from a Hologres external table in MaxCompute, add the following command before the SQL statement:

SET odps.table.api.enable.holo.table=true;

On the Summary tab of LogView, find the external holo tables field to view the attribute. The attribute is displayed in the following format:

<project_name>.<table_name >:< Access mode>[<(Rollback reason)>]

The following table describes parameters in the preceding syntax.