AnalyticDB MySQL版支持通过外表导入导出数据。本文介绍如何通过AnalyticDB MySQL的外部映射表查询HDFS数据,并将HDFS数据导入至AnalyticDB MySQL

前提条件

  • AnalyticDB MySQL版集群需为V3.1.4.4或以上版本。
    说明
  • HDFS数据文件格式需为CSV、Parquet或ORC。
  • 已创建HDFS集群并在HDFS文件夹中准备需要导入的数据,本文示例中所用文件夹为hdfs_import_test_data.csv
  • 已在HDFS集群中为AnalyticDB MySQL集群配置如下服务访问端口:
    • namenode:用于读写文件系统元信息。您可以在fs.defaultFS参数中配置端口号,默认端口号为8020。

      详细配置方式,请参见core-default.xml

    • datanode:用于读写数据。您可以在dfs.datanode.address参数中配置端口号,默认端口号为50010。

      详细配置方式,请参见hdfs-default.xml

  • 如果您的AnalyticDB MySQL集群是弹性模式,您需要在集群信息页面的网络信息区域,打开启用ENI网络的开关。启用ENI网络

操作步骤

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

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

  3. 使用CREATE TABLE语句在目标库adb_demo中创建CSV、Parquet或ORC格式的外部映射表。
  4. 创建目标表。

    您可以使用以下语句在目标数据库adb_demo中创建一张目标表,用于存储从HDFS导入的数据:

    • 创建普通外部映射表对应的目标表(本文示例中目标表名为adb_hdfs_import_test),语法如下。
      CREATE TABLE IF NOT EXISTS adb_hdfs_import_test
      (
          uid string,
          other string
      )
      DISTRIBUTED BY HASH(uid);
    • 创建带分区外部映射表对应的目标表时(本文示例中目标表名为adb_hdfs_import_parquet_partition),需要同时在创建语句中定义普通列(如uidother)和分区列(如p1p2p3),语法如下。
      CREATE TABLE IF NOT EXISTS adb_hdfs_import_parquet_partition
      (
          uid string,
          other string,
          p1 date,
          p2 int,
          p3 varchar
      )
      DISTRIBUTED BY HASH(uid);
  5. 将HDFS中的数据导入至目标AnalyticDB MySQL集群中。

    您可以根据业务需要选择如下几种方式导入数据(分区表导入数据语法与普通表一致,如下示例中以普通表为例):

    • (推荐)方式一:使用INSERT OVERWRITE INTO导入数据。数据批量导入,性能好。导入成功后数据可见,导入失败数据会回滚,示例如下。
      INSERT OVERWRITE INTO adb_hdfs_import_test
      SELECT * FROM hdfs_import_test_external_table;
    • 方式二:使用INSERT INTO导入数据。数据插入实时可查,数据量较小时使用,示例如下。
      INSERT INTO adb_hdfs_import_test
      SELECT * FROM hdfs_import_test_external_table;
    • 方式三:异步执行导入数据,示例如下。
      SUBMIT JOB INSERT OVERWRITE INTO adb_hdfs_import_test
      SELECT * FROM hdfs_import_test_external_table;
      返回结果如下。
      +---------------------------------------+
      | job_id                                |
      +---------------------------------------+
      | 2020112122202917203100908203303****** |
      +---------------------------------------+

      您还可以根据上述job_id查看异步任务的状态,更多详情,请参见异步提交导入导出任务

后续步骤

导入完成后,您可以登录AnalyticDB MySQL的目标库adb_demo中,执行如下语句查看并验证源表数据是否成功导入至目标表adb_hdfs_import_test中:
SELECT * FROM adb_hdfs_import_test LIMIT 100;

创建HDFS外表

  • 创建文件格式为CSV的外表
    语句如下:
    CREATE TABLE IF NOT EXISTS hdfs_import_test_external_table
    (
        uid string,
        other string
    )
    ENGINE='HDFS'
    TABLE_PROPERTIES='{
        "format":"csv",
        "delimiter":",",
        "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_import_test_csv_data/hdfs_import_test_data.csv"
    }';
    参数 是否必填 说明
    ENGINE='HDFS' 必填 外部表的存储引擎说明。本示例使用的存储引擎为HDFS。
    TABLE_PROPERTIES AnalyticDB MySQL访问HDFS数据的方式。
    format 数据文件的格式。创建CSV格式文件的外表时需设置为csv
    delimiter 定义CSV数据文件的列分隔符。本示例使用的分隔符为英文逗号(,)。
    hdfs_url HDFS集群中目标数据文件或文件夹的绝对地址,需要以hdfs://开头。

    示例:hdfs://172.17.***.***:9000/adb/hdfs_import_test_csv_data/hdfs_import_test_data.csv

    partition_column 选填 定义表的分区列,用英文逗号(,)切分各列。定义分区列的方法,请参见创建带分区的HDFS外表
    compress_type 定义数据文件的压缩类型,CSV格式的文件目前仅支持Gzip压缩类型。
    skip_header_line_count 定义导入数据时需要在开头跳过的行数。CSV文件第一行为表头,若设置该参数为1,导入数据时可自动跳过第一行的表头。

    默认为0,即不跳过。

  • 创建HDFS Parquet格式/HDFS ORC格式的外表
    以Parquet格式为例,创建HDFS外表语句如下:
    CREATE TABLE IF NOT EXISTS hdfs_import_test_external_table
    (
        uid string,
        other string
    )
    ENGINE='HDFS'
    TABLE_PROPERTIES='{
        "format":"parquet",
        "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_import_test_parquet_data/"
    }';
    参数 是否必填 说明
    ENGINE='HDFS' 必填 外部表的存储引擎说明。本示例使用的存储引擎为HDFS。
    TABLE_PROPERTIES AnalyticDB MySQL访问HDFS数据的方式。
    format 数据文件的格式。
    • 创建Parquet格式文件的外表时需设置为parquet
    • 创建ORC格式文件的外表时需设置为orc
    hdfs_url HDFS集群中目标数据文件或文件夹的绝对地址,需要以hdfs://开头。
    partition_column 选填 定义表的分区列,用英文逗号(,)切分各列。定义分区列的方法,请参见创建带分区的HDFS外表
    说明
    • 外表创建语句中的列名需与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格式外表暂不支持LISTSTRUCTUNION等复合类型,会导致建表失败;ORC格式外表的列使用MAP类型可以建表,但ORC的查询会失败。

创建带分区的HDFS外表

HDFS支持对Parquet、CSV和ORC文件格式的数据进行分区,包含分区的数据会在HDFS上形成一个分层目录。在下方示例中,p1为第1级分区,p2为第2级分区,p3为第3级分区:
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
以Parquet格式为例,创建外表时指定列的建表语句示例如下:
CREATE TABLE IF NOT EXISTS hdfs_parquet_partition_table
(
  uid varchar,
  other varchar,
  p1 date,
  p2 int,
  p3 varchar
)
ENGINE='HDFS'
TABLE_PROPERTIES='{
  "hdfs_url":"hdfs://172.17.***.**:9000/adb/parquet_partition_classic/",
  "format":"parquet",  //如需创建CSV或ORC格式外表,仅需将format的取值改为csvorc。
  "partition_column":"p1, p2, p3"  //针对包含分区的HDFS数据,如需以分区的模式进行查询,那么在导入数据至AnalyticDB MySQL时就需要在外表创建语句中指定分区列partition_column。
}';
说明
  • TABLE_PROPERTIES中的partition_column属性必须声明分区列及数据类型(本例中的p1 datep2 intp3 varchar)且partition_column属性里必须严格按照第1级, 第2级, 第3级的顺序声明(本例中p1为第1级分区,p2为第2级分区,p3为第3级分区)。
  • 列定义中必须定义分区列(本例中的p1,p2,p3)及类型,且分区列需要置于列定义的末尾。
  • 定义分区列的先后顺序需要与partition_column中分区列的顺序保持一致。
  • 支持作为分区列的数据类型需为:BOOLEANTINYINTSMALLINTINTINTEGERBIGINTFLOATDOUBLEDECIMALVARCHARSTRINGDATETIMESTAMP
  • 查询分区列的语法和结果展示与其他数据列没有区别。