Hive QL操作

Hive QL操作

6.1 SELECT…FROM 语句

SELECT是SQL中的射影算子，FROM子句标识了从哪个表、视图或嵌套查询中选择记录。对于一个给定的记录，SELECT指定了要保存的列以及输出函数需要调用的一个或多个列（例如，像count(*)这样的聚合函数）

6.1.1使用正则表达式指定列

create table 
logs
(
    uuid string,
    userid string ,
    fromUrl string ,
    dateString string,
    timeString string,
    ipAddress string,
    browserName string,
    pcSystemNameOrmobileBrandName string ,
    systemVersion string,
    language string, 
    cityName string
)
partitioned BY (day string)
row format delimited fields terminated 
by ' ';

logs表结构：

hive> desc logs;
OK
userid                  string
fromurl                 string
datestring              string
timestring              string
ipaddress               string
browsername             string
pcsystemnameormobilebrandname   string
systemversion           string
language                string
cityname                string
day                     string

# Partition Information
# col_name              data_type               comment

day                     string
Time taken: 0.152 seconds, Fetched: 16 row(s)

现在想要查询某些字段，比如说所有的时间，我们就可以使用正则表达式

需要设置如下属性才能使用  注意 在Hive编程指南中没有对此问题作出解答
set hive.support.quoted.identifiers=none；

select  userid, `.*string` from logs;

注意次引号为 反单引号 （ESC那个！）

效果如下：

6.1.2 使用列值进行计算

这里就不得不提到在Hive中的算术运算符

运算符	类型	描述
A+B	数值	A和B相加
A-B	数值	A减去B
A*B	数值	A和B相乘
A/B	数值	A除以B。如果能整除，那么返回商数
A%B	数值	A除以B的余数
A&B	数值	A和B按位取与
A|B	数值	A和B按位取或
A^B	数值	A和B按位取亦或
~A	数值	A按位取反

6.1.3 LIMIT语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数

#只返回前两条数据
select * from testuser limit 2;

6.2 WHERE 语句

在之前的WEB学习中，大家肯定知晓WHERE就是条件查询，用于将不满足条件的行过滤掉

# 简单写一个条件查询语句
select * from testuser where id = '1' ;

效果

6.3 GROUP BY 分组

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列对结果进行分组，然后对每个组执行聚合操作。

# 根据性别进行分组查询每个性别的平均工资
select sex , avg(salary) as avgSalary   from testuser group by sex;

6.4 JOIN语句

6.4.1 内连接

#使用内连接，查询性别不同但是工资相同的人
select  a.sex, a.name, a.salary , b.sex , b.name,b.salary from testuser a join testuser b on a.salary = b.salary where a.sex = false and b.sex = true; 

三表连接搞起来

 #使用内连接，查询来自三个不同城市的人且具有相同的第二种爱好
 select 
 a.address.city  , a.name as name , a.hobbies[1] as hobby , 
 b.address.city  , b.name as name ,b.hobbies[1] as hobby ,
 c.address.city  , c.name as name ,c.hobbies[1] as hobby 
 from testuser a  
 join testuser b on a.hobbies[1] = b.hobbies[1]  
 join testuser c on a.hobbies[1] = c.hobbies[1]   
 where a.address.city = 'bj' and b.address.city = 'sh' and c.address.city = 'ly'; 

6.4.2 JOIN优化

这种优化其实是指手动指定那些数据最大，从而最后查询（和之前查过的，已经缓存上的数据进行对比），这样可以减少服务器的压力

在上述三表连接的操作中，其实就是一种“失误”的写法，为什么呢 ？因为通过比较我们可以发现，来自北京的人最多，也就是说当前a表最大。Hive同时假定查询中最后一个表是最大的那个表。在对每行记录进行连接操作时，它会尝试将其他表缓存起来，然后扫描最后那个表进行计算。因此，用户需要保证连续查询中的表的大小从左到右是依次增加的。

但是咱们将最大的表放在第一个，显然就违反这个逻辑，因为测试数据较小我们根本不会发现有任何差异，但是真正的在生产环境中，我们就需要将表依次从小到大进行排列，来达到效率的最大化。

但是假如我们在书写SQL语句的时候不能将最大的表放在最后，或者忘记放在最后，有什么办法去解决这个问题呢？

幸运的是，用户并非总是要将最大的表放置在查询语句的最后面的。这是因为Hive还提供了一个“标记”机制来显式地告之查询优化器哪张表是大表，来看具体的SQL语句：

 
 select 
 /*+STREAMTABLE(a)*/
 a.address.city  as name, a.name, a.hobbies[1] as hobby , 
 b.address.city  as name, b.name,b.hobbies[1] as hobby ,
 c.address.city  as name, c.name,c.hobbies[1] as hobby 
 from testuser a  
 join testuser b on a.hobbies[1] = b.hobbies[1]  
 join testuser c on a.hobbies[1] = c.hobbies[1]   
 where a.address.city = 'bj' and b.address.city = 'sh' and c.address.city = 'ly'; 

/+STREAMTABLE(a)/ 使用此关键字，括号写表的 |别名|

6.4.3 LEFT OUTER JOIN 左外连接

在这种JOIN连接操作中，JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。JOIN操作符右边表中如果没有符合ON后面连接条件的记录时，那么从右边表指定选择的列的值将会是NULL。

左链接,显示关键词left左边表中的所有数据,右边表数据数据少了补NULL值,数据多了不显示;

# 连接部门表查询员工来自哪个部门
select a.name  ,b.depname from testemp a left join testdep  b on a.depid = b.id;

6.4.4 FULL OUTER JOIN 全连接|满外连接

6.4.5 RIGHT OUTER JOIN 右外连接

右外连接(RIGHT OUTER JOIN)会返回右边表所有符合WHERE语句的记录。左表中匹配不上的字段值用NULL代替。

# 右外连接
select a.name  ,b.depname from testemp a right  join testdep  b on a.depid = b.id;

6.4.6 笛卡尔积JOIN

笛卡尔积是一种连接，表示左边表的行数乘以右边表的行数等于笛卡尔结果集的大小。也就是说如果左边表有5行数据，而右边表有6行数据，那么产生的结果将是30行数据

不带条件

SELECT * FROM testemp , testdep;

可以看到每个人的名字出现了三次，也就是3*6=18行数据

带条件

SELECT   * FROM testemp a  ,testdep b where a.depid = b.id;

6.5 排序 ORDER BY | SORT BY|CLUSTER BY

6.5.1 ORDER BY

order by 在这里叫全局排序，也就是一个MR程序中，升序的关键字ASC，默认升序。DESC为降序。 ORDER BY 子句在SELECT语句的结尾。

#按照工资就行升序排序
select * from testuser order by salary;

#按照工资就行降序排序
select * from testuser order by salary desc;

6.5.2 SORT BY

每个MapReduce内部进行排序，对全局结果集来说不是排序，但是对于Reduce=1的情况是无法体现和ORDER BY的区别

Reduce个数为1

select * from testuser sort  by salary;

设置Reduce个数

设置完成Reduce个数之后就能体现出来差别了

set mapreduce.job.reduces=2;
select * from testuser sort  by salary;

可以看到前4个是升序 后两个是升序

查看Reduce个数

get mapreduce.job.reduces;

6.5.3 Distribute By

Distribute By：类似MR中partition，进行分区，结合sort by使用。
注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

实际操作：

hive (default)> insert overwrite local directory '/root/realclean.log' select * from logs distribute by dateString sort by  timeString;

6.5.4 Cluster By

当distribute by和sorts by字段相同时，可以使用cluster by方式。cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。

select * from logs cluster by timeString;
select * from logs distribute by timeString sort by timeString;
上述两种写的方式是相同的

6.6 抽样|分桶

6.6.1 抽样

Hive提供了另外一种按照百分比进行抽样的方式，这种事基于行数的，按照输入路径下的数据块百分比进行的抽样。

select * from testuser   tablesample(0.1 percent);

提示：这种抽样方式不一定适用于所有的文件格式。另外，这种抽样的最小抽样单元是一个HDFS数据块。因此，如果表的数据大小小于普通的块大小128M的话，那么将会返回所有行。

6.6.2 分桶

分区针对的是数据的存储路径；分桶针对的是数据文件。分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。分桶是将数据集分解成更容易管理的若干部分的另一个技术。

创建表

create table student4(sno int,sname string,sex string,sage int, sdept string) clustered by(sno) into 3 buckets row format delimited fields terminated by ',';

create table 
logs_buck
(
    uuid string,
    userid string ,
    fromUrl string ,
    dateString string,
    timeString string,
    ipAddress string,
    browserName string,
    pcSystemNameOrmobileBrandName string ,
    systemVersion string,
    language string, 
    cityName string
)
clustered by(uuid)
into 3 buckets row format delimited fields terminated by ' ';

set hive.enforce.bucketing = true;

load data local inpath '/root/realclean.log' overwrite into table logs_buck;

我们看到虽然设置了强制分桶，但实际student表下面只有一个logs一个文件。分桶也就是分区，分区数量等于文件数，所以上面方法并没有分桶。

创建一个新表
create table 
logs_buck1
(
    uuid string,
    userid string ,
    fromUrl string ,
    dateString string,
    timeString string,
    ipAddress string,
    browserName string,
    pcSystemNameOrmobileBrandName string ,
    systemVersion string,
    language string, 
    cityName string
)
clustered by(uuid)
into 3 buckets row format delimited fields terminated by ' ';

这是我们这是的是3个桶
所以设置一下reducetask的个数
set mapreduce.job.reduces=3;

insert into table logs_buck1 select *  from logs_buck distribute by(timeString) sort by(timeString asc);