HIVE教程（一)HIVE基本介绍

HIVE教程（一）
HIVe基本介绍
hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行。Hive的优点是学习成本低，可以通过类似SQL语句实现快速MapReduce统计，使MapReduce变得更加简单，而不必开发专门的MapReduce应用程序。hive是十分适合数据仓库的统计分析和Windows注册表文件。

hive 是一种底层封装了Hadoop 的数据仓库处理工具，使用类SQL 的hiveQL 语言实现数据查询，所有hive 的数据都存储在Hadoop 兼容的文件系统（例如，Amazon S3、HDFS）中。hive 在加载数据过程中不会对数据进行任何的修改，只是将数据移动到HDFS 中hive 设定的目录下，因此，hive 不支持对数据的改写和添加，所有的数据都是在加载的时候确定的。hive 的设计特点如下。 
● 支持创建索引，优化数据查询。
● 不同的存储类型，例如，纯文本文件、HBase 中的文件。
● 将元数据保存在关系数据库中，大大减少了在查询过程中执行语义检查的时间。
● 可以直接使用存储在Hadoop 文件系统中的数据。
● 内置大量用户函数UDF 来操作时间、字符串和其他的数据挖掘工具，支持用户扩展UDF 函数来完成内置函数无法实现的操作。
● 类SQL 的查询方式，将SQL 查询转换为MapReduce 的job 在Hadoop集群上执行。
一、基本介绍
1、HIVE类型
Hive所有数据类型分为四种类型，列类型，文字，Null 值，复杂类型
列类型：
整型：TINYINT，SMALLINT，INT，BIGINT
字符串类型：字符串类型（1 to 65355），CHAR（255）
时间戳：它支持传统的UNIX时间戳可选纳秒的精度。它支持的java.sql.Timestamp格式“YYYY-MM-DD HH:MM:SS.fffffffff”和格式“YYYY-MM-DD HH:MM:ss.ffffffffff”
日期：DATE值在年/月/日的格式形式描述 {{YYYY-MM-DD}}.
小数点:DECIMAL(precision, scale)，decimal(10,0)
2、创建数据库
CREATE DATABASE|SCHEMA [IF NOT EXISTS] <database name>
IF NOT EXISTS是一个可选子句，通知用户已经存在相同名称的数据库
例如：
CREATE DATABASE [IF NOT EXISTS] userdb;
验证数据库
SHOW DATABASES;
3、删除数据库
DROP DATABASE StatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name
[RESTRICT|CASCADE];
例如：DROP DATABASE IF EXISTS userdb;

4、创建表
语法：
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name

[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]
例子：
 CREATE TABLE IF NOT EXISTS employee ( eid int, name String,
 salary String, destination String)
 COMMENT ‘Employee details’
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ‘\t’
 LINES TERMINATED BY ‘\n’
 STORED AS TEXTFILE;
5、修改表
ALTER TABLE name RENAME TO new_name
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
ALTER TABLE name DROP [COLUMN] column_name
ALTER TABLE name CHANGE column_name new_name new_type
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])
查询重命名表，把 employee 修改为 emp
ALTER TABLE employee RENAME TO emp;
ALTER TABLE employee CHANGE name ename String;
ALTER TABLE employee CHANGE salary salary Double;

5、删除表
DROP TABLE [IF EXISTS] table_name;


6、Hive分区
    增加分区：
    ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec
[LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...;

partition_spec:
: (p_column = p_col_value, p_column = p_col_value, ...)
ALTER TABLE employee
 ADD PARTITION (year=’2013’)
location '/2012/part2012';
重命名分区
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

删除分区
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec, PARTITION partition_spec,...;
ALTER TABLE employee DROP [IF EXISTS]
   > PARTITION (year=’1203’);

二、基本语法
1、关系运算符

A LIKE B    如果字符串模式A匹配到B，否则FALSE。
A RLIKE B 如果A或B为NULL；TRUE，如果A任何子字符串匹配Java正则表达式B；否则FALSE
A REGEXP B 等同于RLIKE.
2、算术运算符
+，-，*，/，%，&，|,^,~
3、逻辑运算符
A AND B 同 A && B
A OR B 同A || B
NOT A 同 !A
4、基本查询
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]
[LIMIT number];
例子：SELECT * FROM employee WHERE Salary>=40000;
（1）ORDER BY
ORDER BY子句用于检索基于一列的细节并设置排序结果按升序或降序排列。
SELECT Id, Name, Dept FROM employee ORDER BY DEPT;
生成一个查询用于检索员工的详细信息。

（2）GROUP BY
GROUP BY子句用于分类所有记录结果的特定集合列。它被用来查询一组记录。
SELECT Dept,count(*) FROM employee GROUP BY DEPT;
检索每个部门的员工数量
（3）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]

JOIN

    JOIN子句用于合并和检索来自多个表中的记录。 JOIN和SQLOUTER JOIN 类似。连接条件是使用主键和表的外键。

    下面的查询执行JOIN的CUSTOMER和ORDER表，并检索记录：
hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT
   > FROM CUSTOMERS c JOIN ORDERS o
   > ON (c.ID = o.CUSTOMER_ID);

    成功执行查询后，能看到以下回应：
+----+----------+-----+--------+
| ID | NAME     | AGE | AMOUNT |
+----+----------+-----+--------+
| 3  | kaushik  | 23  | 3000   |
| 3  | kaushik  | 23  | 1500   |
| 2  | Khilan   | 25  | 1560   |
| 4  | Chaitali | 25  | 2060   |
+----+----------+-----+--------+


    LEFT OUTER JOIN

    HiveQL LEFT OUTER JOIN返回所有行左表，即使是在正确的表中没有匹配。这意味着，如果ON子句匹配的右表0（零）记录，JOIN还是返回结果行，但在右表中的每一列为NULL。

    LEFT JOIN返回左表中的所有的值，加上右表，或JOIN子句没有匹配的情况下返回NULL。

    下面的查询演示了CUSTOMER 和ORDER 表之间的LEFT OUTER JOIN用法：
hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE
   > FROM CUSTOMERS c
   > LEFT OUTER JOIN ORDERS o
   > ON (c.ID = o.CUSTOMER_ID);

    成功执行查询后，能看到以下回应：
+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                |
+----+----------+--------+---------------------+
| 1  | Ramesh   | NULL   | NULL                |
| 2  | Khilan   | 1560   | 2009-11-20 00:00:00 |
| 3  | kaushik  | 3000   | 2009-10-08 00:00:00 |
| 3  | kaushik  | 1500   | 2009-10-08 00:00:00 |
| 4  | Chaitali | 2060   | 2008-05-20 00:00:00 |
| 5  | Hardik   | NULL   | NULL                |
| 6  | Komal    | NULL   | NULL                |
| 7  | Muffy    | NULL   | NULL                |
+----+----------+--------+---------------------+


    RIGHT OUTER JOIN

    HiveQL RIGHT OUTER JOIN返回右边表的所有行，即使有在左表中没有匹配。如果ON子句的左表匹配0（零）的记录，JOIN结果返回一行，但在左表中的每一列为NULL。

    RIGHT JOIN返回右表中的所有值，加上左表，或者没有匹配的情况下返回NULL。

    下面的查询演示了在CUSTOMER和ORDER表之间使用RIGHT OUTER JOIN。
hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE
   > FROM CUSTOMERS c
   > RIGHT OUTER JOIN ORDERS o
   > ON (c.ID = o.CUSTOMER_ID);

    成功执行查询后，能看到以下回应：
+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 |
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 |
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 |
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+


    FULL OUTER JOIN

    HiveQL FULL OUTER JOIN结合了左边，并且满足JOIN条件合适外部表的记录。连接表包含两个表的所有记录，或两侧缺少匹配结果那么使用NULL值填补

    下面的查询演示了CUSTOMER 和ORDER 表之间使用的FULL OUTER JOIN：
hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE
   > FROM CUSTOMERS c
   > FULL OUTER JOIN ORDERS o
   > ON (c.ID = o.CUSTOMER_ID);

    成功执行查询后，能看到以下回应：
+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
| 1    | Ramesh   | NULL   | NULL                |
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 |
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 |
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 |
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |
| 5    | Hardik   | NULL   | NULL                |
| 6    | Komal    | NULL   | NULL                |
| 7    | Muffy    | NULL   | NULL                | 
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 |
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 |
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 |
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+




三、逻辑判断
1、if
例子：
if(merchandise_gds_orders_week is null, 0, merchandise_gds_orders_week) as orders_week,
嵌套if
if(merchandise_gds_orders / merchandise_gds_is_4_page > 1.0, 1.0, if(merchandise_gds_is_4_page = 0, 0.0, merchandise_gds_orders / merchandise_gds_is_4_page))
2、case when
总结一下：两种表达方式是等效的
方法一：

case
when tb1.os = 'android' then 'android'
when tb1.os = 'ios' then 'iPhone'
else 'PC'
end as os,


case tb1.os
when 'android' then 'android'
when 'ios' then 'iPhone'
else 'PC'
end as os,

HIVE教程（一)HIVE基本介绍