创建分区表，并追加数据

创建分区表

create table if not exists tuser_txt_partition 
(
　　Fguid string,
　　FID bigint,
　　FUserName string,
　　FParentID int,
　　FCompanyID int,
　　FStatus tinyint,
　　ts bigint,
　　FAddTimeDate string,
　　FAddDate string,
　　FAddTime string,
　　FIP string,
　　FThreadID smallint
)
partitioned by (pyear int)  
row format delimited 
fields terminated by '\t' 
lines terminated by '\n' 
stored as textFile;

hive> 
    > create table if not exists tuser_txt_partition (Fguid string,FID bigint,FUserName string,FParentID int,FCompanyID int,FStatus tinyint,ts bigint,FAddTimeDate string,FAddDate string,FAddTime string,FIP string,FThreadID smallint)
    > partitioned by (pyear int)
    > row format delimited fields terminated by '\t' lines terminated by '\n' 
    > stored as textFile;
OK
Time taken: 0.461 seconds
hive> 

 

追加数据

insert into tuser_txt_partition partition(pyear=2014) 
select * from tuser_copy 
where fadddate>='2014-01-01' and fadddate<'2015-01-01'; 

hive> 
    > insert into tuser_txt_partition partition(pyear=2014)
    > select * from tuser_copy 
    > where fadddate>='2014-01-01' and fadddate<'2015-01-01'; 
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20190923235829_11787aa0-7c01-4673-8e42-6b7b2e972032
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1569239441933_0007, Tracking URL = http://centos02:8088/proxy/application_1569239441933_0007/
Kill Command = /opt/bigdata/hadoop/hadoop-2.8.5/bin/hadoop job  -kill job_1569239441933_0007
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-09-23 23:59:50,711 Stage-1 map = 0%,  reduce = 0%
2019-09-24 00:00:10,732 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 4.17 sec
MapReduce Total cumulative CPU time: 4 seconds 170 msec
Ended Job = job_1569239441933_0007
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to directory hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_txt_partition/pyear=2014/.hive-staging_hive_2019-09-23_23-58-29_741_380471746446079924-1/-ext-10000
Loading data to table cashmid.tuser_txt_partition partition (pyear=2014)
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 4.17 sec   HDFS Read: 87776 HDFS Write: 1882 SUCCESS
Total MapReduce CPU Time Spent: 4 seconds 170 msec
OK
tuser_copy.fguid    tuser_copy.fid    tuser_copy.fusername    tuser_copy.fparentid    tuser_copy.fcompanyid    tuser_copy.fstatus    tuser_copy.ts    tuser_copy.faddtimedate    tuser_copy.fadddate    tuser_copy.faddtime    tuser_copy.fip    tuser_copy.fthreadid
Time taken: 107.776 seconds
hive> 

 

 

 

 

insert into tuser_txt_partition partition(pyear=2015) 
select * from tuser_copy 
where fadddate>='2015-01-01' and fadddate<'2016-01-01'; 

hive> 
    > insert into tuser_txt_partition partition(pyear=2015) 
    > select * from tuser_copy
    > where fadddate>='2015-01-01' and fadddate<'2016-01-01'; 
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20190924001449_ad73c736-0abf-4fbd-8ce6-293e963f838a
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1569239441933_0008, Tracking URL = http://centos02:8088/proxy/application_1569239441933_0008/
Kill Command = /opt/bigdata/hadoop/hadoop-2.8.5/bin/hadoop job  -kill job_1569239441933_0008
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-09-24 00:15:55,641 Stage-1 map = 0%,  reduce = 0%
2019-09-24 00:16:09,687 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 4.09 sec
MapReduce Total cumulative CPU time: 4 seconds 90 msec
Ended Job = job_1569239441933_0008
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to directory hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_txt_partition/pyear=2015/.hive-staging_hive_2019-09-24_00-14-49_833_8063445736939626875-1/-ext-10000
Loading data to table cashmid.tuser_txt_partition partition (pyear=2015)
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 4.09 sec   HDFS Read: 87776 HDFS Write: 22200 SUCCESS
Total MapReduce CPU Time Spent: 4 seconds 90 msec
OK
tuser_copy.fguid    tuser_copy.fid    tuser_copy.fusername    tuser_copy.fparentid    tuser_copy.fcompanyid    tuser_copy.fstatus    tuser_copy.ts    tuser_copy.faddtimedate    tuser_copy.fadddate    tuser_copy.faddtime    tuser_copy.fip    tuser_copy.fthreadid
Time taken: 86.613 seconds
hive> 

 

insert into tuser_txt_partition partition(pyear=2016) 
select * from tuser_copy 
where fadddate>='2016-01-01' and fadddate<'2017-01-01'; 

hive> 
    > insert into tuser_txt_partition partition(pyear=2016) 
    > select * from tuser_copy 
    > where fadddate>='2016-01-01' and fadddate<'2017-01-01'; 
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20190924001836_97cb897e-5ae7-4b80-9b06-8d01a0d26e43
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1569239441933_0009, Tracking URL = http://centos02:8088/proxy/application_1569239441933_0009/
Kill Command = /opt/bigdata/hadoop/hadoop-2.8.5/bin/hadoop job  -kill job_1569239441933_0009
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-09-24 00:19:51,766 Stage-1 map = 0%,  reduce = 0%
2019-09-24 00:20:07,707 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.79 sec
MapReduce Total cumulative CPU time: 3 seconds 790 msec
Ended Job = job_1569239441933_0009
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to directory hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_txt_partition/pyear=2016/.hive-staging_hive_2019-09-24_00-18-36_832_6467865414043963066-1/-ext-10000
Loading data to table cashmid.tuser_txt_partition partition (pyear=2016)
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 3.79 sec   HDFS Read: 87776 HDFS Write: 63743 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 790 msec
OK
tuser_copy.fguid    tuser_copy.fid    tuser_copy.fusername    tuser_copy.fparentid    tuser_copy.fcompanyid    tuser_copy.fstatus    tuser_copy.ts    tuser_copy.faddtimedate    tuser_copy.fadddate    tuser_copy.faddtime    tuser_copy.fip    tuser_copy.fthreadid
Time taken: 96.264 seconds
hive> 

 

insert into tuser_txt_partition partition(pyear=2017) 
select * from tuser_copy 
where fadddate>='2017-01-01' and fadddate<'2018-01-01'; 

hive> 
    > insert into tuser_txt_partition partition(pyear=2017) 
    > select * from tuser_copy 
    > where fadddate>='2017-01-01' and fadddate<'2018-01-01'; 
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20190924002135_4d9164ac-7920-443a-bd12-b6265ca8448d
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1569239441933_0010, Tracking URL = http://centos02:8088/proxy/application_1569239441933_0010/
Kill Command = /opt/bigdata/hadoop/hadoop-2.8.5/bin/hadoop job  -kill job_1569239441933_0010
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-09-24 00:22:43,672 Stage-1 map = 0%,  reduce = 0%
2019-09-24 00:22:54,559 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.98 sec
MapReduce Total cumulative CPU time: 2 seconds 980 msec
Ended Job = job_1569239441933_0010
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to directory hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_txt_partition/pyear=2017/.hive-staging_hive_2019-09-24_00-21-35_172_178865373385174486-1/-ext-10000
Loading data to table cashmid.tuser_txt_partition partition (pyear=2017)
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 2.98 sec   HDFS Read: 87776 HDFS Write: 64 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 980 msec
OK
tuser_copy.fguid    tuser_copy.fid    tuser_copy.fusername    tuser_copy.fparentid    tuser_copy.fcompanyid    tuser_copy.fstatus    tuser_copy.ts    tuser_copy.faddtimedate    tuser_copy.fadddate    tuser_copy.faddtime    tuser_copy.fip    tuser_copy.fthreadid
Time taken: 82.316 seconds
hive> 

 

insert into tuser_txt_partition partition(pyear=2018) 
select * from tuser_copy 
where fadddate>='2018-01-01' and fadddate<'2019-01-01'; 

hive> 
    > insert into tuser_txt_partition partition(pyear=2018) 
    > select * from tuser_copy 
    > where fadddate>='2018-01-01' and fadddate<'2019-01-01'; 
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20190924002401_0e378852-1359-4b74-b27c-289521797033
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1569239441933_0011, Tracking URL = http://centos02:8088/proxy/application_1569239441933_0011/
Kill Command = /opt/bigdata/hadoop/hadoop-2.8.5/bin/hadoop job  -kill job_1569239441933_0011
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-09-24 00:25:08,863 Stage-1 map = 0%,  reduce = 0%
2019-09-24 00:25:18,133 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.72 sec
MapReduce Total cumulative CPU time: 3 seconds 720 msec
Ended Job = job_1569239441933_0011
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to directory hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_txt_partition/pyear=2018/.hive-staging_hive_2019-09-24_00-24-01_611_2808754057693294333-1/-ext-10000
Loading data to table cashmid.tuser_txt_partition partition (pyear=2018)
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 3.72 sec   HDFS Read: 87776 HDFS Write: 64 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 720 msec
OK
tuser_copy.fguid    tuser_copy.fid    tuser_copy.fusername    tuser_copy.fparentid    tuser_copy.fcompanyid    tuser_copy.fstatus    tuser_copy.ts    tuser_copy.faddtimedate    tuser_copy.fadddate    tuser_copy.faddtime    tuser_copy.fip    tuser_copy.fthreadid
Time taken: 81.736 seconds
hive> 

 

 

select fid,fusername,fcompanyid,ts,faddtimedate,fadddate,faddtime,fip 
from tuser_txt_partition
where pyear=2016 
limit 8;