MaxCompute:Create an OSS external table

Prerequisites

Precautions

Limits

OSS external tables do not support the clustering attribute.

Platforms

Syntax used to create an OSS external table

The following table describes the syntax that is used to create an OSS external table in each scenario. The following table also provides references of the examples in each scenario. For more information about syntax parameters and properties, see Reference: Parameters in the syntax, Reference: WITH SERDEPROPERTIES properties, and Reference: TBLPROPERTIES properties.

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
<col_name> <data_type>,
...
)
[partitioned BY (<col_name> <data_type>, ...)] 
stored BY '<StorageHandler>'  
WITH serdeproperties (
 ['<property_name>'='<property_value>',...]
) 
location '<oss_location>';

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[partitioned BY (<col_name> <data_type>, ...)]
[row format serde '<serde_class>'
  [WITH serdeproperties (
    ['<property_name>'='<property_value>',...])
  ]
]
stored AS <file_format> 
location '<oss_location>' 
[USING '<resource_name>']
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
<col_name> <date_type>,
...
)
[partitioned BY (<col_name> <data_type>, ...)] 
stored BY '<StorageHandler>' 
WITH serdeproperties (
 ['<property_name>'='<property_value>',...]
) 
location '<oss_location>' 
USING '<jar_name>';

Syntax used to add partition data to OSS external tables

Example: Prepare data

Example: Create a non-partitioned table as an OSS external table by using a built-in text extractor

Create a mapping between the non-partitioned external table and the Demo1/ directory in Example: Prepare data. Sample statement used to create an OSS external table:

create external table if not exists mc_oss_csv_external1
(
vehicleId int,
recordId int,
patientId int,
calls int,
locationLatitute double,
locationLongtitue double,
recordTime string,
direction string
)
stored by 'com.aliyun.odps.CsvStorageHandler' 
with serdeproperties (
 'odps.properties.rolearn'='acs:ram::xxxxxx:role/aliyunodpsdefaultrole'
) 
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo1/';

You can execute the DESC EXTENDED mc_oss_csv_external1; statement to view the schema of the OSS external table that you created.

Example: Create a partitioned table as an OSS external table by using a built-in text extractor

Create a mapping between the partitioned external table and the Demo2/ directory in Example: Prepare data. Sample statements used to create an OSS external table and add partition data to the OSS external table:

create external table if not exists mc_oss_csv_external2
(
vehicleId int,
recordId int,
patientId int,
calls int,
locationLatitute double,
locationLongtitue double,
recordTime string
)
partitioned by (
direction string
)
stored by 'com.aliyun.odps.CsvStorageHandler' 
with serdeproperties (
 'odps.properties.rolearn'='acs:ram::xxxxxx:role/aliyunodpsdefaultrole'
) 
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo2/';
-- Add partition data. 
msck repair table mc_oss_csv_external2 add partitions;
-- The preceding statement is equivalent to the following statement: 
alter table mc_oss_csv_external2 add partition (direction = 'N') partition (direction = 'NE') partition (direction = 'S') partition (direction = 'SW') partition (direction = 'W');

You can execute the DESC EXTENDED mc_oss_csv_external2; statement to view the schema of the OSS external table that you created.

Example: Create an OSS external table that is used to read or write compressed data by using a built-in text extractor

Create a mapping between the OSS external table and the Demo3/ directory in Example: Prepare data. Sample statement used to create an OSS external table:

create external table if not exists mc_oss_csv_external3
(
vehicleId int,
recordId int,
patientId int,
calls int,
locationLatitute double,
locationLongtitue double,
recordTime string,
direction string
)
stored by 'com.aliyun.odps.CsvStorageHandler' 
with serdeproperties (
 'odps.properties.rolearn'='acs:ram::xxxxxx:role/aliyunodpsdefaultrole',
 'odps.text.option.gzip.input.enabled'='true',
 'odps.text.option.gzip.output.enabled'='true' 
) 
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo3/';

You can execute the DESC EXTENDED mc_oss_csv_external3; statement to view the schema of the OSS external table that you created.

Example: Create an OSS external table by using a built-in open source data extractor

• TEXTFILE data files are mapped to the OSS external table.

  • Example of creating an OSS external table that is associated with OSS data in the TEXT format

    CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
    (
    <col_name> <data_type>,
    ...
    )
    [partitioned BY (<col_name> <data_type>, ...)]
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    stored AS textfile
    location '<oss_location>';
    SELECT ... FROM <mc_oss_extable_name> ...;

    CREATE EXTERNAL TABLE statements do not support custom row format settings. Default row format settings:

    FIELDS TERMINATED BY :'\001'
    ESCAPED BY: '\'
    COLLECTION ITEMS TERMINATED BY: '\002'
    MAP KEYS TERMINATED BY: '\003'
    LINES TERMINATED BY: '\n'
    NULL DEFINED AS :'\N'

  • Example of creating an OSS external table that is associated with OSS data in the CSV format

    -- Disable the native text reader. 
    SET odps.ext.hive.lazy.simple.serde.native=false;
    -- Create an OSS external table. 
    CREATE EXTERNAL TABLE <mc_oss_extable_name>
    (
    <col_name> <data_type>,
    ...
    )
    row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
    WITH serdeproperties (
    "separatorChar" = ",",
    "quoteChar"= '"',
    "escapeChar"= "\\"
    )
    stored AS textfile
    location '<oss_location>'
    tblproperties (
    "skip.header.line.count"="1",
    "skip.footer.line.count"="1"
    );
    SELECT ... FROM <mc_oss_extable_name> ...;

    Note

    OpenCSVSerde supports only data of the STRING type.

  • Example of creating an OSS external table that maps to JSON data files

    CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
    (
    <col_name> <data_type>,
    ...
    )
    [partitioned BY (<col_name> <data_type>, ...)]
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    stored AS textfile
    location '<oss_location>';
    SELECT ... FROM <mc_oss_extable_name> ...;

• PARQUET data files are mapped to the OSS external table.

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  stored AS parquet  
  location '<oss_location>';
  SELECT ... FROM <mc_oss_extable_name> ...;

• ORC data files are mapped to the OSS external table.

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  stored AS orc
  location '<oss_location>';
  SELECT ... FROM <mc_oss_extable_name> ...;

  Note

  For more information about the examples, see Use OSS external tables to perform data mapping based on field names.

• RCFILE data files are mapped to the OSS external table.

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_rcfile_extable>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  row format serde 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
  stored AS rcfile
  location '<oss_location>';
  SELECT ... FROM <mc_oss_rcfile_extable> ...;

• AVRO data files are mapped to the OSS external table.

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  stored AS avro
  location '<oss_location>';
  SELECT ... FROM <mc_oss_extable_name> ...;

• SEQUENCEFILE data files are mapped to the OSS external table.

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  stored AS sequencefile
  location '<oss_location>';
  SELECT ... FROM <mc_oss_extable_name> ...;

• Hudi data files are mapped to the OSS external table.

  Note

  • Hudi external tables only support reading data from all files mapped by the external table. They do not support automatically hiding system columns, incremental reads, snapshot reads, or write operations. We recommend that you use features such as MaxCompute Delta tables or Paimon external tables to achieve ACID-compliant read and write operations.

  • The default version of the Hudi SDK integrated into MaxCompute is org.apache.hudi:hudi-hadoop-mr-bundle:0.12.2-emr-1.0.6, which does not guarantee forward or backward compatibility of the Hudi SDK. The compatibility is ensured by the open-source community.

  • MaxCompute provides a method for users to upload jars and specify the SDK compatibility version when creating external tables, to read data compatible with specific versions.

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  stored AS 
  INPUTFORMAT 'org.apache.hudi.hadoop.HoodieParquetInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
  location '<oss_location>';
  SELECT ... FROM <mc_oss_extable_name> ...;

• Delta Lake data files are mapped to the OSS external table.

  Note

  • Delta Lake external tables only support reading data from all files mapped by the external table. They do not support automatically hiding system columns, incremental reads, snapshot reads, or write operations. We recommend that you use features such as MaxCompute Delta tables or Paimon external tables to achieve ACID-compliant read and write operations.

  • The default version of the Delta Lake SDK integrated into MaxCompute is io.delta:delta-core_2.11:0.2.0.5, which does not guarantee forward or backward compatibility of the Delta Lake SDK. The compatibility is ensured by the open-source community.

  • MaxCompute provides a method for users to upload jars and specify the SDK compatibility version when creating external tables, to read data compatible with specific versions.

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> (
     <col_name> <data_type>,
     ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  STORED AS
  INPUTFORMAT 'io.delta.hive.DeltaInputFormat'
  OUTPUTFORMAT 'io.delta.hive.DeltaOutputFormat'
  LOCATION '<oss_location>';
  SELECT ... FROM <mc_oss_extable_name> ...;

• Apache Paimon data files are mapped to the OSS external table.

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  stored BY 'org.apache.paimon.hive.PaimonStorageHandler'
  WITH serdeproperties (
      'odps.properties.rolearn'='acs:ram::xxxxxxxxxxxxx:role/aliyunodpsdefaultrole'
  )
  location '<oss_location>'
  USING 'paimon_maxcompute_connector.jar';

  You must upload the JAR file paimon_maxcompute_connector.jar to the MaxCompute project in advance. For more information, see Apache Paimon external tables.

Example: Create an OSS external table by using a custom extractor

Create a mapping between the OSS external table and the SampleData/ directory in Example: Prepare data.

Perform the following steps:

1. Use Java programs TextExtractor.java, TextOutputer.java, SplitReader.java, and TextStorageHandler.java that are developed by using MaxCompute Studio.

   For more information about how to develop a Java program, see Develop a UDF.

2. MaxCompute Studio allows you to package the TextStorageHandler.java program into a JAR file and upload the file to your MaxCompute project as a resource.

   In this example, the JAR file is named javatest-1.0-SNAPSHOT.jar. For more information about how to package resources into files and upload the files to your MaxCompute project, see Package a Java program, upload the package, and create a MaxCompute UDF.

   Note

   If multiple dependencies are required, separately package them and upload them as MaxCompute resources.

3. Execute the following statement to create an OSS external table:

   CREATE EXTERNAL TABLE [IF NOT EXISTS] ambulance_data_txt_external
   (
   vehicleId int,
   recordId int,
   patientId int,
   calls int,
   locationLatitute double,
   locationLongtitue double,
   recordTime string,
   direction string
   )
   stored BY 'com.aliyun.odps.udf.example.text.TextStorageHandler' 
     WITH serdeproperties (
   'delimiter'='|',  
   'odps.properties.rolearn'='acs:ram::xxxxxxxxxxxxx:role/aliyunodpsdefaultrole'
   )
   location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/SampleData/'
   USING 'javatest-1.0-SNAPSHOT.jar'; 

   delimiter specifies the name of the delimiter of OSS data.

   You can execute the DESC EXTENDED ambulance_data_txt_external; statement to view the schema of the OSS external table that you created.

   Note

   If you create an OSS external table by using a custom extractor, data in the external table is not sharded. This prevents data accuracy issues when the extractor is used to read data from OSS. If data in shards can be processed, you must run the following command to enable data sharding. When data sharding is enabled, multiple mappers are started to process data.

   set odps.sql.unstructured.data.single.file.split.enabled=true;

Example: Create an OSS external table that is used to read or write non-text data by using a custom extractor

Example: Create an OSS external table and specify the first row as the table header

-- Create an OSS external table.
CREATE EXTERNAL TABLE mf_oss_wtt
(
  id bigint,
  name string,
  tran_amt double
)
STORED BY 'com.aliyun.odps.CsvStorageHandler'
WITH serdeproperties (
  'odps.text.option.header.lines.count' = '1',
  'odps.sql.text.option.flush.header' = 'true'
)
location 'oss://oss-cn-beijing-internal.aliyuncs.com/mfosscostfee/demo11/';

-- Insert data into the external table.
INSERT overwrite TABLE mf_oss_wtt VALUES (1, 'val1', 1.1),(2, 'value2', 1.3);
-- Query data from the external table.
-- When you create the table, you can set the data types of all fields to STRING. If you do not set the data types of all fields to STRING, an error is reported when the table header is read. 
-- When you create the table, you can also configure the following settings to skip the table header: 'odps.text.option.header.lines.count' = '1'.
SELECT * FROM mf_oss_wtt;
+------------+------+------------+
| id         | name | tran_amt   |
+------------+------+------------+
| 1          | val1 | 1.1        |
| 2          | value2 | 1.3        |
+------------+------+------------+

Example: Create an OSS external table in which the number of columns is different from the number of columns in the OSS data file

1. Create a data file in the CSV format and upload the data file to the OSS directory doc-test-01/demo.

   1,kyle1,this is desc1
   2,kyle2,this is desc2,this is two
   3,kyle3,this is desc3,this is three, I have 4 columns

2. Create an OSS external table.

   • Perform the truncate operation to process the rows in the external table in which the number of columns is different from the number of columns in the data file.

     -- Delete the table.
     DROP TABLE test_mismatch;
     -- Create an OSS external table.
     CREATE EXTERNAL TABLE IF NOT EXISTS test_mismatch
     (
       id string,
       name string,
       dect string,
       col4 string
     )
     stored BY 'com.aliyun.odps.CsvStorageHandler'
     WITH serdeproperties ('odps.sql.text.schema.mismatch.mode' = 'truncate')
     location 'oss://oss-cn-shanghai-internal.aliyuncs.com/doc-test-01/demo/';

   • Perform the ignore operation to process the rows in the table in which the number of columns is different from the number of columns in the data file.

     -- Delete the table.
     DROP TABLE test_mismatch01;
     -- Create an OSS external table.
     CREATE EXTERNAL TABLE IF NOT EXISTS test_mismatch01
     (
       id string,
       name string,
       dect string,
       col4 string
     )
     stored BY 'com.aliyun.odps.CsvStorageHandler'
     WITH serdeproperties ('odps.sql.text.schema.mismatch.mode' = 'ignore')
     location 'oss://oss-cn-shanghai-internal.aliyuncs.com/doc-test-01/demo/';

3. Query data in the table.

   • SELECT * FROM test_mismatch;
     
     -- The following result is returned:
     +----+------+------+------+
     | id | name | dect | col4 |
     +----+------+------+------+
     | 1  | kyle1 | this is desc1 | NULL |
     | 2  | kyle2 | this is desc2 | this is two |
     | 3  | kyle3 | this is desc3 | this is three |
     +----+------+------+------+

   • SELECT * FROM test_mismatch01;
     
     -- The following result is returned:
     +----+------+------+------+
     | id | name | dect | col4 |
     +----+------+------+------+
     | 2  | kyle2 | this is desc2 | this is two |
     +----+------+------+------+

Reference: Parameters in the syntax

This section describes the parameters in the syntax.

Reference: WITH SERDEPROPERTIES properties

Reference: TBLPROPERTIES properties

References

After you create an OSS external table, you can use the external table to read the data that is stored in an OSS directory. For more information, see Read OSS data.