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

前提条件

  • AnalyticDB MySQL版集群需为V3.1.4.4或以上版本。

    如何查看集群版本,请参见查看版本

    如需升级版本,请提交工单联系技术支持。

  • HDFS数据文件格式需为Parquet、ORC、CSV或TEXT。
  • 已创建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或TEXT格式的外部映射表。具体语法,请参见附录1:创建HDFS外表

    • 创建带分区外部映射表。

      使用CREATE TABLE语句在目标库adb_demo中创建CSV、Parquet或ORC格式的外部映射表。具体语法,请参见附录2:创建带分区的HDFS外表

  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;

附录1:创建HDFS外表

AnalyticDB MySQL版集群支持创建格式为CSV、Parquet、ORC或TEXT的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 选填 定义表的分区列,用英文逗号(,)切分各列。定义分区列的方法,请参见附录2:创建带分区的HDFS外表
    compress_type 定义数据文件的压缩类型,CSV格式的文件目前仅支持Gzip压缩类型。
    skip_header_line_count 定义导入数据时需要在开头跳过的行数。CSV文件第一行为表头,若设置该参数为1,导入数据时可自动跳过第一行的表头。

    默认为0,即不跳过。

  • 创建文件格式为Parquet的外表
    说明
    • 外表创建语句中的列名需与Parquet文件中该列的名称完全对应(可忽略大小写),且列的顺序也需一致。
    • 创建外表时,可以仅选择Parquet文件中的部分列作为外表中的列,未被选择的列将将不会被导入。
    • 外表创建语句中若出现了Parquet文件中不存在的列,针对该列的查询结果均会返回NULL。

    语句如下:

    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
    hdfs_url HDFS集群中目标数据文件或文件夹的绝对地址,需要以hdfs://开头。
    partition_column 选填 定义表的分区列,用英文逗号(,)切分各列。定义分区列的方法,请参见附录2:创建带分区的HDFS外表
    compress_type 定义数据文件的压缩类型,Parquet格式的文件目前支持的压缩类型为Snappy、Gzip、LZO、Brotli、LZ4、Zstd和Uncompressed。

    示例:"compress_type":"snappy"

    创建Parquet文件格式的外表时,需要注意数据类型的对应关系,具体规则如下表。

  • 创建文件格式为ORC的外表
    语句如下:
    CREATE TABLE IF NOT EXISTS hdfs_import_test_external_table
    (
        uid string,
        other string
    )
    ENGINE='HDFS'
    TABLE_PROPERTIES='{
        "format":"orc",
        "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_import_test_orc_data/"
    }';
    参数 是否必填 说明
    ENGINE='HDFS' 必填 外部表的存储引擎说明。本示例使用的存储引擎为HDFS。
    TABLE_PROPERTIES AnalyticDB MySQL访问HDFS数据的方式。
    format 数据文件的格式。创建ORC格式文件的外表时需设置为orc
    hdfs_url HDFS集群中目标数据文件或文件夹的绝对地址,需要以hdfs://开头。
    partition_column 选填 定义表的分区列,用英文逗号(,)切分各列。定义分区列的方法,请参见附录2:创建带分区的HDFS外表
  • 创建文件格式为TEXT的外表
    语句如下:
    CREATE TABLE IF NOT EXISTS hdfs_import_test_external_table
    (
        uid string,
        other string
    )
    ENGINE='HDFS'
    TABLE_PROPERTIES='{
        "format":"text",
        "row_delimiter":"\n",
        "field_delimiter":",",
        "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_import_test_text_data/"
    }';
    参数 是否必填 说明
    ENGINE='HDFS' 必填 外部表的存储引擎说明。本示例使用的存储引擎为HDFS。
    TABLE_PROPERTIES AnalyticDB MySQL访问HDFS数据的方式。
    format 数据文件的格式。创建TEXT格式文件的外表时需设置为text
    row_delimiter 定义TEXT文件的行分隔符,目前仅支持分隔符\n
    field_delimiter 定义TEXT文件的列分隔符。分隔符只能为一个字符,例如英文逗号(,)。若分隔符设置为\n表示整行为一个字段。
    hdfs_url HDFS集群中目标数据文件或文件夹的绝对地址,需要以hdfs://开头。
    compress_type 选填 定义数据文件的压缩类型,TEXT格式的文件目前仅支持Gzip压缩类型。

附录2:创建带分区的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

针对如上包含分区的HDFS数据,如果需要以分区的模式进行查询,那么在导入数据至AnalyticDB MySQL时就需要在外表创建语句中指定分区列(即partition_column)。

本文以Parquet文件格式为例(如需创建CSV或ORC文件格式,仅需将示例中format的取值改为csvorc即可),介绍如何在创建外表时指定分区列,语法如下:
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",
  "partition_column":"p1, p2, p3"
}'
说明
  • 在外表中定义分区列时,需要在列定义中指定包含分区的列及其数据类型(如示例中的p1 date, p2 int, p3 varchar),还需要在TABLE_PROPERTIES中的partition_column属性里指定这些列为分区列。
  • 创建外表的SQL语句中分区列的顺序和partition_column属性中指定的分区列的顺序,都需要与HDFS数据源中的分区顺序完全一致。例如源数据的分区顺序:p1为第1级分区,p2为第2级分区,p3为第3级分区,列定义中的顺序和partition_column属性中的列顺序也必须为p1p2p3
  • 支持作为分区列的数据类型需为:boolean、tinyint、smallint、int、integer、bigint、float、double、decimal、varchar、string、date、timestamp
  • 查询时,分区列使用的查询语法以及查询结果的展示效果和其它数据列没有区别。