Hive基本操作

 

目录

一、Hive创建数据库与数据表

1.1.数据库相关操作

1.1.1.创建数据库

1.1.2.创建数据库并指定HDFS存储路径

1.1.3.修改数据库

1.1.4.查看数据库详情信息

1.1.5.删除数据库

1.2.数据表相关操作

1.2.1.创建表语法

 1.2.2.管理表（内部表）

 1.2.3.外部表

 1.2.4.分区表

1.2.5.分桶表

1.2.6.编辑hive表

1.2.7.删除表

1.2.8.表加载数据

 1.2.9.表数据导出

1.2.10.清空表数据

二、Hive查询语法

2.1.语法结构

2.2.select

2.2.1.全表查询

2.2.2.选择特定列查询

2.2.3.设置列别名

2.3.limit

2.4.where

2.5.like和rlike

2.6.join

2.6.1.等值join

2.6.2.表的别名

2.6.3.内连接（inner join）

2.6.4.左外连接（left outer join）

2.6.5.右外连接（right outer join）

2.6.6.满外连接（full outer join）

2.6.6.多表连接

2.7.比较运算符（between、in、is null）

2.8.逻辑运算符（and、or、not）

2.9.分组

2.9.1.group by

2.9.2.having

2.10.排序

2.10.1.全局排序（order by）

2.10.2.按照别名排序

2.10.3.多个列排序

2.10.4.每个MapReduce内部排序（sort by）局部排序

2.10.5.分区排序（distribute by）

2.10.6.cluster by

2.11.常用函数

2.11.1.count()

2.11.2.max()

2.11.3.min()

2.11.4.sum()

2.11.5.avg()

---

一、Hive创建数据库与数据表

1.1.数据库相关操作

1.1.1.创建数据库

语法：

create database if not exists 数据库名;

1.1.2.创建数据库并指定HDFS存储路径

语法：

create database if not exists 数据库名 location 'HDS存储路径';

示例：

create database if not exists mytestdatabase location '/mytestdatabase';

注意：

hive表的存放模式由hive-site.xml文件中的一个属性指定

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

<value>/user/hive/warehouse</value>

1.1.3.修改数据库

语法：

alter database 数据库名 set dbproperties('属性键'='属性值');

示例：

##修改数据库的创建日期
alter database mytestdatabase set dbproperties('createtime'='20201120');

 注意：

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

1.1.4.查看数据库详情信息

##查看数据库基本信息

desc database 数据库名;

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

desc database extended 数据库名;

1.1.5.删除数据库

##删除空数据库（如果数据库下有表执行该命令会报错，需要先删除该数据库下的所有表）

drop database 数据库名;

##删除非空数据库<强制删除数据库>（包含数据库下的表一同删除。危险动作，不要轻易执行！！！）

drop database 数据库名 cascade;

1.2.数据表相关操作

1.2.1.创建表语法

语法：

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]

说明：

1、 CREATE TABLE

创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

2、 EXTERNAL

该关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；

若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

3、 LIKE

允许用户复制现有的表结构，但是不复制数据。

4、 ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive通过 SerDe 确定表的具体的列的数据。

5、 STORED AS

SEQUENCEFILE|TEXTFILE|RCFILE

如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

6、CLUSTERED BY

对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

把表（或者分区）组织成桶（Bucket）有两个理由：

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

 1.2.2.管理表（内部表）

1.2.2.1.Hive建表时的字段类型

分类	类型	描述	字面量示例
原始类型	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
	decimal	任意精度的带符号小数	1.0
	string	动态字符串	“a”,‘b’
	varchar	可设置长度的字符串	“a”,‘b’
	char	固定长度的字符串	“a”,‘b’
	binary	字节数组	无法表示
	timestamp	时间戳，毫秒值精度
	date	日期	‘2021-09-18’
	interval	时间频率间隔
复杂类型	array	有序的同类型的集合	array(1,2)
	map	key-value，key必须为原始类型，value可以任意类型	map('a','b',1,2)
	struct	字段集合，类型可以不同
	union	在有限取值范围内的一个值

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

 语法：

create table if not exists '表名'(字段1 字段1类型 ,字段2 字段2类型,字段n 字段n类型,......) row format delimited fields terminated by '分隔符' stored as '文件存储格式' location '文件存储地址';

示例：

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

1.2.2.3.根据查询结果创建表

create table if not exists 新表 as select * from 旧表;

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

create table if not exists 新表 like 旧表;

1.2.2.5.查询表的类型

desc formatted 表名;

 1.2.2.6.export导出与import导入表数据

 示例：

create table techer2 like techer;

export table techer to '/export/techer';

import table techer2 from '/export/techer';

 1.2.3.外部表

1.2.3.1.外部表说明

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

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

示例：

<