hive数据库表基本操作

查看所有的数据库

show databases;

创建数据库

create database if not exists myhive;
use myhive;

说明：hive的表的存放位置模式是由hive-site.xml当中的一个数据指定的

<name>hive.metastore.warehouse.dir<\name>
<value>/user/hive/warehouse<\value>

创建数据库并指定位置

create database myhive2 location ‘/myhive2’;

设置数据库键值信息

数据库可以有一些描述性的键值对信息，在创建时添加
create database foo with dbproperties(‘owner’=‘duck’,‘date’=‘2021-04-07’);

查看数据库键值对信息

describe database extended foo;

修改数据库的键值对信息

alter database foo set dbproperties(‘owner’=‘dake’);

查看数据库更多详细信息

desc database foo extended myhive;

删除数据库

删除一个空的数据库，如果数据库中有表，就会报错
drop database myhive;
强制删除数据库，包含库中的表一起删除
drop database myhive cascade;

从数据库中模糊查询表

show tables like ‘name’;

建表语法

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]
说明：
external：可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（location），hive创建内部表时，会将数据移动到数据仓库指定的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变，在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据
comment : 表示注释默认不能使用中文

【内部表操作】

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

create table if not exist per(id int,name string) row format delimited fields terminated by ‘\t’;

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

create table if not exist per(id int,name string) row format delimited fields terminated by ‘\t’ location ‘/user/per’;

根据查询结果创建一张表

create table per1 as select * from per; #通过复制表结构和表内容创建表

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

create table per2 like per; #只复制表结构

查询表的详细信息

desc formatted tablename;

删除表

drop table tablename;

修改表结构

重命名:
alter table score4 rename to score5;
增加/修改列信息:
1.查询表结构
desc score5;
2.添加列
alter table score5 add columns (mycol string,mysco int);
3.更新列
alter table score5 change column 列原始名 列新名 int(列新的类型);
4.删除表
drop table tablename;

创建视图

CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], …) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, …)] AS SELECT；

删除视图

DROP VIEW view_name;

【外部表操作】

外部表说明

外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs中，不会删掉

内部表和外部表的使用场景

外部表对HDFS文本文件做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过select + insert进入内部表

操作案例
创建老师与学生外部表，并向表中加载数据

创建老师表

create external table teacher (t_id string,t_name string) row format delimited fields terminated by ‘\t’;

创建学生表

create external table teacher (s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by ‘\t’;

加载数据

load data local inpath ‘/export/student.csv’ into table student;

加载数据并覆盖已有数据

load data local inpath ‘/export/student.csv’ overwrite into table student;

从hdfs文件系统中向表中加载数据（需要提前将数据上传到hdfs文件系统）

load data inpath ‘hivedatas/teacher.csv’ into table teacher;

Hive表查询语法分析

SELECT [ALL | DISTINCT] SELECT_EXPR,SELECT_EXPR,…
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY
ORDER BY col_list]
]
[LIMIT NUMBER]

1. order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模比较大时，需要较长的计算时间。
2. sort by 不是全部排序，其在数据进入reducer前完成排序。因此，如果用sort by 进行排序，并且设置mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出有序，不保证全局有序。
3. distribute by (字段)根据指定的字段将数据分到不同的reducer,且分发算法是hash散列。
4. cluster by(字段)除了具有distribute by 的功能外，还会对该字段进行排序
   因此，如果distribute 和sort字段是同一个时，此时，cluster by = distribute by + sort by

常用函数

求总行数(count)
select count() from score;
求分数的最大值(max)
select max() from score;
求分数的最小值(min)
select min() from score;
** 求分数的总和(sum)**
select sum() from score;
求分数的平均值(avg)
select avg(*) from score;

sort by: 每个MapReduce内部进行排序，对全局结果集来说不是排序

设置reduce个数

set mapreduce.job.reduce=3;

查看设置reduce个数

set mapreduce.job.reduces;

查询成绩按成绩降序排序

select * from score sort by s_score;

将查询结果导入到文件中（按照成绩降序排序）

insert overwrite local directory ‘/home/hivedata/sort’ select from score sort by s_score;

分区排序（DISTRIBUTE BY）

Distribute By: 类似MR中partition,进行分区，结合sort by 使用
注意：Hive要求DISTRIBUTE BY 语句要写在SORT BY 语句之前
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute的效果。
实例操作：先按学生id进行分区，再按照学生成绩进行排序
1.设置reduce的个数，将对应的s_id划分到对应的reduce当中去
set mapreudce.job.reduce=7;
2.通过distribute by 进行数据的分区
insert overwrite local directory ‘/home/qchen/hivedata/sort’ select * from score distribute by s_id sort by s_score;

CLUSTER BY

当distribute by和 sort by字段相同时，可以使用cluster by方式
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ACS或者DESC。
以下两种写法等价
select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;