通过外表导入至数仓版

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

前提条件

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

    说明

    查看企业版湖仓版集群的内核版本,请执行SELECT adb_version();。如需升级内核版本,请联系技术支持。

  • HDFS数据文件格式需为CSV、Parquet或ORC。

  • 已创建HDFS集群并在HDFS文件夹中准备需要导入的数据,本文示例中所用文件夹为hdfs_import_test_data.csv

  • 已在HDFS集群中为AnalyticDB for MySQL集群配置如下服务访问端口:

    • namenode:用于读写文件系统元信息。您可以在fs.defaultFS参数中配置端口号,默认端口号为8020。

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

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

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

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

操作步骤

  1. 连接目标AnalyticDB for MySQL集群。详细操作步骤,请参见连接集群

  2. 创建目标数据库。详细操作步骤,请参见创建数据库

    本示例中,AnalyticDB for 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 for MySQL集群中。

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

    • (推荐)方式一:使用INSERT OVERWRITE导入数据。数据批量导入,性能好。导入成功后数据可见,导入失败数据会回滚,示例如下。

      INSERT OVERWRITE 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 adb_hdfs_import_test
      SELECT * FROM hdfs_import_test_external_table;

      返回结果如下。

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

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

后续步骤

导入完成后,您可以登录AnalyticDB for 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 for 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_ha_host_port

    如果HDFS集群配置了HA功能,创建外表时需配置hdfs_ha_host_port参数,格式为ip1:port1,ip2:port2,参数中的IP与Port是主备namenode的IP与Port。

    示例:192.168.xx.xx:8020,192.168.xx.xx:8021

  • 创建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 for MySQL访问HDFS数据的方式。

    format

    数据文件的格式。

    • 创建Parquet格式文件的外表时需设置为parquet

    • 创建ORC格式文件的外表时需设置为orc

    hdfs_url

    HDFS集群中目标数据文件或文件夹的绝对地址,需要以hdfs://开头。

    partition_column

    选填

    定义表的分区列,用英文逗号(,)切分各列。定义分区列的方法,请参见创建带分区的HDFS外表

    hdfs_ha_host_port

    如果HDFS集群配置了HA功能,创建外表时需配置hdfs_ha_host_port参数,格式为ip1:port1,ip2:port2,参数中的IP与Port是主备namenode的IP与Port。

    示例:192.168.xx.xx:8020,192.168.xx.xx:8021

    说明
    • 外表创建语句中的列名需与Parquet或ORC文件中该列的名称完全相同(可忽略大小写),且列的顺序需要一致。

    • 创建外表时,可以仅选择Parquet或ORC文件中的部分列作为外表中的列,未被选择的列不会被导入。

    • 如果创建外表创建语句中出现了Parquet或ORC文件中不存在的列,针对该列的查询结果均会返回NULL。

    Parquet文件与AnalyticDB for MySQL的数据类型映射关系

    Parquet基本类型

    Parquet的logicalType类型

    AnalyticDB for MySQL的数据类型

    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 for MySQL的数据类型映射关系

    ORC文件中的数据类型

    AnalyticDB for MySQL中的数据类型

    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的取值改为csv或orc。
  "partition_column":"p1, p2, p3"  //针对包含分区的HDFS数据,如需以分区的模式进行查询,那么在导入数据至AnalyticDB MySQL时就需要在外表创建语句中指定分区列partition_column。
}';
说明
  • TABLE_PROPERTIES中的partition_column参数用于指定分区列(本例中的p1、p2、p3)。且partition_column参数中的分区列必须按照第1级、第2级、第3级的顺序声明(本例中p1为第1级分区,p2为第2级分区,p3为第3级分区)。

  • 列定义中必须定义分区列(本例中的p1、p2、p3)及类型,且分区列需要置于列定义的末尾。

  • 列定义中分区列的先后顺序需要与partition_column中分区列的顺序保持一致。

  • 分区列支持的数据类型包括:BOOLEANTINYINTSMALLINTINTINTEGERBIGINTFLOATDOUBLEDECIMALVARCHARSTRINGDATETIMESTAMP

  • 查询数据时,分区列和其它数据列的展示和用法没有区别。

  • 不指定format时,默认格式为CSV。

  • 其他参数的详细说明,请参见参数说明