分类： 大数据
2013-11-19 09:34:45
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
方法一(union all + row_number()over )：
insert overwrite table limao_store
select p_key,sort_word
from ( select tmp1.*, row_number() over(distribute by sort_word sort by p_key desc) rownum
from ( select *
from limao_store
union all
select *
from limao_incre
) tmp1
) hh
where hh.rownum = 1;
分析， 长表排序
方法二(left outer join + union all)：
注意： hive 不支持 顶层 union all ,而且union all 结果必须有别名
insert overwrite table limao_store
select t.p_key,t.sort_word from (
select s.p_key,s.sort_word from limao_store s left outer join limao_incre i on(s.p_key=i.p_key) where i.p_key=null
union all
select p_key,sort_word from limao_incre);
分析： 不能识别 incre中的重复数据 长表关联 ， 表宽度加倍
方法三（left outer join + insert into）
insert overwrite table store
select s.* from store s left outer join incre i on(s.p_key=i.p_key) where i.p_key=null
insert into table jm_g_l_cust_secu_acct
select * from jm_g_l_cust_secu_acct_tmp;
分析： insert into 最好不用。 使用insert into 在hdfs中的表现为，在表（分区）文件夹下，建立新的文件
存放insert into数据， 造成文件碎片，降低以后该表查询效率。
==================================================================================
use nets_life;
create table limao_store
(
p_key string,
sort_word string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
create table limao_incre
(
p_key string,
sort_word string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
建表语句
use nets_life;
create table limao_store
(
p_key string,
sort_word string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
create table limao_incre
(
p_key string,
sort_word string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
=====================================================
================================================================================================
|
