hivesqlin
⑴ hive sql 怎么不能这样写啊 （mysql 是没有问题的）
你可以先用hive或者手写map/rece程序。建议你直接使用hive，hive也像mysql一样可以建表。表里的数据就是对应从本地load到hadoop上的文件，格式自己定。然后用JDBC可以像操作mysql一样操作hive（sql语法稍有不同）
⑵ Hive的left join，left outer join和left semi join三者的区别
贴一直复制过来的。。hive不支持‘left join’的写法（我记得使用的时候好像报错了，最后都改成了outer join）hive的left outer join：如果右边有多行和左边表对应，就每一行都映射输出；如果右边没有行与左边行对应，就输出左边行，右边表字段为NULL；hive的left semi join：相当于SQL的in语句，比如上面测试3的语句相当于“select * from table1 where table1.student_no in (table2.student_no)”，注意，结果中是没有B表的字段的。
⑶ hive sql里，帮我描述一个简单的sql的原理
select a.id,a.info,b.num from a join b on a.id=b.id and where b.num>=10
两个表做关联，首先where会过滤掉不需要的数据。
至于表怎么做map和rece操作，在hive里的表是虚拟的，其实还是对hdfs文件进行操作，你可以在hdfs:///user/hive/warehouse路径下找到以表名来命名的文件，里面就是表的内容，可以执行-cat命令查看。所以，它的map操作很简单，就是按行读文件，然后会根据hive的默认分隔符\001对每行进行切分。切分完成后就会按照你SQL指定的逻辑进行合并，最后再输出成hdfs文件，只不过在hive里面看它是以表的形式展现的。
job数会在你执行sql语句之后紧接着有相应的日志记录，
Total MapRece jobs = 2
Launching Job 1 out of 2
Number of rece tasks not specified. Estimated from input data size: 2
In order to change the average load for a recer (in bytes):
set hive.exec.recers.bytes.per.recer=<number>
In order to limit the maximum number of recers:
set hive.exec.recers.max=<number>
In order to set a constant number of recers:
这样就是有两个job，正在执行第一个job。
Hadoop job information for Stage-1: number of mappers: 5; number of recers: 2
而这个就会告诉你有多少个mapper和recer。
像你写的这个sql有join操作，而且是hiveSQL里面最普通的join，那么一定会有recer参与，如果数据量很大，比如上千万条记录，join就会特别慢，job进度就会一直卡在rece操作。可以改成mapjoin或者sort merge bucket mapjoin。
其实hive效率不高，不适合实时查询，即使一个表为空，用hive进行查询也会很耗时，因为它要把sql语句翻译成MR任务。虽然简化了分布式编程，但是效率上就会付出代价。
你的这句sql应该会翻译成一个JOB来执行，就是简单地map和rece。
maprece就是按行读文件，然后切分，合并，输出成文件。
⑷ 如何看hive中哪个队列比较闲
解决方案
MapRece运行队列的指定是通过配置（Configuration）属性“maprece.job.queuename”指定的。
大家可能首先想到的是通过“set maprece.job.queuename=queueName”的方式来选取运行队列，这在手动任务（临时分析需求）的场景下是不可取的，如前所述，我们为这类似的任务专门分配了相应的队列资源“hive.temporary”，我们必须能够保证用户通过Beeline连接HiveServer2后提交的Hive SQL语句运行在指定的队列“hive.temporary”中，而且用户无法随意更改运行队列，即无法随意更改属性“maprece.job.queuename”。
目前HiveServer2使用的权限控制策略为SQL Standard Based Hive Authorization和Storage Based Authorization in the Metastore Server。其中SQL Standard Based Hive Authorization会对Hive终端命令“set”做出限制：只能针对白名单（hive.security.authorization.sqlstd.confwhitelist）中列出的属性进行赋值。白名单默认包含一批属性，其中就包括“maprece.job.queuename”，我们需要通过配置文件hive-site.xml或者启动HiveServer2时通过参数“--hiveconf”设置白名单“hive.security.authorization.sqlstd.confwhitelist”的值，排除属性“maprece.job.queuename”，使得我们的用户通过Beeline连接至HiveServer2之后，无法随意更改“maprece.job.queuename”的值。
既然用户无法更改属性“maprece.job.queuename”，那么HiveServer2启动之后属性“maprece.job.queuename”必须具有一个默认值，即“hive.temporary”，这样用户通过Beeline连接HiveServer2之后提交的Hive SQL就会运行在队列“hive.temporary”中。那么，接下来的问题就是如果完成这个默认设定？
一般情况下，我们会这样认为，HiveServer2的运行至少涉及到两份配置文件：
（1）Hadoop：core-site.xml、hdfs-site.xml、mapred-site.xml、yarn-site.xml
（2）Hive：hive-site.xml
这些配置文件中的属性值都会“打包”到MapRece任务的配置属性中去。我们自然会想到在mapred-site.xml或者hive-site.xml中指定“maprece.job.queuename”即可，然而实际验证之后发现情况并不是这样的。
（1）在hive-site.xml（mapred-site.xml）中指定“maprece.job.queuename”；
⑸ hive join支持using么
hive支持’left join’写； hiveleft outer join：右边行左边表应,每行都映射输；右边没行与左边行应,输左边行,右边表字段NULL； hiveleft semi join：相于SQLin语句 两测试数据表建表语句...
⑹ hive not in 通过什么实现
目前hive不支持 in或not in 中包含查询子句的语法，所以只能通过left join实现。
假设有一个登陆表login(当天登陆记录,只有一个uid),和一个用户注册表regusers(当天注册用户，字段只有一个uid)，这两个表都包含一个字段,uid。
in查询
如果要查询当天登陆的注册用户，需要用in查询，hive sql如下:
select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is not null
如果login表和regusers表按天分区，字段是dt，那么查询2013年1月1号当天登陆的注册用户，hive sql如下:
select login.uid from login day_login left outer join
(select uid from regusers where dt='20130101') day_regusers
on day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is not null
not in查询
如果要查询当天登陆的老用户(这里假设非当天注册用户就是老用户)，需要用not in查询，hive sql如下:
select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is null;
如果login表和regusers表按天分区，字段是dt，那么查询2013年1月1号当天登陆的老用户，hive sql如下:
select login.uid from login day_login left outer join
(select uid from regusers where dt='20130101') day_regusers
on day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is null;
Hive join优化
========================================================
由
于 hive 与传统关系型数据库面对的业务场景及底层技术架构都有着很大差异，因此，传统数据库领域的一些技能放到 Hive 中可能已不再适用。关于
hive 的优化与原理、应用的文章，前面也陆陆续续的介绍了一些，但大多都偏向理论层面，本文就介绍一个实例，从实例中一步步加深对 hive
调优的认识与意识。
1、需求
需求我做了简化，很简单，两张表做个 join，求指定城市，每天的 pv，用传统的 RDBMS SQL 写出来就这样的：
?
1
2
3
4
5
6
7
8
9
10
11
SELECT t.statdate,
c.cname,
count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON (t.area1= c.cname
OR t.area2 =c.cname
OR t.area3 = c.cname)
WHERE t.statdate>='20140818' and t.statdate<='20140824'
AND platform='pc'
GROUP BY t.statdate,
c.cname;
怎么样？根据 SQL 看懂需求没问题吧？
2、非等值 join 问题
然后把这条 SQL 贴到 hive 中去执行，然后你会发现报错了：
?
1
FAILED: SemanticException [Error 10019]: Line 5:32 OR not supported in JOIN currently 'cname'
这是因为 hive 受限于 MapRece 算法模型，只支持 equi-joins（等值 join），要实现上述的非等值 join，你可以采用笛卡儿积（ full Cartesian proct ）来实现：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT t.statdate,
c.cname,
count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
AND (t.area1= c.cname
OR t.area2 =c.cname
OR t.area3 = c.cname)
GROUP BY t.statdate,
c.cname;
然后再拿着这条语句执行下。
3、优化：rece side join VS Cartesian proct
如果你真的把这条语句放到 Hive 上执行，然后恰好你有张表还非常大，那么恭喜你。。。集群管理员估计会找你的麻烦了。。。
友情提示：笛卡儿积这种语句在 Hive 下慎用，大数据场景下的 m * n 映射结果你懂的。。。对此，Hive 特意提供了一个环境变量：hive.mapred.mode=strict; 防止笛卡儿积的执行：
?
1
FAILED: SemanticException [Error 10052]: In strict mode, cartesian proct is not allowed. If you really want to perform the operation, set hive.mapred.mode=nonstrict
从 2 中的观察得知我们在 on 后面跟 join
条件，走的是 rece side join，如果你在 where 后跟则是走 Cartesian proct，但是这里单条 sql
又没法实现 rece side join，还有没有其它办法呢？
4、改写非等值 join：union all
既然不允许非等值 join，那我们换一下思路，多个子查询 union all，然后汇总：
?
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
SELECT dt,
name,
count(cid)
FROM
(SELECT t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc') tmp_trackflow
GROUP BY dt,
name;
5、优化：map side join
上述语句走的是 rece side join，从我们的需求及业务得知，tmpdb.city 是一张字典表，数据量很小，因此我们可以试试把上述的语句改写成 mapjoin：
?
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
SELECT dt,
name,
count(cid)
FROM
(SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc') tmp_trackflow
GROUP BY dt,
name;
6、优化无极限：开启 parallel 和 控制 rece 个数
上述语句执行时，你可以看到执行计划和状态信息，以及结合你的 union all 语句可知，三个 union 语句之间没有依赖关系，其实是可以并行执行的：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
explain SQL...
...
STAGE DEPENDENCIES:
Stage-11 is a root stage
Stage-1 depends on stages: Stage-11
Stage-2 depends on stages: Stage-1
Stage-3 depends on stages: Stage-2, Stage-6, Stage-9
Stage-12 is a root stage
Stage-5 depends on stages: Stage-12
Stage-6 depends on stages: Stage-5
Stage-13 is a root stage
Stage-8 depends on stages: Stage-13
Stage-9 depends on stages: Stage-8
Stage-0 is a root stage
...
我们在 SQL 前加上如下环境变量选项：
?
1
2
set mapred.rece.tasks=60;
set hive.exec.parallel=true;
让执行计划中的 Stage-11、Stage-12、Stage-13 并行执行，并控制好 rece task 个数。
完整的语句如下：
?
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
hive -e "
SET mapred.rece.tasks=60;
SET hive.exec.parallel=TRUE;
SELECT dt,
name,
count(cid)
FROM
(SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc') tmp_trackflow
GROUP BY dt,
name;
" > a1.txt
最后的优化效果是：2 中的语句三个小时没出结果。。。5 比 4 快 8 倍左右，6 比 5 快 2 倍左右，最终 10min 出结果。
⑺ hiveContext读取hive非分区表不报错，读取hive分区表报null指针异常
首先要从你的分区表结构开始查起，看是否符合你要分区的条件，这个错误报的很泛，并不能完全定位出问题，只能根据有可能会影响的地方出手一点点排除，查找问题了。可以在stickoverflow，或者官方论坛中去找找线索了
⑻ 如何每日增量加载数据到Hive分区表
加载数据
数据加载到Hive分区表（两个分区，日期（20160316）和小时（10））中
每日加载前一天的日志文件数据到表db_track.track_log
1. 数据存储
数据日志文件，放入某个目录下,每天日志文件放入同一个目录
eg: 20160316 - 目录名称
日志文件数据，每个小时生成一个文件，一天总共有二十四个文件
eg: 2016031820
2. shell脚本编写
负责调度的shell脚本load_tracklogs.sh
注：这里涉及到了两个点：1)for循环 2) linux下字符串的截取${line:0:4} 3) 传递参数到hive的sql脚本
#!/bin/sh
## 环境变量生效
. /etc/profile
## HIVE HOME
HIVE_HOME=/opt/cdh5.3.6/hive-0.13.1-cdh5.3.6
## 日志目录
LOG_DIR=/data/tracklogs
## 目录名称, 依据日期date获取
yesterday=`date -d -1days '+%Y%m%d'`
###
for line in `ls $LOG_DIR/${yesterday}`
do
echo "loading $line .............."
#从文件名称中解析出日期和小时
daily=${line:0:4}${line:4:2}${line:6:2}
hour=${line:8:2}
LOAD_FILE=${LOG_DIR}/${yesterday}/${line}
### echo $daily + $hour
### ${HIVE_HOME}/bin/hive -e "LOAD DATA LOCAL INPATH '${LOAD_FILE}' OVERWRITE INTO TABLE db_track.track_log PARTITION(date = '${daily}', hour = '${hour}') ;"
${HIVE_HOME}/bin/hive --hiveconf LOAD_FILE_PARAM=${LOAD_FILE} --hiveconf daily_param=${daily} --hiveconf hour_param=${hour} -f /home/hadoop/load_data.sql
done
负责加载数据的sql脚本
注: 怎么在HQL脚本中获取外接传递的参数
LOAD DATA LOCAL INPATH '${hiveconf:LOAD_FILE_PARAM}' OVERWRITE INTO TABLE db_track.track_log PARTITION(date = '${hiveconf:daily_param}', hour = '${hiveconf:hour_param}') ;
制定每天定时执行
可以在当前用户下直接创建：crontab -e
注：crontab中的五个 *号分别代表分，时，日，月，周，下面的例子就是每天晚上1点30运行任务的例子,注意sh命令前一般需要加上绝对路径
# LODAD DATA INTO TRACK_LOG
30 1 * * * /bin/sh /home/hadoop/load_tracklogs.sh