Hive基本操作

基本概念

Hive是基于Hadoop的一个【数据仓库工具】，可以将结构化和半结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能。

介绍

Hive本质是将SQL转换为MapReduce的任务进行运算，底层由HDFS来提供数据存储，简单来说Hive可以理解为一个将SQL转换为MapReduce的任务的工具，甚至可以说Hive就是MapReduce客户端。

特点

• 可扩展性，可以自由的扩展集群的规模，一般情况下不需要重启服务。
• 延伸性，Hive支持自定义函数。
• 容错，即使节点出现错误，SQL仍然可以完成执行。

优缺点

优点

• 操作接口采用类SQL语法，提供快速开发的能力(简单、容易上手)。
• 避免了去写MapReduce，减少开发人员的学习成本。
• Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合。如果是实时可以建议使用Flink。
• Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高。
• Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。
• 集群可自由拓展并且具有良好的容错性，节点出现问题SQL仍可完成执行。

缺点

• Hive的HQL表达能力有限
  • 代式算法无法表达。
  • 数据挖掘方面不擅长。
• Hive的效率比较低
  • Hive自动生成的MapReduce作业，通常情况下不够智能化。
  • Hive调优比较困难，粒度较粗。

架构

client

Hive允许clien连接的方式有三个Cli(Hive Shell)、JDBC/ODBC(Java访问hive)、WEBUI(浏览器访问Hive)。JDBC访问时中间件Thrift软件框架，跨语言服务开发。DDL、DQL、DML，整体仿写一套SQL语句。

Metastore

元数据包括表名、表所属的数据库(默认是default)、表的拥有者、列/分区字段、表的类型 (是否是外部表)、表的数据所在目录等。

• 一般需要借助于其他的数据载体(数据库)。
• 主要用于存放数据库的建表语句等信息。
• 推荐使用Mysql数据库存放数据
• 连接数据库需要提供: uri username password driver。

Driver

元数据存储在数据库中，默认存在自带的derby数据库(单用户局限性)中，推荐使用Mysql进行存储。

(1) 解析器(SQL Parser)：将SQL字符串转换成抽象语法树AST，这一步一般使用第三方工具库完成，比如ANTLR；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。

(2) 编译器（Physical Plan）：将AST编译生成逻辑执行计划。

(3) 优化器（Query Optimizer）：对逻辑执行计划进行优化。

(4) 执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。

数据处理

Hive的数据存储在HDFS中，计算由MapReduce完成。HDFSh和MapReduces是源码级别上整合，两者结合最佳。解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。

Hive交互方式

第一种

shell交互Hive，用命令hive启动一个hive的shell命令行，在命令行中输入sql或者命令来和Hive交互。

服务端启动metastore服务 nohup hive --service metastore > /dev/null 2>&1 &

进入命令 hive

退出命令 quit;

第二种

Hive启动为一个服务器，对外提供服务，其他机器可以通过客户端通过协议连接到服务器来完成访问操作，这是生产环境用法最多的。

服务端启动hiveserver2服务 nohup hiveserver2 > /dev/null 2>&1 &

进入命令 1) 先执行beeline，再执行   connect jdbc:hive2://node1:10000
         2) 或者直接执行 beeline -u jdbc:hive2://node1:10000 -n root
退出命令行 exit

第三种

使用-e参数来直接执行hql的语句

bin/hive -e "use t_test;select * from test"

使用-f参数通过指定文本文件来执行hql的语句

hive -f hive.sql

hive cli和beeline cli的区别

• metastore服务实际是一种thriftf服务，通过它我们可以获取到hive元数据，并且通过thrift获取原数据的方式，屏蔽了数据库访问需要驱动，url，用户名，密码等细节。
• HiveServer2是一个服务端接口，使远程客户端可以执行对Hive的查询并返回结果。一般来讲，我们认为HiveServer2是用来提交查询的，也就是用来访问数据的。而MetaStore才是用来访问元数据的。
• beeline cli优化了命令行界面。

基本操作

Hive库操作

创建数据库

1、创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db

create database shop;

2、避免要创建的数据库已经存在错误，增加if not exists判断

create database if not exists shop;

创建数据库并指定位置

create database db_hive2 location '/db_hive2.db'

修改数据库

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置健-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

alter database db_hive set dbproperties('createtime' = '20210130')

查看数据库详细信息

1、显示数据库(show)

show databases;

2、可以通过like进行过滤

show databases like 'db_hive*';

3、查看详情(desc)

desc database db_hive

4、切换数据库

use db_hive

删除数据库

1、最简写法

drop database db_hive;

2、如果删除数据库不存在，最好判断一下

drop database if exists db_hive2;

3、如果数据库不为空，使用cascade命令强制删除。

drop database db_hive cascade;

Hive表操作

数据类型

data_type

• primitive_type 基础数据类型
• array_type 数据类型 array <data_type>
• map_type 键值类型 map <primitive_type,data_type>
• struct_type 结构体 struct <col_name:data_type [comment col_comment],… >
• union_type 组合类型 uniontype <data_type,data_type,…>

primitive_type

• tinyint
• smallint
• int
• bigint
• boolean
• float
• double
• double precision
• string
• binary
• timestamp
• decimal
• decimal(precision,scale)
• date
• varchar
• char

创建表

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]

• CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用IF NOT EXISTS选项来忽略这个异常。
• EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定实际数据的路径(LOCATION)，Hive创建内部表时，会将数据移动到数据仓库指定的路径(默认位置)；r若创建外部表，仅记录数据所在路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据(即可通过相同映射关系再次创建)。
• COMMENT 为表和列添加注释
• PARTITIONED BY 创建分区表
• CLUSTERED BY 创建分桶表
• SORTED BY 不常用
• 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通过SerDeq确定表的具体的列的数据。SerDe是Serialize/Deserilize的简称，目的就是用于序列化和反序列化。
• STORED AS 指定存储文件类型 常用的文件类型：SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)。如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用STORED AS SEQUENCEFILE。
• LOCATION 指定表在HDFS上存储位置。
• LIKE 允许用户复制现有的表结构，但是不复制数据。

创建表案例

1、数据案例

{
    "name":"John",
    "friends":["Anan","Cici"],
    "children":{
        "Dave":19,
        "Kanavi":20
    },
    "address":{
        "street":"love rode",
        "city":"shaoxing"
    }
}

2、文本数据

John,Anan_Cici,Dave:19_Kanavi:20,love rode_shaoxing

3、建表语句

create table if not exists t_person(
    name string,
    friends array<string>,
    children map<string,int>,
    address struct<street:string,city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated ':'
lines terminated by '\n'

4、上传数据

# 将文本文件上传到HDFS
hdfs fs person.txt /data

# 将文本数据load进数据表
load data inpath '/data/person.txt' into t_person

修改表

显示表

show tables;
show tables like 'u';

重命名

alter table old_table_name rename to new_table_name;

增加/修改列信息

1、查询表结构

desc test_new;

2、添加列

alter table test_new add columns (education string);

3、更新列

alter table test_new change education educationnew string;

删除表

drop table test_new;

表与文件夹与文件

• 当我们创建好表的时候，会在当前表所属的库中创建一个文件夹。
• 当我们设置表路径的时候，可以直接指向一个已有的路径,也可以直接去使用文件夹中的数据。
• 当我们load数据的时候，就会将数据文件存放到表对应的文件夹中。
• 而且数据一旦被load，就不能被修改。
• 我们查询数据也是查询文件中的文件,这些数据最终都会存放到HDFS。
• 当我们删除表的时候，表对应的文件夹会被删除，同时数据也会被删除。

Hive内部表(管理表)

# 创建表
create table if not exists person2(id int,name string) row format delimited fields terminated by '\t' stored as textfile location '/user/person2'

# 根据查询结果创建表
create table person3 as select * from person2;

# 根据已经存在的表结构创建表
create table person4 like person3;

#查询表的类型
desc formatted person;

Hive外部表

外部表说明

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

创建外部表

create external table if not exists person01(
    id int,
    name string
) row format delimited fields terminated by '\t'
location '/data/user'

user.txt放在user目录下

内部表和外部表相互转换

alter table person01 set talproperties('EXTERNAL' = 'TRUE')

Hive载入数据

基本语法

load data # 加载数据
[local] # 本地，不加Local就是从HDFS，如果是HDFS，将会删除原来的数据
inpath # 数据路径
'/opt/module/data/student.txt' # 具体路径
overwrite # 覆盖
into table # 加入到表
student # 表的名字
[partition(partcol1=val1,...)] # 分区

加载linuxb本地数据

load data local inpath 'data/test/ftp/dep.txt' into table dept;

从HDFS文件系统向表中加载数据(需要提前将数据上传到hdfsw文件系统中)

hadoop fs -put data/test/ftp/dep.txt /data/inner/RAW/01/emp

load data inpath '/data/inner/RAW/01/emp/emp.txt' into table emp

加载数据并覆盖已有数据

load data inpath '/data/inner/RAW/01/emp/emp.txt' overwrite into table emp;

非常不建议使用insert插入数据

Hive数据导出

将表中的数据备份
1、将查询结果存放到本地

//创建存放数据的目录
mkdir -p root/data

//导出查询结果的数据
insert overwrite local directory '/root/data/user01' select * from t_user;

2、按照指定的方式将数据输出到本地

insert overwrite local directory '/root/data/person'
row format delimited fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n'
select * from t_person;

3、将查询结果输出到HDFS

//创建存放数据的目录
hdfs fs -mkdir -p /data/copy

//导出查询结果数据
insert overwrite directory '/data/copy/user'
row format delimited fields terminated by ','
select * from user;

4、直接使用HDFS命令保存表对应的文件夹

//创建存放数据的目录
hdfs fs -mkdir -p /data/copy

//使用HDFS命令拷贝文件到其他目录
hdfs fs -cp /hive/warehouse/t_person/* /data/person

5、将表结构和数据同时备份

1、将数据导出到HDFS
hdfs fs -mkdir -p  /data/copy

2、导出查询结果数据
export table t_user to '/data/copy/user'

3、删除表结构
drop table t_user

4、恢复表结构和数据
import from  '/data/copy/user'