Hive
Hive 概念
-
基于Hadoop的数据仓库解决方案
1.1 将结构化的数据文件映射为数据表
1.2 提供类sql的查询语言
1.3 hive让更多的人使用hadoop -
Hive为Apache顶级项目
1.1 Hive始于2007年的Facebook
1.2 官网:hive.apache.org
Hive 的优点和特点
- 入门简单 HQL类SQL语法
- 统一的元数据处理,可与impala/spark等共享元数据
- 灵活性和可扩展性较好:支持UDF,自定义储存格式等
- 支持在不同的计算框架上运行
- 提供了一个简单的优化模型
- 适合离线数据处理,稳定可靠
- 有庞大活跃的社区
Hive 元数据管理
- 记录数据仓库中模型的定义、各层级间的映射关系
- 存储在关系型数据库中
- HCatalog
- 将Hive元数据共享给其他应用程序
登录 beeline
beeline -u jdbc:hive2://localhost:10000
进入交互界面(ctrl+c 输入hive)执行后就退出
beeline> [root@sandbox-hdp ~]# hive -e "show databases"
数据类型
-
基本数据类型
-
复杂数据类型
Hive Meta Data Structure (元数据总览)
Data Structure | Logical | Physical (HDFS) |
---|---|---|
Database | A collection of tables | Folder with files |
Table | A collection of rows of data | Folder with files |
Partition | Columns to split data | Folder |
Buckets | Columns to distribute data | Files |
Row | Line of records | Line in a file |
Columns | Slice of records | Specified positions in each line |
Views | Shortcut of rows of data | n/a |
Index | Statistics of data | Folder with files |
hive database
- 数据库是用于类似目的或属于同一组的表的集合
- 如果未指定数据库(使用数据库名称),则默认使用默认数据库。
- Hive在 /user/ Hive /warehouse 为每个数据库创建一个目录,这个目录可以通过Hive.metastore.warehouse.warehouse.dir 默认数据库表直接建立在该目录下
面试题:如何知道和显示当前所在数据库
select current_database();
hive tables
External tables 外部表
Internal Tables/Managed Table 内部表/管理表
- 什么是内部表和外部表
内部表
(1)hdfs中为所属数据库目录下的子文件夹
(2)数据完全由Hive管理,删除表会删除数据
外部表
(1)数据保存在指定位置的hdfs路径中
(2)hive不完全管理数据,删除表不会删除数据
(3)临时表对当前session(会话)有效 session退出后自动删除 - 内部表和外部表的主要区别是什么?
(1) 数据完全由Hive管理,删除表会删除数据
(2)hive不完全管理数据,删除表不会删除数据 - 使用它们的最佳实践是什么?
(1)外部表:用于存放元数据,防止开发人员误操作,保护数据,外部表路灵活
(2)内部表:在数据清洗,hive许多功能对外部表不支持
hive 建表语句
CREATE EXTERNAL TABLE IF NOT EXISTS emp( --外部表
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 '这是一个外部表' --备注
row FORMAT delimited
fields terminated BY '|' --如何分割列(字段)
collection items terminated BY ',' --如何分割集合
map keys terminated BY ':' --如何分割映射
stored AS textfile --文件储存格式
location '/root/test/data'; --数据储存路径(HDFS)
--查看数据库更多描述信息
desc database database_name;
--修改数据库的所属用户为root
alter database database_name set owner user root;
--删除数据库的所有信息
drop database if exists database_name;
--关闭INFO日志
set hive.server2.logging.operation.level=NONE;
--开启IFNO日志
set hive.server2.logging.operation.level=EXECUTION;
显示当前使用的数据库
select current_databases();
查看表结构的详情
desc formatted 表名
显示建表语句
show create table 表名
上传数据
hdfs dfs -put 文件 表的location (文件在本地,location在fdfs目录下)
查看数组列的数据
select 列名 列名[下标] from 表名;
查看 struct 列的数据
select 列名.键1,列名.键2 form 表名
查看map 列数据
select 列名[键] from 表名
查看嵌套列 数据
select 列名 from 表名
select depart_title['Product'] form employee_external
select depart_title['Product'][0] from employee_external;
Hive 高阶语句
CTAS
CTAS不能创建partition(分区),external(外部),bucket(桶)表;
create table table_name1 as select * from table_name2;
CTE
create table cte_employee as with
r1 as (select name from employee where sex_age.sex='Male'),
r2 as (select name from r1 where name = 'Michael'),
r3 as (select name from employee where sex_age.sex='female')
select * from r2 union all select * from r3;
Like 复制表
create table table_name1 like table_name2;
Hive Temporary Table (临时表)
- 应用程序自动管理在复杂查询期间生成的中间数据的一种方便方法(类似于只有一条语句的CTE)。
- 只有会话,自动删除,相同的名字在不同的会话
- 表空间为/tmp/hive—<用户名>(安全考虑)
- 使用相同的名称时,临时表将隐藏永久表(临时表与外部表,内部表重名优先调用临时表 临时表表名加个tmp)
Hive table -drop/truncate/alter table
完全删除元数据,并将数据移动到HDFS中用户主目录的 .bash 文件夹(如果配置)。
与PERGE选项在最后,数据被完全删除。当删除外部表时,数据不会被删除。
drop table if exists employee;
移除所有数据行(在外部表上失败)。
TRUNCATE TABLE employee;
修改表名 经常用于数据修复前的暂时备份
alter table employee rename to new_employee;
设置表属性
ALTER TABLE c_employee SET TBLPROPERTIES ('comment'='New name,comments');
设置 SerDe 属性
ALTER TABLE employee_internal SET SERDEPROPERTIES('field.delim'= '$’ )
设置文件格式
ALTER TABLE c_employee SET FILEFORMAT RCFILE
Hive Table - Alter Table Columns
此语句可用于更改列名、位置或类型
ALTER TABLE employee_internal CHANGE old_name new_name STRING [BEFORE|AFTER] sex_age,
该语句在表的末尾添加另一列和类型
ALTER TABLE c_employee ADD COLUMNS (work string)
此语句按指定的列和类型替换表中的所有列 在本例中,ALTER之后,表中只有一列。
ALTER TABLE c_employee REPLACE COLUMNS(name string)
load 装载
load data local inpath '文件的绝对路径' overwrite into table table_name;
Partitions 分区
- hive 分区的作用?
1.1 缩小数据查询范围 - 有几种分区?
1.1 静态分区
1.2 动态分区 - 使用不同分区的场合和最佳实践?
3.1 静态:数据加载
3.2 动态:数据转换
静态分区
建表
create table dept(
dept_no int,dapt_name string,loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';
添加分区
alter table table_name add partition (month='201901') partition (month='201903');
删除分多个区
alter table 表名 drop if exists partition (year=2014,month=11),partition(....).....
删除多个分区
alter table table_name drop partition (month='201901') partition (month='201903');
向分区中加载数据
load data local inpath '文件的绝对路径' into table_name partition(month='201901');
查看分区表中的数据
select * from table_name where month='201901' ;
多分区联合查询
select * from table_name where month='201901'
union all
select * from table_name where month='201902'
union all
select * from table_name where month='201903'
创建多级分区
create table table_name(
name string,
work string
)
partition by (month string,day string......)
row format delimited fields terminated by '\t';
加载数据到分区中
load data local inpath '/root/text/partition/dept.txt' into table dept partition(month='201901');
动态分区
设置属性,只对当前会话有效
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
建表
create table people(
id int,name string,age int,start_date date
)
row format delimited
fields terminated by ',';
建立分区表
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 '/root/text/partition people.txt' into table people;
向分区表中动态插入数据
insert into dynamic_people
partition(year,month)
select id,name,age,start_date,year(start_date),month(start_date)
from people;
0: jdbc:hive2://localhost:10000> show partitions dynamic_people;
+---------------------+
| partition |
+---------------------+
| year=2017/month=11 |
| year=2017/month=12 |
| year=2018/month=10 |
| year=2018/month=8 |
| year=2019/month=3 |
+---------------------+
0: jdbc:hive2://localhost:10000> select * from dynamic_people where year= 2018;
+--------------------+----------------------+---------------------+----------------------------+----------------------+-----------------------+
| dynamic_people.id | dynamic_people.name | dynamic_people.age | dynamic_people.start_date | dynamic_people.year | dynamic_people.month |
+--------------------+----------------------+---------------------+----------------------------+----------------------+-----------------------+
| 4 | justin | 34 | 2018-10-12 | 2018 | 10 |
| 3 | robin | 14 | 2018-08-13 | 2018 | 8 |
+--------------------+----------------------+---------------------+----------------------------+----------------------+-----------------------+
0: jdbc:hive2://localhost:10000> select * from dynamic_people where year= 2017 and month = 11;
+--------------------+----------------------+---------------------+----------------------------+----------------------+-----------------------+
| dynamic_people.id | dynamic_people.name | dynamic_people.age | dynamic_people.start_date | dynamic_people.year | dynamic_people.month |
+--------------------+----------------------+---------------------+----------------------------+----------------------+-----------------------+
| 5 | jarry | 24 | 2017-11-11 | 2017 | 11 |
+--------------------+----------------------+---------------------+----------------------------+----------------------+-----------------------+
Hive Buckets 分桶
- 分桶对应HDFS中的文件
- 更高的查询效率
- 使抽样更高效
- 一般根据“桶列”的哈希函数将数据分桶
- 分桶只有动态分桶
修改属性
SET hive.enforce.bucketing = true;
此开关打开之后,会自动根据 bucket 个数自动分配 Reduce task 的个数,Reduce 个数与 bucket 个数一致。
此外,Reduce 的个数还可以通过 mapred.reduce.tasks 进行设置,但是这方法不推荐在 Hive 分桶中使用
分桶语句
CLUSTERED BY (列名) INTO 桶数 BUCKETS
创建表语句
注意分桶字段只能是建表中已有的字段 而分区表的字段必须是建表中没有的字段
create table test_bucket(
id int,name string,age int)
clustered by (age) into 4 buckets
row format delimited
fields terminated by '';
INSERT OVERWRITE TABLE employee_id_buckets SELECT * FROM employee_id;
随机抽样基于整行数据
SELECT * FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 4 ON rand()) s;
随机抽样基于指定列(使用分桶列更高效)
SELECT * FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 4 ON id) s;
--若分桶数为8
--取得桶数等于 分桶数除以out of后面的值 8/4=2
--bucket 3 从第几个分桶开始取 取 3 和 3+4=7 两个分桶
--on id 基于 id
--s 别名
随机抽样基于block size
SELECT * FROM table_name TABLESAMPLE(10 PERCENT) s;
SELECT * FROM table_name TABLESAMPLE(1M) s;
SELECT * FROM table_name TABLESAMPLE(10 rows) s;