Use OSS external tables to perform data mapping based on field names - MaxCompute

Object Storage Service (OSS) allows you to upload the data of tables that use different schemas to an OSS directory. You can create OSS external tables in MaxCompute to read data from and write data to OSS based on field names. This topic describes how to use an OSS external table to read data from and write data to OSS based on field names.

Prerequisites

MaxCompute is activated. A MaxCompute project is created. For more information about how to create a MaxCompute project, see Create a MaxCompute project.
OSS is activated and a bucket is created. For more information about how to create a bucket, see Create buckets.

Perform data read and write operations based on field names

Upload data in the Optimized Row Columnar (ORC) format to a specified OSS directory.
In this example, a directory named demo7 is created in OSS and two data files are uploaded to the directory.
Note
The OSS directory and data files in this example are only for reference. The actual directory and files may vary.
- orc_file_with_schema0
```
-- Field information
`#id` bigint,`#name` stirng
-- Data
{`#id`:1,`#name`:"a"}
{`#id`:2,`#name`:null}
```
- orc_file_with_schema1
```
-- Field information
age bigint,`#name` string,`#id` string
-- Data
{age:10,`#name`:"c",`#id`:"3"}
{age:20,`#name`:"d",`#id`:"4"}
```

Log on to a tool for MaxCompute connection and development.

The following table describes the tools that you can use to create an OSS external table.

Creation method	Tool
Execute MaxCompute SQL statements	MaxCompute client
	DataWorks
	ODPS SQL nodes in the DataWorks console
	SQL scripts in MaxCompute Studio
Perform operations on the web UI	SQL scripts in MaxCompute Studio
Perform operations on the web UI	DataWorks console

Create an OSS external table in MaxCompute.

Execute the following sample statement to create an OSS external table. For more information about the syntax of the CREATE EXTERNAL TABLE statement, see Create an OSS external table.

CREATE EXTERNAL TABLE mf_oss_diff_schm
(
    `#name` string, `#id` string, age bigint
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
WITH SERDEPROPERTIES (
    'mcfed.orc.schema.resolution'='name'
)
STORED AS ORC
LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/mfoss*******/demo7/';

Query data from the OSS external table.

set odps.ext.oss.orc.native=true;
select * from mf_oss_diff_schm;

The following result is returned:

+------------+------------+------------+
| #name      | #id        | age        |
+------------+------------+------------+
| a          | 1          | NULL       |
| NULL       | 2          | NULL       |
| c          | 3          | 10         |
| d          | 4          | 20         |
+------------+------------+------------+

Write data to the OSS external table and query data from the OSS external table.

insert into table mf_oss_diff_schm values("e","5",30);

-- Query data.
select * from mf_oss_diff_schm;
-- The following result is returned:
+------------+------------+------------+
| #name      | #id        | age        |
+------------+------------+------------+
| a          | 1          | NULL       |
| NULL       | 2          | NULL       |
| e          | 5          | 30         |
| c          | 3          | 10         |
| d          | 4          | 20         |
+------------+------------+------------+