Hologres:Query MaxCompute data encrypted based on BYOK

Limits

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

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

• You can query only MaxCompute data that is encrypted based on BYOK. 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 the JSON tab. 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-side navigation pane, choose Identities > Roles.

   2. On the Roles page, click Create Role. In the Select Role Type step of the Create Role panel, select Alibaba Cloud Service for Select Trusted Entity.

   3. Click Next. In the Configure Role step, set Role Type to Normal Service Role, enter AliyunHologresEncryptionDefaultRole in the RAM Role Name field, select Hologres from the Select Trusted Service drop-down list, and then click OK.

   4. In the Finish step, click Add Permissions to RAM Role.

   5. In the Add Permissions panel, set Authorized Scope to Alibaba Cloud Account. In the Select Policy section, click the Custom Policy tab and enter AliyunHologresEncryptionDefaultRolePolicy that you created in Step 1.

   6. Click OK. The RAM role is created and permissions are granted to the RAM role.

      After the RAM role is created, click the name of the role. On the Trust Policy Management tab, 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 Create a foreign table in Hologres to accelerate queries of 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.