Hive语法详解
一.Hive语法详解
Hive 是基于Hadoop 构建的一套数据仓库分析系统，是一个用来搭建数仓的工具。它提供了丰富的SQL查询方式来分析存储在Hadoop分布式文件系统中的数据，可以将结构化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行，通过自己的SQL 去查询分析需要的内容，这套SQL 简称Hive SQL，使不熟悉mapreduce 的用户很方便的利用SQL 语言查询，汇总，分析数据。而mapreduce开发人员可以把已写的mapper 和reducer 作为插件来支持Hive 做更复杂的数据分析。
它与关系型数据库的SQL 略有不同,但支持了绝大多数的语句如DDL、DML 以及常见的聚合函数、连接查询、条件查询。HIVE不适合用于联机事务处理，也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业。
HIVE的特点：可伸缩（在Hadoop的集群上动态的添加设备），可扩展，容错，输入格式的松散耦合。
1.安装和配置
可以通过下载tarball来安装Hive的稳定版本，也可以从中下载源代码构建Hive。
1.1要求
(1).Java 1.7
注意： Hive版本 1.2 以后需要Java 1.7或更高版本。Hive版本0.14到1.1也适用于Java 1.6。强烈建议用户开始使用Java 1.8（参见 HIVE-8607）。
(2).Hadoop 2.x（首选），1.x（Hive 2.0.0以后版本不支持）。
Hive版本高达0.13也支持Hadoop 0.20.x，0.23.x。
(3).Hive常用于生产Linux和Windows环境。Mac是一种常用的开发环境。本文档中的说明适用于Linux和Mac。在Windows上使用它需要稍微不同的步骤。
1.2从稳定版本安装Hive
首先从一个Apache下载镜像下载最新的Hive稳定版本（请参阅Hive版本）。
接下来你需要解压缩tarball。这将导致创建一个名为hive-x.y.z （其中x.y.z是版本号）的子目录：
$ tar -xzvf hive-xyztar.gz
将环境变量设置HIVE_HOME为指向安装目录：
$ cd hive-xyz
$ export HIVE_HOME = {{pwd}}
最后，添加$HIVE_HOME/bin到你的PATH：
$ export PATH = $ HIVE_HOME / bin：$ PATH
1.3从Source构建Hive
最新的Hive代码的Hive GIT存储库位于:
git clone https://git-wip-us.apache.org/repos/asf/hive.git( 主分支）。
所有发行版本都在名为“branch-0.＃”或“branch-1.＃”的分支或即将发布的“branch-2.＃”中，但0.8.1版本的分支在“branch-0.8-r2”中”。任何具有其他名称的分支都是正在进行中的功能分支。有关 详细信息，请参阅 了解Hive分支。
从0.13开始，Hive是使用Apache Maven构建的。
在master上编译Hive
要从主分支构建当前的Hive代码：
$ git clone https://git-wip-us.apache.org/repos/asf/hive.git
$ cd hive
$ mvn clean package -Pdist
$ cd packaging/target/apache-hive-{version}-SNAPSHOT-bin/apache-hive-{version}-SNAPSHOT-bin
$ ls
LICENSE
NOTICE
README.txt
RELEASE_NOTES.txt
bin/ (all the shell scripts)
lib/ (required jar files)
conf/ (configuration files)
examples/ (sample input and query files)
hcatalog / (hcatalog installation)
scripts / (upgrade scripts for hive-metastore)
这里，{version}指的是当前的Hive版本。
如果使用Maven（mvn）构建Hive源代码，我们将引用目录“/packaging/target/apache-hive-{version}-SNAPSHOT-bin/apache-hive-{version}-SNAPSHOT-bin”作为
在branch-1上编译Hive
在branch-1中，Hive支持Hadoop 1.x和2.x.。你需要通过Maven配置文件指定要构建的Hadoop版本。要针对Hadoop 1.x构建，请使用配置文件hadoop-1; 用于Hadoop 2.x使用hadoop-2。例如，要针对Hadoop 1.x构建，上面的mvn命令变为：
$ mvn clean package -Phadoop-1，dist
在Hadoop 0.20上编译Hive之前的0.13
Hive 0.13之前使用Apache Ant构建。在Hadoop 0.20上构建旧版本的Hive：
$ svn co http://svn.apache.org/repos/asf/hive/branches/branch-{version} hive
$ cd hive
$ ant clean package
$ cd build/dist
ls
LICENSE
NOTICE
README.txt
RELEASE_NOTES.txt
bin/ (all the shell scripts)
lib/ (required jar files)
conf/ (configuration files)
examples/ (sample input and query files)
hcatalog / (hcatalog installation)
scripts / (upgrade scripts for hive-metastore)
如果使用Ant，我们将引用目录“ build/dist”作为
在Hadoop 0.23上编译Hive之前的0.13
要在Hadoop 0.23,2.0.0或其他版本的Ant中构建Hive，请使用适当的标志进行构建; 以下一些例子：
$ ant clean package -Dhadoop.version = 0.23.3 -Dhadoop-0.23.version = 0.23.3 -Dhadoop.mr.rev = 23
$ ant clean package -Dhadoop.version = 2.0.0-alpha -Dhadoop-0.23.version = 2.0.0-alpha -Dhadoop.mr.rev = 23
1.4运行Hive
Hive使用Hadoop，因此你必须在你的path路径中配置Hadoop或者export HADOOP_HOME=
此外，你必须使用下面的HDFS命令来创建/tmp和/user/hive/warehouse(也就是hive.metastore.warehouse.dir)，并将它们设置为chmod g+w，然后才能在hive中创建表。
$ $HADOOP_HOME/bin/hadoop fs -mkdir /tmp
$ $HADOOP_HOME/bin/hadoop fs -mkdir /user/hive/warehouse
$ $HADOOP_HOME/bin/hadoop fs -chmod g+w /tmp
$ $HADOOP_HOME/bin/hadoop fs -chmod g+w /user/hive/warehouse
你可能会发现设置它是有用的，尽管没有必要HIVE_HOME：
$ export HIVE_HOME=
运行Hive CLI
要使用shell中的Hive 命令行界面（CLI）：
$ $ HIVE_HOME/bin /hive
运行HiveServer2和Beeline
从Hive 2.1开始，我们需要在下面运行schematool命令作为初始化步骤。例如，我们可以使用“derby”作为db类型。
$ $ HIVE_HOME/bin/schematool -dbType
HiveServer2（在Hive 0.11中引入）有自己的名为Beeline的CLI 。HiveCLI现在已被弃用而不支持Beeline，因为它缺乏HiveServer2的多用户、安全性和其他功能。从shell运行HiveServer2和Beeline：
$ $ HIVE_HOME/bin/hiveserver2
$ $ HIVE_HOME/bin/beeline -u jdbc:hive2:// $ HS2_HOST:$ HS2_PORT
Beeline以HiveServer2的JDBC URL启动，这取决于HiveServer2启动的地址和端口。默认情况下是（localhost:10000），因此地址将类似于jdbc:hive2:// localhost:10000。
或者在相同的过程中启动Beeline和HiveServer2以进行测试，以获得与HiveCLI类似的用户体验：
$ $ HIVE_HOME/bin/beeline -u jdbc:hive2://
运行HCatalog
要在Hive版本0.11.0及更高版本中从shell运行HCatalog服务器：
$ $ HIVE_HOME/hcatalog/sbin/hcat_server.sh
要在Hive版本0.11.0及更高版本中使用HCatalog命令行界面（CLI）：
$ $ HIVE_HOME/hcatalog/bin/hcat
更多信息请参阅HCatalog手册中的Tarball HCatalog安装和HCatalog CLI。
运行WebHCat（Templeton）
要在Hive版本0.11.0及更高版本中从shell运行WebHCat服务器：
$ $ HIVE_HOME/hcatalog/sbin/webhcat_server.sh
有关更多信息，请参阅WebHCat手册中的WebHCat 安装。
1.5配置管理概述
默认情况下，Hive从
可以通过设置HIVE_CONF_DIR环境变量来更改Hive配置目录的位置。
配置变量可以通过
Log4j配置存储在
Hive配置是Hadoop之上的覆盖 - 它默认继承Hadoop配置变量。
可以通过以下方式操作Hive配置：
编辑hive-site.xml并在其中定义任何所需的变量（包括Hadoop变量）
使用set命令
使用以下语法调用Hive（不建议使用），Beeline或HiveServer2：
$ bin/hive --hiveconf x1=y1 --hiveconf x2=y2 //这将变量x1和x2分别设置为y1和y2
$ bin/hiveserver2 --hiveconf x1 = y1 --hiveconf x2 = y2 //这将服务器端变量x1和x2分别设置为y1和y2
$ bin/beeline --hiveconf x1 = y1 --hiveconf x2 = y2 //这将客户端变量x1和x2分别设置为y1和y2。
将HIVE_OPTS环境变量设置为“ --hiveconf x1=y1 --hiveconf x2=y2”，与上面的相同。
1.6运行时配置
Hive查询使用map-reduce查询执行，因此这些查询的行为可以由Hadoop配置变量控制。HiveCLI（不建议使用）和Beeline命令'SET'可用于设置任何Hadoop（或Hive）配置变量。例如：
beeline> SET mapred.job.tracker=myhost.mycompany.com:50030;
beeline> SET -v;
后者显示所有当前设置。如果没有该-v选项，则仅显示与基本Hadoop配置不同的变量。
1.7 Hive，Map-Reduce和Local-Mode
Hive编译器为大多数查询生成map-reduce作业。然后将这些作业提交给变量指示的Map-Reduce集群：
mapred.job.tracker
虽然这通常指向具有多个节点的map-reduce集群，但Hadoop还提供了一个非常好的选项，可以在用户的工作站上本地运行map-reduce作业。这对于在小型数据集上运行查询非常有用 - 在这种情况下，本地模式执行通常比将作业提交到大型集群要快得多。从HDFS透明地访问数据。相反，本地模式仅使用一个reducer运行，并且处理较大的数据集可能非常慢。
从版本0.7开始，Hive完全支持本地模式执行。要启用此功能，用户可以启用以下选项：
hive> SET mapreduce.framework.name = local;
此外，mapred.local.dir应指向在本地计算机上有效的路径（例如/tmp/
从版本0.7开始，Hive还支持一种模式，可以自动在本地模式下运行map-reduce作业。相关的选项有:
hive.exec.mode.local.auto #默认false
hive.exec.mode.local.auto.inputbytes.max
hive.exec.mode.local.auto.tasks.max
如果启用本地模式，Hive将分析查询中每个map-reduce作业的大小，如果满足以下阈值，则可以在本地运行它：
(1).作业的总输入大小低于：（hive.exec.mode.local.auto.inputbytes.max默认为128MB）
(2).map-tasks的总数小于：（hive.exec.mode.local.auto.tasks.max默认为4）
(3).所需的reduce任务总数为1或0。
因此，对于小数据集的查询或者对于具有多个map-reduce作业的查询，其中后续作业的输入要小得多（由于先前作业中的减少/过滤），可以在本地运行作业。
请注意，Hadoop服务器节点和运行Hive客户端的计算机的运行时环境可能存在差异（因为不同的jvm版本或不同的软件库）。在本地模式下运行时，这可能会导致意外的行为/错误。另请注意，本地模式执行是在一个单独的子jvm（Hive客户端）中完成的。如果用户愿意，可以通过hive.mapred.local.mem选项控制该子jvm的最大内存量。默认情况下设置为零，在这种情况下，Hive允许Hadoop确定子jvm的默认内存限制。
1.8 Hive Logging
Hive使用log4j进行日志记录。默认情况下，CLI不会将日志发送到控制台。默认日志记录级别WARN适用于0.13.0之前的Hive版本。从Hive 0.13.0开始，默认日志记录级别为INFO。
日志存储在目录中：/tmp/<user.name>:
/tmp/<user.name>/hive.log
注意：在本地模式下，在Hive 0.13.0之前，日志文件名是“ .log”而不是“ hive.log”。该错误在版本0.13.0中得到修复（参见HIVE-5528和HIVE-5676）。
要配置其他日志位置，请在$HIVE_HOME/conf/hive-log4j.properties日志文件中设置hive.log.dir。确保目录中设置了粘滞位（chmod 1777
hive.log.dir=<other_location>
如果用户愿意，可以通过添加如下参数将日志发送到控制台:
bin/hive --hiveconf hive.root.logger=INFO,console //for HiveCLI (deprecated)
bin/hiveserver2 --hiveconf hive.root.logger=INFO,console
或者，用户只能通过以下方式更改日志级别:
bin/hive --hiveconf hive.root.logger=INFO,DRFA //for HiveCLI (deprecated)
bin/hiveserver2 --hiveconf hive.root.logger=INFO,DRFA
记录的另一个选项是TimeBasedRollingPolicy（适用于Hive 1.1.0及更高版本，HIVE-9001），提供DAILY选项，如下所示：
bin/hive --hiveconf hive.root.logger=INFO,DAILY //for HiveCLI (deprecated)
bin/hiveserver2 --hiveconf hive.root.logger=INFO,DAILY
请注意，hive.root.logger通过'set'命令进行设置不会更改日志记录属性，因为它们是在初始化时确定的。
Hive还在每个Hive会话中存储查询日志/tmp/<user.name>/，但可以在hive-site.xml中使用该hive.querylog.location属性进行配置。从Hive 1.1.0开始，通过将属性hive.log.explain.output设置为true，可以在INFO级别记录EXPLAIN EXTENDED查询输出 。
在Hadoop集群上执行Hive期间的日志记录由Hadoop配置控制。通常，Hadoop将为每个映射生成一个日志文件，并减少存储在执行任务的集群计算机上的任务。可以通过单击Hadoop JobTracker Web UI中的“任务详细信息”页面来获取日志文件。
使用本地模式（mapreduce.framework.name=local）时，Hadoop/Hive执行日志将在客户端计算机上生成。从版本0.6开始 - Hive使用hive-exec-log4j.properties（仅在它缺失时回退到hive-log4j.properties）回退以确定默认情况下这些日志的传送位置。默认配置文件在本地模式下执行的每个查询生成一个日志文件并将其存储在其中/tmp/<user.name>。提供单独配置文件的目的是使管理员能够根据需要集中执行日志捕获（例如，在NFS文件服务器上）。执行日志对于调试运行时错误非常有用。
有关WebHCat错误和日志记录的信息，请参阅WebHCat手册中的错误代码和响应以及日志文件。
从Hive 2.1.0开始（使用 HIVE-13027），Hive默认使用Log4j2的异步记录器。将hive.async.log.enabled设置为false将禁用异步日志记录并回退到同步日志记录。异步日志记录可以显着提高性能，因为日志记录将在使用LMAX中断队列缓冲日志消息的单独线程中处理。有关优缺点，请参阅https://logging.apache.org/log4j/2.x/manual/async.html 。
HiveServer2日志
从Hive 0.14开始，客户端可以使用HiveServer2操作日志。有关配置，请参阅HiveServer2日志记录。
审核日志
对于每个Metastore API调用，都会从Hive Metastore服务器记录审核日志。
审计日志具有记录在Metastore日志文件中的函数和一些相关的函数参数。它记录在log4j的INFO级别，因此你需要确保启用INFO级别的日志记录（请参阅HIVE-3505 ）。日志条目的名称是“HiveMetaStore.audit”。
审计日志在Hive 0.7中添加了安全客户端连接（HIVE-1948 ），在Hive 0.10中添加了非安全连接（HIVE-3277 ;另见HIVE-2797 ）。
Perf Logger
为了通过PerfLogger获取性能指标，你需要为PerfLogger类（HIVE-12675 ）设置DEBUG级别日志记录。这可以通过在log4j属性文件中设置以下内容来实现:
log4j.logger.org.apache.hadoop.hive.ql.log.PerfLogger=DEBUG
如果记录器级别已通过hive.root.logger在root用户设置为DEBUG，则不需要上述设置来查看性能日志。
2.DDL操作(数据定义语言)
2.1建表操作
create [external] table [if not exists] tableName
[(col_name data_type [comment col_comment], ...)]
[comment table_comment]
[partitioned by (col_name data_type [comment col_comment], ...)]
[clustered by (col_name, col_name, ...)
[sorted by (col_name [asc|desc], ...)] into num_buckets buckets]
[row format row_format]
[stored as file_format]
[location hdfs_path]
说明：
1、CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
2、EXTERNAL 创建外部表。在建表的同时指定一个指向实际数据的路径（LOCATION）。Hive 创建内部表时会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
3、LIKE 允许用户复制现有的表结构，但是不复制数据。
4、ROW FORMAT DELIMITED 是用来设置创建的表在加载数据的时候，支持的列分隔符。Hive默认的分隔符是\001，属于不可见字符，这个字符在vi里是^A
5、STORED AS SEQUENCEFILE|TEXTFILE|RCFILE
如果文件数据是纯文本，可以使用STORED AS TEXTFILE;
如果数据需要压缩，使用STORED AS SEQUENCEFILE。
6、CLUSTERED BY 对于每一个表或者分区，Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
7、LOCATION 指定加载数据路径(指定在hdfs上的位置).针对的extetnal外部表，要指定存储路径，不指定的话就使用默认路径。内部表不用指定，默认路径/user/hive/warehouse
补充:
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED,将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。可通过 show create table 库名.表名; 查看建表语句.
注意:
(1).表名和列名不区分大小写，但SerDe和属性名称区分大小写。
在Hive 0.12及更早版本中，表名和列名中只允许使用字母数字和下划线字符。在Hive0.13后来，列名可以包含任何的Unicode字符。在重音符飘号（`）中指定的任何列名都按字面处理。在反引号字符串中，使用双反引号（``）来表示反引号字符。反引号引用还允许对表和列标识符使用保留关键字。
(2).表和列注释是字符串文字（单引号）。
(3).在没有EXTERNAL子句的情况下创建的表称为内部表，由Hive管理其数据。要查明表是内部还是外部表，请在DESCRIBE EXTENDED table_name的输出中查找tableType 。
(4).TBLPROPERTIES子句允许你使用自己的元数据键/值对标记表定义。还存在一些预定义的表属性，例如由Hive自动添加和管理的last_modified_user和last_modified_time。其他预定义表属性包括：
(5).要为表指定数据库，请在CREATE TABLE语句之前（在Hive 0.6及更高版本中）发出USE database_name语句，或者使用数据库名称（在Hive 0.7及更高版本中使用“ ” ）限定表名。关键字“ ”可用于默认数据库。database_name.table.name
建表的三种方式
Hive创建表的方式（默认路径/user/hive/warehouse，也可以location指定，主要针对external表）
1、使用create命令创建一个新表,带分区
CREATE TABLE `FDM_SOR.mytest_deptaddr`(
`dept_no` int,
`addr` string,
`tel` string)
partitioned by(statis_date string )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
可以使用describe formatted mytest_deptaddr 查看建表相关的各种配置属性以及默认属性。从下面可以看出，在创建表时有很多属性，比如存储地址，存储格式等属性我们都没有直接配置，而是选择了系统默认的。
2、把一张表的某些字段抽取出来，创建成一张新表，使用as
create table mytest_tmp1
as
select * from FDM_SOR.mytest_deptaddr where statis_date='20180229';
注意：1.as会复制属性以及属性值到新的表中.(可通过添加where 1=0不复制数据)
2.使用as创建的表，并不会带原表的分区（分区丢失），包扣一些字段的约束等（可以通过describe formatted查看）
3.新表中会将原表的分区当做字段出现在新表中。
3、复制表结构，使用like创建表
create table mytest_tmp like FDM_SOR.mytest_deptaddr;
注意：该like只会复制表结构(包扣表的分区以及存储格式之类的,区别as).
创建内部表
默认情况下，Hive会创建内部表，其中文件、元数据和统计信息由内部Hive进程管理。内部表存储在hive.metastore.warehouse.dir路径属性下，默认情况下位于/user/hive/warehouse/databasename.db/tablename/ 文件夹路径中。location在表创建期间，属性可以覆盖默认位置。如果删除内部表或分区，则删除与该表或分区关联的数据和元数据。如果未指定PURGE选项，则数据将移动到回收站文件夹中一段时间。当Hive应该管理表的生命周期或生成临时表时使用内部表。
CREATE TABLE pokes (foo INT, bar STRING);
创建外部表
外部表描述外部文件的元数据/模式。外部表文件可以由Hive外部的进程访问和管理。外部表可以访问存储在Azure Storage Volumes（ASV）或远程HDFS位置等源中的数据。如果更改外部表的结构或分区，则可以使用MSCK REPAIR TABLE table_name语句来刷新元数据信息。
当文件已存在或位于远程位置时使用外部表，即使删除表，文件也应保留。
EXTERNAL关键字允许你创建表并提供LOCATION，以便Hive不使用此表的默认位置。如果你已经生成了数据，这会派上用场。删除EXTERNAL表时，表中的数据不会从文件系统中删除。
EXTERNAL表指向其存储的任何HDFS位置，而不是存储在配置属性指定的文件夹中hive.metastore.warehouse.dir。
CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
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 '\054'
STORED AS TEXTFILE
LOCATION '<hdfs_location>';
创建分区表
可以使用PARTITIONED BY子句创建分区表。表可以包含一个或多个分区列，并为分区列中的每个不同值组合创建单独的数据目录。此外，可以使用CLUSTERED BY列对表或分区进行分区，并且可以通过SORT BY列在该存储桶中对数据进行排序。这可以提高某些类型的查询的性能。例如:
CREATE TABLE par_table(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(date STRING, pos STRING)
ROW FORMAT DELIMITED '\t'
FIELDS TERMINATED BY '\n'
STORED AS SEQUENCEFILE;
如果在创建分区表时出现此错误：“FAILED：语义分析错误：列在分区列中重复”，这意味着你尝试将分区列包含在表本身的数据中。你可能已经定义了该列。但是，你创建的分区会生成一个可以查询的伪列，因此你必须将表列重命名为其他内容（用户不应该查询！）。
创建分桶表(Bucket)
分桶对应hdfs目录下的一个个文件,它是将1张大表进行hash(表行索引多分桶数hash,hash值相同的到同一个文件中去),将一份数据拆分成多份,优化查询效率.分桶和MapReduce的分区概念相似。例如:
create table bucketed_user(id int,name string)
cluster by(id) sorted by(name) into 4 buckets
row format delimited fields terminated by '\t' stored as textfile;
set hive.enforce.bucketing=true;
select * from bucketed_user tablesample(bucket 1 out of 2 on id);
bucket join
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
CLUSTERED BY和SORTED BY创建命令不会影响数据如何插入表中 - 只会影响它的读取方式。这意味着用户必须小心地通过指定减少器的数量等于桶的数量并在查询中使用CLUSTER BY和SORT BY命令来正确插入数据。
连接两个在(包含连接列)相同列上划分了桶的表，可以使用map端连接(map-side join)高效的实现。比如join操作。对于join操作两个表有一个相同的列，如果对这两个表都进行了桶操作，那么将保存相同列值的桶进行join操作就可以，可以大大减少join的数据量。
对于map端连接的情况，两个表以相同方式划分桶。处理左边表内某个桶的mapper知道右边表内相匹配的行在对应的桶内。因此，mapper只需要获取那个桶(这只是右边表内存储数据的一小部分)即可进行连接。这一优化方法并不一定要求两个表必须桶的个数相同，两个表的桶个数是倍数关系也可以。
创建临时表
版本信息:截至Hive 0.14.0。已创建为临时表的表仅对当前会话可见。数据将存储在用户的暂存目录中，并在会话结束时删除。
如果使用数据库中已存在的永久表的数据库/表名创建临时表，则在该会话中，对该表的任何引用都将解析为临时表，而不是永久表。如果不删除临时表或将其重命名为非冲突名称，用户将无法访问该会话中的原始表。
临时表不支持分区列，也不支持创建索引。
CREATE TEMPORARY TABLE list_bucket_multiple (col1 STRING, col2 int, col3 STRING);
显示命令
•show tables;
•show create table tableName; --显示建表语句
•show databases;
•show partitions ;
•show functions
•describe extended tableName dot col_name
2.2修改表结构
表操作
更改表名：
ALTER TABLE tableName RENAME TO new_tableName;
修改表属性：
ALTER TABLE tableName SET TBLPROPERTIES (property_name = property_value, property_name = property_value,... )
修改表注释:
ALTER TABLE tableName SET TBLPROPERTIES('comment' = new_comment);
改变表文件格式与组织:
• ALTER TABLE tableName SET FILEFORMAT file_format
• ALTER TABLE tableName CLUSTERED BY(userid) SORTED BY(viewTime)
INTO num_buckets BUCKETS
这个命令修改了表的物理存储属性
增加表的元数据信息:
ALTER TABLE tableName SET TBLPROPERTIES [property_name = property_value…..]
用户可以用这个命令向表中增加metadata
修改表存储位置:
alter table table_name set location 'viewfs://...'
修改表的字段分隔符 :
alter table table_name SET SERDEPROPERTIES ('field.delim' = '\t');
alter table table_name SET SERDEPROPERTIES ('colelction.delim' = '\t');
alter table table_name SET SERDEPROPERTIES ('mapkey.delim' = '\t');
alter table table_name SET SERDEPROPERTIES ('line.delim' = '\t');
注意：如果一个表带有分区，修改了表的schema后，只会对后续插入的分区有影响。之前的分区还是原来的列。因为hive把每个分区的schema信息单独记录在数据库了。所以需要把原来的分区全部drop掉，重新add。
列操作
官网添加列的语法:
ALTER TABLE table_name
[PARTITION partition_spec] -- (Note: Hive 0.14.0 and later)
ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
[CASCADE|RESTRICT] -- (Note: Hive 1.1.0 and later)
注意：默认模式为RESTRICT（即不修改元数据），cascade则同步修改元数据，这样才会重新刷新数据时添加的字段才会有值，不然刷新数据新添加的字段以前的数据都为null。
表添加一列：
ALTER TABLE tableName ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) ;
ADD是代表新增一字段，字段位置在所有列后面(partition列前)
REPLACE则是表示替换表中所有字段。
添加一列并增加列字段注释
ALTER TABLE tableName ADD COLUMNS (new_col2 INT COMMENT 'a comment');
删除列：
ALTER TABLE name DROP [COLUMN] column_name
修改列字、类型、位置、注释：
ALTER TABLE tableName CHANGE [COLUMN] col_old_name col_new_name
column_type [COMMENT col_comment] [FIRST|AFTER column_name]
这个命令可以修改表的列名、数据类型、列注释和列所在的位置顺序，FIRST将列放在第一列，AFTER将列放在col_name后面一列，但必须先存在该字段才能修改名字及指定位置.
分区操作
增加分区:
ALTER TABLE tableName
ADD [IF NOT EXISTS] PARTITION (partition_col = partition_col_value, ...)
[LOCATION 'location1'] partition_spec [ LOCATION 'location2' ] ...
partition_spec: PARTITION (partition_col = partition_col_value, ...)
删除分区:
ALTER TABLE tableName DROP partition_spec, partition_spec,...
2.3视图操作
创建视图（用于视图查询）
CREATE VIEW创建具有给定名称的视图。如果已存在具有相同名称的表或视图，则会引发错误。你可以使用IF NOT EXISTS跳过错误。
语法如下:
•CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ]
[COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)] AS SELECT
•增加视图,如果没有提供表名，视图列的名字将由定义的SELECT表达式自动生成;如果修改基本表的属性，视图中不会体现，无效查询将会失败.视图是只读的，不能用LOAD/INSERT/ALTER.
•如果未提供列名，则将从定义的SELECT表达式自动派生视图列的名称。(如果SELECT包含非混淆的标量表达式，例如x + y，则生成的视图列名称将以_C0，_C1等形式生成)重命名列时，还可以选择提供列注释。（注释不会自动从基础列继承）
•如果视图定义的SELECT表达式无效，则CREATE VIEW语句将失败。
请注意，视图是纯粹的逻辑对象，没有关联的存储（Hive支持从2.3.0版本开始支持的物化视图除外）当查询引用视图时，将评估视图的定义以生成一组行进一步处理查询。（这是一个概念性描述;实际上，作为查询优化的一部分，Hive可以将视图的定义与查询结合起来，例如将查询中的过滤器推送到视图中）
在创建视图时冻结视图的模式; 对基础表的后续更改（例如，添加列）将不会反映在视图的架构中。如果以不兼容的方式删除或更改基础表，则后续尝试查询无效视图将失败。
视图是只读的，不能用作LOAD/INSERT/ALTER的目标。
视图可能包含ORDER BY和LIMIT子句。
从Hive 0.13.0开始 ，视图的select语句可以包含一个或多个公用表表达式（CTE）。
关键信息源于多个复杂的关联表，可以建立视图来简化操作不用每次重复执行一段重复代码，因为视图把查询语句虚拟成一个虚表来供我们操作。
视图是从一个或者几个来导出的表，数据库中只存放视图的定义而不存放视图对
应的数据，这些数据仍然存放在之前的表中，他就像一个窗口供我们查看数据库
中我们需要的一部分数据。
create view viewname as (子查询);
示例:
create view deemview as
(SELECT * from dept LEFT JOIN emp on dept.id=emp.dept_id);
查看视图：
show create view viewname;
注意:视图不可以创建索引，也不能关联触发器和默认值；
视图可以使用order by；
修改视图也是对表的数据的修改，删除视图时不会删除表内的数据；
视图支持嵌套，也就是可以把根据视图检索出来的东西来建立新的视图。
使用SHOW CREATE TABLE显示创建视图的CREATE VIEW语句。从Hive 2.2.0开始，SHOW VIEWS显示数据库中的视图列表。
删除视图
DROP VIEW [IF EXISTS] [db_name.]view_name;
DROP VIEW删除指定视图的元数据。（在视图上使用DROP TABLE是错误的）
删除其他视图引用的视图时不会给出警告（依赖视图悬空为无效，必须由用户删除或重新创建）。
在Hive 0.7.0或更高版本中，如果视图不存在，DROP将返回错误，除非指定了IF EXISTS或配置变量hive.exec.drop.ignorenonexistent设置为true。
更改视图属性
ALTER VIEW [db_name.]view_name SET TBLPROPERTIES properties;
properties:(property_name=property_value,property_name=property_value,...)
与ALTER TABLE一样，你可以使用此语句将自己的元数据添加到视图中。
2.4索引操作
语法如下:
CREATE INDEX index_name
ON TABLE base_tableName (col_name, ...)
AS 'index.handler.class.name'
[WITH DEFERRED REBUILD]
[IDXPROPERTIES (property_name=property_value, ...)]
[IN TABLE index_tableName]
[PARTITIONED BY (col_name, ...)]
[
[ ROW FORMAT ...] STORED AS ...
| STORED BY ...
]
[LOCATION hdfs_path]
[TBLPROPERTIES (...)]
[COMMENT "index comment"]
AS ...语句指定了 索引处理器，也就是一个实现了索引接口的Java类。
建立完索引之后 需要重建索引数据，会触发一个mr job
创建索引
create index [index_studentid] on table student_3(studentid)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
with deferred rebuild
IN TABLE index_table_student_3;
CompactIndexHandler ：创建索引需要的实现类
index_studentid:索引名称
student_3:表名
index_table_student_3:创建索引后的表名
加载索引数据
alter index index_studentid on table student_3 rebuild;
删除索引
DROP INDEX index_studentid on student_3;
查看索引
SHOW INDEX on student_3;
想要索引在查询时，生效，还得设置使用索引(默认是不使用的):
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
SET hive.optimize.index.filter=true;
SET hive.optimize.index.filter.compact.minsize=0;
2.5元数据(Metadata)存储
元数据Metadata位于嵌入式Derby数据库中，其磁盘存储位置由Hive配置变量javax.jdo.opt.connectionurl决定。默认情况下，这个位置是./metastore_db(参见conf/hive-default.xml)。derby 属于内嵌模式,无需配置直接可用。实际生产环境中则使用 mysql 来进行元数据的存储。
现在，在默认配置中，这个元数据一次只能被一个用户看到。
Metadata可以存储在JPOX支持的任何数据库中。关系型数据库的位置和类型可以由javax.jdo.option.ConnectionURL和javax.jdo.option.ConnectionDriverName两个变量控制。有关受支持数据库的更多细节，请参考JDO(或JPOX)文档。数据库模式在JDO元数据注释文件包中定义。jdo在src/contrib/hive/metastore/src/model中。
如果希望将metastore作为网络服务器运行，以便可以从多个节点访问它，请参阅Hive Using Derby in Server Mode。
元数据库数据字典:
表名 说明 关联键
BUCKETING_COLS
COLUMNS 表字段信息(字段注释，字段名，字段类型，字段序号) SD_ID
DBS 元数据库信息，存放HDFS路径信息 DB_ID
PARTITION_KEYS Hive分区表分区键 PART_ID
SDS 所有hive表、表分区所对应的hdfs数据目录和数据格式。 SD_ID,
SERDE_ID
SD_PARAMS 序列化反序列化信息，如行分隔符、列分隔符、NULL的表示字符等 SERDE_ID
SEQUENCE_TABLE 保存hive对象的下一个可用ID。
SERDES
SERDE_PARAMS
SORT_COLS
TABLE_PARAMS 表级属性，如是否外部表，表注释等 TBL_ID
TBLS 所有hive表的基本信息 TBL_ID,SD_ID
从上面几张表的内容来看，hive整个创建表的过程已经比较清楚了
1.解析用户提交hive语句，对其进行解析，分解为表、字段、分区等hive对象
2.根据解析到的信息构建对应的表、字段、分区等对象，从SEQUENCE_TABLE中获取构建对象的最新ID，与构建对象信息(名称，类型等)一同通过DAO方法写入到元数据表中去，成功后将SEQUENCE_TABLE中对应的最新ID+5。
3.DML操作(数据操纵语言)
DML包括：INSERT(插入)、UPDATE(更新)、DELETE(删除)。
Hive支持DML（Data Manipulation Language）中大量的现有功能，包括以下语句：
使用where条件过滤表的行；
使用列或子查询的select表达式；
使用等值连接，连接多张表；
合并表的所有行或子查询；
针对多个"分组"的列进行聚合计算；
将查询结果存入另一张表；
导出表中的内容到本地目录或HDFS目录中。
hive不支持update操作。数据是以load方式加载到建立好的表中。数据一旦导入就不可以修改。
3.1向数据表内加载文件
在将数据加载到表中时，Hive不会进行任何转换。加载操作当前是纯复制/移动操作，它将数据文件移动到与Hive表对应的位置。句法如下所示:
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)
•Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。
•filepath:相对路径，例如：project/data1
绝对路径，例如：/user/hive/project/data1
包含模式的完整URI：hdfs://namenode:9000/user/hive/project/data1
例如: LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;
加载本地数据，同时给定分区信息.加载的目标可以是一个表或者分区。如果表包含分区，必须指定每一个分区的分区名.
•filepath 可以引用一个文件（这种情况下，Hive 会将文件移动到表所对应的目录中）或者是一个目录（在这种情况下，Hive 会将目录中的所有文件移动至表所对应的目录中）
•LOCAL关键字:指定了LOCAL，即本地
•load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。load 命令会将 filepath 中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置.
例如：加载本地数据，同时给定分区信息：
hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE tableName PARTITION (ds='2008-08-15');
没有指定LOCAL
如果 filepath 指向的是一个完整的 URI，hive 会直接使用这个 URI。
如果没有指定 schema 或者 authority，Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI.
如果路径不是绝对的，Hive 相对于 /user/ 进行解释。 Hive 会将 filepath 中指定的文件内容移动到 table （或者 partition）所指定的路径中.
加载DFS数据 ，同时给定分区信息：
hive> LOAD DATA INPATH '/user/myname/kv2.txt' OVERWRITE INTO TABLE tableName PARTITION (ds='2008-08-15');
3.2将查询结果插入Hive表
Hive的insert语句能够从查询语句中获取数据，并同时将数据Load到目标表中。现在假定有一个已有数据的表staged_employees（雇员信息全量表），所属国家cnty和所属州st是该表的两个属性，我们做个试验将该表中的数据查询出来插入到另一个表employees中。
INSERT OVERWRITE TABLE employees PARTITION (country = '中国', state = '北京')
SELECT * FROM staged_employees se WHERE se.cnty = '中国' AND se.st = '北京';
由于使用了OVERWRITE关键字，目标表中原来相同partition中的所有数据被覆盖，如果目标表中没有partition，则整个表会被覆盖。
由于一个国家有很多个省份，如果想根据（国家country，地区partition）两个维度对数据进行分区的话，这条SQL语句的执行个数应该等于地区的数目，比如中国有23个省就要对该SQL语句执行23次。因此hive对这个SQL语句进行了改造，用from...insert...select...只需要扫描一次原表就可以生成不同的输出（多路输出）。比如下面的SQL语句扫描了一次原始数据表，但是同时生成了3个省份的结果数据：
FROM staged_employees se
INSERT OVERWRITE TABLE employees PARTITION (country = '中国', state = '河北省')
SELECT * WHERE se.cnty = '中国' AND se.st = '河北省'
INSERT OVERWRITE TABLE employees PARTITION (country = '中国', state = '陕西省')
SELECT * WHERE se.cnty = '中国' AND se.st = '陕西省'
INSERT OVERWRITE TABLE employees PARTITION (country = '中国', state = '河南省')
SELECT * WHERE se.cnty = 'US' AND se.st = '河南省';
通过缩进可以很清楚的看到，我们扫描了一次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两种不同的方法写出数据。
基本模式
如果结果表已经建好，可以使用INSERT OVERWRITE(覆盖插入)或INSERT INTO(追加插入)将结果写入结果表：
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1,partcol2=val2 ...)]
select_statement1 FROM from_statement
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement1 FROM from_statement;
多插入模式
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;
FROM from_statement
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2]
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;
自动分区模式
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;
INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;
INSERT OVERWRITE将覆盖表或分区中的任何现有数据。
除非IF NOT EXISTS为分区提供（从Hive 0.9.0开始）。
从Hive 2.3.0（HIVE-15880）开始，如果表具有TBLPROPERTIES（“auto.purge”=“true”），则在对表运行INSERT OVERWRITE查询时，表的先前数据不会移动到废纸篓。此功能仅适用于托管表（请参阅托管表），并在“auto.purge”属性未设置或设置为false时关闭。
INSERT INTO将附加到表或分区，保持现有数据不变。（注意：INSERT INTO语法在在0.8版本之后才可用）从Hive 0.13.0开始，通过使用TBLPROPERTIES（“immutable”=“true”）创建表，可以使表不可变。默认值为“immutable”=“false”。如果任何数据已经存在，则不允许将INSERT INTO行为插入到不可变表中，尽管如果不可变表为空，INSERT INTO仍然有效。INSERT OVERWRITE的行为不受“immutable”表属性的影响。不可变表被保护以防止意外更新，因为脚本将数据加载到错误地运行多次。第一次插入到不可变表中成功，连续插入失败，导致表中只有一组数据。
可以对表或分区执行插入操作。如果表已分区，则必须通过指定所有分区列的值来指定表的特定分区。如果hive.typecheck.on.insert设置为true，则验证，转换和规范化这些值以符合其列类型（Hive 0.12.0以后）。
可以在同一查询中指定多个插入子句（也称为多表插入）。
每个select语句的输出都写入选定的表（或分区）。目前，OVERWRITE关键字是必需的，意味着所选表或分区的内容将替换为相应select语句的输出。
输出格式和序列化类由表的元数据确定（通过表中的DDL命令指定）。
从Hive 0.14开始，如果表具有实现AcidOutputFormat的OutputFormat，并且系统配置为使用实现ACID 的事务管理器，则将禁用该表的INSERT OVERWRITE。这是为了避免用户无意中覆盖交易历史记录。通过使用TRUNCATE TABLE（对于非分区表）或DROP PARTITION，然后使用INSERT INTO，可以实现相同的功能。
从Hive 1.1.0开始，TABLE关键字是可选的。
从Hive 1.2.0开始，每个INSERT INTO T都可以采用类似INSERT INTO T（z，x，c1）的列列表。有关示例，请参阅 HIVE-9481的说明。
注意:
多表插入可最大限度地减少所需的数据扫描次数。Hive可以通过仅将输入数据扫描一次（并将不同的查询运算符应用于输入数据）将数据插入到多个表中。
从Hive 0.13.0开始 ，select语句可以包含一个或多个公共表表达式（CTE），如SELECT语法中所示 。有关示例，请参阅 公用表表达式。
动态分区插入
在动态分区插入中，用户可以提供部分分区规范，这意味着只需在PARTITION子句中指定分区列名称列表。列值是可选的。如果给出了分区列值，我们将其称为静态分区，否则它是动态分区。每个动态分区列都有一个来自select语句的相应输入列。这意味着动态分区创建由输入列的值确定。动态分区列必须在SELECT语句的列中最后指定，并且与它们在PARTITION（）子句中出现的顺序相同。截至Hive 3.0.0（HIVE-19083）无需指定动态分区列。如果未指定，Hive将自动生成分区规范。
默认情况下，在Hive 0.9.0之前禁用动态分区插入，并在Hive 0.9.0及更高版本中默认启用。这些是动态分区插入的相关配置属性：
配置属性 默认 注意
hive.exec.dynamic.partition true 需要设置true为启用动态分区插入
hive.exec.dynamic.partition.mode strict
(严格模式) 在strict模式下，用户必须指定至少一个静态分区，以防用户意外覆盖所有分区，在nonstrict模式下允许所有分区都是动态的
hive.exec.max.dynamic.partitions.pernode 100 允许在每个映射器/ reducer节点中创建的最大动态分区数
hive.exec.max.dynamic.partitions 1000 允许总共创建的最大动态分区数
hive.exec.max.created.files 100000 MapReduce作业中所有映射器/缩减器创建的HDFS文件的最大数量
hive.error.on.empty.partition false 如果动态分区插入生成空结果，是否抛出异常
示例:
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.cnt
这里的dt是静态分区，country分区将由SELECT子句中的最后一列（即pvs.cnt）动态创建。请注意，不使用该名称。在nonstrict非严格模式下，dt也可以动态创建分区。
注意：静态分区值必须在动态分区值的前面！
3.3将查询结果写入文件系统
目录可以是完整的URI。如果未指定scheme或authority，Hive将使用fs.default.name指定Namenode URI 的hadoop配置变量中的方案和权限。
如果使用LOCAL关键字，Hive会将数据写入本地文件系统上的目录。
写入文件系统的数据被序列化为文本，其中列由^ A分隔，行由换行符分隔。如果任何列不是基本类型，则这些列将序列化为JSON格式。
句法:
Standard syntax:
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
[ROW FORMAT row_format] [STORED AS file_format] (注意:只能从Hive 0.11.0开始使用)
SELECT ... FROM ...
Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...
row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char] (注意:只能从Hive0.13开始使用)
注意:
对目录、本地目录和表（或分区）的INSERT OVERWRITE语句都可以在同一查询中一起使用。
INSERT OVERWRITE语句到HDFS文件系统目录是从Hive中提取大量数据的最佳方法。Hive可以在map-reduce作业中并行写入HDFS目录。
正如你所料，目录是OVERWRITten; 换句话说，如果指定的路径存在，它将被破坏并替换为输出。
从Hive 0.11.0开始，可以指定使用的分离器; 在早期版本中，它始终是^ A字符（\ 001）。但是，自定义分隔符仅支持Hive版本0.11.0到1.1.0中的LOCAL写入 - 此错误在版本1.2.0中已修复（请参阅 HIVE-5672）。
在Hive 0.14中，插入符合ACID的表将在选择和插入期间停用矢量化。这将自动完成。仍然可以使用向量化查询已插入数据的ACID表。
保存到本地
调用hive的标准输出，将查询结果写到指定的文件中:
hive -e "select user, login_timestamp from user_login" > /tmp/out.txt
或者
$ cat file.sql
select user, login_timestamp from user_login
$ hive -f file.sql > /tmp/out.txt
使用INSERT OVERWRITE LOCAL DIRECTORY保存结果到本地
hive> insert overwrite local directory "/tmp/out/"
> row format delimited fields terminated by "\t"
> select user, login_time from user_login;
修改分隔符号为tab
命令执行后/tmp/out/目录下多了两个文件：
$ find /tmp/out/ -type f
/tmp/out/.000000_0.crc
/tmp/out/000000_0
这两个文件存放的内容不一样，其中000000_0存放查询的结果，带有.crc文件存放查询结果文件的crc32校验。
保存到HDFS
使用INSERT OVERWRITE DIRECTORY可完成操作
insert overwrite directory "/tmp/out/"
row format delimited fields terminated by "\t"
select user, login_time from user_login;
使用HDFS直接导出表
Hive是构建在HDFS之上，因此可使用HDFS的命令hadoop dfs -get直接导出表到本地。
hive> show create table user_login; --找到被导出的表的存放目录
hive> hadoop dfs -get /user/hive/warehouse/test.db/user_login /tmp/out/
3.4从SQL插入值到表中
INSERT ... VALUES语句可用于直接从SQL将数据插入表中(从Hive 0.14开始可用)。
句法:
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]
Where values_row is:
( value [, value ...] )
where a value is either null or any valid SQL literal
VALUES子句中列出的每一行都插入到表tablename中。
必须为表中的每个列提供值。尚不支持允许用户仅将值插入某些列的标准SQL语法。为了模仿标准SQL，可以为用户不希望为其赋值的列提供空值。
支持动态分区的方式与INSERT ... SELECT相同。
如果正在使用插入的表支持ACID和支持ACID的事务管理器，则此操作将在成功完成后自动提交。
Hive不支持复杂类型（数组，映射，结构，联合）的文字，因此无法在INSERT INTO ... VALUES子句中使用它们。这意味着用户无法使用INSERT INTO ... VALUES子句将数据插入到复杂的数据类型列中。
示例:
CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2))
CLUSTERED BY (age) INTO 2 BUCKETS STORED AS ORC;
INSERT INTO TABLE students
VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);
CREATE TABLE pageviews (userid VARCHAR(64), link STRING, came_from STRING)
PARTITIONED BY (datestamp STRING) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC;
INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23')
VALUES ('jsmith', 'mail.com', 'sports.com'), ('jdoe', 'mail.com', null);
INSERT INTO TABLE pageviews PARTITION (datestamp)
VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');
INSERT INTO TABLE pageviews
VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');
3.5更新
版本信息UPDATE从Hive 0.14开始提供。
更新只能在支持ACID的表上执行。有关详细信息，请参阅Hive事务。
句法:
UPDATE tablename SET column = value [, column = value ...] [WHERE expression]
引用的列必须是要更新的表的列。
分配的值必须是Hive在select子句中支持的表达式。因此支持算术运算符，UDF，强制转换，文字等。不支持子查询。
只会更新与WHERE子句匹配的行。
无法更新分区列。
Bucketing列无法更新。
在Hive 0.14中，成功完成此操作后，将自动提交更改。
注意:
将关闭矢量化以进行更新操作。这是自动的，不需要用户操作。非更新操作不受影响。仍然可以使用矢量化查询更新的表。
在版本0.14中，建议你在执行更新时设置hive.optimize.sort.dynamic.partition = false，因为这会生成更高效的执行计划。
3.6删除
版本信息:从Hive 0.14开始可以使用DELETE 。
删除只能在支持ACID的表上执行。有关详细信息，请参阅Hive事务。
句法:
DELETE FROM tablename [WHERE expression]
只会删除与WHERE子句匹配的行。
在Hive 0.14中，成功完成此操作后，将自动提交更改。
注意:
将关闭矢量化以进行删除操作。这是自动的，不需要用户操作。非删除操作不受影响。仍然可以使用矢量化查询具有已删除数据的表。
在版本0.14中，建议你在执行删除时设置hive.optimize.sort.dynamic.partition = false，因为这会生成更高效的执行计划。
4.DQL操作(数据查询语言)
•基本的Select 操作
•基于Partition的查询
•Join
4.1基本的Select 操作
select [all | distinct] select_expr, select_expr, ...
from table_reference
[where where_condition]
[group by col_list [having condition]]
[cluster by col_list
| [distribute by col_list] [sort by| order by col_list]
]
[limit number]
说明：
1、order by 会对输入做全局排序，因此只有一个reducer任务，会导致当输入规模较大时，需要较长的计算时间。
2、sort by只在本机做排序,不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3、distribute by(字段)分区排序, 根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
4、Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。
5、LIMIT子句用于限制返回的行数。例如 select * from score limit 3;
6、GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
7、JOIN语句:Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
8、使用ALL和DISTINCT选项区分对重复记录的处理。默认是ALL，表示查询所有记录。DISTINCT表示去掉重复的记录
9、Where 条件: 类似我们传统SQL的where 条件,目前支持 AND,OR ,0.9版本支持between,IN, NOT IN;不支持EXIST ,NOT EXIST
HiveQL逻辑执行顺序
FROM->WHERE->SELECT(map端)->GROUP BY->HAVING->SELECT(reduce端)->ORDER BY
Hive总是按照从左到右的顺序执行的，如a、b、c三个表关联
SELECT 语句可以使用正则表达式做列选择，下面的语句查询除了 ds 和 hr 之外的所有列： SELECT (ds|hr)?+.+ FROM test
例如按先件查询:
hive> SELECT a.foo FROM tableName a WHERE a.ds='
将查询数据输出至目录：
INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM tableName a WHERE a.ds='
将查询结果输出至本地目录：
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;
选择所有列到本地目录 ：
INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a;
INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100;
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM events a;
INSERT OVERWRITE DIRECTORY '/tmp/reg_4' select a.tableName, a.pokes FROM profiles a;
INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(1) FROM tableName a WHERE a.ds='
INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT a.foo, a.bar FROM tableName a;
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/sum' SELECT SUM(a.pc) FROM pc1 a;
将一个表的统计结果插入另一个表中：
hive> FROM tableName a INSERT OVERWRITE TABLE events SELECT a.bar, count(1) WHERE a.foo > 0 GROUP BY a.bar;
hive> INSERT OVERWRITE TABLE events SELECT a.bar, count(1) FROM tableName a WHERE a.foo > 0 GROUP BY a.bar;
将一个表的统计结果插入到多个表中：
FROM src
INSERT OVERWRITE TABLE dest1 SELECT src.* WHERE src.key < 100
INSERT OVERWRITE TABLE dest2 SELECT src.key, src.value WHERE src.key >= 100 and src.key < 200
INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08', hr='12') SELECT src.key WHERE src.key >= 200 and src.key < 300
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/dest4.out' SELECT src.value WHERE src.key >= 300;
将文件流直接插入文件：
hive> FROM tableName a INSERT OVERWRITE TABLE events
SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2017-08-09';
4.2基于Partition的查询
•一般 SELECT 查询会扫描整个表，使用 PARTITIONED BY 子句建表，查询就可以利用分区剪枝（input pruning）的特性
•Hive 当前的实现是，只有分区断言出现在离 FROM 子句最近的那个WHERE 子句中，才会启用分区剪枝
1、在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。
2、分区表指的是在创建表时指定的partition的分区空间。
3、如果需要创建有分区的表，需要在create表的时候调用可选参数partitioned by，详见表创建的语法结构。
技术细节
1、一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。
2、表和列名不区分大小写。
3、分区是以字段的形式在表结构中存在，通过describe table命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。
4、分区建表分为2种:一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式。
a、单分区建表语句：create table day_table (id int, content string) partitioned by (dt string);单分区表，按天分区，在表结构中存在id，content，dt三列。
b、双分区建表语句：create table day_hour_table (id int, content string) partitioned by (dt string, hour string);双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。
5、添加分区表语法（表已创建，在此基础上添加分区）：
ALTER TABLE tableName ADD partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ... partition_spec: : PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
用户可以用 ALTER TABLE ADD PARTITION 来向一个表中增加分区。当分区名是字符串时加引号。例：
ALTER TABLE day_table ADD PARTITION (dt='2008-08-08', hour='08') location '/path/pv1.txt' PARTITION (dt='2008-08-08', hour='09') location '/path/pv2.txt';
7、删除分区语法：
ALTER TABLE tableName DROP partition_spec, partition_spec,...
用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。例：
ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');
8、数据加载进分区表中语法：
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
例：
LOAD DATA INPATH '/user/pv.txt' INTO TABLE day_hour_table PARTITION(dt='2008-08- 08', hour='08'); LOAD DATA local INPATH '/user/hua/' INTO TABLE day_hour partition(dt='2010-07- 07');
当数据被加载至表中时不会对数据进行任何转换。Load操作只是将数据复制至Hive表对应的位置。数据加载时在表下自动创建一个目录，文件存放在该分区下。
9、基于分区的查询的语句：
SELECT day_table. FROM day_table WHERE day_table.dt>= '2008-08-08';
10、查看分区语句：
hive> show partitions day_hour_table;
OK
dt=2008-08-08/hour=08 dt=2008-08-08/hour=09 dt=2008-08-09/hour=09
4.3 Join操作
(1).LEFT，RIGHT和FULL OUTER关键字用于处理join中空记录的情况;
(2).LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现;
(3).join 时，每次MR任务的逻辑是：reducer 会缓存 join 序列中除了最后一个表的所有表的记录,再通过最后一个表将结果序列化到文件系统.
(4).实践中，应该把最大的那个表写在最后
Hive表连接的语法支持如下：
join_table:
table_reference JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition
| table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)
table_reference:
table_factor
| join_table
table_factor:
tbl_name [alias]
| table_subquery alias
| ( table_references )
join_condition:
ON equality_expression ( AND equality_expression )*
equality_expression:
expression = expression
•Hive 只支持等值连接（equality joins）、外连接（outer joins）和（left semi joins）。不支持所有非等值的连接，因为非等值连接非常难转化到 map/reduce 任务.
对于右表的筛选条件的设置要特别注意，如果放在where中，则left join会变为inner join，实际是先执行了left join之后，又执行筛选，这样会把左边中的部分记录删除，不符合left join对左表取全量的初衷了。需要将右表条件以子表的形式指定，或者放在on中。在oracle中也是如此，条件放在on和where中逻辑上的含义是不同的，这应该不算是hive的bug。
写join查询时，有几个典型的点要考虑，如下：
等连接(只有等连接是允许的):
select a.* from a join b on (a.id = b.id and a.department = b.department)
这个非等值连接是不合法的：
select a.* from a join b on (a.id < b.id)
但是可以用如下方式实现：
select a.* from a join b on (true) where a.id < b.id
多表连接,可以join两个以上的表：
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
需要注意的是，多个表join时，最好按照表由小到大的顺序join。虽然实际原因要考虑数据倾斜等问题，此处不展开叙述(见第八章节)。
join的缓存和任务转换
hive转换多表join时，如果每个表在join字句中，使用的都是同一个列，只会转换为一个单独的map/reduce。
例如：
这个会转换为单独的map/reduce任务，只有b表的key1列在join被调用。
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
而这个链接：
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
被转换为两个map/reduce任务，因为b的key1列在第一个join条件使用，而b表的key2列在第二个join条件使用。
第一个map/reduce任务join a和b。第二个任务是第一个任务的结果join c
如果join中多个表的 join key 是同一个，则 join 会被转化为单个mr 任务.
容易混淆的问题是表分区的情况
SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key)
WHERE a.ds='2010-07-07' AND b.ds='2010-07-07';
如果 d 表中找不到对应 c 表的记录，d 表的所有列都会列出 NULL，包括 ds 列。也就是说，join 会过滤 d 表中不能找到匹配 c 表 join key 的所有记录。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关.
解决办法:
SELECT c.val, d.val FROM c LEFT OUTER JOIN d
ON (c.key=d.key AND d.ds='2009-07-07' AND c.ds='2009-07-07');
对于left join和full join, on 和 where 在筛选条件的时候，on 会显示所有满足 | 不满足条件的数据,而 where 只显示满足条件的数据。对于inner join和right join, on 和 where 没区别.
LEFT SEMI JOIN
LEFT SEMI JOIN 的限制是 JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行.
SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B);
可以被重写为：
SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)
在编写带有 join 操作的代码语句时，应该将条目少的表/子查询放在 Join 操作符的左边。 因为在 Reduce 阶段，位于 Join 操作符左边的表的内容会被加载进内存，载入条目较少的表可以有效减少OOM即内存溢出。所以对于同一个 key 来说，对应的 value 值小的放前，大的放后，这便是"小表放前"原则。
若一条语句中有多个 Join，依据 Join 的条件相同与否，有不同的处理方法。
对于一条语句中有多个 Join 的情况，如果 Join 的条件相同，小表在左边.
比如查询：
INSERT OVERWRITE TABLE pv_users
SELECT
pv.pageid,u.age
FROM page_view p
JOIN user u ON (pv.userid = u.userid)
JOIN newuser x ON (u.userid = x.userid);
如果 Join 的 key 相同，不管有多少个表，都会则会合并为一个 Map-Reduce任务.在做 OUTER JOIN 的时候也是一样.
如果 Join 的条件不相同，比如：
INSERT OVERWRITE TABLE pv_users
SELECT
pv.pageid, u.age
FROM page_view p
JOIN user u ON (pv.userid = u.userid)
JOIN newuser x on (u.age = x.age);
Map-Reduce 的任务数目和 Join 操作的数目是对应的，上述查询和以下查询是等价的：
INSERT OVERWRITE TABLE tmptable
SELECT *
FROM page_view p
JOIN user u
ON (pv.userid = u.userid);
INSERT OVERWRITE TABLE pv_users
SELECT
x.pageid, x.age
FROM tmptable x
JOIN
newuser y
ON (x.age = y.age);
MAPJOIN操作(map side join)
如果你有一张表非常非常小，而另一张关联的表非常非常大的时候，你可以使用mapjoin此Join 操作在 Map 阶段完成，不再需要Reduce，也就不需要经过Shuffle过程，从而能在一定程度上节省资源提高JOIN效率前提条件是需要的数据在 Map 的过程中可以访问到。(其中使用了分布式缓存技术)比如查询：
INSERT OVERWRITE TABLE pv_users
SELECT /+ MAPJOIN(pv) / pv.pageid, u.age
FROM page_view pv
JOIN user u ON (pv.userid = u.userid);
可以在 Map 阶段完成 Join./+mapjoin(n)/不能省略，只需替换表名即可。
相关的参数为：
hive.join.emit.interval = 1000
hive.mapjoin.size.key = 10000
hive.mapjoin.cache.numrows = 10000
hive.auto.convert.join.noconditionaltask.size=10MB
值得注意的是，Hive版本0.11之后，Hive默认启动该优化，也就是不在需要显示的使用MAPJOIN标记，其会在必要的时候触发该优化操作将普通JOIN转换成MapJoin.
装载到内存的表必须是通过scan的表（不包括group by等操作），如果join的两个表都满足上面的条件，/mapjoin/指定表格不起作用，只会装载小表到内存，否则就会选那个满足条件的scan表。
两个属性来设置该优化的触发时机:
hive.auto.convert.join 默认值为true，自动开启MAPJOIN优化
hive.mapjoin.smalltable.filesize 默认值为2500000(25M),通过配置该属性来确定使用该优化的表的大小，如果表的大小小于此值就会被加载进内存中
map join的使用场景:关联操作中有一张表非常小 ;不等值的链接操作.
4.4 group by
对检索结果的保留行进行单纯分组，一般总爱和聚合函数一块用例如AVG（），COUNT（），max（），main（）等一块用。 也可用于去重.
group by操作表示按照某些字段的值进行分组，有相同的值放到一起，语法样例如下：
select col1,col2,count(1),sel_expr(聚合操作)
from tableName
where condition -->Map端执行
group by col1,col2 -->reduce端执行
having... -->reduce端执行
注意：
(1)：select后面的非聚合列必须出现在group by中(如上面的col1和col2)。
(2)：除了普通列就是一些聚合操作。
group的特性：
(1)：使用了reduce操作，受限于reduce数量，通过参数mapred.reduce.tasks设置reduce个数。
(2)：输出文件个数与reduce数量相同，文件大小与reduce处理的数量有关。
问题：
(1):网络负载过重。
(2):出现数据倾斜(我们可以通过hive.groupby.skewindata参数来优化数据倾斜的问题)。
group by 和 distribute by的对比
distribute by是控制在map端如何拆分数据给reduce端的。hive会根据distribute by后面列，对应reduce的个数进行分发，默认是采用hash算法。sort by为每个reduce产生一个排序文件。在有些情况下，你需要控制某个特定行应该到哪个reducer，这通常是为了进行后续的聚集操作。distribute by刚好可以做这件事。因此，distribute by经常和sort by配合使用。
注：Distribute by和sort by的使用场景
1.Map输出的文件大小不均;
2.Reduce输出文件大小不均;
3.小文件过多;
4.文件超大.
4.5 union all
用来合并多个select的查询结果，需要保证select中字段须一致
union all必须满足:字段名字一样,字段类型一样,字段个数一样,子表不能有别名.如果需要从合并之后的表中查询数据,那么合并的表必须要有别名.
•select_statement UNION ALL select_statement UNION ALL select_statement ...
select * from (
select col1,col2 from m union all select col1,col3 as col2 from n
)temp;
4.6 with as的使用
当我们书写一些结构相对复杂的SQL语句时，可能某个子查询在多个层级多个地方存在重复使用的情况，这个时候我们可以使用 with as 语句将其独立出来，极大提高SQL可读性，简化SQL。
注：目前oracle、sql server、hive等均支持with as用法,但mysql并不支持！
with as 也叫做子查询部分，首先定义一个sql片段，该sql片段会被整个sql语句所用到，为了让sql语句的可读性更高些，作为提供数据的部分，也常常用在union等集合操作中。
with as就类似于一个视图或临时表，可以用来存储一部分的sql语句作为别名，不同的是with as 属于一次性的，而且必须要和其他sql一起使用才可以！
如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
例如:(前面的with子句定义的查询在后面的with子句中可以使用)
with t1 as (select * from carinfo),t2 as (select t1.id from t1)
select * from t2
5.DCL操作(数据控制语言)
数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
- GRANT：授权.
2)REVOKE: 权限回收. - ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
回滚命令使数据库状态回到上次最后提交的状态。 - COMMIT [WORK]：提交。
在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成后才可以看到。
5.1提交数据的三种类型
提交数据有三种类型：显式提交、隐式提交及自动提交。
(1) 显式提交
用COMMIT命令直接完成的提交为显式提交。其格式为：SQL>COMMIT；
(2) 隐式提交
用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
(3) 自动提交
若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：SQL>SET AUTOCOMMIT ON；
5.2 hive权限管理
[官网地址]:Hive - SQL Standards Based Authorization in HiveServer2
三种授权模型：
1 .Storage Based Authorization in the Metastore Server 基于存储的授权
可以对 Metastore 中的元数据进行保护，但是没有提供更加细粒度的访问控
制（例如：列级别、行级别）。
2.SQL Standards Based Authorization in HiveServer2 基于SQL标准的Hive授权
完全兼容SQL的授权模型，推荐使用该模式。基于SQL标准的完全兼容SQL的授权模型，除支持对于用户的授权认证，还支持角色 role 的授权认证。
role 可理解为是一组权限的集合，通过 role 为用户授权。
一个用户可以具有一个或多个角色。默认包含两种角色：public、admin。
3.Default Hive Authorization (Legacy Mode) hive 默认授权
防止用户产生误操作，而不是防止恶意用户访问未经授权的数据。
限制：
①启用当前认证方式之后，dfs, add, delete, compile, and reset 等命令被禁用。
②通过 set 命令设置 hive configuration 的方式被限制某些用户使用。
（可通过修改配置文件 hive-site.xml 中
hive.security.authorization.sqlstd.confwhitelist 进行配置）
③添加、删除函数以及宏（批量规模）的操作，仅为具有 admin 的用户开放。
④用户自定义函数（开放支持永久的自定义函数），可通过具有 admin 角色的
用户创建，其他用户都可以使用。
在 hive 服务端修改配置文件 hive-site.xml 添加以下配置内容：
hive.security.authorization.enabled
true
hive.server2.enable.doAs
false
hive.users.in.admin.role
root
hive.security.authorization.manager
org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory<
/value>
hive.security.authenticator.manager
org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator
服务端启动 hiveserver2 ；客户端通过 beeline 进行连接
角色的添加、删除、查看、设置：
CREATE ROLE role_name; -- 创建角色
DROP ROLE role_name; -- 删除角色
SET ROLE (role_name|ALL|NONE); -- 设置角色
SHOW CURRENT ROLES; -- 查看当前具有的角色
SHOW ROLES; -- 查看所有存在的角色
Hive从0.10可以通过元数据控制权限。但是Hive的权限控制并不是完全安全的。基本的授权方案的目的是防止用户不小心做了不合适的事情。
为了使用Hive的授权机制，有两个参数必须在hive-site.xml中设置：
hive.security.authorization.enabled //参数是开启权限验证，默认为 false。
hive.security.authorization.createtable.owner.grants //参数是指表的创建者对表拥有所有权限。
授权范围可以包括select、insert、update、delete、all。
Hive中没有超级管理员，任何用户都可以进行Grant/Revoke操作，为了完善"超级管理员",必须添加hive.semantic.analyzer.hook配置，并实现自己的权限控制类.
基于CDH5.x的Hive权限配置
1、打开权限控制，默认是没有限制的
set hive.security.authorization.enabled=true;
2、配置默认权限
hive.security.authorization.createtable.owner.grants = ALL;
hive.security.authorization.createtable.role.grants=admin_role:ALL;
hive.security.authorization.createtable.user.grants=user1,user2:select;user3:create;
角色的授权和撤销
1) 把 role_test1 角色授权给 zhangsan用户，命令如下。
hive> grant role role_test1 to user zhangsan;
2) 查看 zhangsan用户被授权的角色，命令如下。
hive> show role grant user zhangsan;
3) 取消 zhangsan用户的 role_test1 角色，命令如下。
hive> revoke role role_test1 from user zhangsan;
分配权限
基于角色:
GRANT CREATE ON DATABASE default TO group test_role;
GRANT SELECT on table authorization_test to group test_role;
GRANT DROP on table authorization_test to group test_role;
GRANT ALL on table authorization_test to group test_role;
基于用户:
GRANT CREATE ON DATABASE default TO user mllib;
GRANT SELECT on table authorization_test to user mllib;
GRANT DROP on table authorization_test to user mllib;
GRANT ALL on table authorization_test to user mllib;
分配创建数据库的权限:
GRANT CREATE TO user root;
查看权限分配
SHOW GRANT user mllib ON DATABASE default;
SHOW GRANT group test_role ON DATABASE default;
删除权限
revoke all on database spark from user mllib;
HIVE支持以下权限
权限名称 含义
ALL 所有权限
ALTER 允许修改元数据（modify metadata data of object）—表信息数据
UPDATE 允许修改物理数据（modify physical data of object）—实际数据
CREATE 允许进行Create操作
DROP 允许进行DROP操作
INDEX 允许建索引（目前还没有实现）
LOCK 当出现并发的使用允许用户进行LOCK和UNLOCK操作
SELECT 允许用户进行SELECT操作
SHOW_DATABASE 允许用户查看可用的数据库
登录hive元数据库，可以发现以下表:
Db_privs: 记录了User/Role在DB上的权限.
Tbl_privs: 记录了User/Role在table上的权限.
Tbl_col_privs: 记录了User/Role在table column上的权限.
Roles: 记录了所有创建的role.
Role_map: 记录了User与Role的对应关系.
超级管理权限
HIVE本身有权限管理功能，需要通过配置开启。
其中hive.security.authorization.createtable.owner.grants设置成ALL表示用户对自己创建的表是有所有权限的（这样是比较合理地）。
6.Hive执行流程
1.Antlr定义HQL语法规则,完成HQL词法和语法解析，将HQL转化为抽象语法树AST Tree
2.遍历AST Tree，抽象出查询的基本组成单元QueryBlock
3.遍历QueryBlock，翻译为执行操作树OperatorTree
4.逻辑层优化器进行OperatorTree变换，合并不必要的ReduceSinkOperator，减少shuffle数据量
5.遍历OperatorTree，翻译为MapReduce任务
6.物理层优化器进行MapReduce任务的变换，生成最终的执行计划
Hive最终生成的MapReduce任务，Map阶段和Reduce阶段均由OperatorTree组成。逻辑操作符，就是在Map阶段或者Reduce阶段完成单一特定的操作。
基本的操作符包括TableScanOperator，SelectOperator，FilterOperator， JoinOperator，GroupByOperator，ReduceSinkOperator，FileSinkOperate，
MapjoinOperate，LimitOperate，UnionOperate.
说明:
TableScanOperator从MapReduce框架的Map接口原始输入表的数据，控制扫描表的数据行数，标记是从原表中取数据。
JoinOperator完成Join操作。FilterOperator完成过滤操作;
ReduceSinkOperator将Map端的字段组合序列化为Reduce Key/value,Partition Key,只可能出现在Map阶段,同时也标志Hive生成的MapReduce程序中Map阶段的结束。
Operator在Map Reduce阶段之间的数据传递都是一个流式的过程。每一个Operator对一行数据完成操作后之后将数据传递给childOperator计算。
Hive on Mapreduce执行流程
流程图如下所示:
Step 1：UI(user interface) 调用executeQuery接口，发送 HQL 查询语句给Driver.
Step 2：Driver 为查询语句创建会话句柄，并将查询语句发送给 Compiler， 等待其进行语句解析并生成执行计划.
Step 3 and 4：Compiler 从 metastore 获取相关的元数据.
Step 5：元数据用于对查询树中的表达式进行类型检查，以及基于查询调整分区，生成计划.
Step 6 (6.1，6.2，6.3)：由 Compiler 生成的执行计划是阶段性的 DAG，每个阶段都可能会涉及到 Map/Reduce job、元数据的操作、HDFS 文件的操作，Execution Engine 将各个阶段的 DAG 提交给对应的组件执行。
Step 7, 8 and 9：在每个任务（mapper / reducer）中，查询结果会以临时文件的方式存储在 HDFS 中。保存查询结果的临时文件由 Execution Engine 直接从 HDFS 读取，作为从 Driver Fetch API 的返回内容。
查询处理器
以下是hive查询处理器的主要组件:
Parse和SemanticAnalysis(ql/parse) -- 包含用于解析SQL,将其转换为抽象语法树,将抽象语法树转换为运算符计划以及最终将运算符计划转换为由Driver执行的任务的有向图的java代码。
优化器(ql/optimizer) -- 包含一些简单的基于规则的优化,例如修建Hive查询处理器在将HQL转化为一系列的map/reducef任务时执行的表扫描(列修剪)中的非引用列。
计划组件(ql/plan) -- 此组件包含端译器(Parser,SemanticAnalysis和Optimizer)使用的类(称为描述符),用于将信息传递给执行代码使用的运算符树。
元数据层(ql/metadata) -- 查询处理器使用此组件与MetaStore连接,以便检索有关表,分区和表的列的信息,编译器使用此信息将SQL译为一系列map/reduce任务。
Map/Reduce Execution Engine(ql/exec) -- 该组件包含所有查询运算符以及用于map/reduce任务中调用这些运算符的框架。
用于Hive的Hadoop记录读取器,输入和输出格式化程序(ql/io) -- 该组件包含Hive在Hadoop作业中注册的记录读取和输入,输出格式化程序。
会话(ql/session) -- hive的基本会话实现。
类型接口(q/typeinfo) -- 提供从MetaStore和SerDes检索的表列的所有类型信息。
Hive功能框架(ql/udf) -- hive运算符,函数及聚合函数的框架和实现。该组件还包含用户可以实现的接口,以创津用户定义的函数。
工具(ql/tools) -- 查询处理框架提供的一些简单工具。目前,该组件包含可以解析查询并显示查询的源表和目标表的沿袭工具的实现。
7.Hive数据类型
(1).基本数据类型
Hive数据类型 Java数据类型 说明 备注
TINYINT byte 1byte有符号整数，范围为-128到127
SMALLINT short 2byte有符号整数，
范围为-32768到32767
INT
(INTEGER) int 4byte有符号整数，范围从-2147483648到2147483647
BIGINT long 8byte有符号整数,范围为-9223372036854775808至9223372036854775807
BOOLEAN boolean 布尔类型，true或者false
FLOAT float 用于存储正或负1.40129846432481707e-45 .. 3.40282346638528860e + 38范围内的单精度浮点值
DOUBLE double 用于存储正值或负值4.94065645841246544e-324d -1.79769313486231570e + 308范围内的浮点值
DECIMAL BigDecimal 固定精度的数值,精度为38位 从Hive 0.11.0开始
NUMERIC BigDecimal 和DECIMAL一样 从Hive 3.0.0开始
STRING string 字符系列。可以指定字符集。可以使用单引号(')或者双引号(")
CHAR 固定长度的存储，它用空格填充，可以存储最大长度为255 从Hive 0.13.0开始
VARCHAR 存储可变长度字符，最大长度为65,535。
如果转换/分配给该类型的字符串值超过长度说明符，则字符串将被静默截断 从Hive 0.12.0开始
BINARY 字节数组 从Hive 0.8.0开始
TIMESTAMP 时间戳类型,支持传统的UNIX时间戳 从Hive 0.8.0开始
DATE 日期类型,以YYYY—MM—DD的形式描述特定的年/月/日 从Hive 0.12.0开始
INTERVAL 时间类型 从Hive 1.2.0开始
注意：Hive不支持Long类型，用bigint表示。
Hive的String类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。
INT2、INT4、INT8、FLOAT4、FLOAT8 和 BPCHAR 内置数据类型对应于同名的 Netezza 数据类型。
INT2 是 SMALLINT 数据类型的别名。
INT4 是 INTEGER 数据类型的别名。
INT8 是 BIGINT 数据类型的别名。
FLOAT4 是 REAL 数据类型的别名。
FLOAT8 是 DOUBLE 数据类型的别名。
BPCHAR 是 VARCHAR 数据类型的别名，但只能用作强制类型转换目标数据类型。BPCHAR 数据类型不接受长度自变量。
如果使用其中任何名称的用户定义数据类型，那么必须使用标准引用，以确保用户定义数据类型不会被内置数据类型别名覆盖。
(2).复合类型
Hive有四种复合数据类型:ARRAY、MAP、STRUCT和UNIONTYPE。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复合数据类型允许任意层次的嵌套。
目前Hive对UNIONTYPE类型的支持不完整，不推荐使用。
注意：MAP，STRUCT和ARRAY里的元素间关系都可以用同一个字符表示，这里用""。
例如：假设某表有如下一行，我们用JSON格式来表示其数据结构。在Hive下访问的格式为
{
"name": "weixiaobao",
"wives": ["zengrou" , "jianning"], //列表Array
"children": { //键值Map
"hutou": 1 ,
"shuangshuang": 1
}
"address": { //结构Struct
"street":"tong chi dao" ,
"city":"liao dong"
}
}
2.基于上述数据结构，我们在Hive里创建对应的表，并导入数据
创建本地测试文件test.txt
weixiaobao,zengrong_jianning,hutou:1_shuangshuang:1,tong chi dao_liao dong
注意：MAP，STRUCT和ARRAY里的元素间关系都可以用同一个字符表示，这里用""。
3.Hive上创建测试表test
create table test(
name string,
wives array
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fieldsterminated by ',' -- 列分隔符
collection itemsterminated by '_' --MAPSTRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':' -- MAP中的key与value的分隔符
lines terminated by '\n'; --行分隔符
4.导入文本数据到测试表
load data local inpath'/opt/module/datas/test.txt' into table test;
5.访问三种集合列里的数据，以下分别是ARRAY，MAP，STRUCT的访问方式
select wives[1],children['xiaosong'],address.city from test where name="weixiaobao";
_c0 _c1 city
jianning 1 liao dong
嵌套Array的处理:
创建表
create external table test_stg（
employee_id INT,
name STRING,
genre array
map1 map<string,string>,
union1 uniontype<float,boolean,string>,
abu ARRAY< String>,
sabu MAP< String,ARRAY< INT>,
starring array<structid:bigint,name:string>）
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY '^'
MAP KEYS TERMINATED BY '$';
Hive的默认分隔符是：
行分隔符 => Control-A（'\001'）
集合分隔符 => Control-B（'\002'）
映射键定界符 => Control-C（'\003'）
如果你覆盖这些分隔符，则在解析过程中将使用重写的分隔符。Hive实际上支持8个分隔符级别(对应于ASCII代码1,2，... 8)，但只能覆盖前三个分隔符。
对于嵌套数组Map数据类型字段 sabu 中的项目，你的大小写分隔符将为'\ 004'，因为Map Key Delimiter为'\ 003'（覆盖如'：'）。
例如按照以下格式编写输入文件：
1 | JOHN | abu1 / abu2 | key1：1'\004'2'\004'3 / key2：6'\004'7'\004'8
输出 SELECT * FROM test_stg; 将会是：
1 JOHN ["abu1"，"abu2"] {"key1"：[1,2,3]，"key2"：[6,7,8] }
(3).类型转换
Hive的原子数据类型是可以进行隐式转换的，同Java语言一样，Hive也包括 隐式转换（implicit conversions）和显式转换（explicitly conversions）。
例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST 操作。
隐式类型转换规则如下：
1.任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT
2.所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
3.TINYINT、SMALLINT、INT都可以转换为FLOAT。
4.BOOLEAN类型不可以转换为任何其它的类型。
可以使用CAST操作显示进行数据类型转换，例如CAST('1' AS INT)将把字符串'1' 转换成整数1；如果强制类型转换失败，如执行CAST('X' AS INT)，表达式返回空值 NULL。
对cast有一下几点需要说明的：
(1)、如果将浮点型的数据转换成int类型的，内部操作是通过round()或者floor()函数来实现的，而不是通过cast实现！
(2)、对于BINARY类型的数据，只能将BINARY类型的数据转换成STRING类型。如果你确信BINARY类型数据是一个数字类型(a number)，这时候你可以利用嵌套的cast操作，比如a是一个BINARY，且它是一个数字类型，那么你可以用下面的查询：
SELECT (cast(cast(a as string) as double)) from src;
我们也可以将一个String类型的数据转换成BINARY类型。
(3)、对于Date类型的数据，只能在Date、Timestamp以及String之间进行转换。下表将进行详细的说明：
有效的转换 结果
cast(date as date) 返回date类型
cast(timestamp as date) timestamp中的年/月/日的值是依赖与当地的时区，结果返回date类型
cast(string as date) 如果string是YYYY-MM-DD格式的，则相应的年/月/日的date类型的数据将会返回；但如果string不是YYYY-MM-DD格式的，结果则会返回NULL。
cast(date as timestamp) 基于当地的时区，生成一个对应date的年/月/日的时间戳值
cast(date as string) date所代表的年/月/日时间将会转换成YYYY-MM-DD的字符串。
时间戳类型的数据不包含任务的时区信息，但是to_utc_timestamp和from_utc_timestamp函数可以用于时区转换。DATE类型则表示日期，对应年月日三个部分。
二.Hive常用函数总结
1.字符串操作函数
字符串长度函数：length
语法: length(string A) 返回字符串A的长度;返回值: int
注意:impala中length以字节为单位,一个汉字占3个字节.
举例：hive> select length('abcedfg') from dual;
7
字符串反转函数：reverse
语法: reverse(string A) 返回字符串A的反转结果
举例：hive> select reverse('abcedfg') from dual;
gfdecba
字符串连接函数：concat(string|binary A, string|binary B...)
功能：将多个字符串连接成一个字符串。
语法：concat(str1, str2,...)
返回结果为连接参数产生的字符串,如果有任何一个参数为null，则返回值为null.
例如: concat('foo', 'bar') 结果: 'foobar'
带分隔符字符串连接函数：concat_ws(string SEP, string A, string B...)
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符,可以一次性指定分隔符～（concat_ws就是concat with separator）
需要注意的是分隔符不能为null，如果为null，则返回结果为null。
举例：hive> select concat_ws(',','abc','def','gh') from lxw_dual;
abc,def,gh
字符串截取函数：substr,substring
语法: substr(string A, int start),substring(string A, int start)
返回值: string
说明：返回字符串A从start位置到结尾的字符串
举例：hive> select substr('abcde',3) from dual;
cde
hive> select substring('abcde',3) from dual;
cde
hive> select substr('abcde',-1) from dual; （和ORACLE相同）
e
字符串转大写函数：upper,ucase
语法: upper(string A) ucase(string A) 返回字符串A的大写格式
返回值: string
举例：hive> select upper('abSEd') from dual;
ABSED
hive> select ucase('abSEd') from dual;
ABSED
字符串转小写函数：lower,lcase
语法: lower(string A) lcase(string A) 返回字符串A的小写格式
返回值: string
举例：hive> select lower('abSEd') from dual;
absed
hive> select lcase('abSEd') from dual;
absed
去空格函数：trim
语法: trim(string A) 去除字符串两边的空格
返回值: string
举例：hive> select trim(' abc ') from dual;
abc
左边去空格函数：ltrim
语法: ltrim(string A) 去除字符串左边的空格
返回值: string
举例：hive> select ltrim(' abc ') from dual;
abc
右边去空格函数：rtrim
语法: rtrim(string A) 去除字符串右边的空格
返回值: string
举例：hive> select rtrim(' abc ') from dual;
Abc
分割字符串函数: split
语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str，会返回分割后的字符串数组
举例：hive> select split('abtcdtef','t') from dual;
["ab","cd","ef"]
重复字符串函数：repeat
语法: repeat(string str, int n) 返回重复n次后的str字符串
返回值: string
举例：
hive> select repeat('abc',5) from dual;
abcabcabcabcabc
2.正则匹配函数
正则表达式替换函数：regexp_replace
语法: regexp_replace(string A, string B, string C)
说明：将字符串A中的符合java正则表达式B的部分替换为C。
注意，在有些情况下要使用转义字符.
\d表示匹配所有数字;\D表示匹配所有非数字
举例：hive> select regexp_replace('foobar','oo|ar', '') from dual;
fb
正则表达式解析函数：regexp_extract
语法: regexp_extract(string subject, string pattern, int index)
说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。注意，在有些情况下要使用转义字符
举例：
hive> select regexp_extract('foothebar', 'foo(.?)(bar)', 1) from dual;
the
hive> select regexp_extract('foothebar', 'foo(.?)(bar)', 2) from dual;
bar
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) from dual;
foothebar
3.解析函数
URL解析函数：parse_url
语法: parse_url(string urlString, string partToExtract [, string keyToExtract])
返回值: string
说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
举例：
hive> select parse_url('http://facebook.com/path1/?k1=v1′, 'HOST') from dual;
结果:facebook.com
hive> select parse_url('http://facebook.com/path1/?k1=v1′,'QUERY','k1′) from dual;
结果:v1
json解析函数：get_json_object
语法: get_json_object(string json_string, string path)
说明：解析json的字符串json_string,返回path指定的内容(String类型)。如果输入的json字符串无效，那么返回NULL。
举例：
select get_json_object('{"store":
{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
"bicycle":{"price":19.95,"color":"red"}
},
"email":"amy@only_for_json_udf_test.net",
"owner":"amy"
} , $.owner')
from dual;
结果: amy
4.聚合函数:
1）求总行数（count）
select count(1) from score;
count(*) 所有值不全为NULL时，加1操作
count(1) 不管有没有值，只要有这条记录，值就加1
count(col) col列里面的值为null，值不会加1，这个列里面的值不为NULL，才加1
2）求分数的最大值（max）
select max(s_score) from score;
3）求分数的最小值（min）
select min(s_score) from score;
4）求分数的总和（sum）
select sum(s_score) from score;
5）求分数的平均值（avg）
select avg(s_score) from score;
5.开窗函数--窗口函数与分析函数
开窗函数就是定义一个行为列，简单讲，就是在你查询的结果上，直接多出一列值（可以是聚合值或是排序号），特征就是带有over（）。
使用场景:开窗函数适用于在每一行的最后一列添加聚合函数的结果。
（1）用于分区排序
（2）动态Group By
（3）Top N
（4）累计计算
（5）层次查询
注意:开窗函数只能出现在 SELECT 或 ORDER BY 子句中.
OVER从句
1、使用标准的聚合函数COUNT、SUM、MIN、MAX、AVG
2、使用PARTITION BY语句，使用一个或者多个原始数据类型的列
3、使用PARTITION BY与ORDER BY语句，使用一个或者多个数据类型的分区或者排序列
4、使用窗口规范，窗口规范支持以下格式：
(rows | range) between (unbounded | [num]) preceding and | current row | (unbounded | [num]) following)
(rows | range) between current row and (current row | (unbounded | [num]) following)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
当ORDER BY后面缺少窗口从句条件，窗口规范默认是 range between unbounded preceding and current row.
当ORDER BY和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding and unbounded following.
OVER从句支持以下函数， 但是并不支持和窗口一起使用它们:
Ranking函数: Rank, NTile, DenseRank, CumeDist, PercentRank.
Lead 和 Lag 函数.
聚合函数
--例如:
sum(sales)over(partition by user_type order by sales asc rows between unbounded preceding and current row) as sales_2;
--例如
select shop_id, stat_date, ordamt, sum(ordamt) over win as t
from rt_data
where dt = '2015-01-11' and shop_id = 10026
window win as (distribute by shop_id sort by shop_id, ordamt desc rows between unbounded preceding and current row);
over语句还可以独立出来，用window重写，但需要注意的是，如下sort by使用了多个字段，如果用range指定窗口的话会出错，需要用rows来指定窗口，因为range是对列的比较，不支持多列比较。
注意:结果和ORDER BY相关,默认为升序
如果不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：无界限（起点或终点）
UNBOUNDED PRECEDING：表示从前面的起点
UNBOUNDED FOLLOWING：表示到后面的终点
其他COUNT、AVG，MIN，MAX，和SUM用法一样。
分析函数：用于等级、百分点、n分片等
Ntile
可以看成是：它把有序的数据集合 平均分配 到 指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
如果切片不均匀，默认增加第一个切片的分布
NTILE不支持ROWS BETWEEN
语法是：
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;
-- 例2
select name,orderdate,cost,
ntile(3) over() as sample1 , --全局数据切片
ntile(3) over(partition by name), -- 按照name进行分组,在分组内将数据切成3份
ntile(3) over(order by cost),--全局按照cost升序排列,数据切成3份
ntile(3) over(partition by name order by cost ) --按照name分组，在分组内按照cost升序排列,数据切成3份
from t_window
Rank，Dense_Rank, Row_Number
(hive0.11.0版本开始加入)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 会对所有数值输出不同的序号，序号唯一连续；
注意:当使用order by 排序，desc时NULL值排在首位，ASC时NULL值排在末尾.
可以通过NULLS LAST、NULLS FIRST 控制:
RANK() OVER (ORDER BY column_name DESC NULLS LAST)
PARTITION BY 分组排列顺序
RANK() OVER(PARTITION BY month ORDER BY column_name DESC)
这样，就会按照month 来分，即所需要排列的信息先以month 的值来分组，在分组中排序，各个分组间不干涉
窗口函数 Lag, Lead, First_value,Last_value
可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等。
可以结合聚集函数SUM() 、AVG() 等使用。
可以结合FIRST_VALUE() 和LAST_VALUE()，返回窗口的第一个和最后一个值
Lag, Lead
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值.
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值, 与LAG相反。
lag 和lead 可以获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）.
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(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;
注意:如果不指定ORDER BY，则默认按照记录在文件中的偏移量进行排序，会出现错误的结果
其他开窗函数
1.count() over(partition by ... order by ...)
2.max() over(partition by ... order by ...)
3.min() over(partition by ... order by ...)
4.sum() over(partition by ... order by ...)
5.avg() over(partition by ... order by ...)
以上5个为聚合窗口函数.
6.cume_dist() over(partition by ... order by ...)
小于等于当前值的行数/分组内总行数。
计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布： 小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。如果存在并列情况，则需加上并列的个数-1.
7.PERCENT_RANK() over(partition by ... order by ...)
(当前行的rank值-1)/(分组内的总行数-1)
计算给定行的百分比排名。可以用来计算超过了百分之多少的人。如360小助手开机速度超过了百分之多少的人。
select studentid,departmentid,classid,math,
row_number() over(partition by departmentid,classid order by math) as row_number,
percent_rank() over(partition by departmentid,classid order by math) as percent_rank
from student_scores;
结果
studentid departmentid classid math row_number percent_rank
111 department1 class1 69 1 0.0
113 department1 class1 74 2 0.25
112 department1 class1 80 3 0.5
115 department1 class1 93 4 0.75
114 department1 class1 94 5 1.0
124 department1 class2 70 1 0.0
121 department1 class2 74 2 0.3333333333333333
123 department1 class2 78 3 0.6666666666666666
122 department1 class2 86 4 1.0
结果解释:
studentid=115,percent_rank=(4-1)/(5-1)=0.75
studentid=123,percent_rank=(3-1)/(4-1)=0.6666666666666666
over(partition by ......)与group by 区别:
group by可以实现同样的分组聚合功能，但sql语句不能写与分组聚合无关的字段，否则会报错，即group by与over(partition by ......)主要区别为:带上group by的hive sql语句只能显示与分组聚合相关的字段;而带上over(partition by ......)的hql语句能显示所有字段.
8.ratio_to_report(a) over(partition by b):
按照b分组后a的值在所属分组中总值的占比,a的值必须为数值或数值型字段
9.precentile_cont( x ) within group(order by ...) over()
x为输入的百分比，是0-1之间的一个小数，返回该百分比位置的数据，若没有则返回以下计算值（r）：
a=1+( x *(N-1) ) x为输入的百分比，N为分区内的记录的行数
b=ceil (a) 向上取整
c = floor(a) 向下取整
r=a * 百分比位置上一条数据 + b * 百分比位置下一条数据
10.precentile_disc( x ) within group(order by ...) over():
x为输入的百分比，是0-1之间的一个小数，返回百分比位置对应位置上的数据
值，若没有对应数据值，就取大于该分布值的下一个值
11.stddev() over():计算样本标准差，只有一行数据时返回0
stddev()=sqrt( variance() ) sqrt()--求开方
12.stddev_samp() over():计算样本标准差，只有一行数据时返回null
stddev_samp()=sqrt( var_samp() )
13.stddev_pop() over():计算总体标准差
stddec_pop=sqrt( var_pop() )
14.variance() over():计算样本方差，只有一行数据时返回0
15.var_samp() over():计算样本方差，只有一行数据时返回null
16.var_pop() over():计算总体方差
17.covar_samp over()：返回一对表达式的样本协方差
18.covar_pop over()： 返回一堆表达式的总体协方差
19.corr() over() ：返回一对表达式的相关系数
6.Hive行列转换函数
列转行(多对一)
使用函数：lateral view explode(split(column, ',')) num as 别名
eg: 如表：t_row_to_column_tmp 数据如下，对tag列进行拆分
SQL代码：
select id,tag,tag_new
from t_row_to_column_tmp
lateral view explode(split(tag, ',')) num as tag_new
where id=212022894;
解析和查找：LATERAL VIEW是用来生成用户自定义表以展开的形式显示map或array的值，如同EXPLODE()，但其会忽略值为NULL的列，如果要显示这些列，可以使用LATERAL VIEW OUTER explode(field)(Hive0.12.0之后版本)
行转列(一对多)
使用函数：concat_ws(',',collect_set(column))
说明：collect_list 不去重，collect_set 去重。 column 的数据类型要求是 string.
一般和case when一起使用.
eg：如表：t_column_to_row ，根据id，对tag_new 进行合并
SQL代码1：
select id,concat_ws(',',collect_set(tag_new)) as tag_col
from t_column_to_row group by id;
例二:
原始数据格式:
现在的目标是像转为一行，即：
productid 服务 位置 设施
344920 ** ** **
现在想到的方式case when 结合concat_ws与collect_set实现，代码如下：
select productid
,concat_ws('',collect_set(fuwu)) as 服务
,concat_ws('',collect_set(weizhi)) as 位置
,concat_ws('',collect_set(sheshi)) as 设施
from
( select productid
,case when tagtype='位置' then highlight else '' end as weizhi
,case when tagtype='服务' then highlight else '' end as fuwu
,case when tagtype='设施' then highlight else '' end as sheshi
from dw_htl.tmp_dianpingt
) a
group by productid
效果如下:
例三: 使用case ... when...
序号 分类 明细
1 a a1
2 a a2
1 b b1
2 b b2
1 c c1
2 c c2
select 序号,
(case 分类 when 'a' then 明细 else 0 end) a,
(case 分类 when 'b' then 明细 else 0 end) b,
(case 分类 when 'c' then 明细 else 0 end) c
from tableName
group by 序号;
想查询得到下面的显示:
序号 a b c
1 a1 b1 c1
2 a2 b2 c2
7.高级聚合
GROUPING SETS,Grouping_ID,CUBE,ROLLUP这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。
(1).GROUPING SETS
该关键字可以实现对同一个数据集的多重GROUP BY操作。事实上GROUPING SETS是多个GROUP BY进行UNION ALL操作的简单表达，它仅使用一个stage完成这些操作。GROUPING SETS的子句中如果包换()数据集，则表示整体聚合。
grouping sets为自定义维度，根据需要分组即可。例：
hive>
SELECT name, work_place[0] AS main_place,
count(employee_id) AS emp_id_cnt
FROM employee_id
GROUP BY name, work_place[0]
GROUPING SETS((name, work_place[0]), name, work_place[0], ());
等价于
SELECT name, work_place[0] AS main_place,
count(employee_id) AS emp_id_cnt
FROM employee_id
GROUP BY name, work_place[0]
UNION ALL
SELECT name, NULL AS main_place, count(employee_id) AS emp_id_cnt
FROM employee_id
GROUP BY name
UNION ALL
SELECT NULL AS name, work_place[0] AS main_place,
count(employee_id) AS emp_id_cnt
FROM employee_id
GROUP BY work_place[0]
UNION ALL
SELECT NULL AS name, NULL AS main_place,
count(employee_id) AS emp_id_cnt
FROM employee_id;
然而GROUPING SETS目前还有未解决的问题，参考HIVE-6950
(2).ROLLUP和CUBE
这两个关键字都是GROUP BY的高级实现。cube的分组组合最全，是各个维度值的笛卡尔（包含null）组合，rollup的各维度组合应满足前一维度为null后一位维度必须为null，前一维度取非null时下一维度随意.
对比于规定了n层聚合的GROUPING SETS，ROLLUP会创建n+1层聚合，在此n表示分组列的个数。
GROUP BY a, b, c WITH ROLLUP
等价于 GROUP BY a,b,c GROUPING SETS ((a,b,c),(a,b),(a),())
CUBE将会对分组列进行所有可能的组合聚合。如果为CUBE指定了n列，则将返回2^n个聚合组合。
GROUP BY a, b, c WITH CUBE
等价于 GROUP BY a,b,c GROUPING SETS ((a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),())
hive.new.job.grouping.set.cardinality
是否应启动新的map-reduce作业以对集合/汇总/多维数据集进行分组。
对于如下查询：从T组中选择a，b，c，count（1）a，b，c with rollup;
每行创建4行：（a，b，c），（a，b，null），（a，null，null），（null，null，null）
这可能导致map-reduce边界爆炸，如果T的基数非常高，
并且地图侧聚合不是很好。
此参数决定hive是否应添加其他map-reduce作业。如果分组设置
基数（上例中的4）超过此值，则在
假设原始分组将减小数据大小的情况下添加新的MR作业。
(3).Grouping_ID函数
此函数返回与每列是否存在相对应的位向量。
Grouping__ID函数在Hive 2.3.0中得到修复，因此该版本之前的行为是不同的（这是预期的）。对于每列，如果该列已在该行中聚合，则为结果集中的行生成值"1"，否则该值为"0"。这可用于区分数据中是否存在空值。当我们没有统计某一列时，它的值显示为null，这可能与列本身就有null值冲突，这就需要一种方法区分是没有统计还是值本来就是null。（grouping_id其实就是所统计各列二进制和）.例如:
SELECT key, value, GROUPING__ID, count()
FROM T1
GROUP BY key, value WITH ROLLUP;
将得到以下结果：
第1栏（关键） 第2栏（价值） GROUPING__ID 计数（）
空值 空值 3 6
1 空值 0 2
1 空值 1 1
1 1 0 1
2 空值 1 1
2 2 0 1
3 空值 0 2
3 空值 1 1
3 3 0 1
4 空值 1 1
4 五 0 1
请注意，第三列是选定列的位向量。
对于第一行，没有选择任何列。
对于第二行，两个列都被选中（第二列恰好为空），这解释了值0.
对于第三行，只选择了第一列，这解释了值1。
(4).GROUPING
GROUPING指示对于给定行是否聚合GROUP BY子句中的表达式。值0表示作为分组集的一部分的列，而值1表示不属于分组集的列。 例如:
SELECT key, value, GROUPING__ID,
grouping(key, value), grouping(value, key), grouping(key), grouping(value),
count()
FROM T1
GROUP BY key, value WITH ROLLUP;
此查询将产生以下结果。
第1栏（关键） 第2栏（价值） GROUPING__ID 分组（键，值） 分组（值，键） 分组（钥匙） 分组（值） 计数（）
空值 空值 3 3 3 1 1 6
1 空值 0 0 0 0 0 2
1 空值 1 1 2 0 1 1
1 1 0 0 0 0 0 1
2 空值 1 1 2 0 1 1
2 2 0 0 0 0 0 1
3 空值 0 0 0 0 0 2
3 空值 1 1 2 0 1 1
3 3 0 0 0 0 0 1
4 空值 1 1 2 0 1 1
4 五 0 0 0 0 0 1
8.日期函数
获取年、月、日、小时、分钟、秒、当年第几周
select
year('2018-02-27 10:00:00') as year,
month('2018-02-27 10:00:00') as month,
day('2018-02-27 10:00:00') as day,
hour('2018-02-27 10:00:00') as hour,
minute('2018-02-27 10:00:00') as minute,
second('2018-02-27 10:00:00') as second,
weekofyear('2018-02-27 10:00:00') as weekofyear
返回结果:
获取当前时间:
select
current_timestamp() current_timestamp0,
unix_timestamp() unix_timestamp0,
from_unixtime(unix_timestamp()) from_unixtime0,
current_date() current_date0
返回结果:
from_unixtime：转化unix时间戳到当前时区的时间格式
select from_unixtime(1323308943,'yyyyMMdd');
输出：20111208
select from_unixtime(unix_timestamp('20171205','yyyyMMdd'),'yyyy-MM-dd')
from dual; --20171205转成2017-12-05
unix_timestamp：获取当前unix时间戳
select unix_timestamp();
输出：1430816254
select unix_timestamp('2015-04-30 13:51:20');
输出：1430373080
next_day(date)
计算给出日期date 之后的下一个星期day 的日期。
day 是数字， 1-7分别表示星期日-六；返回日期的格式为"YYYY-MM-DD"
last_day(date)
返回从提供的日期值参数中提取的当月最后一天。
date格式为"yyyy-MM-dd"的字符串(开头为该形式即可)
返回的值是1到31之间的值
如果输入不正确则返回NULL。yyyy-MM-dd 是事先约定的输入格式
add_months( date, n )
返回从提供的日期值参数中提取的当月最后一天。返回日期加上n个月。
日期是开始日期(在添加n个月之前)。n是到目前为止需要增加的月数。
注：目前输入的date格式为"yyyy-MM-dd"或者"yyyyMMdd"（以这个格式开头的都可以接受，否则返回null，下面同上），返回字符串也是这个格式
datediff(string enddate, string startdate)
返回开始日期减去结束日期的天数
select datediff('2015-04-09','2015-04-01');
输出：8
to_char(date,format)
将日期date 转化为一个字符串；date 的格式固定为yyyy-mm-dd hh24:mi:ss:ff3，输出的格式由format 指定。format 当前支持的格式如下（不区分大小写）：
yyyymmdd， 年月日；
yyyymm，年月；
mm，月
dd，日
yyyy-mm-dd
yyyy-mm
yyyymmddhh24miss，年月日时分秒（24小时制）
yyyy-mm-dd hh24:mi:ss
hh24miss
yyyymmddhh24missff3，年月日时分秒毫秒（24小时制）
date_add(string startdate, int days)
在startdate中添加天数:date_add('2008-12-31'， 1) = '2009-01-01'
select date_add('2015-04-09',4);
输出：2015-04-13
date_sub(string startdate, int days)
减去开始日期的天数:date_sub('2008-12-31'， 1) = '2008-12-30'
select date_sub('2015-04-09',4);
输出：2015-04-05
to_date：日期时间转日期函数
select to_date('2015-04-02 13:34:12');
输出：2015-04-02
其他函数
万能反射:reflect(class_name,method_name,column1,column2)
Reflect (Generic) UDF：必须是静态方法
SELECT reflect("java.lang.String", "valueOf", 1),
reflect("java.util.UUID", "randomUUID"),
reflect("java.lang.String", "isEmpty"),
reflect("java.lang.Math", "max", 2, 3),
reflect("java.lang.Math", "min", 2, 3),
reflect("java.lang.Math", "round", 2.5),
reflect("java.lang.Math", "exp", 1.0),
reflect("java.net.URLDecoder", "decode", "%cb%d1%b9%b7","GBK")
FROM src LIMIT 1;
结果: 1 true 3 2 3 2.7182818284590455 搜狗
decode(binary bin, string charset)
使用提供的字符集(' US-ASCII '， ' ISO-8859-1 '， ' UTF-8 '， ' UTF-16BE '， ' UTF-16LE '， ' UTF-16 ')将第一个参数解码为字符串。
如果任一参数为空，结果也为空。(从Hive0.12.0开始)
作用:解决默认转码没有GBK中文字符集的问题
nvl(x,y)空值转换
如果x为null就返回y,不为null就返回自身x
空格字符串函数：space
语法: space(int n) 返回长度为n的字符串
返回值: string
举例：
hive> select space(10) from dual;
hive> select length(space(10)) from dual;
10
首字符ascii函数：ascii
语法: ascii(string str) 返回字符串str第一个字符的ascii码
返回值: int
举例：
hive> select ascii('abcde') from dual;
97
左补足函数：lpad
语法: lpad(string str, int len, string pad) 将str进行用pad进行左补足到len位
返回值: string
说明：与GP，ORACLE不同，pad 不能默认
举例：
hive> select lpad('abc',10,'td') from dual;
tdtdtdtabc
右补足函数：rpad
语法: rpad(string str, int len, string pad) 将str进行用pad进行右补足到len位
返回值: string
举例：
hive> select rpad('abc',10,'td') from dual;
Abctdtdtdt
集合查找函数: find_in_set
语法: find_in_set(string str, string strList)
返回值: int
说明: 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0
举例：
hive> select find_in_set('ab','ef,ab,de') from dual;
2
hive> select find_in_set('at','ef,ab,de') from dual;
0
INSTR((string,str[,start][,appear])
返回string 中str 出现的位置.
start 代表开始搜索的位置，可选参数，默认为1（1表示第一个字符位置）；
appear 指示搜索第几次出现的，可选参数，默认为1；若没有匹配到，返回0。（ start和appear 取值为大于0的整数）
if(boolean testCondition, T valueTrue, T valueFalse)
当testCondition为true时返回valueTrue，否则返回valueFalseOrNull
mod（n1,n2)
返回一个n1 除以n2 的余数。支持的类型有Byte、Short、Integer、Long、Float、Double。返回值的正负和n1 相关。使用此函数需要注意的2 个问题：
对小数执行mod 计算可能会产生精度丢失.
如:mod(3.1415926535897384626,3.1415926535897384627，返回结果为0.0)
传入比MAX_LONG 还大的整数作为参数，则参数会被自动升级成Double 类型，函数也可以正常计算结果，但返回的结果是小数类型。
to_number()
将给出的字符转换为数字；string 必须为全数字串。（ Oracle 中的to_number 很复杂，可变参数且支持多种类型。当前仅支持整数类型，包括short、int、long）
三.Hive内置函数汇总
[官网地址]
数学函数
返回值类型 函数名称(参数) 函数说明
DOUBLE round(DOUBLE a) 返回对a四舍五入的BIGINT值
DOUBLE round(DOUBLE a, INT d) 返回DOUBLE型d的保留n位小数的DOUBLE型的近似值
DOUBLE bround(DOUBLE a) 银行家舍入法（14：舍，69：进，5->前位数是偶：舍，5->前位数是奇：进）
DOUBLE bround(DOUBLE a, INT d) 银行家舍入法,保留d位小数
BIGINT floor(DOUBLE a) 向下取整，最数轴上最接近要求的值的左边的值 如：6.10->6 -3.4->-4
BIGINT ceil(DOUBLE a), ceiling(DOUBLE a) 求其不小于小给定实数的最小整数如：ceil(6) = ceil(6.1)= ceil(6.9) = 6
DOUBLE rand(), rand(INT seed) 每行返回一个DOUBLE型随机数,seed是随机因子
DOUBLE exp(DOUBLE|DECIMAL a) 返回e的a幂次方， a可为小数
DOUBLE ln(DOUBLE|DECIMAL a) 以自然数为底d的对数，a可为小数
DOUBLE log10(DOUBLE|DECIMAL a) 以10为底d的对数，a可为小数
DOUBLE log2(DOUBLE|DECIMAL a) 以2为底数d的对数，a可为小数
DOUBLE log(DOUBLE|DECIMAL base, DOUBLE|DECIMAL a) 以base为底的对数，base 与 a都是DOUBLE类型
DOUBLE pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p) 计算a的p次幂
DOUBLE sqrt(DOUBLE a), sqrt(DECIMAL a) 计算a的平方根
STRING bin(BIGINT a) 计算STRING类型二进制a的STRING类型
STRING hex(BIGINT|STRING|BINARY a) 计算a的STRING类型，若a为STRING类型就转换成字符相对应的十六进制
BINARY unhex(STRING a) hex的逆方法
STRING conv(BIGINT|STRING num, INT from_base, INT to_base) 将GIGINT/STRING类型的num从from_base进制转换成to_base进制
DOUBLE abs(DOUBLE a) 计算a的绝对值
INT or DOUBLE pmod(INT|DOUBLE a, INT|DOUBLE b) a对b取模
DOUBLE sin(DOUBLE|DECIMAL a) 求a的正弦值
DOUBLE asin(DOUBLE|DECIMAL a) 求d的反正弦值
DOUBLE cos(DOUBLE|DECIMAL a) 求余弦值
DOUBLE acos(DOUBLE|DECIMAL a) 求反余弦值
DOUBLE tan(DOUBLE|DECIMAL a) 求正切值
DOUBLE atan(DOUBLE|DECIMAL a) 求反正切值
DOUBLE degrees(DOUBLE|DECIMAL a) 奖弧度值转换角度值
DOUBLE radians(DOUBLE|DECIMAL a) 将角度值转换成弧度值
INT or DOUBLE positive(INT|DOUBLE a) 返回a
INT or DOUBLE negative(INT|DOUBLE a) 返回a的相反数
DOUBLE or INT sign(DOUBLE|DECIMAL a) 如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0
DOUBLE e() 数学常数e
DOUBLE pi() 数学常数pi
BIGINT factorial(INT a) 求a的阶乘
DOUBLE cbrt(DOUBLE a) 求a的立方根
INT BIGINT shiftleft(TINYINT|SMALLINT|INT a,INT b)
shiftleft(BIGINT a, INT b) 按位左移
INT BIGINT shiftright(TINYINT|SMALLINT|INT a, INTb)
shiftright(BIGINT a, INT b) 按拉右移
INT
BIGINT shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b),
shiftrightunsigned(BIGINT a, INT b) 无符号按位右移（<<<）
T greatest(T v1, T v2, ...) 求最大值
T least(T v1, T v2, ...) 求最小值
集合函数
返回值类型 函数名称(参数) 函数说明
int size(Map<K.V>) 求map的长度
int size(Array
array
array
boolean array_contains(Array
array sort_array(Array
类型转换函数
返回值类型 函数名称(参数) 函数说明
binary binary(string|binary) 将输入的值转换成二进制
cast(expr as
日期函数
返回值类型 函数名称(参数) 函数说明
string from_unixtime(bigint unixtime[, string format]) 将时间的秒值转换成format格式（format可为"yyyy-MM-dd hh:mm:ss","yyyy-MM-dd hh","yyyy-MM-dd hh:mm"等等）如from_unixtime(1250111000,"yyyy-MM-dd") 得到2009-03-12
bigint unix_timestamp() 获取本地时区下的时间戳(秒值)
bigint unix_timestamp(string date) 将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳(秒值)
bigint unix_timestamp(string date, string pattern) 将指定时间字符串格式字符串转换成Unix时间戳，如果格式不对返回0 .如：unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400
string to_date(string timestamp) 返回时间字符串的日期部分
int year(string date) 返回时间字符串的年份部分
Int quarter(date/timestamp/string a) 返回季度(从Hive 1.3.0开始)
int month(string date) 返回时间字符串的月份部分
int day(string date) , dayofmonth(date) 返回时间字符串的天
int hour(string date) 返回时间字符串的小时
int minute(string date) 返回时间字符串的分钟
int second(string date) 返回时间字符串的秒
int weekofyear(string date) 返回时间字符串位于一年中的第几个周内. 如weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44
int datediff(string enddate, string startdate) 计算开始时间startdate到结束时间enddate相差的天数
string date_add(string startdate, int days) 从开始时间startdate加上days
string date_sub(string startdate, int days) 从开始时间startdate减去days
timestamp from_utc_timestamp(timestamp, string timezone) 如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳
timestamp to_utc_timestamp(timestamp, string timezone) 如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳
date current_date() 返回当前时间日期
timestamp current_timestamp() 返回当前时间戳
string add_months(string dt, int n) 返回当前时间dt下再增加n个月的日期
string next_day(string start_date, string day_of_week) 返回当前时间的下一个星期X所对应的日期 如：next_day('2015-01-14', 'TU') = 2015-01-20
以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20
string last_day(string date) 返回这个月的最后一天的日期，忽略时分秒部分
string trunc(string date, string format) 返回时间的最开始年份或月份 如trunc("2016-06-26","MM")=2016-06-01, trunc("2016-06-26","YY")=2016-01-01.注意:所支持的格式为MONTH/MON/MM, YEAR/YYYY/YY
double months_between(date1, date2) 返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1<date2,则返回负，否则返回0.0 如：months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677 1997-02-28 10:30:00与1996-10-30相差3.94959677个月
string date_format(date/timestamp/string ts, string fmt) 按指定格式返回时间date 如：date_format("2016-06-22","MM-dd")=06-22
条件函数
返回值类型 函数名称(参数) 函数说明
T if(boolean a, T v1, T v1) 如果条件a为true就返回v1,否则返回v2，（v1，v2为泛型）
T nvl(T value, T default_value) 如果value值为NULL就返回default_value,否则返回value
T COALESCE(T v1, T v2, ...) 返回第一非null的值，如果全部都为NULL就返回NULL 如：COALESCE (NULL,44,55)=44/strong>
T CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END 如果a=b就返回c,a=d就返回e，否则返回f.如CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END将返回4
T CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END 如果a=ture就返回b,c= ture就返回d，否则返回e 如：CASE WHEN 5>0 THEN 5 WHEN 4>0 THEN 4 ELSE 0 END 将返回5；CASE WHEN 5<0 THEN 5 WHEN 4<0 THEN 4 ELSE 0 END 将返回0
boolean isnull( a ) 如果a为null就返回true，否则返回false
boolean isnotnull ( a ) 如果a为非null就返回true，否则返回false
字符函数(字符串操作)
返回值类型 函数名称(参数) 函数说明
int ascii(string str) 返回str中首个ASCII字符串的整数值
string base64(binary bin) 将二进制bin转换成64位的字符串
string concat(string|binary A, string|binary B...) 对二进制字节码或字符串按次序进行拼接
array<struct<string,double>> context_ngrams(array<array
string concat_ws(string SEP, string A, string B...) 与concat()类似，但使用指定的分隔符喜进行分隔
string concat_ws(string SEP, array
string decode(binary bin, string charset) 使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任意输入参数为NULL都将返回NULL
binary encode(string src, string charset) 使用指定的字符集charset将字符串编码成二进制值，支持的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任一输入参数为NULL都将返回NULL
int find_in_set(string str, string strList) 返回以逗号分隔的字符串中str出现的位置,如果参数str为逗号或查找失败将返回0,如果任一参数为NULL将返回NULL.
string format_number(number x, int d) 将数值x的小数位格式化成d位,四舍五入.
string get_json_object(string json_string, string path) 从指定路径上的JSON字符串抽取出JSON对象，并返回这个对象的JSON格式，如果输入的JSON是非法的将返回NULL,注意此路径上JSON字符串只能由数字,字母, 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制
boolean in_file(string str, string filename) 如果文件名为filename的文件中有一行数据与字符串str匹配成功就返回true
int instr(string str, string substr) 查找字符串str中子字符串substr出现的位置,如果查找失败将返回0,如果参数为Null将返回null.注意位置为从1开始.
int length(string A) 返回字符串的长度
int locate(string substr, string str[, int pos]) 查找字符串str中的pos位置后字符串substr第一次出现的位置
string lower(string A) lcase(string A) 将字符串A的所有字母转换成小写字母
string lpad(string str, int len, string pad) 从左边开始对字段使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分
string ltrim(string A) 去掉字符串A前面的空格
array<struct<string,double>> ngrams(array<array
string parse_url(string url, string partToExtract [, string keyToExtract]) 返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分，这个参数包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO,例如：parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2', 'HOST') ='facebook.com'，如果参数partToExtract值为QUERY则必须指定第三个参数key. 如：parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2','QUERY','k1') ='v1'
string printf(String format, Obj... args) 按照printf风格格式输出字符串
string regexp_extract(string subject, string pattern, int index) 抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串，注意转义字符的使用,如第二个参数使用'\s'将被匹配到s,'\s'才是匹配空格
string regexp_replace(string a, string b, string c) 按照Java正则表达式b将字符串a中符合条件的部分成c所指定的字符串，如果c为空，则将符合正则的部分将被去掉。 如：regexp_replace("foobar","oo|ar", "")='fb' .注意转义字符的使用.
string repeat(string str, int n) 重复输出n次字符串str
string reverse(string A) 反转字符串
string rpad(string str, int len, string pad) 从右边开始对字符串str使用字符串pad填充到len长度为止,如果字符串str本身长度比len大的话,将去掉多余的部分
string rtrim(string A) 去掉字符串后面出现的空格
array<array
string space(int n) 返回n个空格
array split(string str, string pat) 按照正则表达式pat来分割字符串str,并将分割后的数组字符串的形式返回
map<string,string> str_to_map(text[, delimiter1, delimiter2]) 将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串,第二个参数是键值对之间的分隔符,默认为逗号;第三个参数是键值之间的分隔符,默认为"="
string substr(string|binary A, int start) substring(string|binary A, int start) 对于字符串A,从start位置开始截取字符串并返回
string substr(string|binary A, int start, int len) , substring(string|binary A, int start, int len) 对于二进制/字符串A,从start位置开始截取长度为length的字符串并返回
string substring_index(string A, string delim, int count) 截取第count分隔符之前的字符串，如count为正则从左边开始截取，如果为负则从右边开始截取
string translate(string|char|varchar input, string|char|varchar from, string|char|varchar to) 将input出现在from中的字符串替换成to中的字符串 如：translate("MOBIN","BIN","M")="MOM"
string trim(string A) 将字符串A前后出现的空格去掉
string Lower(string A) 或lcase(string A) 将字符串A中的字母转换成小写字母
string upper(string A) 或ucase(string A) 将字符串A中的字母转换成大写字母
string initcap(string A) 将字符串A转换第一个字母大写其余字母的字符串
int levenshtein(string A,string B) 计算两个字符串之间的差异大小 如：levenshtein('kitten', 'sitting') = 3
string soundex(string A) 将普通字符串转换成soundex字符串
binary unbase64(string str) 将64位的字符串转换二进制值
聚合函数
返回值类型 函数名称(参数) 函数说明
BIGINT count(*) ; count(expr) 或count(1); count(DISTINCT expr[, expr...]) 统计总行数，包括含有NULL值的行; 统计提供非NULL的expr表达式值的行数; 统计提供非NULL且去重后的expr的行数
DOUBLE sum(col), sum(DISTINCT col) sum(col),表示求指定列的和，sum(DISTINCT col)表示求去重后的列的和
DOUBLE avg(col), avg(DISTINCT col) avg(col),表示求指定列的平均值，avg(DISTINCT col)表示求去重后的列平均值
DOUBLE min(col) 求指定列的最小值
DOUBLE max(col) 求指定列的最大值
DOUBLE variance(col)或var_pop(col) 求指定列数值的方差
DOUBLE var_samp(col) 求指定列数值的样本方差
DOUBLE stddev_pop(col) 求指定列数值的标准偏差
DOUBLE stddev_samp(col) 求指定列数值的样本标准偏差
DOUBLE covar_pop(col1, col2) 求指定列数值的协方差
DOUBLE covar_samp(col1, col2) 求指定列数值的样本协方差
DOUBLE corr(col1, col2) 返回两列数值的相关系数
DOUBLE percentile(BIGINT col, p) 返回col的p%分位数(p∈(0,1)p∈(0,1))
DOUBLE percentile_approx(DOUBLE col, p [,B]) 近似中位数函数,求近似的第pth个百分位数，p必须介于0和1之间，返回类型为double，但是col字段支持浮点类型。参数B控制内存消耗的近似精度，B越大，结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时，结果为准确的百分位数
ARRAY collect_set(col) 返回去重后的集合.
ARRAY collect_list(col) 返回不去重的集合(从Hive 0.13.0开始)
INT ntile(INT x) 将一个有序分区划分为x组，称为bucket，并为分区中的每一行分配一个桶号。这可以方便地计算三元组、四分位数、十分位数、百分位数和其他常见的汇总统计数据。(截至Hive 0.11.0)
表生成函数
返回值类型 函数名称(参数) 函数说明
T explode(array
Tkey,Tvalue explode(MAP<Tkey,Tvalue> m) 每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值
Int,T posexplode(ARRAY) 在explode的基础上返回各元素在数组中的索引值(从0开始)
N rows stack(INT n, v_1, v_2, ..., v_k) 把M列转换成N行，每行有M/N个字段，其中n必须是个常数
tuple json_tuple(jsonStr, k1, k2, ...) 从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值;同一个row，只解析一次.
tuple parse_url_tuple(url, p1, p2, ...) 返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,....是要抽取的部分，这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:
inline(ARRAY<STRUCT[,STRUCT]>) 将结构体数组提取出来并插入到表中
辅助功能类函数
返回值类型 函数名称(参数) 函数说明
string md5(STRING|BINARY A) 计算字符串或二进制文件的MD5 128位校验和(从Hive 1.3.0开始)。该值作为一个由32位十六进制数字组成的字符串返回，如果参数为NULL，则返回NULL。例如: md5('123456')= 'e10adc3949ba59abbe56e057f20f883e'
reflect(class, method[, arg1[, arg2..]]) 通过使用反射匹配参数签名来调用Java方法。(从Hive 0.7.0开始)
参数1:Java中的类名;
参数2:Java类中的静态方法;
后续参数(若有):字段(方法所需参数)
Int hash(a1[, a2...]) 返回参数的哈希值
java_method(class, method[, arg1[, arg2..]]) 反射的同义词
string current_user() 从配置的authenticator管理器返回当前用户名(从Hive 1.2.0开始)。可以与连接时提供的用户相同，但是对于某些身份验证管理器情况可能有所不同
string logged_in_user() 从会话状态返回当前(连接到Hive时提供的)用户名(从Hive 2.2.0开始).
string current_database() 返回当前数据库名称(截至Hive 0.13.0)
string sha1(string/binary), sha(string/binary) (从Hive 1.3.0开始) 计算字符串或二进制文件的SHA-1摘要,并以十六进制字符串的形式返回值.示例:sha1('ABC') = '3c01bdbb26f358bab27f267924aa2c9a03fcfdb8'。 string sha2(string/binary, int)计算SHA-2哈希函数家族(SHA-224、SHA-256、SHA-384和SHA-512)。第一个参数是要散列的字符串或二进制文件。第二个参数表示结果所需的位长，它的值必须是224、256、384、512或0(等于256)。从Java 8开始支持SHA-224。如果参数为NULL或哈希长度不是允许的值之一，则返回值为NULL。示例:sha2('ABC',256) = 'b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78'。
binary aes_encrypt(input string/binary, key string/binary) 使用AES加密输入(从Hive 1.3.0开始)。可以使用128、192或256位的密钥长度。如果安装了Java Cryptography Extension (JCE)无限强度管辖策略文件，则可以使用192和256位密钥。如果参数为NULL或键长度不是允许的值之一，则返回值为NULL。示例:base64(aes_encrypt('ABC','1234567890123456'))=' y6Ss+zCYObpCbgfWfyNWTw')
binary aes_decrypt(input binary, key string/binary) 使用AES解密输入(从Hive 1.3.0开始)。可以使用128、192或256位的密钥长度。如果安装了Java Cryptography Extension (JCE)无限强度管辖策略文件，则可以使用192和256位密钥。如果参数为NULL或键长度不是允许的值之一，则返回值为NULL。示例: aes_decrypt(unbase64('y6Ss+zCYObpCbgfWfyNWTw'),'1234567890123456')='ABC'
string version() 返回Hive版本(从Hive 2.1.0开始)。字符串包含两个字段，第一个是构建号，第二个是构建散列。例如:"select version();"可能返回"2.1.0.2.5.0.0-1245 r027527b9c5ce1a3d7d0b6d2e6de2378fb0c39232"。实际的结果将取决于你的构建
数据屏蔽函数(从Hive 2.1.0开始)
返回值类型 函数名称(参数) 函数说明
string mask(string str[, string upper[, string lower[, string number]]]) 返回一个隐藏的str版本。默认情况下，大写字母转换为"X"，小写字母转换为"X"，数字转换为"n"。例如，mask("abcd-EFGH-8765-4321")将返回xxxx-XXXX-nnnn-nnnn。你可以通过提供额外的参数来覆盖掩码中使用的字符:第二个参数控制大写字母的掩码字符，第三个参数控制小写字母，第四个参数控制数字。例如,mask("abcd-efgh-8765-4321","U","l","#")导致llll-UUUU-########。
string mask_first_n(string str[, int n]) 返回一个带前n个值屏蔽的str屏蔽版本。大写字母转换为"X"，小写字母转换为"X"，数字转换为"n"。例如，mask_first_n("1234-5678-8765-4321"，4)的结果是nnnn-5678-8765-4321。
string mask_last_n(string str[, int n]) 返回一个带最后n个值屏蔽的str屏蔽版本。大写字母转换为"X"，小写字母转换为"X"，数字转换为"n"。例如: mask_last_n("1234-5678-8765-4321",4)生成1234-5678-8765-nnnn。
string mask_show_first_n(string str[, int n]) 返回str的掩码版本，显示未掩码的前n个字符。大写字母转换为"X"，小写字母转换为"X"，数字转换为"n"。例如，mask_show_first_n("1234-5678-8765-4321",4)生成1234-nnnn-nnnn-nnnn -nnnn。
string mask_show_last_n(string str[, int n]) 返回str的掩码版本，显示最后n个未掩码的字符。大写字母转换为"X"，小写字母转换为"X"，数字转换为"n"。例如，mask_show_last_n("1234-5678-8765-4321",4)的结果是nnnn-nnnn-nnnn-4321。
string mask_hash(string|char|varchar str) 返回基于str的散列值。散列是一致的，可以用于跨表连接带屏蔽的值。对于非字符串类型，此函数返回null。
四.Hive自定义函数(UDF,UDAF,UDTF)
1、UDF：User-Defined Funcation
用户定义（普通）函数只对单行数值产生作用.主要解决数据格式以及计算问题.(一对一)
自定义函数时导入依赖(用 hive --vesion 查看当前hive的版本)
Hive的Maven依赖:
继承UDF类，添加方法 evaluate()
//@function 自定义UDF求季度
import org.apache.hadoop.hive.ql.exec.UDF;
public class MyUDF1 extends UDF {
public int evaluate(String date) {
Integer month = Integer.valueOf(date.substring(5, 7));
int quarter = month / 4 + 1;
return quarter;
}
}
使用步骤:
(1)将写好的自定义函数打成jar包上次到服务器;
(2)将jar包添加到hive的classpath路径中:
add JAR /home/hadoop/udf.jar
(3)创建临时函数与开发好的java class关联:
create temporary function quarter as 'com.sun.core.MyUDF1';
(4)此时即可在hql中使用该自定义的函数:
select good_name,quarter (dates) from tb_goods;
Hive的自定义函数的步骤:
1.自定义UDF extends org.apache.hadoop.hive.ql.exec.UDF
2.需要实现evaluate函数，evaluate函数支持重载
3.把程序打包放到目标机器上去
4.进入hive客户端，添加jar包：hive>add jar jar路径
5.创建临时函数：create temporary function 自定义名称AS '自定义UDF的全类名';
6.执行HQL语句；
7.销毁临时函数：hive> drop temporary function 自定义名称
永久函数的使用:
1. 把自定义函数的jar上传到hdfs中.
hdfs dfs -put lower.jar 'hdfs:///path/to/hive_func';
2. 创建永久函数
hive> create function quarter as 'com.sun.core.MyUDF1' using jar 'hdfs://nameservice1/udf/quarter .jar'
3.验证
hive> select quarter ("2018/05/12"),quarter("2018-10-12");
hive> show functions;
2、UDAF：User-Defined Aggregation Funcation
用户自定义聚合函数，可对多行数据产生作用.用来解决输入多行输出一行；
等同与SQL中常用的SUM()，AVG()，也是聚合函数.一般在group by中使用.
聚合函数使用：
SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_name
HAVING SUM(sales) > 1500 ORDER BY SUM(sales);
关键字HAVING总要放在GROUP BY之后，ORDER BY之前.
UDAF实现有简单与通用两种方式：
a.简单UDAF因为使用Java反射导致性能损失，而且有些特性不能使用，已经被弃用了:
(Evaluator需要实现 init、iterate、terminatePartial、merge、terminate这几个函数
init初始化，iterate函数处理读入的行数据，terminatePartial返回iterate处理的中建结果，merge合并上述处理结果，terminate返回最终值)
import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;
import org.apache.hadoop.io.IntWritable;
//UDAF是输入多个数据行，产生一个数据行
//用户自定义的UDAF必须是继承了UDAF，且内部包含多个实现了exec的静态类
public class MaxiNumber extends UDAF {
public static class MaxiNumberIntUDAFEvaluator implements UDAFEvaluator {
// 最终结果
private IntWritable result;
// 负责初始化计算函数并设置它的内部状态，result是存放最终结果的 @Override
public void init() {
result = null;
}
// 每次对一个新值进行聚集计算都会调用iterate方法
public boolean iterate(IntWritable value) {
if (value == null)
return false;
if (result == null)
result = new IntWritable(value.get());
else
result.set(Math.max(result.get(), value.get()));
return true;
}
// Hive需要部分聚集结果的时候会调用该方法
// 会返回一个封装了聚集计算当前状态的对象
public IntWritable terminatePartial() {
return result;
}
// 合并两个部分聚集值会调用这个方法
public boolean merge(IntWritable other) {
return iterate(other);
}
// Hive需要最终聚集结果时候会调用该方法
public IntWritable terminate() {
return result;
}
}
}
b. 另一种涉及两个类：AbstractGenericUDAFResolver、GenericUDAFEvaluator；
继承UDAFResolver类，重写 getEvaluator() 方法；
继承GenericUDAFEvaluator类，生成实例给getEvaluator()；
在GenericUDAFEvaluator类中，重写init()、iterate()、terminatePartial()、merge()、terminate()方法；
resolver负责类型检查，操作符重载。evaluator真正实现UDAF的逻辑。通常来说，顶层UDAF类继承org.apache.hadoop.hive.ql.udf.GenericUDAFResolver2，里面编写嵌套类evaluator 实现UDAF的逻辑。
实现resolver
resolver通常继承org.apache.hadoop.hive.ql.udf.GenericUDAFResolver2，但是我们更建议继承AbstractGenericUDAFResolver，隔离将来hive接口的变化。
GenericUDAFResolver和GenericUDAFResolver2接口的区别是，后面的允许evaluator实现可以访问更多的信息，例如DISTINCT限定符，通配符FUNCTION(*)。
public class GenericUDAFSum extends AbstractGenericUDAFResolver {
static final Log LOG = LogFactory.getLog(GenericUDAFSum.class.getName());
@Override
public GenericUDAFEvaluator getEvaluator(TypeInfo[] parameters)
throws SemanticException {
// Type-checking goes here!
return new GenericUDAFSumLong();
}
public static class GenericUDAFSumLong extends GenericUDAFEvaluator {
// UDAF logic goes here! }
}
这个就是UDAF的代码骨架，第一行创建LOG对象，用来写入警告和错误到hive的log。GenericUDAFResolver只需要重写一个方法：getEvaluator，它根据SQL传入的参数类型,返回正确的evaluator。这里最主要是实现操作符的重载。
getEvaluator的完整代码如下：
public GenericUDAFEvaluator getEvaluator(TypeInfo[] parameters)
throws SemanticException {
if (parameters.length != 1) {
throw new UDFArgumentTypeException(parameters.length - 1,
"Exactly one argument is expected.");
}
if (parameters[0].getCategory() != ObjectInspector.Category.PRIMITIVE) {
throw new UDFArgumentTypeException(0,
"Only primitive type arguments are accepted but "
+ parameters[0].getTypeName() + " is passed.");
}
switch (((PrimitiveTypeInfo) parameters[0]).getPrimitiveCategory()) {
case BYTE:
case SHORT:
case INT:
case LONG:
case TIMESTAMP:
return new GenericUDAFSumLong();
case FLOAT:
case DOUBLE:
case STRING:
return new GenericUDAFSumDouble();
case BOOLEAN:
default:
throw new UDFArgumentTypeException(0,
"Only numeric or string type arguments are accepted but "
+ parameters[0].getTypeName() + " is passed.");
}
}
这里做了类型检查，如果不是原生类型(即符合类型，array,map此类)，则抛出异常，还实现了操作符重载，对于整数类型，使用GenericUDAFSumLong实现UDAF的逻辑，对于浮点类型，使用GenericUDAFSumDouble实现UDAF的逻辑。
实现evaluator
所有evaluators必须继承抽象类:
org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator。
子类必须实现它的一些抽象方法，实现UDAF的逻辑。
GenericUDAFEvaluator有一个嵌套类Mode,这个类很重要，它表示了udaf在mapreduce的各个阶段，理解Mode的含义，就可以理解了hive的UDAF的运行流程。
public static enum Mode {
/**
* PARTIAL1: 这个是mapreduce的map阶段:从原始数据到部分数据聚合
* 将会调用iterate()和terminatePartial()
/
PARTIAL1,
/*
* PARTIAL2: 这个是mapreduce的map端的Combiner阶段，负责在map端合并map的数据::从部分数据聚合到部分数据聚合:
* 将会调用merge() 和 terminatePartial()
/
PARTIAL2,
/*
* FINAL: mapreduce的reduce阶段:从部分数据的聚合到完全聚合
* 将会调用merge()和terminate()
/
FINAL,
/*
* COMPLETE: 如果出现了这个阶段，表示mapreduce只有map，没有reduce，所以map端就直接出结果了:从原始数据直接到完全聚合
* 将会调用 iterate()和terminate()
*/
COMPLETE
};
一般情况下，完整的UDAF逻辑是一个mapreduce过程，如果有mapper和reducer，就会经历PARTIAL1(mapper)，FINAL(reducer)，如果还有combiner，那就会经历PARTIAL1(mapper)，PARTIAL2(combiner)，FINAL(reducer)。
而有一些情况下的mapreduce，只有mapper，而没有reducer，所以就会只有COMPLETE阶段，这个阶段直接输入原始数据，出结果。
下面以GenericUDAFSumLong的evaluator实现讲解
public static class GenericUDAFSumLong extends GenericUDAFEvaluator {
private PrimitiveObjectInspector inputOI;
private LongWritable result;
//这个方法返回了UDAF的返回类型，这里确定了sum自定义函数的返回类型是Long类型 @Override
public ObjectInspector init(Mode m, ObjectInspector[] parameters) throws HiveException {
assert (parameters.length == 1);
super.init(m, parameters);
result = new LongWritable(0);
inputOI = (PrimitiveObjectInspector) parameters[0];
return PrimitiveObjectInspectorFactory.writableLongObjectInspector;
}
/** 存储sum的值的类 */
static class SumLongAgg implements AggregationBuffer {
boolean empty;
long sum;
}
//创建新的聚合计算的需要的内存，用来存储mapper,combiner,reducer运算过程中的相加总和。
@Override
public AggregationBuffer getNewAggregationBuffer() throws HiveException {
SumLongAgg result = new SumLongAgg();
reset(result);
return result;
}
//mapreduce支持mapper和reducer的重用，所以为了兼容，也需要做内存的重用。
@Override
public void reset(AggregationBuffer agg) throws HiveException {
SumLongAgg myagg = (SumLongAgg) agg;
myagg.empty = true;
myagg.sum = 0;
}
private boolean warned = false;
//map阶段调用，只要把保存当前和的对象agg，再加上输入的参数，就可以了。 @Override
public void iterate(AggregationBuffer agg, Object[] parameters) throws HiveException {
assert (parameters.length == 1);
try {
merge(agg, parameters[0]);
} catch (NumberFormatException e) {
if (!warned) {
warned = true;
LOG.warn(getClass().getSimpleName() + " "
+ StringUtils.stringifyException(e));
}
}
}
//mapper结束要返回的结果，还有combiner结束返回的结果 @Override
public Object terminatePartial(AggregationBuffer agg) throws HiveException {
return terminate(agg);
}
//combiner合并map返回的结果，还有reducer合并mapper或combiner返回的结果。 @Override
public void merge(AggregationBuffer agg, Object partial) throws HiveException {
if (partial != null) {
SumLongAgg myagg = (SumLongAgg) agg;
myagg.sum += PrimitiveObjectInspectorUtils.getLong(partial, inputOI);
myagg.empty = false;
}
}
//reducer返回结果，或者是只有mapper，没有reducer时，在mapper端返回结果。 @Override
public Object terminate(AggregationBuffer agg) throws HiveException {
SumLongAgg myagg = (SumLongAgg) agg;
if (myagg.empty) {
return null;
}
result.set(myagg.sum);
return result;
}
}
除了GenericUDAFSumLong，还有重载的GenericUDAFSumDouble，以上代码都在hive的源码:org.apache.hadoop.hive.ql.udf.generic.GenericUDAFSum。
3、UDTF：User-Defined Table-Generating Functions
用户定义表生成函数，用来解决输入一行输出多行；
继承GenericUDTF类，重写initialize（返回输出行信息：列个数，类型）, process, close三方法；
UDTF首先会调用initialize方法，此方法返回UDTF的返回行的信息（返回个数，类型）;初始化完成后，会调用process方法,真正的处理过程在process函数中，在process中，每一次forward()调用产生一行；如果产生多列可以将多个列的值放在一个数组中，然后将该数组传入到forward()函数。
最后close()方法调用，对需要清理的方法进行清理。
下面是一个用来切分"key:value;key:value;"这种字符串，返回结果为key, value两个字段:
import java.util.ArrayList;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
importorg.apache.hadoop.hive.serde2.objectinspector.primitive.
PrimitiveObjectInspectorFactory;
public class ExplodeMap extends GenericUDTF{
@Override
public void close() throws HiveException {}
@Override
public StructObjectInspector initialize(ObjectInspector[] args)
throws UDFArgumentException {
if (args.length != 1) {
throw new UDFArgumentLengthException("ExplodeMap takes only one argument");
}
if (args[0].getCategory() != ObjectInspector.Category.PRIMITIVE) {
throw new UDFArgumentException("ExplodeMap takes string as a parameter");
}
ArrayList<String> fieldNames = new ArrayList<String>();
ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
fieldNames.add("col1");
fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
fieldNames.add("col2");
fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames,fieldOIs);
}
@Override
public void process(Object[] args) throws HiveException {
String input = args[0].toString();
String[] test = input.split(";");
for(int i=0; i<test.length; i++) {
try {
String[] result = test[i].split(":");
forward(result);
} catch (Exception e) {
continue;
}
}
}
}
使用方法
UDTF有两种使用方法，一种直接放到select后面，一种和lateral view一起使用。
1：直接select中使用
select explode_map(properties) as (col1,col2) from src;
不可以添加其他字段使用
select a, explode_map(properties) as (col1,col2) from src;
不可以嵌套调用
select explode_map(explode_map(properties)) from src;
不可以和group by/cluster by/distribute by/sort by一起使用
select explode_map(properties) as (col1,col2) from src group by col1, col2;
2：和lateral view一起使用
select src.id, mytable.col1, mytable.col2 from src lateral view explode_map(properties) mytable as col1, col2;
此方法更为方便日常使用。执行过程相当于单独执行了两次抽取，然后union到一个表里。
注意点:
UDTF将单一输入行转化为多个输出行，并且在使用UDTF时，select语句中不能包含其他的列，UDTF不支持嵌套，也不支持group by 、sort by等语句。如果想避免上述限制，需要使用lateral view语法，案例：
select a.timestamp, get_json_object(a.appevents, '$.eventid'), get_json_object(a.appenvets, '$.eventname') from log a;
select a.timestamp, b.*
from log a lateral view json_tuple(a.appevent, 'eventid', 'eventname') b as f1, f2;
其中，get_json_object为UDF函数，json_tuple为UDTF函数。
UDTF函数在某些应用场景下可以大大提高hql语句的性能，如需要多次解析json或者xml数据的应用场景。
4、HIVE的transform函数的使用
hive中的自定义函数支持Transform和UDF。
UDF是将java代码打包上传，如果你不想写java代码也可以，那就用到了Transform，写一个脚本，通过脚本来处理。
TRANSFORM语言支持通过多种语言，提供了MAP和REDUCE这两个关键字。但MAP和REDUCE一般可理解为只是TRANSFORM的别名。并不代表一般是在map阶段或者是在reduce阶段调用。
默认情况下， transform 函数中带入的参数会被用 '\t 分隔开，并且以字符串对方式传入到用户脚本中。输入中 NULL 值会被转换成字符串 '\N'。脚本的输出使用的分隔符也是 '\t'，同时 '\N' 会再次被转化为 NULL。需要注意的是，当 transform 当参数中含有 '\t' 的时候，用户需要手动处理这些 '\t' 以免脚本里面出错。
UDF所需要的jar包可以放在$HIVE_HOME/auxlib 目录下，当hive启动的时候可以自动去加载，并且UDF可以注册成永久函数;
而Transform需要每次add FILE $HIVE_HOME/auxlib/json-udtf.jar.
Hive的TRANSFORM关键字提供了在SQL中调用自写脚本的功能，适合实现Hive中没有的功能又不想写UDF的情况。
例如，按日期统计每天出现的uid数，通常用如下的SQL
SELECT date, count(uid) FROM xxx GROUP BY date;
如果想在reduce阶段对每天的uid形成一个列表，进行排序并输出，这在Hive中没有现成的功能。
那么，可以自写脚本实现该功能，并用TRANSFORM关键字调用:
SELECT TRANSFORM(date, uid) FROM xxx CLUSTER BY date;
这是一个类似streaming的功能，但是可以更方便的访问Hive中的数据，也可以把SQL语句和自写脚本整合在一起运行。
简单分析官网上的一个例子:
FROM (
FROM pv_users
SELECT TRANSFORM(pv_users.userid, pv_users.date)
USING 'map_script'
AS dt, uid
CLUSTER BY dt
) map_output
INSERT OVERWRITE TABLE pv_users_reduced
SELECT TRANSFORM(map_output.dt, map_output.uid)
USING 'reduce_script'
AS date, count;
'map_script' 的输出，使用 '\t' 分割开以后，对应到 dt 和 uid 两个字段。默认情况下，如果不指定类型，默认认为是 string 类型的。这样一来，就通过脚本（shell、python等）实现了UDF的功能。
上述代码的大致工作流程描述如下：
map_script作为mapper，reduce_script作为reducer。将pv_users表中的userid, date两列作为mapper的输入字段，处理后的输出的前两个字段分别命名为dt, uid，并按照dt字段作partition和sort送给reduce阶段处理。reducer的输入字段为dt和uid，输出处理后的前两个字段，并命名为date, count，写入到pv_users_reduced表中。
这里有几个细节：
(1)mapper和reducer用到的script可以是任何可执行文件。注意如果用到的是本地文件，应当在语句开始前用ADD FILE或ADD FILES将文件加入进来.
(2)mapper和reducer的输入输出都是以TAB为分隔符
(3)如果USING 'script'语句后面没有AS，则Hive默认script的输出中第一个TAB之前的字段为key，后面的部分全部为value。若指定了AS，则严格按照AS后面的字段数输出，例如AS dt, uid，则输出前两个字段并忽略后面的字段。此外，AS语句可以指定数据类型，如AS (date STRING, count INT)。默认都是string类型。
(4)CLUSTER BY关键字是DISTRIBUTE BY和SORT BY的简写，这两者可以认为对应与Hadoop的partition和sort过程。如果partition和sort的key是不同的，可以使用DISTRIBUTE BY和SORT BY分别指定。
(5)MAP和REDUCE关键字是SELECT TRANSFORM关键字的别名，原文中给出了上面等价代码 .
五.采样与锁机制(Hive高级)
聚合和抽样，特别是聚合函数，在大数据处理过程中是处理数据的主要方法。通过自由的条件限制以及聚合函数组合，基本能完成任意要求的数据处理或分组。
当数据集非常大的时候，我们需要找一个子集来加快数据分析。此时我们需要数据采集工具以获得需要的子集。
在此可以使用三种方式获得采样数据：random sampling， bucket sampling， block sampling.
-
Random sampling
使用RAND()函数和LIMIT关键字来获取样例数据。使用DISTRIBUTE和SORT关键字来保证数据是随机分散到mapper和reducer的。ORDER BY RAND()语句可以获得同样的效果，但是性能没这么高。
--Syntax：
SELECT * FROMDISTRIBUTE BY RAND() SORT BY RAND() LIMIT ; -
Bucket table sampling
该方式是最佳化采样bucket表。RAND()函数也可以用来采样整行。如果采样列同时使用了CLUSTERED BY，使用TABLESAMPLE语句会更有效率。
--Syntax：
SELECT * FROMTABLESAMPLE(BUCKET OUT OF ON [colname|RAND()]) table_alias;
例：hive>
CREATE TABLE employee_id_buckets (
name string,
employee_id int,
work_place ARRAY,
sex_age STRUCTsex:string,age:int,
skills_score MAP<string,int>,
depart_title MAP<string,ARRAY>
)
CLUSTERED BY (employee_id) INTO 2 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';
INSERT OVERWRITE TABLE employee_id_buckets SELECT * FROM employee_id;
SELECT name FROM employee_id_buckets TABLESAMPLE(BUCKET 1 OUT OF 2 ON rand()) a;
- Block sampling
该方式允许Hive随机抽取N行数据，数据总量的百分比（n百分比）或N字节的数据。
--Syntax：
SELECT * FROM TableName
TABLESAMPLE(N PERCENT|ByteLengthLiteral|N ROWS) s;
--ByteLengggthLiteral：
--(Digit)+ ('b' | 'B' | 'k' | 'K' | 'm' | 'M' | 'g' | 'G')
例：按行抽样
hive>SELECT name FROM employee_id_buckets TABLESAMPLE(4 ROWS) a;
例：按数据量百分比抽样
hive>SELECT name FROM employee_id_buckets TABLESAMPLE(10 PERCENT) a;
注：此方法有待考证，在Hive0.11.0中将所有25条数据全取出来了，在Hive0.13.0中取出了其中的12条，但是都不符合要求！！
例：按数据大小采样
hive>SELECT name FROM employee_id_buckets TABLESAMPLE(1M) a;
4.Hive锁
Hive结合Zookeeper支持锁功能
Hive中包含了一个使用Zookeeper进行锁定的锁功能。Zookeeper实现了高度可靠的分布式协调服务。处理需要增加一些额外的设置和配置步骤。Zookeeper对于Hive用户来说是透明的。
现在我们来配置Hive，让其可以使用这些Zookeeper节点来启用并发支持：
需要在Hive的配置文件hive-site.xml中增加如下配置
配置好这些属性以后，Hive会对特定的查询自动启动获取锁，用户可以使用如下命令来查看锁：
SHOW LOCKS
SHOW LOCKS
SHOW LOCKS
SHOW LOCKS
对于一些操作，锁的性质是有层次的。例如，一些分区操作，表也是被锁定(例如，保证表的分区正在创建时，表不能被删除)
共享锁(S)和独占/互斥/排他锁(X)：
Hive提供了2种类型的锁：共享锁Shared和互斥锁Exclusive。
用户可以显示的管理锁，假设某个Hive会话对表people创建了一个显示锁：
lock table people exclusive ;
而此时另外一个Hive会话，尝试查询这个被锁定的表:
select * from people ;//失败 conflicting lock.
通过如下命令对表进行解锁以后，才可以进行查询等一些操作：
unlock table people ;
锁模式获取背后的原理：
对于非分区表，锁定模式相当直观。当Hive表正在读取时，一个共享锁Shared(S)被获取。而对其他操作(插入数据、修改表属性)就要获取互斥锁Exclusive(X)。只触发S锁的操作可以并发的执行，只要有一个操作对表或者分区出发了X锁，则该表或者分区不能并发的执行作业。
对于分区表，原理如下：当读取表分区时，会获取表的S锁。对于其他操作，会获取分区的X锁。但是，如果修改仅仅是针对新分区，就会获取表的S锁; 如果修改的是针对所有分区，会获取表的X锁。
所以，当旧分区读写时，新分区也可以被转换为RCFile。
无论何时，分区被锁定时，会获取所有的它的父节点的S锁。
基于这，各种操作触发的锁如下：
备注: load data (local) inpath ' ' into table xx partition() 触发的锁操作同insert。
直接用命令 hadoop dfs -put fileName /destDir 不触发锁。
load data 时若分区不存在会创建分区，而hadoop dfs -put不会。
默认安装hive，hive是使用derby内存数据库保存hive的元数据，这样是不可以并发调用hive的，需要配置为使用mysql保存hive的元数据。
为了避免死锁，这里提出一个非常简单的计划.所有锁定的对象按照字典排序，然后在按照锁定模式获取。注意一些场景，对象列表可能不知道——例如动态分区，编译时不知道正在修改的分区列表。所以，列表会保守生成，由于分区的数量可能不知道，所以会在表或所知的前缀上获取排他锁。
添加两个可配置的参数，决定锁尝试时，锁尝试次数和等待时间。如果重试的次数是非常高的，它可以导致一个活锁。
需要注意的是，不是等待的锁请求将被拒绝。存在的锁将会释放，然后等待的锁会在尝试周期后继续尝试。
因为锁的分层特性，上面列出的规定recipe将无法正常工作：
表T的共享锁规定如下：
调用create( )，创建一个路径名为"/warehouse/T/read-"的节点。协议中，这个锁定的节点会在后面使用。保证设置序列和临时标志。
在锁定的节点调用getChildren( )，不设置watch的标记
如果已经有一个子节点，路径名以"write-"和一个更小的序列号数字开头，已经被获取了，那么这个锁不能被获取。删除第一步骤创建的节点，返回。
否则授权锁。
表T的互斥锁规定如下：
调用create( )，创建一个路径名为"/warehouse/T/write-"的节点。协议中，这个锁定的节点会在后面使用。保证设置序列和临时标志。
在锁定的节点调用getChildren( )，不设置watch的标记
如果已经有一个子节点，路径名以"read-"或者"write-"和一个更小的序列号数字开头，已经被获取了，那么这个锁不能被获取。删除第一步骤创建的节点，返回。
否则授权锁。
拟定的计算，如果遇到长时间的读，为了读饥饿写，会导致写的饥饿。
这个默认hive行为不会改变，并发不会支持。
关闭并发
关闭并发，可以修改下面的变量为false: hive.support.concurrency(默认为true)
Hive解锁操作
使用Hive过程中可能会出现如下情况：在代码正在执行insert into或insert overwrite时，中途手动将程序停掉，会出现卡死情况，只能执行查询操作，而drop insert操作均不可操作，无论执行多久，都会保持卡死状态.
在etl过程中，由于全表全量覆盖，由于各种原因，没有按时操作成功，那么在使用这张表的时候就会出现表被锁，卡死状态，若是关键业务表，那么相关业务任务均会受到牵连。
在进行ddl操作时，排他锁会阻止 ddl 操作(drop、alter table).
如果一个hive查询使用到了表A，执行时间10分钟。在这10分钟内要drop table A，就会产生排它锁。
解锁方式1：ps -ef | grep hive ， 找到执行的hive任务，kill
解锁方式2：进到hive meta 库， SELECT * FROM HIVE_LOCKS;
DELETE FROM HIVE_LOCKS WHERE ....;
解锁分区锁的方法:
Hive Unlock All Partitions
unlock table your_table partition(dt='2014-04-01');
六.Hive注意事项
1.内存溢出
原因：使用了mapjoin，表数据过大，放在内存join时空间不足导致溢出
解决办法：不使用mapjoin，增大reduce数量
2.return code 1 from 。。。。。
原因:读写源数据或目标数据的权限不够
解决办法：赋权限
3.return code 2 from ..........
原因:1.权限
2.数据文件损坏
3.磁盘空间
解决办法：1.赋权限
2.更换损坏的数据文件
3.清理磁盘
4.数据倾斜
原因:空值过多，和业务数据相关，具体情况具体看。
解决办法:目前启动系统的防止数据倾斜参数
hive.groupby.skewindata=true;
5.order by后面可以有多列进行排序，默认按字典排序
order by为全局排序
order by需要reduce操作,且只有一个reduce,与配置无关。数据量很大时慎用.
1.从SQL到HQL(类)的转变
(1).Hive只支持join的等值连接
Hive不支持join的非等值连接,不支持or。分别举例如下及实现解决办法。
不支持不等值连接
错误:select * from a inner join b on a.id<>b.id;
替代方法:select * from a inner join b on a.id=b.id and a.id is null;
不支持or
错误:select * from a inner join b on a.id=b.id or a.name=b.name;
替代方法:select * from a inner join b on a.id=b.id
union all
select * from a inner join b on a.name=b.name;
两个sql的union all的字段名必须一样或者列别名要一样。
(2).分号字符
分号是SQL语句结束标记，在HiveQL中也是，但是在HiveQL中，对分号的识别没有那么智慧，例如：
select concat(key,concat(';',key)) from dual;
但HiveQL在解析语句时提示：
FAILED: Parse Error: line 0:-1 mismatched input '
解决的办法是，使用分号的八进制的ASCII码进行转义，那么上述语句应写成：
select concat(key,concat('\073',key)) from dual;
(3).IS [NOT] NULL
SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False.
(4).Hive不支持将数据插入现有的表或分区中，
仅支持覆盖重写整个表，示例如下：
insert overwrite table t1 select * from t2;
(5).hive不支持INSERT INTO, UPDATE, DELETE操作
这样的话，就不要很复杂的锁机制来读写数据。
INSERT INTO syntax is only available starting in version 0.8。
INSERT INTO就是在表或分区中追加数据。
(6).hive支持嵌入mapreduce程序来处理复杂逻辑
如：
FROM (
MAP doctext USING 'python wc_mapper.py' AS (word, cnt)
FROM docs
CLUSTER BY word
) a
REDUCE word, cnt USING 'python wc_reduce.py';
--doctext: 是输入
--word, cnt: 是map程序的输出
--CLUSTER BY: 将word hash后，又作为reduce程序的输入,并且map程序、reduce程序可以单独使用，如：
FROM (
FROM session_table SELECT sessionid, tstamp, data DISTRIBUTE BY sessionid SORT BY tstamp
) a
REDUCE sessionid, tstamp, data USING 'session_reducer.sh';
--DISTRIBUTE BY: 用于给reduce程序分配行数据
(7).hive支持转换后的数据写入
hive支持将转换后的数据直接写入不同的表，还能写入分区、hdfs和本地目录。这样能免除多次扫描输入表的开销。
FROM t1
INSERT OVERWRITE TABLE t2
SELECT t3.c2, count(1) FROM t3 WHERE t3.c1 <= 20 GROUP BY t3.c2
INSERT OVERWRITE DIRECTORY '/output_dir'
SELECT t3.c2, avg(t3.c1) FROM t3 WHERE t3.c1 > 20 AND t3.c1 <= 30 GROUP BY t3.c2
INSERT OVERWRITE LOCAL DIRECTORY '/home/dir'
SELECT t3.c2, sum(t3.c1) FROM t3 WHERE t3.c1 > 30 GROUP BY t3.c2;
(8).hive0.13版本之前不支持in和not in的子查询
select , where 及 having 之后不能跟子查询语句
(一般使用left join、right join 或者inner join或left semi join替代)
错误：select * from a where a.id in (select id from b)
替代方法: select * from a inner join b on a.id=b.id
或in的另外一种高效实现left semi join:
select a.* from a left semi join b on a.id=b.id
a的id在b的id中，但是只能select a表中的字段，不能把b表中的字段查出来。另外right join 和full join不支持这种写法。
2.其他
(9).HiveQL中String类型的字段若是空字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False，使用left join可以进行筛选行。
(10).group by的字段必须是select后面的字段(非别名).
如果select后面有聚合函数,则该select语句中必须有group by语句;
而且group by后面不能使用别名;
有聚合函数存在就必须有group by.
(11).hive不支持group_concat,可用concat_ws('|',collect_set(str)) 实现.
(12).not in 和 <> 不起作用,可用left join tmp on tableName.id = tmp.id where tmp.id is null 替代实现
(13).hive中用！或者<>符号实现'不等于'，都会将空值即null过滤掉，此时要用where (uid <> '3' or uid is null)或者where (uid != '3' or uid is null)来保留null的情况.
(14).union all 后面的表需要加括号,不然执行报错;
hive也不支持顶层的union all，使用子查询来解决;
union和union all 的子句中不能有DISTRIBUTE BY | SORT BY| ORDER BY | LIMIT 等查询条件.例如：select t.id from (select id,name from tb limit 1 union all select id,name from tb limit 1) t; 会报如下错误:
Failed to recognize predicate '
distributeByClause sortByClause limitClause can only be applied to the whole union.' in statement
(15).把重复关联键少的表放在join前面做关联可以提高join的效率
(16).left semi join的右边表的字段不能出现在select列中.右边的表只能在ON子句中设置过滤条件，在WHERE子句、SELECT子句或其他地方过滤都不行。
(17).为了提高hive sql的执行效率,可以在执行hql语句之前设置性能参数(只针对当前会话有效)。如:
set hive.auto.convert.join=false;
set hive.ignore.mapjoin.hint=false;
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=16;
set hive.groupby.skewindata=true;
set mapred.reduce.tasks=30;
3.Hive并发调用
运行hive，可以有以下访问方式:
1.hiveserver:
hive以thrift服务的服务器形式运行，允许不同的语言编写客户端进行访问，通过thrift，jdbc，odbc连接器和hive服务器与hive通信，这种方式很适合java编程人员通过jdbc接口去访问hive，但是在实践中，发现并发调用时，很容易出现hiveserver无故宕机，没有jvm的dump文件，hiveserver的程序也毫无输出。
2.metastore:
(1).内嵌metastore:默认情况下,metastore和hive是运行在同一个进程里,这种方式经过测试，在并发中是最稳定的，使用这种方式，暂时没有出现问题。
(2).远程metastore:通过配置hive.metastore.local为false,让metastore作为一个单独的进程运行,hive客户端都要连接远程metastore才能执行任务，但是在实践中，一样很容易出现远程metastore无故宕机，同样没有jvm的dump文件，远程metastore程序也毫无输出。
如果多个机器安装了hive，并且多个机器之间共享同一个mysql元数据，那么默认情况下，在并发调用时，会偶尔发生hive报DELETEME找不到的错误。这个错误是hive使用的datanucleus框架的bug，在hive中会去取schme name和catalog,是第三方的库datanucleus在操作，可以看到它创建DELETEME123213一些随机数字的表，然后删掉。目的是为了去获取schme name和catalog。而多个机器在并发过程中，datanucleus发现有DELETEME表，会删除，这个会导致创建了DELETEME的hive进程在访问mysql过程中报错。
要解决这个问题，需要做以下配置:
针对元数据库metastore中的表,分区,视图的编码设置:
metastore 支持数据库级别，表级别的字符集是 latin1，因此只需要把相应注释的地方的字符集由 latin1 改成 utf-8即可。用到注释的就三个地方:表、分区和视图。如下修改分为两个步骤((2)和(3))：
进入hive的元数据库中执行以下5 条 SQL 语句 :
①修改表字段注解和表注解
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
② 修改分区字段注解：
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
③修改索引注解：
alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;
(可选)修改 metastore 的连接 URL:
(12).Hive中虚拟列
在hive中可以直接使用的列。
INPUT__FILE__NAME: maptask中的输入文件.
BLOCK__OFFSET__INSIDE__FILE: 记录在文件中的偏移量.
应用场景: 查找记录所在的文件位置及偏移量.
查找记录所在的文件位置及偏移量
结果:
(13).使用hive重定向导出的数据文件中包含WARN日志记录问题
hive -f 或hive -e重定向导出的数据文件中带有如下两句WARN日志：
WARN: The method class org.apache.commons.logging.impl.SLF4JLogFactory#release() was invoked.
WARN: Please see http://www.slf4j.org/codes.html#release for an explanation.
备注：hive -f与hive -e作用相同。-f是指定hql文件。-e是指定要执行的sql。
检查这是个bug，只要在hql中调用了add jar命令，就会报这个错。
解决办法1：
hive -f xx.hql | grep -v "WARN" > xx.out
解决办法2：
添加“export HIVE_SKIP_SPARK_ASSEMBLY=true;” 到 /etc/profile
echo “export HIVE_SKIP_SPARK_ASSEMBLY=true;” >> /etc/profile
source /etc/profile
解决办法3：
hive -e "select * from aaa" | sed -e 's/\t/,/g' -e '/^WARN:/d' > /root/datas/aaa.csv
解决办法4：
可以使用导出到本地文件系统或者到处到Hive表中:
insert overwrite local directory'/work/adlog/datong/DW_ADS_IMP_out'
row format delimited
fields terminated by '\t'
select ....
这种方式就没有重定向的问题。
七.Hive文件储存
存储格式有：textfile、orcfile、rcfile、parquet、sequencefile、avro
1.TEXTFILE
默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理。源文件可以直接通过hadoop fs -cat 查看.
存储方式：行存储
磁盘开销大,数据解析开销大.
压缩的text文件 hive无法进行合并和拆分.
2.SEQUENCEFILE
一种Hadoop API提供的二进制文件，使用方便、可分割、可压缩等特点。将数据存储成行式格式。
SEQUENCEFILE将数据以< key,value>的形式序列化到文件中。序列化和反序列化使用Hadoop 的标准的Writable 接口实现。key为空，用value 存放实际的值， 这样可以避免map 阶段的排序过程。
三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。文件和Hadoop api中的mapfile是相互兼容的。使用时设置参数:
set hive.exec.compress.output=true;
set io.seqfile.compression.type=BLOCK; – NONE/RECORD/BLOCK
create table test2(str STRING) STORED AS SEQUENCEFILE;
3.RCFILE
一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。
理论上具有高查询效率（但hive官方说效果不明显，只有存储上能省10%的空间，所以不好用，可以不用）。
RCFile结合行存储查询的快速和列存储节省空间的特点
1）同一行的数据位于同一节点，因此元组重构的开销很低；
2) 块内列存储，可以进行列维度的数据压缩，跳过不必要的列读取。
查询过程中，在IO上跳过不关心的列。实际过程是，在map阶段从远端拷贝仍然拷贝整个数据块到本地目录，也并不是真正直接跳过列，而是通过扫描每一个row group的头部定义来实现的。
但是在整个HDFS Block 级别的头部并没有定义每个列从哪个row group起始到哪个row group结束。所以在读取所有列的情况下，RCFile的性能反而没有SequenceFile高。
4.ORC
ORC是hive给出的新格式，属于RCFILE的升级版。
ORC（OptimizedRC File）存储源自于RC（RecordColumnar File）这种存储格式，RC是一种列式存储引擎，对schema演化（修改schema需要重新生成数据）支持较差，而ORC是对RC改进，但它仍对schema演化支持较差，主要是在压缩编码，查询性能方面做了优化。RC/ORC最初是在Hive中得到使用，最后发展势头不错，独立成一个单独的项目。Hive 1.x版本对事务和update操作的支持，便是基于ORC实现的（其他存储格式暂不支持）。ORC发展到今天，已经具备一些非常高级的feature，比如支持update操作，支持ACID，支持struct，array复杂类型。你可以使用复杂类型构建一个类似于parquet的嵌套式数据架构，但当层数非常多时，写起来非常麻烦和复杂，而parquet提供的schema表达方式更容易表示出多级嵌套的数据类型。
ORC是RCfile的升级版，性能有大幅度提升，而且数据可以压缩存储，压缩比和LZO压缩差不多，比text文件压缩比可以达到70%的空间。而且读性能非常高，可以实现高效查询。存储为ORC文件的表使用表属性约束所有客户端都使用相同的选项来存储数据。ORC文件具体属性表如下所示:
属性 默认值 描述
orc.compress ZLIB 列压缩格式,包含ZONE、ZLIB、SNAPPY
orc.compress.size 262144 每一个压缩块大小,默认256KB
orc.stripe.size 67108864 写入字节的内存缓冲区,默认64MB
orc.row.index.stride 10000 索引项之间的行数
orc.create.index true 是否创建索引
orc.bloom.filter.columns "" 列名的逗号分割列表
orc.bloom.filter.fpp 0.05 过滤比率
ORCFile由一个或者多个Stripe组成,每个Stripe默认大小是250MB。每个Stripe由3部分组成:索引数据(Index Data)、行数据(Row Data)和Stripe Footer。
具体介绍https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ORC
注意：
只有TEXTFILE表能直接加载数据，必须本地load数据和external外部表直接加载运路径数据，都只能用TEXTFILE表。更深一步，hive默认支持的压缩文件（hadoop默认支持的压缩格式）也只能用TEXTFILE表直接读取。其他格式不行。可以通过TEXTFILE表加载后insert到其他表中。
换句话说，SequenceFile、RCFile表不能直接加载数据，数据要先导入到textfile表，再从textfile表通过insert select from 导入到SequenceFile,RCFile表。
SequenceFile、RCFile表的源文件不能直接查看，在hive中用select看。
RCFile源文件可以用 hive –service rcfilecat /xxx/000000_0查看，但是格式不同，很乱。
ORCFile源文件可以用 hive –orcfiledump来进行分析ORC存储文件，就可以看到这些信息： hive –orcfiledump < path_to_file>
建表语句如下：
同时，将ORC的表中的NULL取值，由默认的\N改为",
ORC三种创建/使用方式:
- STORED AS ORC;
- ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
with serdeproperties('serialization.null.format' = ") STORED AS ORC;
3,.ROW FORMAT DELIMITED NULL DEFINED AS " STORED AS ORC;
方式一:
create table if not exists test_orc(
advertiser_id string,ad_plan_id string,cnt BIGINT
) partitioned by (day string, type TINYINT COMMENT '0 as bid, 1 as win, 2 as ck', hour TINYINT)
STORED AS ORC;
alter table test_orc set serdeproperties('serialization.null.format' = '');
方式二:
drop table test_orc;create table if not exists test_orc(
advertiser_id string,ad_plan_id string,cnt BIGINT
) partitioned by (day string, type TINYINT COMMENT '0 as bid, 1 as win, 2 as ck', hour TINYINT)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde' with serdeproperties('serialization.null.format' = '')
STORED AS ORC;
方式三:
drop table test_orc;create table if not exists test_orc(
advertiser_id string,ad_plan_id string,cnt BIGINT
) partitioned by (day string, type TINYINT COMMENT '0 as bid, 1 as win, 2 as ck', hour TINYINT)ROW FORMAT DELIMITED
NULL DEFINED AS ''
STORED AS ORC;
查看结果:
hive> show create table test_orc;
CREATE TABLEtest_orc(
advertiser_idstring,ad_plan_idstring,cntbigint)
PARTITIONED BY (
daystring,
typetinyint COMMENT '0 as bid, 1 as win, 2 as ck',
hourtinyint)
ROW FORMAT DELIMITED
NULL DEFINED AS ''
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION 'hdfs://namenode/hivedata/warehouse/pmp.db/test_orc'
TBLPROPERTIES ('transient_lastDdlTime'='1465992916');
5.Parquet
Parquet源自于google Dremel系统（可下载论文参阅），相当于Google Dremel中的数据存储引擎，而Apache顶级开源项目Drill正是Dremel的开源实现。
Apache Parquet 最初的设计动机是存储嵌套式数据，比如Protocolbuffer，thrift，json等，将这类数据存储成列式格式，以方便对其高效压缩和编码，且使用更少的IO操作取出需要的数据，这也是Parquet相比于ORC的优势，它能够透明地将Protobuf和thrift类型的数据进行列式存储，在Protobuf和thrift被广泛使用的今天，与parquet进行集成，是一件非容易和自然的事情。 除了上述优势外，相比于ORC, Parquet没有太多其他可圈可点的地方，比如它不支持update操作（数据写成后不可修改），不支持ACID等。
6.Avro
Avro（读音类似于[ævrə]）是Hadoop的一个子项目，由Hadoop的创始人Doug Cutting牵头开发。Avro是一个数据序列化系统，设计用于支持大批量数据交换的应用,将数据存储成行式格式。它的主要特点有：支持二进制序列化方式，可以便捷、快速地处理大量数据；动态语言友好，Avro提供的机制使动态语言可以方便地处理Avro数据。
如果需要在Hive中使用Avro( Starting in Hive 0.14)，需要在$HIVE_HOME/lib目录下放入以下四个工具包：avro-1.7.1.jar、avro-tools-1.7.4.jar、 jackson-core-asl-1.8.8.jar、jackson-mapper-asl-1.8.8.jar。当然，你也可以把这几个包存在别的路径下面，但是你需要把这四个包放在CLASSPATH中。
为了解析Avro格式的数据，我们可以在Hive建表的时候用下面语句：
hive> CREATE EXTERNAL TABLE tweets
> COMMENT "A table backed by Avro data with the
> Avro schema embedded in the CREATE TABLE statement"
> ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
> STORED AS
> INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
> OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
> LOCATION '/user/wyp/examples/input/'
> TBLPROPERTIES (
> 'avro.schema.literal'='{
> "type": "record",
> "name": "Tweet",
> "namespace": "com.miguno.avro",
> "fields": [
> { "name":"username", "type":"string"},
> { "name":"tweet", "type":"string"},
> { "name":"timestamp", "type":"long"}
> ]
> }'
> );
可将TBLPROPERTIES中的语句写入到一个文件中,然后再引用:
TBLPROPERTIES (
'avro.schema.url'='file:///path/to/the/schema/tweets.avsc');
注意:Hive0.14及之后的版本中可用 STORED AS AVRO 简化建表语句.
具体说明可查阅官网:
https://cwiki.apache.org/confluence/display/Hive/AvroSerDe
然后用Snappy压缩我们需要的数据，下面是压缩前我们的数据：
{ "username": "miguno",
"tweet": "Rock: Nerf paper, scissors is fine.",
"timestamp": 1366150681},
{ "username": "BlizzardCS",
"tweet": "Works as intended. Terran is IMBA.",
"timestamp": 1366154481},
{ "username": "DarkTemplar",
"tweet": "From the shadows I come!",
"timestamp": 1366154681},
{ "username": "VoidRay",
"tweet": "Prismatic core online!",
"timestamp": 1366160000}
压缩完的数据假如存放在/home/wyp/twitter.avsc文件中，我们将这个数据复制到HDFS中的/user/wyp/examples/input/目录下：
hadoop fs -put /home/wyp/twitter.avro /user/wyp/examples/input/
然后我们就可以在Hive中使用了.
7.自定义格式
用户可以通过实现inputformat和 outputformat来自定义输入输出格式。
总结：
在hive中创建表是有如下一个语句: [STORED AS file_format]
file_format 的类型有如下：
默认是文本格式.
textfile 存储空间消耗比较大，并且压缩的text 无法分割和合并,查询的效率最低,可以直接存储，加载数据的速度最高.
sequencefile 存储空间消耗最大,压缩的文件可以分割和合并,查询效率高，需要通过text文件转化来加载.
rcfile 存储空间最小，查询的效率最高 ，需要通过text文件转化来加载，加载的速度最低.
面向行: SequenceFile、Avro、TextFile
面向列: Parquet、RCFile、ORCFile
相比传统的行式存储引擎，列式存储引擎具有更高的压缩比，更少的IO操作而备受青睐（注：列式存储不是万能高效的，很多场景下行式存储仍更加高效），尤其是在数据列（column）数很多，但每次操作仅针对若干列的情景，列式存储引擎的性价比更高。
在互联网大数据应用场景下，大部分情况下，数据量很大且数据字段数目很多，但每次查询数据只针对其中的少数几行，这时候列式存储是极佳的选择
文件格式的压缩比较
实际业务我们是按照列来分析数据及使用数据的。
八.HQL优化及数据倾斜解决方案
数据倾斜就是key分布不均匀，导致分发到不同的reduce上，个别reduce任务特别重，导致其他reduce都完成，而这些个别的reduce迟迟不完成的情况。 发生数据倾斜的原因有
key分布不均匀。
map端数据倾斜，输入文件太多且大小不一 。
reduce端数据倾斜，分区器问题。
业务数据本身的特征。
数据倾斜是进行大数据计算时最经常遇到的问题之一。当我们在执行HiveQL或者运行MapReduce作业时候，如果遇到一直卡在map100%,reduce99%一般就是遇到了数据倾斜的问题。数据倾斜其实是进行分布式计算的时候，某些节点的计算能力比较强或者需要计算的数据比较少，早早执行完了，某些节点计算的能力较差或者由于此节点需要计算的数据比较多，导致出现其他节点的reduce阶段任务执行完成，但是这种节点的数据处理任务还没有执行完成。
在hive中产生数据倾斜的原因和解决方法：
1).group by优化
使用Hive对数据做一些类型统计时会遇到某种类型的数据量特别多，而其他类型数据的数据量特别少。当按照类型进行group by的时候，会将相同的group by字段的reduce任务需要的数据拉取到同一个节点进行聚合，而当其中每一组的数据量过大时，会出现其他组的计算已经完成而这里还没完成，其他节点一直等待这个节点的任务执行完成，所以会看到一直map 100% reduce 99%的情况。
解决方法：set hive.map.aggr=true
set hive.groupby.skewindata=true
原理：hive.map.aggr=true 这个配置项代表是否在map端进行聚合
hive.groupby.skwindata=true 当选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。
2).join优化
Join数据倾斜解决方案
(1)过滤掉脏数据
如果大key是无意义的脏数据，直接过滤掉。本场景中大key无实际意义，为脏数据，直接过滤掉。
(2)数据预处理
数据做一下预处理，尽量保证join的时候，同一个key对应的记录不要有太多。
(3)增加reduce个数
如果数据中出现了多个大key，增加reduce个数，可以让这些大key落到同一个reduce的概率小很多。
(4)转换为mapjoin
如果两个表join的时候，一个表为小表，可以用mapjoin做。
(5)大key单独处理
(6)hive.optimize.skewjoin; 会将一个join SQL 分为两个job。
(7)调整内存设置
适用于那些由于内存超限内务被kill掉的场景。通过加大内存起码能让任务跑起来，不至于被杀掉。该参数不一定会降低任务执行时间。
如：set mapreduce.reduce.memory.mb=5120 ;
set mapreduce.reduce.java.opts=-Xmx5000M -XX:MaxPermSize=128m;
(8)set yarn.nodemanager.vmem-pmem-ratio=1; 调整虚拟机占
set hive.optimize.skewjoin=true;如果是join过程出现倾斜 应该设置为true,会将一个joinSQL 分为两个job。
set hive.skewjoin.key=1000000;--这个是join的键对应的记录条数超过这个值则会进行优化.
小表要注意放在join的左边（目前TCL里面很多都是小表放在join的右边）。
否则会引起磁盘和内存的大量消耗.
对于普通的join操作，会在map端根据key的hash值，shuffle到某一个reduce上去，在reduce端做join连接操作，内存中缓存join左边的表，遍历右边的表，一次做join操作。所以在做join操作时候，将数据量多的表放在join的右边。当数据量比较大，并且key分布不均匀，大量的key都shuffle到一个reduce上了，就出现了数据的倾斜。
Join连接操作中如果存在多个join，且所有参与join的表中其参与join的key都相同，则会将所有的join合并到一个mapred程序中。例如：
select a.val, b.val, c.val from a
join b on (a.key = b.key1)
join c on (c.key = b.key1); --在一个mapre程序中执行join
select a.val, b.val, c.val from a
join b on (a.key = b.key1)
join c on (c.key = b.key2); --在两个mapred程序中执行join
一个大表和一个小表进行join操作时,使用mapjoin 将小表加载到内存中:
如：select /*+ MAPJOIN(a) / a.c1, b.c1 ,b.c2
from a join b
where a.c1 = b.c1;
涉及参数:
set hive.auto.convert.join=true; --启用该参数后，hive会基于表的size自动的将普通join转换成mapjoin。(默认为true)
set hive.mapjoin.smalltable.filesize=2500000; --小于这个参数大小的小表则会变成MapJoin为(默认2500000Byte)
set hive.auto.convert.join.noconditionaltask=true; --开启合并MapJoin.
set hive.auto.convert.join.noconditionaltask.size=10000000; --n个小表大小的合计, 小于这个合计就可以进行合并(默认1000000Byte)。上面的参数设为true时才生效.
遇到需要进行join操作但是关联字段有数据为空时:
解决方法1：id为空的不参与关联
比如：select * from log a
join users b on a.id is not null and a.id = b.id
union all select * from log a where a.id is null;
解决方法2：给空值分配随机的key值
如：select * from log a
left outer join users b
on case when a.user_id is null then concat('hive',rand() )
else a.user_id end = b.user_id;
3).map和reduce优化
1.当出现小文件过多，需要合并小文件。可以通过set hive.merge.mapfiles=true来解决。
2.单个文件大小稍稍大于配置的block块的大写，此时需要适当增加map的个数。解决方法：set mapred.map.tasks个数
3.文件大小适中，但map端计算量非常大，如select id,count(),
sum(case when...),sum(case when...)...需要增加map个数。解决方法：set mapred.map.tasks个数，set mapred.reduce.tasks个数
4).当包含count(distinct)时
如果数据量非常大，执行如select a,count(distinct b) from t group by a;类型的SQL时，会出现数据倾斜的问题。
解决方法：使用sum...group by代替。
如select a,sum(1) from (select a, b from t group by a,b) group by a;
5).表的优化
空key的过滤;空key的转换;空key的打散;
其他查询优化:
计算引擎优化
三种执行引擎：mr、tez、spark
set hive.execution.engine=mr; (默认)
set hive.execution.engine=spark; 配置spark 计算引擎
set hive.execution.engine=tez; 配置tez 计算引擎
如果设置执行引擎为mr，那么就会调用Hadoop的maprecude来运行需要执行job的程序；如果设置执行引擎为spark，那么就会调用spark来执行任务。有条件的话，就设置执行引擎为Spark，因为实在是运行的比Hadoop的MapReduce快了很多。
合并小文件
1.Job合并输出小文件
文件数目过多，会给 HDFS 带来压力，并且会影响处理效率，可以通过合并Map 和Reduce 的结果文件来消除这样的影响：
· hive.merge.mapfiles = true 是否和并 Map 输出文件，默认为 True
· hive.merge.mapredfiles = false 是否合并 Reduce 输出文件，默认为 False
· hive.merge.size.per.task = 25610001000 合并之后的输出文件大小
当输出文件平均大小小于该值,启动新job合并文件:
set hive.merge.smallfiles.avgsize=256000000;
2.job合并输入小文件:
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
合并文件数由mapred.max.split.size限制的大小决定
Hive实现(not) in
通过left outer join进行查询,假设B表中包含另外的一个字段 key1
select a.key from a left outer join b on a.key=b.key where b.keyis null;
通过left semi join 实现 in
SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key);
Left semi join 的限制：join条件中右边的表只能出现在join条件中。
排序优化
Order by 实现全局排序，一个reduce实现，效率低
Sort by 对分区内的数据进行排序，单个reduce输出的结果是有序的，效率高，通常和DISTRIBUTE BY关键字一起使用（DISTRIBUTE BY关键字可以指定map 到 reduce端的分发key）
CLUSTER BY col1 等价于DISTRIBUTE BY col1 SORT BY col1
使用分区
Hive中的每个分区都对应hdfs上的一个目录，分区列也不是表中的一个实际的字段，而是一个或者多个伪列，在表的数据文件中实际上并不保存分区列的信息与数据。Partition关键字中排在前面的为主分区（只有一个），后面的为副分区
静态分区：静态分区在加载数据和使用时都需要在sql语句中指定
案例：(stat_date='20120625',province='hunan')
动态分区：使用动态分区需要设置hive.exec.dynamic.partition参数值为true，默认值为false，在默认情况下，hive会假设主分区时静态分区，副分区使用动态分区；如果想都使用动态分区，需要设置
set hive.exec.dynamic.partition.mode=nostrick，默认为strick
set hive.exec.max.dynamic.partitions=1000
set hive.exec.max.dynamic.partitions.pernode=100：在每一个mapper/reducer节点允许创建的最大分区数
set dfs.datanode.max.xceivers=8192：允许DATANODE打开多少个文件
案例：(stat_date='20120625',province)
Distinct使用
Hive支持在group by时对同一列进行多次distinct操作，却不支持在同一个语句中对多个列进行distinct操作。
Hql使用自定义的mapred脚本
注意事项：在使用自定义的mapred脚本时，关键字MAP REDUCE 是语句SELECT TRANSFORM ( ... )的语法转换，并不意味着使用MAP关键字时会强制产生一个新的map过程，使用REDUCE关键字时会产生一个red过程。
自定义的mapred脚本可以是hql语句完成更为复杂的功能，但是性能比hql语句差了一些，应该尽量避免使用，如有可能，使用UDTF函数来替换自定义的mapred脚本
聚合函数count和sum
Count和sum函数可能是在hql语句中使用的最为频繁的两个聚合函数了，但是在hive中count函数在计算distinct value时支持加入条件过滤。
通用设置
hive.optimize.cp=true：列裁剪
hive.optimize.prunner：分区裁剪
hive.limit.optimize.enable=true：优化LIMIT n语句
hive.limit.row.max.size=1000000：
hive.limit.optimize.limit.file=10：最大文件数
本地模式(小任务)
job的输入数据大小必须小于参数：hive.exec.mode.local.auto.inputbytes.max(默认128MB)
job的map数必须小于参数：hive.exec.mode.local.auto.tasks.max(默认4)
job的reduce数必须为0或者1
hive.exec.mode.local.auto.inputbytes.max=134217728
hive.exec.mode.local.auto.tasks.max=4
set hive.exec.mode.local.auto=true //自动本地模式,测试
set mapred.job.tracker=local
hive.mapred.local.mem：本地模式启动的JVM内存大小
使用索引：
hive.optimize.index.filter：自动使用索引
hive.optimize.index.groupby：使用聚合索引优化GROUP BY操作
JVM重利用
JVM重利用可以是JOB长时间保留slot，直到作业结束，这在对于有较多任务和较多小文件的任务是非常有意义的，减少执行时间。当然这个值不能设置过大，因为有些作业会有reduce任务，如果reduce任务没有完成，则map任务占用的slot不能释放，其他的作业可能就需要等待。
JVM重用是hadoop调优参数的内容，对hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或者task特别多的场景，这类场景大多数执行时间都很短。hadoop默认配置是使用派生JVM来执行map和reduce任务的，这时jvm的启动过程可能会造成相当大的开销，尤其是执行的job包含有成千上万个task任务的情况。
JVM重用可以使得JVM实例在同一个JOB中重新使用N次，N的值可以在Hadoop的mapre-site.xml文件中进行设置:
mapred.job.reuse.jvm.num.tasks
也可在hive的执行设置：
set mapred.job.reuse.jvm.num.tasks=20;
JVM的一个缺点是，开启JVM重用将会一直占用使用到的task插槽，以便进行重用，直到任务完成后才能释放。如果某个"不平衡"的job中有几个reduce task 执行的时间要比其他reduce task消耗的时间多得多的话，那么保留的插槽就会一直空闲着却无法被其他的job使用，直到所有的task都结束了才会释放。
压缩数据
中间压缩就是处理hive查询的多个job之间的数据，对于中间压缩，最好选择一个节省CPU耗时的压缩方式
set hive.exec.compress.intermediate = true;
set hive.intermediate.compression.codec = org.apache.hadoop.io.compress.SnappyCodec;
set hive.intermediate.compression.type=BLOCK;
开启Reduce输出阶段压缩:
当Hive将输出写入到表中时，输出内容同样可以进行压缩。属性hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为true，来开启输出结果压缩功能。
set hive.exec.compress.output=true; #开启hive最终输出数据压缩功能
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;
set mapred.output.compression.type=BLOCK;
或:
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.codec =
org.apache.hadoop.io.compress.SnappyCodec;
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
设置map个数:set mapred.map.tasks=10;
Hive Map 优化
经过以上的分析，在设置map个数的时候，可以简单的总结为以下几点：
(1)如果想增加map个数，则设置mapred.map.tasks为一个较大的值。
(2)如果想减少map个数，则设置mapred.min.split.size为一个较大的值。
情况1：输入文件size巨大，但不是小文件
增大mapred.min.split.size的值
情况2:输入文件数量巨大，且都是小文件，就是单个文件的size小于blockSize。这种情况通过增大mapred.min.split.size不可行，需要使用CombineFileInputFormat将多个input path合并成一个
InputSplit送给mapper处理，从而减少mapper的数量。
map端聚合: set hive.map.aggr=true;
推测执行: mapred.map.tasks.speculative.execution
Hive Shuffle参数优化
Map端
io.sort.mb --设置内存缓冲的大小，在suffle之前
io.sort.record.percent --数据index与实际数据内容的比例。
io.sort.factor --同时打开的文件句柄的数量，默认是10
io.sort.spill.percent --buffer的阈值，默认是0.8，既80%
min.num.spill.for.combine --当job中设定了combiner，并且spill数最少有3个的时候，那么combiner函数就会在merge产生结果文件之前运行。默认为3
Reduce端
mapred.reduce.parallel.copies --Reduce copy数据的线程数量，默认值是5
mapred.reduce.copy.backoff --默认300s.一般情况下不用调整这个值
io.sort.factor --每次并行spill的数量，默认是10
mapred.job.shuffle.input.buffer.percent --buffer大小占reduce可用内存的比例，默认是0.7
mapred.job.shuffle.merge.percent --buffer中的数据达到多少比例开始写入磁盘,默认0.66
mapred.job.reduce.input.buffer.percent --指定多少比例的内存用来存放buffer中的数据,默认是0
Hive Reduce优化
需要reduce操作的查询:
聚合函数
sum,count,distinct...
高级查询
group by,join,distribute by,cluster by ...
order by 比较特殊，只需要一个reduce
推测执行
mapred.reduce.tasks.speculative.execution
hive.mapred.reduce.tasks.speculative.execution
Reduce 优化
set mapred.reduce.tasks=10;直接设置
hive.exec.reducers.max （默认为999）
hive.exec.reducers.bytes.per.reducer 默认:1G
计算公式
numRTasks = min[maxReducers,input.size/perReducer]
maxReducers = hive.exec.reducers.max
perReducer = hive.exec.reducers.bytes.per.reducer
Hive text+gz压缩格式
set hive.exec.compress.output=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;
map数据量调整：
set mapred.max.split.size=536870912 ;
set mapred.min.split.size=536870912 ;
set mapred.min.split.size.per.node=536870912 ;
set mapred.min.split.size.per.rack=536870912 ;
控制Hive中Map和reduce的数量
Hive中的sql查询会生成执行计划，执行计划以MapReduce的方式执行，那么结合数据和集群的大小，map和reduce的数量就会影响到sql执行的效率。
除了要控制Hive生成的Job的数量，也要控制map和reduce的数量。
1.map的数量
通常情况下和split的大小有关系.hive中默认的hive.input.format是org.apache.hadoop.hive.ql.io.CombineHiveInputFormat，对于combineHiveInputFormat,它的输入的map数量由三个配置决定:
mapred.min.split.size.per.node， 一个节点上split的至少的大小
mapred.min.split.size.per.rack 一个交换机下split至少的大小
mapred.max.split.size 一个split最大的大小
它的主要思路是把输入目录下的大文件分成多个map的输入, 并合并小文件, 做为一个map的输入. 具体的原理是下述三步:
a、根据输入目录下的每个文件,如果其长度超过mapred.max.split.size,以block为单位分成多个split(一个split是一个map的输入),每个split的长度都大于mapred.max.split.size, 因为以block为单位, 因此也会大于blockSize, 此文件剩下的长度如果大于mapred.min.split.size.per.node, 则生成一个split, 否则先暂时保留.
b、现在剩下的都是一些长度效短的碎片,把每个rack下碎片合并, 只要长度超过mapred.max.split.size就合并成一个split, 最后如果剩下的碎片比mapred.min.split.size.per.rack大, 就合并成一个split, 否则暂时保留.
c、把不同rack下的碎片合并, 只要长度超过mapred.max.split.size就合并成一个split, 剩下的碎片无论长度, 合并成一个split.
举例: mapred.max.split.size=1000
mapred.min.split.size.per.node=300
mapred.min.split.size.per.rack=100
输入目录下五个文件,rack1下三个文件,长度为2050,1499,10, rack2下两个文件,长度为1010,80. 另外blockSize为500.
经过第一步, 生成五个split: 1000,1000,1000,499,1000. 剩下的碎片为rack1下:50,10; rack2下10:80
由于两个rack下的碎片和都不超过100, 所以经过第二步, split和碎片都没有变化.
第三步,合并四个碎片成一个split, 长度为150.
如果要减少map数量, 可以调大mapred.max.split.size, 否则调小即可.
其特点是: 一个块至多作为一个map的输入，一个文件可能有多个块，一个文件可能因为块多分给做为不同map的输入， 一个map可能处理多个块，可能处理多个文件。
2.reduce数量
可以在hive运行sql的时，打印出来，如下：
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
reduce数量由以下三个参数决定:
mapred.reduce.tasks (强制指定reduce的任务数量)
hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1G）
hive.exec.reducers.max（每个任务最大的reduce数，默认为999）
计算reducer数的公式很简单N=min( hive.exec.reducers.max，总输入数据量/ hive.exec.reducers.bytes.per.reducer)
只有一个reduce的场景：
a、没有group by 的汇总
b、order by
c、笛卡尔积
并行执行
意思是同步执行hive的多个阶段，hive在执行过程，将一个查询转化成一个或者多个阶段。某个特定的job可能包含众多的阶段，而这些阶段可能并非完全相互依赖的，也就是说可以并行执行的，这样可能使得整个job的执行时间缩短.
hive并发执行开启:
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number= 最大并发job数;
优化的场景：
采用一个数据源多份处理的SQL来执行：
FROM TABLE1
INSERT OVERWRITE LOCAL DIRECTORY '/data/data_table/data_table1.txt' SELECT 20140303, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140303 AND col3 >= 20140201 GROUP BY col1, col2
INSERT OVERWRITE LOCAL DIRECTORY '/data/data_table/data_table2.txt' SELECT 20140302, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140302 AND col3 >= 20140131 GROUP BY col1, col2
INSERT OVERWRITE LOCAL DIRECTORY '/data/data_table/data_table3.txt' SELECT 20140301, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140301 AND col3 >= 20140130 GROUP BY col1, col2
INSERT OVERWRITE LOCAL DIRECTORY '/data/data_table/data_table4.txt' SELECT 20140228, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140228 AND col3 >= 20140129 GROUP BY col1, col2
INSERT OVERWRITE LOCAL DIRECTORY '/data/data_table/data_table5.txt' SELECT 20140227, col1, col2, 2160701, COUNT(DISTINCT col) WHERE col3 <= 20140227 AND col3 >= 20140128 GROUP BY col1, col2
………………省略
没设置前，执行时间是450s
设置参数：
set mapred.job.reuse.jvm.num.tasks=20
set hive.exec.reducers.bytes.per.reducer=150000000
set hive.exec.parallel=true;
执行时间缩短到273s ,合理利用一个参数调整，可以达到部分调
严格模式
$hive>set hive.mapred.mode=strict
在严格模式下:1.分区表必须指定分区进行查询;2.order by时必须使用limit子句;3.不允许笛卡尔积.
设置limit优化测，避免全部查询
hive>set hive.limit.optimize.enable=true
典型的业务场景
1.空值产生的数据倾斜
场景：如日志中，常会有信息丢失的问题，比如日志中的 user_id，如果取其中的 user_id 和 用户表中的user_id 关联，会碰到数据倾斜的问题。
解决方法1： user_id为空的不参与关联
select * from log a
join users b
on a.user_id is not null
and a.user_id = b.user_idunion allselect * from log a
where a.user_id is null;
解决方法2 ：赋与空值分新的key值
select *
from log a
left outer join users b
on case when a.user_id is null then concat('hive',rand() ) else a.user_id end = b.user_id;
结论：方法2比方法1效率更好，不但io少了，而且作业数也少了。解决方法1中 log读取两次，jobs是2。解决方法2 job数是1 。这个优化适合无效 id (比如 -99 , '', null 等) 产生的倾斜问题。把空值的 key 变成一个字符串加上随机数，就能把倾斜的数据分到不同的reduce上 ,解决数据倾斜问题。
2.不同数据类型关联产生数据倾斜
场景：用户表中user_id字段为int，log表中user_id字段既有string类型也有int类型。当按照user_id进行两个表的Join操作时，默认的Hash操作会按int型的id来进行分配，这样会导致所有string类型id的记录都分配到一个Reducer中。
解决方法：把数字类型转换成字符串类型
select * from users a
left outer join logs b
on a.usr_id = cast(b.user_id as string);
3.小表不小不大，怎么用 map join 解决倾斜问题
使用 map join 解决小表(记录数少)关联大表的数据倾斜问题，这个方法使用的频率非常高，但如果小表很大，大到map join会出现bug或异常，这时就需要特别的处理。 以下例子:
select * from log a
left outer join users b
on a.user_id = b.user_id;
users 表有 600w+ 的记录，把 users 分发到所有的 map 上也是个不小的开销，而且 map join 不支持这么大的小表。如果用普通的 join，又会碰到数据倾斜的问题。
解决方法：
select /+mapjoin(x)/* from log a
left outer join (
select /+mapjoin(c)/d.*
from ( select distinct user_id from log ) c
join users d
on c.user_id = d.user_id
) x
on a.user_id = b.user_id;
假如，log里user_id有上百万个，这就又回到原来map join问题。所幸，每日的会员uv不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多等等。所以这个方法能解决很多场景下的数据倾斜问题。
总结
使map的输出数据更均匀的分布到reduce中去，是我们的最终目标。由于Hash算法的局限性，按key Hash会或多或少的造成数据倾斜。大量经验表明数据倾斜的原因是人为的建表疏忽或业务逻辑可以规避的。在此给出较为通用的步骤：
1、采样log表，哪些user_id倾斜，得到一个结果表tmp1。由于对计算框架来说，所有的数据过来，他都是不知道数据分布情况的，所以采样是并不可少的。
2、数据的分布符合社会学统计规则，贫富不均。倾斜的key不会太多。所以tmp1记录数会很少。把tmp1和users做map join生成tmp2,把tmp2读到distribute file cache。这是一个map过程。
3、map读入users和log，假如记录来自log,则检查user_id是否在tmp2里，如果是，输出到本地文件a,否则生成<user_id,value>的key,value对，假如记录来自member,生成<user_id,value>的key,value对，进入reduce阶段。
4、最终把a文件，把Stage3 reduce阶段输出的文件合并起写到hdfs。
如果确认业务需要这样倾斜的逻辑，考虑以下的优化方案：
1、对于join，在判断小表不大于1G的情况下，使用map join
2、对于group by或distinct，设定 hive.groupby.skewindata=true
3、尽量使用上述的SQL语句调节进行优化.
九.Hive与HBase集成
[本章节源自:http://www.cnblogs.com/MOBIN/p/5704001.html]
Hive提供了与HBase的集成，使得能够在HBase表上使用HQL语句进行查询 插入操作以及进行Join和Union等复杂查询。
应用场景
1.将ETL操作的数据存入HBase
- HBase作为Hive的数据源
3.构建低延时的数据仓库
集成使用
1.从Hive中创建HBase表
使用HQL语句创建一个指向HBase的Hive表
CREATE TABLE hbase_table_1(key int, value string) //Hive中的表名hbase_table_1
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' //指定存储处理器WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:val") //声明列族,列名
TBLPROPERTIES ("hbase.table.name" = "xyz", "hbase.mapred.output.outputtable" = "xyz");
//hbase.table.name声明HBase表名，为可选属性默认与Hive的表名相同，//hbase.mapred.output.outputtable指定插入数据时写入的表，如果以后需要往该表插入数据就需要指定该值
2.从Hive中映射HBase
创建一个指向已经存在的HBase表的Hive表
CREATE EXTERNAL TABLE hbase_table_2(key int, value string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = "cf1:val")
TBLPROPERTIES("hbase.table.name" = "some_existing_table", "hbase.mapred.output.outputtable" = "some_existing_table");
该Hive表一个外部表，所以删除该表并不会删除HBase表中的数据
注意:
1.建表或映射表的时候如果没有指定:key则第一个列默认就是行键
2.HBase对应的Hive表中没有时间戳概念，默认返回的就是最新版本的值
3.由于HBase中没有数据类型信息，所以在存储数据的时候都转化为String类型
3.多列及多列族的映射
如下表：value1和value2来自列族a对应的b c列，value3来自列族d对应的列
CREATE TABLE hbase_table_1(key int, value1 string, value2 int, value3 int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = ":key,a:b,a:c,d:e"
);
INSERT OVERWRITE TABLE hbase_table_1
SELECT foo, bar, foo+1, foo+2 FROM pokes WHERE foo=98 OR foo=100;
4.Hive Map类型在HBase中的映射规则
如下表:通过Hive的Map数据类型映射HBase表，这样每行都可以有不同的列组合，列名与map中的key对应，列值与map中的value对应
CREATE TABLE hbase_table_1(value map<string,int>, row_key int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = "cf:,:key");
INSERT OVERWRITE TABLE hbase_table_1
SELECT map(bar, foo), foo FROM pokes WHERE foo=98 OR foo=100;
cf为列族，其列名对应map中的bar,列值对应map中的foo
在HBase下查看数据:
在Hive下查看数据:
注意:由于map中的key是作为HBase的列名使用的，所以map中的key类型必须为String类型
以下映射语句都会报错
1.
CREATE TABLE hbase_table_1(key int, value map<int,int>)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf:");
原因：map中的key必须是String
2.
CREATE TABLE hbase_table_1(key int, value string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf:");
原因：当hbase.columns.mapping中的列族后面为空时(形如cf:),说明在Hive中其对应的数据类型为map，而这条语句中对应的是String所以报错
5.Hive还支持简单的复合行键
如下：创建一张指向HBase的Hive表，行键有两个字段，字段之间使用~分隔
CREATE EXTERNAL TABLE delimited_example(
key struct<f1:string, f2:string>,
value string
)
ROW FORMAT DELIMITED
COLLECTION ITEMS TERMINATED BY '~'
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ('hbase.columns.mapping'=':key,f:c1');
6.使用Hive集成HBase表的需注意
(1).对HBase表进行预分区，增大其MapReduce作业的并行度
(2).合理的设计rowkey使其尽可能的分布在预先分区好的Region上
(3).通过set hbase.client.scanner.caching设置合理的扫描缓存
附件一:官网建表说明
'[]' 表示可选，'|' 表示二选一
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]tableName -- (Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later)
[AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]tableName
LIKE existing_table_or_view_name
[LOCATION hdfs_path];
- data_type
- primitive_type
| array_type
| map_type
| struct_type
| union_type -- (Note: Available in Hive 0.7.0 and later) - primitive_type
- TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
| STRING
| BINARY -- (Note: Available in Hive 0.8.0 and later)
| TIMESTAMP -- (Note: Available in Hive 0.8.0 and later)
| DECIMAL -- (Note: Available in Hive 0.11.0 and later)
| DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later)
| DATE -- (Note: Available in Hive 0.12.0 and later)
| VARCHAR -- (Note: Available in Hive 0.12.0 and later)
| CHAR -- (Note: Available in Hive 0.13.0 and later) - array_type
- ARRAY < data_type >
- map_type
- MAP < primitive_type, data_type >
- struct_type
- STRUCT < col_name : data_type [COMMENT col_comment], ...>
- union_type
- UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later)
- row_format
- DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later)
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] - file_format:
- SEQUENCEFILE
| TEXTFILE -- (Default, depending on hive.default.fileformat configuration)
| RCFILE -- (Note: Available in Hive 0.6.0 and later)
| ORC -- (Note: Available in Hive 0.11.0 and later)
| PARQUET -- (Note: Available in Hive 0.13.0 and later)
| AVRO -- (Note: Available in Hive 0.14.0 and later)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname - constraint_specification:
- [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES tableName(col_name, ...) DISABLE NOVALIDATE
附件二:hive中的转义符
Hadoop和Hive都是用UTF-8编码的，所以, 所有中文必须是UTF-8编码,才能正常使用。
备注：中文数据load到表里面, 如果字符集不同，很有可能全是乱码,需要做转码处理, 但是hive本身没有函数来做这个.
1)java的两种情况：
a.正则表达式匹配和string的split函数，这两种情况中字符串包含转义字符""时，需要先对转义字符自身转义，就是说需要两个转义字符"\"。比如\n,\t等（java解析后，再有正则和split自身特定进行解析）。
b.而当匹配字符正斜线""，则需要四个转义字符"\\"。因为，首先java（编译器？）自身先解析，转义成两个"\"，再由正则或split的解析功能转义成一个""，才是最终要处理的字符。这是因为解析过程需要两次，才能在字符串中出现正斜线""，出现后才能转义后面的字符。
2)hive中的split和正则表达式:
hive底层也是java代码写的，所以同Java一样，两种情况也需要两个"\"，以split处理代码为例：
a.split(dealid,'\\')[0] as dealids代码中，如果以""作为分隔符的话，那么就需要4个转义字符"\\"
b.split(all,'~') :这里切分符号是正则表达式，按一个字符分隔没问题
c. split(all,'[|~]+'): 在[]内部拼接成字符串
3）hive语句在shell脚本中执行:
shell语言也有转义字符，自身直接处理。
而hive语句在shell脚本中执行时，就需要先由shell转义后，再由hive处理。这个过程又造成二次转义。
如上面的hive语句写入shell脚本中，执行是错误的，shell先解析，转义成"|"后传给hive，hive解析这个转义字符后，split就无法正确的解析了。
所以，注意hive语句在shell脚本执行时，转义字符需要翻倍。hive处理的是shell转义后的语句，必须转以后正确，才能执行。
注意：是否使用转义字符是看这个字符在这个语言中有没有特殊意义，有的话，就需要加上\来进行双重转义.
转义字符的使用
需转义字符 转义符\ 转义符\ 转义符\
" " \"
\ 不可识别 不可识别 \\
/ / \/ \/
' ' 不可识别 \'
~ ~ \~
| 不可识别 \| \|
; ; \;
: : \:
, , \,
. . \.
! ! \!
( ( \(
) ) \)
[ 不可识别 \[
] ] \]
{ { \{
} } \}
? ? \?
_ _ \_
- - \-
# \#
## \## \##
& & \&
^ ^ \^
