的分析人员中很多工程师比较擅长而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 6. deductions MAP 7. address STRUCT 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 FILEFORMAT 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 5. deductions MAP 6. address STRUT 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/state=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 se.cnty = '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 se.cnty = '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 se.cnty = 'US' AND se.st = 'OR'; 5.INSERT INTO TABLE employess 6. PARTTITION (country = 'US',state = 'CA') 7. SELECT * WHERE se.cnty = 'US' AND se.st = 'CA'; 8.INSERT INTO TABLE employess 9. PARTTITION (country = 'US',state = 'IL') 10. SELECT * WHERE se.cnty = 'US' AND se.st = 'IL'; 11.... 这种INSERT语法也可以一次插入多个表。 动态分区添加数据 就上例来说，好友一个问题，那就是如果有特别多的分区要创建的话，用户不得不要写许多HiveQL语句！幸运的是，Hive提供一个叫动态分区的功能，可以基于用户的查询参数推断要创建的分区。通过与动态分区相比较，之前我们讨论的分区插入数据被称为静态分区数据导入。 将上面的静态分区作如下修改，就变成动态分区数据导入的示例： Java代码 1.INSERT OVERWRITE TABLE employees 2.PARTITION (country,state) 3.SELECT ...,se.cnty,se.st 4.FROM staged_employees se 对于本例来说，Hive会通过SELECT语句中最后两列se.cnty和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 ...,se.cnty,se.st 4.FROM staged_employees se 5.WHERE se.cnty = '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 ...,se.cnty,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 se.cnty = 'US' AND se.st ='OR' 4.INSERT OVERWRITE DIRECTORY '/tmp/CA_employees' 5. SELECT * FROM se.cnty = 'US' AND se.st ='CA' 6.INSERT OVERWRITE DIRECTORY '/tmp/IL_employees' 7. SELECT * FROM se.cnty = '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 5. deductions MAP 6. address STRUT 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.name,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.69 197.88 194.0 194.12 194.12 3.AAPL 192.63 196.0 190.85 195.46 195.46 4.AAPL 196.73 198.37 191.57 192.05 192.05 5.AAPL 195.17 200.2 194.42 199.23 199.23 6.AAPL 195.91 196.32 193.38 195.86 195.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.0 0.2 80000 5.MARY SMITH 80000.0 0.2 000 6.TODD JONES 70000.0 0.15 59500 7.BILL KING 60000.0 0.15 51000 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.0 0.2 80000 6.MARY SMITH 80000.0 0.2 000 给列奇别名 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.0 0.2 80000 7.MARY SMITH 80000.0 0.2 000 子查询 给列起别名特别适合与子查询中的列，让我们将上个查询示例修改为子查询的使用用例： 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.name,e.salary_minus_fed_taxes 8. > WHERE e.salary_minus_fed_taxes > 70000; 9. JOHN DOE 100000.0 0.2 80000 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.0 0.2 80000.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.0 0.2 4.Mary Smith 80000.0 0.2 5.Boss Man 200000.0 0.3 6.Fred Finance 150000.0 0.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.0 0.3 5.Fred Finance 150000.0 0.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.1984 25.578625440597534 5.1985 20.193676221040867 6.1986 32.46102808021274 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.1987 53.868399108163 6.1991 52.49553383386182 7.1992 54.80338610251119 8.2000 71.742876261757 9.... 如果不使用HAVING，那么就要使用子查询： Java代码 1.hive> SELECT s2.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 5. deductions MAP 6. address STRUCT 7.) PARTITIONED BY (country STRING,state STRING); 接着让我们看看如何为该表的contry分区创建索引： Java代码 1.CREATE INDEX employees_index 2.ON TABLE employees (country) 3.AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' 4.WITH DEFERRED REBUILD 5.IDXPROPERTIES ('creator' = 'me','created_at' = 'some_time') 6.IN TABLE employees_index_table 7.PARTITIONED BY (country,name) 8.COMMENT '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 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'表示使用Apache的org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler作为创建索引的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) 8.COMMENT 'Employees indexed by country and name.'; 重建索引 如果用户在创建索引时指定WITH DEFERRED REBUILD关键字，那么开始时是一个空索引。我们在任何时候使用ALTER INDEX语句来创建或重建索引： Java代码 1.ALTER INDEX employees_index ON TABLE employees 2.PARTITION(country = 'US') 3.REBUILD; Hive没有提供一个内置的在数据变更时自动触发创建索引的机制，故用户需要自己通过ALTER INDEX语句来创建索引；另外，重建索引操作是一个原子操作，因此当rebuild失败时，已构建的索引也无法使用。 查看索引 用户可以查看某个表上的所有的索引： Java代码 1.SHOW FORMATTED INDEX ON employees; 2.SHOW FORMATTED INDEXES ON employees; 删除索引 在删除一个索引的时候，也会同时删除索引数据所在的表，如： Java代码 1.DROP INDEX IF EXISTS employees_index ON TABLE employees; Hive使用的是Hadoop的文件系统和文件格式，比如TEXTFILE，SEQUENCEFILE等。 在Hive中对中间数据或最终数据数据做压缩，是提高数据吞吐量和性能的一种手段。对数据做压缩，可以大量减少磁盘的存储空间，比如基于文本的数据文件，可以将文件压缩40%或更多，同时压缩后的文件在磁盘间传输和I/O也会大大减少；当然压缩和解压缩也会带来额外的CPU开销，但是却可以节省更多的I/O和使用更少的内存开销。 Hadoop jobs作业，往往是I/O密集型的，而非CPU集型的。数据压缩对I/O密集型的作业带来大大的性能提升，但是如果用户的jobs作业时CPU密集型的，那么在使用压缩就会降低性能，这就要用户对作业的类型做判断，权衡是否要对数据做压缩。 选择合适的编解码器 对数据做压缩，可以最大程度的减少文件所需的磁盘空间和网络I/O的开销，但对数据做压缩和解压缩总会增加CPU的开销，故最好对那些I/O密集型的作业使用数据压缩——这样的作业会有富余的CPU资源，或者对那些磁盘空间不富裕的系统。 Hadoop默认支持Gzip和BZip2的解压缩方式，包括原生的linux解压缩库。Snappy压缩在最近的Hive版本中才被添加的压缩算法，如果您使用的是Hive不支持Snappy的话，用户就要手动添加。过去经常使用的是LZO压缩算法。 那么，为什么要使用不同的压缩算法呢？它们都有什么区别呢？BZip2有最高的压缩比但也会带来更高的CPU开销，Gzip较BZip2次之；如果基于磁盘利用率和I/O考虑，这两个压缩算法都是比较有吸引力的算法。 LZO和Snappy算法有更快的解压缩速度，如果在关注数据解压多于磁盘利用率和I/O开销的场景中，它们都是不错的选择。LZO和Snappy在压缩数据上大致相当，但Snappy算法在解压速度上要较LZO更快。 在选择解压缩算法时，另外一个比较重要的考虑就是压缩格式是否是支持可分割的。Hadoop的会将大文件分割成HDFS block(默认MB)大小的splits分片，每个分片对应一个Mapper程序。在这几个压缩算法中，只有BZip2和LZO提供block级的压缩，而Gzip和Snappy则不支持。 Snappy压缩算法时首选。 打开Hive(即由mappe产生的)中间数据文件的压缩功能 HiveQL语句最终会被编译成Hadoop的Mapreduce job，开启Hive的中间数据压缩功能，就是在MapReduce的shuffle阶段对mapper产生的中间结果数据压缩。在这个阶段，优先选择一个低CPU开销的算法。 可以通过参数hive.exec.compress.intermediate来开启和禁用该功能，默认情况下该值值为false，将之设置为true为激活中间数据压缩功能： Java代码 1. 2. 3. 4. 5. 具体的压缩算法参数配置，这是一个hadoop的配置参数，可以在Hadoop的$HADOOP_HOME/conf/marred-site.xml或$HADOOP_HOME/conf/hive-site.xml配置文件中。SnappyCodec比较适合在这种场景中编解码器，该算法会带来很好的压缩性能和较低的CPU开销： Java代码 1. 2. 3. 4. 5. 最Hive的数据文件压缩 用户可以对最终生成的Hive表的数据通常也需要压缩。参数hive.exec.compress.output控制这一功能的激活与禁用。用户可以通过节本来打开这个功能，同样也可以在XML配置文件中将该功能配置成全局变量： Java代码 1. 2. 3. 4. 5. 将hive.exec.compress.output参数设置成true后，然后选择一个合适的编解码器，如选择SnappyCodec： Java代码 1. 2. 3. 4. Sequence Files 压缩数据文件可以节省磁盘空间，但是在Hadoop中有些原生压缩文件的缺点之一就是不支持分割。支持分割的文件可以并行的有多个mapper程序处理大数据文件，大多数文件不支持可分割是因为这些文件只能从头开始读。 Sequence File是可分割的文件格式，支持Hadoop的block级压缩。这样，我们就可以将Hive表存储定义成SEQUENCEFILE： Java代码 1.CREATE TABLE a_sequence_file_table SORTED AS SEQUENCEFILE; Sequence Files有三个不同的压缩选项：NOE,RECORD和BLOCK。RECORD是默认选项，通常BLOCK会带来较RECORD更好的压缩性能。同很多其他压缩选项一样，这个压缩类型参数不是Hive特有的，需要在Hadoop中或在每一个Hive查询脚本中2设置。 Java代码 1. 2. 3. 4. 5. 在当前的基于Hadoop系统的数据仓库中，数据存储格式是影响数据仓库性能的一个重要因素。于是提出了集行存储和列存储的优点于一身的RCFile文件存储格式，据说当前在公司内部，其数据仓库的文件存储格式都是RCFile，另外盛大内部的数据仓库使用的也是RCFile文件存储格式。 我们现在正在进入一个大数据时代，何为“大数据”？它不仅仅就是指海量的数据，也包括如何高效的存储、管理和使用这写数据。在Hadoop运行环境中，数据的存储格式要满足以下几个特点： 1)、数据加载数据要快； 2)、数据查询处理要快； 3)、高效的数据存储空间利用率； 4)、适应高强度的动态负载模式。 在之前的Hadoop ecosystem中，我们已经知道了基于行存储结构的TextFile和SequenceFile的文件格式，也学习过Hbase的面向列存储的文件格式，对于Hive数据仓库来说，他们都有各自的有点个缺点，今天我们来学习一个新的文件存储格式——RCFile。 首先，我们先来探讨一下基于行的存储和面向列的存储的优缺点： 基于行存储的数据结构：优点是具备快速数据加载和动态负载的高适应能力，因为行存储保证了相同记录的所有域都在同一个集群节点；但是它不太满足快速的查询响应时间的要求，特别是在当查询仅仅针对所有列中的少数几列时，它就不能直接定位到所需列而跳过不需要的列，由于混合着不同数据值的列，行存储不易获得一个极高的压缩比，行存储不易获得一个较高的压缩比。 Hadoop block中的基于行存储的示例图 面向列存储的数据结构：我们先看一下Hadoop block中面向列存储的示例： Hadoop block中的面向列存储的示例图 在面向列的文件存储结构中，列A和列B存储在同一列组，而列C和列D分别存储在单独的列组。这种结构使得在查询时能够直接读取需要的列而避免不必要列的读取，并且对于相似数据也可以有一个更好的压缩比。但是他的缺点也想但明显，那就是由于元组重构的较高开销，它并不能提供基于Hadoop系统的快速查询处理，也不能保证不能保证同一记录的所有列都存储在同一集群节点之上，也适应高度动态的数据负载模式。 RCFile： 结合列存储和行存储的优缺点，于是提出了基于行列混合存储的RCFile，它是基于SEQUENCEFILE实现的列存储格式，它即满足快速数据加载和动态负载高适应的需求外，也解决了SEQUENCEFILE的一些瓶颈。该存储结构遵循的是“先水平划分，再垂直划分”的设计理念。先将数据按行水平划分为行组，这样一行的数据就可以保证存储在同一个集群节点；然后在对行进行垂直划分。 我们再来看一下HDFS块内RCFile方式存储的示例图： Hadoop block中的RCFile存储的示例图 RCFile是在Hadoop HDFS之上的存储结构，该结构强调： 1）、RCFile存储的表是水平划分的，分为多个行组，每个行组再被垂直划分，以便每列单独存储； 2）、RCFile在每个行组中利用一个列维度的数据压缩，并提供一种Lazy解压（decompression）技术来在查询执行时避免不必要的列解压； 3）、RCFile支持弹性的行组大小，行组大小需要权衡数据压缩性能和查询性能两方面。 每个HDFS block块中，RCFile以行组为基本单位来组织记录。也就是说，存储在一个HDFS块中的所有记录被划分为多个行组；对于一张表所有行组大小都相同，一个HDFS块会有一个或多个行组。 一个行组包括三个部分： 1）、第一部分是行组头部的同步标识，主要用于分隔HDFS块中的两个连续行组； 2）、第二部分是行组的元数据头部，用于存储行组单元的信息，包括行组中的记录数、每个列的字节数、列中每个域的字节数； 3）、第三部分是表格数据段，即实际的列存储数据。在该部分中，同一列的所有域顺序存储。从上图可以看出，首先存储了列A的所有域，然后存储列B的所有域等。 RCFile的每个行组中，元数据头部和表格数据段（每个列被压缩）分别进行压缩，RCFile使用重量级的Gzip压缩算法，是为了获得较好的压缩比。另外在由于Lazy压缩策略，当处理一个行组时，RCFile只需要解压使用到的列，因此相对较高的Gzip解压开销可以减少。 RCFile具备相当于行存储的数据加载速度和负载适应能力，在读数据时可以在扫描表格时避免不必要的列读取，它比其他结构拥有更好的性能，使用列维度的压缩能够有效提升存储空间利用率。 为了满足用户的个性化需求，Hive被设计成了一个很开放的系统，很多内容都可以定制，主要包括： 1)、文件格式； 2)、内存中的数据格式，如Hadoop的Writable/Text； 3)、用户提供的MapReduce脚本，不管使用何种语言都可以通过标准的stdin/stdout传输数据； 4)、用户自定义函数。 本节我们主要学习用户自定义函数。虽然Hive已经提供了很多内存的函数，但是还是不能，满足用户的需求，因此有提供了自定义函数供用户自己开发函数来满足自己的需求。之定义函数主要分三类： 1)、UDF(User-Defined-Function)用户自定义函数，输入一个数据然后产生一个数据； 2)、UDAF(User-Defined Aggregation Function)用户自定义聚合函数，多个输入数据然后产生一个输出参数； 3)、UDTF(User-Defined Table-generating Function)用户自定义表生成函，数，输入一行数据生成N行数据。 UDF 现在我们开始学习如何来开发自己的UDF函数。假设我们现在有一个被保存在littlebigdata.txt文件中的数据集： Java代码 1.edward capriolo,edward@media6degrees.com,2-12-1981,209.191.139.200,M,10 2.bob,bob@test.net,10-10-2004,10.10.10.1,M,50 3.sara connor,sara@sky.net,4-5-1974,..5.1,F,2 4.... 接下来我们顶一个Hive加载这个数据集： Java代码 1.hive> CREATE TABLE IF NOT EXISTS littlebigdata( 2. > name STRING COMMENT '姓名', 3. > email STRING COMMENT '邮箱', 4. > birthday STRING COMMENT '生日', 5. > ip STRING COMMENT 'ip', 6. > gender STRING COMMENT '性别', 7. > anum INT) 8. > ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; 9. 10.hive> LOAD DATA LOCAL INPATH '${env:HOME}/littlebigdata.txt' 11. > INTO TABLE littlebigdata; 我们接下来会写一个自定义的UDF根据数据集中的生日计算对应星座，首先新类要继承org.apache.hadoop.hive.ql.exec.UDF类，然而UDF类并不是一个抽象类，其中并没有用户自定义函数必须的evaluate()方法，子类必须实现一个或多个evaluate方法给Hive框架调用，这个evaluate方法正是所有实现类的逻辑部分所在，只有用户自己来写方法了： Java代码 1.package org.apache.hadoop.hive.contrib.udf.example; 2. 3.import java.text.SimpleDateFormat; 4.import java.util.Date; 5.import org.apache.hadoop.hive.ql.exec.UDF; 6.import org.apache.hadoop.hive.ql.exec.Description; 7. 8.@Description(name = "Zodiac", 9. value = "FUNC_(date) - from the input data string " + 10. "or separate month and day arguments,return the sign of the Zodiac.", 11. extended = "Example:\\n >SELECT _FUNC_(date_string) FROM src;\\n" + 12. " > SELECT _FUNC_(month,day) FROM src;") 13.public class ZodiacSignUDF extends UDF{ 14. private SimpleDateFormat df = null; 15. 16. /** 17. * 在默认构造函数中初始化df 18. */ 19. public ZodiacSignUDF(){ 20. df = new SimpleDateFormat("MM-dd-yyyy"); 21. } 22. 23. /** 24. * 由出生日期获取星座 25. * @param birthday -Date 26. * @return 27. */ 28. public String evaluate(Date birthday){ 29. return this.evaluate(birthday.getMonth() + 1,birthday.getDay()); 30. } 31. 32. /** 33. * 由出生日期获取星座 34. * @param birthday -String 35. * @return 36. */ 37. public String evaluate(Date birthday){ 38. Date date = null; 39. try{ 40. date = df.parse(birthday); 41. }catch(Excetion e){ 42. //如果日期转换失败，就表明星座是未知的 43. return null; 44. } 45. return this.evaluate(date.getMonth() + 1,date.getDay()); 46. } 47. 48. /** 49. * 由月份和出生日获取星座 50. * @param month -int 51. * @param day -int 52. * @return 53. */ 54. public String evaluate(Integer month,Integer day){ 55. if(month == 1){ 56. if(day < 20){ 57. return "Capricorn"; 58. }else{ 59. return "Aquarius"; 60. } 61. }else if(month == 2){ 62. if(day < 19){ 63. return "Aquarius"; . }else{ 65. return "Pisces"; 66. } 67. } 68. /** ... other months here */ 69. return null; 70. } 71.} 在HiveQL的执行中，没处理一行都会调用一次ZodiacSignUDF类的实例，然后将每一行的出生一起传给evaluate()方法并得到对应的星座返回给Hive。 那么接下来的问题是如何使用这个自定义函数呢？ 首先需要用户便以这个类并将之打成一个jar包，然后将jar文件添加到Hive的classpath并使用CREATE FUNCTION语句将之定义为一个Hive能使用的方法： Java代码 1.hive> ADD JAR /full/path/to/zodiac.jar; 2.hive> CREATE TEMPORARY FUNCTION zodiac 3. AS 'org.apache.hadoop.hive.contrib.udf.exampl.ZodiacSignUDF'; 当然，上面的函数添加方法只能在Hive session会话期间使用，session结束后如还想使用该函数，那就必须再将上面的两部操作做一遍。如果这个函数是经常使用的，那么这么做就太繁琐了，这种情况下可以把上面的两操作语句添加到$HOME/.hiverc文件中作为全局函数，就想使用hive的内置行是一样： Java代码 1.hive> SELECT name,birthday,zodiac(birthday) FROM littlebigdata; 2.edward capriolo 2-12-1981 Aquarius 3.bob 10-10-2004 Libra 4.sara 4-5-1974 Aries 接着我们来看怎么删除一个·自定义函数： Java代码 1.hive> DROP TEMPORARY FUNCTION IF EXISTS zodiac; HiveQL是一种声明式语言，最终会被编译为MapReduce job提交到Hadoop执行。大多情况下，用户并不需要知道Hive是如何运作——只关注手头的业务处理问题就行了。虽然Hive引擎会在在HiveQL语句编译过程中最许多的复杂的工作——查询解析、规划、优化和执行等复杂过程，但是用户大部分时间都可以无视这些过程。 然而，随着Hive的使用，用户会变得越来越有经验，这时候再深入了解和学习Hive理论后的底层实现细节，用户写的HiveQL语句会更高效，特别是在改写HiveQL时做过更多的性能调优之后。 本节我们会学习一些性能调优的手段。 优化Jion连接 Streamtable 在做在对多个表Jion连接操作时，将小表放在Jion的左边，大表放在Jion的右边，在执行这样的Jion连接时小表中的数据会被缓存到内存当中,这样可以有效减少发生内存溢出错误的几率： Java代码 1.hive> SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1) WHERE c.val >= b.val AND a.val = 'test' AND b.val = 20130520 对于上面的HiveQL示例，会先执行ON条件筛选，再连接，然会才会执行WHERE过滤条件，ON后的筛选条件主要是针对从表，对主表不起作用，因为是外关联，主表数据都会输出，对于主表的筛选条件应该放在where后面，如果觉得主表不需要关联的数据太多，可以使用子查询，先过滤主表中无用数据： Java代码 1.Hive> SELECT a.val,b.val FROM a JION ( 2. SELECT c.val FROM c WHERE c.key = 'test' 3. > ) b ON(a.key = b.key1) 4. > WHERE b.val = 20130520; 由于join操作是在where操作之前执行，所以当你在执行join时，where条件并不能起到减少join数据的作用，如： Java代码 1.hive> SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key) 2. >WHERE a.ds='2009-07-07' AND b.ds='2009-07-07' 可以改为： Java代码 1.hive> SELECT a.val, b.val FROM a LEFT OUTER JOIN b 2. > ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07') 在JION操作的每一个MapReduce程序中，Hive都会把出现在JION语句中相对靠后的表的数据stream化，相对靠前的变的数据缓存在内存中，这种情况下最好将大表放在JION的右边 (表中数据a < b < c)： Java代码 1.hive> SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1); 当然您也可以不将大表放在JION的右边，这是就需要指定使用/*+ STREAMTABLE(..) */： Java代码 1.hive> SELECT /*+ STREAMTABLE(b) */ a.val, b.val, c.val FROM a JOIN b 2. > ON (a.key = b.key1) JOIN c ON (c.key = b.key1) 另外如果所有参与join的表中其参与join的key都相同，则会将所有的join合并到一个mapred程序中。 Mapjion Mapjion是是另外一种特殊JION连接操作，它强调在Map端做JION操作： Java代码 1.hive> SELECT /*+ MAPJION(a,b) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1) WHERE c.val >= b.val AND a.val = 'test' AND b.val = 20130520 默认情况下Hive是不会在MapJION操作的，用户需要使用/* + MAPJION(tablelist) */关键字来告知Hive编译器来做Map端的连接。 MAPJION会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map是进行了join操作，省去了reduce运行的效率也会高很多。 Mapjion是一种避免避免数据倾斜的手段，比如对a表和b表做jion连接操作，其中a中只有很少的上千行或百十行数据，而b中却又上千万或上亿行的数据，而且B表中数据倾斜特别严重，这时候制定对a表示用Mapjion，这样就不会由于数据倾斜导致某个reduce上落数据太多而失败。 不同的JION连接操作是不支持不等于操作的(如a.x < b.y 或者 a.x like b.y等)，Hive语法解析会直接抛出错误。Mapjoin还有一个很大的好处是能够进行不等连接的join操作，如果将不等条件写在WHERE中，那么MapReduce过程中会进行笛卡尔积，运行效率特低，若使Mapjoin操作，在Mapper处理过程中就可以完成了不等值的join操作，效率会高很多： Java代码 1.hive> select /*+ MAPJION(a) */ a.a ,a.b from a join b where a.a>b.a; Mapjoin 的是无法执行全连接(FULL JION)和右外连接(RIGHT OUTER JOIN)。 并行执行(Parallel Execution) Hive引擎会将一个HiveQL查询语句分解成几个执行阶段(stages)(可以通过EXPLAIN HiveQL看出)，默认情况下，Hive会一次执行一个阶段(stage)，有时候一个包含多个执行阶段(stages)的HiveQL的stages之间并没有前后执行的依赖顺序，这种情况下将这些stages并行执行是一个很好的选择，可以带来更好的性能。 将hive.exec.parallel参数设置为true就会激活并行执行的功能，如果一个HiveQL作业可以并行的执行其多个stages，这就会提升集群的利用率： Java代码 1. 2. 3. 4. 5. 本地模式(Local Mode) 在处理大的数据集时，Hadoop作业可以从hadoop的全扩展可以带来很大的优势，然而有时候要处理的数据集会很小，在这种场景中，对小数据集的查询激活本机模式是一个不错的选择，这可以相当程度上的避免一些不必要的性能开销。可以在$HIVE_HOME/conf/hive.site.xml中将hive.exec.mode.local.auto参数设置为true由Hive对HiveQL作业自动设置执行模式： Java代码 1. 2. 3. 4. 5. Let hive determine whether to run in local mode automatically 6. 7. JVM重用(JVM Reuse) JVM重用是Hadoop的一个性能调整的参数，它也会对Hive产生非常大的影响，特别是在很那避免处理大量小文件的场景中，每一个task执行方式景都很短，要知道默认新情况下每处理一个task都要启动一个JVM进程，JVM进程是一个重量级的进程，这就需要大量的资源的开销，这种情况下使用JVM重用可以大量减少这些不必要的JVM的初始化和销毁的开销，大大缩短总的执行时间。 这个参数可以再$HADOOP_HOME/conf/mapred-site.xml中设置： Java代码 1. 2. 3. 4. 5. How many tasks to run per jvm.if set to -1,there is no limit. 6. 7. JVM重用必须是针对一个MapReduce作业的同一种task任务的，同一个作业的不同类的task是不能重用JVM的。mapred.job.reuse.jvm.num.tasks为-1表明不JVM重用的次数。 推测式执行(Speculative Execution) 推测式执行时Hadoop的一个重要的功能，其目的是大大加快hadoop作业的整体执行时间。推测式执行的细节这里不再赘述。这个功能需要设置mapred-site.xml中的两个参数来激活这一功能： Java代码 1. 2. 3. 4. 5. may be executed in parallel. 6. 7. 8. 9. 10. 11. 12. may be executed in parallel. 13. 14. Hive提供了一个参数来控制reduce端的并行执行： Java代码 1. 2. 3. 4. 5. Whether speculative execution for reducers should be turned on. 6. 7. 索引(Indexes) 索引可以加快GROUP BY查询语句的执行速度。 Hive从0.80开始，提供了一个Bitmap位图索引，它主要适用于在一个给定的列中只有几个值的场景。详情见HiveQL索引。 严格模式(Strict Mode) Hive中的严格模式可以防止用户发出（可以有问题）的查询无意中造成不良的影响。 将hive.mapred.mode设置成strict可以禁止三种类型的查询： 1）、在一个分区表上，如果没有在WHERE条件中指明具体的分区，那么这是不允许的，换句话说，不允许在分区表上全表扫描。这种的原因是分区表通常会持非常大的数据集并且可能数据增长迅速，对这样的一个大表做全表扫描会消耗大量资源，结果示例如下： Java代码 1.hive> SELECT DISTINCT(planner_id) FROM fracture_ins WHERE planner_id = 5; 2.FAILED: Error in semantic analysis: No Partition Predicate Found For 3.Alias "francture_ins" Table "francture_ins" 必要再WHERE过滤条件中具体指明分区才可以执行成功的查询： Java代码 1.hive> SELECT DISTINCT(planner_id) FROM fracture_ins 2. > WHERE planner_id = 5 AND hit_date=20121212; 3.... 结果信息 ... 2）、第二种是禁止执行有ORDER BY的排序要求但没有LIMIT语句的HiveQL查询。因为ORDER BY全局查询会导致有一个单一的reducer对所有的查询结果排序，如果对大数据集做排序，这将导致不可预期的执行时间： Java代码 1.hive> SELECT * FROM fracture_ins WHERE hit_date>2012 ORDER BY planner_id; 2.FAILED: Error in semantic analysis: line 1:56 In strict mode, 3.limit must be specified if ORDER BY is present planner_id 应该给上面的HiveQL语句加上LIMIT： Java代码 1.hive> SELECT * FROM fracture_ins WHERE hit_date>2012 ORDER BY planner_id LITMIT 1000; 3）、第三种是禁止产生笛卡尔集。在JION接连查询中没有ON连接key而通过WHERE条件语句会产生笛卡尔集： Java代码 1.hive> SELECT * FROM fracture_act JION fracture_ads 2. > WHERE fracture_act.planner_id = fracture_ads.planner_id; 3.FAILED: Error semantic analysis: In strict mode,cartesian product is 4.not allowed.If you really want to perform the operation, 5.+set hive.mapred.mode=nonstrict+ 将之改为JION...ON语句： Java代码 1.hive> SELECT * FROM fracture_act JION fracture_ads 2. > ON(fracture_act.planner_id = fracture_ads.planner_id); 3.... 查询结果 ... 目录： 初始Hive Hive安装与配置 Hive 内建操作符与函数开发 Hive JDBC hive参数 Hive 高级编程 Hive QL Hive Shell 基本操作 hive 优化 Hive体系结构 Hive的原理 配套视频课程 第一部分：关系运算 Hive支持的关系运算符 •常见的关系运算符 •等值比较: = •不等值比较: <> •小于比较: < •小于等于比较: <= •大于比较: > •大于等于比较: >= •空值判断: IS NULL •非空判断: IS NOT NULL •LIKE比较: LIKE •JAVA的LIKE操作: RLIKE •REGEXP操作: REGEXP •等值比较: = 语法：A=B 操作类型：所有基本类型 描述: 如果表达式A与表达式B相等，则为TRUE；否则为FALSE 举例： hive> select 1 from dual where 1=1; •不等值比较: <> 语法: A <> B 操作类型: 所有基本类型 描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B不相等，则为TRUE；否则为FALSE 举例： hive> select 1 from dual where 1 <> 2; •小于比较: < 语法: A < B 操作类型: 所有基本类型 描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于表达式B，则为TRUE；否则为FALSE 举例： hive> select 1 from dual where 1 < 2; •小于等于比较: <= 语法: A <= B 操作类型: 所有基本类型 描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于或者等于表达式B，则为TRUE；否则为FALSE 举例： hive> select 1 from dual where 1 <= 1; •大于等于比较: >= 语法: A >= B 操作类型: 所有基本类型 描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A大于或者等于表达式B，则为TRUE；则为FALSE 举例： hive> select 1 from dual where 1 >= 1; •空值判断: IS NULL 语法: A IS NULL 操作类型: 所有类型 描述: 如果表达式A的值为NULL，则为TRUE；否则为FALSE 举例： hive> select 1 from dual where null is null; •非空判断: IS NOT NULL 语法: A IS NOT NULL 操作类型: 所有类型 描述: 如果表达式A的值为NULL，则为FALSE；否则为TRUE 举例： hive> select 1 from dual where 1 is not null; •LIKE比较: LIKE 语法: A LIKE B 操作类型: strings 描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B 的正则语法，则为TRUE；否则为FALSE。B中字符”_”表示任意单个字符，而字符”%”表示任意数量的字符。 举例： hive> select 1 from dual where ‘key' like 'foot%'; 1 hive> select 1 from dual where ‘key ' like 'foot____'; 1 注意：否定比较时候用 NOT A LIKE B hive> select 1 from dual where NOT ‘key ' like 'fff%'; •JAVA的LIKE操作: RLIKE 语法: A RLIKE B 操作类型: strings 描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；否则为FALSE。 举例： hive> select 1 from dual where 'footbar’ rlike '^f.*r$’; 1 注意：判断一个字符串是否全为数字： hive>select 1 from dual where '123456' rlike '^\\\\d+$'; 1 hive> select 1 from dual where '123456aa' rlike '^\\\\d+$'; 1 •REGEXP操作: REGEXP 语法: A REGEXP B 操作类型: strings 描述: 功能与RLIKE相同 举例： hive> select 1 from dual where ‘key' REGEXP '^f.*r$'; 1 第二部分：逻辑运算与数算 Hive数算 •加法操作: + •减法操作: - •乘法操作: * •除法操作: / •取余操作: % •位与操作: & •位或操作: | •位异或操作: ^ •位取反操作: ~ •加法操作: + 语法: A + B 操作类型：所有数值类型 说明：返回A与B相加的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。比如，int + int 一般结果为int类型，而int + double 一般结果为double类型 举例： hive> select 1 + 9 from dual; 10 •减法操作: - 语法: A – B 操作类型：所有数值类型 说明：返回A与B相减的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。比如，int – int 一般结果为int类型，而int – double 一般结果为double类型 举例： hive> select 10 5 from dual; 5 • 乘法操作 : * 语法: A * B 操作类型：所有数值类型 说明：返回A与B相乘的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。注意，如果A乘以B的结果超过默认结果类型的数值范围，则需要通过cast将结果转换成范围更大的数值类型 举例： hive> select 40 * 5 from dual; 200 • 除法操作 : / 语法: A / B 操作类型：所有数值类型 说明：返回A除以B的结果。结果的数值类型为double 举例： hive> select 40 / 5 from dual; 8.0 注意： hive 中最高精度的数据类型是 double, 只精确到小数点后 16 位，在做除法运算的时候要 特别注意 hive>select ceil(28.0/6.999999999999999999999) from dual limit 1; 结果为4 hive>select ceil(28.0/6.99999999999999) from dual limit 1; 结果为5 • 取余操作 : % 语法: A % B 操作类型：所有数值类型 说明：返回A除以B的余数。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。 举例： hive> select 41 % 5 from dual; 1 hive> select 8.4 % 4 from dual; 0.40000000000000036 注意：精度在 hive 中是个很大的问题，类似这样的操作最好通过 round 指定精度 hive> select round(8.4 % 4 , 2) from dual; 0.4 • 位与操作 : & 语法: A & B 操作类型：所有数值类型 说明：返回A和B按位进行与操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。 举例： hive> select 4 & 8 from dual; 0 hive> select 6 & 4 from dual; 4 • 位或操作 : | 语法: A | B 操作类型：所有数值类型 说明：返回A和B按位进行或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。 举例： hive> select 4 | 8 from dual; 12 hive> select 6 | 8 from dual; 14 • 位异或操作 : ^ 语法: A ^ B 操作类型：所有数值类型 说明：返回A和B按位进行异或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。 举例： hive> select 4 ^ 8 from dual; 12 hive> select 6 ^ 4 from dual; 2 • 位取反操作 : ~ 语法: ~A 操作类型：所有数值类型 说明：返回A按位取反操作的结果。结果的数值类型等于A的类型。 举例： hive> select ~6 from dual; -7 hive> select ~4 from dual; -5 Hive逻辑运算 •逻辑与操作: AND •逻辑或操作: OR •逻辑非操作: NOT • 逻辑与操作 : AND 语法: A AND B 操作类型：boolean 说明：如果A和B均为TRUE，则为TRUE；否则为FALSE。如果A为NULL或B为NULL，则为NULL 举例： hive> select 1 from dual where 1=1 and 2=2; 1 • 逻辑或操作 : OR 语法: A OR B 操作类型：boolean 说明：如果A为TRUE，或者B为TRUE，或者A和B均为TRUE，则为TRUE；否则为FALSE 举例： hive> select 1 from dual where 1=2 or 2=2; 1 • 逻辑非操作 : NOT 语法: NOT A 操作类型：boolean 说明：如果A为FALSE，或者A为NULL，则为TRUE；否则为FALSE 举例： hive> select 1 from dual where not 1=2; • 逻辑非操作 : NOT 语法: NOT A 操作类型：boolean 说明：如果A为FALSE，或者A为NULL，则为TRUE；否则为FALSE 举例： hive> select 1 from dual where not 1=2 ; 第三部分：数值运算 •取整函数: round •指定精度取整函数: round •向下取整函数: floor •向上取整函数: ceil •向上取整函数: ceiling •取随机数函数: rand •自然指数函数: exp •以10为底对数函数: log10 •以2为底对数函数: log2 • 对数函数: log •幂运算函数: pow •幂运算函数: power •开平方函数: sqrt •二进制函数: bin •十六进制函数: hex •反转十六进制函数: unhex •进制转换函数: conv •绝对值函数: abs •正取余函数: pmod •正弦函数: sin •反正弦函数: asin •余弦函数: cos •反余弦函数: acos •positive函数: positive •negative函数: negative • 取整函数 : round 语法: round(double a) 返回值: BIGINT 说明: 返回double类型的整数值部分 （遵循四舍五入） 举例： hive> select round(3.1415926) from dual; 3 hive> select round(3.5) from dual; 4 hive> create table dual as select round(9542.158) from dual; hive> describe dual; _c0 bigint • 指定精度取整函数 : round 语法: round(double a, int d) 返回值: DOUBLE 说明: 返回指定精度d的double类型 举例： hive> select round(3.1415926,4) from dual; 3.1416 • 向下取整函数 : floor 语法: floor(double a) 返回值: BIGINT 说明: 返回等于或者小于该double变量的最大的整数 举例： hive> select floor(3.1415926) from dual; 3 hive> select floor(25) from dual; 25 • 向上取整函数 : ceil 语法: ceil(double a) 返回值: BIGINT 说明: 返回等于或者大于该double变量的最小的整数 举例： hive> select ceil(3.1415926) from dual; 4 hive> select ceil(46) from dual; 46 • 向上取整函数 : ceiling 语法: ceiling(double a) 返回值: BIGINT 说明: 与ceil功能相同 举例： hive> select ceiling(3.1415926) from dual; 4 hive> select ceiling(46) from dual; 46 • 取随机数函数 : rand 语法: rand(),rand(int seed) 返回值: double 说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列 举例： hive> select rand() from dual; 0.5577432776034763 • 自然指数函数 : exp 语法: exp(double a) 返回值: double 说明: 返回自然对数e的a次方 举例： hive> select exp(2) from dual; 7.3056093065 自然对数函数: ln 语法: ln(double a) 返回值: double 说明: 返回a的自然对数 • 以 10 为底对数函数 : log10 语法: log10(double a) 返回值: double 说明: 返回以10为底的a的对数 举例： hive> select log10(100) from dual; 2.0 • 以 2 为底对数函数 : log2 语法: log2(double a) 返回值: double 说明: 返回以2为底的a的对数 举例： hive> select log2(8) from dual; 3.0 • 对数函数 : log 语法: log(double base, double a) 返回值: double 说明: 返回以base为底的a的对数 举例： hive> select log(4,256) from dual; 4.0 • 幂运算函数 : pow 语法: pow(double a, double p) 返回值: double 说明: 返回a的p次幂 举例： hive> select pow(2,4) from dual; 16.0 • 幂运算函数 : power 语法: power(double a, double p) 返回值: double 说明: 返回a的p次幂,与pow功能相同 举例： hive> select power(2,4) from dual; 16.0 • 开平方函数 : sqrt 语法: sqrt(double a) 返回值: double 说明: 返回a的平方根 举例： hive> select sqrt(16) from dual; 4.0 • 二进制函数 : bin 语法: bin(BIGINT a) 返回值: string 说明: 返回a的二进制代码表示 举例： hive> select bin(7) from dual; 111 • 十六进制函数 : hex 语法: hex(BIGINT a) 返回值: string 说明: 如果变量是int类型，那么返回a的十六进制表示；如果变量是string类型，则返回该字符串的十六进制表示 举例： hive> select hex(17) from dual; 11 hive> select hex(‘abc’) from dual; 616263 • 反转十六进制函数 : unhex 语法: unhex(string a) 返回值: string 说明: 返回该十六进制字符串所代码的字符串 举例： hive> select unhex(‘616263’) from dual; abc hive> select unhex(‘11’) from dual; - hive> select unhex(616263) from dual; abc • 进制转换函数 : conv 语法: conv(BIGINT num, int from_base, int to_base) 返回值: string 说明: 将数值num从from_base进制转化到to_base进制 举例： hive> select conv(17,10,16) from dual; 11 hive> select conv(17,10,2) from dual; 10001 • 绝对值函数 : abs 语法: abs(double a) abs(int a) 返回值: double int 说明: 返回数值a的绝对值 举例： hive> select abs(-3.9) from dual; 3.9 hive> select abs(10.9) from dual; 10.9 • 正取余函数 : pmod 语法: pmod(int a, int b),pmod(double a, double b) 返回值: int double 说明: 返回正的a除以b的余数 举例： hive> select pmod(9,4) from dual; 1 hive> select pmod(-9,4) from dual; 3 • 正弦函数 : sin 语法: sin(double a) 返回值: double 说明: 返回a的正弦值 举例： hive> select sin(0.8) from dual; 0.71735609095228 • 反正弦函数 : asin 语法: asin(double a) 返回值: double 说明: 返回a的反正弦值 举例： hive> select asin(0.71735609095228) from dual; 0.8 • 余弦函数 : cos 语法: cos(double a) 返回值: double 说明: 返回a的余弦值 举例： hive> select cos(0.9) from dual; 0.62160996827064 • 反余弦函数 : acos 语法: acos(double a) 返回值: double 说明: 返回a的反余弦值 举例： hive> select acos(0.62160996827064) from dual; 0.9 • positive 函数 : positive 语法: positive(int a), positive(double a) 返回值: int double 说明: 返回a 举例： hive> select positive(-10) from dual; -10 hive> select positive(12) from dual; 12 • negative 函数 : negative 语法: negative(int a), negative(double a) 返回值: int double 说明: 返回-a 举例： hive> select negative(-5) from dual; 5 hive> select negative(8) from dual; -8 第四部分：日期函数 •UNIX时间戳转日期函数: from_unixtime • 获取当前UNIX时间戳函数: unix_timestamp •日期转UNIX时间戳函数: unix_timestamp • 指定格式日期转UNIX时间戳函数: unix_timestamp •日期时间转日期函数: to_date •日期转年函数: year • 日期转月函数: month • 日期转天函数: day • 日期转小时函数: hour • 日期转分钟函数: minute • 日期转秒函数: second • 日期转周函数: weekofyear • 日期比较函数: datediff • 日期增加函数: date_add • 日期减少函数: date_sub • UNIX 时间戳转日期函数 : from_unixtime 语法: from_unixtime(bigint unixtime[, string format]) 返回值: string 说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式 举例： hive> select from_unixtime(13233043,'yyyyMMdd') from dual; 20111208 • 获取当前 UNIX 时间戳函数 : unix_timestamp 语法: unix_timestamp() 返回值: bigint 说明: 获得当前时区的UNIX时间戳 举例： hive> select unix_timestamp() from dual; 1323309615 • 日期转 UNIX 时间戳函数 : unix_timestamp 语法: unix_timestamp(string date) 返回值: bigint 说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。 举例： hive> select unix_timestamp('2011-12-07 13:01:03') from dual; 1323234063 • 指定格式日期转 UNIX 时间戳函数 : unix_timestamp 语法: unix_timestamp(string date, string pattern) 返回值: bigint 说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。 举例： hive> select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from dual; 1323234063 • 日期时间转日期函数 : to_date 语法: to_date(string timestamp) 返回值: string 说明: 返回日期时间字段中的日期部分。 举例： hive> select to_date('2011-12-08 10:03:01') from dual; 2011-12-08 • 日期转年函数 : year 语法: year(string date) 返回值: int 说明: 返回日期中的年。 举例： 日期格式要带- hive> select year('2011-12-08 10:03:01') from dual; 2011 hive> select year('2012-12-08') from dual; 2012 • 日期转月函数 : month 语法: month (string date) 返回值: int 说明: 返回日期中的月份。 举例： hive> select month('2011-12-08 10:03:01') from dual; 12 hive> select month('2011-08-08') from dual; 8 • 日期转天函数 : day 语法: day (string date) 返回值: int 说明: 返回日期中的天。 举例： hive> select day('2011-12-08 10:03:01') from dual; 8 hive> select day('2011-12-24') from dual; 24 • 日期转小时函数 : hour 语法: hour (string date) 返回值: int 说明: 返回日期中的小时。 举例： hive> select hour('2011-12-08 10:03:01') from dual; 10 • 日期转分钟函数 : minute 语法: minute (string date) 返回值: int 说明: 返回日期中的分钟。 举例： hive> select minute('2011-12-08 10:03:01') from dual; 3 • 日期转秒函数 : second 语法: second (string date) 返回值: int 说明: 返回日期中的秒。 举例： hive> select second('2011-12-08 10:03:01') from dual; 1 • 日期转周函数 : weekofyear 语法: weekofyear (string date) 返回值: int 说明: 返回日期在当前的周数。 举例： hive> select weekofyear('2011-12-08 10:03:01') from dual; 49 • 日期比较函数 : datediff 语法: datediff(string enddate, string startdate) 返回值: int 说明: 返回结束日期减去开始日期的天数。 举例： hive> select datediff('2012-12-08','2012-05-09') from dual; 213 • 日期增加函数 : date_add 语法: date_add(string startdate, int days) 返回值: string 说明: 返回开始日期startdate增加days天后的日期。 举例： hive> select date_add('2012-12-08',10) from dual; 2012-12-18 • 日期减少函数 : date_sub 语法: date_sub (string startdate, int days) 返回值: string 说明: 返回开始日期startdate减少days天后的日期。 举例： hive> select date_sub('2012-12-08',10) from dual; 2012-11-28 第五部分：条件函数 •If函数: if •非空查找函数: COALESCE •条件判断函数：CASE • If 函数 : if 语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull) 返回值: T 说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull 举例： hive> select if(1=2,100,200) from dual; 200 hive> select if(1=1,100,200) from dual; 100 • 非空查找函数 : COALESCE 语法: COALESCE(T v1, T v2, …) 返回值: T 说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL 举例： hive> select COALESCE(null,'100','50′) from dual; 100 条件判断函数： CASE 语法 : CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END 返回值 : T 说明：如果 a 等于 b ，那么返回 c ；如果 a 等于 d ，那么返回 e ；否则返回 f 举例： hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from dual; mary 第六部分：字符串函数 •字符串长度函数：length •字符串反转函数：reverse •字符串连接函数：concat • 带分隔符字符串连接函数：concat_ws • 字符串截取函数：substr,substring • 字符串截取函数：substr,substring • 字符串转大写函数：upper,ucase • 字符串转小写函数：lower,lcase • 去空格函数：trim • 左边去空格函数：ltrim • 右边去空格函数：rtrim •正则表达式替换函数：regexp_replace •正则表达式解析函数：regexp_extract •URL解析函数：parse_url •json解析函数：get_json_object •空格字符串函数：space •重复字符串函数：repeat •首字符ascii函数：ascii •左补足函数：lpad •右补足函数：rpad •分割字符串函数: split •集合查找函数: find_in_set • 字符串长度函数： length 语法: length(string A) 返回值: int 说明：返回字符串A的长度 举例： hive> select length('abcedfg') from dual; 7 • 字符串反转函数： reverse 语法: reverse(string A) 返回值: string 说明：返回字符串A的反转结果 举例： hive> select reverse(abcedfg’) from dual; gfdecba • 字符串连接函数： concat 语法: concat(string A, string B…) 返回值: string 说明：返回输入字符串连接后的结果，支持任意个输入字符串 举例： hive> select concat(‘abc’,'def’,'gh’) from dual; abcdefgh • 带分隔符字符串连接函数： concat_ws 语法: concat_ws(string SEP, string A, string B…) 返回值: string 说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符 举例： hive> select concat_ws(',','abc','def','gh') from 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 • 字符串截取函数： substr,substring 语法: substr(string A, int start, int len),substring(string A, int start, int len) 返回值: string 说明：返回字符串A从start位置开始，长度为len的字符串 举例： hive> select substr('abcde',3,2) from dual; cd hive> select substring('abcde',3,2) from dual; cd hive>select substring('abcde',-2,2) from dual; de • 字符串转大写函数： upper,ucase 语法: upper(string A) ucase(string A) 返回值: string 说明：返回字符串A的大写格式 举例： hive> select upper('abSEd') from dual; ABSED hive> select ucase('abSEd') from dual; ABSED • 字符串转小写函数： lower,lcase 语法: lower(string A) lcase(string A) 返回值: string 说明：返回字符串A的小写格式 举例： 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 • 正则表达式替换函数： regexp_replace 语法: regexp_replace(string A, string B, string C) 返回值: string 说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。 举例： hive> select regexp_replace('foobar', 'oo|ar', '') from dual; fb • 正则表达式解析函数： regexp_extract 语法: regexp_extract(string subject, string pattern, int index) 返回值: string 说明：将字符串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 注意，在有些情况下要使用转义字符，下面的等号要用双竖线转 义，这是 java 正则表达式的规则。 select data_field, regexp_extract(data_field,'.*?bgStart\\\\=([^&]+)',1) as aaa, regexp_extract(data_field,'.*?contentLoaded_headStart\\\\=([^&]+)',1) as bbb, regexp_extract(data_field,'.*?AppLoad2Req\\\\=([^&]+)',1) as ccc from pt_nginx_loginlog_st where pt = '2012-03-26' limit 2; • 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://.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from dual; .com hive> select parse_url('http://.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') from dual; v1 • json 解析函数： get_json_object 语法: get_json_object(string json_string, string path) 返回值: string 说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。 举例： hive> 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 • 空格字符串函数： space 语法: space(int n) 返回值: string 说明：返回长度为n的字符串 举例： hive> select space(10) from dual; hive> select length(space(10)) from dual; 10 • 重复字符串函数： repeat 语法: repeat(string str, int n) 返回值: string 说明：返回重复n次后的str字符串 举例： hive> select repeat('abc',5) from dual; abcabcabcabcabc • 首字符 ascii 函数： ascii 语法: ascii(string str) 返回值: int 说明：返回字符串str第一个字符的ascii码 举例： hive> select ascii('abcde') from dual; 97 • 左补足函数： lpad 语法: lpad(string str, int len, string pad) 返回值: string 说明：将str进行用pad进行左补足到len位 举例： hive> select lpad('abc',10,'td') from dual; tdtdtdtabc 注意：与 GP ， ORACLE 不同， pad 不能默认 • 右补足函数： rpad 语法: rpad(string str, int len, string pad) 返回值: string 说明：将str进行用pad进行右补足到len位 举例： hive> select rpad('abc',10,'td') from dual; abctdtdtdt • 分割字符串函数 : split 语法: split(string str, string pat) 返回值: array 说明: 按照pat字符串分割str，会返回分割后的字符串数组 举例： hive> select split('abtcdtef','t') from dual; ["ab • 集合查找函数 : 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 第七部分：集合统计函数 •个数统计函数: count •总和统计函数: sum •平均值统计函数: avg • 最小值统计函数: min •最大值统计函数: max • 个数统计函数 : count 语法: count(*), count(expr), count(DISTINCT expr[, expr_.]) 返回值: int 说明: count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；count(DISTINCT expr[, expr_.])返回指定字段的不同的非空值的个数 举例： hive> select count(*) from dual; 20 hive> select count(distinct t) from dual; 10 • 总和统计函数 : sum 语法: sum(col), sum(DISTINCT col) 返回值: double 说明: sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果 举例： hive> select sum(t) from dual; 100 hive> select sum(distinct t) from dual; 70 • 平均值统计函数 : avg 语法: avg(col), avg(DISTINCT col) 返回值: double 说明: avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值 举例： hive> select avg(t) from dual; 50 hive> select avg (distinct t) from dual; 30 • 最小值统计函数 : min 语法: min(col) 返回值: double 说明: 统计结果集中col字段的最小值 举例： hive> select min(t) from dual; 20 • 最大值统计函数 : max 语法: maxcol) 返回值: double 说明: 统计结果集中col字段的最大值 举例： hive> select max(t) from dual; 120 第八部分：符合类型构建操作 •Map类型构建: map •Struct类型构建: struct •array类型构建: array • Map 类型构建 : map 语法: map (key1, value1, key2, value2, …) 说明：根据输入的key和value对构建map类型 举例： hive> Create table alex_test as select map('100','tom','200','mary') as t from dual; hive> describe alex_test; t map hive> select t from alex_test; {"100":"tom • Struct 类型构建 : struct 语法: struct(val1, val2, val3, …) 说明：根据输入的参数构建结构体struct类型 举例： hive> create table alex_test as select struct('tom','mary','tim') as t from dual; hive> describe alex_test; t struct hive> select t from alex_test; {"col1":"tom • array 类型构建 : array 语法: array(val1, val2, …) 说明：根据输入的参数构建数组array类型 举例： hive> create table alex_test as select array("tom hive> describe alex_test; t array hive> select t from alex_test; ["tom 第九部分：复杂类型访问操作 •array类型访问: A[n] •map类型访问: M[key] •struct类型访问: S.x • array 类型访问 : A[n] 语法: A[n] 操作类型: A为array类型，n为int类型 说明：返回数组A中的第n个变量值。数组的起始下标为0。比如，A是个值为['foo', 'bar']的数组类型，那么A[0]将返回'foo',而A[1]将返回'bar' 举例： hive> create table alex_test as select array("tom hive> select t[0],t[1],t[2] from alex_test; tom mary tim • map 类型访问 : M[key] 语法: M[key] 操作类型: M为map类型，key为map中的key值 说明：返回map类型M中，key值为指定值的value值。比如，M是值为{'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'}的map类型，那么M['all']将会返回'foobar' 举例： hive> Create table alex_test as select map('100','tom','200','mary') as t from dual; hive> select t['200'],t['100'] from alex_test; mary tom • struct 类型访问 : S.x 语法: S.x 操作类型: S为struct类型 说明：返回结构体S中的x字段。比如，对于结构体struct foobar {int foo, int bar}，foobar.foo返回结构体中的foo字段 举例： hive> create table alex_test as select struct('tom','mary','tim') as t from dual; hive> describe alex_test; t struct hive> select t.col1,t.col3 from alex_test; tom tim 第十部分：复杂类型长度统计函数 •Map类型长度函数: size(Map •array类型长度函数: size(Array •类型转换函数 \\;lkjfdsaaaaaaaaaa • Map 类型长度函数 : size(Map 语法: size(Map 返回值: int 说明: 返回map类型的长度 举例： hive> select size(map('100','tom','101','mary')) from dual; 2 • array 类型长度函数 : size(Array 语法: size(Array 返回值: int 说明: 返回array类型的长度 举例： hive> select size(array('100','101','102','103')) from dual; 4 • 类型转换函数 类型转换函数: cast 语法: cast(expr as 返回值: Expected "=" to follow "type" 说明: 返回array类型的长度 举例： hive> select cast(1 as bigint) from dual; 1
Copyright © 2019-2025 51dongshi.net 版权所有
违法及侵权请联系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市万商天勤律师事务所王兴未律师提供法律服务
