目录
2、向old_table中添加数据,新建文本文件data.txt,向其中添加三行数据,建表时的分隔符为逗号,所以我们用逗号分隔:
5、然后将使用命令hadoop fs -cp旧表数据复制到新表的hdfs目录下:
6、使用命令 MSCK REPAIR TABLE new_table刷新原数据信息:
1、创建一张表,即将要被复制的表old_table:
hive (default)> create table old_table(age bigint,height string,weight string) partitioned by(p_month int,p_day int,p_hour int)row format delimited fields terminated by ',' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' ;
OK
Time taken: 0.429 seconds
hive (default)> desc old_table;
OK
col_name data_type comment
age bigint
height string
weight string
p_month int
p_day int
p_hour int
# Partition Information
# col_name data_type comment
p_month int
p_day int
p_hour int
Time taken: 0.112 seconds, Fetched: 13 row(s)
2、向old_table中添加数据,新建文本文件data.txt,向其中添加三行数据,建表时的分隔符为逗号,所以我们用逗号分隔:
25,170,70
24,175,65
27,180,80
3、使用load命令向表中添加数据:
hive (default)> LOAD DATA LOCAL INPATH '/home/hadoop/temp/data.txt' OVERWRITE INTO TABLE old_table PARTITION (p_month='201609',p_day='20160908',p_hour='2016090800');
Loading data to table default.old_table partition (p_month=201609, p_day=20160908, p_hour=2016090800)
OK
Time taken: 3.424 seconds
hive (default)> show partitions old_table;
OK
partition
p_month=201609/p_day=20160908/p_hour=2016090800
Time taken: 0.202 seconds, Fetched: 1 row(s)
hive (default)> select * from old_table;
OK
old_table.age old_table.height old_table.weight old_table.p_month old_table.p_day old_table.p_hour
25 170 70 201609 20160908 2016090800
24 175 65 201609 20160908 2016090800
27 180 80 201609 20160908 2016090800
Time taken: 0.099 seconds, Fetched: 3 row(s)
4、复制该表结构到新表,即new_table:
hive (default)> create table new_table like old_table;
OK
Time taken: 0.099 seconds
hive (default)> desc new_table;
OK
col_name data_type comment
age bigint
height string
weight string
p_month int
p_day int
p_hour int
# Partition Information
# col_name data_type comment
p_month int
p_day int
p_hour int
Time taken: 0.093 seconds, Fetched: 13 row(s)
hive (default)> select * from new_table limit 10;
OK
new_table.age new_table.height new_table.weight new_table.p_month new_table.p_day new_table.p_hour
Time taken: 0.566 seconds
hive (default)> show partitions new_table;
OK
partition
Time taken: 0.063 seconds
5、然后将使用命令hadoop fs -cp旧表数据复制到新表的hdfs目录下:
[hadoop@node1 ~]$ hadoop fs -cp /user/hive/warehouse/old_table/* /user/hive/warehouse/new_table/
6、使用命令 MSCK REPAIR TABLE new_table刷新原数据信息:
hive (default)> MSCK REPAIR TABLE new_table;
OK
Partitions not in metastore: new_table:p_month=201609/p_day=20160908/p_hour=2016090800
Repair: Added partition to metastore new_table:p_month=201609/p_day=20160908/p_hour=2016090800
Time taken: 0.447 seconds, Fetched: 2 row(s)
7、然后查询可看到new_table的数据:
hive (default)> select * from new_table;
OK
new_table.age new_table.height new_table.weight new_table.p_month new_table.p_day new_table.p_hour
25 170 70 201609 20160908 2016090800
24 175 65 201609 20160908 2016090800
27 180 80 201609 20160908 2016090800
Time taken: 0.5 seconds, Fetched: 3 row(s)