Hive的trick和坑

1. Hive直接插入数据

我们知道Hive在很多时候很好用,但是有时在做一些测试的时候,需要表里有一些数据,这个时候如果再导来到去或者再写源文件生成数据却是非常的麻烦,经我反复揣摩终于发现了一种直接往Hive表中插入数据的方法。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INSERT INTO TABLE target_table
SELECT STACK(
2, # 插入记录的总数
'John', 80, # 记录1
'Bill', 61 # 记录2
)
FROM dual # 任意一张已经存在的表(表中的记录条数>=1即可)
LIMIT 2; # 插入记录的总数

2. join有天坑

Full outer join

Full outer join 在on中过滤分区有问题,解决方法是将分区过滤条件放到左表和右表子查询里面
比如:

# 错误写法
Select a.x,b.x from a full|left|right outer join b on (a.key=b.key and a.ds=d1 and b.ds=d1)


# 正确写法
Select t1.x ,t2.x from (select * from a where ds=d1) t1
full outer join
 (select * from  b where ds=d1) t2 on t1.key=t2.key

Left/Right Outer Join

Left/Right Outer Join 将驱动表(left outer join的左表或者right outer join的右表)的分区过滤放在on会有问题,解决方法是将驱动表的过滤条件放到where或者子查询里面,如:

# 错误写法
Select a.x,b.x from a left outer join b on (a.key=b.key and a.ds=d1 and b.ds=d1)
# 正确写法
Select a.x ,b.x  from a
left outer join b on a.key=b.key  and b. ds=d1 and b.x=y
where a.ds=d1
或者
Select t1.x ,t2.x from (select * from a where ds=d1) t1
Left outer join b t2 on t1.key=t2.key and t2.ds=d1

多表连接

正确的写法:

# 错误写法
SELECT a.val, b.val, c.val FROM a JOIN b JOIN c ON (a.key = b.key1 and c.key = b.key2)



# 正确写法
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

内连接注意事项

正确的方法是使用 a join b on a.key=b.key,
下面写法在mysql/oracle等价于内连接,但是在Hive中会导致笛卡尔积,会跑不出来的
From a ,b where a.key=b.key