Hive及HBase表的数据迁移

前文：

            公司最近数据迁移，做了个测试来验证执行命令。

一、Hive部分

##############################################
创建原始数据表

create table log_text_1(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
partitioned by (part string)
row format delimited fields terminated by '\t'
stored as textfile ;

create table log_text_2(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
partitioned by (part string)
row format delimited fields terminated by '\t'
stored as textfile ;

##############################################
导入数据

load data local inpath '/opt/module/datas/part1_1.txt' into table default.log_text_1 partition(part='part1_1');

load data local inpath '/opt/module/datas/part1_2.txt' into table default.log_text_1 partition(part='part1_2');

load data local inpath '/opt/module/datas/part1_3.txt' into table default.log_text_1 partition(part='part1_3');

load data local inpath '/opt/module/datas/part2_1.txt' into table default.log_text_2 partition(part='part2_1');
																					   																							   
load data local inpath '/opt/module/datas/part2_2.txt' into table default.log_text_2 partition(part='part2_2');
																					   																							   
load data local inpath '/opt/module/datas/part2_3.txt' into table default.log_text_2 partition(part='part2_3');

删除错误分区
alter table log_text_1 drop partition (part='part2_1');
查看成功分区
show partitions log_text_1;

##############################################
创建orc数据表

create table log_orc_1(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
partitioned by (part string)
row format delimited fields terminated by '\t'
stored as orc ;

create table log_orc_2(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
partitioned by (part string)
row format delimited fields terminated by '\t'
stored as orc ;

##############################################
导入orc表数据

尝试使用导入
load data local inpath '/opt/module/datas/part1_1.txt' into table default.log_orc_1 partition(part='part1_1');
load data local inpath '/opt/module/datas/part1_2.txt' into table default.log_orc_1 partition(part='part1_2');
load data local inpath '/opt/module/datas/part1_3.txt' into table default.log_orc_1 partition(part='part1_3');
load data local inpath '/opt/module/datas/part2_1.txt' into table default.log_orc_2 partition(part='part2_1');			
load data local inpath '/opt/module/datas/part2_2.txt' into table default.log_orc_2 partition(part='part2_2');			
load data local inpath '/opt/module/datas/part2_3.txt' into table default.log_orc_2 partition(part='part2_3');
错误！
原因：orc压缩格式表不能用load，也不能select

测试插入
insert overwrite  table log_orc_1 partition(part='part1_1') select * from log_text_1 where part  =  'part1_1';
成功删除
alter table log_orc_1 drop partition (part='part1_1');

from log_text_1
insert overwrite table log_orc_1 partition(part='part1_1') 
select track_time,url,session_id,referer,ip,end_user_id,city_id where part='part1_1'
insert overwrite table log_orc_1 partition(part='part1_2') 
select track_time,url,session_id,referer,ip,end_user_id,city_id where part='part1_2'
insert overwrite table log_orc_1 partition(part='part1_3') 
select track_time,url,session_id,referer,ip,end_user_id,city_id where part='part1_3';

from log_text_2
insert overwrite table log_orc_2 partition(part='part2_1') 
select track_time,url,session_id,referer,ip,end_user_id,city_id where part='part2_1'
insert overwrite table log_orc_2 partition(part='part2_2') 
select track_time,url,session_id,referer,ip,end_user_id,city_id where part='part2_2'
insert overwrite table log_orc_2 partition(part='part2_3') 
select track_time,url,session_id,referer,ip,end_user_id,city_id where part='part2_3';

##############################################
导出数据(速度很快，基本为IO流操作)

方案一：全部导出
export table default.log_orc_1 to  '/migrate/log_orc_all';

方案二：部分导出
尝试导入到同一个文件夹下
export table default.log_orc_1 partition(part='part1_1') to  '/migrate/log_orc_part'; 
上句执行成功
export table default.log_orc_1 partition(part='part1_2') to  '/migrate/log_orc_part';
执行失败：FAILED: SemanticException Line 1:13 Invalid path ''/migrate/log_orc_part'': Target is not an empty directory : hdfs://hadoop211:9000/migrate/log_orc_part
只能导出到空文件中,去除相应的操作数据
hadoop dfs -rm /migrate/log_orc_part/_metadata
hadoop dfs -rm -r/migrate/log_orc_part/part=part1_1

新方案：导出对应的文件夹中，不需先创建文件夹
export table default.log_orc_1 partition(part='part1_1') to  '/migrate/log_orc_part/part1_1';
export table default.log_orc_1 partition(part='part1_2') to  '/migrate/log_orc_part/part1_2';
export table default.log_orc_1 partition(part='part1_3') to  '/migrate/log_orc_part/part1_3';

##############################################
导入数据(速度很快，基本为IO流操作)

方案一：全部导入
import table log_orc_1_in1 from  '/migrate/log_orc_all';

方案二：部分导入
import table log_orc_1_in2 partition(part='part1_1') from  '/migrate/log_orc_part/part1_1';
import table log_orc_1_in2 partition(part='part1_2') from  '/migrate/log_orc_part/part1_2';
import table log_orc_1_in2 partition(part='part1_3') from  '/migrate/log_orc_part/part1_3';

尝试新方案导入所有分区，避免写多条sql语句
import table log_orc_1_in3 from  '/migrate/log_orc_part/';
执行失败：FAILED: SemanticException [Error 10027]: Invalid path

##############################################

二、HBase表

##############################################

创建Hbase表

临时表，用于向Hbase导入数据
CREATE TABLE emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by '\t';
向临时表导入数据
load data local inpath '/opt/module/datas/emp.txt' into table emp;

创建HBase表
CREATE TABLE hive_hbase_emp_table(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,info:ename,info:job,info:mgr,info:hiredate,info:sal,info:comm,info:deptno")
TBLPROPERTIES ("hbase.table.name" = "hbase_emp_table");

查看HBase表结构
hive> show create table hive_hbase_emp_table;
OK
CREATE TABLE `hive_hbase_emp_table`(
  `empno` int COMMENT 'from deserializer', 
  `ename` string COMMENT 'from deserializer', 
  `job` string COMMENT 'from deserializer', 
  `mgr` int COMMENT 'from deserializer', 
  `hiredate` string COMMENT 'from deserializer', 
  `sal` double COMMENT 'from deserializer', 
  `comm` double COMMENT 'from deserializer', 
  `deptno` int COMMENT 'from deserializer')
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.hbase.HBaseSerDe' 
STORED BY 
  'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
WITH SERDEPROPERTIES ( 
  'hbase.columns.mapping'=':key,info:ename,info:job,info:mgr,info:hiredate,info:sal,info:comm,info:deptno', 
  'serialization.format'='1')
TBLPROPERTIES (
  'hbase.table.name'='hbase_emp_table', 
  'transient_lastDdlTime'='1565098184')

向HBase表导入数据
insert into table hive_hbase_emp_table select * from emp;

##############################################
在HBase上导出数据

查看用法./hbase org.apache.hadoop.hbase.mapreduce.Export
./hbase org.apache.hadoop.hbase.mapreduce.Export hbase_emp_table /migrate/hbase_emp_table_all

##############################################
导入数据导HBase上

方案一：
hbase org.apache.hadoop.hbase.mapreduce.Import  hbase_emp_table_in1 /migrate/hbase_emp_table_all
执行错误：没有对应表
2019-08-06 22:43:02,690 INFO  [main] mapreduce.Job: Task Id : attempt_1564995760485_0007_m_000000_1, Status : FAILED
Error: org.apache.hadoop.hbase.client.RetriesExhaustedWithDetailsException: Failed 10 actions: Table 'hbase_emp_table_in1' was not found, got: hbase_emp_table.: 10 times, servers with issues: null, 
        at org.apache.hadoop.hbase.client.AsyncProcess$BatchErrors.makeException(AsyncProcess.java:260)
        at org.apache.hadoop.hbase.client.AsyncProcess$BatchErrors.access$2400(AsyncProcess.java:240)
        at org.apache.hadoop.hbase.client.AsyncProcess.waitForAllPreviousOpsAndReset(AsyncProcess.java:1889)
        at org.apache.hadoop.hbase.client.BufferedMutatorImpl.backgroundFlushCommits(BufferedMutatorImpl.java:247)
        at org.apache.hadoop.hbase.client.BufferedMutatorImpl.close(BufferedMutatorImpl.java:170)
        at org.apache.hadoop.hbase.mapreduce.TableOutputFormat$TableRecordWriter.close(TableOutputFormat.java:120)
        at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.close(MapTask.java:670)
        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:793)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
        at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:422)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657)
        at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)

创建一张对应HBase的Hive后再导入数据
CREATE TABLE hive_hbase_emp_table_in1(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,info:ename,info:job,info:mgr,info:hiredate,info:sal,info:comm,info:deptno")
TBLPROPERTIES ("hbase.table.name" = "hbase_emp_table_in1");

./hbase org.apache.hadoop.hbase.mapreduce.Import  hbase_emp_table_in1 /migrate/hbase_emp_table_all
执行成功！
select * from hive_hbase_emp_table_in1;
出现数据。

##############################################

方案二：采用修复方式

下载原数据
hadoop dfs -get /hbase/data/default/hbase_emp_table

drop table hive_hbase_emp_table;

上传数据
hadoop dfs -put hbase_emp_table /hbase/data/default/hbase_emp_table

./hbase hbck -fixTableOrphans hbase_emp_table
./hbase hbck -fixMeta hbase_emp_table
./hbase hbck -fixAssignments hbase_emp_table
./hbase hbck -repair hbase_emp_table
HBase中修复了相应表，查询不到数据。

创建相应的HBase表
CREATE TABLE hive_hbase_emp_table(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,info:ename,info:job,info:mgr,info:hiredate,info:sal,info:comm,info:deptno")
TBLPROPERTIES ("hbase.table.name" = "hbase_emp_table");
执行失败：
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:MetaException(message:Table hbase_emp_table already exists within HBase; use CREATE EXTERNAL TABLE instead to register it in Hive.)
该修复方案不适合于在Hive中创建的HBase表

##############################################