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网络的开关。
操作步骤
连接目标AnalyticDB for MySQL集群。详细操作步骤,请参见连接集群。
创建目标数据库。详细操作步骤,请参见创建数据库。
本示例中,AnalyticDB for MySQL集群的目标库名为
adb_demo
。使用
CREATE TABLE
语句在目标库adb_demo
中创建CSV、Parquet或ORC格式的外表。创建普通外表。具体语法,请参见创建HDFS外表。
创建带分区外表。具体语法,请参见创建带分区的HDFS外表。
创建目标表。
您可以使用以下语句在目标数据库
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
),需要同时在创建语句中定义普通列(如uid
和other
)和分区列(如p1
、p2
和p3
),语法如下。CREATE TABLE IF NOT EXISTS adb_hdfs_import_parquet_partition ( uid string, other string, p1 date, p2 int, p3 varchar ) DISTRIBUTED BY HASH(uid);
将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格式外表暂不支持
LIST
、STRUCT
和UNION
等复合类型,会导致建表失败。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
中分区列的顺序保持一致。分区列支持的数据类型包括:
BOOLEAN
、TINYINT
、SMALLINT
、INT
、INTEGER
、BIGINT
、FLOAT
、DOUBLE
、DECIMAL
、VARCHAR
、STRING
、DATE
、TIMESTAMP
。查询数据时,分区列和其它数据列的展示和用法没有区别。
不指定format时,默认格式为CSV。
其他参数的详细说明,请参见参数说明。