hive语法和常用函数

Hive是一个基于Hadoop分布式系统上的数据仓库，最早是由Facebook公司开发的，Hive极大的推进了Hadoop ecosystem在数据仓库方面上的发展。

Facebook的分析人员中很多工程师比较擅长而SQL而不善于开发MapReduce程序，为此开发出Hive，并对比较熟悉SQL的工程师提供了一套新的SQL-like方言——Hive QL。

Hive SQL方言特别和MySQL方言很像，并提供了Hive QL的编程接口。

Hive QL语句最终被Hive解析器引擎解析为MarReduce程序，作为job提交给Job Tracker运行。

这对MapReduce框架是一个很有力的支持。

Hive是一个数据仓库，它提供了数据仓库的部分功能：数据ETL(抽取、转换、加载)工具，数据存储管理，大数据集的查询和分析能力。

由于Hive是Hadoop上的数据仓库，因此Hive也具有高延迟、批处理的的特性，即使处理很小的数据也会有比较高的延迟。

故此，Hive的性能就和居于传统数据库的数据仓库的性能不能比较了。

Hive不提供数据排序和查询的cache功能，不提供索引功能，不提供在线事物，也不提供实时的查询功能，更不提供实时的记录更性的功能，但是，Hive能很好地处理在不变的超大数据集上的批量的分析处理功能。

Hive是基于hadoop平台的，故有很好的扩展性(可以自适应机器和数据量的动态变化)，高延展性(自定义函数)，良好的容错性，低约束的数据输入格式。

下面我们来看一下Hive的架构和执行流程以及编译流程：
用户提交的Hive QL语句最终被编译为MapReduce程序作为Job提交给Hadoop执行。

Hive的数据类型
Hive的基本数据类型有：TINYINT，SAMLLINT，INT，BIGINT，BOOLEAN，FLOAT，DOUBLE，STRING，TIMESTAMP(V0.8.0+)和BINARY(V0.8.0+)。

Hive的集合类型有：STRUCT，MAP和ARRAY。

Hive主要有四种数据模型(即表)：(内部)表、外部表、分区表和桶表。

表的元数据保存传统的数据库的表中，当前hive只支持Derby和MySQL数据库。

内部表：
Hive中的表和传统数据库中的表在概念上是类似的，Hive的每个表都有自己的存储目录，除了外部表外，所有的表数据都存放在配置在hive-site.xml文件的${hive.metastore.warehouse.dir}/table_name目录下。

Java代码
1.CREATE TABLE IF NOT EXISTS students(user_no INT,name STRING,sex STRING,
2. grade STRING COMMOT '班级'）COMMONT '学生表'
3.ROW FORMAT DELIMITED
4.FIELDS TERMINATED BY ','
5.STORE AS TEXTFILE;
外部表：
外部表指向已经存在在Hadoop HDFS上的数据，除了在删除外部表时只删除元数据而不会删除表数据外，其他和内部表很像。

Java代码
1.CREATE EXTERNAL TABLE IF NOT EXISTS students(user_no INT,name STRING,sex STRING,
2.class STRING COMMOT '班级'）COMMONT '学生表'
3.ROW FORMAT DELIMITED
4.FIELDS TERMINATED BY ','
5.STORE AS SEQUENCEFILE
6.LOCATION '/usr/test/data/students.txt';
分区表：
分区表的每一个分区都对应数据库中相应分区列的一个索引，但是其组织方式和传统的关系型数据库不同。

在Hive中，分区表的每一个分区都对应表下的一个目录，所有的分区的数据都存储在对应的目录中。

比如说，分区表partitinTable有包含nation(国家)、ds(日期)和city(城市)3个分区，其中nation = china，ds = 20130506，city = Shanghai则对应HDFS上的目录为：
/datawarehouse/partitinTable/nation=china/city=Shanghai/ds=20130506/。

Java代码
1.CREATE TABLE IF NOT EXISTS students(user_no INT,name STRING,sex STRING,
2.class STRING COMMOT '班级'）COMMONT '学生表'
3.PARTITIONED BY (ds STRING,country STRING)
4.ROW FORMAT DELIMITED
5.FIELDS TERMINATED BY ','
6.STORE AS SEQUENCEFILE;
分区中定义的变量名不能和表中的列相同。

桶区表：
桶表就是对指定列进行哈希(hash)计算，然后会根据hash值进行切分数据，将具有不同hash 值的数据写到每个桶对应的文件中。

Java代码
1.CREATE TABLE IF NOT EXISTS students(user_no INT,name STRING,sex STRING,
2.class STRING COMMOT '班级',score SMALLINT COMMOT '总分'）COMMONT '学生表'
3.PARTITIONED BY (ds STRING,country STRING)
4.CLUSTERED BY(user_no) SORTED BY(score) INTO 32 BUCKETS
5.ROW FORMAT DELIMITED
6.FIELDS TERMINATED BY ','
7.STORE AS SEQUENCEFILE;
内部表和外部表的主要区别:
1)、内部表创建要2步：表创建和数据加载，这两个过程可以同步执行。

在数据加载的过程中，数据数据会移动到数据仓库的目录中；外部表的创建只需要一个步骤，表创建数据加载同时完成，表数据不会移动。

2)、删除内部表时，会将表数据和表的元数据一同删除；而删除外部表时，紧删除表的元数据而不会删除表数据。

在上一节《深入学习《Programing Hive》：Hive的数据模型(表)》中，已经学习过表的定义，接下来接着学习Hive的DDL操作。

复制表结构：
Java代码
1.CREATE TABLE IF NOT EXISTS students2 LIKE students;
查看表结构：
Java代码
1.DESCRIBE TABLE students;
删除表：
Java代码
1.DROP TABLE students;
创建视图：
Hive中的视图(view)的概念和传统数据库中的表是相同的，是只读的，目前还不支持不支持物化视图。

如果在创建试图后，再将基本表中被视图引用的列修改，那么修改后的数据列将不会体现在视图中；如果基本表被删除或以不兼容的方式被修改，则查询该视图时会失败。

Java代码
1.CREATE VIEW IF NOT EXISTS view_students (user_no,name,score)
2.AS
3.SELECT user_no,name,score FROM students;
删除视图：
Java代码
1.DROP VIEW view_students;
定制表存储方式：
Java代码
1.CREATE TABLE IF NOT EXISTS employees(
2. no STRING,
3. name STRING,
4. salary FLOAT,
5. subordinates ARRAY<STRING>,
6. deductions MAP<STRING,FLOAT>,
7. address STRUCT<street:STRING,district:STRING,city:STRING,province:STRING,ZIP:STRING
>
8.)
9.ROW FORMAT DELIMITED
10.FIELDS TERMINATED BY '\001'
11.COLLECTIN ITEMS TERMINATED BY '\002'
12.MAP KEYS TERMINATED '\003'
13.LINES TERMINATED BY '\n'
14.STORED AS
15.INPUTFORMAT 'org.linkedin.haivvreo.AvroContainerInputFormat'
16.OUTPUTFORMAT 'org.linkedin.haivvreo.AvroContainerOutputFormat';
修改表结构：
1）、重命名表
Java代码
1.ALTER TABLE table_name RENAME TO new_table_name;
只修改了表名，不会改变表结构和数据存放的位置。

2）、修改列名、类型、位置及注释
Java代码
1.CREATE TABLE IF NOT EXISTS test(a INT,b STRING,c FLOAT);//创建测试表
2.//修改列名
3.
4. TABLE test CHANGE a a2 INT;
5./*将a列的名字改为a2，并将其数据类型改为STRING，然后将之放在b列之后；修改
6. * 后的表结构为：b INT,a2 STRING,c FLOAT
7. */
8.ALTER TABLE test CHANGE a a2 STRING AFTER b;
9./*
10. * 将b列的名字改为b2，并将其数据类型由FLOAT改为DOUBLE，然后将之
11. * 放在第一列；修改后的表结构为：
12. * b2 DOUBLE,a STRING,c FLOAT
13. */
14.ALTER TABLE test CHANGE b b2 DOUBLE FIRST;
列的改变只会修改Hive的元数据，而不改变实际的数据。

用户应该确保元数据的定义和实际的数据结构保持一致。

3）、增加新列
Java代码
1.ALTER TABLE test ADD COLOMNS(d INT,e STRING);
4）、改变表的数据存储格式
Java代码
1.ALTER TABLE test SET SEQUENCEFILE;
5）、添加分区Partition
Java代码
1.ALTER TABLE students ADD PARTITION(ds = '2013-05-07',country = 'china')
2. LOCATION '/usr/test/data/test.txt';
6）、删除分区Partition
Java代码
1.ALTER TABLE students DROP PARTITION(ds = '2013-05-07',country = 'china');
7）、创建函数
Java代码
1.CREATE TEMPORARY FIUNCTION aFunc as class_name;
8）、删除函数
Java代码
1.DROP TEMPORARY FIUNCTION aFunc;
9）、显示表
Java代码
1.SHOW TABLES students;
9）、显示分区
Java代码
1.SHOW PARTITIONS students;
本节继续讨论HiveQL，Hive查询语言，如何向Hive表中添加数据，操纵数据和从表中将数据提取到文件系统。

之前已经学习过Hive表的创建，本节主要关注向表中填充数据，使表中有数据可供查询。

先行创建本节要使用的表employees：
Java代码
1.CREATE TABLE employees (
2. name STRING,
3. salary FLOAT,
4. subordinates ARRAY<STRING> COMMENT '下属',
5. deductions MAP<STRING,FLOAT> COMMENT '扣费',
6. address STRUT<street:STRING,city:STRING,state:STRING,zip:INT>
7.)
8.PARTITIONED BY(country STRING,state STRING);
向管理表(非外部表)中加载数据
由于Hive没有行级的insert,update和delete操纵，向表中加载数据的唯一方法就是“批量”加载数据。

如下示例，想一个分区表中加载一批数据：
Java代码
1.LOAD DATA LOCAL INPATH '${env:HOME}/calafornia-employees'
2.OVERWRITE INTO TABLE employees
3.PARTITION (country = 'US', state = 'CA');
在加载数据之前首先要要保证已经创建有符合条件的分区。

在这个示例中，数据被加载后再Hive中的目录为：
hdfs://master_server/user/hive/warehouse/mydb.db/employees/country=US/stat e=CA
这个示例需要注意几点：
关键字OVERWRITE：加上该词说明如果之前已经箱盖分区中加载过数据的话，则之前的数据会首先被“清洗掉”，然后才加载新数据；如果没有加关键字OVERWRITE的话，如：
Java代码
1.LOAD DATA LOCAL INPATH '${env:HOME}/calafornia-employees'
2.INTO TABLE employees
3.PARTITION (country = 'US', state = 'CA');
就不会将已有的数据清洗，而是直接在原有数据后边追加新的数据。

关键字LOCAL：表明是从本地文件系统的文件夹中加载数据，如果不加LOCAL关键字，则表明是从HDFS系统的文件夹中加载数据：
Java代码
1.LOAD DATA INPATH '${env:HOME}/calafornia-employees'
2.INTO TABLE employees
3.PARTITION (country = 'US', state = 'CA');
另外一点就是，对于要指定要加载的文件的路径，指定一个文件夹是比较符合常规的，这要比每次单独指定文件夹中的一个具体的文件要好，这样Hive会一次把指定文件夹下的所有的位安全都拷贝到Hive仓库中。

Hive在加载数据时并不会管要加载的数据是否和表定义模式相匹配，但是会验证文件格式，
比如说，在表中第一的保存的数据文件为SEQUENCEFILE，那么加载后的文件就必须为SEQUENCEFILE文件。

通过HiveQL查询语句添加数据
INSERT语句可以让用户通过一个HiveQL Query语句向Hive表中插入数据。

看下面的一个示例(这里假设已经定义过表staged_employees)：
Java代码
1.INSERT OVERWRITE TABLE employess
2.PARTTITION (country = 'US',state = 'OR')
3.SELECT * FROM staged_employees se
4.WHERE ty = 'US' AND se.st = 'OR';
OVERWRITE关键字，我们已经讨论过。

但对于本示例来说，如果去掉OVERWRITE或用INTO替换掉，如：
Java代码
1.INSERT INTO TABLE employess
2.PARTTITION (country = 'US',state = 'OR')
3.SELECT * FROM staged_employees se
4.WHERE ty = 'US' AND se.st = 'OR';
那么Hive就会将数据以“追加”的方式插入数据到employess表。

这个示例在一个场景中特别有用：数据已经被保存在另外一个表中，如Hive的外部表，然后用户想把某些数据做最终的保存到分区表中；或者从原有的数据源表中将数据保存成符合用的要求的不同的数据记录的格式。

然而，如果staged_employees中数据特别大，有可能你需要执行很多次这样的插入查询导入数据的话，比如说美国部分的数据就要执行65次之多。

Hive提供了另外一种INSERT语法，是你只扫描一次表就可以将数据插入到对应的分区中：
Java代码
1.ROM staged_employees se
2.INSERT INTO TABLE employess
3. PARTTITION (country = 'US',state = 'OR')
4. SELECT * WHERE ty = 'US' AND se.st = 'OR';
5.INSERT INTO TABLE employess
6. PARTTITION (country = 'US',state = 'CA')
7. SELECT * WHERE ty = 'US' AND se.st = 'CA';
8.INSERT INTO TABLE employess
9. PARTTITION (country = 'US',state = 'IL')
10. SELECT * WHERE ty = 'US' AND se.st = 'IL';
11....
这种INSERT语法也可以一次插入多个表。

动态分区添加数据
就上例来说，好友一个问题，那就是如果有特别多的分区要创建的话，用户不得不要写许多HiveQL语句！幸运的是，Hive提供一个叫动态分区的功能，可以基于用户的查询参数推断要创建的分区。

通过与动态分区相比较，之前我们讨论的分区插入数据被称为静态分区数据导入。

将上面的静态分区作如下修改，就变成动态分区数据导入的示例：
Java代码
1.INSERT OVERWRITE TABLE employees
2.PARTITION (country,state)
3.SELECT ...,ty,se.st
4.FROM staged_employees se
对于本例来说，Hive会通过SELECT语句中最后两列ty和se.st的值来决定employees 表中的分区的key，contry和state。

Hive在动态分区插入操作中特别强调源表的数据列的值和要插入分区的key的值的位置关系，而不是通过名字匹配，这也是为什么在staged_employees表中给国家和州定义不同的名字的原因。

假设staged_employees有100个国家和州的匹配对儿，那么执行玩这个HiveQL语句后，employees就会有100个国家分区！
用户也可以混合使用动态分区和静态分区数据插入，如下例中，我们是用了一个静态的country(US)和动态的州的值：
Java代码
1.INSERT OVERWRITE TABLE employees
2.PARTITION (country = 'US',state)
3.SELECT ...,ty,se.st
4.FROM staged_employees se
5.WHERE ty = 'US';
要注意的是，必须要把静态分区的key放在动态分区的key之前。

默认请情况下，动态分区插入的功能是被禁用的，当被激活后，Hive默认会工作在“严格(strict)”模式下。

在“严格(strict)”模式下，必须使用静态分区和动态分区混合使用的方式，这主要是避免一些不好的数据查询设计。

要使用动态模式，用户首先要激活动态分区的一些参数设置:
Java代码
1.hive> set hibe.exec.dynamic.partition=true;
2.hive> set hibe.exec.dynamic.mode=nonstrict;
3.hive> set hibe.exec.max.dynamic.partitions.pernode=1000;
4.
5.hive> INSERT OVERWRITE TABLE employees PARTITION(country,state)
6. > SELECT ...,ty,se.st FROM staged_employees se;
nonstrict可以完全由用户的查询参数来动态创建所有的分区。

在一个HiveQL中创建表和加载数据
在Hive中，用户可以完全使用一条语句创建表并同时加载数据：
Java代码
1.CREATE TABLE ca_employees
2.AS SELECT name salary,address
3.FROM employees
4.WHERE state = 'CA';
这种方法特别适用于在一个大表中提取一个子数据集的场景，这种功能不适合外部表，因外外部表是在定义时直接给其制定一个数据文件的路径。

导出数据
之前讲的都是如何将数据加载到Hive表中，那么如何将表中数据导出表呢？用户可以使用INSERT ... DIRECTORY ...语句导出数据，示例如下：
Java代码
1.INSERT OVERWRITE LOCAL DIRECTORY 'tmp/ca_employees'
2.SELECT name,salary,address
3.FROM employees
4.WHERE state = 'CA';
其中OVERWRITE和LOCAL的意义同上。

和加载数据一样，用户同样可以在一个语句中将数据导出到多个文件夹：
Java代码
1.FROM staged_employees se
2.INSERT OVERWRITE DIRECTORY '/tmp/or_employees'
3. SELECT * FROM ty = 'US' AND se.st ='OR'
4.INSERT OVERWRITE DIRECTORY '/tmp/CA_employees'
5. SELECT * FROM ty = 'US' AND se.st ='CA'
6.INSERT OVERWRITE DIRECTORY '/tmp/IL_employees'
7. SELECT * FROM ty = 'US' AND se.st ='IL'
前几章已经学习过Hive表的定义和数据操纵，本章我们开始学习HiveQL查询。

SELECT ... FROM ...查询
SELECT在SQL中是一个投影操作。

让我们从新来看之前定义过的分区表employees：
Java代码
1.CREATE TABLE employees (
2. name STRING,
3. salary FLOAT,
4. subordinates ARRAY<STRING> COMMENT '下属',
5. deductions MAP<STRING,FLOAT> COMMENT '扣费',
6. address STRUT<street:STRING,city:STRING,state:STRING,zip:INT>
7.)
8.PARTITIONED BY(country STRING,state STRING);
SELECT查询：
Java代码
1.hive> SELECT name,salary FROM employees;
2.John Doe 100000.0
3.Mary Smith 80000.0
4.Todd Jones 70000.0
5.Bill King 60000.0
用户也可以给FROM之后的表，视图或子查询起一个别名，如：
Java代码
1.hive> SELECT ,e.salary FROM employees e;
上面两个HiveQL语句是相同的，给表起别名在JOIN操作中特别有用。

下面我们来看如何查询employees表中的集合类型的数据。

我们先看一下如何查询ARRAY类型的数据，如employees表的下属“subordinates”
Java代码
1.hive> SELECT name,subordinates FROM employees;
2.John Doe ["Mary Smith","Todd Jones"]
3.Mary Smith ["Bill King"]
4.Todd Jones []
5.Bill king []
再看MAP类型的查询，如“deductions”：
Java代码
1.hive> SELECT name,deductions FROM employees;
2.John Doe {"Federal Taxes":0.2,"State Taxes":0.05,"Insurance":0.1}
3.Mary Smith {"Federal Taxes":0.2,"State Taxes":0.05,"Insurance":0.1}
4.Todd Jones {"Federal Taxes":0.15,"State Taxes":0.03,"Insurance":0.1}
5.Bill King {"Federal Taxes":0.15,"State Taxes":0.03,"Insurance":0.1}
再看STRUCT类型的查询，如“address”：
Java代码
1.hive> SELECT name,address FROM employees;
2.John Doe {"Street":"1 Michign Ave.","city":"Chicago","State":"IL","ZIP":60600}
3.Mary Smith {"Street":"100 Ontario St.","city":"Chicago","State":"IL","ZIP":60601}
4.Todd Jones {"Street":"200 Chicago Ave.","city":"Oak Park","State":"IL","ZIP":60700}
5.Bill King {"Street":"300 Obscure Dr.","city":"Obscuria","State":"IL","ZIP":60100}
接下来我们再看如何查看集合性属性字段中的数据：
Java代码
1.hive> SELECT name,subordinates[0],deductions["State Taxes"],address.city FROM employees
;
2.John Doe Mary Smith 0.05 Chicago
3.Mary Smith Bill King 0.05 Chicago
4.Todd Jones NULL 0.03 Oak Park
5.Bill King NULL 0.03 Obscuria
使用正则表达式查询符合条件的列
在Hive查询中，用户可以使用正则表达式查询符合条件的列，下面的实例中就是使用正则表达式的使用用例，可以查询到symbol列和所有以“price”开头的列：
Java代码
1.hive> SELECT symbol,'price.*' FROM stocks;
2.AAPL 195.69197.88194.0194.12194.12
3.AAPL 192.63196.0190.85195.46195.46
4.AAPL 196.73198.37191.57192.05192.05
5.AAPL 195.17200.2194.42199.23199.23
6.AAPL 195.91196.32193.38195.86195.86
7....
列计算
在HiveQL中，用户不但可以从表中查询某些列，还可以通过函数或数学表达式来计算列的值。

例如，我们可以在employees表中查询雇员的姓名，薪水，联邦税百分百及其他列的值：
Java代码
1.hive> SELECT upper(name),salary,deductions["Federal Taxes"],
2. > round(salary * (1 - deductions["Federal Taxes"]))
3. > FROM employees;
4.JOHN DOE 100000.00.280000
5.MARY SMITH 80000.00.264000
6.TODD JONES 70000.00.1559500
7.BILL KING 60000.00.1551000
Hive是使用JAVA写的开源软件，在函数或数学表达式来计算列的值时类型转型和JAVA的转型相同。

聚合函数
要在HiveQL查询中使用聚合函数，必须先将hive.map.aggr配置参数设置为true，举例如下：
Java代码
1.hive> SET hive.map.aggr=true;
2.hibe> SELECT count(*),avg(salary) FROM employees;
但是将
Java代码
1.hive.map.aggr
设置为true会占用更多的内存。

LIMIT
一次典型的HiveQL查询可能会返回所有符合条件的数据记录，但是LIMIT关键字可以限制返回的记录的条数：
Java代码
1.hive> SELECT upper(name),salary,deductions["Federal Taxes"],
2. > round(salary * (1 - deductions["Federal Taxes"]))
3. > FROM employees
4. > LIMIT 2;
5.JOHN DOE 100000.00.280000
6.MARY SMITH 80000.00.264000
给列奇别名
Java代码
1.hive> SELECT upper(name),salary,deductions["Federal Taxes"] AS
2. > fed_taxes,round(salary * (1 - deductions["Federal Taxes"])) AS
3. > salary_minus_fed_taxes
4. > FROM employees
5. > LIMIT 2;
6.JOHN DOE 100000.00.280000
7.MARY SMITH 80000.00.264000
子查询
给列起别名特别适合与子查询中的列，让我们将上个查询示例修改为子查询的使用用例：
Java代码
1.hive> FROM(
2. > SELECT upper(name),salary,deductions["Federal Taxes"] AS
3. > fed_taxes,round(salary * (1 - deductions["Federal Taxes"]))
4. > AS salary_minus_fed_taxes
5. > FROM employees
6. > ) e
7. > SELECT ,e.salary_minus_fed_taxes
8. > WHERE e.salary_minus_fed_taxes > 70000;
9. JOHN DOE 100000.00.280000
CASE ... WHEN ... THEN语句
CASE ... WHEN ... THEN向标准的SQL语句中一样使用在SELECT列中，对某一个列的返回值做判断，示例如下：
Java代码
1.hive> SELECT name,salary,
2. > CASE
3. > WHEN salary < 50000.0 THEN 'low'
4. > WHEN salary >= 50000.0 AND salary < 70000.0 THEN 'middle'
5. > WHEN salary >= 70000.0 AND salay < 100000.0 THEN 'high'
6. ELSE 'very high'
7. > END AS bracket FROM employees;
8. John Doe 100000.0 very high
9. Mary Smith 80000.0 high
10. Todd Jones 70000.0 high
11. Bill King 60000.0 middle
12. Boss Man 200000.0 very high
13. Fred Finance 150000.0 very high
14. Stcy Accountant 60000.0 middle
WHERE过滤条件
SELECT决定返回哪些数据列，而WHERE决定返回那些符合条件的数据：
Java代码
1.hive> SELECT name,salary,deductions["Federal Taxes"],
2. > salary * (1 - deductions["Federal Taxes"])
3. > FROM employees
4. > WHERE round(salary * (1 - deductions["Federal Taxes"])) >
5. > 70000;
6.ohn Doe 100000.00.280000.0
该示例有一个问题，那就是salary * (1 - deductions["Federal Taxes"])分别在SELECT部分和WHERE部分都执行了，性能上不是多优化。

那么，对salary * (1 - deductions["Federal Taxes"])使用别名能否消除这种冲突呢？，不幸的是这是无效的：
Java代码
1. hive> SELECT name,salary,deductions["Federal Taxes"],
2. > salary * (1 - deductions["Federal Taxes"]) AS
3. > salary_minus_fed_taxes
4. > FROM employees
5. > WHERE round(salary_minus_fed_taxes) > 70000;
6.FAILED:Error in semantic analysis: Line 4:13 Invalid table alias or
7.colomn reference 'salary_minus_fed_taxes': (possible colomn names
8.are: name,salary,subordinates,deductions,address)
如错误信息中所说，用户不能在WHERE部分中引用列的别名，那么我们是否可以使用其他办法来消除这种冲突呢？答案是使用子查询：
Java代码
1.hive> SELECT e.* FROM
2. > (SELECT name,salary,deductions["Federal Taxes"] AS ded,
3. > salary * (1 - deductions["Federal Taxes"]) AS
4. > salary_minus_fed_taxes
5. > FROM employees) e
6. > WHERE round(salary_minus_fed_taxes) > 70000;
浮点比较陷阱
在WHERE查询条件中：在比较不同类型的数值(如FLOAT vs DOUBLE)时，会引发浮点比较陷阱。

看下面的HiveQL语句，本来只想要查询Federal Taxes > 0.2，但是返回结果如下：
Java代码
1.hive> SELECT name,salary,deductions['Federal Taxes']
2. > FROM employees WHERE deductions['Federal Taxes'] > 0.2;
3.John Doe 100000.00.2
4.Mary Smith 80000.00.2
5.Boss Man 200000.00.3
6.Fred Finance 150000.00.3
我们发现，为什不符合过滤条件(deductions('Federal Taxes') == 0.2)的结果也返回了？！这就是浮点比较陷阱引发的。

那么如何来避免这个陷阱呢？那就要将0.2强制转型了：
Java代码
1.hive> SELECT name,salary,deductions['Federal Taxes']
2. > FROM employees
3. > WHERE deductions['Federal Taxes'] > cast(0.2 AS FLOAT);
4.Boss Man 200000.00.3
5.Fred Finance 150000.00.3
LIKE和RLIKE
LIKE标识模糊查询：
Java代码
1.hive> SELECT name,address.street FROM employees WHERE address.street LIKE '%Ave.';
2.John Doe 1 Michigan Ave.
3.Todd Hones 200 Chicago Ave.
4.
5. hive> SELECT name,address.street FROM employees WHERE address.street LIKE '%Chi%';
6.Todd Hones 200 Chicago Ave.
RLIKE是使用正则表达式：
Java代码
1.hive> SELECT name,address.street FROM employees
2. > WHERE address.street RLIKE '.*(Chicago|Ontario).*';
3.Mary Smith 100 Ontario St.
4.Todd Jones 200 Chicago Ave.
GROUP BY
Hive的GROUP BY语句和传统的SQL相同，经常要和聚合函数一块儿使用，我们再看一下stocks表的定义：
Java代码
1.CREAT EXTENAL TABLE IF NOT EXISTS stocks(
2. exchange STRING,
3. symbol STRING,
4. ymd STRING,
5. price_open FLOAT,
6. price_high FLOAT,
7. price_low FLOAT,
8. price_close FLOAT,
9. volume INT,
10. price_adj_close FLOAT)
11. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
12. LOCATION '/data/stocks';
示例如下：
Java代码
1.hive> SELECT year(ymd),avg(price_close) FROM stocks
2. > WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'
3. > GROUP BY year(ymd);
4.19842
5.578625440597534
5.198520.193676221040867
6.198632.461
7....
HAVING
Hive中的HAVING关键字和传统SQL中的概念相同，是对分组后的结果再次过滤。

使用HAVING 可以避免GROUP BY后的子查询：
Java代码
1.hive> SELECT year(ymd),avg(price_close) FROM stocks
2. > WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'
3. > GROUP BY year(ymd)
4. > HAVING avg(price_close) > 50.0;
5.198753.88968399108163
6.199152.49553383386182
7.199254.819
8.200071.74892876261757
9....
如果不使用HAVING，那么就要使用子查询：
Java代码
1.hive> SELECT s
2.year,s2.avg FROM
2. > (SELECT year(ymd) AS year,avg(price_close) AS avg FROM stocks
3. > WHERE exchange = 'NASDAQ' AND symbol = 'AAPL'
4. > GROUP BY year(ymd)
5. > ) s2
6. > WHERE s2.avg > 50.0
JION
Hive支持典型的SQL JION连接，但只支持等值连接。

Hive中的内连接、左外连接、右外连接和全外连接和标准的SQL中的连接有相同的概念；但是在Hive中没有IN、EXISTS关键字的使用，取代这两个关键字的功能的是LEFT SEMI-JION，也是对Inner JION的性能上的加强优化。

LEFT SEMI-JION
LEFT SEMI-JION和MySQL方言中的IN... EXISTS...结构做同样的事情：
Java代码
1.hive> SELECT s.ymd,s.symbol,s.price_close
2. > FROM stocks s LEFT SEMI JION dividends d ON s.ymd = d.ymd AND s.symbol = s.symbol
;
3....
4.1962-11-05 IBM 361.5
5.1962-08-07 IBM 373.25
6.1962-05-08 IBM 459.5
7....
LEFT SEMI JOIN 的限制是，JOIN子句中右边的表只能在ON子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

JION优化
1)、将小表放在JION的左边，Hive会将JION左边的小表中的数据缓存起来，然后流式(stream)处理最后的表中的数据，这可以提高HiveQL查询的性能：
Java代码
1.SELECT s.ymd,s.symbol,s.price_close,d.dividend
2.FROM dividend d JION stocks s ON s.ymd = d.ymd AND s.symbol = d.symbol
3.WHERE s.symbol = 'AAPL';
幸运的是，用户不用必须把要流式(stream)处理的表放在JION的右边，Hive提供了一个“hint”机制来告诉查询优化器那个表中的数据需要被流式(stream)处理：
Java代码
1.SELECT /*+ STREAMTABLE(s) */ s.ymd,s.symbol,s.price_close,d.dividend
2.FROM stocks s JION dividend d ON s.ymd = d.ymd AND s.symbol = d.symbol
3.WHERE s.symbol = 'AAPL';
2)、在多个表做JION连接时，如果ON字句中的jion keys都相同时，Hive会把多个table的jion连接编译为一个MapReduce Job，否则一次jion连接会编译成一个job。

3)、Map-Side Jion：Hive提供Map端的jion连接操作，默认情况下是不支持该操作的，用户必须强制告知查询优化器做Map端的Jion连接操作，这可以避免对结果进行大规模的笛卡尔集的操作，也可以减少数据的I/O流量：
Java代码
1.SELECT /*+ MAPJION(s) */ s.ymd,s.symbol,s.price_close,d.dividend
2.FROM stocks s JION dividend d ON s.ymd = d.ymd AND s.symbol = d.symbol
3.WHERE s.symbol = 'AAPL';
当然做Map端的Jion连接操作之前，必须要设置一些参数：
Java代码
1.hive> SET hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;;
2.hive> SET hive.optimize.bucketmapjion=true;
3.hive> SET hive.optimize.bucketmapjion.sortedmerge=true;
ORDER BY和SORT BY
ORDER BY和SQL方言中的order by 语句很像，会对所有的结果集做整体的排序操作，这就意味着所有的数据是通过一个Reducer处理的；对处理非常大的数据及来说，这会执行非常长的处理时间。

Hive提供了一种局部排序的功能——SORT BY，只对每个Reducer处理的局部数据排序，也是一个本地排序，这也就是说每个Reducer处理后的数据是排序的，但对整体而言是无序的。

Java代码
1.SELECT s.ymd,s.symbol,s.price_close FROM stocks s
2.ORDER BY s.ymd ASC,s.symbol DESC;
3.
4.SELECT s.ymd,s.symbol,s.price_close FROM stocks s
5.SORT BY s.ymd ASC,s.symbol DESC;
DISTRIBUTE BY
我们都知道，MapReduce模型是默认是通过hashPartitioner()函数将key/value的keys的hash值来数据分发到对应的Reducers，DISTRIBUTE BY可以让用户来控制如何将key/value对分发到哪个Reducer。

这在大多数的情况下通常很有用。

但是在默写情况下，用户可能需要有自己来决定按某一个字段来将数据分发到Reducers，如下所示：
Java代码
1.hive> SELECT s.ymd,s.symbol,s.price_close FROM stocks s
2. > DISTRIBUTE BY s.symbol
3. > SORT BY s.symbol ASC,s.ymd ASC;
需要注意的是，DISTRIBUTE BY通常要和SORT BY使用，并且DISTRIBUTE BY必须使用在SORT BY之前。

CLUSTER BY
在上个使用DISTRIBUTE BY的示例中，由DISTRIBUTE BY按s.symbol字段将数据分发到相应的Reducers，然后又SORT BY对Reducer最终输出的数据按s.symbol和s.ymd做升序排序。

CLUSTER BY关键字可以起到DISTRIBUTE BY和SORT BY相同的功能：
Java代码
1.hive> SELECT s.ymd,s.symbol,s.price_close FROM stocks s
2. > CLUSTER BY s.symbol；
使用DISTRIBUTE BY和CLUSTER BY都可以起到对Reducers的输出做并行排倒序。

类型转化(Casting)
之前我们已经学习过数据类型转化的示例，如将一个数值转化为FLOAT类型，转换语法为cast(value AS TYPE)，比如：
Java代码
1.SELECT name,salary FROM employees
2.WHERE cast(salary AS FLOAT) < 100000.0
如果salary不能转换成FLOAT类型的值的话，Hive会返回NULL。

转换二进制类型的值
从Hive 0.80开始，Hive开始支持二进制类型的值的转换。

将BINARY的值b转换成STRING：Java代码
1.SELECT (
2.0 * cast(cast(b AS STRING) AS DOUBLE)) FROM src;
同样也可以讲STRING类型转换成BINARY类型。

采样查询
有些时候，用户并不想对一个大数据集的数据做全部的分析处理，对于这种情况，Hive提供了对bucket table的采样查询功能：
Java代码
1.hive> SELECT * FROM numbers TABLESAMPLE(BUCKET 1 OUT OF 2 ON number)s;
Hive中的桶下标是从1开始的。

Block数据块数据采样(Block Sampling)
Hive还提供了另外一种采样的语法——Block数据块数据采样，用户可以一个数据块的数据行(rows)采样：
Java代码
1.hive> SELECT * FROM numbersflat TABLESAMPLE(0.1 PERCENT) s;
UNION ALL
UNION ALL可以将两个或多个子查询的结果集合并到一块儿，这就要就要合并的结果集必须要有相同的列数，并且相对应的列要有相匹配的类型：
Java代码
1.SELECT log.ymd,log.level,log.message
2. FROM(
3. SELECT l1.ymd,l1.level,l1.message,'log1' AS source FROM log1 l1
4. UNION ALL
5. SELECT l2.ymd,l3.level,l2.message,'log2' AS source FROM log2 l2
6.) log
7.SORT BY log.ymd ASC
Hive提供有限的索引功能，这不像传统的关系型数据库那样有“键(key)”的概念，用户可以在某些列上创建索引来加速某些操作，给一个表创建的索引数据被保存在另外的表中。

Hive的索引功能现在还相对较晚，提供的选项还较少。

但是，索引被设计为可使用内置的可插拔的java代码来定制，用户可以扩展这个功能来满足自己的需求。

当然不是说有的查询都会受惠于Hive索引。

用户可以使用EXPLAIN语法来分析HiveQL语句是否可以使用索引来提升用户查询的性能。

像RDBMS中的索引一样，需要评估索引创建的是否合理，毕竟，索引需要更多的磁盘空间，并且创建维护索引也会有一定的代价。

用户必须要权衡从索引得到的好处和代价。

创建索引
现在让我们来为分区表employees创建一个索引。

首先，然我们再回顾一下employees东侧表定义：
Java代码
1.CREATE TABLE employees （
2. name STRING,
3. salary FLOAT,
4. subordinates ARRAY<STRING>,
5. deductions MAP<STRING,FLOAT>,
6. address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
7.) PARTITIONED BY (country STRING,state STRING);
接着让我们看看如何为该表的contry分区创建索引：
Java代码
1.CREATE INDEX employees_index
2.ON TABLE employees (country)
3.AS 'pactIndexHandler'
4.WITH DEFERRED REBUILD
5.IDXPROPERTIES ('creator' = 'me','created_at' = 'some_time')
6.IN TABLE employees_index_table
7.PARTITIONED BY (country,name)
MENT 'Employees indexed by country and name.';
在本示例中，在employees上创建了名为employees_index的索引，索引数据存放在employees_index_table索引表中，WITH DEFERRED REBUILD表明创建一个空索引，可以在之后使用如下语句创建索引数据：
Java代码
1.ALTER INDEX employees_index ON TABLE employees
2.PARTITION(country = 'US')
3.REBUILD;
PARTITIONED BY表明只对某个分区创建索引，若没有该选项则表示对所有分区都创建索引，另外要注意的是index的分区索引默认是和表的分区一致的，也不能对视图VIEW创建索引。

AS 'pactIndexHandler'表示使用Apache的pactIndexHandler作为创建索引的handler，当然也可以使用第三方的实现类或其他的实现类。

当然也可以对其他字段创建索引。

Bitmap位图索引
Hive v0.80添加了一个内置的bitmap位图索引。

Bitmap位图索引通常适用于只有少数不同值的列创建索引。

现在我们修改上一个索引示例为位图索引：
Java代码
1.CREATE INDEX employees_index
2.ON TABLE employees (country)
3.AS 'BITMAP'
4.WITH DEFERRED REBUILD
5.IDXPROPERTIES ('creator' = 'me','created_at' = 'some_time')
6.IN TABLE employees_index_table
7.PARTITIONED BY (country,name)
MENT 'Employees indexed by country and name.';
重建索引
如果用户在创建索引时指定WITH DEFERRED REBUILD关键字，那么开始时是一个空索引。

我们在任何时候使用ALTER INDEX语句来创建或重建索引：
Java代码
1.ALTER INDEX employees_index ON TABLE employees。