All Products
Search
Document Center

Hologres:Export data to MaxCompute by executing SQL statements

Last Updated:Jul 18, 2023

This topic describes how to export data to MaxCompute by executing SQL statements in Hologres.

Prerequisites

  • MaxCompute is activated, and a development tool is connected to MaxCompute.

  • A Hologres instance is created, and a development tool is connected to the instance. For more information, see Connect to HoloWeb.

Limits

  • Only Hologres V0.9 and later allow you to export data to MaxCompute by executing SQL statements. If the version of your Hologres instance is earlier than V0.9, manually upgrade your Hologres instance in the Hologres console or join a Hologres DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Instance upgrades. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.

  • The data types in Hologres are mapped to those in MaxCompute. In versions earlier than Hologres V1.3, Hologres does not support complex data types such as DATE, ARRAY, MAP, and STRUCT. Hologres V1.3 and later allow you to write data of ARRAY and DATE types to MaxCompute. For more information about data type mappings between Hologres and MaxCompute, see Data types.

  • Hologres V1.1 and later allow you to export data to MaxCompute. If the version of your Hologres instance is earlier than V1.1, you must add the following statement before the INSERT statement:

    -- This statement is required in Hologres V0.9 and V0.10.
    set hg_experimental_enable_write_maxcompute = on;
  • You cannot export data to a transactional table in MaxCompute by executing SQL statements in Hologres.

Usage notes

When you export data to MaxCompute by executing SQL statements in Hologres, take note of the following items:

  • Hologres allows you to export data to MaxCompute across regions. However, data export performance is better if your Hologres instance and the destination MaxCompute project reside in the same region because the network is more stable.

  • Hologres supports a maximum of one level of partitions but allows you to export data to the subpartitions of MaxCompute tables. To export data to the subpartitions of a MaxCompute table, you must map the partition key values of the MaxCompute table to the fields in the source Hologres table. In addition, Hologres allows you to export data from a partitioned Hologres table to a non-partitioned MaxCompute table.

  • The number of MaxCompute write servers is limited. To obtain better performance, we recommend that you do not export data to MaxCompute during peak hours such as early mornings.

  • If a field of the TIMESTAMPTZ type exists, the valid values of the field range from 1677-09-21 00:00:00 to 2262-04-12 00:00:00.

Procedure

  1. Create an internal table in Hologres to export data to MaxCompute. In this example, an internal table named holo_source is created.

  2. Create a MaxCompute table to receive data. In this example, a MaxCompute table named mc_sink is created.

  3. Create a foreign table in Hologres to map the data of the Hologres internal table to the MaxCompute table. In this example, a foreign table named mapping_foreign_table is created.

  4. Execute the following SQL statements in Hologres to export data to MaxCompute:

    -- Export the data of specified fields.
    insert into mapping_foreign_table
    select x,x,x from holo_soruce;-- Replace x,x,x with the names of the fields whose data you want to export.
    
    -- Export the data of all fields.
    insert into mapping_foreign_table
    select * from holo_soruce;

The following sections describe how to synchronize data between non-partitioned tables and between partitioned tables.

Export data from a non-partitioned Hologres table to a non-partitioned MaxCompute table

  1. Create an internal table in Hologres as the data source.

    You can create a Hologres internal table to export data to MaxCompute. The following sample code shows how to create a Hologres internal table:

    BEGIN;
    CREATE  TABLE "public"."bank" (
     "age" int8,
     "job" text,
     "marital" text,
     "education" text,
     "card" text,
     "housing" text,
     "loan" text,
     "contact" text,
     "month" text,
     "day_of_week" text,
     "duration" text,
     "campaign" int8,
     "pdays" float8,
     "previous" float8,
     "poutcome" text,
     "emp_var_rate" float8,
     "cons_price_idx" float8,
     "cons_conf_idx" float8,
     "euribor3m" float8,
     "nr_employed" float8,
     "y" int8
    );
    COMMIT;
  2. Create a MaxCompute table to receive data.

    The following sample code shows how to create a MaxCompute table to receive data. The fields and data types in the MaxCompute table must correspond with those in the Hologres internal table. For more information about the syntax, see Table operations.

    CREATE TABLE IF NOT EXISTS mc_bank
    (
     age             BIGINT COMMENT 'Age',
     job             STRING COMMENT 'Job type',
     marital         STRING COMMENT 'Marital status',
     education       STRING COMMENT 'Education level',
     card            STRING COMMENT 'Credit card available or not',
     housing         STRING COMMENT 'Mortgage',
     loan            STRING COMMENT 'Loan',
     contact         STRING COMMENT 'Contact information',
     month           STRING COMMENT 'Month',
     day_of_week     STRING COMMENT 'Day of the week',
     duration        STRING COMMENT 'Duration',
     campaign        BIGINT COMMENT 'Number of contacts during the campaign',
     pdays           DOUBLE COMMENT 'Time elapsed since the last contact',
     previous        DOUBLE COMMENT 'Number of contacts with the customer',
     poutcome        STRING COMMENT 'Result of the previous marketing campaign',
     emp_var_rate    DOUBLE COMMENT 'Employment change rate',
     cons_price_idx  DOUBLE COMMENT 'Consumer price index',
     cons_conf_idx   DOUBLE COMMENT 'Consumer confidence index',
     euribor3m       DOUBLE COMMENT 'Euro deposit rate',
     nr_employed     DOUBLE COMMENT 'Number of employees',
     y               BIGINT COMMENT 'Time deposit available or not'
    );
  3. Create a foreign table in Hologres to map the data of the Hologres internal table to the MaxCompute table.

    You can create a foreign table in Hologres and use this foreign table to map the data of the Hologres internal table to the MaxCompute table. You can also execute the IMPORT FOREIGN SCHEMA statement to create a corresponding foreign table in Hologres for the MaxCompute table. The following sample code shows how to create a foreign table in Hologres:

    BEGIN;
    CREATE FOREIGN TABLE "public"."mapping_bank" (
     "age" int8,
     "job" text,
     "marital" text,
     "education" text,
     "card" text,
     "housing" text,
     "loan" text,
     "contact" text,
     "month" text,
     "day_of_week" text,
     "duration" text,
     "campaign" int8,
     "pdays" float8,
     "previous" float8,
     "poutcome" text,
     "emp_var_rate" float8,
     "cons_price_idx" float8,
     "cons_conf_idx" float8,
     "euribor3m" float8,
     "nr_employed" float8,
     "y" int8
    )
    SERVER odps_server
    OPTIONS (project_name 'xxx',table_name 'mc_bank');-- Replace the project_name parameter with the name of the destination MaxCompute project and the table_name parameter with the name of the destination MaxCompute table.
    COMMIT;

    The following table describes the parameters in the preceding syntax.

    Parameter

    Description

    project_name

    The name of the destination MaxCompute project.

    table_name

    The name of the destination MaxCompute table.

  4. Export data from Hologres to MaxCompute.

    Execute the following statements to export data from Hologres to the destination MaxCompute table. You can export the data of specified fields or of all fields. You must specify fields in the same sequence as they are in the Hologres internal table.

    -- Export the data of specified fields.
    insert into mapping_bank 
    select age,job from bank;
    
    -- Export the data of all fields.
    insert into mapping_bank 
    select *from bank;
                            

Export data from a partitioned Hologres table to a partitioned MaxCompute table

  1. Create an internal table in Hologres as the data source.

    You can create a Hologres internal table to export data to MaxCompute. The following sample code shows how to create a Hologres internal table:

    BEGIN;
    CREATE TABLE "public"."par_bank" (
     "age" int8,
     "job" text,
     "marital" text,
     "education" text,
     "default" text,
     "housing" text,
     "loan" text,
     "contact" text,
     "month" text,
     "day_of_week" text,
     "duration" text,
     "campaign" int8,
     "pdays" float8,
     "previous" float8,
     "poutcome" text,
     "emp_var_rate" float8,
     "cons_price_idx" float8,
     "cons_conf_idx" float8,
     "euribor3m" float8,
     "nr_employed" float8,
     "y" int8,
     "ds" text
    )
    PARTITION BY list (ds);
    COMMIT;
    
    -- Child tables are required.
    CREATE TABLE "public"."par_bank_20190830" PARTITION OF "public"."par_bank" FOR VALUES IN ('20190830');
    
    CREATE TABLE "public"."par_bank_20190901" PARTITION OF "public"."par_bank" FOR VALUES IN ('20190901');
  2. Create a MaxCompute table to receive data.

    The following sample code shows how to create a MaxCompute table to receive data. The MaxCompute table can contain one or two levels of partitions. The fields and data types in the MaxCompute table must correspond with those in the Hologres internal table. For more information about the syntax, see Table operations.

    -- Case 1: Create a MaxCompute table that contains only one level of partitions.
    CREATE TABLE IF NOT EXISTS mc_par_bank
    (
        age            BIGINT COMMENT 'Age',
        job            STRING COMMENT 'Job type',
        marital        STRING COMMENT 'Marital status',
        education      STRING COMMENT 'Education level',
        default        STRING COMMENT 'Credit card available or not',
        housing        STRING COMMENT 'Mortgage',
        loan           STRING COMMENT 'Loan',
        contact        STRING COMMENT 'Contact information',
        month          STRING COMMENT 'Month',
        day_of_week    STRING COMMENT 'Day of the week',
        duration       STRING COMMENT 'Duration',
        campaign       BIGINT COMMENT 'Number of contacts during the campaign',
        pdays          DOUBLE COMMENT 'Time elapsed since the last contact',
        previous       DOUBLE COMMENT 'Number of contacts with the customer',
        poutcome       STRING COMMENT 'Result of the previous marketing campaign',
        emp_var_rate   DOUBLE COMMENT 'Employment change rate',
        cons_price_idx DOUBLE COMMENT 'Consumer price index',
        cons_conf_idx  DOUBLE COMMENT 'Consumer confidence index',
        euribor3m      DOUBLE COMMENT 'Euro deposit rate',
        nr_employed    DOUBLE COMMENT 'Number of employees',
        y              BIGINT COMMENT 'Time deposit available or not'
    ) 
    PARTITIONED BY
    (
        ds             STRING
    );
    -- Create the required partitions if they do not exist.
    alter table mc_par_bank add if not exists partition (ds='20190830');
    alter table mc_par_bank add if not exists partition (ds='20190901');
    
    
    -- Case 2: Create a MaxCompute table that contains two levels of partitions.
    CREATE TABLE IF NOT EXISTS mc_par_bank_2
    (
        age            BIGINT COMMENT 'Age',
        job            STRING COMMENT 'Job type',
        marital        STRING COMMENT 'Marital status',
        education      STRING COMMENT 'Education level',
        default        STRING COMMENT 'Credit card available or not',
        housing        STRING COMMENT 'Mortgage',
        loan           STRING COMMENT 'Loan',
        contact        STRING COMMENT 'Contact information',
        month          STRING COMMENT 'Month',
        day_of_week    STRING COMMENT 'Day of the week',
        duration       STRING COMMENT 'Duration',
        campaign       BIGINT COMMENT 'Number of contacts during the campaign',
        pdays          DOUBLE COMMENT 'Time elapsed since the last contact',
        previous       DOUBLE COMMENT 'Number of contacts with the customer',
        poutcome       STRING COMMENT 'Result of the previous marketing campaign',
        emp_var_rate   DOUBLE COMMENT 'Employment change rate',
        cons_price_idx DOUBLE COMMENT 'Consumer price index',
        cons_conf_idx  DOUBLE COMMENT 'Consumer confidence index',
        euribor3m      DOUBLE COMMENT 'Euro deposit rate',
        nr_employed    DOUBLE COMMENT 'Number of employees'
    ) 
    PARTITIONED BY
    (
        y              BIGINT, 
        ds             STRING
    );
    
    alter table mc_par_bank_2 add if not exists partition (y='1',ds='20190830');
    alter table mc_par_bank_2 add if not exists partition (y='1',ds='20190901');
  3. Create a foreign table in Hologres to map the data of the Hologres internal table to the MaxCompute table.

    You can create a foreign table in Hologres and use this foreign table to map the data of the Hologres internal table to the MaxCompute table. The following sample code shows how to create a foreign table in Hologres:

    -- Create a foreign table to map the data of the Hologres internal table to the MaxCompute table that contains only one level of partitions.
    BEGIN;
    CREATE FOREIGN TABLE "public"."mapping_par_bank" (
     "age" int8,
     "job" text,
     "marital" text,
     "education" text,
     "default" text,
     "housing" text,
     "loan" text,
     "contact" text,
     "month" text,
     "day_of_week" text,
     "duration" text,
     "campaign" int8,
     "pdays" float8,
     "previous" float8,
     "poutcome" text,
     "emp_var_rate" float8,
     "cons_price_idx" float8,
     "cons_conf_idx" float8,
     "euribor3m" float8,
     "nr_employed" float8,
     "y" int8,
     "ds" text
    )
    SERVER odps_server
    OPTIONS (project_name 'xxx',table_name 'mc_par_bank');
    COMMIT;
    
    
    -- Create a foreign table to map the data of the Hologres internal table to the MaxCompute table that contains two levels of partitions.
    BEGIN;
    CREATE FOREIGN TABLE "public"."mapping_par_bank_2" (
     "age" int8,
     "job" text,
     "marital" text,
     "education" text,
     "default" text,
     "housing" text,
     "loan" text,
     "contact" text,
     "month" text,
     "day_of_week" text,
     "duration" text,
     "campaign" int8,
     "pdays" float8,
     "previous" float8,
     "poutcome" text,
     "emp_var_rate" float8,
     "cons_price_idx" float8,
     "cons_conf_idx" float8,
     "euribor3m" float8,
     "nr_employed" float8,
     "y" int8,
     "ds" text
    )
    SERVER odps_server
    OPTIONS (project_name 'xxx',table_name 'mc_par_bank_2');// Replace the project_name parameter with the name of the destination MaxCompute project and the table_name parameter with the name of the destination MaxCompute table.
    COMMIT;
                            

    The following table describes the parameters in the preceding syntax.

    Parameter

    Description

    project_name

    The name of the destination MaxCompute project.

    table_name

    The name of the destination MaxCompute table.

  4. Export data from Hologres to MaxCompute.

    Execute the following statements to export data from Hologres to the destination MaxCompute table. You can export the data of specified fields or of all fields. You must specify fields in the same sequence as they are in the Hologres internal table.

    -- Export data to the MaxCompute table that contains only one level of partitions.
    -- Example 1: Export data from the parent table in Hologres by using a WHERE clause.
    INSERT INTO mapping_par_bank SELECT * FROM "public"."par_bank" WHERE ds='20190830';
    
    -- Example 2: Export data from the corresponding child table in Hologres.
    insert into mapping_par_bank select * from "public"."par_bank_20190901";
    
    
    -- Export data to the MaxCompute table that contains two levels of partitions.
    -- Example 1: Export data from the parent table in Hologres by using a WHERE clause.
    INSERT INTO mapping_par_bank_2 SELECT * FROM "public"."par_bank" WHERE y='1' and ds='20190830';
    
    -- Example 2: Export data from the corresponding child table in Hologres.
    INSERT INTO mapping_par_bank_2 SELECT * FROM "public"."par_bank_20190901" where y='1';

FAQ

  • Problem description

    The error message internal error: odps_writer_xxx,UploadId: xxx, Block 0 close odps writer failed: RequestId=xxx, ErrorCode=FlowExceeded, ErrorMessage= GlobalSlot Quota is returned when I export data to a MaxCompute table.

  • Cause

    When you use SQL statements in Hologres to export data to MaxCompute, the Tunnel SDK service of MaxCompute is used to ensure high-performance data export. For more information, see Tunnel commands. However, when you use the Tunnel SDK service of MaxCompute, the number of concurrent write requests and the data size are limited. For more information, see Limits. During peak hours of the Tunnel SDK service of MaxCompute, if the write task triggers write limits, the preceding error message is returned.

  • Solution

    • Export data to MaxCompute again.

    • If the error persists after you export data to MaxCompute again, execute the following statement to set a rate of concurrent data writes to MaxCompute:

      -- Set a rate of concurrent data writes to MaxCompute. We recommend that you set the hg_experimental_write_maxcompute_dop parameter to a value from 0 to the shard count.
      set hg_experimental_write_maxcompute_dop =<count>;