Migrate compressed data from OSS to MaxCompute-MaxCompute(MaxCompute)-阿里云帮助中心

This topic describes how to migrate data from Snappy-compressed, comma-delimited files in OSS to MaxCompute by using an external table.

Prerequisites

MaxCompute is activated and a project is created. For more information, see Create a MaxCompute project.
OSS is activated and a bucket is created. The bucket must contain Snappy-compressed data files. For more information, see Create buckets.
The sample file for this topic is in an OSS bucket in the China (Beijing) region. The file path is mfosscostfee/demo7/.
This directory contains a sample file in the .snappy format. When you follow these steps, use your actual file information.

Procedure

Connect to MaxCompute by using a development tool.

You can create OSS external tables by using various tools.

Creation method	Tool
Create an OSS external table by using MaxCompute SQL	MaxCompute client
	DataWorks
	ODPS SQL node in the DataWorks console
	SQL script in MaxCompute Studio
Create an OSS external table by using a GUI	SQL script in MaxCompute Studio
Create an OSS external table by using a GUI	External table in the DataWorks console

Create an OSS external table.

For more information, see Create an OSS external table.

-- Create an external table.
CREATE EXTERNAL TABLE IF NOT EXISTS mc_oss_ext_snap_split
(
    str1  STRING,
    str2  STRING,
    str3  STRING,
    str4  STRING,
    str5  STRING,
    str6  STRING,
    str7  STRING,
    str8  STRING,
    str9  STRING,
    str10 STRING,
    str11 STRING,
    str12 STRING,
    str13 STRING,
    str14 STRING,
    str15 STRING,
    str16 STRING,
    str17 STRING,
    str18 STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES ('field.delim'=',') -- The content in the Snappy file is delimited by commas (,).
STORED AS TEXTFILE
LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/mfosscostfee/demo7/'
;

Load data from the external table into a MaxCompute internal table.

Run the following commands:

-- Create an internal table.
CREATE TABLE IF NOT EXISTS mc_oss_snap_split
(
    str1  STRING,
    str2  STRING,
    str3  STRING,
    str4  STRING,
    str5  STRING,
    str6  STRING,
    str7  STRING,
    str8  STRING,
    str9  STRING,
    str10 STRING,
    str11 STRING,
    str12 STRING,
    str13 STRING,
    str14 STRING,
    str15 STRING,
    str16 STRING,
    str17 STRING,
    str18 STRING
);
-- Load data from the external table into the internal table.
INSERT INTO TABLE mc_oss_snap_split SELECT * FROM mc_oss_ext_snap_split LIMIT 10;

Verify the data migration.

Run the following command to query the data in the internal table:

SELECT str2, str3 FROM mc_oss_snap_split;

Sample output:

+------------+------------+
| str2       | str3       |
+------------+------------+
| 113.221620 | 23.398279  |
| 113.288735 | 23.157167  |
| 113.040365 | 23.681102  |
| 113.910224 | 22.757139  |
| 119.086087 | 33.583632  |
| 113.363475 | 23.141354  |
| 113.328440 | 23.130362  |
| 113.249651 | 23.205976  |
| 113.258325 | 23.159060  |
| 117.463688 | 38.836611  |
+------------+------------+

The output confirms that the data is migrated from OSS to MaxCompute.