【笔记】Hive基础

已于 2024-09-01 17:24:33 修改
阅读量923 收藏 9
点赞数 27
分类专栏： 大数据 文章标签： 笔记 hive 数据挖掘
于 2024-09-01 17:15:24 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_53095310/article/details/141609667
版权

目录

一、入门

二、数据定义（DDL）

2.1数据库

（1）创建数据库

（2）查询数据库

（3）修改数据库

（4）删除数据库

（5）切换数据库

2.2表

（1）创建表

（2）查看表

（3）修改表

（4）删除表

（5）清空表

三、数据操作（DML）

（1）Load

（2）INSERT

（3）导入导出

四、查询

（1）语法

（2）基本查询（Select...From）

（3）分组

（4）Join语句

（5）排序

五、函数

（1）简介

（2）单行函数

（3）聚合函数

（4）炸裂函数

（5）窗口函数（开窗函数）

（6）自定义函数（UDF）

六、分区表和分区桶

（1）分区表

（2）分区桶

来源：大数据Hive 3.x教程

一、入门

        Hive是基于Hadoop的一个数据仓库工具，提供类SQL查询功能，可将结构化数据文件映射为表。

        本质：将HQL转化为MapReduce程序。

        架构：

二、数据定义（DDL）

2.1数据库

（1）创建数据库

语法：

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment（注释）]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...（kv键值对）)];

        注：方括号内为修改内容。

 eg：1.创建数据库，但不指定路径：

create database db_hive1;

        默认路径：${hive.metastore.warehouse.dir}/database_name.db 

2.创建数据库并指定路径：

create database db_hive2 location '/db_hive2';

 3.创建带有dbproperties的数据库：

create database db_hive3 with dbproperties('create_date'='****-**-**');

（2）查询数据库

1.展示所有数据库

语法：SHOW DATABASES [LIKE 'identifier_with_wildcards'];

注：LIKE通配符（模糊查询）：*表示任意个任意字符，|表示或的关系 。

eg：show databases like 'db*';

         只能查询到db为开头的数据库。

2.查看数据库信息

语法：DESCRIBE DATABASE [EXTENDED] db_name;

 注：EXTENDED-是否展示详细信息。

（3）修改数据库

语法：

--修改dbproperties
ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);

--修改location（路径）
ALTER DATABASE database_name SET LOCATION hdfs_path;

--修改owner user（权限）
ALTER DATABASE database_name SET OWNER USER user_name;

（4）删除数据库

语法：DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];
RESTRICT（严格模式）若数据库不为空，则删除失败，默认为该模式
CASCADE（级联模式）若数据库不为空，则会将库中的表一并删除

（5）切换数据库

语法：USE database_name;

2.2表

2.2.1普通建表

（1）创建表

语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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, ...)]
语法关键字说明
TEMPORARY（临时表）该表只在当前会话可见，会话结束，表则会被删除
EXTERNAL（外部表）Hive只接管元数据，而不完全接管HDFS中的数据
内部表（管理表）Hive完全接管该表，包括元数据和HDFS中的数据
PARTITIONED BY创建分区表：依据hive表数据分到不同路径里面
CLUSTERED BY ... SORTED BY...INTO ... BUCKETS创建分桶表
ROW FORMAT（重点）使用SERDE序列化和反序列化每行数据
STORED AS（重点）指定文件格式

ROW FORAMT语法一：

ROW FORAMT DELIMITED 
[FIELDS TERMINATED BY char] 
[COLLECTION ITEMS TERMINATED BY char] 
[MAP KEYS TERMINATED BY char] 
[LINES TERMINATED BY char] 
[NULL DEFINED AS char]

注：

  • fields terminated by ：列分隔符
  • collection items terminated by ： map（每个键值对之间分隔符）、struct（字段之间分隔符）和array（数组之间分隔符）中（复杂数据类型）每个元素之间的分隔符
  • map keys terminated by ：map中的key与value的分隔符
  • lines terminated by ：行分隔符

 语法二：SERDE关键字可用于指定其他内置SERDE或者用户自定义SERDE

ROW FORMAT SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]

1.数据类型

基本数据类型

类型

说明

定义

tinyint

1byte有符号整数

smallint

2byte有符号整数

int

4byte有符号整数

bigint

8byte有符号整数

boolean

布尔类型，true或者false

float

单精度浮点数

double

双精度浮点数

decimal

十进制精准数字类型

eg：decimal(16,2)（16代表整数位+小数位的个数，2代表小数位个数）

varchar

字符序列，需指定最大长度，最大长度的范围是[1,65535]

varchar(32)

string

字符串，无需指定最大长度

timestamp

时间类型

binary

二进制数据

复杂数据类型
类型说明定义取值

array

数组是一组相同类型的值的集合array<string>arr[0]（取出数组第0个元素）
mapmap是一组相同类型的键-值对集合map<string, int>map['key']
struct结构体由多个属性组成，每个属性都有自己的属性名和数据类型struct<id:int, name:string>struct.id

2.类型转换

类型转换

隐式转换

不同类型会向两者之间的类型进行转换

官方说明：Allowed Implicit Conversions

显示转换

可用cast函数完成显示类型转换

显示转换

语法：cast(expr as <type>)

2.2.2 CTAS（Create Table As Select）建表

        利用select查询语句返回的结果直接建表等操作。

语法：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name [AS select_statement]

2.2.3 Create Table Like语法

        允许复刻一张已经存在的表结构。

（2）查看表

1.展示所有表

语法：SHOW TABLES [IN database_name] LIKE ['identifier_with_wildcards'];

2.查看表信息

语法：DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name

注：EXTENDED-展示详细信息；

       FORMATTED：对详细信息进行格式化的展示。

（3）修改表

1.重命名表

语法：ALTER TABLE table_name RENAME TO new_table_name

2.修改列信息

增加列：增加新列，新列位置位于末尾

语法：ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)

更新列：修改列名、数据类型、注释信息以及表位置

语法：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 REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

（4）删除表

语法：DROP TABLE [IF EXISTS] table_name;

（5）清空表

语法：TRUNCATE [TABLE] table_name

注：truncate负责内部表，不删除外部表数据

三、数据操作（DML）

（1）Load

        if为本地导入，类似于put。

语法：LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)];
关键字说明
LOCAL本地加载数据/HDFS加载到HIVE

OVERWRITE

覆盖表数据
PARTITION上传至目标分区

（2）INSERT

1.查询结果插入表

语法：INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement;
关键字说明
INTO追加结果至目标表
OVERWRITE结果覆盖原数据

2.给定值插入表

语法：INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

3.查询结果写入目标路径

语法：INSERT OVERWRITE [LOCAL] DIRECTORY directory [ROW FORMAT row_format] [STORED AS file_format] select_statement;

（3）导入导出

EXPORT将表数据导出至HDFS路径
IMPORT将内容导入Hive

语法：

--导出
EXPORT TABLE tablename TO 'export_target_path'

--导入
IMPORT [EXTERNAL] TABLE new_or_original_tablename FROM 'source_path' [LOCATION 'import_target_path']

四、查询

（1）语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference       -- 从什么表查
  [WHERE where_condition]    -- 过滤
  [GROUP BY col_list]        -- 分组查询
  [HAVING col_list]          -- 分组后过滤
  [ORDER BY col_list]        -- 排序
  [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]
  [LIMIT number]             -- 限制输出的行数

（2）基本查询（Select...From）

1.全表查询

select * from tablename;

2.选择特定列查询

select key1, key2 from tablename;

注意：1.SQL大小写不敏感；2.SQL可写一/多行；3.关键字不能被缩写也不能分行；4.各子句一般要分行写；5.缩进提高可读性

3.列别名

eg:select key1 as key2 from tablename;

4.Limit：限制返回行数

select * from tablename limit 2,3; -- 表示从第2行开始，向下抓取3行

5.Where：将不满足条件的行过滤；where跟随from

eg：select * from tablename where key1 > 10;

6.关系运算函数

基本语法
操作符支持数据类型描述
in（num1，num2）ALLin运算显示列表中的值
A [not] like Bstring

通配符模式。如果A与B匹配，返回true；不匹配返回false。B的表达式说明：‘x%’--A必须以‘x’开头，‘%x’--A必须以‘x’结尾，‘%x%’--表示A包含‘x’。

A rlike B,A regexp BstringB为正则表达式。IF A匹配，则返回true；否则返回false。匹配使用JDK正则表达式借口实现。
A [not] between B and CALL

IF A>=B and <=C，则为true，反之为false。

7.聚合函数

聚合函数
count(*)统计所有行数，包括null
count(某列）该列一共多少行，不包含null
max()求最大值，不包含null，除非所有值为null

min()

求最小值，不包含null，除非所有值为null

sum()

求和，不包含null
avg()求平均值，不包含null

（3）分组

1.Group By

eg：计算emp表每个部门的平均工资

select 
    t.deptno, 
    avg(t.sal) avg_sal 
from emp t 
group by t.deptno;

2.Having

where不能写分组聚合函数
having可使用分组聚合函数，只用于group by分组统计语句

eg：求每个部门的平均薪水大于1000的部门

select 
    deptno, 
    avg(sal) avg_sal 
from emp 
group by deptno  
having avg_sal > 1000;

（4）Join语句

1.等值Join

Hive支持的sql join语句，but只支持等值连接，不支持非等值连接

连接
内连接（join）进行连接的两表都存在与连接条件相匹配的数据才会被保留下来

左外连接

（left join）

join操作符左边表中符合where子句的所有记录将会被返回

右外连接

（right join）

join操作符右边表中符合where子句的所有记录将会被返回

满外连接

（full join）

返回所有表符合where的记录。IF字段没有符合条件的值，null代替
多表连接连接n个表，至少需要n-1个连接条件
笛卡尔集所有表中的所有行互相连接

union &

union all

join是左右关联，union和union all是上下拼接。

union去重，union all不去重。

拼接要求：1.两列个数相同；2.列类型一致

（5）排序

1.全局排序（Order By）：只有一个Reduce

Order By子句排序
asc（ascend）升序（默认）
desc（descend）降序

        Order By子句在select语句结尾。

eg：查询员工信息按工资升序排列

select * from emp 
order by sal;

2.Reduce内部排序（Sort By）

        每个Reduce内部进行排序，为每个reduce产生一个排序文件。

设置reduce个数

set mapreduce.job.reduces=3;

查看reduce个数

set mapreduce.job.reduces;

eg：根据部门编号降序查看员工信息

select * from emp 
sort by deptno desc;

 查询结果导入文件：按照部门编号降序排序

insert overwrite local directory '/opt/module/hive/datas/sortby-result'
select * from emp sort by deptno desc;

3.分区（Distribute By）

注：distribute by语句要写在sort by语句之前；mapreduce.job.reduces的值设置返回-1。

4.分区排序（Cluster By）

        具有distribute by功能外还兼具sort by功能。但排序只能是升序排序，不能指定排序规则为asc或者desc。

--以下两种写法等价：
select * from tablename 
cluster by deptno;

select * from tablename 
distribute by deptno
sort by deptno;

五、函数

（1）简介

--查看系统内置函数
show functions;
--查看内置函数用法
desc function upper;
--查看内置函数详细信息
desc function extended upper;

（2）单行函数

1.算术运算函数

运算符描述
A&BA和B按位取与
A|BA和B按位取或
A^BA和B按位取异或
~AA按位取反

2.数值函数

round：四舍五入

select round(3.3);
--结果：3

ceil：向上取整

select ceil(3.1);
--结果：4

floor：向下取整

select floor(4.8);
--结果：4

3.字符串函数

 substring：截取字符串

语法一语法二
语法substring(stringA,int start)substring(stringA,int start,int len)
返回值stringstring
说明返回字符串A从start位置到结尾的字符串返回字符串A从start位置开始，长度为len的字符串
--eg：从第3个字符开始，向后获取2个字符
select substring("tablename",3,2);
--结果：bl

replace：替换

语法replace(string A, string B, string C)
返回值string
说明将字符串A中子字符串B替换C
select replace('table', 'a', 'A')
--结果：tAble

regexp_replace：正则替换

语法regexp_replace(stringA,stringB,stringC)
返回值string
说明将字符串A中的符合java正则表达式B的部分替换为C
select regexp_replace('100-200', '(\\d+)', 'num')
--结果：num-num

regexp：正则匹配

repeat：重复字符串

split：字符串切割

nvl：替换null值

concat：拼接字符串

concat_ws：以指定分隔符拼接字符串或者字符串数组

get_json_object：解析json字符串

4.日期函数

函数语法返回值说明
unix_timestampunix_timestamp()bigint返回当前或指定时间的时间戳
from_unixtimefrom_unixtime(bigint unixtime[, string format])string转化UNIX时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式
current_date//当前时期
current_timestamp//当前的日期加时间，并且精确的毫秒
monthmonth (string date)int获取日期中的月
dayday(string date)int获取日期中的日
hourhour(string date)int获取日期中的时
datediffdatediff(string enddate, string startdate)int两个日期相差的天数（结束日期减去开始日期的天数）
date_adddate_add(string startdate, int days)string日期加天数
date_subdate_sub (string startdate, int days)string日期减天数
date_format//将标准日期解析成指定格式字符串

5.流程控制函数

case when：条件判断函数
语法一语法二
函数case when a then b [when c then d]* [else e] endcase a when b then c [when d then e]* [else f] end
返回值TRUETRUE
说明如果a为true，则返回b；如果c为true，则返回d；否则返回e如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
IF
语法if（boolean testCondition, T valueTrue, T valueFalseOrNull）
返回值TRUE
说明条件判断（类似Java三元运算符）。当条件testCondition为true时，返回valueTrue；否则返回valueFalseOrNull
--eg：条件满足，输出正确
select if(10 > 5,'正确','错误');
--结果：TURE

6.集合函数

函数语法说明
sizeeg：select size(friends) from test;集合中元素的个数
mapmap (key1, value1, key2, value2, …)创建map集合，根据输入的key和value对构建map类型
map_keys

eg:select map_keys(map('key1',1,'key2',2));

结果：["key1","key2"]

返回map中的key
map_values

eg:select map_values(map('key1',1,'key2',2));

结果：[1,2]

返回map中的value
arrayarray(val1, val2, …)根据输入的参数构建数组array类
array_contains

eg：select array_contains(array('a','b','c','d'),'a');

结果：true

判断array中是否包含某个元素
sort_array

eg：select sort_array(array('a','d','c'));

结果：["a","c","d"]

将array中的元素排序
structstruct(val1, val2, val3, …)根据输入的参数构建结构体struct类
named_struct

select named_struct('name','iop','age',15);

结果：{"name":"iop","age":15}

声明struct的属性和值

（3）聚合函数

        1.collect_list 收集并形成list集合，结果不去重

        2.collect_set 收集并形成set集合，结果去重

（4）炸裂函数

        UDTF：接收一行数据，输出一行or多行数据

（5）窗口函数（开窗函数）

        定义：窗口函数为每行数据划分一个窗口，继而对窗口范围内数据计算，最后将计算结果返回给该行。

常用窗口函数
聚合函数max、min、sum、avg、count
跨行取值函数lead、lag、first_value、last_value
排名函数rank、dense_rank、row_number

lead和lag：获取当前行的上/下边某行、某个字段的值

lag(字段名,偏移量,默认值)...
lead(字段名,偏移量,默认值)...
--注：不支持自定义窗口

first_value和last_value：获取窗口内某一列的第一个值/最后一个值

first_value(字段名,是否跳过null（T/F）)...
last_value(字段名,是否跳过null（T/F）)...

rank、dense_rank、row_number：计算排名

注：rank、dense_rank、row_number不支持自定义窗口

（6）自定义函数（UDF）

编程步骤：

1.继承Hive提供的类

        org.apache.hadoop.hive.ql.udf.generic.GenericUDF

        org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

2.实现类抽象方法

3.hive命令行窗口创建函数

--添加jar
add jar linux_jar_path
--创建function
create [temporary] function [dbname.]function_name AS class_name;

4.hive命令行窗口删除函数

drop [temporary] function [if exists] [dbname.]function_name;

六、分区表和分区桶

（1）分区表

1.语法

创建分区表

create table dept_partition
(
    deptno int,    --部门编号
    dname  string, --部门名称
    loc    string  --部门位置
)
    partitioned by (day string)
    row format delimited fields terminated by '\t';

写数据：load，insert

读数据:将分区字段看作表的伪列

2.基本操作

查看分区信息

show partitions dept_partition;

 创建/删除多个分区

alter table dept_partition 
add/drop partition(day='20240901') partition(day='20240902');;

3.二级分区表

4.动态分区

        插入数据时分区由每行数据最后一个字段值动态决定

（2）分区桶

        分桶针对数据文件，分区针对数据存储路径

        基本原理：为每行数据计算一个指定字段的数据hash值，然后模以一个指定分桶数，最后将取模运算结果相同的行，写入同一个文件中，这个文件就称为一个分桶（bucket）。

天启和风
关注
专栏目录
【Python大数据笔记_day05_Hive基础操作】
LKL的博客
11-09 1827
Hive基础操作
hive笔记一：hive基础
Mavey__的博客
07-28 318
hive架构，hive数据类型，hive数据模型，元数据保存方式、Hive的优缺点、Hive与传统数据库的区别
Hive 优化
JXCypress
04-21 651
Hive 优化问题： 数据倾斜（sum ,count 不存在数据倾斜） jobs 多 count（distinct）效率低，数据量一多就出问题方案： 设计一个好的数据模型 减少jobs数 设置合理的mapper reducd 数目 对小文件进行合并 ---------- 1.1 join 优化 Join 查找操作
Hive学习笔记
NQ31
07-19 709
Hive学习笔记
大数据学习笔记13-Hive基础1
MU2377的博客
04-29 622
Hive是一个SQL转化转化工具，将SQL的计算转为MapReduce的计算，让开发人员更加方便进行大数据开发。
大数据学习笔记14-Hive基础2
MU2377的博客
04-29 1561
字段类型;分隔符指定;表的删除修改;表的分类;表数据写入;表分区
Hive基础
小琳猫的博客
12-10 1072
本篇博客参考线上教程的笔记，对Hive数据仓库的基础进行简单总结，以便加深理解和记忆
Hive笔记-4
shadown7z的博客
06-22 468
Export导出语句可将表的数据和元数据信息一并到处的HDFS路径，Import可将Export导出的内容导入Hive，表的数据和元数据信息都会恢复。Export和Import可用于两个Hive实例之间的数据迁移。6.2 基本查询(Select ... From)(2) OVERWRITE: 用结果覆盖原有数据。(1) INTO: 将结果追加到目标表。员工编号 姓名 岗位 薪资 部门。部门编号 部门名称 部门位置id。4.2.1 将查询结果插入表中。(2) 根据查询结果插入数据。
【C++学习笔记】数组与指针（三）
qq_38196449的博客
09-09 1169
0（空字符）
UEFI学习笔记（五）：EDK II PCD的概念、类型、使用
qq_44189622的博客
09-11 788
如果在BIOS里面有一些模块是binary方式集成进来的而这些binary又需要用到PCD（用于Binary Release），那么这些Binary集成的要用到的PCD就必须要设置为。PCD的值存在memory里面，下次启动时，上次更改的值丢失了，每次启动都是从default值开始。是存在VPD空间的（在FLASH上，只读），一般是出厂配置。如果platform是从源码build出来的，没有binary在里面的时候，PCD用的都是。作用域在一个模块中（模块级的），可以在Binary Level进行修改。
OJ在线判题系统项目笔记
今天不coding的博客
09-11 725
OJ在线判题系统项目笔记
C++学习笔记（10）
qq_60098634的博客
09-07 701
unordered_multimap 和 unordered_map 的区别在：unordered_multimap 允许关键字重复，而。unordered_multiset 和 unordered_set 的区别在：unordered_multiset 允许关键字重复，而。
Netty笔记03-组件Channel
最新发布
weixin_46425661的博客
09-12 507
本文主要讲解Netty中的组件Channel、ChannelFuture和CloseFuture
【学习笔记】SSL密码套件之哈希
Taki_UP的博客
09-12 708
本篇介绍了TLS/SSL密码套件中常用的哈希算法，包括Poly1305、SHA384、SHA256、SHA、MD5
sheng的学习笔记-AI-规则学习（rule learning）
coldstarry的专栏
09-09 1123
机器学习中的“规则”(rule)通常是指语义明确、能描述数据分布所隐含的客观规律或领域概念、可写成“若……，则……”形式的逻辑规则。​“规则学习”(rule learning)是从训练数据中学习出一组能用于对未见示例进行判别的规则。一条规则形如：在数理逻辑中“文字”专指原子公式(atom)及其否定。与神经网络、支持向量机这样的“黑箱模型”相比，规则学习具有更好的可解释性，能使用户更直观地对判别过程有所了解。另一方面，数理逻辑具有极强的表达能力，绝大多数人类知识都能通过数理逻辑进行简洁的刻画和表达。
JS笔记
2201_76100326的博客
09-11 436
Math对象属于Javascript内置对象，无需实例化（不需要添加new ），可以直接使用。只有一个Math.PI的属性。
【学习笔记】SSL证书之前向保密
Taki_UP的博客
09-10 687
本篇介绍了密码学中前向保密（Forward Secrecy）的相关内容
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值