LOAD
LOAD statements let you import data from an external data store directly into a MaxCompute table or partition. Supported sources include Object Storage Service (OSS) and Hologres. To import data from Amazon Redshift or BigQuery, first export it to OSS, then run a LOAD statement.
Run LOAD statements in the MaxCompute client, the DataWorks console, or MaxCompute Studio.
How it works
A LOAD statement reads files from an external location and writes them into a destination table. Two variants control write behavior:
| Statement | Behavior |
|---|---|
LOAD INTO |
Appends data to the table or partition. Running it multiple times creates duplicate rows. |
LOAD OVERWRITE |
Clears the table or partition first, then inserts the data. Safe to re-run. |
If you run LOAD INTO more than once against the same source, each run appends all the source rows again, resulting in duplicates. Use LOAD OVERWRITE when you need idempotent loads, or implement deduplication logic downstream.
Two syntax forms determine how the external data is parsed:
| Method | Use when |
|---|---|
Built-in storage handler (stored by) |
Importing CSV from OSS or any data from Hologres via JDBC |
Open source format (stored as) |
Importing ORC, Parquet, TEXTFILE, or other Hive-compatible formats from OSS |
Prerequisites
Before you begin, ensure that you have:
-
The
CreateTablepermission andAlterpermission on the MaxCompute project. See MaxCompute permissions. -
Authorization for MaxCompute to access the external data store (see the authorization steps in each method below).
Limitations
-
Data can only be imported into MaxCompute projects in the same region as the external data store.
-
When importing from OSS into a partitioned table, the table schema (excluding partition key columns) must match the layout of the data in OSS. The OSS data must not include partition key columns.
-
When importing from Hologres:
-
Hologres partitioned tables are not supported as a source.
-
Hologres external tables using the dual-signature authorization mode are not supported.
-
Import CSV or Hologres data using a storage handler
Use this method to import CSV files from OSS or tables from Hologres.
Syntax
{LOAD OVERWRITE | LOAD INTO} TABLE <table_name> [PARTITION (<pt_spec>)]
FROM LOCATION <external_location>
STORED BY <storage_handler>
[WITH SERDEPROPERTIES (<options>)];Parameters
Common parameters
| Parameter | Required | Description |
|---|---|---|
table_name |
Yes | The destination table. Create the table before running the statement. The schema (excluding partition key columns) must match the external data layout. |
pt_spec |
No | Partition specification in the format partition_col1=value1, partition_col2=value2, ... |
OSS-specific parameters
| Parameter | Required | Description |
|---|---|---|
external_location |
Yes | The OSS directory containing the source data. Format: 'oss://<oss_endpoint>/<path>/'. MaxCompute reads all files in the directory. See OSS domain names for endpoint formats. |
storage_handler |
Yes | Set to com.aliyun.odps.CsvStorageHandler for CSV files. |
odps.properties.rolearn |
Yes | The Alibaba Cloud Resource Name (ARN) of the AliyunODPSDefaultRole RAM role. Format: acs:ram::<account_id>:role/aliyunodpsdefaultrole. |
odps.text.option.delimiter |
No | The field delimiter character. Defaults to comma (,). |
Hologres-specific parameters
| Parameter | Required | Description |
|---|---|---|
external_location |
Yes | The JDBC URL of the Hologres instance. Format: 'jdbc:postgresql://<endpoint>:<port>/<database>?ApplicationName=MaxCompute&[currentSchema=<schema>&][useSSL={true|false}&]table=<holo_table_name>/' |
storage_handler |
Yes | Set to com.aliyun.odps.jdbc.JdbcStorageHandler. |
odps.properties.rolearn |
Yes | The ARN of the RAM role with Hologres access. |
mcfed.mapreduce.jdbc.driver.class |
Yes | Set to org.postgresql.Driver. |
odps.federation.jdbc.target.db.type |
Yes | Set to holo. |
odps.federation.jdbc.colmapping |
No | Column mapping between MaxCompute fields and Hologres fields. Format: MaxCompute_field1:"Hologres_field1"[,...]. Required only when field names or order differ. Enclose Hologres field names in double quotation marks if they contain only uppercase letters. |
For the Hologres external_location parameters:
| Sub-parameter | Required | Description |
|---|---|---|
endpoint |
Yes | The endpoint of the Hologres instance in the classic network. See Instance configurations. |
port |
Yes | The port number of the Hologres instance. See Instance configurations. |
database |
Yes | The name of the Hologres database. See CREATE DATABASE. |
ApplicationName |
Yes | Keep the default value MaxCompute. |
currentSchema |
No | The schema containing the source table. Omit if the table name is unique in the database or the table is in the default schema. |
holo_table_name |
Yes | The name of the Hologres source table. See Overview. |
Example: Import a CSV file from OSS
This example imports vehicle.csv from OSS into a MaxCompute table. The MaxCompute and OSS resources belong to the same Alibaba Cloud account. Data is transferred over a virtual private cloud (VPC).
1. Authorize MaxCompute to access OSS.
Perform one-click authorization to create the AliyunODPSDefaultRole RAM role.
If MaxCompute and OSS belong to different accounts, grant OSS access via STS authorization instead.
2. Upload the source file to OSS.
Save vehicle.csv to the mc-test/data_location/ directory of an OSS bucket in the oss-cn-hangzhou region. See Create a bucket if you need to create one first.
The file contains:
1,1,51,1,46.81006,-92.08174,9/14/2014 0:00,S
1,2,13,1,46.81006,-92.08174,9/14/2014 0:00,NE
1,3,48,1,46.81006,-92.08174,9/14/2014 0:00,NE
1,4,30,1,46.81006,-92.08174,9/14/2014 0:00,W
1,5,47,1,46.81006,-92.08174,9/14/2014 0:00,S
1,6,9,1,46.81006,-92.08174,9/14/2014 0:00,S
1,7,53,1,46.81006,-92.08174,9/14/2014 0:00,N
1,8,63,1,46.81006,-92.08174,9/14/2014 0:00,SW
1,9,4,1,46.81006,-92.08174,9/14/2014 0:00,NE
1,10,31,1,46.81006,-92.08174,9/14/2014 0:00,NThe OSS path for the directory (using the internal endpoint for VPC access):
oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-test/data_location/3. Create the destination table.
Log on to the MaxCompute client and run:
CREATE TABLE ambulance_data_csv_load (
vehicleId INT,
recordId INT,
patientId INT,
calls INT,
locationLatitute DOUBLE,
locationLongtitue DOUBLE,
recordTime STRING,
direction STRING
);4. Import the data.
LOAD OVERWRITE TABLE ambulance_data_csv_load
FROM
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-test/data_location/'
STORED BY 'com.aliyun.odps.CsvStorageHandler'
WITH SERDEPROPERTIES (
'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole', -- ARN of AliyunODPSDefaultRole. Get it from the Roles page in the RAM console.
'odps.text.option.delimiter'=','
);To find the ARN of a RAM role, see View the information about a RAM role.
5. Verify the result.
-- Enable full table scan for this session only.
SET odps.sql.allow.fullscan=true;
SELECT * FROM ambulance_data_csv_load;Expected output:
+------------+----------+-----------+-------+------------------+-------------------+----------------+-----------+
| vehicleid | recordid | patientid | calls | locationlatitute | locationlongtitue | recordtime | direction |
+------------+----------+-----------+-------+------------------+-------------------+----------------+-----------+
| 1 | 1 | 51 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | S |
| 1 | 2 | 13 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | NE |
| 1 | 3 | 48 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | NE |
| 1 | 4 | 30 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | W |
| 1 | 5 | 47 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | S |
| 1 | 6 | 9 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | S |
| 1 | 7 | 53 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | N |
| 1 | 8 | 63 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | SW |
| 1 | 9 | 4 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | NE |
| 1 | 10 | 31 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | N |
+------------+----------+-----------+-------+------------------+-------------------+----------------+-----------+Example: Import data from a Hologres table
This example imports data from a Hologres table into a MaxCompute internal table.
Prerequisites: A Hologres instance and database exist, and a source table is populated. Authorize MaxCompute to access Hologres by creating a RAM role with the required permissions. See Create a Hologres external table in STS mode for setup details.
The Hologres source table contains:
-- Query result from the Hologres external table mf_holo_ext4:
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | ereg |
+----+------+1. Create the destination table.
CREATE TABLE mf_from_holo (id BIGINT, name STRING);2. Import the data.
LOAD INTO TABLE mf_from_holo
FROM LOCATION 'jdbc:postgresql://hgprecn-cn-wwo3ft0l****-cn-beijing-internal.hologres.aliyuncs.com:80/mf_db?application_name=MaxCompute¤tSchema=public&useSSL=false&table=mf_holo/'
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
WITH SERDEPROPERTIES (
'odps.properties.rolearn'='acs:ram::18927322887*****:role/hologressrole',
'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
'odps.federation.jdbc.target.db.type'='holo'
);3. Verify the result.
SELECT * FROM mf_from_holo;
-- Expected output:
-- +----+------+
-- | id | name |
-- +----+------+
-- | 2 | ereg |
-- | 1 | abc |
-- +----+------+Import data in an open source format
Use this method to import ORC, Parquet, TEXTFILE, RCFILE, SEQUENCEFILE, or other Hive-compatible formats from OSS.
Syntax
{LOAD OVERWRITE | LOAD INTO} TABLE <table_name> [PARTITION (<pt_spec>)]
FROM LOCATION <external_location>
[ROW FORMAT SERDE '<serde_class>'
[WITH SERDEPROPERTIES (<options>)]
]
STORED AS <file_format>;Parameters
| Parameter | Required | Description |
|---|---|---|
table_name |
Yes | The destination table. The schema (excluding partition key columns) must match the external data layout. |
pt_spec |
No | Partition specification in the format partition_col1=value1, partition_col2=value2, ... |
external_location |
Yes | The OSS directory containing the source data. Format: 'oss://<oss_endpoint>/<path>/'. MaxCompute reads all files in the directory. See OSS domain names. |
serde_class |
No | The SerDe class for parsing the data. Same usage as for MaxCompute external tables. See Create an OSS external table. Omit to use the default. |
options |
No | SerDe properties. Same as those used when creating a MaxCompute external table. See Create an OSS external table. |
file_format |
Yes | The data format: ORC, PARQUET, RCFILE, SEQUENCEFILE, or TEXTFILE. |
A single file cannot exceed 3 GB. Split files larger than 3 GB before importing.
Example 1: Import a TEXTFILE from OSS into a static partition
This example imports vehicle.textfile into a specific partition of a partitioned table.
1. Authorize MaxCompute to access OSS.
Perform one-click authorization to create the AliyunODPSDefaultRole RAM role.
2. Upload the source file to OSS.
Save vehicle.textfile to the mc-test/data_location/ directory of an OSS bucket in the oss-cn-hangzhou region. The OSS path:
oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-test/data_location/3. Create the destination table.
CREATE TABLE ambulance_data_textfile_load_pt (
vehicleId STRING,
recordId STRING,
patientId STRING,
calls STRING,
locationLatitute STRING,
locationLongtitue STRING,
recordTime STRING,
direction STRING
)
PARTITIONED BY (ds STRING);4. Import the data into partition `ds='20200910'`.
LOAD OVERWRITE TABLE ambulance_data_textfile_load_pt PARTITION(ds='20200910')
FROM
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-test/data_location/'
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS TEXTFILE;5. Verify the result.
-- Enable full table scan for this session only.
SET odps.sql.allow.fullscan=true;
SELECT * FROM ambulance_data_textfile_load_pt;Example 2: Import CSV files from OSS into a dynamic partition
If OSS subdirectories are named after partition values, MaxCompute can populate partitions automatically without specifying each one explicitly.
In this example, vehicle1.csv is in mc-test/data_location/ds=20200909/ and vehicle2.csv is in mc-test/data_location/ds=20200910/.
1. Authorize MaxCompute to access OSS.
Perform one-click authorization to create the AliyunODPSDefaultRole RAM role.
2. Upload the source files to OSS.
oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-test/data_location/ds=20200909/
oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-test/data_location/ds=20200910/3. Create the destination table.
CREATE TABLE ambulance_data_csv_load_dynpt (
vehicleId STRING,
recordId STRING,
patientId STRING,
calls STRING,
locationLatitute STRING,
locationLongtitue STRING,
recordTime STRING,
direction STRING
)
PARTITIONED BY (ds STRING);4. Import the data using dynamic partitioning.
The PARTITION(ds) clause without a value tells MaxCompute to derive partition values from the OSS subdirectory names.
LOAD OVERWRITE TABLE ambulance_data_csv_load_dynpt PARTITION(ds)
FROM
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-test/data_location/'
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
STORED AS TEXTFILE;5. Verify the result.
-- Enable full table scan for this session only.
SET odps.sql.allow.fullscan=true;
SELECT * FROM ambulance_data_csv_load_dynpt;Expected output (rows from ds=20200909 and ds=20200910):
+----------+----------+-----------+-------+------------------+-------------------+----------------+-----------+----------+
| vehicleid| recordid | patientid | calls | locationlatitute | locationlongtitue | recordtime | direction | ds |
+----------+----------+-----------+-------+------------------+-------------------+----------------+-----------+----------+
| 1 | 7 | 53 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | N | 20200909 |
| 1 | 8 | 63 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | SW | 20200909 |
| 1 | 9 | 4 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | NE | 20200909 |
| 1 | 10 | 31 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | N | 20200909 |
| 1 | 1 | 51 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | S | 20200910 |
| 1 | 2 | 13 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | NE | 20200910 |
| 1 | 3 | 48 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | NE | 20200910 |
| 1 | 4 | 30 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | W | 20200910 |
| 1 | 5 | 47 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | S | 20200910 |
| 1 | 6 | 9 | 1 | 46.81006 | -92.08174 | 9/14/2014 0:00 | S | 20200910 |
+----------+----------+-----------+-------+------------------+-------------------+----------------+-----------+----------+What's next
To export data from MaxCompute to OSS or Hologres, use UNLOAD statements. See UNLOAD.