【学习笔记】Hive

Hive是基于Hadoop的数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换成MapReduce任务进行运行。

DDL是SQL语言集中对数据库内部的对象结构进行创建、删除和修改等操作语言，数据库对象包括database、table等。DDL的核心语法有CREATE、DROp和ALTER所组成。DDL不涉及表内部数据操作。

CREATE DATAbase 数据库名
[COMMENT 注解]
[LOCATION hdfs_路径]
[WITH DBPROPERTIES (property_name=属性配置, ...)]

选择特定的数据库

USE 数据库名

删除数据库，默认RESTRICT仅在数据库为空时才删除，CASCADE能删除带表的数据库

DROP DATAbase 数据库名 [RESTRICT|CASCADE]

每个表有一个名字标识，表包含带有数据的记录

CREATE TABLE 表名 (字段名 数据类型 [COMMENT 注解], ...)
[COMMENT 注解]
[ROW FORMAT DELIMITED 
   fields terminated by 分隔符           --字段之间的分隔符
   collection items terminated by 分隔符 --集合元素之间的分隔符
   map keys terminated by 分隔符         --Map映射kv之间的分隔符
   lines terminated by 分隔符            --行数据之间的分隔符]

数据类型分为原生数据类型和复杂数据类型
原生数据类型有：

整型：tinyint、smallint、int、bigint浮点型：float、double布尔：boolean字符串：string时间戳：timestamp
复杂数据类型有：array<类型>map<类型,类型>struct<属性名1:类型,属性名2:类型,…> 显示数据库信息

SHOW DATAbaseS           --显示所有数据库
SHOW SCHEMAS

SHOW TABLES [IN 数据库名] --显示当前数据库所有表

DESC FORMATTED 表名 --查询显示一张表的元数据信息

LOAD DATA [LOCAL] INPATH 路径 [OVERWRITE] INTO TABLE 表名

INSERT INTO TABLE 表名 VALUES (值1, 值2, ...)

INSERT INTO TABLE 表名 
 SELECT select_statement FROM from_statement 

执行顺序：from > where > group > having > order > select

SELECT [ALL|DISTINCT] 字段名1,字段名2,... FROM 表名 
 [WHERe 布尔表达式]
 [GROUP BY 字段名]
 [ORDER BY 字段名]
 [LIMIT [偏移量,] 行数]

默认值ALL表示不进行去重，DISTINCT会进行数据去重

WHERe 后面的布尔表达式支持：

比较运算：=，>，<，>=，<=，!=，<>（不等于）逻辑运算：and，or空值判断：xxx is nullbetween：xxx between v1 and v2in：xxx in (v1, v2, v3)

聚合操作

AVG(column)：某列的平均值COUNT(column)：某列的行数COUNT(*)：被选行数MAX(column)：某列的最高值MIN(column)：某列的最低值SUM(column)：某列的总和

SELECt COUNT([DISTINCT] field_name) AS name_cnt FROM table_name  --as 可以给返回数据字段起别名

分组 使用group by 所查找出的字段 必须是group by 后面的字段名 或者 被聚合函数应用的字段

SELECt field_name2, COUNT([DISTINCT] field_name1) AS name_cnt FROM table_name 
 WHERe 布尔条件 
 GROUP BY field_name2

HAVINg过滤，WHERe关键字无法与聚合函数一起使用，HAVINg子句可以筛选分组后的各组数据。

SELECt field_name2, COUNT([DISTINCT] field_name1) AS name_cnt FROM table_name 
 WHERe 布尔条件 
 GROUP BY field_name2
 HAVINg name_cnt > 0

ORDER BY 控制返回结果升序或降序，默认升序

SELECt * FROM table_name ORDER BY field_name [ASC|DESC]

LIMIT 限制SELECt语句返回的行数，接受一个或两个数字参数，必须为非负整数常量

SELECT * FROM table_name LIMIT [offset,]line_num

最常用的两种join分别是inner join（内连接）和left join（左连接）

table1 [INNER] JOIN table2 [CONDITION]

table1 {LEFT} [OUTER] JOIN table2 ConDITION

内连接只有进行连接的两个表都存在与连接条件相匹配的数据才会保留

SELECt t1.a,t1.b,t2.c
FROM table1 t1 INNER JOIN table2 t2
ON t1.a = t2.a

左连接在连接时以左表的全部数据为准，右表与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null。

SELECt t1.a,t1.b,t2.c
FROM table1 t1 LEFT OUTER JOIN table2 t2
ON t1.a = t2.a

Hive函数分为两大类：内置函数和用户定义函数UDF 官方教程

内置函数主要分为：

数学函数：round()、rand()日期函数：current_date()、unix_timestamp()、from_unixtime()、datediff()、date_add()、date_sub()字符串函数：length()、reverse()、concat()、concat_ws()、substr()、split()集合函数：size(Map)、map_keys(Map)、array_contains(Array, value)、sort_array(Array)条件函数：if()、nvl()、case 字段 when 条件1 then 值1 [when 条件2 then 值2 …] [else 值n] end类型转换函数数据脱敏函数其他杂项函数 用户定义函数UDF

用户定义函数根据输入输出行数分为：

UDF：普通函数，一进一出UDAF：聚合函数，多进一出UDTF：表生成函数，一进多出 使用UDF

--添加jar包到hive中
add jar hdfs:///apps/resource/udf/udf_jar/master/platform/bigdata-hive-udfs/1.6/bigdata-hive-udfs-1.6-jar-with-dependencies.jar; 
--创建临时UDF
CREATE TEMPORARY FUNCTION func_name as 'com.pinduoduo.bigdata.dwarch.udf.MaxPartition';

SELECt field_name
 FROM table_name
 WHERe field_name2=func_name(parameters)