hive
- hive概念
- hive的优势和特点
- 数据库和数据仓库区别
- 安装zookeeper
- 安装hive
- 修改mysql登录密码
- 数据库
- 🌰
- 数据表
- hive建表语句
- 🌰
- hive和MapReduce
- hive元数据管理
- hive元数据结构
- 数据类型----复杂数据类型
- 数据分层
- hive建表-Storage SerDe
- hive建表高阶语句-CTAS and WITH
- 🌰
- 创建临时表
- 表操作-删除/修改表
- 删除表
- 修改表(alter针对元数据)
- hive分区(partitions)
- hive分区操作-定义分区
- 静态分区操作
- 动态分区操作
- 分桶
- hive侧视图
- 🌰
hive概念
- 基于hadoop的数据仓库解决方案
将结构化的数据文件映射为数据库表
提供类sql的查询语言HQL(Hive Query Language)
Hive让更多的人使用Hadoop - Hive成为Apache顶级项目
Hive始于2007年的Facebook
官网:hive.apache.org
hive的优势和特点
- 提供了一个简单的优化模型
HQL类SQL语法,简化MR开发
支持在不同的计算框架上运行
支持在HDFS和HBase上临时查询数据
支持用户自定义函数、格式
成熟的JDBC和ODBC驱动程序,用于ETL和BI
稳定可靠(真实生产环境)的批处理
有庞大活跃的社区
数据库和数据仓库区别
数据库是为某种目的将结构化的数据放在一起的,放在自身database里面,主要用于事务处理
数据仓库用于OLAP(On-Line Analytical Processing),支持复杂的分析操作,存储结构化、非结构化、半结构化数据。不放在自身,数据放在hdfs。
安装zookeeper
####### 安装zookeeper #######
前置条件安装完hadoop集群
下载zoopkeeper稳定版,拖进linux里的/opt目录下
tar -zxf zookeeper-3.4.5-cdh5.14.2.tar.gz
mv zookeeper-3.4.5-cdh5.14.2 soft/zk345
cp zoo_sample.cfg zoo.cfg
修改zoo.cfg
vi zoo.cfg
dataDir=/opt/soft/zk345/tmp
如果是完全分布式还需要在其他机器上布置zookeeper并在此配置文件下根据建立例如server.0=xxx:2287:3387 多台之间端口可以重复
配置环境变量
a)Export ZOOKEEPER_HOME=/zookeeper解压路径
b)Export PATH=……:$ZOOKEEPER_HOME/bin
启动zookeeper
zkServer.sh start
安装hive
######### 安装hive #########
tar -zxf hive-1.1.0-cdh5.14.2.tar.gz
mv zookeeper-3.4.5-cdh5.14.2 soft/zk345
修改hive-site.xml
配置环境变量
a)Export HIVE_HOME=/hive解压路径
b)Export PATH=……:$HIVE_HOME/bin
schematool -dbType mysql -initSchema 初始化hive
hive --service metastore 启动hive
hiveserver2 启动beeline
beeline -u jdbc:hive2://主机地址:10000 -n root 进入beeline
hive 进入hive
!quit 退出beeline
修改mysql登录密码
修改mysql登陆密码:
set password for 'root'@'localhost'=password('1234'); 给数据库mysql设置密码
grant all privileges on *.* to 'root' @'%' identified by '1234'; 授予远程权限
flush privileges; 刷新权限
show variables like '%character%'; 设置编码格式
数据库
表的集合,hdfs中表现为一个文件夹
默认在hive.metastore.warehouse.dir属性目录下
如果没有指定数据库,默认使用default数据库
create database if not exists myhivebook;
use myhivebook;
show databases;
// 比show显示更多的细节,例如定位
describe database default;
// 改变数据库的主人
alter database myhivebook set owner user dayongd;
// cascade说明把该数据库包括表全部删除
drop database if exists myhivebook cascade;
🌰
// 插入数据
create database mydemo;
use mydemo;
create table userinfos(userid int, username string, age int);
insert into userinfos values(1,'zs',21),(2,'ls',22),(3,'ww',23);
// 查看数据
!hdfs dfs -text /usr/hive/warehouse/mydemo.db/userinfos/...
- 用完先删表,再删库,再删元数据里面的表
hive> drop table userinfos;
hive> drop database mydemo;
hive> drop table mydemo;
数据表
- 分为内部表和外部表
- 内部表(管理表)
hdfs中所属数据库目录下的子文件夹
数据完全由hive管理,删除表(元数据)会删除数据 - 外部表(External Tables)
数据保存在指定位置的hdfs路径中
hive不完全管理数据,删除表(元数据)不会删除数据
hive建表语句
// IF NOT EXISTS可选,如果表存在,则忽略
CREATE EXTERNAL TABLE IF NOT EXISTS employee_external(
// 列出所有列和数据类型
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可选
COMMENT 'This is an external table'
ROW FORMAT DELIMITED
// 如何分割列(字段)
FIELDS TERMINATED BY '|'
// 如何分割集合和映射
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
// 文件存储格式
STORED AS TEXTFILE
// 数据存储路径(HDFS)
LOCATION '/user/root/employee';
🌰
// 建内部表
create table class(class_id int, class_name string)
row format delimited
fields terminated by ','
stored as textfile;
插入数据:
insert into class values(1,'kb03'),(2,'kb06');
// 建外部表
create external table student(
stu_id int,
stu_name string,
age int
)
row format delimited fields terminated by ','
location '/data';
插入数据:
在外部建立数据文件d1.csv,拖进/opt,
hdfs dfs -put d1.csv /data/
hive和MapReduce
MapReduce执行效率更快,hive开发效率更快
hive元数据管理
- 记录数据仓库中模型的定义、各层级间的映射关系
- 存储在关系数据库中
- 默认Derby,轻量级内嵌SQL数据库
Derby非常适合测试和演示
存储在.metastore_db目录中 - 实际生产一般存储在MySql中
修改配置文件hive-site.xml
- HCatalog
将hive元数据共享给其他应用程序
hive元数据结构
数据结构 | 描述 | 逻辑关系 | 物理存储(hdfs) |
database | 数据库 | 表的集合 | 文件夹 |
table | 表 | 行数据的集合 | 文件夹 |
partition | 分区 | 用于分割数据 | 文件夹 |
buckets | 分桶 | 用于分布数据 | 文件 |
row | 行 | 行记录 | 文件中的行 |
columns | 列 | 列记录 | 每行中指定的位置 |
views | 视图 | 逻辑概念,可跨越多张表 | 不存储数据 |
index | 索引 | 记录统计数据信息 | 文件夹 |
数据类型----复杂数据类型
ARRAY:存储的数据为想同类型
MAP:具有相同类型的键值对
STRUCT:封装了一组字段
类型 | 格式 | 定义 | 示例 |
ARRAY | [‘Apple’,‘Orange’,‘Mongo’] | ARRAY<string.> | a[0] = ‘Apple’ |
MAP | {‘A’:‘Apple’,‘O’:‘Orange’} | MAP<string,string> | b[‘A’] = ‘Apple’ |
STRUCT | {‘Apple’,2} | STRUCT<fruit:string,weight:int> | c.weight = 2 |
数据分层
- ODS 层
原始数据层,存放原始数据,直接加载原始日志、数据,数据保持原貌不做处理。 - DWD层
明细数据层
结构和粒度与ods层保持一致,对ods层数据进行清洗(去除空值,脏数据,超过极限范围的数据),也有公司叫dwi。 - DWS层
服务数据层
以dwd为基础,进行轻度汇总。一般聚集到以用户当日,设备当日,商家当日,商品当日等等的粒度。
在这层通常会有以某一个维度为线索,组成跨主题的宽表,比如 一个用户的当日的签到数、收藏数、评论数、抽奖数、订阅数、点赞数、浏览商品数、添加购物车数、下单数、支付数、退款数、点击广告数组成的多列表。 - ADS层
数据应用层, 也有公司或书把这层成为app层、dal层、dm层,叫法繁多。
面向实际的数据需求,以DWD或者DWS层的数据为基础,组成的各种统计报表。
统计结果最终同步到RDS以供BI或应用系统查询使用。
hive建表-Storage SerDe
Hive支持不同类型的Storage SerDe
LazySimpleSerDe: TEXTFILE
BinarySerializerDeserializer: SEQUENCEFILE
ColumnarSerDe: ORC, RCFILE
ParquetHiveSerDe: PARQUET
AvroSerDe: AVRO
OpenCSVSerDe: for CST/TSV
JSONSerDe
RegExSerDe
HBaseSerDe
hive建表高阶语句-CTAS and WITH
CTAS - as select方式建表
CTAS不能创建partition,external,bucket table
CREATE TABLE ctas_employee as SELECT * FROM employee;
CTE(CTAS with Common Table Expression)
CREATE TABLE cte_employee AS
WITH
r1 AS (SELECT name FROM r2 WHERE name = 'Michael'),
r2 AS (SELECT name FROM employee WHERE sex_age.sex= 'Male'),
r3 AS (SELECT name FROM employee WHERE sex_age.sex= 'Female')
SELECT * FROM r1 UNION ALL SELECT * FROM r3;
Like
CREATE TABLE employee_like LIKE employee;
🌰
查询userinfos表里的每个学生的分数在每个班总分的占比
select r.username,r.classname,r.score,(r.score/l.countScore*100) rate
from (select classname,sum(score) countScore from scores group by classname) l
inner join
(select u.*,s.classname,s.score from userinfos u inner join scores s on u.userid=s.userid) r
on l.classname=r.classname
with
a1 as (select classname,sum(score) countScore from scores group by classname),
a2 as (select u.*,s.classname,s.score from userinfos u inner join scores s on u.userid=s.userid)
select a2.username,a2.classname,a2.score,(a2.score/a1.countScore*100) from a1 inner join a2 on a1.classname=a2.classname
创建临时表
临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法
表只对当前session有效,session退出后自动删除
表空间位于/tmp/hive-<user_name>(安全考虑)
如果创建的临时表表名已经存在,实际用的是临时表
CREATE TEMPORARY TABLE tmp_table_name1 (c1 string);
CREATE TEMPORARY TABLE tmp_table_name2 AS..
CREATE TEMPORARY TABLE tmp_table_name3 LIKE..
表操作-删除/修改表
删除表
// with perge直接删除(可选),否则会放到.Trash目录
drop table if exists employee [with perge];
// 清空表数据
truncate table employee;
修改表(alter针对元数据)
// 修改表名,常用于数据备份
alter table 表名 rename to 新表名;
// 修正表文件格式
alter table 表名 set fileformat rcfile;
// 修改列名
alter table 表名 change 旧表名 新表名 数据类型;
// 添加列
alter table 表名 add columns(列名 数据类型);
// 删除列
alter table 表名 drop [column] 列名
// 替换列
alter table 表名 replace columns (col_spec[,col_spec...])
hive分区(partitions)
- 分区主要用于提高性能
分区列的值将表划分为segments(文件夹)
查询时使用“分区”列和常规列类似
查询时hive自动过滤掉不用于提高性能的分区 - 分为静态分区和动态分区
- // 默认分区数量最大为100个,修改分区数量方法:
set hive.exec.max.dynamic.partitions.pernode=100000;
set hive.exec.max.dynamic.partitions=1000000;
set hive.exec.max.created.files=1000000;
hive分区操作-定义分区
静态分区操作
手动输入区名和区的数量
// 定义分区
create table mypart(mpid int, mapname string)
partitioned by(gender string)
row format delimited fields terminated by ',';
// 分区插入数据
create table userinfos(userid int, username string, age int, gender string);
insert into userinfos values(1,'zs',21,'male'),(2,'ls',22,'male'),(3,'ww',23,'female');
insert overwrite into mypart partition(gender='male')
select userid,username from userinfos;
// 静态分区
alter table mypart add partition(gender='male') partition(gender='female');
// 删除分区
alter table mypart drop partition(gender='male');
动态分区操作
// 设置属性
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
// 向分区插入数据
insert into table mypart partition(gender)
select userid,username,gender from userinfos;
// 向userinfos表添加列birthday
alter table userinfos add columns (birthday date);
// 创建userinfos表
create table userinfos(
userid int,
username string,
age int,
gender string,
birthday date
)
partitioned by (year int, month int)
row format delimited fields terminated by ',';
create table customs(
userid int,
username string,
age int,
gender string,
birthday string
)
row format delimited fields terminated by ',';
// 导入外部数据,把下图的表拖入linux的/opt目录下
load data local inpath '/opt/data.csv' overwrite into table mydemo.customs;
insert overwrite table userinfos partition(year,month)
select userid,username,age,gender,regexp_replace(birthday,'/','-'),split(birthday,'/')[0] as year,split(birthday,'/')[1] from customs as month;
分桶
- 分桶对应于hdfs中的文件
更高的查询处理效率
使抽样更高效
根据“桶列”的哈希函数将数据分桶 - 分桶只有动态分桶
set hive.enforce.bucketing=true; - 定义分桶,分桶的列是表中已有的列,分桶数是最好是2的n次方
clustered by (employee_id) into 2 buckets - 必须使用insert方式加载数据
create table mycust(
> userid int,
> username string,
> age int,
> gender string,
> bir string
> )
> clustered by(gender) into 2 buckets
> row format delimited fields terminated by ',';
load data local inpath '/opt/mydata.csv' into table mycust;
set hive.enforce.bucketing = true; 开启桶表
hive侧视图
常与表生成函数结合使用,将函数的输入和输出连接
outer关键字:即使output为空也会生成结果
select name,work_place,loc from employee lateral view outer explode(split(null,',')) a as loc;
支持多层级
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;
通常用于规范化行或解析json
// explode
select explode(split('hello,world',','));
// 行转列
select userid,
> sum(if(subject='chinese',score,0)) as chinese,
> sum(if(subject='math',score,0)) as math
> from tb_score
> group by userid;
// 列转行
union会去重,union all不会
select userid,'chinese' as subject,chinese as score from view_sc
> union all
> select userid,'math' as subject,math as score from view_sc;
🌰
create table words(line string);
load data inpath '/opt/abc.txt' overwrite into table mydemo.words;
select word,count(*) as world_count
from words
laternal view explode(split(line,' ')) t as word
group by word;