黑洞

这里藏着一些独特的想法

0%

Hive常用SQL之DDL

忽略异常

1
2
3
4
5
--如果不存在则创建表
create table if not exists t_1(id int, firstname string, ...);

--如果存在则删除表
drop table if exists t_1;

指定分隔符

1
2
3
4
5
6
7
8
9
10
create table t_hot_hero_skin_price(
id int,
name string,
win_rate int,
skin_price map<string,int>
)
row format delimited
fields terminated by ',' --字段之间分隔符
collection items terminated by '-' --集合元素之间分隔符
map keys terminated by ':'; --集合元素kv之间分隔符;

如果没有指定字段之间的分隔符,Hive默认使用\001作为分隔符。

在清洗数据时应该尽量使用\001作为分隔符,避免与字段值的冲突,造成解析错误。

json格式数据处理

在hive中,没有json类的存在,一般使用string类型来修饰,叫做json字符串。我们可以使用使用JsonSerDe类进行解析,在加载json数据到表中的时候完成解析动作。

1
2
3
4
5
6
7
8
9
10
11
-- 建表的时候直接使用JsonSerDe解析
CREATE TABLE tb_json_test2 (
device string,
deviceType string,
signal double,
`time` string
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;

load data local inpath '/root/hivedata/device.json' into table tb_json_test2;

外部表

1
create external table t_1(id int, firstname string, ...)

当删除外部表时,只会删除表的元数据,实际文件将在HDFS上保留。

查看一个表是否为外部表可以通过以下命令

1
2
3
4
5
desc formatted t_1 --查看元数据
show create t_1; --或者查看建表语句

MANAGED_TABLE 内部表 受控表
EXTERNAL_TABLE 外部表

指定表数据路径

存储路径由 hive.metastore.warehouse.dir 这个属性指定
默认值是:/user/hive/warehouse

1
2
3
4
5
6
7
8
--在建表的时候 可以使用location关键字指定表的路径在HDFS任意位置
create table t_team_ace_player_location(
id int,
team_name string,
ace_player_name string)
location '/data'; --使用location关键字指定本张表数据在hdfs上的存储路径

--此时再上传数据 就必须上传到指定的目录下 否则就解析映射失败了

在实际开发中,最好集中维护管理Hive表数据,避免文件在HDFS随意存放。

分区表

  • 分区表是一种优化表,建表的时候可以不使用。但是当创建分区表之后,使用分区字段查询可以减少全表扫描,提高查询的效率。

  • 企业中常用的分区字段

    • 地域维度:省,市
    • 时间维度:day,month,year

创建分区表

分区表的字段名不能与表的字段名重复!

1
2
3
4
5
6
7
8
9
10
11
12
13
create table t_all_hero_part(
id int,
name string,
hp_max int,
mp_max int,
attack_max int,
defense_max int,
attack_range string,
role_main string,
role_assist string
) partitioned by (role string) --这里以role_main这个字段分类
row format delimited
fields terminated by "\t";

加载分区表

  • 静态加载(手动)
1
2
3
4
5
6
7
--local 表示从本地文件系统加载,而非HDFS!
load data local inpath '/root/hivedata/archer.txt' into table t_all_hero_part partition(role='sheshou');
load data local inpath '/root/hivedata/assassin.txt' into table t_all_hero_part partition(role='cike');
load data local inpath '/root/hivedata/mage.txt' into table t_all_hero_part partition(role='fashi');
load data local inpath '/root/hivedata/support.txt' into table t_all_hero_part partition(role='fuzhu');
load data local inpath '/root/hivedata/tank.txt' into table t_all_hero_part partition(role='tanke');
load data local inpath '/root/hivedata/warrior.txt' into table t_all_hero_part partition(role='zhanshi');
  • 动态加载

当分类过多(比如:所有国家)时,一个个手动加载显然不现实,这时候就需要使用动态加载。

原理:查询全表并选择指定字段作为分区参考,插入到新建的分区表中。

首先设置允许动态分区、设置动态分区模式

1
2
3
4
5
6
7
8
--动态分区
set hive.exec.dynamic.partition=true; --注意hive3已经默认开启了
set hive.exec.dynamic.partition.mode=nonstrict;

模式分为strict严格模式 nonstrict非严格模式
严格模式要求 分区字段中至少有一个分区是静态分区。
partition(country="china",province) --第一个分区写死了(静态) 符合严格模式。
partition(country,province) --两个分区都是动态确定的 需要非严格模式

查询返回的内容,其字段类型、顺序、个数要和待插入的表保持一致。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--创建一张新的分区表 t_all_hero_part_dynamic
create table t_all_hero_part_dynamic(
id int,
name string,
hp_max int,
mp_max int,
attack_max int,
defense_max int,
attack_range string,
role_main string,
role_assist string
) partitioned by (role string)
row format delimited
fields terminated by "\t";

--执行动态分区插入 --注意 分区值并没有手动写死指定
insert into table t_all_hero_part_dynamic partition(role)
select tmp.*,tmp.role_main from t_all_hero tmp;

--查询验证结果
select * from t_all_hero_part_dynamic;

多重分区表

分区表也支持基于多个字段进行分区

1
partitioned by(字段1 ,字段2....)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--以国家、省份 创建分区表
create table t_user_double_p(
id int,
name string,
country string
) partitioned by(guojia string,sheng string)
row format delimited
fields terminated by ',';

--加载数据到多分区表中
load data local inpath '/root/hivedata/china_sh.txt' into table t_user_double_p partition(guojia="china",sheng="shanghai");

load data local inpath '/root/hivedata/china_sz.txt' into table t_user_double_p partition(guojia="china",sheng="shenzhen");

load data local inpath '/root/hivedata/usa_texas.txt' into table t_user_double_p partition(guojia="usa",sheng="texas");

--查询来自于中国深圳的用户有哪些?
select * from t_user_double_p where guojia="china"and sheng="shenzhen";

查看分区

1
2
3
4
show partitions t_users;

--查看分区下面的分区
show partitions t_china_gdp.province_partition;

添加、删除分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--通过hive添加
load data local inpath '/root/hivedata/students.txt' into table t_user_province partition(province ="SH");

--手动创建分区文件夹时绕开了hive,发现hive无法识别新分区,要添加分区元数据
ALTER TABLE t_user_province ADD PARTITION (province='XM') location '/user/hive/warehouse/mydb.db/t_user_province/province=XM';

----此外还支持一次添加多个分区
ALTER TABLE table_name ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808' PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';

--重命名分区
ALTER TABLE t_user_province PARTITION (province ="SH") RENAME TO PARTITION (province ="Shanghai");

--删除分区
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us');

--不经过回收站,直接删除数据
ALTER TABLE table_name DROP [IF EXISTS] PARTITION (dt='2008-08-08', country='us') PURGE;

分桶表

分桶表也是一种优化表,可以提高join查询(减少笛卡尔积的数量)和抽样查询的效率。

分桶表的字段必须是表中已有的字段。

  • 原理:hashfunc(分桶字段) % N bucket 余数相同的来到同一个桶中
  1. 如果分桶的字段是数字类型的字段,hashfunc(分桶字段)=分桶字段本身
  2. 如果分桶的字段是字符串或者其他字段,hashfunc(分桶字段) = 分桶字段.hashcode
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CLUSTERED BY xxx INTO N BUCKETS
--根据xxx字段把数据分成N桶
--根据表中的字段把数据文件成为N个部分

t_user(id int,name string)
--1、根据谁分?
CLUSTERED BY xxx --xxx必须是表中的字段
--2、分成几桶?
N BUCKETS --N的值就是分桶的个数
--3、分桶的规则?
clustered by id into 3 bucket

hashfunc(分桶字段) % N bucket 余数相同的来到同一个桶中
1、如果分桶的字段是数字类型的字段,hashfunc(分桶字段)=分桶字段本身
2、如果分桶的字段是字符串或者其他字段,hashfunc(分桶字段) = 分桶字段.hashcode

创建分桶表

1
2
3
4
5
6
7
8
9
10
--根据state州分为5桶 每个桶内根据cases确诊病例数倒序排序
CREATE TABLE t_usa_covid19_bucket(
count_date string,
county string,
state string,
fips int,
cases int,
deaths int)
CLUSTERED BY(state)
sorted by (cases desc) INTO 5 BUCKETS;--指定每个分桶内部根据 cases倒序排序

加载分桶表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--step1:开启分桶的功能 从Hive2.0开始不再需要设置
set hive.enforce.bucketing=true;

--step2:把源数据加载到普通hive表中
CREATE TABLE t_usa_covid19(
count_date string,
county string,
state string,
fips int,
cases int,
deaths int)
row format delimited fields terminated by ",";

--将源数据上传到HDFS,t_usa_covid19表对应的路径下
hadoop fs -put us-covid19-counties.dat /user/hive/warehouse/covid19.db/t_usa_covid19

--step3:使用insert+select语法将数据加载到分桶表中
insert into t_usa_covid19_bucket select * from t_usa_covid19;

select * from t_usa_covid19_bucket;

分桶表的使用

1
2
3
4
5
--基于分桶字段state查询来自于New York的数据
--不再需要进行全表扫描过滤
--根据分桶的规则hash_function(New York) % 5计算出分桶编号
--查询指定分桶里面的数据 就可以找出结果 此时是分桶扫描而不是全表扫描
select * from t_usa_covid19_bucket where state="New York";

其他语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--描述数据库信息
describe database database_name;
describe database extended database_name;
desc database extended database_name;

--查询指定表的元数据信息
desc formatted database_name.table_name;

--查询创建表的语句,用于复制、重建表。
show create table table_name;

--更改表名
ALTER TABLE table_name RENAME TO new_table_name;

--当数据库没有表时
DROP DATABASE [IF EXISTS] database_name;

--当数据库下有表时,强制删除,慎用!
DROP DATABASE database_name CASCADE;

--显示当前支持的所有自定义和内置的函数
show functions;
如果觉得文章写得不错或对您有帮助,请我喝杯柠檬茶吧!