MaxCompute:Map OSS external table data by column name

Read and write data by column name

1. Upload Optimized Row Columnar (ORC) data to a specified OSS directory.

   In this example, a directory named demo7 is created in OSS, and two data files are uploaded.

   Note

   The specific procedure depends on the status of your OSS and files.

   • orc_file_with_schema0:

     -- Field information
     `#id` bigint,`#name` string
     -- 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"}

2. Log on to a development tool and connect to MaxCompute.

   You can use one of the development tools described in the following table to create an OSS external table.

   Creation method	Platform
   Create an OSS external table using MaxCompute SQL	MaxCompute client
   	Connect using DataWorks
   	DataWorks console - ODPS SQL node
   	MaxCompute Studio - SQL program
   Create an OSS external table using the UI	MaxCompute Studio - SQL program
   	DataWorks console - External Table

3. Create an OSS external table in MaxCompute.

   Run the following command 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/';

4. Query the data.

   SET odps.ext.oss.orc.native=true;
   
   -- Query the data.
   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         |
   +------------+------------+------------+

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

   INSERT into TABLE mf_oss_diff_schm values("e","5",30);
   
   -- Query the 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         |
   +------------+------------+------------+