MaxCompute:Hologres external tables

Create a RAM role

Create a RAM role to obtain its Amazon Resource Name (ARN). The ARN is used for STS authentication when you create the external table.

1. Log on to the Resource Access Management console. You can create a role as shown in the figure.

   

   Select the trusted entity type for the RAM role as needed. You can choose either an Alibaba Cloud account or an identity provider.

   • Alibaba Cloud account:

     A RAM user that belongs to an Alibaba Cloud account can assume a RAM role to access cloud resources. For more information, see Create a RAM role for a trusted Alibaba Cloud account.

   • Identity provider:

     By setting up Single Sign-On (SSO), you can log on to the Alibaba Cloud Management Console from your corporate account system. This meets your enterprise's unified user logon authentication requirements. For more information, see Create a RAM role for a trusted identity provider.

2. Modify the trust policy.

   1. On the Roles page, click the name of the RAM role that you created.

   2. Click the Trust Policy tab.

   3. On the Trust Policy tab, click Edit Trust Policy.

   4. Modify the trust policy configuration.

      The required modification to the trust policy depends on the selected trusted entity type.

      • Trusted entity is an Alibaba Cloud account:

        {
          "Statement": [
            {
              "Action": "sts:AssumeRole",
              "Effect": "Allow",
              "Principal": {
                "RAM": [
                  "acs:ram::<UID>:root"
                ]
              }
            },
            {
              "Action": "sts:AssumeRole",
              "Effect": "Allow",
              "Principal": {
                "Service": [
                  "<UID>@odps.aliyuncs.com"
                ]
              }
            }
          ],
          "Version": "1"
        }

      • Trusted entity is an identity provider:

        {
          "Statement": [
                {
                    "Action": "sts:AssumeRole",
                    "Condition": {
                        "StringEquals": {
                            "saml:recipient": "https://signin.aliyun.com/saml-role/sso"
                        }
                    },
                    "Effect": "Allow",
                    "Principal": {
                        "Federated": [
                            "acs:ram::<UID>:saml-provider/IDP"
                        ]
                    }
                },
                {
                    "Action": "sts:AssumeRole",
                    "Effect": "Allow",
                    "Principal": {
                        "Service": [
                            "<UID>@odps.aliyuncs.com"
                        ]
                    }
                }
            ],
            "Version": "1"
        }

        Note

        <UID> specifies the Alibaba Cloud account ID. You can obtain the ID from the User Information page.

   5. Click OK.

Configuring RAM role permissions in Hologres

The RAM role requires developer permissions for the Hologres instance to use Hologres within its authorized scope. By default, a RAM role does not have permissions to view or manage instances in the Hologres console. The Alibaba Cloud account must grant the required RAM permissions before the RAM role can perform subsequent operations. You can add the RAM role to the Hologres instance and grant permissions in one of the following ways:

• Grant permissions in the Hologres console.

  1. Log on to the Hologres console.

  2. In the navigation pane on the left, click Instance List, and then click the name of the Hologres instance to which you want to grant permissions.

  3. On the instance details page, click Account Management.

  4. Follow the steps in the figure to add a RAM role to the Hologres instance. On the User Management page, click Add User.

     

  5. Follow the steps shown in the figure. On the DB Authorization tab, grant the RAM role developer permissions for the instance.

     Note

     If the database permission policy is set to Expert mode, you must change the role type to SuperUser on the User Management page. After this change, no further database authorization is required.

     

• Grant permissions using SQL statements.

  You can grant permissions using SQL statements. For more information about the authorization SQL, see Hologres permission model.

• By default, a RAM user who assumes a RAM role does not have the required permissions to access the Hologres console. The Alibaba Cloud account must grant the AliyunRAMReadOnlyAccess permission to the RAM user on the Resource Access Management page. Without this permission, the RAM user cannot perform operations in the Hologres console. For more information, see Grant permissions to a RAM user.

  

  

Example

After you complete the preceding steps, you can log on to the MaxCompute client and create a Hologres external table based on the prepared data. For more information, see Create a Hologres external table.

1. Getting Started: Use the MaxCompute client to experience data analytics. Then, switch to your destination project.

   For more information about the command that you can use to enter the destination project, see Project operations.

2. Execute the following command to create a Hologres external table.

   The following is a sample SQL command:

   CREATE EXTERNAL TABLE IF NOT EXISTS my_table_holo_jdbc
   (
    id bigint,
    name string
   )
   stored BY 'com.aliyun.odps.jdbc.JdbcStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::139699392458****:role/aliyunodpsholorole')
   location 'jdbc:postgresql://hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80/mc_test?ApplicationName=MaxCompute&currentSchema=public&useSSL=false&table=holo/'
   tblproperties (
     'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
     'odps.federation.jdbc.target.db.type'='holo',
     'odps.federation.jdbc.colmapping'='id:id,name:name'
   );

3. Execute the following command to query data from the Hologres source table based on the newly created Hologres external table.

   The SQL code is as follows:

   -- Add the following properties to access the Hologres external table.
   SET odps.sql.split.hive.bridge=true;
   SET odps.sql.hive.compatible=true;
   SET odps.table.api.enable.holo.table=true; -- Enable the JDBC direct read mode.
   -- Query data from the Hologres external table.
   SELECT * FROM my_table_holo_jdbc limit 10;

   The following result is returned:

   +------------+------------+
   | id         | name       |
   +------------+------------+
   | 1          | kate       |
   | 2          | mary       |
   | 3          | bob        |
   | 4          | tom        |
   | 5          | lulu       |
   | 6          | mark       |
   | 7          | haward     |
   | 8          | lilei      |
   | 9          | hanmeimei  |
   | 10         | lily       |
   +------------+------------+

4. Optional: Perform data exchange and association analysis with Hologres based on the Hologres external table.

   For example, you can use a Hologres external table to write data processed by MaxCompute into Hologres for accelerated analysis and online services. The following is a sample command.

   -- Add the following properties to access the Hologres external table.
   SET odps.sql.split.hive.bridge=true;
   SET odps.sql.hive.compatible=true;
   SET odps.table.api.enable.holo.table=true; -- Enable the JDBC direct read mode.
   -- Insert data into the Hologres external table.
   INSERT INTO my_table_holo_jdbc VALUES (12,'alice');
   -- Query data from the Hologres external table.
   SELECT * FROM my_table_holo_jdbc;

   The following result is returned.

   +------------+------------+
   | id         | name       |
   +------------+------------+
   | 12          | alice      |
   | 1          | kate       |
   | 2          | mary       |
   | 3          | bob        |
   | 4          | tom        |
   | 5          | lulu       |
   | 6          | mark       |
   | 7          | haward     |
   | 8          | lilei      |
   | 9          | hanmeimei  |
   | 10         | lily       |
   | 11         | lucy       |
   +------------+------------+

   Frequently updated dimension tables are stored in Hologres to meet real-time dynamic update requirements. MaxCompute accesses the dimension tables through external tables to perform association analysis with fact tables in MaxCompute. The following is a sample command.

   -- Add the following properties to access the Hologres external table.
   SET odps.sql.split.hive.bridge=true;
   SET odps.sql.hive.compatible=true;
   -- Create a MaxCompute internal table.
   CREATE TABLE holo_test AS SELECT * FROM my_table_holo_jdbc;
   -- Perform association analysis between the MaxCompute internal table and the Hologres external table.
   SELECT * FROM my_table_holo_jdbc t1 INNER JOIN holo_test t2 ON t1.id=t2.id;

   The following result is returned.

   +------------+------------+------------+------------+
   | id         | name       | id2        | name2      |
   +------------+------------+------------+------------+
   | 1          | kate       | 1          | kate       |
   | 2          | mary       | 2          | mary       |
   | 3          | bob        | 3          | bob        |
   | 4          | tom        | 4          | tom        |
   | 5          | lulu       | 5          | lulu       |
   | 6          | mark       | 6          | mark       |
   | 7          | harward    | 7          | harward    |
   | 8          | lilei      | 8          | lilei      |
   | 9          | hanmeimei  | 9          | hanmeimei  |
   | 10         | lily       | 10         | lily       |
   | 11         | lucy       | 11         | lucy       |
   | 12         | alice      | 12         | alice      |
   +------------+------------+------------+------------+

Example

You can directly log on to the MaxCompute client and create a Hologres external table in dual-signature mode. For more information, see Create a Hologres external table.

-- Create the external table.
CREATE EXTERNAL TABLE IF NOT EXISTS holo_mc_external_dbl
(
  id int,
  name string,
  ds string
)
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
location 'jdbc:postgresql://hgprecn-cn-zvp2o6aq****-cn-beijing-internal.hologres.aliyuncs.com:80/mc_test?ApplicationName=MaxCompute&currentSchema=public&preferQueryMode=simple&useSSL=false&table=mf_holo_mc_up/'
TBLPROPERTIES (
  'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
  'odps.federation.jdbc.target.db.type'='holo',
  'odps.federation.jdbc.colmapping'='id:id,name:name,ds:ds'
);

-- Query the external table (must be executed together with the command to enable the dual-signature switch).
SET odps.sql.common.table.planner.ext.hive.bridge=true;
SELECT * FROM holo_mc_external_dbl;

How do I resolve the ODPS-0130071 error when directly reading Hologres data through MaxCompute?

• Symptom

  When you directly read Hologres data, the following error is reported: ODPS-0130071 Failed to split to equal size...max count: 7777. For example:

  ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: storage/table/src/input_splits_builder.cpp(195): StorageException: Failed to split to equal size, total size: 2143570729934, min size: 268435456, max size: 272629760, max count: 7777, split size: 275629513, split count: 7777

• Cause

  When MaxCompute directly reads data from Hologres, the default Mapper splitting strategy (input data volume/split.size of 256 MB) causes the number of Mapper instances that are generated by the task to exceed the maximum limit of 7,777.

  Note

  This limit is imposed to prevent users from submitting tasks that generate many Mapper instances, which could affect the stability of Hologres files or network connections.

• Solution

  You can resolve the error by setting the following parameters.

  SET odps.external.holo.mapper.instances=10000; -- Increase the concurrency limit, up to a maximum of 10000.
  SET odps.sql.mapper.split.size=512; -- Adjust the task concurrency, up to a maximum of 512M

Why is an SQL job based on a Hologres external table running slowly, and how can I fix it?

Slow data search on a MaxCompute external table using an SDK.

• Symptom

  Searching for data in a MaxCompute external table using an SDK is slow.

• Solution

  External tables support only full table scans, which causes the slow query performance. We recommend that you use a MaxCompute internal table instead.

How do I resolve the error that occurs when a keyword is used as a column name in Hologres?

• Symptom

  When you use a keyword as a column name in Hologres, the following error is reported if you do not add a special configuration:

  ODPS-0123131:User defined function exception - SQLException in nextKeyValue

  Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ","

• Solution

  Add the odps.federation.jdbc.colmapping parameter to map the fields of the Hologres source table to the fields of the Hologres external table.

  For example, if the Hologres source table contains the keyword field "offset", you must add the parameter 'odps.federation.jdbc.colmapping'='offset:"offset"' when you create the Hologres external table.