hive操作

浪法师
311 阅读5分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第15天，点击查看活动详情

操作步骤

  • 创建数据库
// 创建数据库
create database if not exists myhive;
// 使用数据库
use myhive;

// 指定在hdfs存放的位置
create database myhive2 location '/myhive2';
  • 查看数据库详细信息
// 查看数据库详细信息
desc database myhive; // location: hdfs://min-node1:8020/user/hive/warehouse/myhive.db
desc database myhive2; // location: hdfs://min-node1:8020/myhive2
  • 删除数据库
// 删除 数据库
-- 删除一个空数据库，如果数据库下面有数据表，那么就会报错
drop database myhive2;
-- 强制删除数据库，包含数据库下面的表一起删除
drop database myhive2 cascade ;

内部表

-- 内部表 没有被external修饰的是内部表
-- 删除内部表会直接删除元数据（metadata）及存储数据，
use myhive;
create table if not exists user_test(
    id int,
    name string,
    age int
);
insert into user_test values (1,'zhangsan', 23);
insert into user_test values (1,'zhangsan', 23);
select * from user_test; //  1  zhangsan  23
  • 指定字段之间的非分隔符
-- 指定字段之间的非分隔符
create table if not exists user_test2(
    id int,
    name string
)
row format delimited fields terminated by '\t';
  • 根据结构创建表 并填充数据
-- 根据结构创建表 并填充数据
create table user_test3 as select * from user_test;
select * from user_test3;
  • 只复制表结构
-- 只复制表结构
create table user_test4 like user_test2;
desc user_test4;

image.png

  • 查询表的类型
-- 查询表的类型
desc formatted user_test4;

image.png

外部表

-- 外部表  删除外部表但是数据不会删除
-- 外部表因为是指定其他的hdfs路径的数据加载到表当中来，
-- 所以hive表会认为自己不完全独占这份数据，所以删除hive外部表的时候，数据仍然存放在hdfs当中，不会删掉。
create external table user_1 (
    id int,
    name string
)
row format delimited fields terminated by '\t';
-- 从本地导入数据
load data local inpath '/export/data/hivedatas/sample.txt' into table user_1;
-- 在远端导入数据， 覆盖数据
load data inpath './sample.txt' overwrite into table user_1;
select * from user_1;

image.png

Array类型

-- Array类型
-- Array是数组类型，Array中存放相同类型的数据
create external table array_location (
    name string,
    work_locations array<string>
)
row format delimited fields terminated by '\t'
collection items terminated by ',';

load data local inpath '/export/data/hivedatas/locations.txt' into table array_location;
select * from array_location;

image.png

  • 查询work_locations的第二个元素
-- 查询work_locations的第二个元素
select name, work_locations[1] from array_location;

image.png

  • 查询location数组中元素的个数
-- 查询location数组中元素的个数
select name, size(work_locations) location_size from array_location;

image.png

  • 查询location数组中包含tianjin的信息
-- 查询location数组中包含tianjin的信息
select * from array_location where array_contains(work_locations, 'shanghai');

image.png

map 类型

-- map 类型
-- map就是描述key-value数据
create external table map_txt (
    id int,
    name string,
    members map<string, string>,
    age int
) row format delimited fields terminated by ','
collection items terminated by '#'
map keys terminated by ':';
load data local inpath '/export/data/hivedatas/map.txt' into table map_txt;
select * from map_txt;

image.png

  • 根据键找对应的值
-- 根据键找对应的值
select id, name,
members['father'] father,
members['mother'] mother, 
members['brother'] brother,
members['sister'] sister, age from map_txt;

image.png

  • 获取所有的键
-- 获取所有的键
select id, name, map_keys(members) as relation from map_txt;

image.png

  • 获取所有的值
--获取所有的值
select id,name,map_values(members) as relation from map_txt;

image.png

  • 获取键值对个数
-- 获取键值对个数
select id, name, size(members) num from map_txt;

image.png

  • 获取有指定key的数据
-- 获取有指定key的数据
select * from map_txt where array_contains(map_keys(members), 'sister');

image.png

  • 查找包含sister这个键的数据，并获取sister键对应的值
-- 查找包含sister这个键的数据，并获取sister键对应的值
select id, name, members['sister'] sister from map_txt where array_contains(map_keys(members),'sister');

image.png

struct类型

-- struct类型
create external table struct_test (
    id string,
    info struct<name:string,age:int>
)
row format delimited fields terminated by '#'
collection items terminated by ':';

load data local inpath '/export/data/hivedatas/struct.txt' into table struct_test;
select * from struct_test;

image.png

  • 根据struct来获取指定的成员的值
-- 根据struct来获取指定的成员的值
alter table struct_test change id ip string; -- 修改字段名
select ip,info.name from struct_test;

image.png

分区表

  • 静态分区 - 单级分区
-- 分区表
--- 静态分区
-- 单级分区
-- partitioned by (字段名 类型) 分类的标准就是分区字段，可以一个，也可以多个。
drop table score;
create table score(
    sid string,
    cid string,
    sscore int
)
partitioned by (dt string)
row format delimited fields terminated by '\t';
load data local inpath '/export/data/hivedatas/score.txt' into table score partition (dt='2022-10-24');
select * from score;

load data local inpath '/export/data/hivedatas/score2.txt' into table score partition (dt='2022-10-25');
select * from score where dt='2022-10-25';

image.png

  • 多级分区
-- 多级分区
drop table score2;
create table score2 (
    sid string,
    cid string,
    sscore int
)
partitioned by (year string, month string,day string)
row format delimited fields terminated by '\t';
load data local inpath '/export/data/hivedatas/score2.txt' into table score2
    partition (year='2022',month='10',day='25');

select * from score2;
select * from score2 where year='2022' and month='10' and day='24';

image.png

  • 查看分区
-- 查看分区
show partitions score2;

image.png

  • 添加一个分区
-- 添加一个分区
alter table score add partition (dt='2022-10-23');
show partitions score;

image.png

  • 删除分区
-- 删除分区
alter table score drop partition (dt='2022-10-23');
show partitions score;

image.png

  • 动态分区 - 一级分区
---- 动态分区
set hive.exec.dynamic.partition=true; -- 开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict; --- 设置模式非严格模式

--- 一级分区
--- 普通表
create table test1 (
    id int,
    date_val string,
    name string,
    score int
)
row format delimited fields terminated by '\t';
-- 添加数据
load data local inpath '/export/data/hivedatas/partition.txt' into table test1;
select * from test1;
-- 创建分区表
create table test2 (
    id int,
    name string,
    score int
)
partitioned by (dt string)
row format delimited fields terminated by ',';
--- 将普通表中的数据插入分区表
insert overwrite table test2 partition (dt)
select id,name,score,date_val from test1;

select * from test2;


create table test2_1 (
    id int,
    date_val string,
    name string,
    score int
)
row format delimited fields terminated by '\t';
-- 添加数据
load data local inpath '/export/data/hivedatas/partition.txt' into table test2_1;
-- 创建分区表
create table test2_2 (
    id int,
    date_val string,
    name string,
    score int
)
partitioned by (month string)
row format delimited fields terminated by ',';

insert overwrite table test2_2 partition (month)
select id,name,date_val,score,substring(date_val,1,7) from test2_1;

select * from test2_2;

image.png

  • 多级分区
---- 多级分区
create table test3_1 (
    id int,
    date_val string,
    name string,
    score int
)
row format delimited fields terminated by '\t';
load data local inpath '/export/data/hivedatas/partition.txt' into table test3_1;
create table test3_2 (
    id int,
    date_val string,
    name string,
    score int
)
partitioned by (y string, m string, d string)
row format delimited fields terminated by ',';

insert overwrite table test3_2 partition (y, m, d)
select id,name,date_val,score,substring(date_val,1,4),substring(date_val,6,2),substring(date_val,9,2) from test3_1;

select * from test3_2;

image.png

分桶表

-- 分桶表
-- 开启hive的桶表功能
set hive.enforce.bucketing=true;
-- 设置reduce的个数
set mapreduce.job.reduces=3; -- #该参数在Hive2.x版本之后不起作用

create table course
(
    cid   string,
    cname string,
    tid   string
)
    clustered by (cid) into 3 buckets
    row format delimited fields terminated by '\t';
-- 桶表的数据加载，由于桶表的数据加载通过hdfs  dfs  -put文件或者通过load  data均不好使，
-- 只能通过insert  overwrite
-- 创建普通表：
create table course_common
(
    cid    string,
    cname string,
    tid    string
) row format delimited fields terminated by '\t';
-- 普通表中加载数据
load data local inpath '/export/data/hivedatas/course.txt' into table course_common;

select * from course_common;

-- 通过insert  overwrite给桶表中加载数据
insert overwrite table course select * from course_common cluster by(cid);

select * from course;

image.png

avatar
文章
阅读
粉丝