Hadoop 之Hive

/*
Hive提供了sql语言到map-reduce的映射器
Hive不算是完整的数据库，它是基于hadoop的数据查询分析工具，它不具备行数据的DML操作。
Hive提供了诸如分区，外部表，集合类型列，正则表达式等高级特性。

*/

NoSQL,NOT Only SQL。并非是表格式的数据机构，甚至它的表结构是不固定的。不仅仅用sql来查询。
NewSQL,SQL的逆袭
解决"即席查询"的问题
Hive还不算是完整的数据库系统

数据仓库工具，可以把Hadoop下的原始结构化数据变为Hive中的表。
支持一种与SQL几乎完全相同的语言HiveQL。除了不支持更新，索引和事务，几乎SQL的其他
特征都能支持。
可以看成是SQL到Map-Reduce的映射器。
提供shell,JDBC/ODBC,Thrift,web等接口。


Hive 构架：
Hive用户接口：shell,thrift,web等.
Thrift服务器(Java 接口)
元数据库 "Derby,Mysql"等
解析器
Hadoop


UDF = USER DEFINITION FUNCTION

Hive安装

内嵌模式: 元数据保持在内嵌的Derby模式，只允许一个会话连接。
本地独立模式：在本地安装Mysql，把元数据放在Mysql内。
远程模式：元数据放置在远程的Mysql数据库。

Hive Sql语言
--创建表sql
create table Loc(
CDRID STRING,
IMSI INT,
....
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE;

--观看表的描述

DESCRIBE EXTENDED mydb.employees;

--插入数据sql
LOAD DATA LOCAL INPATH '/home/james/location_20120316.txt'
OVERWRITE INTO TABLE LOC;

INSERT OVERWRITE TABLE RESULT
SELECT 
IMSI,IMEI,SUBSTR(CGI,8),STARTTIME,NULL,UPDATETYPE,3......
FROM LOC
WHERE IMSI IS NOT NULL;

--查询SQL
SELECT IMSI,CGI,TIME FROM RESULT;
SELECT BAR FROM POKES LIMIT 5;

--表连接
SELECT RESULT.IMSI,LOC.INSTIME
FROM RESULT JOIN LOC ON (RESULT.IMSI = LOC.IMSI);


--显示库中的表名
show tables;

--删除表
drop table abc;



JDBC/ODBC接口

用户可以像传统关系数据库一样使用JDBC或ODBC连接Hive
目前还不成熟
使用jdbc的方式连接Hive，首先做的事情就是需要启劢hive的Thrift Server,否则连接hive的时候会报connection refused的错误。
启动命令：
hive -- service hiveserver

Hive的数据放在那儿？
数据在HDFS的warehouse目录下，一个表对应一个子目录。
桶与reduce
本地的/tmp目录存放日志和执行计划

集合数据类型
struct('John','Doe')
map('first','john','last','Doe')
array('john','Doe')

使用样例
create table employees(
name STRING,
salary FLOAT,
subordinates ARRAY(<TRING>,
deductions MAP<STRING,FLOAT>,
address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>);
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

DDL操作
定义数据库
定义表

--创建和列表数据库
CREATE DATABASE financials;
CREATE DATABASE IF NOT EXISTS financials;
SHOW DATABASES;
SHOW DATABASES LIKE 'h.*'

--改变数据库存放目录
CREATE DATABASE financials
LOCATION '/my/preferred/directory'

--切换数据库
USE financials;
--显示数据库名称
set hive.cli.print.current.db=true;
--删除和更改数据库
DROP DATABASE IF EXISTS financials;
DROP DATABASE IF EXISTS financials CASCADE;
--数据库的扩展属性信息
ALTER DATABASE financials SET DBPROPERTIES('edited-by','Joe Dba');

--外部表
--方便对外部数据文件的阅读，而且不会将数据加载到oracle
Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径， 
不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除， 
而外部表只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。


CREATE EXTERNAL TABLE IF NOT EXISTS stocks
(
exchange STIRNG,
...
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks'

--分区表
--将数据物理上划分，但并不影响sql的操作。
create table employees(
name STRING,
salary FLOAT,
...
)
PARTITIONED BY (country STRING,state STRING);

注意分区关键字并不需要出现在列字段当中。它会自动保存在目录名称中。
分区表的存储：会变成一个子目录里面的一系列文件。

--Strict模式及其对操作的影响
set hive.mapred.mode=strict;

若设置为strict模式，则不允许不指定条件查询，即where中没有分区条件的过滤。
除非设置为nostrict模式。


--列出分区

SHOW PARTITIONS employees PARTITION(country='US');
SHOW PARTITIONS employees PARTITION(country='US','state=AK');

--更改表
alter table xx rename to xx;
--添加分区
alter table xx add if not exists
partition(year=2011,month=1,day=1) location '/logs/2011/01/01'


--列操作
--修改列的类型
alter table xx change column xx int;

--新增列
alter table xx add columns (xx string comment '');
--replace列
alter table log_messages replace columns
(
xx int comment ''
);

DML操作

不支持行级别的DML操作。将数据放入表中的唯一办法就是批量载入。

--数据批量加载语句
Load data local input 'path'
overwrite into table employees
partition(country='US',state='CA')

--insert overwrite语句

insert overwrite table employees
partition(country='US',state='OR')
select * from staged_employees se
where se.cnty='US' and se.st='OR';


--数据插入分区表。Hadoop需要呆板的指定各个分区的数据，然后插入到分区表中。
--除非通过参数开启动态分区插入
FROM staged_employees se
INSERT OVERWRITE table employees
partition(country='US',state='OR')
select * where se.cnty='US' and se.st='OR'
INSERT OVERWRITE table employees
partition(country='US',state='CA')
select * where se.cnty='US' and se.st='CA'
INSERT OVERWRITE table employees
partition(country='US',state='IL')
select * where se.cnty='US' and se.st='IL';

--动态分区插入，不必要指定分区条件。
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrict;
set hive.exec.max.dynamic.partitions.pernode=1000;

insert overwrite table employees
partition(country,state)
select ..,se.cty,se.st
from staged_employees se;
--创建表
create table ... as select ...

--导出数据
--由于数据文件本身是文本明文，可以直接使用hdfs的拷贝文件导出
--
INSERT OVERWRITE LOCAL DIRECTORY 'tmp/ca_employees'
select name,salary,address
from employees
where se.state = 'CA';

--使用正则表达式
--查询以prick开头的任意列
SELECT sysmbol,price.* FROM stocks;

--%相当于任意多个字符。_表示任意单个字符
SELECT NAME,ADDRESS.STREET FROM EMPLOYEES WHERE ADDRESS.STREET
LIKE '%Ave.';

--RLIKE 指正则表达式
select name,address.street
from employees where address.street RLIKE '.*(Chicago|Ontario).*';

--explode 函数

select array(1,2,3) from dual;
[1,2,3]

select explode(array(1,2,3)) as elements from src;
1,
2,
3

--嵌套视图
FROM
(
select upper(name),salary,deductions["Federal Taxes"] as fed_taxes,
round(salary*(1-deductions["Federal Taxes"])) as salary_minus_fed_taxes
from employees
)e
select e.name,e.salary_minus_fed_taxes
where e.salary_minus_fed_taxes >70000;

--查询优化小技巧
--加快聚组统计的速度，原理相当于map-reduce中的combine。
--先在每个节点汇总统计，然后将汇总数推送到reduce节点
set hive.map.aggr=true;
select count(*),avg(salary) from employees;


--连接操作,支持大部分的关系代数连接方式(各种内连接,外连接,半连接)
--连接是缓慢的操作!
--原本是将数据送到reduce节点进行连接,使用"map-side joins",它会将两个表中的小表完整的复制到各个存在大表数据的节点,
--然后连接后发送到reduce节点。
--不太成熟，会经常判断失误
支持大部分常见的关系代数连接方式
连接是缓慢的操作
使用map-side joins优化连接.

select /*+ MAPJOIN(d) */
s.ymd,s.symbol,s.price_close,d.dividend
FROM stocks s JOIN dividends d on s.ymd = d.ymd
and s.symbol=d.symbol
where s.symbol='AAPL';

--hive.auto.convert.join=true
--hive 将自动判断是否适合使用mapjoin.
--判断的条件是两个表中的其中小表是否足够小。
--hive.mapjoin.smalltable.filesize=25000000
--此参数是判断小表是否足够小的标准


--排序

order by --全局排序
sort by --多个reduce排序,不保证全局有序.仅在reducer节点内排序
distribute by--将相同的字段值送到同一个reduce节点,通常与sort by一块使用
select s.ymd,s.symbol,s.price_close
from stocks s
distribute by s.symbol
sort by s.symbol asc,s.ymd asc;


cluster by -- 相当于sort by + distribute by 

--bucket 抽样查询
抽样查询时数据分析里常见的操作
select * from number tablesample(BUCKET 3 OUT OF 10 ON rand())s;
--块级抽样
select * from numbersflat TABLESAMPLE(0.1 PERCENT) S;

--视图与索引
--Hive具有与关系型数据库基本类似的视图功能
Hive只有非常简单的索引，关系型数据库的索引时B+树算法
实现的，Hive的索引只是简单低把排序数据放到另外一个表中。