自用hive with as insert into partition等综合例子

1

create table table1 as
    with hive1 as (
            select 
                a1,
                a2,
                a3,
                a4,
                a5,
                a6,
                a7,
                a8,
                a9
            from
            (
                select
                    a1,
	                a2,
	                a3,
	                a4,
	                a5,
	                a6,
	                a7,
	                a8,
	                a9,
                    ROW_NUMBER() OVER(PARTITION BY a1, a2 ORDER BY `timestamp` desc) AS rn
                from
                    source_table1
                where 
                    partition_key=key_value
                    and conditions
            ) as b
            where 
                b.rn = 1
        ),
        hive2 as(
            select 
                get_json_object(json_string,'$.json_key') as b1,
                b2
            from source_table2
            where partition_key=key_value
                and condition
        ),
        hive3 as(
            select c1,c2,c3
            from source_table3
            where 
            partition_key=key_value
            and condition
        )
        select 
            *
        from hive1
        join hive2 on hive2.a1=hive1.b1
        join hive3 on hive3.c1=hive1.a1
;
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

• hive1使用了取最新时间戳的方法
• 使用了多个with as
• create table as
• get json object函数

2

create table if not exists table_name(
    q string,
    dd bigint,
    cc float,
)
partitioned by (q string);
1
2
3
4
5
6

• hive创建动态分区表

3

• insert into与with as连用

/*先with as， 再insert into，后select*/
with hive1 as (
        select 
            aa
        from
            aa_table,
        where partition_key=key_value
    hive2 as(
        select 
            bb
        from 
            bb_table
        where partition_key=key_value
    ),
    hive3 as(
        select cc
        from cc_table
        where partition_key=key_value
    )
insert into table target_table partition(partition_key=key_value)
select 
    *
from hive1
join hive2 on hive2.photo_id=hive1.dd
join hive3 on hive3.photo_id=hive1.dd;
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