Use the LOAD command to import data into MaxCompute from external storage, or data in open-source formats. - MaxCompute

Use the LOAD statement to load data from external data stores (e.g., OSS, Hologres, Amazon Redshift, or BigQuery) into a MaxCompute table or partition.

Overview

MaxCompute uses the LOAD OVERWRITE or LOAD INTO command to import data in CSV or other open-source formats from external storage, such as What is Hologres?, What is OSS?, Amazon Redshift, and BigQuery, into a MaxCompute table or partition.

You must first import data from Amazon Redshift and BigQuery into OSS before you can load it into MaxCompute.
MaxCompute uses dynamic partitioning to load data into partitioned tables.
The LOAD INTO command appends data to a table or partition. The LOAD OVERWRITE command first clears the table or partition and then inserts the new data.

Restrictions

Permission requirements

MaxCompute permissions
- CreateTable and Alter permissions: Required to create tables or modify table data in a MaxCompute project. For instructions, see MaxCompute permissions.
External storage permissions
- OSS permissions: You must grant MaxCompute permissions to access OSS (read and list objects). For enhanced security, use STS mode for authorization. For more information, see Authorize access in STS mode.
- Hologres permissions: To authorize MaxCompute to access Hologres, create a RAM role, grant it the required permissions, and add the role to the Hologres instance. For details, see Create a Hologres external table (STS mode).

Other restrictions

The LOAD command does not support allowlist/denylist parameters.
The external storage and the target MaxCompute project must be in the same region.
When you load data from OSS:
The schema of the target partitioned table (excluding partition columns) must match the schema of the external data. The external data's schema must not include the partition columns.
When you load data from Hologres:
- You cannot load data from a Hologres partitioned table into MaxCompute.
- MaxCompute cannot load data from a Hologres external table that uses the dual signature authorization mode.

Load data

OSS and Hologres

Load data from OSS or Hologres

Command syntax

{LOAD OVERWRITE|INTO} TABLE <table_name> [PARTITION (<pt_spec>)]
FROM LOCATION <external_location>
STORED BY <StorageHandler>
[WITH SERDEPROPERTIES (<Options>)];

Parameters

OSS

Click to view parameter details

table_name: Required.
The name of the target table. You must create this table before loading data. Its schema, excluding partition columns, must match the external data format.
pt_spec: Optional.
The partition of the target table to load data into. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...).
external_location: Required.
The OSS directory to read data from. The format is 'oss://<oss_endpoint>/<object>'. For more information, see Access OSS by using endpoints and bucket domain names. By default, MaxCompute reads all files in this directory.
StorageHandler: Required.
Specifies the name of the built-in storage handler. Use com.aliyun.odps.CsvStorageHandler for CSV files. It defines how to read and write CSV data. You only need to provide its name, as the system handles the implementation logic. The usage is the same as for MaxCompute external tables. For more information, see Create an OSS external table.
Options: Optional.
Specifies parameters for the external table. SERDEPROPERTIES supports the same properties as those used for MaxCompute external tables. For a list of supported properties, see Create an OSS external table.

Hologres

Click to view parameter details

table_name: Required.
The name of the target table. You must create this table before loading data. Its schema, excluding partition columns, must match the external data format.
pt_spec: Optional.
The partition of the target table to load data into. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...).
external_location: Required.
The JDBC connection URL for Hologres. The format is '<jdbc:postgresql://<endpoint>:<port>/<database>?ApplicationName=MaxCompute&[currentSchema=<schema>&][useSSL={true|false}&]table=<holo_table_name>/>'.
- endpoint: Required.
  The classic network domain name of the Hologres instance. To learn how to obtain it, see Instance Details.
  Important
  MaxCompute can connect to Hologres only through a classic network domain name. Connections that use a VPC network domain name are not supported.
- port: Required.
  The network port of the Hologres instance. To learn how to obtain it, see Instance Details.
- database: Required.
  The name of the Hologres database to connect to. For more information about Hologres databases, see CREATE DATABASE.
- ApplicationName: Required.
  The value is MaxCompute by default and does not require modification.
- schema: Optional.
  You can omit this parameter if the table name is unique within the Hologres database or if the source table is in the default schema. For more information about schemas, see CREATE SCHEMA.
- holo_table_name: Required.
  The name of the Hologres source table. For more information about Hologres source tables, see CREATE TABLE.
StorageHandler: Required.
Defines how to query the Hologres external table. This value is fixed at com.aliyun.odps.jdbc.JdbcStorageHandler.
Options: Required.
Specifies parameters related to the external table. The properties supported by SERDEPROPERTIES are the same as those for MaxCompute external tables.
- mcfed.mapreduce.jdbc.driver.class: Required.
  Specifies the driver for connecting to the Hologres database. The value is fixed at org.postgresql.Driver.
- odps.federation.jdbc.target.db.type: Required.
  Specifies the type of database to connect to. The value is fixed at holo.
- odps.federation.jdbc.colmapping: Optional.
  Specifies the column mapping between the MaxCompute external table and the Hologres source table. Use this parameter if you need to map only a subset of columns or if column names differ.
  - If you do not configure this parameter, columns are mapped by name.
  - If this parameter is configured but specifies the mapping for only a subset of columns, the remaining columns are mapped by name. An error is reported if any unmapped columns have mismatched names or data types.
  - If this parameter is configured and a column name in Hologres contains uppercase letters, you must enclose the column name in double quotation marks (""). The format is: MaxCompute_column1:"Hologres_column1"[,MaxCompute_column2:"Hologres_column2",...].
    Note
    The Hologres source table has the columns c bool, map_B string, a bigint. The Hologres external table has the columns a bigint, x string, c bool.
    If you set this parameter to 'x:"map_B"', the data can be successfully mapped and queried.
- mcfed.mapreduce.jdbc.input.query: Optional. Specifies a custom SQL query to read data from the Hologres source table. The schema of the query's result set (including column names and data types) must match the schema of the MaxCompute external table. If you use aliases in the query, they must match the column names in the external table. The format of select_sentence is SELECT xxx FROM <holo_database_name>.<holo_schema_name>.<holo_table_name>.

Examples

OSS

This example shows how to load data by using a built-in extractor (StorageHandler). It assumes that MaxCompute and OSS are owned by the same Alibaba Cloud account and demonstrates how to load data from the vehicle.csv file into MaxCompute over the Alibaba Cloud internal network.

Click here for one-click authorization.
Save the vehicle.csv file to the mc-test/data_location/ directory in an OSS bucket in the oss-cn-hangzhou region, and then construct the OSS directory path. For more information about how to create an OSS bucket, see Create a bucket.
The OSS directory path is constructed from the bucket, region, and endpoint information as follows:
```
oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-test/data_location/
```

Log on to the MaxCompute client (odpscmd) to create the target table ambulance_data_csv_load. Sample command:

CREATE TABLE ambulance_data_csv_load (
vehicleId INT,
recordId INT,
patientId INT,
calls INT,
locationLatitude DOUBLE,
locationLongitude DOUBLE,
recordTime STRING,
direction STRING );

Run the load overwrite command to load the vehicle.csv file from OSS into the target table. Sample command:

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 (
    -- The ARN of AliyunODPSDefaultRole. You can obtain it on the RAM role management page.
    'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole',
    'odps.text.option.delimiter'=','
);

To view the ARN for a role, see View a RAM role.

Verify the data in the target table ambulance_data_csv_load. Sample command:

-- Enable a full table scan. This setting is valid only for the current session.
set odps.sql.allow.fullscan=true;
SELECT * FROM ambulance_data_csv_load;

-- The following result is returned:
+------------+------------+------------+------------+------------------+-------------------+------------+------------+
| vehicleid  | recordid   | patientid  | calls      | locationlatitude | locationlongitude | 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          |
+------------+------------+------------+------------+------------------+-------------------+------------+------------+

Hologres

Scenario
A Hologres instance and database have been created, and a table has been created in the database.
A Hologres external table has been created in MaxCompute. You can query the data in the Hologres table by using the external table.
```
-- Query the Hologres external table:
SELECT * FROM holo_ext;

-- The following result is returned:
+------------+------+
| id         | name |
+------------+------+
| 1          | abc  |
| 2          | ereg |
+------------+------+
```

The following example shows how to use the LOAD command to load data from this Hologres table into a MaxCompute internal table.

Create a MaxCompute internal table.

-- Create an internal table.
CREATE TABLE from_holo(id BIGINT, name STRING);

Use the LOAD command to load data into MaxCompute.

-- Load data from the Hologres table into a MaxCompute internal table.
LOAD INTO TABLE from_holo 
FROM LOCATION 'jdbc:postgresql://hgprecn-cn-wwo3ft0l****-cn-beijing-internal.hologres.aliyuncs.com:80/<YOUR_HOLO_DB_NAME>?application_name=MaxCompute&currentSchema=public&useSSL=false&table=<YOUR_HOLOGRES_TABLE_NAME>/' 
STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler' 
WITH SERDEPROPERTIES (
  'odps.properties.rolearn'='acs:ram::18927322887*****:role/hologresrole',
  'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 
  'odps.federation.jdbc.target.db.type'='holo'
);

Query the table to see the result.

SELECT * FROM from_holo;

-- The following result is returned:
+------------+------+
| id         | name |
+------------+------+
| 2          | ereg |
| 1          | abc  |
+------------+------+

Other open-source formats

Load data in other open-source formats

The size of a single file to be loaded cannot exceed 3 GB. If a file is too large, split it before loading.

Command syntax

{LOAD OVERWRITE|INTO} TABLE <table_name> [PARTITION (<pt_spec>)]
FROM LOCATION <external_location>
[ROW FORMAT SERDE '<serde_class>'
  [WITH SERDEPROPERTIES (<Options>)]
]
STORED AS <file_format>;

Parameters

Click to view parameter details

table_name: Required.
The name of the target table. You must create this table before loading data. Its schema, excluding partition columns, must match the external data format.
pt_spec: Optional.
The partition of the target table to load data into. The format is (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...).
external_location: Required.
The OSS directory to read data from. The format is 'oss://<oss_endpoint>/<object>'. For more information, see Access OSS by using endpoints and bucket domain names. By default, MaxCompute reads all files in this directory.
serde_class: Optional.
You can omit this parameter if you use the default value. The usage is the same as for MaxCompute external tables. For more information, see Create an OSS external table.
Options: Optional.
You can omit this parameter if you use the default value. Specifies parameters for the external table. SERDEPROPERTIES supports the same properties as those used for MaxCompute external tables. For a list of supported properties, see Create an OSS external table.
file_format: Required.
The format of the data file to be loaded, such as ORC, PARQUET, RCFILE, SEQUENCEFILE, or TEXTFILE. The usage is the same as for MaxCompute external tables. For more information, see Create an OSS external table.

Examples

Same account

Same account ownership

This example shows how to load data from the vehicle.textfile file into MaxCompute over the Alibaba Cloud internal network.

Note

If MaxCompute and OSS are owned by different Alibaba Cloud accounts, see Authorization in STS mode.

Click here for one-click authorization.
Save the vehicle.textfile file to the mc-test/data_location/ directory in an OSS bucket in the oss-cn-hangzhou region, and then construct the OSS directory path. For more information about how to create an OSS bucket, see Create a bucket.
The OSS directory path is constructed from the bucket, region, and endpoint information as follows:
```
oss://oss-cn-hangzhou-internal.aliyuncs.com/mc-test/data_location/
```

Log on to the MaxCompute client (odpscmd) to create the target table ambulance_data_textfile_load_pt. Sample command:

CREATE TABLE ambulance_data_textfile_load_pt (
  vehicleId STRING,
  recordId STRING,
  patientId STRING,
  calls STRING,
  locationLatitude STRING,
  locationLongitude STRING,
  recordTime STRING,
  direction STRING
)
PARTITIONED BY (
  ds STRING
);

Run the load overwrite command to load the vehicle.textfile file from OSS into the target table. Sample command:

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'
WITH SERDEPROPERTIES ('field.delim' = ',')
STORED AS TEXTFILE;

Verify the data in the target table ambulance_data_textfile_load_pt. Sample command:

-- Enable a full table scan. This setting is valid only for the current session.
SET odps.sql.allow.fullscan=true;
SELECT * FROM ambulance_data_textfile_load_pt;

-- The following result is returned:
+-----------+----------+-----------+-------+------------------+-------------------+----------------+-----------+----------+
| vehicleid | recordid | patientid | calls | locationlatitude | locationlongitude | recordtime     | direction | ds       |
+-----------+----------+-----------+-------+------------------+-------------------+----------------+-----------+----------+
| 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 |
| 1         | 7        | 53        | 1     | 46.81006         | -92.08174         | 9/14/2014 0:00 | N         | 20200910 |
| 1         | 8        | 63        | 1     | 46.81006         | -92.08174         | 9/14/2014 0:00 | SW        | 20200910 |
| 1         | 9        | 4         | 1     | 46.81006         | -92.08174         | 9/14/2014 0:00 | NE        | 20200910 |
| 1         | 10       | 31        | 1     | 46.81006         | -92.08174         | 9/14/2014 0:00 | N         | 20200910 |
+-----------+----------+-----------+-------+------------------+-------------------+----------------+-----------+----------+

Dynamic partitions

Load data by using dynamic partitions

Note

You can use dynamic partitions to load data when your OSS subdirectories are named according to partition key-value pairs, such as ds=20200909/.

Click here for one-click authorization.
Save the vehicle1.csv and vehicle2.csv files to the mc-test/data_location/ds=20200909/ and mc-test/data_location/ds=20200910/ directories in an OSS bucket in the oss-cn-hangzhou region, respectively, and then construct the OSS directory paths. For more information about how to create an OSS bucket, see Create a bucket.
The OSS directory paths are constructed from the bucket, region, and endpoint information as follows:
```
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/'
```

Log on to the MaxCompute client (odpscmd) to create the target table ambulance_data_csv_load_dynpt. Sample command:

CREATE TABLE ambulance_data_csv_load_dynpt (
  vehicleId STRING,
  recordId STRING,
  patientId STRING,
  calls STRING,
  locationLatitude STRING,
  locationLongitude STRING,
  recordTime STRING,
  direction STRING
)
PARTITIONED BY (
  ds STRING
);

Run the load overwrite command to load the files from OSS into the target table. Sample command:

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;

Verify the data in the target table ambulance_data_csv_load_dynpt. Sample command:

-- Enable a full table scan. This setting is valid only for the current session.
SET odps.sql.allow.fullscan=true;
SELECT * FROM ambulance_data_csv_load_dynpt;

-- The following result is returned:
+------------+------------+------------+------------+------------------+-------------------+----------------+------------+------------+
| vehicleid  | recordid   | patientid  | calls      | locationlatitude | locationlongitude | 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   |
+------------+------------+------------+------------+------------------+-------------------+----------------+------------+------------+

Billing

The LOAD command loads external data into the data warehouse. While you are not charged for the volume of imported data under the pay-as-you-go model or for individual tasks under the subscription model, all LOAD tasks consume compute resources from their resource group.

References

To export data from a MaxCompute project to external storage, such as OSS or Hologres, for other compute engines to use, see UNLOAD.