All Products
Search
Document Center

Hologres:Execute MaxCompute SQL statements

Last Updated:Feb 20, 2024

In Hologres V0.10 and later, Hologres is further integrated with MaxCompute. You can execute MaxCompute SQL statements in Hologres. This allows you to manage MaxCompute data with ease. This topic describes how to execute MaxCompute SQL statements in Hologres.

Prerequisites

  • Hologres instances are purchased, and the Hologres instances are connected to HoloWeb. In this example, HoloWeb is used. For more information about how to connect Hologres instances to HoloWeb, see Connect to HoloWeb.

  • MaxCompute is activated. For more information, see Activate MaxCompute and DataWorks.

  • You have permissions to execute SQL statements in MaxCompute. For more information about the permissions in MaxCompute, see User planning and management.

Limits

  • Only Hologres V0.10 and later allow you to execute MaxCompute SQL statements in Hologres. You can view the version of your Hologres instance on the instance details page in the Hologres console. If the version of your Hologres instance is earlier than V0.10, 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.

  • We recommend that you execute only MaxCompute DDL statements in Hologres. If you need to execute MaxCompute DML statements, execute them in MaxCompute.

  • You can execute only one SQL statement at a time.

Syntax

  • Sample code

    select exec_external_sql(
      'server', 
      'database', 
      'sql' , 
      timeout_ms, 
      'options'
    );

    Specify the preceding parameters in sequence. If specific parameters need to be omitted, use the parameter names as the parameter values. Example:

    exec_external_sql(
                server:='odps_server' , 
                database:='odps_project_name' , 
                sql:='sql' , 
                timeout_ms:=timeout_ms , 
                options:='options'
    )
  • Parameters

    Parameter

    Description

    Remarks

    Example

    server

    The server where foreign tables reside. You can use the odps_server server that is created at the underlying layer of Hologres. The exec_external_sql function supports only the odps_server server. For more information about foreign servers, see postgres_fdw.

    If you do not specify a server, the odps_server server is used by default.

    'odps_server'

    database

    The name of the MaxCompute project.

    None.

    'seahawks'

    sql

    The MaxCompute SQL statement that you want to execute. We recommend that you execute only MaxCompute DDL statements. Valid values:

    • create table

    • alter table

    • desc table

    • drop table

    The SQL statement must conform to the MaxCompute SQL syntax. We recommend that you execute only MaxCompute DDL statements. If the SQL statement contains single quotation marks ('), you must add double dollar signs ($$) before and after the SQL statement to escape the single quotation marks. Example: $$SQL$$.

    Note

    Escape characters are not supported on the ad hoc query pages of HoloWeb and HoloStudio.

    'CREATE TABLE IF EXISTS MC_TBL ;'

    timeout_ms

    The timeout period for the execution of the SQL statement. Unit: milliseconds.

    If you do not specify a timeout period or specify a value smaller than 0, this parameter is set to 60000 by default. If the timeout period expires, Hologres stops executing the SQL statement and sends an instruction to MaxCompute to cancel the execution of the SQL statement.

    50000

    options

    The SQL properties that you can set when you submit SQL statements in the DataWorks console or on the MaxCompute client.

    For more information, see SET operations. If you need to set multiple properties, change the data type of this parameter to JSON.

    {"odps.sql.type.system.odps2":"true"} or { "odps.sql.type.system.odps2":"true", "odps.sql.decimal.odps2=":"true"}

Examples

You can execute MaxCompute SQL statements in Hologres. If you execute the CREATE TABLE statement, a table is created in the MaxCompute project that you specify. We recommend that you execute only MaxCompute DDL statements in Hologres.

  • Example 1: Create a non-partitioned MaxCompute table

    select exec_external_sql(
                'odps_server',
                'mc_project' , -- Specify the name of the MaxCompute project.
                'create table par_mc_table(id int,name string);' ,-- Create a MaxCompute table.
                5000  -- Set the timeout period to 5,000 ms.
                );                
  • Example 2: Create a partitioned MaxCompute table and specify a partition field

    -- Create a partitioned table.
    select exec_external_sql(
              server:='odps_server',
              database:='mc_project', -- Specify the name of the MaxCompute project.
              sql:='create table par_mc_table(id int,name string) partitioned by (pt string);', -- Create a partitioned table.
              timeout_ms:=10000-- Set the timeout period to 10,000 ms.
                      );
    -- Specify a partition field for the partitioned table.
    select exec_external_sql(
               'odps_server',
               'mc_project', -- Specify the name of the MaxCompute project.
               $$ALTER TABLE par_mc_table ADD IF NOT EXISTS partition(pt='202102');$$-- Specify a partition field.
                      );
  • Example 3: Create a MaxCompute table across regions

    select exec_external_sql(
                'hangzhou_odps_server' ,
                'hologres_test' ,
                'create table mc_test(id int,create_time datetime,decimal_column decimal(38, 10));' ,
                50000,    -- Set the timeout period to 50,000 ms.
                '{
                "odps.sql.type.system.odps2":"true",
                "odps.sql.decimal.odps2":"true"
      }'                  -- Set MaxCompute SQL properties. In this example, new data types and the DECIMAL type are enabled.
    );
                        
  • Example 4: Delete a MaxCompute table

    select exec_external_sql(
                'odps_server',
                'mc_project',  -- Specify the name of the MaxCompute project.
                'drop table if exists mc_table;' ,-- Delete a MaxCompute table.
                50000
                );               
  • Example 5: Export data to MaxCompute by executing SQL statements

    In Hologres V0.9 and later, you can export data to MaxCompute by executing SQL statements. For more information, see Export data to MaxCompute by executing SQL statements. Before you export data, you must create a table in MaxCompute to receive data. Previously, this is inconvenient. In Hologres V0.10 and later, you can execute MaxCompute SQL statements in Hologres. This way, you can create a MaxCompute table and export data in Hologres.

    This example shows you how to export data from a non-partitioned Hologres table to a non-partitioned MaxCompute table. You can perform the following steps:

    1. Create a Hologres internal table whose data is to be exported to MaxCompute. For example, use the following DDL statements to create a table named holo_table and insert the sample data into the table:

      create table "public"."holo_table" (
       "id" int4,
       "name" text
      );
      
      insert into "public"."holo_table" values 
      (1,'a'),
      (2,'b'),
      (3,'c');
    2. Create a MaxCompute table to receive data.

      select exec_external_sql(
                  'odps_server',
                  'mc_project' , -- Specify the name of the MaxCompute project.
                  'create table mc_sink_table(id int,name string);' ,-- Create a MaxCompute table to receive data.
                  5000-- Set the timeout period to 5,000 ms.
                  );
    3. Create a foreign table in Hologres to map the source table in MaxCompute.

      begin;
      create foreign table "public"."mc_mapping_foreign_table" (
       "id" int4,
       "name" text
      )
      server odps_server_bj
      options (project_name 'default_project_2361b62', table_name 'mc_sink_table');
      commit;
    4. Execute the following SQL statements in Hologres to export data to MaxCompute:

      • Export the data of all fields.

        set hg_experimental_enable_write_maxcompute = on;-- You must set the Grand Unified Configuration (GUC) parameter hg_experimental_enable_write_maxcompute to on because this data export feature is in a beta version.
        insert into mc_mapping_foreign_table
        select * from holo_table;
      • Export the data of specific fields.

        set hg_experimental_enable_write_maxcompute = on;-- You must set the GUC parameter hg_experimental_enable_write_maxcompute to on because this data export feature is in a beta version.
        insert into mc_mapping_foreign_table (name)
        select name from holo_table;

      For more information, see Export data to MaxCompute by executing SQL statements.