MaxCompute:UNLOAD

Authorize MaxCompute to access OSS

One-click authorization provides higher security. For details, see STS authorization.

The examples in this topic use one-click authorization with the default role AliyunODPSDefaultRole.

Authorize MaxCompute to access Hologres

Create a RAM role, grant it access to MaxCompute, and add it to the Hologres instance. For details, see Create a Hologres foreign table in STS mode.

Syntax

UNLOAD FROM {<select_statement> | <table_name> [PARTITION (<pt_spec>)]}
INTO
LOCATION <external_location>
STORED BY <StorageHandler>
[WITH SERDEPROPERTIES ('<property_name>'='<property_value>', ...)];

Parameters

Key SERDEPROPERTIES for OSS:

To control the number of output files, set the data size (in MB) that a single worker reads from the MaxCompute table before the export:

SET odps.stage.mapper.split.size=256;

The MaxCompute table is compressed before export. The exported data size is approximately four times the pre-export data size.

Examples

This example exports data from the sale_detail table:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

Step 1: Log on to the OSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS console. Create the directory mc-unload/data_location/ in an OSS bucket in the oss-cn-hangzhou region. For more information, see Create buckets.

The OSS path for this bucket is:

oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-unload/data_location

Step 2: Log on to the MaxCompute client and run the UNLOAD statement.

Example 1: Export all data as CSV with GZIP compression

-- Control output file count: set the data size (MB) per worker.
SET odps.stage.mapper.split.size=256;
-- Export all data from sale_detail as a GZIP-compressed CSV file.
UNLOAD FROM
  (SELECT * FROM sale_detail)
INTO
  LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-unload/data_location'
  STORED BY 'com.aliyun.odps.CsvStorageHandler'
  WITH SERDEPROPERTIES (
    'odps.properties.rolearn'='acs:ram::<uid>:role/AliyunODPSDefaultRole',
    'odps.text.option.gzip.output.enabled'='true'
  );

The following statement is equivalent — it references the table directly, which does not generate a query statement and incurs no query fee:

SET odps.stage.mapper.split.size=256;
UNLOAD FROM sale_detail
INTO
  LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-unload/data_location'
  STORED BY 'com.aliyun.odps.CsvStorageHandler'
  WITH SERDEPROPERTIES (
    'odps.properties.rolearn'='acs:ram::<uid>:role/AliyunODPSDefaultRole',
    'odps.text.option.gzip.output.enabled'='true'
  );

Example 2: Export a partition as TSV with GZIP compression

SET odps.stage.mapper.split.size=256;
UNLOAD FROM sale_detail PARTITION (sale_date='2013', region='china')
INTO
  LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-unload/data_location'
  STORED BY 'com.aliyun.odps.TsvStorageHandler'
  WITH SERDEPROPERTIES (
    'odps.properties.rolearn'='acs:ram::139699392458****:role/AliyunODPSDefaultRole',
    'odps.text.option.gzip.output.enabled'='true'
  );

Step 3: Log on to the OSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS console to verify the exported files in the target directory.

• Result for Example 1:

• Result for Example 2:

Syntax

UNLOAD FROM {<select_statement> | <table_name> [PARTITION (<pt_spec>)]}
INTO
LOCATION <external_location>
[ROW FORMAT SERDE '<serde_class>'
  [WITH SERDEPROPERTIES ('<property_name>'='<property_value>', ...)]
]
STORED AS <file_format>
[PROPERTIES ('<tbproperty_name>'='<tbproperty_value>')];

Parameters

Key PROPERTIES for open source formats:

If the output is compressed with Snappy or LZO, the file name extension (.snappy or .lzo) is not shown in the file name.

Examples

This example uses the same sale_detail table as the previous section.

Step 1: Log on to the OSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS console. Create the directory mc-unload/data_location/ in your OSS bucket. For more information, see Create buckets.

Step 2: Log on to the MaxCompute client and run the UNLOAD statement.

Example 1: Export all data as Parquet with Snappy compression

SET odps.stage.mapper.split.size=256;
UNLOAD FROM
  (SELECT * FROM sale_detail)
INTO
  LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-unload/data_location'
  ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  WITH SERDEPROPERTIES ('odps.properties.rolearn'='acs:ram::<uid>:role/AliyunODPSDefaultRole')
  STORED AS PARQUET
  PROPERTIES ('mcfed.parquet.compression'='SNAPPY');

Example 2: Export a partition as Parquet with Snappy compression

SET odps.stage.mapper.split.size=256;
UNLOAD FROM sale_detail PARTITION (sale_date='2013', region='china')
INTO
  LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-unload/data_location'
  ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  WITH SERDEPROPERTIES ('odps.properties.rolearn'='acs:ram::139699392458****:role/AliyunODPSDefaultRole')
  STORED AS PARQUET
  PROPERTIES ('mcfed.parquet.compression'='SNAPPY');

Step 3: Log on to the OSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS consoleOSS console to verify the exported files.

• Result for Example 1:

• Result for Example 2:

Syntax

UNLOAD FROM {<select_statement> | <table_name>}
INTO
LOCATION <external_location>
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
WITH SERDEPROPERTIES (
  'odps.properties.rolearn'='<ram_arn>',
  'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
  'odps.federation.jdbc.target.db.type'='holo'
);

Parameters

Required SERDEPROPERTIES for Hologres:

The data types of fields in the Hologres destination table must match those in the MaxCompute source table. For the type mapping, see Data type mappings between MaxCompute and Hologres.

Example

This example exports data from the data_test table in MaxCompute to a Hologres table named mc_2_holo:

+------------+------+
| id         | name |
+------------+------+
| 3          | rgege |
| 4          | Gegegegr |
+------------+------+

Step 1: Create the Hologres table mc_2_holo in the test database. Run the following statement in HoloWeb. For more information, see Connect to HoloWeb and perform queries.

CREATE TABLE mc_2_holo (id INT, name TEXT);

Step 2: Log on to the MaxCompute client and run the UNLOAD statement:

UNLOAD FROM
  (SELECT * FROM data_test)
INTO
  LOCATION 'jdbc:postgresql://hgprecn-cn-5y**********-cn-hangzhou-internal.hologres.aliyuncs.com:80/test?ApplicationName=MaxCompute&currentSchema=public&useSSL=false&table=mc_2_holo/'
  STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
  WITH SERDEPROPERTIES (
    'odps.properties.rolearn'='acs:ram::13**************:role/aliyunodpsholorole',
    'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
    'odps.federation.jdbc.target.db.type'='holo'
  );

Step 3: Query the exported data in Hologres to verify the result:

SELECT * FROM mc_2_holo;

Expected output:

id    name
4     Gegegegr
3     rgege