hive3.0.0操作集锦(下)

4.查询

   (1)select...from语句

select name,salary,subordinates[0],deductions['State Taxes'],address.state,
salary*(1-deductions['Federal Taxes'])
from employee;

   使用函数：https://www.cnblogs.com/MOBIN/p/5618747.html

   limit语句：select * from employee limit 2;用于限定返回的行数。

   列别名：select name,salary*(1-deductions['Federal Taxes']) as salary_minus from employee;很多使用函数或者其它语句产生的新值没有变量名称，列别名易于引用没有变量名称的值。

   嵌套select语句：

from (
   select upper(name) as up_name,deductions['Federal Taxes'] fed_taxes,
   round(salary*(1-deductions['Federal Taxes'])) salary_minus
   from employee
   ) e
select e.up_name,e.fed_taxes,e.salary_minus
where e.salary_minus>70000;

嵌套语句避免了中间数据的存储，如需多个步骤产生结果，不需每查询一次产生一个新表，再通过新表进行下一个步骤。

  case...when...then句式：Hiveql中的if语句。

select name,salary,
        case
          when salary<50000.0 then 'low'
          when salary>=50000.0 and salary<70000.0 then 'middle'
          when salary>=70000.0 and salary<100000.0 then 'high'
          else 'very high'
        end as bracket
from employee;

     (2)where语句:

select * from employee 
where country='US' and state='IL';

 where条件中不可以出现列别名,如下面where中带有列别名会报错：

select name,salary*3 as s,address.state
from employee
where s>30000.0;

正确的方式是：

select name,salary*3 as s,address.state
from employee
where salary*3>30000.0;

 或者使用select嵌套:

from (
select name,salary*3 as s,address.state
from employee
) e
select * 
where e.s>30000.0;

        LIKE和RLIKE:select * from employee where name like '%J%';返回所有name column含有'J'的行。

                                select * from employee where name rlike '.*(J|G).*';返回和上面一样。这个是Java的正则表达式，点号(.)表示和任意字符匹配,(*)表示重复左边字符串0次到无数次,(|)表示或者，即和J或者G匹配。

      (3)group by语句:

select name,sum(salary),address.state
from employee
group by name,address.state;

group by通常会和聚合函数一起使用,select查询的列，要么用聚合函数，要么加入分组条件中，确保每个分组最终结果只有一条数据。

        having语句：

select name,avg(salary) a,address.state
from employee
group by name,address.state
having a>10000.0;

having是group by之后的选择，如不使用，也可使用嵌套select:

from (
select name,avg(salary) a,address.state
from employee
group by name,address.state
) e
select *
where e.a>10000.0;

      (4)join语句：

            内连接(inner join):

select a.name,a.address.state,b.address.state
from employee a join employee b on a.name=b.name
where a.name='John Doe';

内连接(inner join)中，只有进行连接的两个表中都存在与连接标准相匹配的数据才会保留下来。连接标准就是join...on....中on后面的判断条件。join中，是先连接形成select查询的数据集，再where语句判断来进行筛选。

还可以多个表进行连接：

select a.name,a.address.state,b.address.state,c.address.state
from employee a join employee b on a.name=b.name 
                join employee c on b.name=c.name  or b.salary>c.salary
where a.name='John Doe';

在多个表进行内连接中，从左到右，Hive先启动一个MapReduce让a表和b表进行连接，判断连接标准形成数据集，然后再启动一个MapReduce让这个数据集和c表进行连接，判断连接标准形成最终数据集。

            join优化：

select /*+streamtable(a)*/a.name,a.address.state,b.address.state,c.address.state
from employee a join employee b on a.name=b.name 
                join employee c on a.name=c.name 
where a.name='John Doe';

Hive会假定查询中最后一个表是最大的那个表。在对每行记录进行连接操作时，它会尝试将其他表缓存起来，然后扫描最后那个表进行计算。因此，用户需要保证连续查询中的表的大小从左到右是依次增加的（《Hive编程指南》原句，测试数据量小看不出来，而且我不能表述得比这个更加清楚，意思是我的内心是接受的）。也可以通过"/*+streamtable(s)*/"标记那张大表，这样Hive就不会先缓存他了。

如果每个on子句都使用相同的连接键(a.name)，那么只会产生一个MapReduce job。

           left outer join:

select a.name,b.name,a.salary,b.salary
from employee a left outer join employee b on b.address.state='CL';

左外连接，无论on子句什么条件(即使是筛选，如：a.name='John',a表没有这条数据)，左边表的内容(如：a.name,a.salary)都会出现在查询结果中，只不过查询结果中对应b表的内容(b.name,b.salary)会由NULL代替。但对右边的b表就没有这样的规则，可以在on子句中对其进行筛选，但如果筛选结果没有匹配on子句的，和上面那条规则结果一样，查询出a表所有的name,salary列数据，只不过查询中b表的内容会由NULL替代。

           outer join:

select a.name,b.name,a.salary,b.salary
from employee a full outer join employee b on b.address.state=a.address.state;

左外连接在where中对a表进行筛选则不会出现b表位置被NULL代替的情况。这种方式并非让人很满意，可能会想知道是否可以将where语句中的内容放到on语句里。幸运的是，有一个适用于所有种类连接的解决办法，那就是使用嵌套select语句：

select a.name,b.name,a.salary,b.salary
from (select * from employee where address.state='CL' )a 
left outer join 
(select * from employee where address.state='CL')b; 

虽然还是没有完全摆脱where语句，但这也是一种方法。

          right outer join:

select a.name,b.name,a.salary,b.salary
from employee a right outer join employee b on a.address.state='CL';

右外连接跟左外连接刚好相反，只能对左边的表在on子句中进行筛选。 

          full outer join:

select a.name,b.name,a.salary,b.salary
from employee a full outer join employee b on b.address.state='HL';

完全外连接左右表对应查询的列(a.name,b.name,a.salary,b.salary)都会出现在结果中，只不过如果左右表没有匹配的，查询出的行将是左右表数据行之和，对应表列位置由NULL代替。

         left semi join:

select a.name,a.salary
from employee a left semi join employee b on 
b.address.state=a.address.state and b.address.state='CL';     --这一条做法很蠢，但为了演示。;

左半开连接会返回左边表的记录，但select和where语句中不能引用到右边表中的字段。Hive不支持右半开连接(right semi join)。semi-join比通常的内连接(inner join)要更高效，因为：对于左表中的一条数据，在右表中一旦找到匹配的记录，Hive就会立即停止扫描。从这点看，左表中选择的列是可以预测的。

        map-side join:这个优化是通过set hive.auto.convert.join=true;，现在默认配置这个选项的值为true。将连接中数据量小的表加载到内存中，减少map和reduce过程的执行步骤。

         (5)order by 和sort by:排序，asc为降序(缺省值)，desc为升序

order by:对查询的结果进行全局排序，如果数据量较大，可能查询时间会比较长，因为所有的数据都会通过一个reducer进行处理。

sort by:对每一个reducer内进行数据排序，如reducer超过一个，可能会出现数据重叠。

select e.* from employee e
order by e.name asc,e.salary desc;
--sort by e.name desc,e.salary asc;

        (6)含有sort by的distribute by:只在reducer内进行排序的sort by看起来毫无用处，但当加上可以控制reducer内数据的distribute by语句后，一切都开始变好起来。

distribute by会把要求的列放到一个reducer中，结果看上去你可能会觉得这是group by同父异母的兄弟。

select e.* from employee e
distribute by e.name
sort by e.name asc,e.salary desc;

 结果会把name相同的数据放到一个reducer中，然后再用sort by在reducer中排序。

       (7)cluster by:如果distribute by和sort by涉及的列相同，而且采用的是升序排序方式，cluster by相当于前面2个句子的简写模式。

select e.name,e.salary from employee e
cluster by e.name,e.salary;

不可以在列后修改排列方式，只可以默认的升序。

使用distribute by.......sort by或其简化版cluster by语句会剥夺sort by的并行性，然而这样可以实现输出的数据是全局排列的。

      (8)类型转换：当需要指定的类型或者相同的类型进行比较的时候，可以使用cast()进行类型转换。

select * from employee
where cast(salary as float)<10000.0;

当不可以进行直接转换的时候，可以进行嵌套转换。如b为binary型，转为double型：

select cast(cast(b as string) as double) from src

      (9)数据块抽样：按照百分比进行抽样。

select * from employee tablesample(1 percent) e;

      (10)union all:可以将两个表或多个表进行合并查询。

select * from (
select name,salary from employee where salary >10000.0
union all
select name,salary from employee where salary <5000.0
) as e;

需要注意的是，连接的两张表选择的列不仅数量要相同，对应的类型也必须相同，如第二个为float,对应另一张表的第二个也必须为float。还有，像上面嵌套查询，结尾的as e必须带上。

 

 

5.视图:    支持array,map,struct的元素查询。order by,sort by等排序好像没什么效果。

 

      (1)使用视图降低查询复杂度：可以使用视图将查询语句分割成多个小的，更可控的片段。

from (
select name,avg(salary) a,address.state
from employee
group by name,address.state
) e
select *
where e.a>10000.0;

将嵌套子句变成视图：

create view view_test as
select name,avg(salary) a,address.state
from employee
group by name,address.state;

这样就大大简化了之前的查询：

select * from view_test
where a>10000.0;

     (2)使用视图来限制基于条件过滤的数据：对于视图来说一个常见的使用场景就是基于一个或多个列的值来限制输出结果。有些传统数据库允许将视图作为一个安全机制，也就是不给用户直接访问具有敏感数据的原始表，而是提供给用户一个通过where子句限制了的视图，以供访问。Hive目前并不支持这个功能，因为用户必须具有能够访问整个底层原始表的权限，这时视图才能工作。然而，通过创建视图来限制数据访问可以用来保护信息不被随意查询：

create table userinfo(
username string,
userId string,
password string);

create view safer_user_info as
select firstname,lastname,userId
from userinfo;

    (3)视图零零碎碎的东西：

         Hive会先解析视图，然后使用解析结果再来解析整个查询语句。然而，作为Hive查询优化器的一部分，查询语句和视图语句可能会合并成一个单一的实际查询语句。

         可以使用describe 来查看视图的内容(formatted,extended等)，如果查询了新的列，未给新列命名，会使用cN_(N为数字，从0开始)来代替。

         定义一个视图实际上不会“具体化”操作任何数据，所以视图实际上是对其所使用到的表和列的一个查询语句固化过程。因此,如果视图所涉及的表或者列不再存在时，会导致视图查询失败。

         创建视图时用户还可以使用其它一些字句：

create view if not exists safer_userinfo
comment "This is a test view"
tblproperties ("creator"="me")
as select ...;

         create table  table_name like ...结构同样适用于复制视图：

create table userinfo
like safer_userinfo

          drop view if exists view_name;来删除视图。

          视图不能够作为insert或load命令的目标表。

          视图是只读的，对于视图只允许改变tblproperties属性：

alter view safer_userinfo set tblproperties ("creator"="some_time");

6.索引

       提示：Hive3.0开始已经没有索引了。