创建OSS外部表

前提条件

注意事项

使用限制

OSS外部表不支持cluster属性。

操作入口

创建OSS外部表语法

创建OSS外部表的场景、相应语法格式及示例如下。详细语法参数及属性列表信息，请参见参考：语法参数说明、参考：with serdeproperties属性列表和参考：tblproperties属性列表。

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

补全OSS外部表分区数据语法

示例：数据准备

示例：通过内置文本数据解析器创建OSS外部表-非分区表

与示例：数据准备中的Demo1/目录建立映射关系。创建OSS外部表命令示例如下。

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

您可以执行desc extended mc_oss_csv_external1;命令查看创建好的OSS外部表结构信息。

示例：通过内置文本数据解析器创建OSS外部表-分区表

与示例：数据准备中的Demo2/目录建立映射关系。创建OSS外部表并引入分区数据命令示例如下。

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/';
--引入分区数据。
msck repair table mc_oss_csv_external2 add partitions;
--等效于如下语句。
alter table mc_oss_csv_external2 add partition (direction = 'N') partition (direction = 'NE') partition (direction = 'S') partition (direction = 'SW') partition (direction = 'W');

您可以执行desc extended mc_oss_csv_external2;命令查看创建好的外部表结构信息。

示例：通过内置文本数据解析器创建OSS外部表-压缩数据

与示例：数据准备中的Demo3/目录建立映射关系。创建OSS外部表命令示例如下。

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

您可以执行desc extended mc_oss_csv_external3;命令查看创建好的外部表结构信息。

示例：通过内置开源数据解析器创建OSS外部表

• 映射TEXTFILE数据文件

  • 关联TEXT数据建表示例

    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> ...;

    建表时不支持自定义row format。row format默认值如下。

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

  • 关联CSV数据建表示例

    --关闭native的text reader。
    SET odps.ext.hive.lazy.simple.serde.native=false;
    --创建OSS外部表。
    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> ...;

    说明

    OpenCSVSerde只支持STRING类型。

  • 关联JSON数据建表示例

    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数据文件

  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数据文件

  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> ...;

  说明

  具体示例，请参见OSS外部表根据字段名称Mapping数据。

• 映射RCFILE数据文件

  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数据文件

  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数据文件

  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格式数据文件

  说明

  • Hudi外表只支持读取外表映射的全部文件的数据，不支持自动隐藏系统列，不支持增量读、快照读操作，不支持写操作。建议您使用MaxCompute Delta表或Paimon外部表等功能实现ACID方式的读写操作。

  • MaxCompute系统默认集成的Hudi SDK版本为org.apache.hudi:hudi-hadoop-mr-bundle:0.12.2-emr-1.0.6，无法保证Hudi SDK向前或向后兼容，兼容性由开源社区保证。

  • MaxCompute将提供支持用户上传jar并指定SDK兼容版本的方式创建外表，用于读取兼容特定版本的数据。

  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格式数据文件

  说明

  • Delta Lake外表只支持读取外表映射的全部文件的数据，不支持自动隐藏系统列，不支持增量读、快照读操作，不支持写操作。建议您使用MaxCompute Delta表或Paimon外部表等功能实现ACID方式的读写操作。

  • MaxCompute系统默认集成的Delta Lake SDK版本为io.delta:delta-core_2.11:0.2.0.5，无法保证Delta Lake SDK向前或向后兼容，兼容性由开源社区保证。

  • MaxCompute将提供支持用户上传jar并指定SDK兼容版本的方式创建外表，用于读取兼容特定版本的数据。

  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> ...;

• 映射Paimon格式数据文件

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

  paimon_maxcompute_connector.jar需要预先上传至MaxCompute项目，详情请参见Paimon外部表。

示例：通过自定义解析器创建OSS外部表

与示例：数据准备中的SampleData/目录建立映射关系。

操作流程如下：

1. 使用MaxCompute Studio开发TextExtractor.java、TextOutputer.java、SplitReader.java和TextStorageHandler.java四个Java程序。

   更多开发Java程序信息，请参见开发UDF。

2. 通过MaxCompute Studio的一键式打包功能，将TextStorageHandler.java打包上传为MaxCompute资源。

   假设，此处打包的资源名称为javatest-1.0-SNAPSHOT.jar。更多打包上传信息，请参见打包、上传及注册。

   说明

   如果有多个依赖请分别打包后上传为MaxCompute资源。

3. 执行如下命令创建OSS外部表。

   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是OSS数据的分隔符名称。

   您可以执行desc extended ambulance_data_txt_external;命令查看创建好的外部表结构信息。

   说明

   对于自定义解析器，默认不会对数据分片，防止解析器出现正确性问题。如果您确认可以处理分片，需要使用如下命令允许进行数据分片，即启动多个mapper。

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

示例：通过自定义解析器创建OSS外部表-非文本数据

示例：创建OSS外部表并指定对应OSS文件的第一行为表头

--创建外部表
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 overwrite TABLE mf_oss_wtt VALUES (1, 'val1', 1.1),(2, 'value2', 1.3);
--查询数据
--在建表的时候可以把所有字段建成string，否则表头读取时会报错。
--或者在建表的时候需要加跳过表头的参数：'odps.text.option.header.lines.count' = '1'
SELECT * FROM mf_oss_wtt;
+------------+------+------------+
| id         | name | tran_amt   |
+------------+------+------------+
| 1          | val1 | 1.1        |
| 2          | value2 | 1.3        |
+------------+------+------------+

示例：创建OSS外部表且外部表列数与OSS数据列数不一致

1. 准备如下数据，保存为CSV格式上传至OSS的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. 创建外部表。

   • 指定对于列数不一致行的处理方式为truncate。

     --删除表
     DROP TABLE test_mismatch;
     --新建外部表
     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/';

   • 指定对于列数不一致行的处理方式为ignore。

     --删除表
     DROP TABLE test_mismatch01;
     --新建外部表
     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. 查询表数据。

   • SELECT * FROM test_mismatch;
     
     --返回结果
     +----+------+------+------+
     | 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;
     
     --返回结果
     +----+------+------+------+
     | id | name | dect | col4 |
     +----+------+------+------+
     | 2  | kyle2 | this is desc2 | this is two |
     +----+------+------+------+

参考：语法参数说明

上述语法中各参数的含义如下。

参考：with serdeproperties属性列表

参考：tblproperties属性列表

相关文档

创建OSS外部表后，您可以通过外部表读取存储在OSS目录中的数据文件。具体操作请参见读取OSS数据。