Hive使用摘要

之前有写过pandas和numpy的一些浅显的使用，没有系统的学习所有的API，基本上还是在够用的基础上，以后如果需要更复杂的数据需求再进一步研究吧。最近转做后台，之前在写numpy使用的时候立了个flag说要做一些有产出的机器学习项目。现在确实做了文章的推荐模型的训练，从Hive取数到模型输出，不过最终没能部署上线，是有些遗憾（公司部署环境对python不友好，可能需要spark模型上线），可能之后上线之后再把上线的流程补上吧。项目告一段落最后也想说总结一下（其实是反过来在看的时候有些东西都忘了~~记录一下），最近跟数据打交道的比较多，也结合之前训练数据提取的经验，会写一系列的文章。首先第一篇就先写Hive吧。知识不够全面，不过我平时也够用

首先第一步就是补上网上大神的参考文章

• lwx的大数据田地

基本操作

Hive的基本操作与MYSQL类似，除了部分函数不支持以外，可以按照写sql的思路来写Hive sql。

新建

新建表：

CREATE TABLE `test`(
`id` bigint COMMENT '这是字段备注',
`value` string COMMENT '这是字段备注')
COMMENT '表名称备注'
PARTITIONED BY (
`dt` string)


上面是一个创建表的语句，创建的是一个含有id和value字段的hive表，有dt分区。

分区就是存储时候的文件夹名称。因为hive表也是在HDFS之上的应用，所以也是存储到硬盘的。

添加分区：

ALTER TABLE table_name ADD PARTITION (partCol = 'value1') location 'loc1'; //示例
ALTER TABLE table_name ADD IF NOT EXISTS PARTITION (dt='20130101') LOCATION '/user/hadoop/warehouse/table_name/dt=20130101'; //一次添加一个分区
ALTER TABLE page_view ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808' PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809'; //一次添加多个分区


添加新列：

ALTER TABLE table_name ADD COLUMNS (col_name STRING); //在所有存在的列后面，但是在分区列之前添加一列


删除

删除表：

DROP TABLE IF NOT EXISTS tablename;


删除分区：

ALTER TABLE login DROP IF EXISTS PARTITION (dt='2008-08-08');
ALTER TABLE page_view DROP IF EXISTS PARTITION (dt='2008-08-08', country='us');


修改

表的重命名：

ALTER TABLE table_name RENAME TO new_table_name


修改列：

CREATE TABLE test_change (a int, b int, c int);
// will change column a's name to a1
ALTER TABLE test_change CHANGE a a1 INT;
// will change column a's name to a1, a's data type to string, and put it after column b. The new table's structure is: b int, a1 string, c int
ALTER TABLE test_change CHANGE a a1 STRING AFTER b;
// will change column b's name to b1, and put it as the first column. The new table's structure is: b1 int, a string, c int
ALTER TABLE test_change CHANGE b b1 INT FIRST;


修改表属性：

alter table table_name set TBLPROPERTIES ('EXTERNAL'='TRUE'); //内部表转外部表
alter table table_name set TBLPROPERTIES ('EXTERNAL'='FALSE'); //外部表转内部表


JOIN

select * from A join B on A.id = B.id; //取交集
select * from A left join/left outer join B on A.id = B.id; // A表为基准表
select * from A right join/right outer join B on A.id = B.id; // B表为基准表
select * from A full outer join B on A.id = B.id;//取并集
select * from A left semi join B on A.id = B.id; //可以用来取差集


窗口分析函数

统计函数

over(partition by&＃8230;order by)窗口函数可以统计个数，统计最大值，最小值等信息。partition by语法可以看成是局部的group by，order by就是排序。使用方法与order by 一样。

另外随机取数的时候可以使用order by rand()

以上的函数也可以结合group by函数来计算，但是没有上述的灵活。在使用这些统计函数的时候有些小的trick，就是灵活的结合CASE WHEN函数，比如我们要统计前三个月的pv

SUM(CASE WHEN time > 3month TEHN 1 ELSE NULL) AS 3monthPV


SUM示例

准备数据：

SELECT COOKIEid,
createtime,
pv,
SUM(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
SUM(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1
SUM(pv) OVER(PARTITION BY COOKIEid) AS pv3, --分组内所有行
SUM(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
SUM(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
SUM(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---当前行+往后所有行
FROM lxw1234;
COOKIEid createtime pv pv1 pv2 pv3 pv4 pv5 pv6
-----------------------------------------------------------------------------
COOKIE1 2015-04-10 1 1 1 26 1 6 26
COOKIE1 2015-04-11 5 6 6 26 6 13 25
COOKIE1 2015-04-12 7 13 13 26 13 16 20
COOKIE1 2015-04-13 3 16 16 26 16 18 13
COOKIE1 2015-04-14 2 18 18 26 17 21 10
COOKIE1 2015-04-15 4 22 22 26 16 20 8
COOKIE1 2015-04-16 4 26 26 26 13 13 4


• pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
• pv2: 同pv1
• pv3: 分组内(COOKIE1)所有的pv累加
• pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号， 13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号
• pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
• pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，14号=14号+15号+16号=2+4+4=10

• 如果不指定ROWS BETWEEN,默认为从起点到当前行;

• 如果不指定ORDER BY，则将分组内所有值累加;
• 关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
• PRECEDING：往前
• FOLLOWING：往后
• CURRENT ROW：当前行
• UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

序列函数

序列函数主要是给数据排序

NTILE

SELECT
COOKIEid,
createtime,
pv,
NTILE(2) OVER(PARTITION BY COOKIEid ORDER BY createtime) AS rn1, --分组内将数据分成2片
NTILE(3) OVER(PARTITION BY COOKIEid ORDER BY createtime) AS rn2, --分组内将数据分成3片
NTILE(4) OVER(ORDER BY createtime) AS rn3 --将所有数据分成4片
FROM lxw1234
ORDER BY COOKIEid,createtime;
COOKIEid day pv rn1 rn2 rn3
-------------------------------------------------
COOKIE1 2015-04-10 1 1 1 1
COOKIE1 2015-04-11 5 1 1 1
COOKIE1 2015-04-12 7 1 1 2
COOKIE1 2015-04-13 3 1 2 2
COOKIE1 2015-04-14 2 2 2 3
COOKIE1 2015-04-15 4 2 3 3
COOKIE1 2015-04-16 4 2 3 4
COOKIE2 2015-04-10 2 1 1 1
COOKIE2 2015-04-11 3 1 1 1
COOKIE2 2015-04-12 5 1 1 2
COOKIE2 2015-04-13 6 1 2 2
COOKIE2 2015-04-14 3 2 2 3
COOKIE2 2015-04-15 9 2 3 4
COOKIE2 2015-04-16 7 2 3 4


排序

SELECT
COOKIEid,
createtime,
pv,
RANK() OVER(PARTITION BY COOKIEid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY COOKIEid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY COOKIEid ORDER BY pv DESC) AS rn3
FROM lxw1234
WHERE COOKIEid = 'COOKIE1';
COOKIEid day pv rn1 rn2 rn3
--------------------------------------------------
COOKIE1 2015-04-12 7 1 1 1
COOKIE1 2015-04-11 5 2 2 2
COOKIE1 2015-04-15 4 3 3 3
COOKIE1 2015-04-16 4 3 3 4
COOKIE1 2015-04-13 3 5 4 5
COOKIE1 2015-04-14 2 6 5 6
COOKIE1 2015-04-10 1 7 6 7
rn1: 15号和16号并列第3, 13号排第5
rn2: 15号和16号并列第3, 13号排第4
rn3: 如果相等，则按记录值排序，生成唯一的次序，如果所有记录值都相等，或许会随机排吧。


条件函数

• IF函数：IF
• 非空查找函数：COALESCE
• 条件判断函数：CASE

集合操作

test1

test2

交集（exist / in）=> LEFT SEMI JOIN

过滤掉test1的数据

select test_1.id, test_1.num from test_1 left semi join test_2 on (test_1.id = test_2.id);


差集（exist / in）=> LEFT OUTER JOIN .. is NULL

A差B的情况

select test_1.id, test_1.num from test_1 left outer join test_2 on (test_2.id = test_2.id) where test_2.num is null;