ORC 普通表的导入导出方法介绍

2020-09-02 其他常见问题

内容纲要

概要说明

面对一些重要数据，很多场景下都需要将数据仓库进行复制，这可以是整个数据库的更广泛级别，也可以是较小的级别，例如表或分区。本案例将演示表和分区的导入导出。

1、掌握Inceptor 表的数据导入和导出操作

2、掌握Inceptor 的Export 和Import 的使用

3、掌握 MSCK 分区修复命令的使用

详细说明

Inceptor 数据导入导出按照表类型大致分为以下几种：

ORC 非分区普通表（也包括CSV表、TEXT表等）
ORC 分区普通表
ORC 非分区事务表
ORC 分区事务表

本案例介绍 ORC 非分区普通表的数据导入导出：大致分为三种方法：

1、第一种方法，SQL 建外表的方式，大致分为4步：

insert 到源集群的 HDFS ，并get 到本地文件系统
将源集群的文件 scp 到目标集群，并 put 到目标集群的HDFS
在目标集群创建外表指定location，或者load 数据到外表
在目标集群创建ORC 非分区普通表，然后insert into select

1、insert到源集群HDFS，并get 到本地文件系统

a、写入源 HDFS 文件系统，语法：

INSERT OVERWRITE DIRECTORY '/crmdev/orc_unpart/' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' SELECT * FROM crmdev.orc_unpart;

说明：

这个语句将查询结果写入一个目录，而不是文件，写入的结果可能是多个文件。
写入本地文件系统要加上 LOCAL 关键字；写入的是 server 所在的pod内的路径；
ROW FORMAT指定文件的行格式，不指定使用默认值；
STORED AS指定文件格式，不指定则使用默认值；

注意事项：

必须是HDFS 文件系统；
必须指定分隔符，默认的分隔符是SOH，在导入的时候识别不了；

b、get 到源集群的本地文件系统

$ hadoop fs -get /crmdev/orc_unpart/
2020-04-07 11:01:00,360 INFO util.KerberosUtil: Using principal pattern: HTTP/_HOST
$ ls -l
总用量 4
drwxr-xr-x 2 root root 4096 4月   7 11:01 orc_unpart

2、将源集群的文件 scp 到目标集群，并 put 到目标集群的HDFS

$ scp -r orc_unpart/ 172.22.22.24:/mnt/disk1/crmdev/
root@172.22.22.24's password: 
000000_0                            100%   17MB  17.3MB/s   00:00
000001_0                            100%   17MB  17.2MB/s   00:00
000002_0                            100%   17MB  17.1MB/s   00:00

在目标集群将数据上传到目标集群的HDFS：

$ hadoop fs -put orc_unpart/ /crmdev/
2020-04-07 11:34:08,536 INFO util.KerberosUtil: Using principal pattern: HTTP/_HOST

3、在目标集群创建外表指定location，或者load 数据到外表

创建外表的语句中ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’需要与INSERT OVERWRITE DIRECTORY ‘/crmdev/orc_unpart/’ ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ SELECT * FROM crmdev.orc_unpart;中的保持一致。

CREATE EXTERNAL TABLE crmdev.csv_unpart(
  group_id int DEFAULT NULL, 
  code string DEFAULT NULL, 
  name string DEFAULT NULL, 
  new_price decimal(8,2) DEFAULT NULL, 
  main_percent decimal(8,2) DEFAULT NULL, 
  today_ranking decimal(8,2) DEFAULT NULL, 
  rise_percent decimal(6,2) DEFAULT NULL, 
  fiveday_percent decimal(8,2) DEFAULT NULL, 
  fiveday_ranking decimal(6,2) DEFAULT NULL, 
  fiveday_rise_percent decimal(5,2) DEFAULT NULL, 
  teneday_percent decimal(8,2) DEFAULT NULL, 
  tenday_ranking decimal(6,2) DEFAULT NULL, 
  tenday_rise_percent decimal(5,2) DEFAULT NULL, 
  guild string DEFAULT NULL, 
  code_id string DEFAULT NULL, 
  data_dt date DEFAULT NULL
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
STORED AS CSVFILE;

然后load 数据文件到外表（load 的过程就是将/crmdev/orc_unpart/ 下的文件数据mv 到表对应的数据路径下，并不移动目录）：

LOAD DATA INPATH '/crmdev/orc_unpart/' OVERWRITE INTO TABLE crmdev.csv_unpart;

或者再建表的时候直接指定 location 到 HDFS 文件位置，如下：

CREATE EXTERNAL TABLE crmdev.csv_unpart(
  group_id int DEFAULT NULL, 
  code string DEFAULT NULL, 
  name string DEFAULT NULL, 
  new_price decimal(8,2) DEFAULT NULL, 
  main_percent decimal(8,2) DEFAULT NULL, 
  today_ranking decimal(8,2) DEFAULT NULL, 
  rise_percent decimal(6,2) DEFAULT NULL, 
  fiveday_percent decimal(8,2) DEFAULT NULL, 
  fiveday_ranking decimal(6,2) DEFAULT NULL, 
  fiveday_rise_percent decimal(5,2) DEFAULT NULL, 
  teneday_percent decimal(8,2) DEFAULT NULL, 
  tenday_ranking decimal(6,2) DEFAULT NULL, 
  tenday_rise_percent decimal(5,2) DEFAULT NULL, 
  guild string DEFAULT NULL, 
  code_id string DEFAULT NULL, 
  data_dt date DEFAULT NULL
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
STORED AS CSVFILE
LOCATION '/crmdev/orc_unpart/';

严禁直接针对该HDFS 文件创建ORC 表，后者load 到ORC 表，在 load 或者location建表的时候不会报错，但是在查询的时候会抛异常，会报错format 错误：

java.io.IOException: Malformed ORC file

4、在目标集群创建ORC 非分区普通表，然后insert into select

在目标集群创建 ORC 目标表；

CREATE TABLE crmdev.orc_unpart(
  group_id int DEFAULT NULL, 
  code string DEFAULT NULL, 
  name string DEFAULT NULL, 
  new_price decimal(8,2) DEFAULT NULL, 
  main_percent decimal(8,2) DEFAULT NULL, 
  today_ranking decimal(8,2) DEFAULT NULL, 
  rise_percent decimal(6,2) DEFAULT NULL, 
  fiveday_percent decimal(8,2) DEFAULT NULL, 
  fiveday_ranking decimal(6,2) DEFAULT NULL, 
  fiveday_rise_percent decimal(5,2) DEFAULT NULL, 
  teneday_percent decimal(8,2) DEFAULT NULL, 
  tenday_ranking decimal(6,2) DEFAULT NULL, 
  tenday_rise_percent decimal(5,2) DEFAULT NULL, 
  guild string DEFAULT NULL, 
  code_id string DEFAULT NULL, 
  data_dt date DEFAULT NULL
)
STORED AS ORC;

然后 INSERT INTO TABLE crmdev.orc_unpart SELECT * FROM crmdev.csv_unpart;将迁移过来的外表的数据写入到内表；

INSERT INTO TABLE crmdev.orc_unpart SELECT * FROM crmdev.csv_unpart;

至此，sql 方式迁移 ORC 非分区表完成；理论上，这种方式支持所有表的迁移；

2、第二种，HDFS 方式，此种方式最简单，只需要两步：

在目的集群创建同样结构的目标表
将源表的HDFS 数据文件 copy 到目标集群的数据目录下

CREATE TABLE crmdev.orc_unpart_hdfs(
group_id int DEFAULT NULL,
code string DEFAULT NULL,
name string DEFAULT NULL,
new_price decimal(8,2) DEFAULT NULL,
main_percent decimal(8,2) DEFAULT NULL,
today_ranking decimal(8,2) DEFAULT NULL,
rise_percent decimal(6,2) DEFAULT NULL,
fiveday_percent decimal(8,2) DEFAULT NULL,
fiveday_ranking decimal(6,2) DEFAULT NULL,
fiveday_rise_percent decimal(5,2) DEFAULT NULL,
teneday_percent decimal(8,2) DEFAULT NULL,
tenday_ranking decimal(6,2) DEFAULT NULL,
tenday_rise_percent decimal(5,2) DEFAULT NULL,
guild string DEFAULT NULL,
code_id string DEFAULT NULL,
data_dt date DEFAULT NULL
)
STORED AS ORC;

将源表的HDFS 数据文件 get下来，并scp 到目标集群的数据目录下

$ hadoop fs -get /inceptor1/user/hive/warehouse/crmdev.db/tableau/orc_unpart_hdfs/
2020-04-07 14:52:40,260 INFO util.KerberosUtil: Using principal pattern: HTTP/_HOST
$ scp -r orc_unpart_hdfs/ 172.22.22.24:/mnt/disk1/crmdev/

在目标集群，将数据文件 put 到目标集群的表数据目录下

$ hadoop fs -put orc_unpart_hdfs/   hdfs://nameservice1/inceptor1/user/hive/warehouse/crmdev.db/tableau/orc_unpart_hdfs/

至此数据迁移就完成了；

3、第三种方法，Export/Import 方式

Export/Import 和第二种直接操作 HDFS 文件很像，但是有一定的区别，Export会同步导出表的元数据信息，所有的表属性/参数都将是用于EXPORT生成存档的表的属性/参数；并且再Import 的时候会根据元数据信息重新构建表结构，如果目标存在，则检查它是否具有适当的架构，Input / OutputFormat等。如果目标表存在且未分区，则它必须为空。如果目标表存在并且已分区，则表中不能存在要导入的分区。

EXPORT TABLE crmdev.orc_unpart_export TO '/crmdev/orc_unpart_export/';

可以看到有个_metadata，json 格式的，保存了表的元数据信息：

$ ll orc_unpart_export/
总用量 8
drwxr-xr-x 2 root root 4096 4月   7 16:12 data
-rw-r--r-- 1 root root 3086 4月   7 16:12 _metadata

将数据目录和文件 scp 到目标集群，并上传到 HDFS 上；

$ hadoop fs -put orc_unpart_export/ /crmdev/
2020-04-07 16:18:39,851 INFO util.KerberosUtil: Using principal pattern: HTTP/_HOST

在目标集群，执行 Import ，需要注意的是，import 语句只能写到当前数据库的表中，不支持database_name.table_name；

IMPORT如果目标表/分区不存在，它将创建目标表/分区。

USE crmdev;
IMPORT TABLE orc_unpart_export FROM '/crmdev/orc_unpart_export/';

跨大版本的Export /Import 是不支持的，实际测试，5.2 到5.2.3可行，5.2 到 6.2 不可行

2020-04-07 16:38:39,334 ERROR inceptor.InceptorDriver: (Logging.scala:logError(75)) [HiveServer2-Handler-Pool: Thread-300(SessionHandle=9d8e86f5-f7a6-4dff-a365-97791785a14f)] - COMPILE FAILED: Semantic error: [Error 40000] Exception while processing: Error in serializing metadata
org.apache.hadoop.hive.ql.parse.SemanticException: Exception while processing: Error in serializing metadata
        at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
        at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
        at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56)
        at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.json.JSONException: JSONObject["partitions"] not a string.
        at org.json.JSONObject.getString(JSONObject.java:722)
        at org.apache.hadoop.hive.ql.parse.EximUtil.readMetaData(EximUtil.java:222)
        ... 24 more