hive sql 注意事项

如果是按时间分区的表，查询时一定要使用分区限制，如果没有分区限制，会从该表的所有数据里面遍历。

 

注意sql中or的使用，or 这个逻辑必须单独括起来，否则可能引起无分区限制，下面举个例子，想查询到的是gd或gx的某天的数据。

Select x from t where ds=d1 and province=’gd’ or province=’gx’

该语句会从所有的分区里面查询！也没有得到自己想要的数据！这里的意思是某天gd的或者所有日期里面gx的数据 ，正确的写法是：

Select x from t where ds=d1 and (province=’gd’ or province=’gx’)

 

很容易出错的地方

1、  只支持等值连接，不支持非等值连接

2、  内连接时小表放前面，大表放后面 a Join b on  a.x=b.x  a为小表

3、  连接小表时使用map join 条件

SELECT /*+ MAPJOIN(b) */ a.key, a.value  FROM a join b on a.key = b.key

l   小表可以放进内存（维度表、行数<2w 为宜）

l   小表不能为驱动表 (eg:left outer join 时的左表)

4、  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

 

5、  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

 

6、  支持多表连接，语法：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

         下面写法错误：

SELECT a.val, b.val, c.val FROM a JOIN b JOIN c ON (a.key = b.key1 and c.key = b.key2)

 

7、  内连接注意事项

要使用 a join b on a.key=b.key，

下面写法在mysql/oracle等价于内连接，但是在Hive中会导致笛卡尔积，会跑不出来的

From a ,b where a.key=b.key

 

8、  join的两个表中有一个表应该是distinct的，但如果实际的数据并非distinct的，这个时候应该做一次select distinct放到子查询，再join

9.hive sql中匹配 ‘\’，竟然用四个\,如：channel not like '%\\\\'

10.设置执行sql的内存：

set mapreduce.map.memory.mb=8192;
set mapred.map.child.java.opts=-Xmx7792m;
set mapreduce.reduce.memory.mb=8192;
set mapred.reduce.child.java.opts=-Xmx7792m;