MaxCompute:LOAD

Syntax

CSV and TSV files:

{LOAD OVERWRITE|INTO} TABLE <table_name> [PARTITION (<pt_spec>)]
FROM LOCATION <oss_location>
STORED BY <StorageHandler>
[WITH SERDEPROPERTIES ('<property_name>'='<property_value>', ...)];

Open-source formats (ORC, Parquet, TextFile, etc.):

{LOAD OVERWRITE|INTO} TABLE <table_name> [PARTITION (<pt_spec>)]
FROM LOCATION <oss_location>
[ROW FORMAT SERDE '<serde_class>'
  [WITH SERDEPROPERTIES ('<property_name>'='<property_value>', ...)]
]
STORED AS <file_format>;

Parameters

Common SERDEPROPERTIES for OSS:

Load a CSV file from OSS

This example loads vehicle.csv from OSS into the MaxCompute table ambulance_data_csv_load. MaxCompute and OSS are owned by the same Alibaba Cloud account, and data transfers over the Alibaba Cloud internal network.

1. Authorize MaxCompute to access OSS.

Click here for one-click authorization.

2. Upload the source file to OSS.

Save vehicle.csv to the mc-test/data_location/ directory in an OSS bucket in the oss-cn-hangzhou region. For details on creating a bucket, see Create a bucket.

The resulting OSS directory path is:

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

3. Create the target table and load data.

Log on to the MaxCompute client (odpscmd) and run:

-- Create the target table.
CREATE TABLE ambulance_data_csv_load (
    vehicleId         INT,
    recordId          INT,
    patientId         INT,
    calls             INT,
    locationLatitude  DOUBLE,
    locationLongitude DOUBLE,
    recordTime        STRING,
    direction         STRING
);

-- Load the CSV file into the table.
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 (
    -- ARN of AliyunODPSDefaultRole. Obtain it from the RAM role management page.
    'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole',
    'odps.text.option.delimiter'=','
);

4. Verify the result.

-- Enable a full table scan (effective for the current session only).
SET odps.sql.allow.fullscan=true;
SELECT * FROM ambulance_data_csv_load;

-- Expected output:
+------------+------------+------------+------------+------------------+-------------------+----------------+------------+
| 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          |
+------------+------------+------------+------------+------------------+-------------------+----------------+------------+

Load a TextFile from OSS into a partitioned table

This example loads vehicle.textfile from OSS into a specific partition of ambulance_data_textfile_load_pt.

1. Authorize MaxCompute to access OSS.

Click here for one-click authorization.

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

2. Upload the source file to OSS.

Save vehicle.textfile to the mc-test/data_location/ directory in an OSS bucket in the oss-cn-hangzhou region.

The resulting OSS directory path is:

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

3. Create the target table and load data.

-- Create the target partitioned table.
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);

-- Load data into the ds='20200910' partition.
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;

4. Verify the result.

-- Enable a full table scan (effective for the current session only).
SET odps.sql.allow.fullscan=true;
SELECT * FROM ambulance_data_textfile_load_pt;

-- Expected output:
+-----------+----------+-----------+-------+------------------+-------------------+----------------+-----------+----------+
| 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 |
+-----------+----------+-----------+-------+------------------+-------------------+----------------+-----------+----------+

Load data with dynamic partitions

Use dynamic partitions when OSS subdirectories are named with partition key-value pairs (for example, ds=20200909/). MaxCompute infers partition values from the directory names, so you specify only the partition key in the PARTITION clause.

1. Authorize MaxCompute to access OSS.

Click here for one-click authorization.

2. Upload the source files to OSS.

Save vehicle1.csv to mc-test/data_location/ds=20200909/ and vehicle2.csv to mc-test/data_location/ds=20200910/ in an OSS bucket in the oss-cn-hangzhou region.

The resulting OSS directory paths are:

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 target table and load data.

-- Create the target partitioned table.
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);

-- Load data using dynamic partitions.
-- MaxCompute reads the partition value from each subdirectory name.
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;

4. Verify the result.

-- Enable a full table scan (effective for the current session only).
SET odps.sql.allow.fullscan=true;
SELECT * FROM ambulance_data_csv_load_dynpt;

-- Expected output:
+------------+------------+------------+------------+------------------+-------------------+----------------+------------+------------+
| 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   |
+------------+------------+------------+------------+------------------+-------------------+----------------+------------+------------+

Syntax

{LOAD OVERWRITE|INTO} TABLE <table_name> [PARTITION (<pt_spec>)]
FROM LOCATION <hologres_location>
STORED BY <StorageHandler>
[WITH SERDEPROPERTIES ('<property_name>'='<property_value>', ...)];

Parameters

Hologres location URL sub-parameters:

Required SERDEPROPERTIES for Hologres:

Column mapping behavior:

• If odps.federation.jdbc.colmapping is not set, columns are mapped by name.

• If it is set for only a subset of columns, the remaining columns are mapped by name. An error occurs if any unmapped columns have mismatched names or types.

Load data from a Hologres table

This example loads data from the Hologres table holo_ext into the MaxCompute internal table from_holo.

Source data in Hologres:

-- Query the Hologres external table:
SELECT * FROM holo_ext;

-- Result:
+------------+------+
| id         | name |
+------------+------+
| 1          | abc  |
| 2          | ereg |
+------------+------+

1. Create the MaxCompute target table.

CREATE TABLE from_holo (id BIGINT, name STRING);

2. Load data from Hologres.

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'
);

3. Verify the result.

SELECT * FROM from_holo;

-- Expected output:
+------------+------+
| id         | name |
+------------+------+
| 2          | ereg |
| 1          | abc  |
+------------+------+