本文介绍如何通过AnalyticDB for MySQL的外部映射表直接查询OSS数据文件,以及如何将OSS中的数据文件导入AnalyticDB for MySQL。目前支持的OSS数据文件格式有Parquet、CSV、TEXT。
前提条件
- 通过以下步骤在对象存储(Object Storage Service,简称OSS)中创建存储AnalyticDB for MySQL数据的目录。
- 开通OSS服务说明 OSS与AnalyticDB for MySQL所属Region相同。
- 创建存储空间
- 新建目录
- 上传测试数据文件
本示例将
oss_import_test_data.txt
文件上传至OSS中的bucket-name.oss-cn-hangzhou.aliyuncs.com/adb/
目录,数据行分隔符为换行符,列分隔符为;
,文件示例数据如下所示。number;note 0001;hello_world_1 0002;hello_world_2 0003;hello_world_3 0004;hello_world_4 0005;hello_world_5 0006;hello_world_6 ...
- 开通OSS服务
- 根据AnalyticDB for MySQL快速入门,完成创建实例、设置白名单、创建账号和数据库等准备工作。

操作步骤
本示例将oss_import_test_data.txt
中的数据导入AnalyticDB for MySQL的adb_demo
库中。
创建OSS外表语法
- 创建OSS CSV格式外表
-
示例的
oss_import_test_data.txt
文件为CSV格式,本节介绍CSV格式的OSS外表创建语法。CREATE TABLE IF NOT EXISTS oss_import_test_external_table ( uid string, other string ) ENGINE='OSS' TABLE_PROPERTIES='{ "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com", "url":"oss://$bucketname/adb/oss_import_test_data.txt", "accessid":"LTAIF****5FsE", "accesskey":"Ccw****iWjv", "delimiter":";", "skip_header_line_count":1 }'
参数 说明 ENGINE=’OSS’ 表示该表是外部表,使用的存储引擎是OSS。 TABLE_PROPERTIES 用于告知AnalyticDB for MySQL如何访问OSS中的数据。 endpoint OSS的 EndPoint(域名节点)
。说明 目前仅支持AnalyticDB for MySQL通过OSS中ECS的VPC网络(内网)访问OSS。登录OSS控制台,单击目标Bucket,在Bucket概览页面查看
endpoint
。url OSS中目标数据文件或文件夹的绝对地址。建议文件夹地址以 /
结尾。示例:文件:oss://$Bucket名称/adb/oss_import_test_data.txt
文件夹:oss://$Bucket名称/adb_data/
accessid 您在访问OSS中的文件或文件夹时所持有的AccessKey ID。 如何获取您的AccessKey ID和AccessKey Secret,请参见获取账号的AK信息。
accesskey 您在访问OSS中的文件或文件夹时所持有的Access Key Secret。 delimiter 定义CSV数据文件的列分隔符。 ossnull(可选) 标识 NULL
值,包含以下四种取值:- 默认值为1:EMPTY_SEPARATORS
a,"",,c --> "a","",NULL,"c"
- EMPTY_QUOTES
a,"",,c --> "a",NULL,"","c"
- BOTH
a,"",,c --> "a",NULL,NULL,"c"
- NEITHER
a,"",,c --> "a","","","c"
skip_header_line_count(可选) CSV文件第一行为表头,导入数据时设置为1可自动跳过第一行。或设置为其它值表示开头跳过的行数。默认为0,也就是不跳过。 - 默认值为1:EMPTY_SEPARATORS
- 创建OSS Parquet格式外表
CREATE TABLE IF NOT EXISTS oss_parquet_external_table ( uid string, other string ) ENGINE='OSS' TABLE_PROPERTIES='{ "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com", "url":"oss://****", "accessid":"LTAIF****5FsE", "accesskey":"Ccw****iWjv", "format":"parquet" }'
参数 说明 ENGINE=’OSS’ 表示该表是外部表,使用的存储引擎是OSS。 TABLE_PROPERTIES 用于告知AnalyticDB for MySQL如何访问OSS中的数据。 endpoint OSS的 EndPoint(域名节点)
。说明 目前仅支持AnalyticDB for MySQL通过OSS中ECS的VPC网络(内网)访问OSS。登录OSS控制台,单击目标Bucket,在Bucket概览页面查看
endpoint
。url OSS中目标数据文件或文件夹的绝对地址。建议文件夹地址以 /
结尾。示例:文件:oss://$Bucket名称/adb/oss_import_test_data.txt
文件夹:oss://$Bucket名称/adb_data/
accessid 您在访问OSS中的文件或文件夹时所持有的AccessKey ID。 如何获取您的AccessKey ID和AccessKey Secret,请参见获取账号的AK信息。
accesskey 您在访问OSS中的文件或文件夹时所持有的Access Key Secret。 format 数据文件的格式,创建Parquet格式文件的外表时必须将其设置为parquet。 创建Parquet格式文件的外表时,需要注意数据类型的对应关系,具体规则如下:
Parquet基本类型 Parquet的logicalType类型 可对应ADB类型 BOOLEAN 无 boolean INT32 INT_8 tinyint INT32 INT_16 smallint INT32 无 int或integer INT64 无 bigint FLOAT 无 float DOUBLE 无 double - FIXED_LEN_BYTE_ARRAY
- BINARY
- INT64
- INT32
DECIMAL decimal BINARY UTF-8 - varchar
- string
- json(如果已知Parquet该列内容为json格式)
INT32 DATE date INT64 TIMESTAMP_MILLIS timestamp或datetime INT96 无 timestamp或datetime 注意- 外表定义中column的名称应与Parquet文件的中该column的名称必须完全对应(可忽略大小写),而顺序可以是随意的,但建议也保持同样顺序。
- 外表定义中的column可以只选择Parquet文件的部分列,未被外表定义的Parquet文件的列将被忽略;反之如果定义了Parquet文件中未包含的列,该列的查询将均为NULL。
- 创建OSS TEXT格式外表
CREATE TABLE IF NOT EXISTS oss_text_external_table ( uid string, other string ) ENGINE='OSS' TABLE_PROPERTIES='{ "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com", "accessid":"LTAIF****5FsE", "accesskey":"Ccw****iWjv", "format":"text", "row_delimiter":"\n", "field_delimiter":"\n", "URL":"oss://****" }';
参数 说明 ENGINE=’OSS’ 表示该表是外部表,使用的存储引擎是OSS。 TABLE_PROPERTIES 用于告知AnalyticDB for MySQL如何访问OSS中的数据。 endpoint OSS的 EndPoint(域名节点)
。说明 目前仅支持AnalyticDB for MySQL通过OSS中ECS的VPC网络(内网)访问OSS。登录OSS控制台,单击目标Bucket,在Bucket概览页面查看
endpoint
。url OSS中目标数据文件的绝对地址。 示例:oss://$Bucket名称/adb/oss_import_test_data.txt
accessid 您在访问OSS中的文件时所持有的AccessKey ID。 如何获取您的AccessKey ID和AccessKey Secret,请参见获取账号的AK信息。
accesskey 您在访问OSS中的文件时所持有的Access Key Secret。 format 数据文件的格式,创建TEXT格式文件的外表时必须将其设置为text。 row_delimiter 定义TEXT文件的行分割符,目前仅支持一种: \n
field_delimiter 定义TEXT文件的列分隔符,只能为一个字符。设置为 \n
表示整行为一个字段。
针对带有分区的Parquet/CSV数据文件创建OSS外表
有的OSS数据源是包含分区的,会在OSS上形成一个分层目录,类似如下内容:
parquet_partition_classic/
├── p1=2020-01-01
│ ├── p2=4
│ │ ├── p3=SHANGHAI
│ │ │ ├── 000000_0
│ │ │ └── 000000_1
│ │ └── p3=SHENZHEN
│ │ └── 000000_0
│ └── p2=6
│ └── p3=SHENZHEN
│ └── 000000_0
├── p1=2020-01-02
│ └── p2=8
│ ├── p3=SHANGHAI
│ │ └── 000000_0
│ └── p3=SHENZHEN
│ └── 000000_0
└── p1=2020-01-03
└── p2=6
├── p2=HANGZHOU
└── p3=SHENZHEN
└── 000000_0
上述数据中p1为第1级分区,p2为第2级分区,p3为第3级分区。对应这种数据源,一般都希望以分区的模式进行查询,那么就需要在创建OSS外表时额外指明分区列。具体的建表语法示例如下(本例为Parquet格式,分区也支持CSV格式):
CREATE TABLE IF NOT EXISTS oss_parquet_partition_table
(
uid varchar,
other varchar,
p1 date,
p2 int,
p3 varchar
)
ENGINE='OSS'
TABLE_PROPERTIES='{
"endpoint":"oss-xxxx.aliyuncs.com",
"url":"oss://****/****/oss_parquet_data_dir",
"accessid":"****",
"accesskey":"****",
"format":"parquet",
"partition_column":"p1, p2, p3"
}'
- 如上例所示,除了在table的列定义中声明p1、p2、p3及其类型,还需要在
TABLE_PROPERTIES
部分中的partition_column属性里声明它们为分区列。且partition_column属性里必须按“第1级, 第2级, 第3级......”的严格顺序声明(例中p1为第1级分区,p2为第2级分区,p3为第3级分区),在列定义中也需保持相同顺序,并将分区列置于列定义列表的末尾。 - 可以作为分区列的数据类型有:boolean、tinyint、smallint、int/integer、bigint、float、double、decimal、varchar/string、date、timestamp。
- 查询时分区列和其它数据列的表现和用法没有区别。
在文档使用中是否遇到以下问题
更多建议
匿名提交