HIVE all hive all came to be

转载

angel 2023-10-27 08:04:33

文章标签 HIVE all hive big data hadoop 表名 文章分类 Hive 大数据

文章目录

hive基础

hive基础数据类型
类型转换

DDL数据定义

1. 1创建数据库
1.2查询数据库
1.3修改数据库
1.4删除数据库
2.1创建表
2.2管理表与外部表的互相转换
2.3修改表
2.4删除表以及表中数据

DML数据操作

3.1 向表中导入数据
3.2向表中插入数据
3.4数据导出
3.5数据迁移

查询

5.1排序

分区表和分桶表

6.1分区表
6.2分桶表

函数

7.1常用函数
7.2开窗函数(重点)

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的基本数据类型是可以隐式转换，小转大

float和string都可以隐式转换为double
boolean类型不可以转换为任何其他的类型
可以使用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():会根据顺序计算