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

前提条件

  • 通过以下步骤在对象存储(Object Storage Service,简称OSS)中创建存储AnalyticDB for MySQL数据的目录。
    1. 开通OSS服务
      说明 OSS与AnalyticDB for MySQL所属Region相同。
    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 for MySQL快速入门,完成创建实例、设置白名单、创建账号和数据库等准备工作。

查询和导入OSS外表

  1. 连接目标集群,进入目标数据库。

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

  2. 通过CREATE TABLE,在adb_demo数据库中创建外部映射表oss_import_test_external_table
    • CSV格式文件
      注意 如果从OSS导入的CSV格式文件中含有表头,需要设置skip_header_line_count过滤掉表头保证数据正确性。
      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

      accessid 您在访问OSS中的文件时所持有的AccessKey ID。

      如何获取您的accessid和accesskey,请参见获取账号的AK信息

      accesskey 您在访问OSS中的文件时所持有的Access Key Secret。
      format(可选) 数据文件的格式,默认值为CSV格式。取值范围:
      • CSV
      • TEXT
      • Parquet
      delimiter(CSV格式必选) 定义CSV数据文件的列分隔符。
      ossnull(CSV格式可选) 标识NULL值,包含以下四种取值。
      • 1,默认值为1:EMPTY_SEPARATORS

        a,"",,c --> "a","",NULL,"c"

      • 2:EMPTY_QUOTES

        a,"",,c --> "a",NULL,"","c"

      • 3:BOTH

        a,"",,c --> "a",NULL,NULL,"c"

      • 4:NEITHER

        a,"",,c --> "a","","","c"

      skip_header_line_count(CSV格式可选) CSV文件第一行为表头,导入数据时设置为1可自动跳过第一行。或设置为其它值表示开头跳过的行数。默认为0,也就是不跳过。
      row_delimiter(TEXT格式必选) 定义TEXT文件的行分割符,目前仅支持一种:\n
      field_delimiter(TEXT格式必选) 定义TEXT文件的列分隔符,只能为一个字符。设置为\n表示整行为一个字段。
    • Parqeut格式文件
      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"
      }'
    • 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://****"
      }';
  3. 查询OSS数据(若仅需导入数据则可跳过此步骤)。
    查询外部映射表和查询AnalyticDB for MySQL内表语法没有区别,您可以根据需要决定是否将数据导入到AnalyticDB for MySQL后再进行查询。
    • 对于Parquet格式数据文件,直接查询的速度比较快。
    • 对于数据量较大的CSV/TEXT数据文件,请您按照后续步骤将数据导入AnalyticDB for MySQL后再进行查询,否则查询速度无法保证。
    select * from oss_import_test_external_table
  4. 通过CREATE TABLE,在adb_demo数据库中创建目标表adb_oss_import_test存储从OSS中导入的数据。
    CREATE TABLE IF NOT EXISTS adb_oss_import_test
    (
    uid string,
    other string
    )
    DISTRIBUTED BY HASH(uid)                  
  5. 执行INSERT语句将OSS数据导入AnalyticDB for 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                                |
      +---------------------------------------+
      | 2020112122202917203100908203303000715 |

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

针对Parquet文件格式的注意事项

  • 如果路径下有非Parquet文件类型的其它文件存在,查询将会报错。所以请注意保证数据目录的纯净。
  • 外表定义中column的名称应与Parquet文件的中该column的名称必须完全对应(可忽略大小写),而顺序可以是随意的,但建议也保持同样顺序。
  • 外表定义中的column可以只选择Parquet文件的部分列,未被外表定义的Parquet文件的列将被忽略;反之如果定义了Parquet文件中未包含的列,该列的查询将均为NULL。
  • 外表定义中column的数据类型应与Parquet文件的中该column数据类型必须对应,可参考下表:
    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

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