Run MaxCompute SQL commands in Hologres-Hologres(Hologres)-阿里云帮助中心

exec_external_sql lets you run MaxCompute DDL statements directly from Hologres — no need to switch between consoles. This is particularly useful when you need to create a MaxCompute table and immediately export Hologres data to it in a single workflow.

Prerequisites

Before you begin, ensure that you have:

Hologres V0.10 or later. To check your instance version, open the instance details page in the Hologres console. If your version is earlier than V0.10, upgrade your instance before continuing.
Hologres connected to a development tool. This topic uses HoloWeb as an example. For setup instructions, see Connect to HoloWeb and run a query.
MaxCompute activated. For setup instructions, see .Use MaxCompute through the query editor
Permission to run SQL statements in MaxCompute. For details, see User planning and management.

Limitations

DDL only. exec_external_sql supports only Data Definition Language (DDL) statements: CREATE TABLE, ALTER TABLE, DESC TABLE, and DROP TABLE. To run Data Manipulation Language (DML) statements, use MaxCompute directly.
One statement per call. Each call to exec_external_sql runs exactly one SQL statement.
odps server only. The server parameter currently supports only the odps_server foreign server built into Hologres.

Syntax

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

Parameters are positional. To omit an intermediate parameter, use named-parameter syntax:

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

Parameters

Parameter	Type	Description	Default
`server`	string	The foreign server name. Hologres creates `odps_server` at the underlying layer automatically. For more information about foreign servers, see Postgres FDW.	If blank, `odps_server` is used.
`database`	string	The MaxCompute project name.	—
`sql`	string	The MaxCompute DDL statement to run. Must follow MaxCompute syntax. If the statement contains single quotation marks (`'`), wrap it in dollar-sign delimiters (`$$...$$`) instead.	—
`timeout_ms`	integer	Execution timeout in milliseconds. If the timeout is exceeded, the execution stops and a cancel instruction is sent to MaxCompute.	`60000` (60 s) if not specified or < 0
`options`	JSON string	SQL flags, equivalent to those set when submitting SQL through DataWorks or a MaxCompute client. To set multiple flags, use JSON format. For available flags, see SET operations.	—

The ad hoc query pages in HoloWeb and DataWorks do not support escape characters. Use the dollar-sign delimiter ($$...$$) when your SQL statement contains single quotation marks.

Examples

All examples use exec_external_sql to send DDL statements to a MaxCompute project.

Create a non-partitioned table

SELECT exec_external_sql(
  'odps_server',
  'mc_project',              -- MaxCompute project name
  'CREATE TABLE par_mc_table(id INT, name STRING);',
  5000                       -- Timeout: 5,000 ms
);

Create a partitioned table and add a partition

-- Step 1: Create a partitioned table (named parameters, timeout: 10,000 ms)
SELECT exec_external_sql(
  server     := 'odps_server',
  database   := 'mc_project',
  sql        := 'CREATE TABLE par_mc_table(id INT, name STRING) PARTITIONED BY (pt STRING);',
  timeout_ms := 10000
);

-- Step 2: Add a partition (dollar-sign delimiter escapes the single quote in '202102')
SELECT exec_external_sql(
  'odps_server',
  'mc_project',
  $$ALTER TABLE par_mc_table ADD IF NOT EXISTS PARTITION(pt='202102');$$
);

Create a table across regions with ODPS2 type flags

Use the options parameter to enable MaxCompute ODPS2 data types and decimal types when creating a table in a cross-region project.

SELECT exec_external_sql(
  'hangzhou_odps_server',
  'hologres_test',
  'CREATE TABLE mc_test(id INT, create_time DATETIME, decimal_column DECIMAL(38, 10));',
  50000,
  '{
    "odps.sql.type.system.odps2": "true",
    "odps.sql.decimal.odps2": "true"
  }'
);

Drop a table from MaxCompute

SELECT exec_external_sql(
  'odps_server',
  'mc_project',
  'DROP TABLE IF EXISTS mc_table;',
  50000
);

Export data from Hologres to MaxCompute end-to-end

Hologres V0.9 and later versions support exporting data to MaxCompute. Starting from Hologres V0.10, you can also create the MaxCompute table directly from Hologres and immediately export data to it — all without switching to the MaxCompute console. The following example exports data from a Hologres internal table to a non-partitioned MaxCompute table.

Create a Hologres internal table and insert sample data.

CREATE TABLE "public"."holo_table" (
  "id"   INT4,
  "name" TEXT
);

INSERT INTO "public"."holo_table" VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c');

Create a MaxCompute table to receive the data.

SELECT exec_external_sql(
  'odps_server',
  'mc_project',
  'CREATE TABLE mc_sink_table(id INT, name STRING);',
  5000
);

Create a foreign table in Hologres that maps 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;

Export the data. Enable the GUC parameter first, then insert.

Export all fields:

SET hg_experimental_enable_write_maxcompute = on; -- Beta feature; required before export
INSERT INTO mc_mapping_foreign_table
SELECT * FROM holo_table;

Export specific fields:

SET hg_experimental_enable_write_maxcompute = on; -- Beta feature; required before export
INSERT INTO mc_mapping_foreign_table (name)
SELECT name FROM holo_table;

What's next

Export data to MaxCompute — full reference for Hologres-to-MaxCompute data export, including partitioned table scenarios.

上一篇: Access Hologres as a MaxCompute foreign table 下一篇: Data asset management