hive基础20201213

Hive

• 基于Hadoop的数据仓库解决方案
• 将结构化的数据文件映射为数据库表
• 提供类sql的查询语言HQL（Hive Query Language）

1. Hive的优势和特点

• 入门简单，HQL类SQL语法
• 统一的元数据管理，可与impala/spark等共享元数据
• 灵活性和扩展性较好：支持UDF，自定义存储格式等
• 支持在不同的计算框架上运行（MR, Tez, Spark）
• 提供了一个简单的优化模型
• 适合离线数据处理，稳定可靠（真实生产环境）
• 有庞大活跃的社区

2.Hive架构

Hive 底层存储是基于 HDFS 进行存储，Hive 的计算底层是转换成 MapReduce 进行计算

如图中所示，Hive 通过给用户提供的一系列交互接口，接收到用户的指令
(SQL)，使用自己的 Driver，结合元数据 (MetaStore)，将这些指令翻译MapReduce，提交到 Hadoop 中执行，最后，将执行返回的结果输出到用户交互接口。

3.Hive元数据管理

• 记录数据仓库中模型的定义、各层级间的映射关系
• 存储在关系数据库中
• 内嵌模式：默认Derby, 轻量级内嵌SQL数据库
  Derby非常适合测试和演示
  存储在.metastore_db目录中
• 本地模式：实际生产一般存储在MySQL中
  修改配置文件hive-site.xml：设置 MySQL 的 Connection URL、用户名和密码以及ConnectionDriverName；
  将 MySQL 的 JDBC 驱动 Jar 文件放到 Hive 的 lib 目录下
• 远程模式：在服务器端启动 MetaStoreServer，客户端利用 Thrift 协议通过 MetaStoreServer 访问元数据库
• HCatalog：将Hive元数据共享给其他应用程序

4.Hive数据结构

5.数据库（Database)

表的集合，HDFS中表现为一个文件夹，默认在hive.metastore.warehouse.dir属性目录下，如果没有指定数据库，默认使用default数据库

create database if not exists mydatabase; --创建数据库
use mydatabase; -- 选择数据库
show databases; -- 查看数据库信息
describe database default;  -- 可以查看数据库更多的描述信息
alter database mydatabase set owner user dayongd; -- 修改数据库信息
drop database if exists mydatabase cascade --如果数据库是空数据库，可以直接用 drop 删除，如果数据库不为空，可以
采用 cascade 命令，强制删除

6.数据表（Table)

• 内部表（管理表）
  HDFS中为所属数据库目录下的子文件夹
  数据完全由Hive管理，删除表(元数据)会删除数据

• 外部表（External Tables）
  数据保存在指定位置的HDFS路径中
  Hive不完全管理数据，删除表(元数据)不会删除数据

• Hive建表语句

--外部表和内部表创建方式一样，区别在于外部表多了external关键字
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 'This is an external table'  --为表和列添加注释
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|' --如何分隔列（字段）
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE  --文件存储格式
LOCATION '/home/hadoop/hive/warehouse/employee'; --数据存储路径（HDFS）

hive处理csv格式文件,separatorChar指定数据的分割
ROW FORMAT  serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties ("separatorChar"=",") 

Hive中默认分隔符：字段：^A(\001)/集合：^B(\002)/映射：^C(\003)

• 创建临时表
• 临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法
• 表只对当前session有效，session退出后自动删除
• 表空间位于/tmp/hive-<user_name>(安全考虑)
• 如果创建的临时表表名已存在，实际用的是临时表

--注意temporary关键字
CREATE TEMPORARY TABLE tmp_table_name1 (c1 string);
CREATE TEMPORARY TABLE tmp_table_name2 AS..
CREATE TEMPORARY TABLE tmp_table_name3 LIKE..

• Hive建表高阶语句
• CTAS – as select方式建表

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;--户复制现有的表结构，不复制数据

• 删除表

DROP TABLE IF EXISTS employee [purge];
TRUNCATE TABLE employee; -- 只能删除内部表数据，不能删除外部表中数据

• 修改表（Alter针对元数据）

ALTER TABLE employee RENAME TO new_employee;
ALTER TABLE c_employee SET TBLPROPERTIES ('comment'='New name, comments');
ALTER TABLE employee_internal SET SERDEPROPERTIES ('field.delim' = '$');
ALTER TABLE c_employee SET FILEFORMAT RCFILE; -- 修正表文件格式
-- 修改表的列操作
ALTER TABLE employee_internal CHANGE old_name new_name STRING; -- 修改列名
ALTER TABLE c_employee ADD COLUMNS (work string); -- 添加列
ALTER TABLE c_employee REPLACE COLUMNS (name string); -- 替换列

• 分区表
• 分区列的值将表划分为一个个的文件夹
• 查询时语法使用"分区"列和常规列类似
• 查询时Hive会只从指定分区查询数据，提高查询效率
• 分为静态分区和动态分区
  静态分区只能通过load方式一次一个分区装载数据

静态分区->创建分区表
create table dept_partition(
deptno int, 
dname string,
loc string )
partitioned by (month string)/partitioned by (month string, day string)
  --通过PARTITINED BY定义分区,可以创建多级分区
row format delimited fields terminated by '\t';

静态分区->添加分区
alter table dept_partition add partition(month='201906') ;
alter table dept_partition add partition(month='201905') partition(month='201904');

静态分区->删除分区
alter table dept_partition drop partition (month='201904');
alter table dept_partition drop partition (month='201905'), partition (month='201906');

静态分区->查看分区表有多少分区
show partitions dept_partition;

查看分区表结构
desc formatted dept_partition;

静态分区-> 加载数据到分区表中
load data local inpath '/opt/datas/dept.txt' into table dept_partition partition(month='201909');

单分区查询
select * from dept_partition where month='201909';

使用动态分区需设定属性
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

动态分区插入数据
insert into employee_partitioned partition(year, month)
select
name,array('Toronto') as work_place,
named_struct("sex","male","age",30) as sex_age,
map("python",90) as skills_score,
map("r&d", array('developer')) as depart_title,
year(start_date) as year,month(start_date) as month
from employee_hr eh;

• 分桶（Bucket）
• 更高的查询处理效率
• 使抽样（sampling）更高效
• 一般根据"桶列"的哈希函数将数据进行分桶

--注意分桶字段只能是建表中已有的字段
--分区表的字段必须是建表中没有的字段
create table test_bucket(
 id int,
 name string,
 age int
)
clustered by (age) into 4 buckets
row format delimited
fields terminated by ' ';

插入数据到分桶表：必须使用INSERT方式加载数据
insert into table test_bucket
 select id, name, age
from people;

• 分桶抽样（Sampling）
• 随机抽样基于整行数据

SELECT * FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand()) s;

• 随机抽样基于指定列（使用分桶列更高效）

SELECT * FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON id) s;

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

CREATE VIEW view_name AS SELECT statement; -- 创建视图
SHOW TABLES; -- 查找视图
SHOW CREATE TABLE view_name; -- 查看视图定义
DROP view_name; -- 删除视图
--更改视图属性
ALTER VIEW view_name SET TBLPROPERTIES ('comment' = 'This is a view');
ALTER VIEW view_name AS SELECT statement; -- 更改视图定义,

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

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;

7.数据导入

• 向表中装载数据（Load）

load data [local] inpath '/opt/datas/student.txt' [overwrite] into table
student [partition (partcol1=val1,…)];
1、local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表
2、overwrite:表示覆盖表中已有数据，否则表示追加

• 查询语句中创建表并加载数据（as select）

create table if not exists student3
as select id, name from student;

• 创建表时通过 location 指定加载数据路径

 create table if not exists student5(
 id int, name string)
 row format delimited fields terminated by '\t'
 location '/kgc/hive/student5';

 上传数据到 hdfs 上
 dfs -put /opt/datas/student.txt /kgc/hive/student5;

• 通过查询语句向表中插入数据（Insert）

--（1）创建一张分区表
create table student(id string, name string) partitioned by
(month string) row format delimited fields terminated by '\t';
--（2）基本插入数据
insert into table student partition(month='201709')
values('1004','zhaoliu');
--（3）基本模式插入（根据单张表查询结果）
 insert overwrite table student partition(month='201708')
 select id, name from student where month='201709';
--（4）多插入模式（根据多张表查询结果）
 from student
 insert overwrite table student partition(month='201707')
 select id, name where month='201709'
 insert overwrite table student partition(month='201706')
 select id, name where month='201709';

• import 数据到指定 hive 表中

import table student2 partition(month='201709') from
'/user/hive/warehouse/export/student';

8.数据导出

• . Insert 导出

 将查询的结果导出到本地
 insert overwrite local directory '/opt/datas/export/student'
 select * from student;

 将查询的结果导出到 HDFS 上(没有 local)
 insert overwrite directory '/user/hive/warehouse/student2'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
 COLLECTION ITEMS TERMINATED BY '\n'
 select * from student;

• Hadoop 命令导出到本地

dfs -get /user/hive/warehouse/student/month=201709/000000_0
/opt/datas/export/student3.txt;

• Export 导出到 HDFS 上

export table default.student to '/user/hive/warehouse/export/student';

• 其他工具导出：以使用 sqoop 等