Hive简介

1.Hive简介

2.Hive Sql

3.Hive的三种Join方式

4.Hive事务使用建议

5.Hive 窗口函数、分析函数

6.hive与hbase的区别与联系

1.Hive简介

Hive是一种建立在Hadoop文件系统上的数据仓库架构，并对存储在HDFS中的数据进行分析和管理。

提供以下功能：

（1）它提供了一系列的工具，可用来对数据进行提取/转化/加载（ETL）；

（2）是一种可以存储、查询和分析存储在HDFS（或者HBase）中的大规模数据的机制；

（3）查询是通过MapReduce来完成的；

（4）在Hive0.11对类似select a,b from XXX的查询通过配置也可以不通过MapReduce来完成

Hive设计的初衷是：对于大量的数据，使得数据汇总，查询和分析更加简单。它提供了SQL，允许用户更加简单地进行查询，汇总和数据分析。同时，Hive的SQL给予了用户多种方式来集成自己的功能，然后做定制化的查询，例如用户自定义函数（User Defined Functions，UDFs).

数据单元

根据颗粒度的顺序，Hive数据被组织成： 

- 数据库 ：命名空间功能，为了避免表，视图，分区，列等等的命名冲突。数据库也可以用于加强用户或用户组的安全。

• 表：相同数据库的同类数据单元。

• 分区：对应表下的一个目录。每个表可以有一个或多个用于决定数据如何存储的分区键。分区（除存储单元之外）也允许用户有效地识别满足指定条件的行；例如，STRING类型的date_partition和STRING的country_partition。这些分区键的每个惟一的值定义了表的一个分区。例如，所有的“2009－12－23”日期的“US”数据是表page_views的一个分区。（注意区分，分区键与分区，如果分区键有两个，每个分区键有三个不同的值，则共有6个分区）。因此，如果你只在日期为“2009－12－23”的“US”数据上执行分析，你将只会在表的相关数据上执行查询，这将有效地加速分析。然而要注意，那仅仅是因为有个分区叫2009-12-23，并不意味着它包含了所有数据，或者说，这些数据仅仅是那个日期的数据。用户需要保证分区名字与数据内容之间的关系。分区列是虚拟列，它们不是数据本身的一部分，而是源于数据加载。
• 桶（Buckets or Clusters）：对应一个文件。每个分区的数据，基于表的一些列的哈希函数值，又被分割成桶。例如，表page_views可能通过userid分成桶，userid是表page_view的一个列，不同于分区列。这些桶可以被用于有效地抽样数据。

2.Hive Sql

2.1创建表

表名和列名不区分大小写。

1） Table内部表

hive>create table inner_table (key string);

2） Partition（分区表）

CREATE TABLE page_view(

viewTime INT,

userid BIGINT,

page_url STRING,

country STRING,

ip STRING COMMENT 'IP地址')

COMMENT '网页信息'

PARTITIONED BY (dt STRING, country STRING)

STORED AS SEQUENCEFILE;

使用 PARTITIONED 关键词定义的分区列与数据列是不同的，分区列实际上不存储数据。当使用这种方式创建表的时候，我们假设数据文件的内容，字段之间以ASCII 001（ctrl-A)分隔，行之间以换行分隔。 STORED AS SEQUENCEFILE 表示这个数据是以二进制格式进行存储数据在hdfs上。

如果数据不是以上述格式组织的，我们也可以指定分隔符，如下：

CREATE TABLE page_view(

viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User')

COMMENT 'This is the page view table'

PARTITIONED BY(dt STRING, country STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '1' STORED AS SEQUENCEFILE;

目前，行分隔符不能指定，因为它不是由Hive决定，而是由Hadoop分隔符。

-----------------------------------------------

hive文件存储格式包括以下几类：

1、TEXTFILE

2、SEQUENCEFILE

3、RCFILE

4、ORCFILE(0.11以后出现)

其中TEXTFILE为默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理；

SEQUENCEFILE，RCFILE，ORCFILE格式的表不能直接从本地文件导入数据，数据要先导入到textfile格式的表中， 然后再从表中用insert导入SequenceFile,RCFile,ORCFile表中。

TEXTFILE ：默认格式，数据不做压缩，磁盘开销大，数据解析开销大。

可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。

SequenceFile 是Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点。

RCFILE 是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。

其次，块数据列式存储，有利于数据压缩和快速的列存取。

ORC File ，它的全名是Optimized Row Columnar (ORC) file，其实就是对RCFile做了一些优化。据官方文档介绍，这种文件格式可以提供一种高效的方法来存储Hive数据。它的设计目标是来克服Hive其他格式的缺陷。运用ORC File可以提高Hive的读、写以及处理数据的性能。

------------------------------------------------

3） Bucket Table（桶表）

对表的指定列进行分桶，是一个好的方法，它可以有效地对数据集进行抽样查询。如果没有分桶，则会进行随机抽样，由于在查询的时候，需要扫描所有数据，因此，效率不高。以下例子描述了，在表 page_view 的 userid 列上进行分桶的例子：

CREATE TABLE page_view(

viewTime INT , userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User' )

COMMENT 'This is the page view table' PARTITIONED BY (dt STRING, country STRING)CLUSTERED BY (userid) SORTED BY (viewTime) INTO 32 BUCKETS ROW

FORMAT DELIMITEDFIELDS

TERMINATED BY '1' COLLECTION ITEMS TERMINATED BY '2' MAP KEYS TERMINATED BY '3' STORED AS SEQUENCEFILE;

以上例子，通过一个 userid 的哈希函数，表被分成32个桶。在每个桶中的数据，是以 viewTime 升序进行存储。这样组织数据允许用户有效地在这n个桶上进行抽样。合适的排序使得内部操作充分利用熟悉的数据结构来进行更加有效的查询。

在这个例子， CLUSTERED BY 指定列进行分桶，以及创建多少个桶。行格式分隔符指定在hive表中，行如何存储。在这种分隔符情况下，指定了字段是如何结束，集合项（数组和map）如何结束，以及map的key是如何结束的。对于以上例子的 ROW FORMAT 的值和 STORED AS 表示系统默认值。

4） External Table（外部表）

hive>create external table external_table1 (key string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘\t’

location ‘/home/external’;

在HDFS创建目录/home/external 

#hadoop fs -put /home/external_table.dat /home/external

外部表指向已经在 HDFS 中存在的数据，可以创建 Partition。它和 内部表 在元数据的组织上是相同的，而实际数据的存储则有较大的差异。

内部表 创建过程和数据加载过程（这两个过程可以在同一个语句中完成），在加载数据的过程中，实际数据会被移动到数据仓库目录中；之后对数据对访问将会直接在数据仓库目录中完成。删除表时，表中的数据和元数据将会被同时删除。

外部表 只有一个过程，加载数据和创建表同时完成，并不会移动到数据仓库目录中，只是与外部数据建立一个链接。当删除一个 外部表 时，仅删除该链接创建一个外部表的相关操作 。

2.2浏览表和分区

SHOW TABLES;

列出数据库里的所有的表，也可以这么浏览：

SHOW TABLES 'page.*';

这样将会列出以 page 开头的表，模式遵循Java正则表达式语法。

SHOW PARTITIONS page_view;

列出表的分区。如果表没有分区，则抛出错误。

DESCRIBE page_view;

列出表的列和列的类型。

DESCRIBE EXTENDED page_view;

列出表的列和表的其他属性。这会打印很多信息，且输出的风格不是很友好，通常用于调试。

DESCRIBE EXTENDED page_view PARTITION (ds='2016-08-08');

列出列和分区的所有属性。这也会打印出许多信息，通常也是用于调试。

2.3修改表

对已有的表进行重命名。如果表的新名字存在，则报错：

ALTER TABLE old_table_name RENAME TO new_table_name;

对已有表的列名进行重命名。要确保使用相同的列类型，且要包含对每个已存在列的一个入口(也就是说，就算不修改其他列的列名，也要把此列另上，否则，此列会丢失）。

ALTER TABLE old_table_name REPLACE COLUMNS (col1 TYPE, ...);

对已有表增加列：

ALTER TABLE tab1 ADD COLUMNS (c1 INT COMMENT 'a new int column', c2 STRING DEFAULT 'def val');

注意： 

模式的改变（例如增加列），保留了表的老分区，以免它是一个分区表。所有对这些列或老分区的查询都会隐式地返回一个 null 值或这些列指定的默认值。

2.4删除表和分区

删除表是相当，表的删除会删除已经建立在表上的任意索引。相关命令是：

DROP TABLE pv_users;

ALTER TABLE pv_users DROP PARTITION (ds='2016-08-08')

2.5加载数据

要加载数据到Hive表有许多种方式。用户可以创建一个“外部表”来指向一个特定的HDFS路径。用这种方法，用户可以使用HDFS put 或 copy 命令，复制一个文件到指定的位置，并且附上相应的行格式信息创建一个表指定这个位置。一旦完成，用户就可以转换数据和插入他们到任意其他的Hive表中。例如，如果文件 /tmp/pv_2016-06-08.txt 包含逗号分隔的页面访问记录。这需要以合适的分区加载到表 page_view ，以下命令可以完成这个目标：

CREATE EXTERNAL TABLE page_view_stg(viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User', country STRING COMMENT 'country of origination')COMMENT 'This is the staging page view table'ROW FORMAT DELIMITED FIELDS TERMINATED BY '44' LINES TERMINATED BY '12'STORED AS TEXTFILELOCATION '/user/data/staging/page_view'; hadoop dfs -put /tmp/pv_2016-06-08.txt /user/data/staging/page_view FROM page_view_stg pvsINSERT OVERWRITE TABLE page_view PARTITION(dt='2016-06-08', country='US')SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ipWHERE pvs.country = 'US';

其中，‘44’是逗号的ASCII码，‘12’是换页符（NP from feed,new page）。 null 是作为目标表中的数组和map类型插入，如果指定了合适的行格式，这些值也可以来自外部表。

overwrite即为重写的意思， 指定了OVERWRITE，会有以下效果：

•目标表（或者分区）中的内容（如果有）会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

•如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

如果在HDFS上有一些历史数据，用户想增加一些元数据，以便于可以使用Hive来查询和操纵这些数据，这个方法是很有用的。

另外，系统也支持直接从本地文件系统上加载数据到Hive表。表的格式与输入文件的格式需要相同。如果文件 /tmp/pv_2016-06-08 包含了 US 数据，然后我们不需要像前面例子那样的任何筛选，这种情况的加载可以使用以下语法完成：

LOAD DATA LOCAL INPATH /tmp/pv_2016-06-08_us.txt INTO TABLE page_view PARTITION(date='2016-06-08', country='US')

路径参数可以是一个目录（这种情况下，目录下的所有文件将被加载），一个文件，或一个通配符（这种情况下，所有匹配的文件会上传）。如果参数是目录，它不能包含子目录。同样，通配符只匹配文件名。

在输入文件 /tmp/pv_2016-06-08.txt 非常大的情况下，用户可以采用并行加载数据的方式（使用Hive的外部工具）。只要文件在HDFS上－以下语法可以用于加载数据到Hive表：

LOAD DATA INPATH '/user/data/pv_2016-06-08_us.txt' INTO TABLE page_view PARTITION(date='2016-06-08', country='US')

对于这个例子，我们假设数组和map在文件中的值为 null 。

2.6查询和插入数据

2.6.1简单的查询

对于所有的活跃用户，可以使用以下查询格式：

INSERT OVERWRITE TABLE user_activeSELECT user.*FROM userWHERE user.active = 1;

注意：不像SQL，我们老是插入结果到表中。

2.6.2基于查询的分区

在一个查询中，要使用什么分区，是由系统根据 where 在分区列上条件自动的决定。例如，为了获取所有2008年3月份，从域名 xyz.com 过来的page_views，可以这么写查询：

INSERT OVERWRITE TABLE xyz_com_page_views SELECT page_views.* FROM page_views WHERE page_views. date >= '2008-03-01' AND page_views. date <= '2008-03-31' AND page_views.referrer_url like '%xyz.com' ;

注意：在这里使用的 page_views.date 是用 PARTITIONED BY(date DATATIME, country STRING) 定义的.如果你的分区命名不一样，那么不要指望 .date 能够做你想做的事情，即无法获得分区的优势。

2.6.3连接

表的连接可以使用以下命令：

INSERT OVERWRITE TABLE pv_users SELECT pv.*, u.gender, u.age FROM user u JOIN page_view pv ON (pv.userid = u.id) WHERE pv. date = '2008-03-03' ;

想实现外连接，用户可以使用 LEFT OUTER , RIGHT OUTER 或 FULL OUTER 关键词来指示不同的外连接（左保留，右保留或两端都保留）。

注意：Hive只支持 equi-joins 。所以，把最大的表放在 join 的最右边，可以得到最好的性能。

2.6.4聚合

统计用户每个性别的人数，可以使用以下查询：

INSERT OVERWRITE TABLE pv_gender_sum SELECT pv_users.gender, count ( DISTINCT pv_users.userid) FROM pv_users GROUP BY pv_users.gender;

2.6.5多表/文件插入

聚合或简单查询的输出可以插入到多个表中，或者甚至是HDFS文件（能够使用HDFS工具进行操纵）。例如，如果沿用前面的“性别分类”，例子如下：

FROM pv_users INSERT OVERWRITE TABLE pv_gender_sum SELECT pv_users.gender, count_distinct(pv_users.userid) GROUP BY pv_users.gender INSERT OVERWRITE DIRECTORY '/user/data/tmp/pv_age_sum' SELECT pv_users.age, count_distinct(pv_users.userid) GROUP BY pv_users.age;

第一个插入语句将结果插入到Hive表中，而第二个插入语句是将结果写到HDFS文件。

2.6.6动态分区插入

在前面的例子中，我们知道，在插入语句中，只能有一个分区。如果我们想加载到多个分区，我们必须像以下描述来使用多条插入语句：

FROM page_view_stg pvs INSERT OVERWRITE TABLE page_view PARTITION(dt= '2008-06-08' , country= 'US' ) SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null , null , pvs.ip WHERE pvs.country = 'US' INSERT OVERWRITE TABLE page_view PARTITION(dt= '2008-06-08' , country= 'CA' ) SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null , null , pvs.ip WHERE pvs.country = 'CA' INSERT OVERWRITE TABLE page_view PARTITION(dt= '2008-06-08' , country= 'UK' ) SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null , null , pvs.ip WHERE pvs.country = 'UK' ;

为了加载数据到全部的 country 分区到指定的日期。我们必须在输入数据中为每个 country 增加一条插入语句。这是非常不方便的，因为我们需要提前创建且知道已存在哪些 country 分区列表。如果哪天这些 country 列表变了，我们必须修改我们的插入语句，也应该创建相应的分区。这也是非常低效的，因为每个插入语句可能都是转换成一个MapReduce作业。

动态分区插入（Dynamic-partition insert)(或multi-partition插入) 就是为了解决以上问题而设计的，它通过动态地决定在扫描数据的时候，哪些分区应该创建和填充。这个新的特征是在版本0.6.0加入的。在动态分区插入中，输入列被评估，这行应该插入到哪个分区。如果分区没有创建，它将自动创建这个分区。使用这个特征，我们仅仅需要插入语句来创建和填充所有需要的分区。另外，因为只有一个插入语句，相应的也只有一个MapReduce作业。相比多个插入语句的情况，这将显著地提高性能且降低Hadoop集群负载。

以下是使用一个插入语句，加载数据到所有 country 分区的例子：

FROM page_view_stg pvs INSERT OVERWRITE TABLE page_view PARTITION(dt= '2008-06-08' , country) SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null , null , pvs.ip, pvs.country

与多条插入语句相比，动态分区插入有一些语法上的不同： 

country 出现在 PARTITION 后面，但是没有具体的值 。这种情况， country 就是一个动态分区列 。另一方面， dt 有一个值，这意味着它是一个静态的分区列。如果一个列是动态分区列，它的值将会使用输入列的值。目前，我们仅仅允许在分区条件的最后一列放置动态分区列，因为分区列的顺序，指示了它的层级次序（意味着 dt 是根分区， country 是子分区）。我们不能这样指定分区（dt,country=’US’)，因为这表示，我们需要更新所有的日期的分区且它的 country 子分区是‘US’。

一个额外的 pvs.country 列被加入在查询语句中。 这对动态分区列来说，相当于输入列。注意：对于静态分区列，我们不需要添加一个输入列，因为在PARTITION语句中，它的值已经知道。注意：动态分区列的值（不是名字）查出来是有序的，且是放在select语句的最后。

动态分区插入的语义： 

对于动态分区列，当已经此分区时，（例如， country='CA' 已存在 dt 根分区下面）如果动态分区插入与输入数据中相同的值（’CA’），它将会被重写（overwritten)。

2.6.7插入到本地文件

在某些场合，我们需要把输出写到一个本地文件，以便于能用excel表格打开。这可以使用以下命令：

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/pv_gender_sum' SELECT pv_gender_sum.* FROM pv_gender_sum;

2.6.8抽样

抽样语句允许用户对数据抽样查询，而不是全表查询。当前，抽样是对那些在 CREATE TABLE 语句的 CLUSTERED BY 修饰的列上。以下例子，我们从表 pv_gender_sum 表中的32个桶中，选择第3个桶。

INSERT OVERWRITE TABLE pv_gender_sum_sample SELECT pv_gender_sum.* FROM pv_gender_sum TABLESAMPLE(BUCKET 3 OUT OF 32 );

通常， TABLESAMPLE 的语法像这样：

TABLESAMPLE(BUCKET x OUT OF y)

这个 y 必须是桶的数量的因子或倍数，桶的数量是在创建表的时候指定的。抽样所选的桶由桶大小，y和x共同决定。如果y和桶大小相等，则抽样所选的桶是x对y的求模结果。

2.6.9union all

这个语言也支持 union all ，如果假设我们有两个不同的表，分别用来记录用户发布的视频和用户发布的评论，以下例子是一个union all 的结果与用户表再连接的查询：

INSERT OVERWRITE TABLE actions_users SELECT u.id, actions. date FROM ( SELECT av.uid AS uid FROM action_video av WHERE av. date = '2008-06-03' UNION ALL SELECT ac.uid AS uid FROM action_comment ac WHERE ac. date = '2008-06-03' ) actions JOIN users u ON (u.id = actions.uid);

2.6.10数组操作

表的数组列可以这样：

CREATE TABLE array_table (int_array_column ARRAY< INT >);

假设pv.friends 是类型 ARRAY<INT> （也就是一个整型数组），用户可以通过索引号获取数组中特定的元素，如下：

SELECT pv.friends[ 2 ] FROM page_views pv;

这个查询得到的是pv.friends里的第三个元素。

用户也可以使用函数 size 来获取数组的长度，如下：

SELECT pv.userid, size (pv.friends) FROM page_view pv;

2.6.11Map（关联数组）操作

Map提供了类似于关联数组的集合。这样的结构不仅可以由程序创建。我们也将很快可以继承这个。假设pv.properties是类型 map<String,String> ，如下：

INSERT OVERWRITE page_views_map SELECT pv.userid, pv.properties[ 'page type' ] FROM page_views pv;

这将查询表 page_views 的‘page_type‘属性。

与数组相似，也可以使用函数 size 来获取map的大小：

SELECT size (pv.properties) FROM page_view pv;

2.6.12定制Map/Reduce脚本

通过使用Hive语言原生支持的特征，用户可以插入他们自己定制的mapper和reducer在数据流中。例如，要运行一个定制的mapper脚本 script-map_script 和reducer脚本 script-reduce_script )，用户可以执行以下命令，使用 TRANSFORM 来嵌入mapper和reducer脚本。

注意：在执行用户脚本之前，表的列会转换成字符串，且由 TAB 分隔，用户脚本的标准输出将会被作为以 TAB 分隔的字符串列。用户脚本可以输出调试信息到标准错误输出，这个信息也将显示hadoop的详细任务页面上。

FROM ( FROM pv_users MAP pv_users.userid, pv_users. date USING 'map_script' AS dt, uid CLUSTER BY dt) map_output INSERT OVERWRITE TABLE pv_users_reduced REDUCE map_output.dt, map_output.uid USING 'reduce_script' AS date , count;

map脚本样本（weekday_mapper.py)

import sysimport datetime for line in sys. stdin : line = line .strip() userid, unixtime = line . split ( '\t' ) weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday() print ',' .join([userid, str(weekday)])

当然，对于那些常见的select转换，MAP和REDUCE都是“语法糖”。内部查询也可以写成这样：

SELECT TRANSFORM(pv_users.userid, pv_users. date ) USING 'map_script' AS dt, uid CLUSTER BY dt FROM pv_users;

2.6.13Co-Groups

在使用map/reduce的群体中， cogroup 是相当常见的操作，它是将来自多个表的数据发送到一个定制的reducer，使得行由表的指定列的值进行分组。在Hive的查询语言中，可以使用以下方式，通过使用 union all 和 cluster by 来实现此功能。假设我们想对来自表 actions_video 和 action_comment 的行对 uid 列进行分组，且需要发送他们到 reducer_script 定制的reducer，可以使用以下语法：

FROM ( FROM ( FROM action_video av SELECT av.uid AS uid, av.id AS id, av. date AS date UNION ALL FROM action_comment ac SELECT ac.uid AS uid, ac.id AS id, ac. date AS date ) union_actions SELECT union_actions.uid, union_actions.id, union_actions. date CLUSTER BY union_actions.uid) map INSERT OVERWRITE TABLE actions_reduced SELECT TRANSFORM(map.uid, map.id, map. date ) USING 'reduce_script' AS (uid, id, reduced_val);

2.6.14特性

hive不支持INSERT INTO, UPDATE, DELETE操作，这样的话，就不要很复杂的锁机制来读写数据。

hive支持将转换后的数据直接写入不同的表，还能写入分区、hdfs和本地目录。这样能免除多次扫描输入表的开销。

3.Hive的三种Join方式

3.1Common/Shuffle/Reduce Join

Reduce Join在Hive中也叫Common Join或Shuffle Join

如果两边数据量都很大，它会进行把相同key的value合在一起，正好符合我们在sql中的join，然后再去组合

3.2Map Join

1） 大小表连接：

如果一张表的数据很大，另外一张表很少(<1000行)，那么我们可以将数据量少的那张表放到内存里面，在map端做join。

Hive支持Map Join，用法如下

select /*+ MAPJOIN(time_dim) */ count ( 1 ) from store_sales join time_dim on (ss_sold_time_sk = t_time_sk)

2） 需要做不等值join操作（a.x < b.y 或者 a.x like b.y等）

这种操作如果直接使用join的话语法不支持不等于操作，hive语法解析会直接抛出错误

如果把不等于写到where里会造成笛卡尔积，数据异常增大，速度会很慢。甚至会任务无法跑成功~

根据mapjoin的计算原理，MapJoin会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配。这种情况下即使笛卡尔积也不会对任务运行速度造成太大的效率影响。

而且hive的where条件本身就是在map阶段进行的操作，所以在where里写入不等值比对的话，也不会造成额外负担。

select /*+ MAPJOIN(a) */ a.start_level, b.* from dim_level a join ( select * from test) b where b.xx>=a.start_level and b.xx<end_level;

3） MAP JOIN 结合 UNION ALL

原始sql：

select a.*, coalesce (c.categoryid,’NA’) as app_category from ( select * from t_aa_pvid_ctr_hour_js_mes1) a left outer join ( select * fromt_qd_cmfu_book_info_mes) c on a.app_id=c.book_id;

速度很慢，老办法，先查下数据分布:

select * from (selectapp_id, count ( 1 ) cntfromt_aa_pvid_ctr_hour_js_mes1 group by app_id) t order by cnt DESC limit 50 ;

数据分布如下：

NA 6173701292 1182933141 40673814d 20151236b 1846306s 11242465 6752408 6422316 611104t 5969734 5794733 4895167 4759999 373395107580 10508

我们可以看到除了NA是有问题的异常值，还有appid=1~9的数据也很多，而这些数据是可以关联到的，所以这里不能简单的随机函数了。而t_qd_cmfu_book_info_mes这张app库表，又有几百万数据，太大以致不能放入内存使用mapjoin。

解决方：首先将appid=NA和1到9的数据存入一组，并使用mapjoin与维表（维表也限定appid=1~9，这样内存就放得下了）关联，而除此之外的数据存入另一组，使用普通的join，最后使用union all 放到一起。

select a.*, coalesce (c.categoryid,’NA’) as app_category from --if app_id isnot number value or <=9,then not join ( select * fromt_aa_pvid_ctr_hour_js_mes1 where cast (app_id asint)> 9 ) a left outer join ( select * fromt_qd_cmfu_book_info_mes where cast (book_id asint)> 9 ) c on a.app_id=c.book_id union all select /*+ MAPJOIN(c)*/ a.*, coalesce (c.categoryid,’NA’) as app_category from – if app_id<= 9 , use map join ( select * fromt_aa_pvid_ctr_hour_js_mes1 where coalesce ( cast (app_id as int ),- 999 )<= 9 ) a left outer join ( select * fromt_qd_cmfu_book_info_mes where cast (book_id asint)<= 9 ) c --if app_id is notnumber value,then not join on a.app_id=c.book_id

设置：

当然也可以让hive自动识别，把join变成合适的Map Join如下所示

注：当设置为true的时候，hive会自动获取两张表的数据，判定哪个是小表，然后放在内存中

set hive.auto. convert . join = true ; select count (*) from store_sales join time_dim on (ss_sold_time_sk = t_time_sk)

3.3SMB(Sort-Merge-Buket) Join

场景：

大表对小表应该使用MapJoin，但是如果是大表对大表，如果进行shuffle，那就要人命了啊，第一个慢不用说，第二个容易出异常，既然是两个表进行join，肯定有相同的字段吧。

tb_a - 5亿（按排序分成五份，每份1亿放在指定的数值范围内,类似于分区表）

a_id

100001 ~ 110000 - bucket-01-a -1亿

110001 ~ 120000

120001 ~ 130000

130001 ~ 140000

140001 ~ 150000

tb_b - 5亿（同上，同一个桶只能和对应的桶内数据做join）

b_id

100001 ~ 110000 - bucket-01-b -1亿

110001 ~ 120000

120001 ~ 130000

130001 ~ 140000

140001 ~ 150000

注：实际生产环境中，一天的数据可能有50G（举例子可以把数据弄大点，比如说10亿分成1000个bucket）。

原理：

在运行SMB Join的时候会重新创建两张表，当然这是在后台默认做的，不需要用户主动去创建

4.Hive事务使用建议

1. 传统数据库中有三种模型隐式事务、显示事务和自动事务。在目前Hive对事务仅支持自动事务，因此Hive无法通过显示事务的方式对一个操作序列进行事务控制。
2. 传统数据库事务在遇到异常情况可自动进行回滚，目前Hive无法支持ROLLBACK。
3. 传统数据库中支持事务并发，而Hive对事务无法做到完全并发控制,多个操作均需要获取WRITE的时候则这些操作为串行模式执行（在测试用例中"delete同一条数据的同时update该数据"，操作是串行的且操作完成后数据未被删除且数据被修改）未保证数据一致性。
4. Hive的事务功能尚属于实验室功能，并不建议用户直接上生产系统，因为目前它还有诸多的限制，如只支持ORC文件格式，建表必须分桶等，使用起来没有那么方便，另外该功能的稳定性还有待进一步验证。
5. CDH默认开启了Hive的Concurrency功能，主要是对并发读写的的时候通过锁进行了控制。所以为了防止用户在使用Hive的时候，报错提示该表已经被lock，对于用户来说不友好，建议在业务侧控制一下写入和读取，比如写入同一个table或者partition的时候保证是单任务写入，其他写入需控制写完第一个任务了，后面才继续写，并且控制在写的时候不让用户进行查询。另外需要控制在查询的时候不要允许有写入操作。
6. 如果对于数据一致性不在乎，可以完全关闭Hive的Concurrency功能关闭，即设置hive.support.concurrency为false，这样Hive的并发读写将没有任何限制。

5.Hive 窗口函数、分析函数

1 分析函数：用于等级、百分点、n分片等

Ntile 是Hive很强大的一个分析函数。

• 可以看成是：它把有序的数据集合 平均分配 到 指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
• 语法是：

     ntile (num)  over ([partition_clause]  order_by_clause)  as  your_bucket_num

•    然后可以根据桶号，选取前或后 n分之几的数据。

例子：

    给了用户和每个用户对应的消费信息表， 计算花费前50%的用户的平均消费；

-- 把用户和消费表，按消费下降顺序平均分成2份 drop table if exists test_by_payment_ntile; create table test_by_payment_ntile as select nick, payment , NTILE( 2 ) OVER ( ORDER BY payment desc ) AS rn from test_nick_payment; -- 分别对每一份计算平均值，就可以得到消费靠前50%和后50%的平均消费 select 'avg_payment' as inf, t1.avg_payment_up_50 as avg_payment_up_50, t2.avg_payment_down_50 as avg_payment_down_50 from ( select avg (payment) as avg_payment_up_50 from test_by_payment_ntile where rn = 1 )t1 join ( select avg (payment) as avg_payment_down_50 from test_by_payment_ntile where rn = 2 )t2 on (t1.dp_id = t2.dp_id);

 

Rank，Dense_Rank, Row_Number

SQL很熟悉的3个组内排序函数了。语法一样：

R()  over  (partion  by  col1...  order  by  col2...  desc/asc)

select class1, score, rank() over (partition by class1 order by score desc ) rk1, dense_rank() over (partition by class1 order by score desc ) rk2, row_number() over (partition by class1 order by score desc ) rk3 from zyy_test1;

如上图所示，rank  会对相同数值，输出相同的序号，而且下一个序号不间断；

       dense_rank  会对相同数值，输出相同的序号，但下一个序号，间断

       row_number 会对所有数值输出不同的序号，序号唯一连续；

2. 窗口函数 Lag, Lead, First_value,Last_value

Lag, Lead

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值, 与LAG相反

-- 组内排序后，向后或向前偏移

-- 如果省略掉第三个参数，默认为NULL，否则补上。

select dp_id, mt, payment, LAG(mt, 2 ) over (partition by dp_id order by mt) mt_new from test2;

 

-- 组内排序后，向后或向前偏移

-- 如果省略掉第三个参数，默认为NULL，否则补上。

select dp_id, mt, payment, LEAD(mt, 2 , '1111-11' ) over (partition by dp_id order by mt) mt_new from test2;

 

FIRST_VALUE, LAST_VALUE

first_value:  取分组内排序后，截止到当前行，第一个值

last_value:  取分组内排序后，截止到当前行，最后一个值

-- FIRST_VALUE 获得组内当前行往前的首个值-- LAST_VALUE 获得组内当前行往前的最后一个值-- FIRST_VALUE(DESC) 获得组内全局的最后一个值 select dp_id, mt, payment, FIRST_VALUE(payment) over (partition by dp_id order by mt) payment_g_first, LAST_VALUE(payment) over (partition by dp_id order by mt) payment_g_last, FIRST_VALUE(payment) over (partition by dp_id order by mt desc ) payment_g_last_global from test2 ORDER BY dp_id,mt;

 

6.hive与hbase的区别与联系

共同点：

1.hbase与hive都是架构在hadoop之上的。都是用hadoop作为底层存储

区别：

2.Hive是建立在Hadoop之上为了减少MapReduce jobs编写工作的批处理系统，HBase是为了支持弥补Hadoop对实时操作的缺陷的项目 。

3.想象你在操作RMDB数据库，如果是全表扫描，就用Hive+Hadoop,如果是索引访问，就用HBase+Hadoop 。

4.Hive query就是MapReduce jobs可以从5分钟到数小时不止，HBase是非常高效的，肯定比Hive高效的多。

5.Hive本身不存储和计算数据，它完全依赖于HDFS和MapReduce，Hive中的表纯逻辑。

6.hive借用hadoop的MapReduce来完成一些hive中的命令的执行

7.hbase是物理表，不是逻辑表，提供一个超大的内存hash表，搜索引擎通过它来存储索引，方便查询操作。

8.hbase是列存储。

9.hdfs作为底层存储，hdfs是存放文件的系统，而Hbase负责组织文件。

10.hive需要用到hdfs存储文件，需要用到MapReduce计算框架。

7.Hive的数据存储

Hive的数据分为表数据和元数据，表数据是Hive中表格（table）具有的数据；而元数据是用来存储表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。下面分别来介绍。

Hive是基于Hadoop分布式文件系统的，它的数据存储在Hadoop分布式文件系统中。Hive本身是没有专门的数据存储格式，也没有为数据建立索引，只需要在创建表的时候告诉Hive数据中的列分隔符和行分隔符，Hive就可以解析数据。所以往Hive表里面导入数据只是简单的将数据移动到表所在的目录中（如果数据是在HDFS上；但如果数据是在本地文件系统中，那么是将数据复制到表所在的目录中）。

　　Hive中主要包含以下几种数据模型：Table（表），External Table（外部表），Partition（分区），Bucket（桶）（本博客会专门写几篇博文来介绍分区和桶）。

　　 1、表 ：Hive中的表和关系型数据库中的表在概念上很类似，每个表在HDFS中都有相应的目录用来存储表的数据，这个目录可以通过${HIVE_HOME}/conf/hive-site.xml配置文件中的hive.metastore.warehouse.dir属性来配置，这个属性默认的值是/user/hive/warehouse（这个目录在HDFS上），我们可以根据实际的情况来修改这个配置。如果我有一个表wyp，那么在HDFS中会创建/user/hive/warehouse/wyp目录（这里假定hive.metastore.warehouse.dir配置为/user/hive/warehouse）；wyp表所有的数据都存放在这个目录中。这个例外是外部表。

　　 2、外部表 ：Hive中的外部表和表很类似，但是其数据不是放在自己表所属的目录中，而是存放到别处，这样的好处是如果你要删除这个外部表，该外部表所指向的数据是不会被删除的，它只会删除外部表对应的元数据；而如果你要删除表，该表对应的所有数据包括元数据都会被删除。

　　 3、分区 ：在Hive中，表的每一个分区对应表下的相应目录，所有分区的数据都是存储在对应的目录中。比如wyp表有dt和city两个分区，则对应dt=20131218,city=BJ对应表的目录为/user/hive/warehouse/dt=20131218/city=BJ，所有属于这个分区的数据都存放在这个目录中。

　　 4、桶 ：对指定的列计算其hash，根据hash值切分数据，目的是为了并行，每一个桶对应一个文件（注意和分区的区别）。比如将wyp表id列分散至16个桶中，首先对id列的值计算hash，对应hash值为0和16的数据存储的HDFS目录为：/user/hive/warehouse/wyp/part-00000；而hash值为2的数据存储的HDFS 目录为：/user/hive/warehouse/wyp/part-00002。

表是在数据库下面，而表里面又要分区、桶、倾斜的数据和正常的数据等；分区下面也是可以建立桶的。

Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。 由于Hive的元数据需要不断的更新、修改，而HDFS系统中的文件是多读少改的，这显然不能将Hive的元数据存储在HDFS中。目前Hive将元数据存储在数据库中，如Mysql、Derby中。