Hive数据库【操作】+ 【分区】+【分桶】+【查询】+【运算】+【函数】

目录

• 键值对信息
• 数据库表操作
• 内部表操作
• 外部表操作
• 分区表的操作
• 分桶表操作
• 数据查询
• 关系运算
• 数学运算
• 逻辑运算
• 数值计算
• 日期函数

键值对信息

添加数据库的描述信息(添加键值对信息)

create database [数据库] with dbproperties('name'='RenZetong','data'='20200101')
--数据所有者:RenZetong 日期:2020-01-01

查看键值对信息

describe database extended [数据库]

修改键值对信息

alter database [数据库] set dbproperties('name'='RenZetong2')

查看更多详细信息

desc database extended [数据库]

数据库的删除操作

drop database [数据库]   #删除一个空的数据库
drop database [数据库] cascade  #连同数据库下的表一起删除掉 "强制性删除"

数据库表操作

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]

• comment 表示注释，默认不能使用中文
• partitioned by 表示使用表分区，一个表可以拥有多个分区，每一个分区单独存在一个目录下
• clustered by 对于每一个表分区文件，Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织
• sorted by 指定排序字段和排序规则
• row format 指定表文件字段分隔符
• stored as 指定表文件的存储格式，常用格式：sequencefile，textfile，rcfile，如果是纯文本文件，可以使用STORED AS TEXTFILE
• location 指定表文件的存储路径

内部表操作

创建表并指定字段之间的分隔符

create table stu2(id int,name string)
row format
delimited fields
terminated by '/t';  --指定的分隔符

创建表并指定文件的存放路径

create table stu3(id int,name string)
row format
delimited fields
terminated by '	'
location '/user/stu3'; --指定文件存储路径

根据查询结果来创建表

create table stu3 as select * from stu2;

根据已存在的表结构来创建表

create table stu4 like stu2;

查看表结构

desc stu4;

查看表结构详细信息

desc formatted stu2;

删除表

drop table stu4;

外部表操作

外部表的数据一般是用来共享的，所以删除hive表的时候，数据仍然会存放在hdfs中

分别创建老师和学生的外部表，并向表中添加数据

1、创建老师表

create external table teacher (t_id string,t_name string)
row format
delimited fields terminated by '	'

2、创建学生表

create external table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by '	'

将数据加载入表的方式

1、普通加载方式

load data local inpath '/***/***/data.csv' into table ***

2、加载并覆盖方式

load data local inpath '/***/***/data.csv' overwrite into table *** 

3、从hdfs文件系统中向表中加载数据(前提是得将数据上传到hdfs系统中)

hdfs dfs -put ***.csv /hivesource
load data inpath '/hivesource/***.csv' into table ***

分区表的操作

在hive中分区就是分文件夹，把大文件切割划分成一个个的小文件，这样每次操作一个小的文件就会很容易了，例如把一个大数据按照每月或者每天进行分割成一个个的小文件，存放在不同的文件夹中.(多分区就是目录的嵌套)

创建一个表带一个分区的语法

create table score(s_id string,c_id string,s_score int)
partitioned by (month string) --指定分区字段
row format 
delimited fields 
terminated by '	';

创建一个表带多个分区语法

create table score2(s_id string,c_id string,s_score int)
partitioned by(year string,month string,day string)  --指定分区字段
row format
delimited fields
terminated by '	';

给一个表带一个分区的加载数据

load data local inpath '/root/score.csv' into table score partition(month='20180101');

多分区表加载数据

load data local inpath '/root/score.csv' into table score2
partition(year='2018',month='06',day='01');

分区表查询

select * from score where month = '20180102';

多分区表联合查询(使用union all)

select * from score where month='20180102' union all select * from score where month='20180101';

查看分区

show partitions score;

添加分区

alter table score add partition(month='201805');

删除分区

alter table drop partition(month='201806');

表的修复(建立表与数据文件之间的一个关系映射)

msck repair table ***

分桶表操作

分桶就是MapReduce中的分区。

开启hive的分桶功能

set hive.enforce.bucketing=true;

设置Reduce个数

set mapreduce.job.reduces=3;

创建分桶表

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 '	';

数据查询

求总行数

select count(*) from table;

求分数的最大值

select max(s_score) from table;

求分数的最小值

select min(s_score) from table;

求分数的总和

select sum(s_score) from table;

求分数的平均值

select avg(s_score) from table;

关系运算

等值比较

select 1 from data_tb where 1=1;

不等值比较

select 1 from data_tb where 1<>2;

小于比较

select 1 from data_tb where 1 < 2;

小于等于比较

select 1 from data_tb where 1 <= 1

大于比较

select 1 from data_tb where 2>1

大于等于比较

select 1 from data_tb where 1 >=1

空值判断

select 1 from data_tb where null is null

非空值判断

select 1 from data_tb where 1 is not null

like比较

select 1 from data_tb where 'footabll' like 'foot%';
select 1 from data_tb where 'footabll' like '%foot%';

like否定比较

select 1 from data_tb where not 'football' like 'foot%';
select 1 from data_tb where not 'football' like '%foot%';

数学运算

加法操作

select 1+9 from data_tb;

减法操作

select 10-5 from data_tb;

乘法操作

select 40*5 from data_tb;

除法操作

select 40/5 from data_tb;

逻辑运算

逻辑与操作:AND

select 1 from data_tb where 1=1 and 2=2;

逻辑或操作:OR

select 1 from data_tb where 1=2 or 2=2;

逻辑非操作:NO

select 1 from data_tb where not 1=2

数值计算

取整函数:round

select round(3.1415925) from data_tb;
> 3

select round(3.5) from data_tb;
> 4

指定精度取整函数:round

select round(3.1415926,4) from data_tb;
> 3.1416

随机数函数:rand

select rand() from data_tb;
> 0.26472837228287

select rand(100) from data_tb;
> 0.72636263627272

绝对值函数:abs

select abs(-3.9) from data_tb;
> 3.9

日期函数

from_unixtime函数将yyyy/mm/dd类型的日期转换为yyyy-mm-dd日期类型

select from_unixtime(unix_timestamp(date,'yyyy/mm/dd'),'yyyy-mm-dd') from date_1;

时间转日期函数:to_date

select to_date('2020-11-12 10:02:11') from data_tb;
> 2020-11-12

日期转年函数:year

select year('2020-11-12 10:22:22') from data_dt;
> 2011

日期转月函数:month

select month('2020-11-12') from data_dt;
> 12

日期转天函数:day

select day('2020-11-12') from data_dt;
> 12

日期转小时函数:hour

select hour('2020-01-02 20:22:12') from data_dt;
> 20