Hive基础

Hive的基本概念

hive是基于hadoop的数据仓库解决方案，是为了推广hadoop而产生，它可以将结构化的数据映射为数据表。
Hive的优势与特点

Hive客户端操作

有两种客户端工具：Beeline和Hive命令行（CLI）
有两种模式：

• 命令行模式
  
• 交互模式
  

Hive数据类型

#数据类型
	#数据类型	java					mysql							hive
	#字符串		String					char(n)/varchar(n)/text/...		string/varchar(65536)/char(255)
	#字符		char				
	#整数		byte/short/int/long		smallint/int(n)/bigint(n)		smallint/int/bigint
	#小数		float/double/BigDecial	float/double/money/real			float/double
										numeric(m,n)/decimal(m,n)		decimal(38,18)
	#布尔		boolean					bit								boolean
	#日期		java.util.Date			date/datetime/timestamp			date/timestamp
	#列表		HashSet					set('V1','V2','V3',...)			array<data_type>
	#结构体																struct<col_name:data_type,...>
	#键值																map<key_type,value_type>
	#符合类型															uniontype<data_type,...>

Hive数据库和表操作

• Hive数据结构

数据结构	描述	逻辑关系				物理存储（HDFS)
Database	数据库	表的集合				文件夹
Table		表		行数据的集合			文件夹
Partition	分区	用于分割数据			文件夹
Buckets		分桶	用于分布数据			文件
Row			行		行记录					文件中的行
columns		列		列记录					每行中指定的位置
Views		视图	逻辑概念,可跨越多张表	不存储数据
lndex		索引	记录统计数据信息		文件夹

• 数据库

create database if not exists myhivebook; --创建数据库myhivebook
use myhivebook;	--进入数据库myhivebook
show databases;	--查看数据库
describe database default;  -- 可以查看数据库更多的描述信息
alter database myhivebook set owner user dayongd;	--修改数据库的使用者
drop database if exists myhivebook cascade;	--删除数据库myhivebook;

• 建表语言解析

create external table if not exists employee_external (	--if not exists可选,如果表存在，则忽略
name string,
work_place array<string>,
sex_age struct<sex:string,age:int>,						--列出所有列和数据类型
skills_score map<string,int>,
depart_title map<string,array<string>>
)
comment 'This is an external table'						--comment可选
row format delimited
fields terminated by '|'								--如何分隔列(字段)
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile										--文件存储格式
location '/kb10/employee'; 								--指定数据存储路径（HDFS)

hdfs dfs -put xxxxxx.log /kb10/employee					--上传数据

• 分隔符

     在使用hive的时候，分隔符是必不可少的，当学习的时候使用的都是常规分隔符，比如：逗号“,”、竖线“|”等，这些
     键盘上都可以直接输入的字符，但是这些字符只要是键盘上的，在针对复杂的业务逻辑的时候，都会失效。比如你
     有一个备注字段，这个字段允许用户输入输入键盘上的任何字符，一旦用户输入了你选择的分隔符，那么Hive在使
     用这个数据的时候，就会导致hive表中的字段错位。使用多个组合字符，也可以成为一种选择，但是有些导入导出
     工具，不支持多字符分隔符，比如：阿里的DATAX就不支持多字符分隔符。那么现在我们就需要一种字符，是用户
     输入不了的，计算机又存在的字符，下面这些字符则可以满足需求！
  
    hive中默认分隔符
    字段：^A(\001)
    集合：^B(\002)
    映射：^C(\003)
    在hive中建表时可以指定分割符
    row format delimited fields terminated by '|'		-- 指定列分隔符语法
  

• 内部表

#内部表
create table if not exists clientinfo(
clientName struct<first:string,last:string>,
age int,
hobbies array<string>,
address struct<provice:string,city:string,district:string>,
detailAdd string,
deliveryAdd map<string,string>
)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile --文件存储格式
;
--在本地 
--cd /opt
--vi touch clientinfo.log	--粘贴内容
henry,chen|22|reading,swimming|jiangsu,nanjing,yuhua|xiaohang road #16|
home:gulou zhongshan south road #226,job:yuhua xiaohang road #16
pola,ma|18|reading,climbing|jiangsu,nanjing,yuhua|xiaohang road #16|
home:gulou zhongshan south road #226,job:yuhua xiaohang road #16
arial,chen|12|sleeping,swimming|jiangsu,nanjing,yuhua|xiaohang road #
16|home:gulou zhongshan south road #226,job:yuhua xiaohang road #16
john,li|25|smoking,swimming|jiangsu,nanjing,yuhua|xiaohang road #16
|home:gulou zhongshan south road #226,job:yuhua xiaohang road #16
benshan,zhao|78|debting,swimming|jiangsu,nanjing,yuhua|xiaohang road #16
|home:gulou zhongshan south road #226,job:yuhua xiaohang road #16

load data local inpath '/opt/clientinfo.log' into table clientinfo;

• 外部表

#外部表
create external table shop(
shopId int,
shopName string,
online boolean,
contact struct<mobile:string,fixed:string>,
address array<string>,
volumn map<string,decimal>
)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':';

select 
shopId,
shopName,
if(online,'营业中','未营业') status,
contact.mobile mobile,
contact.fixed fixed,
address[0] provice,
address[1] city,
address[2] district,
address[3] details,
volumn['2018'] 2018_volumn,
volumn['2019'] 2019_volumn,
volumn['2020'] 2020_volumn
from shop;
--在hdfs
--vi shopinfo.log	--粘贴内容
--hdfs dfs -put shopinfo.log /opt/software/hadoop/hive110/warehouse

1|紫燕百味鸡|TRUE|18014499655,02587675943|江苏,南京,雨花,小行路288号|2018:1200000.00,2019:1560000.00,2020:1000000.00
2|如家快捷酒店|TRUE|13987499234,02587076234|江苏,南京,雨花,小行路28号|2018:1382500.00,2019:1990000.00,2020:1259800.00
3|苏果超市|TRUE|13814987651,02587675256|江苏,南京,雨花,小行路06号|2018:880000.00,2019:987000.00,2020:1123000.00
4|罗森|TRUE|14766848644,02587675543|江苏,南京,雨花,小行路08号|2018:1200000.00,2019:1560000.00,2020:1000000.

load data inpath '/opt/software/hadoop/hive110/warehouse/shopinfo.log' [overwrite](覆盖)into table shop;

添加数据 ：
load data [local] inpath ‘文件所处的路径’ into [overwriter] table A;
有local：从linux下拷贝数据，
无local：从hdfs上剪切数据到hive的指定位置下。
有overwrite：覆盖原有的数据
无overwrite：追加

• 临时表

create temporary table if not exists mysql_school_tmp as 
select distinct proname,classname from mysql_school;

• Hive建表与不同类型的Storage SerDe

create table my_table(a string, b string, ...)row format serde 
'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties(
   "separatorChar" = "\t",
   "quoteChar"     = "'",
   "escapeChar"    = "\\"
)  
stored as textfile;

Storage SerDe:
LazySimpleSerDe: TEXTFILE
BinarySerializerDeserializer:  SEQUENCEFILE
ColumnarSerDe: ORC, RCFILE
ParquetHiveSerDe: PARQUET
AvroSerDe: AVRO
OpenCSVSerDe: for CST/TSV
JSONSerDe
RegExSerDe
HBaseSerDe 

• 建表高阶语句 ctas -with

CREATE TABLE yyyy AS
WITH 
r1 AS  (SELECT xxx FROM r2),
r2 AS  (SELECT xxx FROM xxxx ),
r3 AS  (SELECT xxx FROM xxxx )
SELECT * FROM r1 UNION ALL SELECT * FROM r3;

r1,r2,r3分别为查询结果生成的表，没有优先级

• 表删除操作

  truncate table shop; – 清空表数据
  alter table shop rename to shop1;
  drop table if exists shop1;

Hive数据分区

• 静态分区

--静态分区：若分区的值是确定的，那么称为静态分区。新增分区或者是加载分区数据时，已经指定分区名。
--create table dynamic_people(
--id int,
--name string,
--age int,
--start_date date
--)
--partitioned by (year string,month string)
--row format delimited
--fields terminated by ',';
--加载数据指定分区
load data local inpath 'xxx.txt' into table dynamic_people partition(year=2017,month=10);
--新增分区指定分区名
alter table dynamic_people add partition(year=2017,month=1)
partition(year=2019,month=03);

--准备数据 people.txt
001,tom,23,2019-03-16
002,jack,12,2019-03-13
003,robin,14,2018-08-13
004,justin,34,2018-10-12
005,jarry,24,2017-11-11
006,jasper,24,2017-12-12

• 动态分区

    动态分区在默认情况下是禁用的(在 hive2.3.4 版 本后默认是开启的)，所以需要将 hive.exec.dynamic.partition 设为 true。默认情况
    下，用户必须至少指定一个静态分区列，这是为了避免意外覆盖分区。要禁用此
    限制，可以设置分区模式为非严格模式(即将 hive.exec.dynamic.partition.mode 设 为 nonstrict，默认值为 strict)。可以选择在命令行终端方式设置
  

--创建普通表
--create table people(id int,name string,age ----int,start_date date)
--row format delimited
--fields terminated by ',';
--加载数据
--load data local inpath '/opt/datas/people.txt' into table people;
--开启动态分区
--SET hive.exec.dynamic.partition=true;
--SET hive.exec.dynamic.partition.mode=nonstrict;
--创建分区表
--create table dynamic_people(
--id int,
--name string,
--age int,
--start_date date
--)
--partitioned by (year string,month string)
--row format delimited
--fields terminated by ',';
--加载数据
insert into dynamic_people
partition(year,month)
select 
id,
name,
age,
start_date,
year(start_date),
month(start_date)
from
people;

Hive数据分桶

• 数据分桶的适用场景：

    分区提供了一个隔离数据和优化查询的便利方式，不过并非所有的数据都可形成合理的分区，
    尤其是需要确定合适大小的分区划分方式，（不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬情况）
    试试分桶是将数据集分解为更容易管理的若干部分的另一种技术。
  

• 数据分桶的原理

    跟MR中的HashPartitioner的原理一模一样
        MR中：按照key的hash值去模除以reductTask的个数
        Hive中：按照分桶字段的hash值去模除以分桶的个数
    Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
  

• 数据分桶的作用:

    好处：
    1、方便抽样
    2、提高join查询效率
  

• 分桶表

--设置mapreduce的数量与分桶数量一致
set hive.enfore.bucketing = true;	
--此设置为临时设置，一旦退出会话终端，再打开就会恢复默认设置 false
-- 注意分桶字段只能是建表中已有的字段，
-- 而分区表的字段必须是建表中没有的字段
set mapreduce.reduce.tasks=num;

create external table emp(
	name string,
	id int,
	cities array<string>,
	info struct<gebder:string,age:int>,
	scores map<string,int>,
	position map<string,string>
)
clustered by (name) into 5 buckets
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':';
--插入数据到分桶表：
insert into table emp select * from empploy;

Hive视图

• 视图概述

    通过隐藏子查询、连接和函数来简化查询的逻辑结构
    只保存定义，不存储数据
    如果删除或更改基础表，则查询视图将失败
    视图是只读的，不能插入或装载数据
  

• 应用场景

    将特定的列提供给用户，保护数据隐私
    用于查询语句复杂的场景
  

• 视图操作命令

    - 创建视图，支持 CTE, ORDER BY, LIMIT, JOIN,等
    create view view_name as select statement
    create view view_name as select * from statement;
    - 查找视图 (show views在 hive v2.2.0之后)
    show tables 
    - 查看视图定义
    show create table view_name
    - 删除视图
    drop view_name
    drop view if exists view_name
    -更改视图属性
    alter view view_name set tblproperties ('comment' = 'This is a view')
    - 更改视图定义
    alter view view_name 
    - 查询
    select * from view_name 
  

• Hive侧视图
  与表生成函数结合使用，将函数的输入和输出连接

select name,wps,skill,score from employee 
lateral view explode(work_place) work_place_single as wps
lateral view explode(skills_score) sks as skill,score;