Hive数据库实操

最新推荐文章于 2024-04-24 21:10:15 发布

枫落@

最新推荐文章于 2024-04-24 21:10:15 发布

阅读量256

点赞数

文章标签： hive 数据库 hadoop

本文链接：https://blog.csdn.net/qq_42053753/article/details/131889590

版权

1、hive数据库操作

--创建数据库

create database if not exists myhive;

--创建数据库并制定HDFS存储位置

create database myhive2 location '/myhive2';

--查看数据库信息

desc database myhive2;

--删除一个空数据库，如果数据库下面有数据表，那么就会报错

drop database myhive2;

--强制删除数据库，包含数据库下面的表一起删除

drop database myhive2 cascade;

2、hive数据表操作

常用的数据类型：INT 整数型、DOUBLE 双精度浮点数、String 变长字符串，Varchar 变长字符串、timestramp 时间戳、date 日期等。

内部表操作

--创建表

CREATE TABLE test(

id INT,

name STRING,

gender STRING

);

--删除表

DROP TABLE test;

--插入表数据

insert into stu values (1,"zhangsan"), (2, "wangwu");

--查询表数据

select * from stu;

外部表操作

--创建一个外部表（先建表再插入数据）

create external table test_ext1(id int, name string) row format delimited fields terminated by '\t' location '/tmp/test_ext1/';

--创建一个外部表（先有数据后建表）

create external table test_ext2(id int, name string) row format delimited fields terminated by '\t' location '/tmp/test_ext1/';

--查看表类型

desc formatted stu;

--内外部表转换

alter table stu set tblproperties('EXTERNAL'='FALSE');

数据加载和导出

load data local inpath '/home/hadoop/search_log.txt' into table myhive.test_load; 从本地加装
load data inpath '/tmp/search_log.txt' overwrite into table myhive.test_load; 从HDFS上加载
insert overwrite [local] directory ‘path’ select_statement1 FROM from_statement; 从表向其他表加载数据

将查询的结果导出到本地 - 使用默认列分隔符

insert overwrite local directory '/home/hadoop/export1' select * from test_load ;

将查询的结果导出到本地 - 指定列分隔符

insert overwrite local directory '/home/hadoop/export2' row format delimited fields terminated by '\t' select * from test_load;

将查询的结果导出到HDFS上(不带local关键字)

insert overwrite directory '/tmp/export' row format delimited fields terminated by '\t' select * from test_load;

hive表数据导出 - hive shell

bin/hive -e "select * from myhive.test_load;" > /home/hadoop/export3/export4.txt

bin/hive -f export.sql > /home/hadoop/export4/export4.txt

分区表

--创建一个单分区表
create table score(id string, cid string, score int) partitioned by (MONTH STRING) 
row format delimited fields terminated by '\t';

--创建一个多分区表：分区层次为年、月、日
create table score(id string, cid string, score int) partitioned by (year STRING ,MONTH STRING,day STRING) 
row format delimited fields terminated by '\t';

分桶表

开启分桶的自动优化（自动匹配reduce task数量和桶数量一致）

set hive.enforce.bucketing=true;

创建分桶表

create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t';

桶表的数据加载，由于桶表的数据加载通过load data无法执行，只能通过insert select.

如果说分区表的性能提升是：在指定分区列的前提下，减少被操作的数据量，从而提升性能。分桶表的性能提升就是：基于分桶列的特定操作，如：过滤、JOIN、分组，均可带来性能提升。

修改表

--表的重命名
alter table score4 rename to score5;
--添加分区
ALTER TABLE score ADD PARTITION (month='201101');
--新分区是空的没数据，需要手动添加或上传数据文件
--修改分区值(修改元数据记录，HDFS的实体文件夹不会改名，但是在元数据记录中是改名了的)
ALTER TABLE score PARTITION (month='202005') RENAME TO PARTITION (month='201105');
--删除分区
ALTER TABLE score DROP PARTITION (month='201105');
--添加新列
ALTER TABLE score ADD COLUMNS (v1 int, v2 string);
--修改列名
ALTER TABLE score CHANGE v1 v1new INT;
--删除表
DROP TABLE score;
--清空表
TRUNCATE TABLE score;

ps:只可以清空内部表

复杂类型操作

array类型：某个列可以分为多个子列，各元素之间逗号分隔
map类型：Key-Value型数据格式
struct类型：可以在一个列中存入多个子列，每个子列允许设置类型和名称

--复杂类型操作——array类型表创建
create table myhive.test_array(name string, work_locations array<string>)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ',';


--map类型
create table myhive.test_map(id int, name string, members map<string,string>, age int)
row format delimited fields terminated by ',' 
COLLECTION ITEMS TERMINATED BY '#' MAP KEYS TERMINATED BY ':';
--查看成员
select id,name,members['father'],members['mother'] from myhive.test_map; 
--取出map全部key,返回类型是array
select map_keys(members)from myhive.test_map;
--取出map全部value,返回类型是array
select map_values(members) from myhive.test_map;
--size查看map的K-V的个数
select size(members) from myhive.test_map;
--查询map的key中有brother的数据
select * from myhive.test_map where array_contains(map_keys(members), 'brother');
--查询map的value中是否包含王林的数据
select * from myhive.test_map where array_contains(map_values(members), '王林');


--struct类型
create table myhive.test_struct(id string, info struct<name:string, age:int>)
row format delimited fields terminated by '#'
COLLECTION ITEMS TERMINATED BY ':';  --表示struct中二级列的数据之间的分隔符
--查询全部
select * from test_struct;
--直接使用列名.子列名 即可从struct中取出子列查询
select ip, info.name,info.age from test_struct;

3、数据查询

基本查询

--查询所有
SELECT * FROM itheima.orders;

--查询单列
SELECT orderid, totalmoney, username, useraddress, paytime FROM itheima.orders;

--查询数据量
SELECT COUNT(*) FROM itheima.orders;

--过滤广东省订单
SELECT * FROM itheima.orders WHERE useraddress LIKE '%广东%';

--找出广东省单笔营业额最大的订单
SELECT * FROM itheima.orders WHERE useraddress like '%广东%' ORDER BY totalmoney DESC LIMIT 1;

分组、聚合


--统计未支付、已支付各自的人数
SELECT ispay, COUNT(*) AS cnt FROM itheima.orders GROUP BY ispay;

--在已付款订单中，统计每个用户最高的一笔消费金额
SELECT userid, MAX(totalmoney) AS max_money FROM itheima.orders WHERE ispay = 1 GROUP BY userid;

--统计每个用户的平均订单消费额
SELECT userid, AVG(totalmoney) FROM itheima.orders GROUP BY userid;

--统计每个用户的平均订单消费额，过滤大于10000的数据
SELECT userid, AVG(totalmoney) AS avg_money FROM itheima.orders GROUP BY userid HAVING avg_money > 10000;

JOIN

--JOIN订单表和用户表，找出用户名
SELECT o.orderid, o.userid, u.username, o.totalmoney, o.useraddress, o.paytime FROM itheima.orders o JOIN itheima.users u ON o.userid = u.userid;

--左外关联，订单表和用户表，找出用户名
SELECT o.orderid, o.userid, u.username, o.totalmoney, o.useraddress, o.paytime FROM itheima.orders o LEFT JOIN itheima.users u ON o.userid = u.userid;

RLIKE正则匹配

--RLIKE正则匹配
--查找广东省的数据  等价与 LIKE '%广东%'
SELECT * FROM itheima.orders WHERE useraddress RLIKE '.*广东.*';
--查找用户地址是：xx省 xx市 xx区的数据
SELECT * FROM itheima.orders WHERE useraddress RLIKE '..省 ..市 ..区';
--查找用户姓为张、王、邓
SELECT * FROM itheima.orders WHERE username RLIKE '[张王邓]\\S+';
--查找手机号符合：188****0*** 规则
SELECT * FROM itheima.orders WHERE userphone  RLIKE '188\\S{4}0\\S{3}';

UNION联合 - 去重

--联合两个查询结果集
SELECT * FROM course WHERE t_id = '周杰轮'
    UNION 
SELECT * FROM course WHERE t_id = '王力鸿'
--UNION默认有去重功能：
--直接联合两个同样的查询结果
SELECT * FROM course
UNION 
SELECT * FROM course
--如果不需要去重效果
SELECT * FROM course
    UNION ALL 
SELECT * FROM course
--UNION写在FROM中
SELECT t_id, COUNT(*) FROM 
(
	SELECT t_id FROM itheima.course WHERE t_id = '周杰轮'
		UNION ALL
	SELECT t_id FROM itheima.course WHERE t_id = '王力鸿'
) AS u GROUP BY t_id;

--用于INSERT SELECT中
CREATE TABLE itheima.course2 LIKE itheima.course;
INSERT OVERWRITE TABLE itheima.course2
    SELECT * FROM itheima.course
	UNION ALL
    SELECT * FROM itheima.course;

数据采样

语法1，基于随机分桶抽样： SELECT ... FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))

y表示将表数据随机划分成y份（y个桶）
x表示从y里面随机抽取x份数据作为取样
colname表示随机的依据基于某个列的值
rand()表示随机的依据基于整行

--   语法1，基于随机分桶抽样：
SELECT username, orderId, totalmoney FROM itheima.orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON username);
SELECT * FROM itheima.orders TABLESAMPLE(BUCKET 1 OUT OF 10 ON rand());

注意：使用colname作为随机依据，则其它条件不变下，每次抽样结果一致使用rand()作为随机依据，每次抽样结果都不同

语法2，基于数据块抽样 SELECT ... FROM tbl TABLESAMPLE(num ROWS | num PERCENT | num(K|M|G));

num ROWS 表示抽样num条数据
num PERCENT 表示抽样num百分百比例的数据
num(K|M|G) 表示抽取num大小的数据，单位可以是K、M、G表示KB、MB、GB

--语法2，基于数据块抽样
SELECT * FROM itheima.orders TABLESAMPLE(100 ROWS);
SELECT * FROM itheima.orders TABLESAMPLE(10 percent);
SELECT * FROM itheima.orders TABLESAMPLE(1K);

注意：使用这种语法抽样，条件不变的话，每一次抽样的结果都一致即无法做到随机，只是按照数据顺序从前向后取。

Virtual Columns虚拟列

Hive目前可用3个虚拟列：

INPUT__FILE__NAME，显示数据行所在的具体文件
BLOCK__OFFSET__INSIDE__FILE，显示数据行所在文件的偏移量
ROW__OFFSET__INSIDE__BLOCK，显示数据所在HDFS块的偏移量

SELECT *, INPUT__FILE__NAME, BLOCK__OFFSET__INSIDE__FILE, ROW__OFFSET__INSIDE__BLOCK FROM itheima.course;

此虚拟列需要设置：SET hive.exec.rowoffset=true 才可使用

4、函数

--查看当下可用的所有函数；
show functions;
--查看函数的使用方式
describe function extended lcase;
--数值函数
--取整函数: round  返回double类型的整数值部分 （遵循四舍五入）
select round(3.1415926);
--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);
--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();
--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);
--求数字的绝对值
select abs(-3);
--得到pi值（小数点后15位精度）
select pi();

--集合函数
--求元素个数
select size(work_location) from test_array;
select map_keys(members)from test_map;
select map_values(members)from test_map;
select * from test_array where ARRAY_CONTAINS(work_location,'tianjin');
select *,sort_array(work_location) from test_array; --排序函数
--取出map的全部key
--类型转换
--转二进制
select binary('hadoop');
--自由转换，类型转换失败报错或返回NULL
select cast('1' as bigint)

--日期函数
--获取当前时间戳
select current_timestamp();
--当前日期
select current_date();
--时间戳转日期
select to_date(current_timestamp());
--年月日季度等
select year('2020-01-11');
select month('2020-01-11');
select day('2020-01-11');
select quarter('2020-01-11');
select dayofmonth('2020-05-11');
select hour('2020-05-11 10:36:59');
select minute('2020-05-11 10:36:59');
select second('2020-05-11 10:36:59');
select weekofyear('2020-05-11 10:36:59');
--日期之间的天数
select datediff('2022-12-31','2019-12-31');
--日期相加、相减
select date_add('2022-12-31',5);
select date_sub('2022-12-31',5);

--条件函数
--条件判断if
select if(truename is null,'不知道名字',truename) from users;
--null 判断
select isnull(truename) from users;
select isnotnull(truename) from users;
--nv1:如果value为null，则返回default_value,否则value
select nvl(truename,'不知道名字') from users;