HiveSQL语法<基本原理、操作函数...>

HiveSQL语法

基本原理

A、Mammoth背后

B、数据的尺度

C、空间复杂度

D、Map-Reduce

E、工作流

F、复杂度分析

注意事项

关于分区

  Hive表的分区字段是一个特殊字段，代表数据文件在服务器上的存储路径（文件夹），因此限制分区范围能有效的减小查询时的数据读取资源消耗。使用时需注意：

  1）使用大表（如流量日志，订单明细，支付单明细，各种快照（sd结尾）等）时，一定要搞清楚表的分区方法，预估所需的数据范围，写好分区条件。

  2）当where条件中有or时，or的作用范围一定要用括号，否则分区条件将不起作用，例如where dt='x' and pin='y' or pin is null'，必须读取全部分区才能判断是否满足pin is null，正确写法是where dt='x' and (pin='y' or pin is null)。

  3）有时分区条件与业务时间不完全一致，例如某订单表dt=20170102分区中可能有少量0101日的订单，这种情况应该先分析一下dt与业务日期的错位分布，然后适当放松dt条件，而不是完全删掉dt条件。

  4）由于分区字段代表文件夹名称，过滤分区是在真正读取数据之前发生的，所以分区条件中不能使用其他字段作为判断条件，例如where dt=date_add(ord_dt,1) ... and ord_dt>xxx，应该把dt条件转化为常量，直接写成where dt>date_add(xxx,1)。

  5）由于分区过滤发生在hive执行之前，所以有些函数无法起作用，例如
dt =from_unixtime(unix_timestamp(),'yyyy-MM-dd')，这可能是由于timestamp在Hive执行时才取值。

  6）尽量少建立多重分区的表，一方面多重分区很容易造成数据严重倾斜（例如按日期和业务类型分区，那么小金库的分区数据量会显著多于基金业务，人为造成数据不均衡，查询性能会明显下降），另一方面多重分区意味着文件系统里会生成多层文件夹和碎文件块，导致读写速度降低。

  7）在做外关联的时候（left right full outer join），分区条件要放在子查询中才能起到过滤分区的作用。

关于JOIN

A、正确理解关联运算的计算顺序，a join b join c join d相当于((a join b) join c) join d，即上一步关联的结果集整体作为一个中间表再与下一个表进行关联。

B、一般情况下，左右表至少要有一个的key是唯一的，即a join b on a.k1=b.k2，k1（或k2）在a（或b）表中是唯一的，否则将产生多对多关联，严重占用计算资源，极端情况下可能造成集群磁盘写满。

C、只有在明确数据量较小不会威胁集群安全的情况下才可以使用多对多关联。

D、在做外关联的时候（left right full outer join），过滤条件放在子查询中，放在on中和放在外部where中产生的结果可能不一样，放在on后的单表条件和where后面的条件会在join之后执行（包括分区条件），导致很多无用的数据占用join的资源，因此一定要想清楚哪些条件要在join之前做的（例如分区条件），这些条件必须放在子查询中，不要放在on或外部where后面。

  1）规范写法：select ... from (select * from tableA where dt='xxx') a left join (select * from tableB where dt='xxx') b on a.id=b.id，无论Hive如何升级，这种写法总是对的

  2）不规范写法：select ... from tableA a left join tableB b on a.id=b.id and a.dt='xxx' and b.dt='xxx'，程序的具体执行逻辑取决于hive解释器版本，不一定与用户的想法一致，而且会占用额外资源

  3）不规范写法：select ... from tableA a left join tableB b on [a.id](http://a.id/)=[b.id](http://b.id/) where a.dt='xxx' and b.dt='xxx'，除非确信需要在join后过滤，否则这么写结果是错的，而且会占用额外资源，本例会造成笛卡尔积，并且b.dt条件导致left join白做了

E、少用left join，只有在保留左表数据有意义的时候才使用left join，需注意left join外面的where条件里不应该有右表的条件（右表为null除外），否则left join就无意义了

关于union all

Tez模式下unionall是并行计算的，因此涉及很多个大表union all在TEZ模式下执行将占用过多资源，可以考虑改成mapreduce或者改成分区表，一个分区一个分区的插入数据。

关于数据倾斜

A、数据倾斜指数据中少数key对应的记录数非常多的情况，例如日志表中pin=null的情况，订单表中品类=话费充值的情况等。

B、数据倾斜会严重影响reduce阶段的执行时间，包括groupby，join以及窗口函数等情况，因为同一个key的所有记录都会发送到特定的一个reduce节点上，所以个别节点的计算量会远大于平均水平，具体参见Hive计算复杂度分析。

C、对全表做复杂汇总计算如count(distinct)，排orderby序，或者窗口函数的时候，由于只能使用1个reduce节点，所以也会导致运算时间超长。

如果上了资源占用榜 / 执行时间超长 / etl任务超时

A、如果集群没出异常状况，请仔细检查脚本

B、占用资源过多通常是由于分区条件join或者union all使用不当，少数情况是因为整个计算逻辑都不合理

C、执行时间过长是因为某些reduce压力太大，如果参与计算的总数据量不是非常巨大的话，那就要重点考虑检查数据倾斜，多对多关联，以及优化查询逻辑。

实用技巧

A、字符串的比较

  字符串变量的大小比较是按照字典序进行的，即“逐位比对，第n位相同则比对第n+1位”，具体排位参考utf8编码顺序（空值最小），例如：'2014'<'2014-01'<'2014-01-01'<'2014-01-01 09:00:00'。

  日常使用中很多时候没必要截断或者补全之后再作比较，例如：dt>=concat(year(tx_date),'-01-01') 可直接写成 dt>year(tx_date) 或dt> substr(tx_date,1,4) ，取整年的分区可直接写dt>'2014' and dt<'2015' 。

B、测试语句和函数

  如果要测试一个函数或者一种写法是否正确，可以不带from，比如直接写select regexp_replace('1233ascv','a','A');，结果会只返回一行；如果要测试在数据集上的表现正确性，可以基于已有的表构造一个小表create table dev.my_test as select * from sometable limit 100; 。

C、并行去重 D、消灭row_number=1 E、转置 F、调用外部命令 G、剔除无效字符 H、json解析

Hive计算引擎——Tez & MapReduce

  MapReduce是一种编程模型，用于大规模数据集（大于1TB）的并行运算。概念”Map（映射）”和”Reduce（归约）”。

  Tez是Apache开源的支持DAG作业的计算框架，它直接源于MapReduce框架，核心思想是将Map和Reduce两个操作进一步拆分，即Map被拆分成Input、Processor、Sort、Merge和Output， Reduce被拆分成Input、Shuffle、Sort、Merge、Processor和Output等，这样，这些分解后的元操作可以任意灵活组合，产生新的操作，这些操作经过一些控制程序组装后，可形成一个大的DAG作业。总结起来，Tez有以下特点：

  1）Apache二级开源项目（源代码今天发布的）；

  2）运行在YARN之上；

  3）适用于DAG（有向图）应用（同Impala、Dremel和Drill一样，可用于替换Hive/Pig等）

  传统的MR（包括Hive，Pig和直接编写MR程序）。假设有四个有依赖关系的MR作业（1个较为复杂的Hive SQL语句或者Pig脚本可能被翻译成4个有依赖关系的MR作业）或者用Oozie描述的4个有依赖关系的作业，运行过程如下（其中，绿色是Reduce Task，需要写HDFS）：
  云状表示写屏蔽（write barrier，一种内核机制，持久写），Tez可以将多个有依赖的作业转换为一个作业（这样只需写一次HDFS，且中间节点较少），从而大大提升DAG作业的性能Hadoop是基础，其中的HDFS提供文件存储，Yarn进行资源管理。在这上面可以运行MapReduce、Spark、Tez等计算框架。

  A、MapReduce：是一种离线计算框架，将一个算法抽象成Map和Reduce两个阶段进行处理，非常适合数据密集型计算。

  B、Spark：Spark是UC Berkeley AMP lab所开源的类Hadoop MapReduce的通用的并行计算框架，Spark基于map reduce算法实现的分布式计算，拥有Hadoop MapReduce所具有的优点；但不同于MapReduce的是Job中间输出和结果可以保存在内存中，从而不再需要读写HDFS，因此Spark能更好地适用于数据挖掘与机器学习等需要迭代的map reduce的算法。

  C、Storm：MapReduce也不适合进行流式计算、实时分析，比如广告点击计算等。Storm是一个免费开源、分布式、高容错的实时计算系统。Storm令持续不断的流计算变得容易，弥补了Hadoop批处理所不能满足的实时要求。Storm经常用于在实时分析、在线机器学习、持续计算、分布式远程调用和ETL等领域。

  D、Tez：是基于Hadoop Yarn之上的DAG（有向无环图Directed Acyclic Graph）计算框架。它把Map/Reduce过程拆分成若干个子过程，同时可以把多个Ｍap/Reduce任务组合成一个较大的DAG任务，减少了Ｍap/Reduce之间的文件存储。同时合理组合其子过程，也可以减少任务的运行时间。

Hive操作函数

时间函数

A、datediff()： 日期比较函数，datediff语法：datediff(string enddate,string startdate)

返回值：int

说明：返回结束日期减去开始日期的天数。

举例：

hive> select datediff('2016-12-30','2016-12-29');

1

B、date_add()： 日期增加函数，date_add 语法： date_add(string startdate, intdays)

返回值：string

说明：返回开始日期startdate增加days天后的日期。

举例：

hive> select date_add('2016-12-29',10);

2017-01-08

C、date_sub()： 日期减少函数，date_sub语法：date_sub (string startdate,int days)

返回值：string

说明：返回开始日期startdate减少days天后的日期。

举例：

hive> select date_sub('2016-12-29',10);

2016-12-19

D、查询近30天的数据

select * from table where  datediff(current_timestamp,create_time)<=30；

## create_time 为table里的字段，current_timestamp 返回当前时间 2018-06-01 11:00:00

E、from_unixtime()： 日期函数UNIX时间戳转日期函数，from_unixtime语法：from_unixtime(bigint unixtime[, string format])

返回值：string

说明：转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式

举例：

hive> select from_unixtime(1323308943,’yyyyMMdd’) from dual;

20111208

F、unix_timestamp()：指定格式日期转UNIX时间戳函数,unix_timestamp语法: unix_timestamp(string date, string pattern)

返回值： bigint

说明：转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。

举例：

hive> select unix_timestamp(’20111207 13:01:03′,’yyyyMMdd HH:mm:ss’) from dual;

1323234063

G、to_date()：日期时间转日期函数，to_date语法：to_date(string timestamp)

返回值：string

说明：返回日期时间字段中的日期部分。

举例：

hive> select to_date(’2011-12-08 10:03:01′) from dual;

2011-12-08

H、weekofyear ()：日期转周函数，weekofyear语法：weekofyear (string date)

返回值：int

说明：返回日期在当前的周数。

举例：

hive> select weekofyear(’2011-12-08 10:03:01′) from dual;

49

H、$TX_DATE

自定义变量，准确获取当日日期，格式为”yyyy-MM-dd”。

my $TX_DATE = substr(${CONRTROL_FILE},length(${CONTROL_FILE})-12,4).'-'.substr(${ CONRTROL_FILE},length(${CONTROL_FILE})-8,2).'-'.substr(${CONRTROL_FILE},length(${
CONTROL_FILE})-6,2);

limit函数

在hive表前1000行里，过滤出不重复的refid,imsi。

A、错误的写法：

select distinct refid,imsi from HIVE_D_MT_UU_H_SPARK limit 1000;

会去读取全表，把0~1000行的不重复refid，imsi显示出来。

B、正确的写法：

select distinct refid,imsi from (select * from HIVE_D_MT_UU_H_SPARK limit 1000);

C、调优的写法：

CREATE TABLE TEMP_HIVE_D_MT_UU_H_SPARK AS

select * from HIVE_D_MT_UU_H_SPARK limit 1000;

select distinct refid,imsi from TEMP_HIVE_D_MT_UU_H_SPARK;

Hive最快的执行就是不走MapReduce。简单的select的是最快的，嵌套啥的都比较忙。与关系型数据库不同，调优的写法执行更快。

Count函数

A、执行效果上：

  1）count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

  2）count(1)包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

  3）count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计

B、执行效率上：

  列名为主键，count(列名)会比count(1)快 列名不为主键，count(1)会比count(列名)快

  如果表多个列并且没有主键，则count(1)的执行效率优于count(*)

  如果有主键，则 select count（主键）的执行效率是最优的

  如果表只有一个字段，则 select count(*) 最优

  当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count(*)用时多了！ 执行计划上：

  count(1)和count()的效果是一样的。 但是在表做过分析之后，count(1)会比count()的用时少些（1w以内数据量），不过差不了多少。 如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。因为count(*)自动会优化指定到那一个字段。所以没必要去count(1)，用sql会帮你完成优化的。

  因此：count(1)和count(*)在sql调优功能基本上是没有差别！

C、应用

select t.tag_value(某一字段), count(1) from (table) t;

查看各字段数量级的方式！！！

Group By函数

  group by 一般和聚合函数一起使用才有意义,比如count、sum、avg等，使用group by的两个要素：

  出现在select后面的字段 要么是是聚合函数中的，要么就是group by 中的。要筛选结果可以先使用where，再用group by 或者先用group by，再用having。

  group by textA, textB, textC;依次判断textA、textB、textC字段是否相同，对不同字段进行区域性保留，建成一个区域表，起到去重的作用。重要！！！！！

参考：groupby的用法