Hologres:Query encrypted MaxCompute data

Enable Common Table

You can enable the link at the appropriate level for your scenario.

• Session level

  -- Enable at the session level. Execute this command with your query or DML statement.
  SET hg_experimental_external_catalog_routing = 'odps:common_table,dlf:hqe';

• Database level

  -- Enable at the database level.
  ALTER DATABASE <database name> SET hg_experimental_external_catalog_routing = 'odps:common_table,dlf:hqe';

• Instance level

  -- Enable at the instance level.
  ALTER ROLE ALL SET hg_experimental_external_catalog_routing = 'odps:common_table,dlf:hqe';

Limitations

• Only Hologres V1.1 and later allow you to use a foreign table to query encrypted data in MaxCompute. If the version of your Hologres instance is earlier than V1.1, upgrade your instance or join the Hologres DingTalk group and ask engineers to upgrade your instance. For more information about how to upgrade a Hologres instance, see Upgrade instances.

• If the version of your Hologres instance is earlier than V1.3.31, you must add backend configurations before you can query encrypted data in MaxCompute. If the version of your Hologres instance is V1.3.31 or later, you do not need to add backend configurations. If you want to use this feature, we recommend that you upgrade your Hologres instance to V1.3.31 or later. For more information, see Upgrade instances.

• You can query only encrypted MaxCompute data. The following types of materials can be used to create a customer master key (CMK) for encrypting MaxCompute data: key materials that are generated by KMS when you create a CMK, and the imported key materials. You cannot query MaxCompute data that is encrypted by using the default key of DataWorks.

• When you query data, Hologres calls KMS API operations to obtain key information. By default, the key information is cached for 24 hours.

Procedure

1. Create a custom policy.

   1. Log on to the Resource Access Management (RAM) console. In the left-side navigation pane, choose Permissions > Policies. On the Policies page, click Create Policy.

      

   2. On the Create Policy page, click JSON. On the JSON tab, enter the following script in the code editor and set Name to AliyunHologresEncryptionDefaultRolePolicy.

      

      {
          "Version": "1",
          "Statement": [
              {
                  "Action": [
                      "kms:Encrypt",
                      "kms:Decrypt",
                      "kms:GenerateDataKey",
                      "kms:DescribeKey"
                  ],
                  "Resource": "acs:kms:*:*:*/*",
                  "Effect": "Allow"
              }
          ]
      }

   3. Click OK. The custom policy is created.

2. Create a RAM role and grant permissions to the RAM role.

   1. Log on to the RAM console. In the left navigation pane, choose Identities > Roles.

   2. On the Roles page, click Create Role. On the Create Role page, set Principal Type to Cloud Service, and Principal Name to Hologres.

   3. Click OK. In the dialog, enter AliyunHologresEncryptionDefaultRole in the Role Name field, and click OK.

   4. On the role details page, click Grant Permission.

   5. In the Grant Permission panel, set Resource Scope to Account. For Policy, select the custom policy AliyunHologresEncryptionDefaultRolePolicy created in step 1.

   6. Click Grant permissions.

      On the role's details page, click the Trust Policy tab to check the trust policy settings.

3. Modify configurations for a database.

   Hologres Query Engine (HQE) does not allow you to access encrypted data in MaxCompute. Therefore, you must execute the following SQL statement to change the query engine (QE) of foreign tables to Seahawks Query Engine (SQE):

   ALTER DATABASE <dbname> SET hg_experimental_enable_access_odps_orc_via_holo = false;

   The preceding configuration takes effect only after you create a connection again. You can execute the following SQL statement to check whether the configuration of using SQE to query foreign tables takes effect. If false is displayed, the configuration takes effect.

   SHOW hg_experimental_enable_access_odps_orc_via_holo;

4. Query encrypted data.

   After the preceding operations are complete, you can use a foreign table in Hologres to query encrypted data in MaxCompute in the same way that you query regular data in MaxCompute. For more information, see Use foreign tables in Hologres to accelerate queries on MaxCompute data.

FAQ

• Problem description

  An error message similar to the following information is returned when I query data:

  ERROR: status { code: SERVER_INTERNAL_ERROR message: "hos_exception: IO error: Failed to execute pangu open normal file pangu://xxx:xxx/product/odps/xxx/data/xxxx/xxx/xxx, errorcode: 9, errorcode_description: invalid argument, err_msg: PanguParameterInvalidException

• Cause

  You use HQE to query encrypted data in MaxCompute. HQE does not allow you to access the encrypted data in MaxCompute.

• Solution

  1. Change the QE of foreign tables to SQE and modify configurations for the database.

     SQL statement:

     ALTER DATABASE <DB_Name> SET hg_experimental_enable_access_odps_orc_via_holo = false;

  2. The preceding configuration takes effect only after you create a connection. You can execute the following SQL statement to check whether the configuration of using SQE to query foreign tables takes effect. If false is displayed, the configuration takes effect.

     SHOW hg_experimental_enable_access_odps_orc_via_holo;

  3. After the configuration takes effect, query the encrypted data in MaxCompute again.