Hive 动态分区

Hive的insert语句能够从查询语句中获取数据，并同时将数据Load到目标表中。现在假定有一个已有数据的表staged_employees（雇员信息全量表），所属国家cnty和所属州st是该表的两个属性，我们做个试验将该表中的数据查询出来插入到另一个表employees中。

由于使用了OVERWRITE关键字，目标表中原来相同partition中的所有数据被覆盖，如果目标表中没有partition，则整个表会被覆盖。

如果把OVERWRITE关键字删掉，或者替换成INTO，则hive会追加而不是替代原分区或原表中的数据，这个特性在Hive v080之后才支持。

当数据已经存在于hdfs上但不是我们想要的格式的时候，当进行的计算需要分好多步骤有必要存储中间数据的时候，或者原数据没有分区、有很多无效列需要过滤的时候，可以使用insertselect句型来完成这一转换过程。

由于一个国家有很多个省份，如果想根据（国家country，地区partition）两个维度对数据进行分区的话，这条SQL语句的执行个数应该等于地区的数目，比如中国有23个省就要对该SQL语句执行23次。因此hive对这个SQL语句进行了改造，只需要扫描一次原表就可以生成不同的输出（多路输出）。比如下面的SQL语句扫描了一次原始数据表，但是同时生成了3个省份的结果数据：

通过缩进可以很清楚的看到，我们扫描了一次staged_employees表但是执行了3次不同的insert语句，这条大SQL语句是这么执行的：先通过from staged_employees表获取一条记录，然后执行每一个select子句，如果select子句验证通过则执行相应的insert语句。注意这里的三条select子句是完全独立执行的，并不是if then else的关系，这就意味着这3条select子句在某种情况下可能同时通过where检测。

通过这种结构，原始表的数据能被拆分到目标表的不同partition中去。

如果原表的一条记录满足于其中一个给定的select where 子句，则该记录将被写到目标表的固定分区中。其实更进一步，每条Insert语句能将数据写到不同的数据表中，不管这个表是否分区都一样。

于是，就像一个过滤器一样，原表的一些数据被写到了很多输出地址，而剩下的数据会被丢弃。

当然，你也可以混用Insert overwrite和insert into两种不同的方法写出数据。

向动态分区插入数据

但是问题还是没有解决，中国有23个省，那么我们就需要写23个insert into select where子句，这非常不现实。于是hive的一种叫做动态分区的特性就出现了，它能够根据select出来的参数自动推断将数据插入到那个分区中去。本文上面的两种SQL语句设定分区的方式都叫做静态分区插入。

将上一个SQL语句进行改动，会得到以下简洁的新SQL语句：

hive先获取select的最后两个位置的secnty和sest参数值，然后将这两个值填写到Insert语句partition中的两个country和state变量中，即动态分区是通过位置来对应分区值的。原始表select出来的值和输出partition的值的关系仅仅是通过位置来确定的，和名字并没有关系，比如这里secnty和county的名称完全没有关系。

上面的这条SQL语句是对两个分区同时进行了动态设定，如果staged_employees表中有100个国家，每个国家有100个地区，那么该SQL语句自动对每个国家和地区建立相应的partition并插入数据，如果用手写的话不现实。

只要位置正确，你可以混用动态分区和静态分区值设定，比如下面这个例子，你可以静态指定一个country值，但是state值采用动态的方法设定：

注意：静态分区值必须在动态分区值的前面！

使用hive动态分区的参数设定

动态分区功能默认是关闭的，而当它是打开状态时，默认会工作在“strict”模式下，这种模式下要求至少指定一个静态分区的值。这样做是为了防止设计了大量partition的糟糕情况，举个例子你使用时间戳来进行分区，竟然每一秒钟都产生一个分区！还有其他的一些属性设定用来限制类似的情况出现，如下表所示：

名称 默认值 描述

hiveexecdynamicpartition false 设置为true用于打开动态分区功能

hiveexecdynamicpartitionmode strict 设置为nonstrict能够让所有的分区都动态被设定，否则的话至少需要指定一个分区值

hiveexecmaxdynamicpartitionspernode 100 能被每个mapper或者reducer创建的最大动态分区的数目，如果一个mappre或者reducer试图创建多余这个值的动态分区数目，会引发错误

hiveexecmaxdynamicpartitions +1000 被一条带有动态分区的SQL语句所能创建的动态分区总量，如果超出限制会报出错误

hiveexecmaxcreatedfiles 100000 全局能被创建文件数目的最大值，专门有一个hadoop计数器来跟踪该值，如果超出会报错

举个例子，使用全动态分区的SQL语句序列如下所示，需要先设定一些必要的参数才可以：

总结

使用from insert select where结构能够从一个数据表中抽取数据，将结果插入到不同的表和分区中，而使用动态分区能够让hive根据select最末几个位置的值自动设定目标分区的值，使用动态分区需要设定一些hive运行参数。

转自 >

hive读取orc文件行数：避免全分区字段是动态的，必须有至少一个分区字段是指定有值的。

Hive的insert语句能够从查询语句中获取数据，并同时将数据Load到目标表中。现在假定有一个已有数据的表staged_employees（雇员信息全量表），所属国家cnty和所属州st是该表的两个属性，我们做个试验将该表中的数据查询出来插入到另一个表employe。

ENT：按ENTER键一下：

\^：代表CTRL键，如要按组合键如CTRL+S：\^S；（其他如此类推）。

\%：代表ALT键，如要按组合键如ALT+F：\%F；（其他如此类推）。

\{}：代表按下键盘上功能键，之于要什么功能键就在{}中写，如要按F1键：\{F1}；向下的箭头键：\{DOWN};（其他如此类推）。

\^{F4}：代表按下Ctrl+F4键。

ML(684,120)：代表按下鼠标左键，括号中的数字代表鼠标在屏幕上的坐标；（注：我们可以在主窗口把鼠标的位置放好，然后通过ALT+TAB键的 *** 作切换到DATALOAD的窗口按下+M+L就可以比较精确地定位鼠标）。

hivesql sql — 获取指定hive表或指定文件所hive表DDL按区则默认执行近7区DDL同table支持符合sql语则表达式表匹配则提示用户选择(使用file则自关闭该交互功能)

  在进行数据分析时，尤其要对网页进行分析时，我们往往要对其中部分的数据进行抽取，这个就需要靠hive的函数来完成了。

  首先要讲的是split函数，这个函数的作用是对字符窜进行分割，基本用法为：split(string str, string pat) ，返回值为一个数组array，因此要取值得话需要用到切片，即[数字]，其中第一个str是要切分的字符串，第二个pat是以什么字符进行切割。来看案例吧。

  有的时候切割不是一下就能完成，那么就多去嵌套几次split就好了。

  返回字符串从某个位置开始固定长度的子串，和substring功能相同，基本用法为substr(string A, int start, int len )，还是来看例子。值得注意的是，substr(str,0,2)和substr(str,1,2)的功能都是一样的，都是从第一个位置开始。

  这个函数是个神器，可以解析url结构，返回我们想要的东西。基本用法为parse_url(string urlString, string partToExtract [, string keyToExtract])，其中partToExtract的有效值包括HOST，PATH， QUERY， REF， PROTOCOL， AUTHORITY，FILE和USERINFO等，具体我就不一一解释了，大家使用时可以自行百度。重点说一下，当第二个参数是QUERY时，第三个参数就可以使用了，这个是参数提取最有用的方法了，还是以案例来说明。

  这个函数是最终的大杀器了，以上都解决不了你的问题的时候，有了这个一切都可以解决，使用这个函数的基础是正则表达式基础要会一些。这个函数的基本用法是regexp_extract(string subject, string pattern, int index)，第一个参数是待处理的字符串，第二个参数是写好的正则，第三个表达式一般用不上可以忽略掉。来看例子：

  有了以上函数，相信应该能满足大家对于hive进行字符串提取的一切要求了。

1

查询语言。由于 SQL 被广泛的应用在数据仓库中，因此，专门针对 Hive 的特性设计了类 SQL 的查询语言 HQL。熟悉 SQL 开发的开发者可以很方便的使用 Hive 进行开发。

2 数据存储位置。Hive 是建立在 Hadoop 之上的，所有 Hive 的数据都是存储在 HDFS 中的。而数据库

则可以将数据保存在本地文件系统中。

3 数据格式。Hive 中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定三

个属性：列分隔符（通常为空格、”\t”、”\x001″）、行分隔符（”\n”）以及读取文件数据的方法（Hive 中默认有三个文件格式 TextFile，SequenceFile 以及 RCFile）。由于在加载数据的过程中，不需要从用户数据格式到 Hive 定义的数据格式的转换，因此，Hive 在加载的过程中不会对数据本身进行任何修改，而只是将数据内容复制或者移动到相应的 HDFS 目录中。而在数据库中，不同的数据库有不同的存储引擎，定义了自己的数据格式。所有数据都会按照一定的组织存储，因此，数据库加载数据的过程会比较耗时。

4 数据更新。由于 Hive 是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive 中不

支持对数据的改写和添加，所有的数据都是在加载的时候中确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用 INSERT INTO VALUES 添加数据，使用 UPDATE

SET 修改数据。

5 索引。之前已经说过，Hive 在加载数据的过程中不会对数据进行任何处理，甚至不会对数据进行扫描，

因此也没有对数据中的某些 Key 建立索引。Hive 要访问数据中满足条件的特定值时，需要暴力扫描整个数据，因此访问延迟较高。由于 MapReduce 的引入， Hive 可以并行访问数据，因此即使没有索引，对于大数据量的访问，Hive 仍然可以体现出优势。数据库中，通常会针对一个或者几个列建立索引，因此对于少量的特定条件的数据的访问，数据库可以有很高的效率，较低的延迟。由于数据的访问延迟较高，决定了 Hive 不适合在线数据查询。

6 执行。Hive 中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的（类似 select from tbl

的查询不需要 MapReduce）。而数据库通常有自己的执行引擎。

7 执行延迟。之前提到，Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外

一个导致 Hive 执行延迟高的因素是 MapReduce 框架。由于 MapReduce 本身具有较高的延迟，因此在利用 MapReduce 执行 Hive 查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive 的并行计算显然能体现出优势。

8 可扩展性。由于 Hive 是建立在 Hadoop 之上的，因此 Hive 的可扩展性是和 Hadoop 的可扩展性是

一致的（世界上最大的 Hadoop 集群在 Yahoo!，2009年的规模在 4000 台节点左右）。而数据库由于 ACID 语义的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有 100 台左右。

9 数据规模。由于 Hive 建立在集群上并可以利用 MapReduce 进行并行计算，因此可以支持很大规模的

数据；对应的，数据库可以支持的数据规模较小。

根据官网的介绍，hive推出的窗口函数功能是对hive sql的功能增强，确实目前用于离线数据分析逻辑日趋复杂，很多场景都需要用到。以下就是对hive窗口函数的一个总结附上案例。

PRECEDING：往前

FOLLOWING：往后

CURRENT ROW：当前行

UNBOUNDED：起点（一般结合PRECEDING，FOLLOWING使用）

UNBOUNDED PRECEDING 表示该窗口最前面的行（起点）

UNBOUNDED FOLLOWING：表示该窗口最后面的行（终点）

比如说：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行）

ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往后1行）

ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW（表示往前2行到当前行）

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示当前行到终点）

官网有一段话列出了哪些窗口函数是不支持window子句的，如下图所示：

1LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值第一个参数为列名，第二个参数为往下第n行（可选，默认为1，不可为负数），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

2LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值第一个参数为列名，第二个参数为往上第n行（可选，默认为1，不可为负数），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

3FIRST_VALUE取分组内排序后，截止到当前行，第一个值，这最多需要两个参数。第一个参数是您想要第一个值的列，第二个（可选）参数必须是false默认为布尔值的布尔值。如果设置为true，则跳过空值。

4LAST_VALUE取分组内排序后，截止到当前行,最后一个值，这最多需要两个参数。第一个参数是您想要第一个值的列，第二个（可选）参数必须是false默认为布尔值的布尔值。如果设置为true，则跳过空值。

让我们加上window子句来观察一下变化，虽然FIRST_VALUE和LAST_VALUE不常于与window子句结合使用。

1COUNT

2SUM

3MIN

4MAX

5AVG

目前支持这五种带有聚合意义的窗口函数，以常用SUM举例。

从结果当中其实可以得到结论，默认情况就是从起点到当前行，不带order by语句其实就是表示窗口内全部行都参与聚合处理，这里其实还有其他用法，读者可以自行尝试一下。

1ROW_NUMBER

从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列;通常用于获取分组内排序第一的记录;获取一个session中的第一条refer等。

2RANK

生成数据项在分组中的排名，排名相等会在名次中留下空位。

3DENSE_RANK

生成数据项在分组中的排名，排名相等会在名次中不会留下空位。

4CUME_DIST

CUME_DIST 小于等于当前值的行数/分组内总行数

5PERCENT_RANK

PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1

6NTILE

NTILE(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN

以上是带有分析功能的窗口函数，使用的频率没有上面两类高，但是也是需要掌握的。

我们先对1-3三种分析窗口函数进行演示

第4-5种：

第六种：NTILE

一般的求取前20%,就是使用两个窗口函数,如下获取

这种写法是一般的写法,当然也可以把count放在where那,来获取总的行数

hive中提供了一个非常有用的函数桶函数,把全部的数据分成5个桶获取第一个桶即可

percentile_approx / percentile -- spark

approx_percentile -- presto

kylin

percentile_approx(order_price, array(01,05,075,095),9999) over(order by order_price asc )

按照降序,获取中位数,和学过的窗口函数order by排序功能一样,局部的中位数

要想获取全局的中位数直接over()为空即可,在遇到数据倾斜可能失效,使用这个函数和前面的窗口写法获取的大差不差

这类小样本的问题是不能使用上面的三种方法的,可能有的分区只有几条数据,根本就不满足20%的条件,或者是由于样本太小,第20%不存在，还有一种就是特定需求

说一下这个需求的背景,销售排行榜上,我需要对四分位的销售者给予激励,这类销售者具备较大的潜力

有三种方法来解决这个问题,由于20%不存在,那么我们取20%-25%都是可以接受的,在窗口写法的基础上,排个序获取后几位即可,最后一种方法就是在窗口的写法上嵌套一层偏移量函数

以上就是关于Hive 动态分区全部的内容，包括:Hive 动态分区、如何获取hive建表语句、hive读取orc文件行数等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！