外部表概述
现阶段MaxCompute SQL处理的主要是以cfile列格式存储在内部MaxCompute表格中的结构化数据。对于 MaxCompute表外的各种用户数据(包括文本以及各种非结构化的数据),您需要通过不同工具导入MaxCompute表再进行计算。以OSS为例,如果您需要在MaxCompute中处理OSS上的数据,通常有以下两种方式:
- 通过OSS SDK或者其他工具从OSS下载数据,然后再通过MaxCompute Tunnel将数据导入表里。
- 编写UDF,在UDF里直接调用OSS SDK访问OSS数据。
但这两种做法都有不足之处:
- 第一种需要在MaxCompute系统外部做一次中转,如果OSS数据量太大,还需要考虑如何并发来加速,无法充分利用 MaxCompute大规模计算的能力。
- 第二种通常需要申请UDF网络访问权限,还需要开发者自己控制作业并发数,解决数据如何分片的问题。
MaxCompute外部表用于建立MaxCompute表与外部数据源的关联,提供各种数据的接入和输出能力。创建好的外部表可以像普通的MaxCompute表一样使用(大部分场景),充分利用MaxCompute SQL的强大计算功能。
说明
- 使用外部表功能,外部表的数据不会复制一份放在MaxCompute并产生存储费用。
- 外部表当前支持全量搜索。
- Tunnel功能及Tunnel SDK当前不支持外部表操作。您可以通过Tunnel直接上传数据到MaxCompute内部表,或者是通过OSS Python SDK上传到OSS后,在MaxCompute使用外部表做映射。
STS模式授权
本节介绍的是如何在RAM中自定义授权MaxCompute访问OSS和OTS的权限。MaxCompute结合了阿里云的访问控制服务(RAM)和令牌服务(STS)实现对数据的安全访问,详见OSS与OTS的STS模式授权。
表格存储(Tablestore,OTS)是阿里云自研的面向海量结构化数据存储的Serverless NoSQL多模型数据库,被广泛用于社交、物联网、人工智能、元数据和大数据等业务场景。提供兼容HBase的WideColumn模型、消息模型Timeline以及时空模型Timestream,可提供PB级存储、千万TPS以及毫秒级延迟的服务能力。
内置Extractor访问OSS非结构化数据
访问外部数据源时可以使用MaxCompute内置的Extractor,读取按照约定格式存储的OSS数据。
假设有一份CSV数据存在OSS上,Endpoint为oss-cn-shanghai-internal.aliyuncs.com,Bucket为oss-odps-test,数据文件的存放路径为/demo/vehicle.csv。
创建外部表
创建外部表的代码示例如下
CREATE EXTERNAL TABLE IF NOT EXISTS ambulance_data_csv_external
(
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::xxxxx:role/aliyunodpsdefaultrole'
)
LOCATION 'oss://oss-cn-shanghai-internal.aliyuncs.com/oss-odps-test/Demo/';
参数说明:
- com.aliyun.odps.CsvStorageHandler是内置的处理CSV格式文件的StorageHandler,它定义了如何读写CSV文件。
- odps.properties.rolearn中的信息是RAM中AliyunODPSDefaultRole的ARN信息。您可以通过RAM控制台中的角色详情获取。
- LOCATION必须指定一个OSS目录,默认系统会读取这个目录下所有的文件。
- 建议使用OSS提供的内网域名,否则将产生OSS流量费用。
- 建议OSS数据存放的区域对应开通MaxCompute的区域。由于MaxCompute只有在部分区域部署,不承诺跨区域的数据连通性。
- OSS的连接格式为oss://oss-cn-shanghai-internal.aliyuncs.com/Bucket名称/目录名称/。目录后不要加文件名称,以下为错误用法。
- 外部表只是在系统中记录了与OSS目录的关联,当Drop这张表时,对应的LOCATION数据不会被删除。
读取gzip压缩的CSV/TSV数据
创建外部表的代码示例如下
CREATE EXTERNAL TABLE IF NOT EXISTS ambulance_data_csv_external
(
vehicleId bigint,
recordId bigint,
patientId bigint,
calls bigint,
locationLatitute double,
locationLongtitue double,
recordTime string,
direction string
)
STORED BY 'com.aliyun.odps.CsvStorageHandler'
WITH SERDEPROPERTIES (
'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole'
[,'odps.text.option.gzip.input.enabled'='true']
[,'name3'='value3']
)
LOCATION 'oss://oss-cn-hangzhou-zmf.aliyuncs.com/oss-odps-test/Demo/SampleData/CSV/AmbulanceData/';
目前SERDEPROPERTIES还支持的属性项,如下所示。
属性名 | 属性值 | 默认值 | 说明 |
---|---|---|---|
odps.text.option.gzip.input.enabled | True/False | False | 打开/关闭读压缩。 |
odps.text.option.gzip.output.enabled | True/False | False | 打开/关闭写压缩。 |
odps.text.option.header.lines.count | 非负整数 | 0 | 跳过文本文件头N行。 |
odps.text.option.null.indicator | 字符串 | 空字符串 | 在解析或者写出NULL值时代表NULL的字符串。 |
odps.text.option.ignore.empty.lines | True/False | True | 是否忽略空行。 |
odps.text.option.encoding | UTF-8/UTF-16/US-ASCII | UTF-8 | 指定文本的字符编码。 |
odps.text.option.delimiter | 单个字符 | 英文逗号(,) | 指定文本的列分隔符。 |
注意:当关联OSS压缩数据的外部表,需要进行读(Select)、写(Insert)操作,那么创建这个外部表时,需要同时将odps.text.option.gzip.input.enabled和odps.text.option.gzip.output.enabled两个属性设置为True。
自定义Extractor访问OSS
自定义Extractor访问OSS文本文件
当OSS中的数据格式比较复杂,内置的Extractor无法满足需求时,需要自定义Extractor来读取OSS文件中的数据,例如需要自定义分隔符。
- 定义Extractor。写一个通用的Extractor,将分隔符作为参数传进来,可以处理所有类似格式的TXT文件。
- 定义StorageHandler。StorageHandler是外部表自定义逻辑的统一入口。
- 编译打包。将自定义代码编译打包,并上传到MaxCompute。
- 创建一张外部表,不同的是在指定外部表访问数据的时候,需要使用自定义的StorageHandler。其中delimeter的参数值是自定义的分割符,此处需要转义。
自定义Extractor访问非文本文件数据
外部表的数据分区
与MaxCompute内部表不同,对于存放在外部存储上(例如OSS)上面的数据,MaxComput没有数据的管理权限,因此如果需要使用分区表功能,在OSS上数据文件的存放路径必须符合一定的格式,路径格式如下:
partitionKey1=value1\partitionKey2=value2\...
示例
建表语句如下
CREATE EXTERNAL TABLE log_table_external (
click STRING,
ip STRING,
url STRING,
)
PARTITIONED BY (
year STRING,
month STRING,
day STRING
)
STORED BY 'com.aliyun.odps.CsvStorageHandler'
WITH SERDEPROPERTIES (
'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole'
)
LOCATION 'oss://oss-cn-hangzhou-zmf.aliyuncs.com/oss-odps-test/log_data/';
OSS上对应的存储路径应形如以下ls返回结果。
osscmd ls oss://oss-odps-test/log_data/
2017-01-14 08:03:35 128MB Standard oss://oss-odps-test/log_data/year=2016/month=06/day=01/logfile
2017-01-14 08:04:12 127MB Standard oss://oss-odps-test/log_data/year=2016/month=06/day=01/logfile.1
2017-01-14 08:05:02 118MB Standard oss://oss-odps-test/log_data/year=2016/month=06/day=02/logfile
2017-01-14 08:06:45 123MB Standard oss://oss-odps-test/log_data/year=2016/month=07/day=10/logfile
2017-01-14 08:07:11 115MB Standard oss://oss-odps-test/log_data/year=2016/month=08/day=08/logfile
...
在建表完成后,需要用alter语句将数据引入MaxCompute。
ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '06', day = '01')
ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '06', day = '02')
ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '07', day = '10')
ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '08', day = '08')
...
但是,如果OSS上对应的存储路径并非标准形式,也是可以通过自定义分区路径进行数据引入的。
ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '06', day = '01')
LOCATION 'oss://oss-cn-hangzhou-zmf.aliyuncs.com/oss-odps-test/log_data_customized/2016/06/01/';
处理OSS的开源格式数据
对于存储在OSS上的各种流行的开源数据格式(ORC、PARQUET、SEQUENCEFILE、RCFILE、AVRO和TEXTFILE),可以通过非结构化框架在MaxCompute进行处理。
详见处理OSS的开源格式数据。
输出到OSS的非结构化数据
MaxCompute的非结构化框架支持通过INSERT方式将MaxCompute的数据直接输出到OSS。MaxCompute也支持通过外部表关联OSS,进行数据输出。
输出数据到OSS分为两种情况:
- MaxCompute内部表输出到关联OSS的外部表。
- MaxCompute处理外部表后,结果直接输出到关联OSS的外部表。
与访问OSS数据一样,MaxCompute支持通过内置StorageHandler和自定义StorageHandler进行输出。
通过内置StorageHandler输出到OSS
使用MaxCompute内置的StorageHandler ,通过创建外部表可以非常方便的按照约定格式输出数据到OSS进行存储。
目前MaxCompute支持2个内置StorageHandler:
- com.aliyun.odps.CsvStorageHandler定义如何读写CSV格式数据,数据格式中英文逗号,为列分隔符,换行符为\n。
- com.aliyun.odps.TsvStorageHandler定义如何读写CSV格式数据,数据格式中\t为列分隔符,换行符为\n。
操作步骤
- 创建外部表。指定内置StorageHandler及指定对应OSS存储的文件路径。
- 对外部表执行INSERT操作实现数据输出到OSS,INSERT到OSS的单个文件的大小不能超过5G(ps,由于MaxCompute的存储有压缩,自己掂量一下实际存储大小嗷)。
通过自定义StorageHandler输出到OSS
大体步骤与使用内置StorageHandler一样,不同点在于创建外部表时,STORED BY需要指定自定义的StorageHandler。自定义的StorageHandler应该继承OdpsStorageHandler,实现getExtractorClass getOutputerClass接口,MaxCompute Studio配置好MaxCompute Java Module后,可以在examples中看到对应的示例代码,详见通过自定义StorageHandler输出到OSS。
访问OTS非结构化数据
表格存储(Table Store,OTS)是构建在阿里云飞天分布式系统之上的NoSQL数据存储服务,提供海量结构化数据的存储和实时访问。MaxCompute与Table Store是两个独立的大数据计算和存储服务,所以两者之间的网络必须保证连通性。MaxCompute公共云服务访问Table Store存储时,推荐您使用Table Store私网地址,即Host名以ots-internal.aliyuncs.com作为结尾的地址,例如tablestore://odps-ots-dev.cn-shanghai.ots-internal.aliyuncs.com。
MaxCompute与Table Store的数据类型对应关系如下所示。
MaxCompute Type | Table Store Type |
---|---|
STRING | STRING |
BIGINT | INTEGER |
DOUBLE | DOUBLE |
BOOLEAN | BOOLEAN |
BINARY | BINARY |
操作步骤
- MaxCompute计算服务访问Table Store数据需要有一个安全的授权通道。因此,MaxCompute结合了阿里云的访问控制服务(RAM)和令牌服务(STS)实现对数据的安全访问。详见前文STS模式授权。
- 创建外部表。注意,在创建外部表的时候需要保证Table Store中已经有对应的表,否则在创建外部表的时候会报错;同时,在指定字段映射关系的时候,必须需要提供Table Store表中的所有主键,属性字段可以按需取。
- 创建外部表完成后,就可通过正常的MaxCompute SQL语法访问Table Store数据。
- 如果需要将处理后的数据写回到Table Store,则可通过对外部表执行insert overwrite table操作实现。
注意:
- 如果ODPS表内数据本身有一定的顺序,例如已经按照Primary Key做过一次排序,则在写入到OTS表时,会导致压力集中在一个OTS分区上面,无法充分利用分布式写入的特点。因此,当出现这种情况时,建议通过distribute by rand()先将数据打散。
- 对于Table Store这种KV数据的NoSQL存储介质,MaxCompute的输出将只影响相对应主键所在的行,例如示例中只影响所有odps_orderkey + odps_orderdate这两个主键值对应行上的数据。而且在这些Table Store行上,也只会更新在创建外部表(ots_table_external)时指定的属性列,而不会修改未在外部表中出现的数据列。
- 将MaxCompute中的数据写入OTS时一次不能超过4MB,否则需要用户剔除掉超大数据再写入。
- 将数据批量写入时请不要有重复行,否则会报错。