hive基础

什么是hive？

hive是一种基于hadoop的数据仓库工具，将结构化数据文件映射成一张表，并提供hql语句，它的本质是将HQL转换为MR程序。

（1）Hive处理的数据存储在HDFS

（2）Hive分析数据底层的实现是MapReduce

（3）执行程序运行在Yarn上

hive基础数据类型

基本数据类型：tinyint: 1字节、smallint：2字节、int: 4字节、bigInt: 8字节

boolean、float、double、string

timestamp时间类型（不咋用）、binary字节数组

集合数据类型：struct、array、map

类型转换

hive的基本数据类型是可以隐式转换，小转大

1. float和string都可以隐式转换为double
2. boolean类型不可以转换为任何其他的类型
3. 可以使用cast(‘1’ as int) 或 int（‘1’）进行强制转换把字符串1转化为整数1

DDL数据定义

1. 1创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

1.2查询数据库

show databases;
show databases like 'de*';
desc database 数据库名;
desc extended database 数据库名;

1.3修改数据库

alter database 数据库名 dbproperties('createtime'='19990604');

1.4删除数据库

drop database [if exists]数据库名
--如果数据库不为空，采用cascade强制删除
drop database 数据库名 cascade;

2.1创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
[LIKES existing_table_or_view_name]

--查看表的类型
desc formatted 表名
--根据查询结果创建表(查询的结果会添加到新创建的表中)
create table if not exists student as select * from 表名4
--根据已经创建的表结构创建表
create table if not exists student3 like student;

2.2管理表与外部表的互相转换

alter table 表名 set tblproperties('external'='true');

2.3修改表

--重命名表
alter table table_name rename to new_table_name;
--更新列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
--增加和列替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 
--修改表字段名
alter table test2 change column id student_id int
--修改表字段类型
alter table test2 change column student_id student_id string
--增加两列字段
alter table test2 add columns(name string,sex string)
--让name的列排在最前面
alter table test2 change name name string first
--替换所有列
alter table test2 replace columns(id double)

查看表结构

desc formatted 表名

2.4删除表以及表中数据

删除表

drop table test2

清楚表中数据

truncate table test2

DML数据操作

3.1 向表中导入数据

load data [local] inpath '数据的path' [overwrite] into table table_name [partition (partcol1=val1,…)]

overwrite写上表示覆盖，不写是追加

load data相当于移动操作，及执行一次后源文件不存在

3.2向表中插入数据

--插入两条数据，追加
insert into table  student2 values(1,'wangwu'),(2,'zhaoliu');
--将查询到的数据插入表中,前提有表，并且插入数据与表结构相同
insert overwrite table student2 select id, name from student ;

into为追加，overwrite为覆盖

3.4数据导出

insert导出时hive会自动创建目录，有local是本地，没有是导出到hdfs

--将查询结果导出
insert overwrite [local] directory  路径名
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             select * from student;

3.5数据迁移

export和import命令主要用于两个hadoop之间的hive表进行迁移

export table 表名 to 地址
import table 表名 from 地址

查询

group by可以用来去重，select后面只有group by后面的字段和聚合函数

5.1排序

全局排序:order by 使用时只有一个reduce，效率低一般不用

每个reduce内部排序: sort by：在每个reduce内部进行排序，对全局结果并不是有序的

分区:distribute by:分区，一般结合sort by使用

insert overwrite local directory '/opt/module/hive/datas/distribute-result' row format delimited fields terminated by '\t' select ename,empno,deptno,sal from emp distribute by deptno sort by sal desc;

cluster by:当distribute by和sort by字段相同时可以直接用cluster by，但是只能是升序排序

分区表和分桶表

6.1分区表

--创建分区表
create table 表名(字段名)
partitioned by (day string)
--1.分区表加载数据
load data local inpath '地址' into table partition(day='')
--2.分区表把查询结果加载进表:动态分区
insert overwrite table 表名 partition(day) select 字段,day from 表名
--3.分区表把查询结果加载进表:静态分区
insert overwrite table 表名 partition(day='') select 字段 from 表名

增加分区

alter table 表名 add partition(day='')[partition(字段=‘值’)]

删除分区

alter table 表名 drop partition(字段=‘值’)[partition(字段=‘值’)]

二级分区表

create table ()
partitioned by(字段1 字段类型,字段2 字段类型)

修复分区

msck repair table 表名

6.2分桶表

分区针对的是数据的存储路径；分桶针对的是数据文件

set mapreduce.job.reduces=-1;

create table 表名(id int)
clustered by(id)
into 3 buckets

函数

详细查看函数用法

desc function extended 函数;

7.1常用函数

concat(‘字符串1’,‘字符串2’) 讲两个字符串拼接

concat_ws(分隔符，[string | array(string)]，…)将字符串通过分隔符进行连接并返回

注：分隔符如果是null返回值为null，这个函数会跳过分隔符参数后的任何 NULL 和空字符串。

EXPLODE(col)：将hive表的一列中复杂的array或者map结构拆分成多行。

SPLIT(string str, string regex): 按照regex字符串分割str，会返回分割后的字符串数组

LATERAL VIEW udtf(expression) tableAlias AS columnAlias

用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，再将多行结果组合成一个支持别名的虚拟表

select movie,category_name
from movie_info
lateral view explode(split(category,',')) movie_info_tmp as category_name

7.2开窗函数(重点)

LAG  (scalar_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause);

select 列名1,

列名2,

day

lag(day,1,‘1999-01-20’) over(partition by 列名1 order by 列名20)

from 表名

lead (scalar_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause);

lag 表示前offset行，lead表示后offset行

rank():排序相同时重复，总是不变

dense_rank():排序相同时会重复，总数会减少

row_number():会根据顺序计算

本文为个人复习笔记，知识点仅为本人易遗忘部分。