本文介绍如何通过AnalyticDB MySQL版的外部映射表直接查询OSS数据文件,以及如何将OSS中的数据文件导入AnalyticDB MySQL版。目前支持的OSS数据文件格式有Parquet、CSV和ORC。
前提条件
- 通过以下步骤在对象存储(Object Storage Service,简称OSS)中创建存储AnalyticDB MySQL版数据的目录。
- 如果您的AnalyticDB MySQL集群是弹性模式,您需要在集群信息页面的网络信息区域,打开启用ENI网络的开关。
操作步骤
本示例将oss_import_test_data.txt
中的数据导入AnalyticDB MySQL版的adb_demo
库中。
创建OSS外表语法
- 创建OSS CSV格式外表
语法如下。
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://<bucket-name>/adb/oss_import_test_data.txt", "accessid":"LTAIF****5FsE", "accesskey":"Ccw****iWjv", "delimiter":";", "skip_header_line_count":1 }';
参数 是否必填 说明 ENGINE=’OSS’ 必填 表示该表是外部表,使用的存储引擎是OSS。 TABLE_PROPERTIES 用于告知AnalyticDB MySQL版如何访问OSS中的数据。 endpoint OSS的EndPoint(地域节点)。 说明 目前仅支持AnalyticDB MySQL版通过ECS的VPC网络访问OSS。登录OSS控制台,单击目标Bucket,在Bucket概览页面查看EndPoint(地域节点)。
url OSS中源数据文件或文件夹的绝对地址。建议文件夹地址以 /
结尾。示例:- 文件:
oss://<bucket-name>/adb/oss_import_test_data.txt
。 - 文件夹:
oss://<bucket-name>/adb_data/
。
accessid 您在访问OSS中的文件或文件夹时所持有的AccessKey ID。 如何获取您的AccessKey ID和AccessKey Secret,请参见获取账号的AK信息。
accesskey 您在访问OSS中的文件或文件夹时所持有的AccessKey Secret。 delimiter 定义CSV数据文件的列分隔符。例如您可以将列分隔符设置为英文逗号(,)。 null_value 选填 定义CSV数据文件的 NULL
值。默认将空值定义为NULL
,即"null_value": ""
。说明 AnalyticDB MySQL版集群需为V3.1.4.2或以上版本才支持配置该参数。关于版本信息,请参见新功能发布记录。ossnull 选择CSV数据文件中 NULL
值的对应规则。取值范围如下:- 1(默认值):表示
EMPTY_SEPARATORS
,即仅将空值定义为NULL
。示例:
a,"",,c --> "a","",NULL,"c"
- 2:表示
EMPTY_QUOTES
,即仅将""
定义为NULL
。示例:
a,"",,c --> "a",NULL,"","c"
- 3:表示
BOTH
,即同时将空值和""
定义为NULL
。示例:
a,"",,c --> "a",NULL,NULL,"c"
- 4:表示
NEITHER
,即空值和""
均不定义为NULL
。示例:
a,"",,c --> "a","","","c"
说明 上述各示例的前提为"null_value": ""
。skip_header_line_count 定义导入数据时需要在开头跳过的行数。CSV文件第一行为表头,若设置该参数为1,导入数据时可自动跳过第一行的表头。 默认取值为0,即不跳过。
oss_ignore_quote_and_escape 是否忽略字段值中的引号和转义。默认取值为 false
,即不忽略字段值中的引号和转义。说明 AnalyticDB MySQL版集群需为V3.1.4.2或以上版本才支持设置该参数。关于版本信息,请参见新功能发布记录。AnalyticDB MySQL支持通过OSS的CSV格式的外表读写Hive TEXT文件。建表语句示例如下:CREATE TABLE adb_csv_hive_format_oss ( a tinyint, b smallint, c int, d bigint, e boolean, f float, g double, h varchar, i varchar, -- binary j timestamp, k DECIMAL(10, 4), l varchar, -- char(10) m varchar, -- varchar(100) n date ) ENGINE = 'OSS' TABLE_PROPERTIES='{ "format": "csv", "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com", "accessid":"LTAIF****5FsE", "accesskey":"Ccw****iWjv", "url":"oss://<bucket-name>/adb/adb_csv_hive_format_oss.txt", "delimiter": "\\1", "null_value": "\\\\N", "oss_ignore_quote_and_escape": "true", "ossnull": 2, }';
说明 在创建OSS的CSV格式的外表来读取Hive TEXT文件时,需注意如下几点:- Hive TEXT文件的默认列分隔符为
\1
。若您需要通过OSS的CSV格式的外表读写Hive TEXT文件,您可以在配置delimiter
参数时将其转义为\\1
。 - Hive TEXT文件的默认
NULL
值为\N
。若您需要通过OSS的CSV格式的外表读写Hive TEXT文件,您可以在配置null_value
参数时将其转义为\\\\N
。 - Hive的其他基本数据类型(如
BOOLEAN
)与AnalyticDB MySQL版的数据类型一一对应,但BINARY
、CHAR(n)
和VARCHAR(n)
类型均对应AnalyticDB MySQL版中的VARCHAR
类型。
- 文件:
- 创建OSS Parquet格式/OSS ORC格式外表以Parquet格式为例,创建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://<bucket-name>/adb/oss_import_test_data.txt", "accessid":"LTAIF****5FsE", "accesskey":"Ccw****iWjv", "format":"parquet" }';
参数 说明 ENGINE=’OSS’ 表示该表是外部表,使用的存储引擎是OSS。 TABLE_PROPERTIES 用于告知AnalyticDB MySQL版如何访问OSS中的数据。 endpoint OSS的EndPoint(地域节点)(域名节点)。 说明 目前仅支持AnalyticDB MySQL版通过OSS中ECS的VPC网络(内网)访问OSS。登录OSS控制台,单击目标Bucket,在Bucket概览页面查看EndPoint(地域节点)。
url OSS中源数据文件或文件夹的绝对地址。建议文件夹地址以 /
结尾。示例:- 文件:
oss://<bucket-name>/adb/oss_import_test_data.txt
。 - 文件夹:
oss://<bucket-name>/adb_data/
。
accessid 您在访问OSS中的文件或文件夹时所持有的AccessKey ID。 如何获取您的AccessKey ID和AccessKey Secret,请参见获取账号的AK信息。
accesskey 您在访问OSS中的文件或文件夹时所持有的AccessKey Secret。 format 数据文件的格式。 - 创建Parquet格式文件的外表时,必须将其设置为
parquet
。 - 创建ORC格式文件的外表时,必须将其设置为
orc
。
注意- Parquet和ORC格式外表定义中列的名称应与Parquet和ORC文件的中该列的名称必须完全相同(可忽略大小写),建议列的顺序保持一致。
- Parquet和ORC格式外表定义中的列可以只选择Parquet和ORC文件的部分列,未被外表定义的Parquet和ORC文件的列将被忽略;反之如果定义了Parquet和ORC文件中未包含的列,该列的查询结果为NULL。
Parquet文件与AnalyticDB MySQL版3.0的数据类型映射关系如下表。Parquet基本类型 Parquet的logicalType类型 AnalyticDB MySQL版3.0中对应的数据类型 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 注意 Parquet格式外表暂不支持STRUCT
类型,会导致建表失败。ORC文件与AnalyticDB MySQL版3.0的数据类型映射关系如下表。ORC文件中的数据类型 AnalyticDB MySQL版3.0中对应的数据类型 BOOLEAN BOOLEAN BYTE TINYINT SHORT SMALLINT INT INT或INTEGER LONG BIGINT DECIMAL DECIMAL FLOAT FLOAT DOUBLE DOUBLE - BINARY
- STRING
- VARCHAR
- VARCHAR
- STRING
- JSON(如果已知ORC该列内容为JSON格式)
TIMESTAMP TIMESTAMP或DATETIME DATE DATE 注意 ORC格式外表暂不支持LIST
、STRUCT
和UNION
等复合类型,会导致建表失败;ORC格式外表的列使用MAP
类型可以建表,但ORC的查询会失败。 - 文件:
针对带有分区的数据文件创建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格式为例,创建带有分区的OSS外表的语句如下:
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-cn-hangzhou-internal.aliyuncs.com",
"url":"oss://<bucket-name>/adb/oss_parquet_data_dir",
"accessid":"LTAIF****5FsE",
"accesskey":"Ccw****iWjv",
"format":"parquet",
"partition_column":"p1, p2, p3"
}';
说明
TABLE_PROPERTIES
中的partition_column属性必须声明分区列(本例中的p1,p2,p3)且partition_column属性里必须严格按照第1级, 第2级, 第3级的顺序声明(本例中p1为第1级分区,p2为第2级分区,p3为第3级分区)。- 列定义中必须定义分区列(本例中的p1,p2,p3)及类型,且分区列需要置于列定义的末尾。
- 列定义中分区列的先后顺序需要与partition_column中分区列的顺序保持一致。
- 可以作为分区列的数据类型有:
BOOLEAN
、TINYINT
、SMALLINT
、INT
、INTEGER
、BIGINT
、FLOAT
、DOUBLE
、DECIMAL
、VARCHAR
、STRING
、DATE
、TIMESTAMP
。 - 查询时分区列和其它数据列的表现和用法没有区别。