hive语法

 select .. from .. join [tableName] on .. where .. group by .. having .. order by .. sort by .. limit .. union | union all ...

 第一步: FROM <left_table> 第二步: ON <join_condition> 第三步: <join_type> JOIN <right_table> 第四步: WHERE <where_condition> 第五步: GROUP BY <group_by_list> 第六步: HAVING <having_condition> 第七步: SELECT 第八步: DISTINCT <select_list> 第九步: ORDER BY <order_by_condition> 第十步: LIMIT <limit_number>  标准sql语句的一些规则： -1. 列别名的使用，必须完全符合执行顺序，不能提前使用。（mysql除外） -2. 在分组查询时，select子句中只能含有分组字段和聚合函数，不能有其他普通字段。(mysql除外)

 1. 尽量不使用子查询、尽量不使用in 或者not in (可以使用 [not] exists替代) 2. 尽量避免join连接查询，但是通常避免不了 3. 查询永远是小表驱动大表（小表作为驱动表）  --注意：内连接时，默认是左表是驱动表，因此左表一定要是小表。  -- 外连接看需求而定。

 where后不能使用聚合函数，可以使用子查询，也可以是普通函数。 条件可以是： 1. 关系表达式：=, >,>=,<,<=,!=,<> 2. 连接符号：or，and, between .. and .. 3. 模糊查询：like    %:通配符  _:占位符 4. [not] in    >all(set) >any();  注意事项：在hive的where中如果使用了子查询作为条件，等号“=”不好使，需要使用[not] in. 换句话说，即使子查询返回的是唯一的一个值，也是集合形式。

 group by: 分组，通常和聚合函数搭配使用  查询的字段要么出现在group by 后面，要么出现在聚合函数里面  聚合函数：count(),sum(),max(),min(),avg()  count的执行 1. 执行效果上： - count(*)包括了所有的列，相当于行数，在统计结果的时候不会忽略null值 - count(1)包括了所有列，用1代表行，在统计结果的时候也不会忽略null值 - count(列名)只包括列名那一列，在统计结果时，会忽略null值  2.执行效率上： - 列名为主键，count(列名)会比count(1)快 - 列名不为主键，count(1)会比count(列名)快 - 如果表中有多个列并且没有主键，count（1）的效率高于count(*) - 如果有主键count(主键)效率是最高的 - 如果表中只有一个字段count(*)效率最高

 对分组以后的结果集进行过滤。可以使用聚合函数。

 对查询的数据进行排序。 desc 降序 asc 升序  语法： order by colName [desc|asc][,colName [desc|asc]]

 limit ：从结果集中取数据的条数 将set hive.limit.optimize.enable=true 时，limit限制数据时就不会全盘扫描，而是根据限制的数量进行抽样。  同时还有两个配置项需要注意： hive.limit.row.max.size       这个是控制最大的抽样数量 hive.limit.optimize.limit.file 这个是抽样的最大文件数量   注意：limit 在mysql中 可以有两个参数 limit [m,] n        在hive中，只能有一个参数 limit n; 查询前n条。  一般情况下，在使用limit时，都会先order by排序。

 union all：将两个或者多个查询的结果集合并到一起。不去重 union：将两个或者多个查询的结果集合并到一起，去重合并后的数据并排序 union：语句字段的个数要求相同，字段的顺序要求相同。

 1. 内连接: [inner] join 2. 外连接 (outer join)：（引出一个驱动表的概念：驱动表里的数据全部显示)  - 左外连接:left [outer] join, 左表是驱动表  - 右外连接:right [outer] join, 右表是驱动表  - 全外连接:full [outer] join, hive支持，mysql不支持.两张表里的数据全部显示出来 3. 注意：join连接只支持等值连接

 准备数据 u1文件中的数据如下： 1,a 2,b 3,c 4,d 7,y 8,u  u2文件中的数据如下： 2,bb 3,cc 7,yy 9,pp  create table if not exists u1( id int, name string ) row format delimited fields terminated by ',' ;  create table if not exists u2( id int, name string ) row format delimited fields terminated by ',' ;  load data local inpath './data/u1.txt' into table u1; load data local inpath './data/u2.txt' into table u2;

 练习:   查询有领导的员工信息   select * from emp where mgr is not null   select * from emp A where exists (select 1 from emp B where B.empno = A.mgr )   select * from emp A left semi join emp B where A.mgr = B.empno;    查询有下属的员工信息       select * from emp A where exists (select 1 from emp B where B.mgr = A.empno )        查看有部门的所有员工的信息       select * from emp A where exists (select 1 from dept B where B.deptno = A.deptno )

-- 左外连接，左表中的数据全部返回 select * from u1 left join u2 on u1.id =u2.id; select * from u1 left outer join u2 on u1.id =u2.id; -- 左半开连接，只显示左表中满足条件的数据。和exists的逻辑是相同的 select * from u1 left semi join u2 on u1.id =u2.id; -- exists的写法 select * from u1 where exists (select 1 from u2 where u2.id =u1.id);  --验证left semi join 是否可以显示第二张表的信息：错误写法。 select A.*, B.* from u1 A left semi join u2 B on A.id =B.id;

 property.hive.log.dir = ${sys:java.io.tmpdir}/${sys:user.name} property.hive.log.file = hive.log

 1. 本地模式下使用hive进行client 2. 远程模式下使用beeline工具进入client 3. 远程模式下使用hive进入client

 [root@hadoop01 ~]$ hive -e 'hql query'  案例： [root@hadoop01 ~]$ hive --database mydb -e 'select * from studentinfo'; [root@hadoop01 ~]$ hive --database exercise --hivevar ls=2 --hiveconf tn=student -e 'select * from ${hiveconf:tn} limit ${hivevar:ls}';   参数选项说明: -e用于执行hql语句 -f 用于执行sql脚本文件 -S 静音模式，不显示mapreduce执行过程 -i 启动hive时初始化一个文件

 [root@hadoop01 ~]$ hive -f 'sql script'  案例： [root@hadoop01 ~]$ vi hfile.sql use exercise; select count(*) from student where s_id<5; [root@hadoop01 ~]$ hive -f ./hfile.sql或者使用静音模式 [root@hadoop01 ~]$ hive -S -f ./hfile.sql