Hive快速入门系列(8) | Hive的基本操作(不定期更新~)

本次博主为大家带来的是Hive的基本操作。

一. 创建数据库与创建数据库表

1.1 创建数据库

1. 创建数据库

代码语言：javascript复制

//用户可以用 IF NOT EXISTS 选项来忽略这个异常。
 create database [ if not exists ] myhive ;

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

代码语言：javascript复制

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

2. 创建数据库并指定hdfs存储位置

代码语言：javascript复制

create database myhive2 location '/myhive2';

3. 修改数据库可以使用alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置

代码语言：javascript复制

alter  database  myhive2  set  dbproperties('createtime'='202004090');

4. 查看数据库详细信息

①查看数据库基本信息

代码语言：javascript复制

desc  database  myhive2;

②查看数据库更多详细信息

代码语言：javascript复制

desc database extended  myhive2;

5. 删除数据库

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

代码语言：javascript复制

drop  database  myhive2;

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

代码语言：javascript复制

drop  database  myhive  cascade;

包含数据库下面的表一起删除; 不要执行,危险动作

6. 数据库切换

代码语言：javascript复制

 use myhive(自己的数据库名称);

二. Hive建表时候的字段类型

https://cwiki.apache.org/confluence/display/Hive/LanguageManual Types

分类	类型	描述	字面量实例
原始类型	BOOLEAN	true/false	TRUE
	TINYINT	1字节的有符号整数 -128~127	1Y
	SMALLINT	2个字节的有符号整数，-32768~32767	1S
	INT	4个字节的带符号整数	1
	BIGINT	8字节带符号整数	1L
	FLOAT	4字节单精度浮点数1.0
	DOUBLE	8字节双精度浮点数	1.0
	DEICIMAL	任意精度的带符号小数	1.0
	STRING	字符串，变长	“a”,’b’
	VARCHAR	变长字符串	“a”,’b’
	CHAR	固定长度字符串	“a”,’b’
	BINARY	字节数组	无法表示
	TIMESTAMP	时间戳，毫秒值精度	122327493795
	DATE	日期	‘2020-04-29’
	INTERVAL	时间频率间隔
复杂类型	ARRAY	有序的的同类型的集合	array(1,2)
	MAP	key-value,key必须为原始类型，value可以任意类型	map(‘a’,1,’b’,2)
	STRUCT	字段集合,类型可以不同	struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)
	UNION	在有限取值范围内的一个值	create_union(1,’a’,63)

三. 数据库表的基本操作

3.1 内部表

创建基本数据表(内部表):

代码语言：javascript复制

create table tableName(字段名称 字段类型，字段名称 字段类型)
ROW FORMAT DELIMITED IELDS TERMINATED BY char(char分隔符)
指定数据中字段与字段的分隔符 ‘t’ 或 ‘,’ 或 ‘|’ 或其他

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

代码语言：javascript复制

create  table if not exists stu2(id int ,name string) row format delimited fields terminated by 't' stored as textfile location '/user/stu2';

2. 根据查询结果创建表

代码语言：javascript复制

create table stu3 as select * from stu2;

3. 根据已经存在的表结构创建表

代码语言：javascript复制

create table stu4 like stu2;

4. 查询表的类型

代码语言：javascript复制

desc formatted  stu2;

3.2 创建外部数据表

1. 外部表说明

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

2. 管理表和外部表的使用场景

每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT INSERT进入内部表。

3. 操作实例

1. 分别创建老师与学生表外部表，并向表中加载数据 create EXTERNAL table tableName(字段名称字段类型，字段名称字段类型) 建外部表需要指定数据的存储路径。通过LOCATION进行指定。

①创建老师表：

代码语言：javascript复制

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

②创建学生表：

代码语言：javascript复制

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

2. 从本地文件系统向表中加载数据

代码语言：javascript复制

load data local inpath ‘文件路径’ into table 表名;

3. 加载数据并覆盖已有数据

代码语言：javascript复制

load data local inpath ‘文件路径’ overwrite into table 表名;

4. 从hdfs文件系统向表中加载数据（需要提前将数据上传到hdfs文件系统，其实就是一个移动文件的操作）

代码语言：javascript复制

cd /export/servers/hivedatas
hdfs dfs -mkdir -p /hivedatas
hdfs dfs -put techer.csv /hivedatas/
load data inpath '/hivedatas/techer.csv' into table techer;

如果删掉student表，hdfs的数据仍然存在，并且重新创建表之后，表中就直接存在数据了,因为我们的student表使用的是外部表，drop table之后，表当中的数据依然保留在hdfs上面了

3.3 创建分区表

在大数据中，最常用的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天，或者每小时进行切分成一个个的小的文件，这样去操作小的文件就会容易得多了。

企业常见的分区规则：按天进行分区（一天一个分区）

1. 创建分区表语法

代码语言：javascript复制

create table score(s_id string,c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by 't';

2. 创建一个表带多个分区

代码语言：javascript复制

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

3. 加载数据到分区表中

代码语言：javascript复制

load data local inpath '/export/servers/hivedatas/score.csv' into table score partition (month='201806');

4. 加载数据到一个多分区的表中去

代码语言：javascript复制

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

5. 多分区联合查询使用union all来实现

代码语言：javascript复制

select * from score where month = '201806' union all select * from score where month = '201806';

6. 查看分区

代码语言：javascript复制

show  partitions  score;

7. 添加一个分区

代码语言：javascript复制

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

8. 同时添加多个分区

代码语言：javascript复制

alter table score add partition(month='201804') partition(month = '201803');

注意：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹

9. 删除分区

代码语言：javascript复制

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

特别强调: 分区字段绝对不能出现在数据库表已有的字段中!

作用: 将数据按区域划分开，查询时不用扫描无关的数据，加快查询速度。

3.4 创建分桶表

是在已有的表结构之上新添加了特殊的结构。

将数据按照指定的字段进行分成多个桶中去，说白了就是将数据按照字段进行划分，可以将数据按照字段划分到多个文件当中去

1. 开启hive的桶表功能

代码语言：javascript复制

set hive.enforce.bucketing=true;

2. 设置reduce的个数

代码语言：javascript复制

set mapreduce.job.reduces=3;

3. 创建通表

代码语言：javascript复制

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

桶表的数据加载，由于通标的数据加载通过hdfs dfs -put文件或者通过load data均不好使，只能通过insert overwrite

创建普通表，并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去

4. 创建普通表

代码语言：javascript复制

create table course_common (c_id string,c_name string,t_id string) row format delimited fields terminated by 't';

5. 普通表中加载数据

代码语言：javascript复制

load data local inpath '/export/servers/hivedatas/course.csv' into table course_common;

6. 通过insert overwrite给桶表中加载数据

代码语言：javascript复制

insert overwrite table course select * from course_common cluster by(c_id);

特别强调: 分桶字段必须是表中的字段。

分桶逻辑: 对分桶字段求哈希值,用哈希值与分桶的数量取余,余几,这个数据就放在哪个桶内。

3.5 修改表

1. 表重命名

代码语言：javascript复制

	alter  table  old_table_name  rename  to  new_table_name;
	把表score4修改成score5
	alter table score4 rename to score5;

2. 增加/修改列信息

1. 查询表结构

代码语言：javascript复制

desc score5;

2. 添加列

代码语言：javascript复制

alter table score5 add columns (mycol string, mysco string);

3. 查询表结构

代码语言：javascript复制

desc score5;

4. 更新列

代码语言：javascript复制

alter table score5 change column mysco mysconew int;

5. 查询表结构

代码语言：javascript复制

desc score5;

3. 删除表

代码语言：javascript复制

drop table score5;

本次的分享就到这里了

node.js 大数据数据库 hive sql

0 人点赞