Hive编程指南05---Hive查询

Hive查询

SELECT ... FROM 语句

1.ARRAY<STRING>

    数组索引基于0的，col[0]，引用一个不存在的元素将返回NULL，同时，提取出的STRING数据类型的值将不再加引号

    MAP<STRING, FLOAT>

col["键值"]

    STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>

col.street，使用“点”符号，类似于“表的别名.列名”

2.使用正则表达式来指定列

SELECT 'col.*' FROM <table>

col作为前缀的列

3.使用列值进行计算

SELECT round(col1 * (1 - col2) ) FROM <table>;

4.算数运算符

数据类型不同，两种类型中值范围较小的那个数据类型将转换为其他范围更广的数据类型，类似于JAVA自动类型转换。

5.使用函数

a.数学函数

注意：floor、round、ceil（向上取整）输入的是DOUBLE类型的值，而返回值是BIGINT类型的。

b.聚合函数

设置属性hive.map.aggr为true来提高聚合的性能，会触发在map阶段进行的“顶级”聚合过程。（非顶级的聚合过程将会在执行一个GROUP BY 后进行）

不过会需要更多的内存。SET hive.map.aggr=true;

    

c.表生成函数

可以将单列扩展成多列或者多行。例如：SELECT parse_url_tuple(url, 'HOST', 'PATH', 'QUERRY') as (host, path, query) FROM <table>;

d.其他内置函数

6.LIMIT语句

LIMIT子句用于限制返回的行数

SELECT ... FROM ... LIMIT 2;

7.列别名

  SELECT col as other_col FROM ...;

8.嵌套SELECT语句

FROM (

SELECT * FROM <table>

) a

SELECT a.col1 WHERE a.col2='';

9.CASE ... WHEN ... THEN句式

SELECT col1,

CASE

WHEN col2 < ... THEN ''

WHEN col2 >= ... THEN ''

ELSE ''

END as a FROM <table>;

10.什么情况下Hive可以避免进行MapReduce

//本地模式，读取对应的存储目录下的文件

SELECT * FROM <table>;

//WHERE语句过滤条件只是分区字段

SELECT * FROM <table> WHERE par1='';

//设置属性hive.exec.mode.local.auto为true，Hive会尝试本地模式执行其他操作；否则，Hive使用MapReduce来执行其他所有的查询

set hive.exec.mode.local.auto=true;

WHERE语句

在WHERE语句中，不能使用列的别名，只能通过嵌套语句，再使用列别名

SELECT t.* FROM

(SELECT col1 as c1,col2 as c2 FROM <table>) t

WHERE t.c1>...;

1.谓词操作符

    这些操作符可以用于JOIN... ON 和 HAVING语句中

    

    

2.关于浮点数比较

    0.2对于FLOAT类型是0.2000001

    方法1：从TEXTFILE文本文件中读取数据，文件中的0.2会转换为真实的数据

    方法2：显示指出0.2为FLOAT类型，cast操作符，cast(0.2 AS FLOAT)

    方法3：和钱相关的避免使用浮点数

3.LIKE和RLIKE

    LIKE '%xxx' //匹配xxx结尾

    LIKE 'xxx%' //匹配xxx开头

    LIKE '%xxx%' //匹配包含xxx

    //RLIKE，可以通过正则表达式来匹配

    RLIKE '.*(aa|bb).*' //匹配包含aa或者bb

GROUP BY语句

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

HAVING语句

    对GROUP BY语句产生的分组进行条件过滤

JOIN语句

1.INNER JOIN

    两个表中都存在与连接标准相匹配的数据会保留

    SELECT a.col,b.col,c.col 

    FROM <table1> a JOIN <table2> b ON a.col=b.col AND a.col=b.col

    WHERE a.col='' AND b.col='';

2.JOIN优化

    方法一：连续查询中的表的大小从左到右是依次增加的

    方法二：“标记”机制显式告之查询优化器哪张表是大表

        SELECT /*+STREAMTABLE(a) */a.col,b.col

        FROM <table1> a JOIN <table2> b ON a.col=b.col AND a.col=b.col

        WHERE a.col='';

    方法三：map-side JOIN

3.LEFT OUTER JOIN

    如果右边表中没有符合ON后面连接条件的记录时，右边表指定列的值为NULL

4.OUTER JOIN

    OUTER JOIN 在 ON 中不能使用过滤条件 col=''，INNER JOIN 在 ON 中可以使用过滤条件col=''

    可以使用嵌套SELECT语句实现过滤

    SELECT a.col, b.col FROM

        (SELECT * FROM <table1>) a

    LEFT OUTER JOIN

        (SELECT * FROM <table2>) b

    ON (a.col = b.col);

5.RIGHT OUTER JOIN

    如果左边表中没有符合ON后面连接条件的记录时，左边表指定列的值为NULL

6.FULL OUTER JOIN

    所有表中符合WHERE语句条件的所有记录

7.LEFT SEMI-JOIN

    Hive不支持IN

    SELECT a.col1, a.col2 FROM <table1> a

    WHERE a.col1, a.col2 IN 

    (SELECT b.col1, b.col2 FROM <table2> b);

    可以使用LEFT SEMI JOIN，但SELECT和WHERE语句不能引用右边表的字段

    SELECT a.col1,a.col2 

    FROM <table1> a LEFT SEMI JOIN <table2> b ON a.col1=b.col1 AND a.col2=b.col2

8.map-side JOIN

    所有表中只有一张表是小表，可以在最大的表通过mapper的时候将小表完全放到内存中缓存起来。省略掉reduce过程，减少map过程的执行步骤

    set hive.auto.convert.join=true;

    一张桶表是另外一张桶表的若干倍

    set hive.optimize.bucketmapJOIN=true;

    桶表都具有相同的分桶数，而且数据按照连接键或桶的键进行排序的，分类-合并连接(sort-merge JOIN)

    set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

    set hive.optimize.bucketmapjoin=true;

    set hive.optimize.bucketmapjoin.sortedmerge=true;

ORDER BY 和 SORT BY

    ORDER BY会触发全局排序，有一个所有的数据通过一个reducer进行处理

    set hive.mapred.mode=strict; //必须加LIMIT语句进行限制

    SORT BY在每个reducer中对数据进行排序（非全局有序）

含有SORT BY 的 DISTRIBUTE BY

    DISTRIBUTE BY 控制map的输出在reducer中是如何划分的

    DISTRIBUTE BY 和 GROUP BY 在其控制着reducer是如何接受一行行数据进行处理类似，而SORT BY 控制着reducer内的数据是如何排序的

    SELECT a.col1, a.col2 FROM <table> a

    DISTRIBUTE BY a.col1

    SORT BY a.col1, a.col2 DESC;

CLUSTER BY

    DISTRIBUTE BY 和 SORT BY两条语句中涉及到的列完全相同，而且采用升序排序方式，可以使用CLUSTER BY替换，相当于简写，实现文件数据的全局排序

    SELECT a.col1, a.col2 FROM <table> a

    CLUSTER BY a.col1;

类型转化

    cast(value AS TYPE)，可以嵌套cast(cast(value AS TYPE) AS TYPE)

    浮点数转化为整数，推荐round()或者floor()函数

    BINARY类型只支持类型转换为STRING类型

抽样查询

    cast(value AS

UNION ALL

    可以将2个或多个表合并，每个union子查询都必需具有相同的列，而且对应的每个字段的类型必须一致