How to execute MaxCompute SQL statements in Hologres - Hologres

Hologres V0.10 and later versions feature improved integration with MaxCompute. This lets you execute MaxCompute SQL statements in Hologres to quickly operate on MaxCompute data. This topic describes how to execute MaxCompute SQL statements in Hologres.

Prerequisites

Hologres is activated and connected to a development tool. This topic uses HoloWeb as an example. For more information, see Connect to HoloWeb and run a query.
MaxCompute is activated. For more information, see Activate MaxCompute.
The current user has permission to execute SQL statements in MaxCompute. For more information about MaxCompute permissions, see User planning and management.

Limits

Only Hologres V0.10 and later versions support executing MaxCompute SQL statements. To check your current instance version, go to the instance details page in the Hologres console. If your instance version is earlier than V0.10, you must upgrade your instance. For more information, see Common upgrade preparation failure errors or join the Hologres DingTalk group for feedback. For more information, see How do I get more online support?.
You can execute only Data Definition Language (DDL) statements in Hologres. To execute Data Manipulation Language (DML) statements, you must use MaxCompute.
Each call can execute only one SQL statement.

Syntax

Syntax example

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

You must specify the parameters in order. To omit a parameter, you must specify the parameter names explicitly. The following code provides an example:

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

Parameter Description

Parameter name	Description	Notes	Example
server	The name of the foreign server. You can directly call the foreign table server named odps_server that is created in the Hologres underlying layer. This function currently supports only the odps server. For more information about the principles of foreign servers, see Postgres FDW.	If this is an empty string, odps_server is used as the foreign server.	`'odps_server'`
database	The name of the MaxCompute project.	None	`'seahawks'`
sql	The MaxCompute SQL statement to execute. Execute only DDL statements, such as: create table alter table desc table drop table	The SQL statement must follow MaxCompute syntax. Execute only DDL statements. If your SQL statement contains single quotation marks ('), enclose the statement in double dollar signs (`$$SQL$$`) to escape them. Note The ad-hoc query pages in HoloWeb and DataWorks do not support escape characters.	`'CREATE TABLE IF EXISTS MC_TBL ;'`
timeout_ms	The execution timeout period, in milliseconds.	If this parameter is not specified or is less than 0, the value is set to 60000 ms. If the timeout period is exceeded, the execution stops, and a cancel instruction is sent to MaxCompute.	50000
options	The SQL flags that you typically need to set when you submit SQL using DataWorks or a MaxCompute client.	For information about specific configuration items, see SET operations. To set multiple flags, change the field type to JSON format.	`{"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. For example, if you run a `CREATE TABLE` statement, a table is created in the specified MaxCompute project. Currently, you can execute only DDL statements.

Example 1: Create a non-partitioned MaxCompute table

select exec_external_sql(
            'odps_server',
            'mc_project' , --The project name
            'create table par_mc_table(id int,name string);' ,--Create a table in MaxCompute
            5000  --The timeout period is 5000 ms
            );

Example 2: Create a partitioned MaxCompute table and specify a partition

--Create a partitioned table
select exec_external_sql(
          server:='odps_server',
          database:='mc_project', --The MaxCompute project name
          sql:='create table par_mc_table(id int,name string) partitioned by (pt string);', --Create a partitioned table
          timeout_ms:=10000--The timeout period is 10000 ms
                  );
--Specify a partition for the partitioned table
select exec_external_sql(
           'odps_server',
           'mc_project', --The MaxCompute project name
           $$ALTER TABLE par_mc_table ADD IF NOT EXISTS partition(pt='202102');$$--Specify a partition
                  );

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,    --Timeout period
            '{
            "odps.sql.type.system.odps2":"true",
            "odps.sql.decimal.odps2":"true"
  }'                  --The flag settings for MaxCompute. They enable the new data type and the decimal type.
);

Example 4: Delete a table from MaxCompute

select exec_external_sql(
            'odps_server',
            'mc_project',  --The MaxCompute project name
            'drop table if exists mc_table;' ,--Delete the table from MaxCompute
            50000
            );

Example 5: Use this feature to export data to MaxCompute using SQL

Hologres V0.9 and later versions support exporting data to MaxCompute. However, this method can be complex because you must first create a table in MaxCompute to receive the data. Hologres V0.10 and later versions allow you to create the table by running a MaxCompute SQL statement in Hologres and then export the data. This provides an end-to-end development experience.

This example imports non-partitioned data from Hologres to a non-partitioned table in MaxCompute:
1. In Hologres, create an internal table, such as `holo_table`, from which to export data to MaxCompute. The following code provides the sample DDL statement and data:
```
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 the data.
```
select exec_external_sql(
            'odps_server',
            'mc_project' , --The project name
            'create table mc_sink_table(id int,name string);' ,--Create a table in MaxCompute to receive data
            5000--The timeout period is 5000 ms
            );
```
3. In Hologres, create a new foreign table to map to the MaxCompute table.
```
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. In Hologres, export the data to MaxCompute using an SQL statement.
  - Export data from all fields
```
set hg_experimental_enable_write_maxcompute = on;-- This is a beta feature. You must enable the GUC parameter.
insert into mc_mapping_foreign_table
select * from holo_table;
```
  - Export data from specific fields
```
set hg_experimental_enable_write_maxcompute = on;-- This is a beta feature. You must enable the GUC parameter.
insert into mc_mapping_foreign_table (name)
select name from holo_table;
```
  For more information about data exporting, see Export data to MaxCompute.