入门级，Hadoop详情教程（二）(hive)

hive概念

基于Hadoop得数据仓库解决方案

hive的特点和优势

1、将结构化的数据库文件映射为数据库表
2、提供类sql得查询语言HQL（sql代替mapreduce）
3、让更多的人更容易使用hadoop
4、可以整合更多的计算框架
mapreduce（基于磁盘中间结果存于磁盘）
spark（基于内存，减少IO，DAG计算模减少SHUFFLE）
tez（也有DAG和container重用，但是部署繁琐）

链的概念

安全，可追溯，不可篡改
全网公开：无用户授权机制的区块链成为共有链
信息共享：共有链指全世界任何人都可以读取，发送交易，且交易能获得有效确认的，也可以参与其中共识过程的区块链
区块链：允许授权加入，根据索授权限查看信息，多用于机构之间的区块链，成为联盟链或行业链
信息共享：区块链节点数据同步且不可篡改
区块链技术：非对称加密，分片技术，DAG（有向无环图）
私有链：所有网络中的节点都掌握在一家机构手中，
信息共享：由某数据中心构建，指定范围共享
注意：区块链和私有链成为许可链，公有链称为非许可链
区块链与公有链的区别：
区块链：图多入多出
公有链：单入单出
局部处理+并行计算
扩展性强：各节点独立运算，无需等待。多进多出，要修改一个节点，所有进出度都要修改
链式结构：单节点计算能力相似，但不能并行计算，浪费等待时间，且每个交易和确认都需要同步
支持在HDFS和HBase上临时查询数据
支持用户自定义函数、格式
成熟的JDBC和ODBC驱动程序，用于ETL和BI
成熟可靠（真实生产环境）的批处理
有庞大活跃的社区

hive的体系

User Interface:
		ClI:Hive shell
		Java:JDBC
		Browser:WebUI
MataData
		default derby,recommend mysql
CLI(beeline)/JAVA JDBC
=>Driver
=>SQL Parser -> sql cmd 翻译成AST抽象语法树 -> 三方工具antlr对AST进行语法分析：表，字段，语法语义是否存在
=>Query Opitimizer -> 优化逻辑执行计划
=>Physical Plan ->将AST转换成逻辑执行计划
=>Execution -> 转换逻辑执行计划为可执行物理执行计划
Hadoop
Hive => MapReduce/Spark/Tez => HDFS

hive的数据类型

简单类型

数据类型	描述	示例
TINYINT	1byte 有符号整数	20
SMALINT	2byte 有符号整数	20
INT	4byte 有符号整数	20
BIGINT	8byte 有符号整数	20
BOOLEAN	布尔型true/false	TRUE/FALSE
FLOAT	单精度浮点数	3.14159
DOUBLE	双精度浮点数	3.14159
STRING	字符系列/可指定字符集(可用单/双引号)	‘now is the time’ “for all good men”
TIMESTAMP	时间类型	‘2020-01-31 00：13：00.345’
BINARY	字节数组

集合类型

数据类型	描述	示例
STRUCT	like C	stu struct<name:struct< first:STRING,last:STRING>,age:STRING> stu.age/stu.name.first
ARRAY	key value pair	score map<STRING,INT> ‘html’->80 ‘mysql’->88 score[‘html’]
MAP	array	hobby array [‘swimming’,‘reading’,‘travelling’] hobby[0]

hive操作命令

show databases; 查看所有库
use database default; 使用库
describe database default; 库结构
create database if not exists data_name; 建库
drop database if not exists data_name [cascade]; 删库，非空数据库加cascade强制删除
select current_database(); 查看当前所在库
hive的表结构存储在Mysql里

select * from DBS;
select * from TBLS;
select * from COLUMNS_V2;

数据表
内部表 表结构和数据都在内部，核心数据
分区表 分时日志（每天/每小时）等，便于分时统计分析
外部表 表结构在内部，数据在hdfs文件中，不可增删改，共享数据
建表，对数据处理的手段
row format delimited fields terminated by '\t|-'行中列分隔符：默认是'\t' ，.csv文件是xls
collection items terminated by ',' 指定集合数据分隔符 array map struct
map keys terminated by ':' 指定map分隔符
lines terminated by '\n'指定行结束符：默认是’\n’
location '/opt/bigdata/hadoop/hive110/warehouse/student';文件存储位置，默认hive.metastore.warehouse.dir配置位置
建内部表
例如：

drop table shop
create table shop(
shopid BIGINT,
shopname STRING,
running BOOLEAN,
contact ARRAY<STRING>,
address STRUCT<province:STRING,city:STRING,district:STRING,detail:STRING>,
yearlyMoney MAP<STRING,FLOAT>
)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
location '/opt/bigdata/hadoop/hive110/warehouse/kb08/shop'

数据演示：
1|紫燕百味鸡|TRUE|18014499655,02587675943|江苏,南京,雨花,小行路288号|2018:1200000.00,2019:1560000.00,2020:1000000.00
从linux中读取数据
load data local inpath '/root/kb08/hive/shop.log' into table shop
从hdfs下拿文件
load data inpath '/root/kb08/hive/shop.log' into table shop
表数据扁平化

select
shopid,
shopname,
running,
contact[0] mobile,
contact[1] fix,
address.province province,
address.city city,
address.district district,
address.detail detail,
yearlyMoney["2018"] money2018,
yearlyMoney["2019"] money2019,
yearlyMoney["2020"] money2020
from shop;

+---------+-----------+----------+--------------+--------------+-----------+-------+-----------+----------+------------+------------+------------+--+
| shopid  | shopname  | running  |    mobile    |     fix      | province  | city  | district  |  detail  | money2018  | money2019  | money2020  |
+---------+-----------+----------+--------------+--------------+-----------+-------+-----------+----------+------------+------------+------------+--+
| 1       | 紫燕百味鸡     | true     | 18014499655  | 02587675943  | 江苏        | 南京    | 雨花        | 小行路288号  | 1200000.0  | 1560000.0  | 1000000.0  |
| 2       | 如家快捷酒店    | true     | 18234399344  | 02538790604  | 江苏        | 南京    | 雨花        | 小行路28号   | 1382500.0  | 1990000.0  | 1259800.0  |
| 3       | 苏果超市      | true     | 13901456655  | 02581212943  | 江苏        | 南京    | 雨花        | 小行路06号   | 880000.0   | 987000.0   | 1123000.0  |
| 4       | 罗森        | true     | 12987237655  | 02123676663  | 江苏        | 南京    | 雨花        | 小行路08号   | 1200000.0  | 1560000.0  | 1000000.0  |
| 5       | 便利蜂       | true     | 12014488055  | 02327895123  | 江苏        | 南京    | 雨花        | 小行路16号   | 560000.0   | 590000.0   | 876500.0   |
+---------+-----------+----------+--------------+--------------+-----------+-------+-----------+----------+------------+------------+------------+--+

hive表

建表
create table TABLE_NAME

create table TABLE_NAME as
with
r1 as (select ... from r2 where ...),
r2 as (select ...),
r3 as (select ...)
select * from r1 union all select * from r2;

分页查询

select * from
(select row_number() over(order by yearlyMoney['2018']) as rn,T.* from shop T)t
where t.rn between 1 and 3;

建外部表
create external table TABLE_NAME
建分区表

create external table name_part(
字段1,
字段2
)
partitioned by (字段3,字段4);

分区字段不要是中文
设置动态分区
set hive.exec.dynamic.partition=true;
查看
set hive.exec.dynamic.partition;
分区表插入数据
insert into table name_partpartition(字段3=值,字段4=值) select 字段1,字段2 from 原表 where 字段3=值,字段4=值（可做数据处理）;
分桶查询
类似于MapReduce的HashPartitioner: key.hashcode()%numberReducerTasks
分桶是以分桶字段的Hashcode%numberBuckets
临时设置hive分桶开关
set hive.enforce.bucketing=true;
查看
set hive.enforce.bucketing;
分桶抽样
tablesample(bucket bucketNo out of bucketNum)
bucketNo：表示从哪个桶开始抽样
bucketNum：表示抽几个桶，抽样比列bucketNum/bucketTotal:
如果不清楚bucketTotal：desc formatted DEST_TABLE;

hive基本查询

select [all | distinct ] SEL_EXP...
from TABLE_NAME
where CONDITION
group by GROUP_EXP...
having GROUP_CONDITION
order/sort/cluster/distribute by COL_LIST
limit NUM

hive排序

一、order by 全局排序：hive的sql中制定了order by所有的数据都会到同一个reducer进行处理
注意：
当开启MR严格模式（set hive.mapred.mode=strict;）的时候，order by 必须要设置limit 子句，否则报错，对于分区表，必须要对分区字段加限制条件，否则会报错

二、sort by部分排序，只对每个reducer中的字段排序，不能保证所有的数据是有序的，除非只有一个reducer，可指定执行的reducer个数（set mapreduce.jab.reduces=<number>），对输出的数据再执行归并排序，即可得到全部结果

三、distribute by 控制map输出在reducer中的分布，相同的key划分到同一个Reduce中

四、cluster bydistribute by和sort by相结合，cluster by指定的列只能是降序，不能指定asc和desc，distribute by要在sort by之前

hql侧视图

Lateral View 和 UDTF 类功能函数一起使用，扩展数据表，可用group by/order by子句，无需套子查询

行列转换

explode行转列
concat_ws列转行