通过外表导入OSS数据 - 云原生数据仓库AnalyticDB MySQL版

本文介绍如何通过AnalyticDB MySQL版的外部映射表直接查询OSS数据文件，以及如何将OSS中的数据文件导入AnalyticDB MySQL版。目前支持的OSS数据文件格式有Parquet、CSV和ORC。

前提条件

通过以下步骤在对象存储（Object Storage Service，简称OSS）中创建存储AnalyticDB MySQL版数据的目录。
1. 开通OSS服务
  
  说明 OSS与AnalyticDB MySQL版所属地域相同。
2. 创建存储空间
3. 创建目录
4. 上传测试数据文件
  本示例将oss_import_test_data.txt文件上传至OSS中的<bucket-name>.oss-cn-hangzhou.aliyuncs.com/adb/目录，数据行分隔符为换行符，列分隔符为;，文件示例数据如下所示：
```
uid;other
12;hello_world_1
27;hello_world_2
28;hello_world_3
33;hello_world_4
37;hello_world_5
40;hello_world_6
...             
```
如果您的AnalyticDB MySQL集群是弹性模式，您需要在集群信息页面的网络信息区域，打开启用ENI网络的开关。

操作步骤

本示例将oss_import_test_data.txt中的数据导入AnalyticDB MySQL版的adb_demo库中。

连接目标AnalyticDB MySQL集群。详细操作步骤，请参见连接集群。
创建目标数据库。详细操作步骤，请参见创建数据库。

本示例中，AnalyticDB MySQL集群的目标库名为adb_demo。
创建外部映射表。使用CREATE TABLE语句在目标库adb_demo中创建CSV、Parquet或ORC格式的OSS外部映射表。具体语法，请参见创建OSS外表语法。
查询OSS数据（若仅需导入数据则可跳过此步骤）。
查询外表映射表和查询AnalyticDB MySQL版内表语法没有区别，您可以方便地直接进行查询，查询语句如下：
```
SELECT uid, other FROM oss_import_test_external_table WHERE uid < 100 LIMIT 10;
```
说明

对于数据量较大的CSV格式数据文件，强烈建议您按照后续步骤导入AnalyticDB MySQL版后再做查询，否则查询性能可能会较差。

对于Parquet和ORC格式数据文件，直接查询的性能一般也比较高，您可以根据需要决定是否进一步导入到AnalyticDB MySQL版后再做查询。
创建目标表。您可以在目标数据库adb_demo中创建一张目标表adb_oss_import_test，用于存储从OSS导入的数据。建表语句如下：
```
CREATE TABLE IF NOT EXISTS adb_oss_import_test
(
    uid string,
    other string
)
DISTRIBUTED BY HASH(uid);
```

执行INSERT语句将OSS数据导入AnalyticDB MySQL版。

方式一：执行INSERT INTO导入数据。

INSERT INTO adb_oss_import_test
SELECT * FROM oss_import_test_external_table;

方式二：执行INSERT OVERWRITE INTO导入数据。

INSERT OVERWRITE INTO adb_oss_import_test
SELECT * FROM oss_import_test_external_table;

方式三：异步执行INSERT OVERWRITE INTO导入数据。

SUBMIT job INSERT OVERWRITE INTO adb_oss_import_test
SELECT * FROM oss_import_test_external_table;

返回结果如下：

+---------------------------------------+
| job_id                                |
+---------------------------------------+
| 2020112122202917203100908203303****** |

关于异步提交任务详情，请参见异步提交导入导出任务。

创建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。
查询时分区列和其它数据列的表现和用法没有区别。