创建OSS外部表

前提条件

注意事项

操作入口

创建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外部表

• 映射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> ...;

• 映射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类型。执行DML语句时，您需要在SQL语句前增加 set odps.sql.hive.compatible=true;命令，与SQL语句一起提交执行。
  • 关联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> ...;

• 映射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> ...;

• 映射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> ...;

示例：通过自定义解析器创建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。更多打包上传信息，请参见 打包、上传及注册。
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'; 

   其中delimeter是OSS数据的分隔符名称。

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

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

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

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

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

操作流程如下：

1. 使用MaxCompute Studio开发SpeechSentenceSnrExtractor.java、SpeechStorageHandler.java两个Java程序。
   更多开发Java程序信息，请参见 开发UDF。
2. 通过MaxCompute Studio的一键式打包功能，将SpeechStorageHandler.java打包上传为MaxCompute资源。
   假设，此处打包的资源名称为 speechtest-1.0-SNAPSHOT.jar。更多打包上传信息，请参见 打包、上传及注册。
3. 执行如下命令创建OSS外部表。

   create external table if not exists speech_sentence_snr_external
   (
   sentence_snr double,
   id string
   )
   stored by 'com.aliyun.odps.udf.example.speech.SpeechStorageHandler'
   with serdeproperties (
       'odps.properties.rolearn'='acs:ram::xxxxxxxxxxxxx:role/aliyunodpsdefaultrole',   
       'mlfFileName'='sm_random_5_utterance.text.label',
       'speechSampleRateInKHz' = '16'
   )
   location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/SpeechSentence/'
   using 'speechtest-1.0-SNAPSHOT.jar,sm_random_5_utterance.text.label';

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

参考：语法参数说明

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

参考：with serdeproperties属性列表

参考：tblproperties属性列表