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 used to connect to the instance. For more information, see HoloWeb quick start.

Precautions

When you export data to MaxCompute by executing SQL statements in Hologres, take note of the following items:
  • Only Hologres V0.9 and later support data export to MaxCompute by executing SQL statements. If the version of your instance is earlier than V0.9, submit a ticket or join the Hologres DingTalk group to update the instance.
  • Hologres allows you to export data to MaxCompute across regions. However, data export performance is higher if your Hologres instance and the destination MaxCompute project resides within the same region because the network is more stable.
  • Hologres supports only a single 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 data types in Hologres are mapped to those in MaxCompute. However, Hologres does not support complex data types such as DATE, ARRAY, MAP, and STRUCT. For more information about data type mappings between Hologres and MaxCompute, see Data types.
  • The number of MaxCompute write servers is limited. To obtain high 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.
  • Hologres V1.1 and later support data export to MaxCompute. If the version of your 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;
  • Hologres V1.3 and later support the export of ARRAY data to MaxCompute.

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 internal table to MaxCompute. In this example, a foreign table named mapping_foreign_table is created.
  4. Execute the following SQL statements as required in Hologres to export data to MaxCompute:
    -- Export data of the 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.
    The following sample code shows how to create a Hologres internal table to export data to MaxCompute.
    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 destination MaxCompute table to receive data. Fields and data types in the MaxCompute table must be in one-to-one correspondence 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 from 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 MaxCompute.
    You can create a foreign table in Hologres and use this foreign table to map data. Alternatively, you can import a MaxCompute table as a foreign table in Hologres. 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');-- Set the project_name parameter to the name of the destination MaxCompute project and the table_name parameter to the name of the destination MaxCompute table.
    COMMIT;
  4. Export data from Hologres to MaxCompute.
    Execute the following statements as needed to export data to the destination MaxCompute table. You can choose to export the data of specified fields or all fields. You must specify fields in the same sequence as they are in the Hologres internal table.
    -- Export data of the 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.
    The following sample code shows how to create a Hologres internal table to export data to MaxCompute.
    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 partitioned 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 codes shows how to create a destination MaxCompute table to receive data. The MaxCompute table can contain one or two levels of partitions. Fields and data types in the MaxCompute table must be in one-to-one correspondence with those in the Hologres internal table. For more information about the syntax, see Table operations.
    -- Case 1: Create a MaxCompute table that contains a single 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 internal table to MaxCompute.
    You can create a foreign table in Hologres and use this foreign table to map data. The following sample codes shows how to create a foreign table in Hologres.
    -- Create a foreign table to map data to the MaxCompute table that contains a single 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 data 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');-- Set the project_name parameter to the name of the destination MaxCompute project and the table_name parameter to the name of the destination MaxCompute table.
    COMMIT;
                            
  4. Export data from Hologres to MaxCompute.
    Execute the following statements as required to export data to the destination MaxCompute table. You can choose to export the data of specified fields or 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 a single level of partitions.
    -- Example 1: Export data from the parent partitioned 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 partitioned 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 partitioned 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 partitioned table in Hologres.
    INSERT INTO mapping_par_bank_2 SELECT * FROM "public"."par_bank_20190901" where y='1';