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

前提条件

  • 通过以下步骤在对象存储（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/目录，数据行分隔符为换行符，列分隔符为;，文件示例数据如下所示。 

      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
...
      测试数据
  • （可选）若AnalyticDB MySQL集群是弹性模式，您需要在集群信息页面的网络信息区域，打开启用ENI网络的开关。启用ENI网络

操作步骤

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

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

    本示例中，AnalyticDB MySQL集群的目标库名为adb_demo。

  3. 创建外部映射表。使用CREATE TABLE语句在目标库adb_demo中创建CSV、Parquet或TEXT格式的OSS外部映射表。具体语法，请参见创建OSS外表语法。
  4. 查询OSS数据（若仅需导入数据则可跳过此步骤）。
    查询外表映射表和查询 AnalyticDB MySQL版内表语法没有区别，您可以方便地直接进行查询，查询语句如下： 
    SELECT uid, other FROM oss_parquet_external_table WHERE uid < 100 LIMIT 10;
    说明
    • 对于数据量较大的CSV或TEXT数据文件，强烈建议您按照后续步骤导入AnalyticDB MySQL版后再做查询，否则查询性能可能会较差。
    • 对于Parquet格式数据文件，直接查询的性能一般也比较高，您可以根据需要决定是否进一步导入到AnalyticDB MySQL版后再做查询。
  5. 创建目标表。您可以在目标数据库adb_demo中创建一张目标表adb_oss_import_test，用于存储从OSS导入的数据。建表语句如下： 
    CREATE TABLE IF NOT EXISTS adb_oss_import_test
(
    uid string,
    other string
)
DISTRIBUTED BY HASH(uid);
  6. 执行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://$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 MySQL版如何访问OSS中的数据。
    endpoint OSS的EndPoint（域名节点）。
    说明 目前仅支持 AnalyticDB MySQL版通过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数据文件的列分隔符。例如您可以将列分隔符设置为英文逗号（,）。
    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://$bucketname/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文件时，需注意如下几点：
    • 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格式外表

    语法如下。

    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 MySQL版如何访问OSS中的数据。
    endpoint OSS的EndPoint（域名节点）。
    说明 目前仅支持 AnalyticDB 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格式文件的外表时，需要注意数据类型的对应关系，具体规则如下表。

    表 1. 数据类型对应关系表
    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 MySQL版如何访问OSS中的数据。
    endpoint OSS的EndPoint（域名节点）。
    说明 目前仅支持 AnalyticDB 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。
  • 查询时分区列和其它数据列的表现和用法没有区别。