Hive学习笔记【全】
文章目录
一、Hive简介
1、Hive是什么
Hive由Facebook实现并开源,提供HQL(Hive SQL)查询功能, 将 HQL 转换成 MapReduce 执行,是一个基于Hadoop的数据仓库基础工具、一款基于 HDFS 的 MapReduce 计算框架,对存储在 HDFS 中的数据进行分析和管理,使不熟悉 MapReduce 的用户能够利用 HQL 处理和计算 HDFS 上的结构化的数据。
Hive只适用于离线的海量数据计算。
1.1【本质】
1). Hive依赖于HDFS存储数据,处理的底层数据存储在HDFS,将结构化数据映射为一张数据库表;
2). Hive分析和数据计算的底层实现是MapReduce,执行程序运行在Yarn上;
3). Hive实质上是将SQL语句转化为MapReduce任务运行。
1.2【特点】
1). 存储架构在一个数据库中并处理数据到HDFS;
2). 专门为OLAP(联机分析处理)设计,而不是用于OLTP(联机事务处理);
3). 提供SQL类型语言查询叫HiveQL(HQL);
4. 是熟知、快速、可扩展的。
1.3 【优点】
1).可扩展性(横向/纵向扩展):自由扩展集群规模,不需要重启系统。横向扩展–通过分担压力的方法扩展集群的规模。纵向扩展–扩展一台服务器的核、线程、内存等;
2).延展性:支持自定义函数,根据需求来实现自己函数;
3).容错性:保障节点有问题时SQL语句仍然可以完成执行;
4).易用性:操作端口类似SQL,简单易上手;
5).海量性:主要针对处理大型数据,对于处理小型数据没有优势。
1.4 【缺点】
1).不是一个关系型数据库,不能用于实时查询和行级更新的语言;
2).HQL表达能力较差,如迭代算法、数据挖掘方面不够擅长;
3).效率低,调度困难,粒度较粗;
4).查询延时严重,MapReduce Job的启动消耗时间长,不能用于交互查询系统及实时查询中,主要用于离线的海量数据查询;
5).不支持记录级别的增删改操作,但可以通过查询生成新表或者将查询结果导入到文件中;
6).不支持事务,无增删改,主要用于OLAP,而不是OLTP。
1.5 【为什么使用Hive】
1).Mapreduce问题:学习成本太高、项目周期要求太短、实现复杂查询逻辑开发难度太大;
2).友好的接口:操作接口采用类 SQL 的语法,提供快速开发的能力
3).低的学习成本:避免了写 MapReduce,减少开发人员的学习成本
4).好的扩展性:可自由扩展集群规模而无需重启服务,还支持用户自定义函数
1.6 【与RDBMS区别】
1).RDBMS概念:
Relational Database Management System关系数据库管理系统,SQL(MS SQL Server,IBM DB2,Oracle,MySQL和Microsoft Access等所有现代数据库系统)的基础,关系数据库管理系统(RDBMS)是一个数据库管理系统(DBMS),基于关系模型如通过 E. F. Codd 引入。
2).表的概念:
RDBMS的数据存储在数据库对象称为表。表是相关数据条目的集合,它由列和行的组成,是数据存储在关系数据库中最常见和最简单的形式。
字段:旨在维护有关表中的每个记录特定信息表中的列,即列名。
记录:也被称为一行数据,是存在于一个表中的每个单独的条目,是表中的一个水平的实体。
列:包含在一个表中的特定字段相关联的所有信息表的垂直实体。
NULL值:是一个字段显示为空值,这意味着使用一个NULL值的字段是表示一个没有值的字段,一个NULL值不同于零值或包含空格的字段,NULL值字段是一个记录创建过程中已经留为空白。
约束:是对表执行对数据的列的规则,这些约束用于限制数据的类型进入表中,并且确保数据库中的数据的准确性和可靠性。约束它可能是列级或表级,列级约束仅应用于一列,表级约束应用于整个表,MySQL中的key。
3).数据的完整性:
实体完整性:表中没有重复行;
域完整性:限制类型,格式或者值的范围对于给定一个列的有效条目;
参考完整性:行不能被删除,被其他记录使用;
用户定义完整性:强制执行不属于实体,域和参考完整性一些具体的业务规则。
4).数据的规范化:
有效地组织数据库中的数据的过程。
原因1:消除冗余数据,例如,存储了一个以上的相同的数据在同一个表。
原因2:确保数据的相关性意义。
两者都是值得追求的目标,因为它们减少的空间的数据库消耗的量,并确保数据在逻辑上存储。规范化由一系列指导方针帮助创建一个良好的数据库结构。
确保数据的相关性意义。
5).数据的标准化准则:
正常形态–形式的格式或数据库结构的布局方式。
正常形态的目的是组织数据库结构,使其符合第一范式,然后第二范式,最后第三范式的规则。
6).Hive与RDBMS区别
对比项 | Hive | RDBMS |
---|---|---|
查询语言 | HQL | SQL |
数据存储 | HDFS | Raw Device or Local FS |
执行器 | MapReduce | Executor |
数据插入 | 支持批量导入/单条插入 | 支持单条插入或者批量导入 |
数据操作 | 覆盖追加 | 行级更新删除 |
处理数据规模 | 大 | 小 |
执行延迟 | 高 | 低 |
分区 | 支持 | 支持 |
索引 | 0.8版本之后加入简单索引 | 支持复杂的索引 |
扩展性 | 高(好) | 有限(差) |
数据加载模式 | 读时模式(快) | 写时模式(慢) |
应用场景 | 海量数据离线查询 | 实时查询 |
2、Hive的架构
2.1 【执行流程】
HiveQL 通过命令行或者客户端等交互接口提交SQL指令,经过跨语言服务系统,使用自己的Driver,运用 MetaStore 中的元数据进行类型检测和语法分析(SQL Parse),经过 Compiler 编译器(Compiler),生成一个逻辑方案(Logical Plan),然后通过的优化处理(Query Optimizer),产生一个 MapReduce 任务,交给Hadoop进行执行(Executor)处理HDFS上的数据。
2.2 【Hive组成单元】
1).用户接口层USER INTERFACE
名称 | 用途 |
---|---|
CLI | Shell终端命令行(Command Line Interface),采用交互形式使用Hive命令行与Hive交互,最常用(学习、调试、生产) |
JDBC/ODBC | 是Hive的基于JDBC操作提供的客户端,用户(开发员、运维人员)通过客户端连接到Hive Server |
Web UI | 通过浏览器访问Hive |
2).跨语言服务 | |
能够让不同编程语言调用Hive接口 | |
名称 | 用途 |
– | – |
Thrift Server | 由FaceBook开发的软件框架,可以用来进行可扩展且跨语言的服务的开发,Hive集成了该服务,能够让不同编程语言调用Hive接口 |
3).元素据存储系统Meta Store
存储在Hive中的数据的描述信息,即元素据信息。HDFS中的结构化数据映射为数据库表后的数据库表的元数据信息,方便HiveSQL的查询。
元数据–表名、表列和表分区及其属性、表的属性(内部表和外部表)、表的数据所在目录
数据库–Hive能够选择各自的数据库服务器,用以储存表,数据库,列模式或元数据表,它们的数据类型和HDFS映射,默认存储在自带的Derby数据库中。
缺点–不适合多用户操作,存储目录不固定,数据库跟着Hive走,不方便管理。
解决方案–将元数据存储在我们自己创建的MySQL库(本地或者远程)
Hive和MySQL之间通过MetaStore服务交互。
4).底层Driver
完成HQL查询语句的词法/语法分析、编译、优化、生成逻辑执行计划并存储在HDFS中,由Mapreduce调用执行。
Hive的核心是驱动引擎,由四部分组成:
名称 | 用途 |
---|---|
解释器(SQL Parser) | 将HQL语句转换为抽象语法树(AST) |
编译器 (Compiler) | 将语法树编译为逻辑执行计划 |
优化器(Query Optimizer) | 对逻辑执行计划进行优化 |
执行器(Executor) | 调用底层的Mapreduce运行计算框架执行计划 |
HiveSQL处理引擎:HiveQL提供类似于SQL的查询语句,查询Metastore模式中存储的元素据信息。这是传统的方式进行MapReduce程序的替代品之一。相反,使用Java编写的MapReduce程序,可以编写为MapReduce工作,并处理它的查询。 | |
Hive执行引擎((解释器、编译器、优化器、执行器):HiveQL处理引擎和MapReduce的结合部分是由Hive执行引擎操作。执行引擎处理查询并产生执行计划,由MapReduce进行数据的操作。 | |
HDFS/HBASE:Hadoop的分布式文件系统或者HBASE数据存储技术,用于将数据存储到文件系统。 |
3、Hive的数据组织
3.1 【Hive的数据存储】
1).HDFS中的结构化的数据文件映射为数据库表,存储在Hive中。
2).Hive存储结构:数据库、表、视图、分区和表数据等。数据库、表、分区等对应于HDFS上的一个目录。表数据对应HDFS对应目录下的文件。如一个目录下的多个文件可以形成一个表(数据库),表数据为目录下的文件。
3).Hive的数据存储:存储在HDFS中,没有专门的数据存储格式,Hive是读模式(Schema On Read),可支持Text,Sequence、RC或者自定义格式。
4).Hive元数据存储:RDBMS,其他数据存储在HDFS上。默认保存在内嵌Derby数据库中,只允许一个会话连接和简单测试。实际生产环境为了支持多用户会话使用MySQL作为元数据库,Hive内部对MySQL提供了很好的支持。
4).创建表:只需要告诉Hive数据中的列分隔符和行分隔符就可以解析数据。默认列分隔符:控制符^A(Ctrl+A,八进制’\001’,十六进制’\x01’)。默认行分隔符:换行符’\n’。
3.2 【Hive的数据模型】
1).DataBase:在HDFS中表现为${hive.metastore.warehouse.dir}目录下一个文件夹。
2).Table:在 HDFS 中表现所属 database 目录下一个文件夹。
3).External Table:与 Table 类似,不过其数据存放位置可以指定任意HDFS中目录路径。
4).Partition:在 HDFS 中表现为 Table 目录下的子目录。
5).Bucket: HDFS 中表现为同一个表目录或者分区目录下根据某个字段的值进行 hash散列之后的多个文件。
6).View:与传统数据库类似,只读,基于基本表创建。
3.3 【Hive中的表】
分为内部表、外部表、分区表和Bucket表。
1).内部表(managed tables)和外部表(external tables)区别
默认创建的是内部表。
区别项 | 描述 |
---|---|
创建 | 内部:将数据移动到数据仓库指向的路径; 外部:仅记录数据路径,不对数据位置改变。 |
删除 | 内部:元数据和数据一起删除; 外部:只删除元素据,更加安全、灵活、方便共享元数据。 |
external修饰 | 内部:未修饰; 外部:修饰。 |
管理 | 内部:由Hive管理; 外部:HDFS管理。 |
修改 | 内部:同步到元数据; 外部:表结构和分区修改,需要修改MSCK REPAIR TABLE table_name。 |
选择 | 内部:数据所有处理都在Hive中进行; 外部:Hive和其他工具针对相同数据集处理、访问HDFS上初试数据后通过Hive转换数据并存到内部表中、一个数据集有多个不同的Schema |
Hive仅仅知识对存储在HDFS数据提供新的抽象,不是管理存储在HDFS上的数据,可以对Hive表的数据存储目录中的数据进行增删改操作。 |
2).分区表和Bucket表:
分区:传统的DBMS中功能,根据某些字段进行分区操作,细化数据管理,能够在特定区域检索数据,减少扫描成本,还可以在分区建立索引,提高查询效率。hive分区(单值、范围),单值分区(动态:导入数据动态判断目标分区、静态:手动指定分区)
Bucket:表和分区进一步细化为Bucket,Bucket原理和MapReduce编程中的HashPartitioner原理类似。分区和Bucket表都是细化数据管理。Hive数据存储在HDFS中,只可以追加不可以修改,执行修改需要找到对应文件,读取后修改重写重传文件,文件较大,需要大量的IO操作,采用Bucket策略,需要找到文件存放对应的Bucket,然后读取修改即可。提高查询效率,对两张在同一个列上进行Bucket操作的表Join操作,需要对保存相同列值的Bucket文件进行Join操作即可。
区别项 | 描述 |
---|---|
区分模式 | 分区表:手动添加区分,Hive读模式,对添加进分区的数据不做模式校验; Bucket表:按照某些Bucket字段进行Hash散列形成的多个文件,数据准确性高。 |
存储生成 | 分区表:按照分区表将数据按照分区键的列值存储在表目录的子目录中,目录名=‘分区键=键值’,分区键的值不一定在表中有,可以进行增删改清除操作; Bucket表:将Bucket键(字段)数据按照Hash取模方式随机、均匀分发到各个Bucket文件中,改变了数据存储模式,将Bucket键数据相同或者在同一个范围放在一个Bucket文件中并存储。 |
二、Hive安装
1、环境需求
1.1 【安装java】
略。
1.2 【安装Hadoop并配置分布式】
1). 下载安装搭建。
此处下载安装hadoop-3.2.2,配置四个节点,配置HA模式:
namenode:hadoop101,hadoop101;
datanode:hadoop100,hadoop101,hadoop102,hadoop103;
resourcemanager:hadoop102,hadoop103;
nodemanager:hadoop100,hadoop101,hadoop102.hadoop103;
jobhistory:hadoop101。
2). 检查更新jiline.jar包,早期hadoop版本为0.9+,要更换成新的jar包,否则会报错。
hadoop-xxx/share/hadoop/yarn/lib
官网地址:https://jar-download.com/artifacts/org.jline/jline/3.5.1/source-code
3). 配置关于hive的配置文件
core-site.xml
<!--设置HADOOP的代理用户-->
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>
"连接hive时出现User: xxx1 is not allowed to impersonate xxx2,此时将proxyuser.后面的xx改为xxx1"
hdfs-site.xml
<!--启用webhdfs,用于使用Web UI启动hive-->
<property>
<name>dfs.webhdfs.enabled</name>
<value>true</value>
</property>
<!--关闭权限检查-->
<property>
<name>dfs.permissions.enable</name>
<value>false</value>
</property>
1.3 【安装MySQL并设置远程连接】
此处用MySQL作为Hive的MetaStore数据库。
1).安装方法:
Rpm安装方法:
下载mysql-community相应安装包,官网地址:[https://dev.mysql.com/downloads/mysql/](https://dev.mysql.com/downloads/mysql/);
下载packagename-distribution-version.tar,解压,文件夹中含有必要的rpm安装包;
解压进入文件夹,安装rpm,rpm -ivh untar_folder/*。(可能需要某些依赖,请根据提示进行安装)
yum安装方法:
yum install -y mysql-server
2).配置方法:
启动服务,systemctl start mysqld
获取初始密码,grep "temporary password" /var/log/mysqld.log
登录,修改密码,"mysql -uroot -p","alter user root@localhost identified by "password;"
不符合policy,一个尝试,加入字符、大小写等
查看policy,show variables like "validate_password%"
修改policy,set global validate_password.xxx=x
3).配置远程登录
#增加hive用户并授予远程登录权限,设置IP为通配符"%",授予所有数据库权限
mysql>create user hive@"%" identified by "my_password";
mysql>grant all privileges on database.table to hive@"%" with grant option;
#其他主机登录
>mysql -h mysql_ip -u hive -p
password:**********
1.4 【下载配置默认的Derby数据库】
如果不用搭建内嵌模式则不需要下载配置。
1).下载Apache Dderby并解压
官网地址:http://archive.apache.org/dist/db/derby/
选择指定版本下载bin.tar.gz文件。
2). 设置Derby环境变量
>vim /etc/profile
export DERBY_HOME=path/db-derby-xxx-bin
export PATH=$PATH:$DERBY_HOME/bin
export CLASSPATH=$CLASSPATH:$DERBY_HOME/lib/derby.jar:$DERBY_HOME/lib/derbytools.jar
>source /etc/profile
3).创建目录用于存储Metastore
>mkdir $DERBY_HOME/data
2、安装Hive
2.1 【下载安装Hive】
1)下载并解压
官网地址:http://www.apache.org/dyn/closer.cgi/hive/
选择一个镜像地址安装。
2).配置Hive运行环境文件
配置Hive用于Hadoop环境中,需要编辑hive-env.sh文件,文件放置于$HIVE_HOME/conf目录,需要复制并重命名模板文件。
>cp hive-env.sh.template hive-env.sh
>vim hive-env.sh
export HADOOP_HOME=hadoop_path
3).配置HIVE环境变量
在~/.bashrc或者/etc/profile文件中。
>vim /etc/profile
"
HIVE_HOME=hive_path
PATH=$PATH:$HIVE_HOME/bin
CLASSPATH=$CLASSPATH:$HADOOP_HOME/lib/*:$HIVE_HOME/lib/*
export HIVE_HOME PATH CLASSPATH
"
2.2【用一个外部数据库服务器配置Metastore】
指定Hive数据库存储,通过在$HIVE_HOME/conf中创建编辑hive-site.xml文件。
>cp hive-default.xml.template hive-site.xml
"删除所有<configuration></configuration>内所有内容"
2.3 【初始化元数据库】
当使用的 hive 是 2.x 之前的版本,不做初始化也是 OK 的,当 hive 第一次启动的 时候会自动进行初始化,只不过会不会生成足够多的元数据库中的表。在使用过程中会 慢慢生成。但最后进行初始化。如果使用的 2.x 版本的 Hive,那么就必须手动初始化元 数据库。
$schematool -dbType mysql(derby) -initSchema
com.google.common.base.Preconditions.checkArgument 报错:
Hive/lib和hadoop_path/share/hadoop/common/lib下的guava.jar版本不一致,删除低版本,拷贝高版本即可。
2.4 【验证HIVE安装】
启动Hadoop集群。
#验证hive
$hive
hive>
3、启动HIVE
3.1 【命令行启动】
$hive
hive>
3.2 【客户端启动】
与命令行启动效果相同
$hive --service cli
三、Hive元数据和MySQL数据库表
1、MySQL存放Hive的数据库
Hive元数据通常存储在关系型数据库中,常用MySQL数据库作为元数据库管理。
$mysql>use hivedb;
2、存储Hive版本的元数据表
表 | 作用 | 描述 |
---|---|---|
VERSION | 查询Hive版本信息 | 该表出现问题,进入不了Hive-Cli |
3、Hive数据库相关的元数据表
表 | 作用 | 字段描述 |
---|---|---|
DBS | 存储Hive中所有数据库基本信息 | DB_ID数据库id;DESC数据库描述;DB_LOCATION_URI数据库HDFDS路径;NAME数据库名;OWNER_NAME所有者;OWNER_TYPE所有者角色 |
DATABASE_PARAMS | 存储数据库相关参数,CREATE DATABASE使用WITH DBPROPERTIES(property_name=property_value,…)指定参数 | DB_ID数据库id与DBS表关联;PARAM_KEY参数名;PARAM_VALUE参数值 |
4、Hive表和视图相关的元数据表
三张表通过TBL_ID相关联,有些易懂的字段不必解释。
表 | 作用 | 字段描述 |
---|---|---|
TBLS | 存储Hive表、视图、索引表基本信息 | TBL_ID;CREATE_TIME;DB_ID;LAST_ACCESS_TIME;OWNER;RETENTION;SD_ID序列化配置信息;TBL_NAME;TBL_TYPE;VIEW_EXPANDER_TEXT视图中详细HQL语句;VIEW_ORIGINAL_TEXT视图原始HQL语句 |
TABLE_PARAMS | 存储表、视图属性信息 | TBL_ID;PARAM_KEY属性名;PARA_VALUE属性值 |
TBL_PRIVS | 存储视图、表的授权信息 | TBL_GRANT_ID;CREATE_TIME;GRANT_OPTION;GRANTOR;GRANTOR_TYPE;PRINCIPAL_NAME被授权用户;PRINCIPAL_TYPE;TBL_PRIV权限;TBL_ID |
5、Hive文件存储信息相关的元数据表
由于HDFS、HIVE支持非常多的文件格式,解析成MapReduce的时候,需要知道去哪里,那种格式读取文件。
表 | 作用 | 字段描述 |
---|---|---|
SDS | 保存文件存储的基本信息,如INPUT_FORMAT、OUTPUT_FORMAT等,TBLS中的SD_ID与该表相连 | SD_ID存储信息id;CD_ID字段id;INPUT_FORMAT;IS_COMPRESSED;IS_STOREDASSUBDIRECTORIES是否子目录存储;LOCATION;NUM_BUCKETS;OUTPUT_FORMAT;SERDE_ID序列化类ID |
SD_PARAMS | 存储Hive存储的属性信息,在创建表的时候使用STORED BY ‘storage.handler.class.name’ [WITH SERDEPROPRTIES(…)指定 | SD_ID存储配置ID;PARAM_KEY存储属性名;PARAM_VALUE存储属性值 |
SERDES | 存储序列化使用的类信息 | SERDE_ID序列化配置ID;NAME序列话类别名称;SLIB序列话类 |
SERDE_PARAMS | 存储序列化的一些属性、格式信息如行列分隔符 | SERDE_ID;PARAM_KEY;PARAM_VALUE |
6、Hive表字段相关的元数据表
表 | 作用 | 字段描述 |
---|---|---|
COLUMNS_V2 | 该表存储表对应的字段信息 | CD_ID字段信息ID;COMMENT;COLUMNS_NAME;TYPE_NAME;INTEGER_IDX字段顺序 |
7、Hive表分区相关的元数据表
表 | 作用 | 字段描述 |
---|---|---|
PARTITIONS | 存储表分区的基本信息 | PART_ID;CREATE_TIME;LAST_ACCESS_TIME;SD_ID分区存储ID;TBL_ID |
PARTITION_KEYS | 存储分区的字段信息 | TBL_ID;PKEY_COMMENT;PKEY_NAME;PKEY_TYPE;INTEGER_IDX |
PARTITION_KEY_VALS | 存储分区字段值 | PART_ID;PART_KEY_VAL;INTEGER_IDX |
PARTITION_PARAMS | 存储分区的属性信息 | PART_ID;PARAM_KEY;PARAM_VALUE |
8、其他不常用的元数据表
表名 | 描述 |
---|---|
DB_PRIVS | 数据库权限信息表。通过GRANT语句对数据库授权后,在这里存储 |
IDXS | 索引表,存储Hive索引相关的元数据 |
INDEX_PARAMS | 索引相关的元数据 |
TAB_COL_STATS | 表字段的统计信息。使用ANALYZE语句对表字段分析后记录在这里 |
TBL_COL_PRIVS | 表字段的授权信息 |
PART_COL_STATS | 分区字段的统计信息 |
PART_COL_PRIVS | 分区字段的权限信息 |
FUNCS | 用户注册的函数信息 |
FUNC_RU | 用户注册函数的资源信息 |
四、Hive运行模式及其配置连接
1、内嵌模式
1.1 【简介】
元数据保存在内嵌的derby数据库中,Hive和数据库必须在同一个节点上。
特点:只能允许一个会话连接,尝试多个会话连接时会报错。
1.2 【配置方式】
1).搭建Hadoop分布式集群;
2).安装Hive并配置环境变量;
3).安装Derby数据库并配置环境变量;
4).配置MetaStore数据库:Derby数据库
hive-site.xml
<!--Hive中所有数据存储路径:使用本地文件系统直接写路径,使用HDFS使用:hdfs://mycluster:port/path-->
<property>
<name>hive.metastore.warehouse.dir</name>
<value>hdfs://mycluster:port/user/hive/warehouse</value>
<description></description>
</property>
<!--连接本地Derby的Url-->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:derby:;databaseName=metastore_db;create=true</value>
<description></description>
</property>
<!--连接Derby的驱动包-->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>org.apache.derby.jdbc.EmbeddedDriver</value>
<description></description>
</property>
<!--元数据库是否存放在本地,true代表存放在本地-->
<property>
<name>hive.metastore.local</name>
<value>true</value>
<description></description>
</property>
5). 初始化元数据
#初始化元数据
>schematools -dbType derby -initSchema
"如果失败,则去mysql中删除hive创建的database重新初始化"
#验证是否成功
>hive
"如果能够建表,说明搭建成功"
2、本地模式
2.1 【简介】
安装MySQL 替代derby存储元数据,Hive和MySQL可以不在同一个节点上。
由于元数据的获取需要访问mysql,所以这就要求每一个用户必须要有对MySQL的访问权利,参考MySQL远程连接配置。
特点:使用mysql做元数据的存储,操作mysql数据库做元数据的管理。MetaStore和MySQL在同一个节点。
2.2 【配置方式】
1).搭建Hadoop分布式集群;
2).节点1安装Hive并配置环境变量;
3).节点1或者其余节点安装MySQL数据库并设置远程连接用户;
4).节点1配置MetaStore数据库:MySQL数据库
hive-site.xml
<!--Hive中所有数据存储路径:使用本地文件系统直接写路径,使用HDFS使用:hdfs://mycluster:port/path-->
<property>
<name>hive.metastore.warehouse.dir</name>
<value>hdfs://mycluster:port/user/hive/warehouse</value>
<description></description>
</property>
<!--mysql的数据库url-->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://mysql_ip:3306/hivedb?createDatabaseIfNotExist=true</value>
<description></description>
</property>
<!--连接MySQL的驱动包-->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description></description>
</property>
<!--hive存放元数据的数据库的用户名-->
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>mysql_username</value>
<description></description>
</property>
<!--hive存放元数据的数据库的密码-->
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>mysql_password</value>
<description></description>
</property>
<!--metastore和mysql是否在同一个节点,true代表在同一个节点-->
<property>
<name>hive.metastore.local</name>
<value>true</value>
<description></description>
</property>
加入MySQL驱动包:
官网地址:https://repo1.maven.org/maven2/mysql/mysql-connector-java/
mysql-connector-java-x.x.xx-bin.jar
该文件放在Hive根目录下的lib目录中。
5). 初始化元数据
#初始化元数据
>schematools -dbType derby -initSchema
"如果失败,则去mysql中删除hive创建的database重新初始化"
#验证是否成功
>hive
"如果能够建表,说明搭建成功"
3、远程模式
3.1 【简介】
以本地模式为基础。
MySQL数据库所在的节点提供元数据存储,其他节点提供metastore service服务,其他节点可以连接该服务来获取元数据信息。
各种客户端通过 beeline 来连接,连接之前无需知道数据库的用户名和密码。
特点:使用mysql做元数据的存储,使用metastore服务做元数据的管理。MetaStore和MySQL不在同一个节点。
3.2 【配置方式】
1).搭建Hadoop分布式集群;
2).MySQL服务端:节点1安装MySQL数据库并设置远程连接用户;
3).Hive服务端:节点2安装Hive,配置环境变量及MySQL数据库
hive-site.xml
<!--mysql的数据库url-->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://mysql_ip(节点1):3306/hive_metadata?createDatabaseIfNotExist=true</value>
<description></description>
</property>
<!--连接MySQL的驱动包-->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description></description>
</property>
<!--hive存放元数据的数据库的用户名-->
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>mysql_username</value>
<description></description>
</property>
<!--hive存放元数据的数据库的密码-->
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>mysql_password</value>
<description></description>
</property>
"特别改动点"
<!--metastore和mysql是否在同一个节点,true代表在同一个节点-->
<property>
<name>hive.metastore.local</name>
<value>false</value>
<description></description>
</property>
加入MySQL驱动包:
官网地址:https://repo1.maven.org/maven2/mysql/mysql-connector-java/
mysql-connector-java-x.x.xx-bin.jar
该文件放在Hive根目录下的lib目录中。
初始化元数据并测试hive
>schematools -dbType mysql -initSchema
>hive
4). MetaStore服务端:节点3配置搭建metasore服务器
同节点2操作设置;
启动metastore服务端
>hive --service metastore
#或者使用
>nohup hive --service metastore 1>/dev/null 2>&1 &
#该命令可能会阻塞
5). Hive客户端:节点4搭建配置Hive客户端
这里Hive客户端和服务端分开,其实可以设置在同一个节点上,主要是避免客户端直接接触设置MySQL,如密码等。
执行安装Hive并配置环境变量
编辑:hive-site.xml
<!--Hive中所有数据存储路径:使用本地文件系统直接写路径,使用HDFS使用:hdfs://mycluster:port/path-->
<property>
<name>hive.metastore.warehouse.dir</name>
<value>hdfs://mycluster:port/user/hive/warehouse</value>
<description></description>
</property>
<!--metastore和mysql是否在同一个节点,true代表在同一个节点-->
<property>
<name>hive.metastore.local</name>
<value>false</value>
<description></description>
</property>
<!--metastore服务器的url,idea等外部可以通过metastore服务连接到hive,之后连接hive时可以通过连接到metastore服务器ip即可-->
<property>
<name>hive.metastore.uris</name>
<value>thrift://metastore_server_ip:9083</value>
<description></description>
</property>
启动Hive并连接
>hive
4、本地模式和远程模式区别
4.1 【优缺点】
1). 本地模式
优点:
可以多个Hive Client一起使用,并且可以共享元数据。
缺点:
MySQL的连接信息明文存储在客户端配置,Driver和metastore在同一台机器上,能看到MySQL的连接,不安全,不便于数据库连接信息保密和以后对元数据库进行更改;
如果客户端太多也会对MySQL造成较大的压力,因为每个客户端都自己发起连接,可以通过MySQL HA减压;
只需启动metastore 服务就启动了hive。
2). 远程模式
优点:
便于元数据库信息的保密。因为只需要在运行metastore的机器上配置元数据库连接信息,客户端只需要配置metastore连接信息即可。
Driver和metastore不在同一台机器上,看不到mysql的连接,安全可靠。需要启动每个机器的driver和metastore。
缺点:
会引发单点问题。例如metastore服务挂了,其它hive终端就获取不到元数据信息了。不过,一般来说企业环境推荐使用远程模式。
多个hive服务同时访问metastore,使得metastore服务端压力增加,可以通过横向扩展metastore减压。
4.2 【启动方式】
本地模式不需要单独起metastore服务,用的是跟hive在同一个进程里的metastore服务。
远程模式需要单独起metastore服务,然后每个客户端都在配置文件里配置连接到该metastore服务。远程模式的metastore服务和hive运行在不同的进程里。metastore服务端指的是Metastore服务所在的机器,即安装metastore的机器。
五、Hive连接方式、USER INTERFACES连接使用Hive
1、CLI连接
远程模式需要先启动metastore服务。hive操作均在集群启动后。
>nohup hive --service metastore >/dev/null 2>&1 &
1.1 【在Hive客户端直接命令行使用命令】
>hive
#或者
>hive --vervice cli
#查看使用帮助
>hive --help
#启动服务
>hive --service serviceName --help
1.2【在Hive客户端启动beeline客户端去连接】
1). 启动hiveserver2
"启动hiveserver"
>hiveserver2
#以后台运行方式运行,并输出日志(推荐)
>nohup hiveserver2 1>log_path/hiveserver.log 2>log_path/hiveserver.err &
#nohup命令表示正在运行程序,退出该账户时该进程还不会结束,会在你退出账户、关闭终端之后继续运行相应的进程。
2). 启动beeline并连接Hive
hadoopx为metastore服务器所在的ip节点。
#
>beeline -u jdbc:hive2://hadoopx:10000 -n hadoop
#-u指定元数据库的连接信息,-n指定用户名和密码
#另一种连接方法:
>beeline
beeline>!connect jdbc:hive2://:hadoopx:10000#hadoopx为hiveserver2的节点ip地址或者localhohst
Enter username for jdbc:hive2://hadoopx:10000: hadoopx#输入用户名,安装hadoop集群的用户名
Enter password for jdbc:hive2://hadoopx:10000: ******#输入密码
#远程模式不用输入密码和用户名,直接enter
2、在Hive客户端使用Web UI
2.1 【下载对应版本的src包】
官网地址:https://mirrors.bfsu.edu.cn/apache/hive/hive-3.1.2/
解压、编译
#编译命令
>cd src_path
>mvn package -DskipTests
2.2 【打包相关目录并复制到hive】
>cd hive_src_path/hwi/web
>jar -cvf hive-hwi-version.war *
>cp hive-hwi-version.war $HIVE_HOME/lib/
2.3 【修改配置文件】
>vim hive-site.xml
<property>
<name>hive.hwi.listen.host</name>
<value>0.0.0.0</value>
<description>监听的地址</description>
</property>
<property>
<name>hive.hwi.listen.port</name>
<value>9999</value>
<description>监听的端口号</description>
</property>
<property>
<name>hive.hwi.war.file</name>
<value>lib/hive-hwi-2.3.2.war</value>
<description>war 包所在的地址</description>
</property>
2.4 【复制所需要的jar包】
>cp $JAVA_HOME/lib/tools.jar $HIVE_HOME/lib
>cp commons-el-version.jar|jasper-compiler-version.jar|jasper-runtime-version.jar $HIVE_HOME/lib
#此处需要寻找这三个jar包
commons-el-1.0.jar:https://repo1.maven.org/maven2/commons-el/commons-el/1.0/
jasper-xx.jar :http://www.java2s.com/Code/Jar/j/Downloadjasper6026jar.htm
2.5 【安装ant】
1). 下载ant:apache-ant-version-bin.tar.gz
官网地址:
2). 解压,配置环境变量
配置ANT_HOME、PATH变量
3). 验证是否安装成功
>ant -version
2.6 【运行】
>hive --service hwi
#或者>nohup hive --service hwi 1>/dev/null 2>/dev/null &
前文配置9999端口号,浏览器输入hadoopx:9999/hwi即可
3、DbVisualizer/JDBC配置连接Hive
3.1 【安装DbVisualizer】
下载官网:https://www.dbvis.com/download/
安装:
Windows版本直接双击exe文件即可;
Linux版本:如果已经安装java,下载Withour Java,下载sh文件,配置环境变量,即可通过命令bash xxx.sh运行。
3.2 【配置Hive JDBC】
1). 在DbVisual安装目录中新建hive文件夹。
2). 拷贝Hadoop相关jar包放入hive中:
share/hadoop/common/hadoop-common-x.x.x.jar
share/hadoop/common/lib/*.jar
3). 拷贝Hadoop相关jar放入hive文件夹中:
hive_home/jdbc/lib/*.jar
3.3 【软件内部配置】
1). 进入软件
2). 配置添加Driver
Tools–>Driver Manager–>hive–>左上角添加
添加hive配置:
Name:hive
URL Format:jdbc:hive2://metastore_server_ip:10000/databases #database根据mysql的hive-site.xml设定,此处在远程模式中为hive_metastore
Driver Class:选择添加刚刚添加进hive文件夹的jar
3). 添加连接数据库
Database --> Use Connection Wizard --> 输入任意连接名 --> 选择hive驱动
输入连接地址:
jdbc:hive2://hiveserver2_server_ip:10000
输入mysql用户名和密码
输入DataServer服务器地址为hiveserver2
输入port
Connect
4). 完成
选择SQL command即可。
左侧栏显示数据库数据。
右侧显示指令面板,输入HQL指令,点击执行,即可执行指令。
3.4 【使用java通过jdbc连接hive】
1). 准备工作
启动集群、mysql、metastore、hiveserver2
创建java项目,创建包路径hive/main,util (util使jdbc或者odbc工具类,main中有运行的主类)
添加hive驱动包:
新建文件夹hive,将HADOOP包含的jar以及Hive_Path/jdbc/*.jar一并添加
Windows-Preference-Java-BuildPath-add-输入Hive, 新建UserLib,addExternalJar,添加上述所有jar
在项目中BuildPath中选择相应的UserLib即可
2). 编写jdbc工具类
package hive.util
import java.sql.*;
public class jdbcConnection
{
private static String driver='org.apache.hive.jdbc.HiveDriver';
private static String url='jdbc:hive2://ip:10000/databases';
private static String user='username';
private static String pass='password';
//打开连接
public static Connection openConnection()
{
Connection conn=null;
try
{
class.forName(driver);
conn=DriverManager.getConnection(url,user,pass);
}catch(Exception e)
{
e.printStackTrace();
}
return conn;
}
//关闭连接
public static void closeConnection(Statement stmt, Connection conn)
{
try
{
if(stmt != null)
{
stmt.close();
stmt=null;
}
if(conn !=null)
{
conn.close();
conn=null;
}
System.out.println(">>>>>>>>>>>>>>:关闭连接成功....");
}catch(Exception e)
{
e.printStackTrace();
}
}
}
主类执行
package hive.util;
import java.sql.*;
public class Hive
{
public static void main(String[] args)
{
jdbcConnection jdbc=new jdbcConnection();
Connection conn=jdbc.openConnection();
Statement stmt=conn.getStatement();
sql1="sql_query";
sql2="sql_query";
boolean b=stmt.execute(sql1);//return a boolean result
ResultSet result=stmt.executeQuery(sql);
System.out.println(result.getRow());//查看有多少行,如果为0,则不能用result等方法。
}
}
ResultSet转换为列表
public static List<Map> ResultSetToList(ResultSet result)
{
if(result==null)//判断得到结果是否为空
{
return Collections.EMPTY_LIST;
}
List<Map> list=new ArrayList<Map>();
Map rowData=new HashMap();
ResultSetMetaData rm=result.getMetaData();// 得到结果集结构信息,比如字段数、字段名等
int ColumnCount=rm.getColumCount();//获取列数
while(result.next())//在sql中有游标,用next表示游标指向下一个,默认从第0个开始
{
rowData=new HashMap(ColumnCount);
for(int i=1;i<=ColumnCount;i++)//在sql中默认以1初始
{
rowData.put(rm.getColumns(i),result.getObject(i));
}
list.add(rowData);//list中每个值都表示一行数据
}
return list
}
//遍历
public void getAll()
{
List<Map> list=ResultSetToList(result);
for(Map listObject:list)
{
System.out.println(listObject.toString());
}
}
连接sql一般操作:
设定driver:
Hive:driver="org.apache.hive.jdbc.HiveDriver"; 需要加入jar驱动,HIVE_HOME/jdbc/*.jar+HADOOP_HOME/share/hadoop/*.jar
Mysql8.0:driver="com.mysql.cj.jdbc.Driver"; 需要加入驱动:[mysql-connector-java-8.0.16.jar](https://static.runoob.com/download/mysql-connector-java-8.0.16.jar)
加载jdbc驱动:Class.forName(driver);
获取Connection:Connection conn=DriverManager.getConnection(jdbc:hive2(mysql)://ip_address:port(10000or3306)/database,username,password);
获取Statement:Statement stmt=conn.getStatement();
执行不返回结果的sql:stmt.execute(sql_string); //返回boolean型数据,成功则返回true
执行返回结果的sql:ResultSet result=stmt.execute(sql_query);//返回Result结果集
ResultSet常用操作
//基于游标,初始值位于第0个
boolean b=result.next();//游标指向下一个,返回boolean型,如果有下一个则返回true
result.first();//移到第一个
result.last();//移到最后一个
result.getRow();//返回当前游标位置
result.absolute(numrow);//移动到指定行
六、HQL数据类型&存储格式
1、数据类型
1.1 【基本数据类型】
1). 数字类型
类型 | 描述 | 示例 | 语法 |
---|---|---|---|
TININT | 1字节整数:-128~127 | 1Y | col TININT |
SMALLINT | 2字节整数:-32,768~32,767 | 1S | col SMALLINT |
INT/INTEGER | 4字节整数: -2,147,483,648~2,147,483,647 | 2,147,483,647 | col INT |
BIGINT | 8字节整数:-9,223,372,036,854,775,808~9,223,372,036,854,775,807 | 1L | col BIGINT |
FLOAT | 4字节单精确浮点型 | 1.0 | col FLOAT |
DOUBLE | 8字节双精确浮点型 | 1.0 | col DOUBLE |
DOUBLE PRECISION | double的别名,从hive2.2.0开始 | 1.0 | col DOUBLE PRECISION |
DECIMAL | 任意精度的带符号小数:Hive0.11.0开始38位数字精度,Hive0.13.0引入用户自定义精确度 | 1.0 | col DECIMAL/DECIMAL(precision总数位,scal小数位) |
NUMERIC | 与DECIMAL相似,从Hive3.0.0引入 | 1.0 | col NUMERIC/NUMERIC(precision,scale) |
2). 时间、日期类型
类型 | 描述 | 示例 | 语法 |
---|---|---|---|
TIMESTAMP | 时间戳,从1970-01-01 00:00:00 UTC到指定时间的秒数,纳秒精度 Hive0.8.0 | 1441565203 | col TIMESTAMP |
DATE | 日期 Hive 0.12.0 | “1970-01-01” | col DATE |
INTERVAL | 时间间隔 Hive 1.2.0 | INTERVAL ‘1’ DAY INTERVAL ‘1-2’ YEAR TO MONTH INTERVAL (1+dt) DAY(enables dynamic intervals) INTERVAL ‘1 2:3:4.000005’ DAY | col INTERVAL |
3).字符类型
类型 | 描述 | 示例 | 语法 |
---|---|---|---|
STRING | 字符串,变长 | “a”,’ab’ | col STRING |
VARCHAR | 变长(1~65535)字符串 | “a”,’ab’ | col VARCHAR(num) |
CHAR | 固定长度(1~255)字符串 | “a”,’ab’ | col CHAR(num) |
4). 杂类
类型 | 描述 | 示例 | 语法 |
---|---|---|---|
BOOLEAN | true/false | TRUE | col BOOLEAN |
BINARY | 字节数组 | col BINARY | |
NULL | null值,不是空字符串,默认下在文本导入时文本null值用NULL标记,使用LazySimpleSerDe文本TXT格式使用“\N”字符串充当null值 |
1.2 【复合类型】
类型 | 描述 | 示例 | 语法 |
---|---|---|---|
ARRAY | 数组类型 | col ARRAY<data_type> | |
MAP | map类型 | col MAP<primitive_type, data_type> | |
STRUCT | 结构体 | col STRUCT<col_name : data_type [COMMENT col_comment], …> | |
UNIONTYPE | union类型 | col UNIONTYPE<data_type, data_type, …> |
1.3 【字段类型】
类型 | 描述 | 语法 |
---|---|---|
TINYINT | 1Y | |
SMALLINT | 1S | |
INT/INTEGER | 1 | |
BIGINT | 1L | |
FLOAT | 1.0 | |
DOUBLE | 1.0 | |
DECIMAL | 默认(10,0),可以指定比DOUBLE和BIGINT范围更大,可以将DECIMAL和其他数值型相互转换,包括科学计数法4.004E+3或者非科学计数法4004或者两个的组合 | col DECIMAL(precision,scale) |
NUMERIC | 同DECIMAL | col NUMERIC |
STRING | 变长字符串,可以任意指定任意长度 | col STRING |
VARCHAR | 边长字符串,必须在指定范围内的长度 | col VARCHAR(num) |
CHAR | 边长字符串,指定长度在VARCHAR小 | col CHAR(num) |
TIMESTRAMP | 时间戳,表示UTC时间,提供用于时区转换的UDFs类型,文本文件必须使用[yyyy-mm-dd hh:mm:ss.fff]类型数据,如果其他数据,必须指定(INT,FLOAT,STRING等),并用UDFs将其转换为时间戳 | col TIMESTRAP |
DATE | yyyy-mm-dd | col DATE |
INTERVAL | 时间间隔 | col INTERVAL |
BOOLEAN | TRUE/FALSE | col BOOLEAN |
BINARY | 字节数组,类似数据库的VARBINARY | col BINARY |
ARRAY | 数组 | col AARAY(data type) |
MAP | map类型 | col MAP(primitive_type,data_type) |
STRUCT | 结构体 | col STRUCT(col_name:data_type[COMMENT col_comment],…) |
UNION | col UNIONTYPE(data_type,data_type,…) |
1.4 【类型转换函数】
CAST
cast函数提供了不同数据类型的转换,使得不同数据类型的数据能够比较运算。
cast必须满足存储的类型值符合转换条件才能进行转换,否则为NULL,如将“abc”转换成double。
如果使用cast将高类型的数据转换成低类型的数据,cast函数会直接截取,损失精度甚至得到错误结果。
>select
>cast("1223" as double),cast("345.6" as int) from aaa;
"1223.0 345"
void | boolean | tinyint | smallint | int | bigint | float | double | decimal | string | varchar | timestramp | date | binary | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
void to | ture | ture | ture | ture | ture | ture | ture | ture | ture | ture | ture | ture | ture | ture |
boolean to | false | ture | false | false | false | false | false | false | false | false | false | false | false | false |
tinyint to | false | false | ture | ture | ture | ture | ture | ture | ture | ture | ture | false | false | false |
smallint to | false | false | false | ture | ture | ture | ture | ture | ture | ture | ture | false | false | false |
int to | false | false | false | false | ture | ture | ture | ture | ture | ture | ture | false | false | false |
bigint to | false | false | false | false | false | ture | ture | ture | ture | ture | ture | false | false | false |
float to | false | false | false | false | false | false | ture | ture | ture | ture | ture | false | false | false |
double to | false | false | false | false | false | false | false | ture | ture | ture | ture | false | false | false |
decimal to | false | false | false | false | false | false | false | false | ture | ture | ture | false | false | false |
string to | false | false | false | false | false | false | false | ture | ture | ture | ture | false | false | false |
varchar to | false | false | false | false | false | false | false | false | ture | ture | ture | false | false | false |
timestamp to | false | false | false | false | false | false | false | false | false | ture | ture | true | false | false |
date to | false | false | false | false | false | false | false | false | false | ture | ture | false | true | false |
binary to | false | false | false | false | false | false | false | false | false | false | false | false | false | ture |
1.5 【数学函数】
能够使用于decimal类型的数据
数学函数 | 描述 | 语法 |
---|---|---|
POSITIVE | 转换为正数 | POSITIVE(col/num) |
NEGATIVE | 转换为负数 | NEGATIVE(col/num) |
ADDITION | 加法操作col1+col2 | ADDITION(col1/num1,col2/num2) |
SUBTRACTION | 减法操作col1-col2 | SUBSTRACTION(col1/num1,col2/num2) |
MULTIPLICATION | 乘法操作col1*col2 | MULTIPLICATION(col1/num1,col2/num2) |
DIVISION | 除法操作col1/col2 | DIVISION(col1/num1,col2/num2) |
AVERAGE | 取平均,适用于字段 | AVERAGE(col) |
SUM | 取和操作 | SUM(col) |
COUNT | 计数操作,忽略NULL | COUNT(col) |
MODULUS | 取余操作col1 mod col2 | MODULUS(col) |
SIGN | 符号函数 | SIGN(col) |
EXP | 指数函数 | EXP(col) |
LN | LN函数 | LN(col) |
LOG2 | 2为底对数函数 | LOG2(col) |
LOG10 | 10为底对数函数 | LOG10(col) |
LOG(BASE) | BASE为底对数函数 | LOG(BASE)(col) |
SQRT | 开根号操作 | SQRT(col) |
SIN | sin操作 | SIN(col) |
ASIN | 反sin操作 | ASIN(col) |
COS | cos操作 | COS(col) |
ACOS | 反cos操作 | ACOS(col) |
TAN | tan操作 | TAN(col) |
ATAN | 反tan操作 | ATAN(col) |
RADIANS | 转换为弧数Π | RADIANS(col) |
DEGREES | 转换为度数° | DGREES(col) |
FLOOR | 向下取整 | FLOOR(col) |
CEILING | 向上取整 | CEILING(col) |
ROUND | 四舍五入取整 | ROUND(col) |
2、存储格式
数据库-目录/表-目录/表数据-文件
Hive支持内部设定以及自定义的文件格式。
2.1【内部设定的文件格式】
存储格式 | 描述 | 特点 | 默认列分隔符 |
---|---|---|---|
TEXTFILE | 默认格式,除非使用事前使用“hive.default.fileformat”指定; 使用DELIMITED来定义分隔符; 通过启用转义字符“ESCAPE BY”(ESCAPE BY “”,使用‘+特殊字符’)使得数据中能够包含这些分隔符 ; 自定义的NULL格式也可以使用“NULL DEFINED A “\N” ”来指定; 表中字节数组是采用base64编码,阅读采用原始字节设置““hive.serialization.decode.binary.as.base64”=“false”” | 行存储方式。数据不做压缩,磁盘开销大,数据解析开销大。 | |
SEQUENCEFILE | 支持三种压缩选择:NONE/RECORD/BLOCK,RECORD压缩率低,建议使用BLOCK压缩 | Hadoop Api提供的二进制文件支持,具有使用方便、可分割、可压缩的特点。 | |
ORCFILE | 数据按照行分块,每个块按照列存储,其中每个块都存储有一个索引。 支持ACID Transaction& Cost-based Optimizer(CBO) | hive给出的新格式,属于RCFILE的升级版,性能有大幅度提升,而且数据可以压缩存储,压缩快 快速列存取。 | |
PARQUENTFILE | Use ROW FORMAT SERDE … STORED AS INPUTFORMAT … OUTPUTFORMAT syntax … | 行列存储(columnar),同时具有很好的压缩性能;同时可以减少大量的表扫描和反序列化的时间。 | |
AVROFILE | |||
RCFILE | 行列存储文件 | ||
JSONFILE | json格式 | ||
STORED BY | 以非本地表格式方式存储 | 创造或者连接一个非本地表,例如连接一个Hbase、Druid、Accumulo支持的表 | |
INPUTFORMAT and OUTPUTFORMAT | 在文件格式中指定相应的输入、输出格式类的名字作为字符串文本 | ‘org.apache.hadoop.hive.contrib.fileformat.base64.Base64TextInputFormat’ 对于LZO压缩,INPUTFORMAT:“com.hadoop.mapred.DeprecatedLzoTextInputFormat”;OUTPUTFORMAT:“org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat” |
2.2 【数据格式】
数据存储在文本文件中,必须按照一定格式区别行和列,并且在Hive中指明这些区分符。
分隔符 | 描述 |
---|---|
\n | 对于文本文件来说,每行是一条记录,所以\n 来分割记录 |
^A(Ctrl+A) | 分割字段,也可以用\001来表示 |
^B(Ctrl+B) | 用于分割 Arrary 或者 Struct 中的元素,或者用于 map 中键值之间的分割,也可以用\002 分割。 |
^C(Ctrl+C) | 用于 map 中键和值自己分割,也可以用\003 表示。 |
七、HiveQL DDL(Data Definition Language)
1、保留关键字和非保留关键字
非保留关键字 | 保留关键字 | |
---|---|---|
Hive 1.2.0 | ADD, ADMIN, AFTER, ANALYZE, ARCHIVE, ASC, BEFORE, BUCKET, BUCKETS, CASCADE, CHANGE, CLUSTER, CLUSTERED, CLUSTERSTATUS, COLLECTION, COLUMNS, COMMENT, COMPACT, COMPACTIONS, COMPUTE, CONCATENATE, CONTINUE, DATA, DATABASES, DATETIME, DAY, DBPROPERTIES, DEFERRED, DEFINED, DELIMITED, DEPENDENCY, DESC, DIRECTORIES, DIRECTORY, DISABLE, DISTRIBUTE, ELEM_TYPE, ENABLE, ESCAPED, EXCLUSIVE, EXPLAIN, EXPORT, FIELDS, FILE, FILEFORMAT, FIRST, FORMAT, FORMATTED, FUNCTIONS, HOLD_DDLTIME, HOUR, IDXPROPERTIES, IGNORE, INDEX, INDEXES, INPATH, INPUTDRIVER, INPUTFORMAT, ITEMS, JAR, KEYS, KEY_TYPE, LIMIT, LINES, LOAD, LOCATION, LOCK, LOCKS, LOGICAL, LONG, MAPJOIN, MATERIALIZED, METADATA, MINUS, MINUTE, MONTH, MSCK, NOSCAN, NO_DROP, OFFLINE, OPTION, OUTPUTDRIVER, OUTPUTFORMAT, OVERWRITE, OWNER, PARTITIONED, PARTITIONS, PLUS, PRETTY, PRINCIPALS, PROTECTION, PURGE, READ, READONLY, REBUILD, RECORDREADER, RECORDWRITER, REGEXP, RELOAD, RENAME, REPAIR, REPLACE, REPLICATION, RESTRICT, REWRITE, RLIKE, ROLE, ROLES, SCHEMA, SCHEMAS, SECOND, SEMI, SERDE, SERDEPROPERTIES, SERVER, SETS, SHARED, SHOW, SHOW_DATABASE, SKEWED, SORT, SORTED, SSL, STATISTICS, STORED, STREAMTABLE, STRING, STRUCT, TABLES, TBLPROPERTIES, TEMPORARY, TERMINATED, TINYINT, TOUCH, TRANSACTIONS, UNARCHIVE, UNDO, UNIONTYPE, UNLOCK, UNSET, UNSIGNED, URI, USE, UTC, UTCTIMESTAMP, VALUE_TYPE, VIEW, WHILE, YEAR | ALL, ALTER, AND, ARRAY, AS, AUTHORIZATION, BETWEEN, BIGINT, BINARY, BOOLEAN, BOTH, BY, CASE, CAST, CHAR, COLUMN, CONF, CREATE, CROSS, CUBE, CURRENT, CURRENT_DATE, CURRENT_TIMESTAMP, CURSOR, DATABASE, DATE, DECIMAL, DELETE, DESCRIBE, DISTINCT, DOUBLE, DROP, ELSE, END, EXCHANGE, EXISTS, EXTENDED, EXTERNAL, FALSE, FETCH, FLOAT, FOLLOWING, FOR, FROM, FULL, FUNCTION, GRANT, GROUP, GROUPING, HAVING, IF, IMPORT, IN, INNER, INSERT, INT, INTERSECT, INTERVAL, INTO, IS, JOIN, LATERAL, LEFT, LESS, LIKE, LOCAL, MACRO, MAP, MORE, NONE, NOT, NULL, OF, ON, OR, ORDER, OUT, OUTER, OVER, PARTIALSCAN, PARTITION, PERCENT, PRECEDING, PRESERVE, PROCEDURE, RANGE, READS, REDUCE, REVOKE, RIGHT, ROLLUP, ROW, ROWS, SELECT, SET, SMALLINT, TABLE, TABLESAMPLE, THEN, TIMESTAMP, TO, TRANSFORM, TRIGGER, TRUE, TRUNCATE, UNBOUNDED, UNION, UNIQUEJOIN, UPDATE, USER, USING, UTC_TMESTAMP, VALUES, VARCHAR, WHEN, WHERE, WINDOW, WITH |
Hive 2.0.0 | removed: REGEXP, RLIKE added: AUTOCOMMIT, ISOLATION, LEVEL, OFFSET, SNAPSHOT, TRANSACTION, WORK, WRITE | added: COMMIT, ONLY, REGEXP, RLIKE, ROLLBACK, START |
Hive 2.1.0 | added: ABORT, KEY, LAST, NORELY, NOVALIDATE, NULLS, RELY, VALIDATE | added: CACHE, CONSTRAINT, FOREIGN, PRIMARY, REFERENCES |
Hive 2.2.0 | added: DETAIL, DOW, EXPRESSION, OPERATOR, QUARTER, SUMMARY, VECTORIZATION, WEEK, YEARS, MONTHS, WEEKS, DAYS, HOURS, MINUTES, SECONDS | added: DAYOFWEEK, EXTRACT, FLOOR, INTEGER, PRECISION, VIEWS |
Hive 3.0.0 | added: TIMESTAMPTZ, ZONE | added: TIME, NUMERIC, SYNC |
保留关键字可允许作为列名通过使用引用标志符,大多数关键字都被保留以免产生歧义,如果仍要作为标识符,可有两种方法:1)使用引用标识符;1)set hive.support.sql11.reserved.keywords=false. (version 2.1.0 and earlier) |
2、DATABASE操作
2.1 【CREATE】
>create [remote] (database|schema) [if not exists] database_name
>[COMMENT database_comment]
>[LOCATION hdfs_path]
>[MANAGEDLOCATION hdfs_path]
>[with DBPROPERTIES (property_name=property_value,...)]
1)schema和database是内部可交换的,是相同的事情;
2)LOCATION指的是外部表(external tables)的默认存储目录;
3)MANAGEDLOCATION指的是内部表(managed tables)的存储目录,其公用一个共同的根目录/user/hive/warehouse/;
4).REMOTE数据库将会在Hive4.0.0添加以为数据连接器进行支持;
5).[]是可选语句,(|)是采用或操作且必须存在语句。
2.2 【DROP】
>DROP (database|schema) [if exists] database_name [RESTRICT|CASCADE];
1).默认的操作为RESTRICT,如果数据库非空,则删除失败;
2).强制删除在数据库中的所有表,则使用CASCADE;
2.3 【ALTER】
>ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...); -- (Note: SCHEMA added in Hive 0.14.0)
>ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)
>ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)
>ALTER (DATABASE|SCHEMA) database_name SET MANAGEDLOCATION hdfs_path; -- (Note: Hive 4.0.0 and later)
1).SET LOCATION/MANAGEDLOCATION并没有将当前目录数据库的内容移动到新指定的目录里,也并没改变与指定数据库关联的表、分区的位置,它仅仅只是改变了添加到这个数据库的新表的母目录;
2.4 【USE】
>USE database_name;
>SELECT current_database();//检查当前使用的数据库
3、CONNECTOR操作
3.1、【CREATE】
>CREATE CONNECTOR [IF NOT EXISTS] connector_name
>[TYPE datasource_type]
>[URL datasource_url]
>[COMMENT connector_comment]
>[WITH DCPROPERTIES (property_name=property_value, ...)];
1).Hive4.0.0开始可支持此操作,用来初始化基于MYSQL、POSTGRES、DERBY等数据来源的JDBC连接器实现工具,额外的连接器实现工具可以通过此操作实现;
2).TYPE为指定的连接器要连接的任意数据库的类型,如MYSQL,其决定着驱动器的类以及指定到这个数据库的参数;
3).URL表示远程数据库的URL,对于JDBC数据库,其将是JDBC连接的URL,对于Hive类型,其将是thrift的URL;
4).COMMENT表示连接器的描述;
5).DCPROPERTIES包含一系列关于连接器的键值对,包含了远程数据库的认证信息等等,所有初始的键都包含"hive.sql"的前缀。
3.2 【DROP】
>DROP CONNECTOR [IF EXISTS] connector_name;
当有在此连接器的数据库被使用时仍能够删除成功,但是在操作表时显示error。
3.3 【ALTER】
>ALTER CONNECTOR connector_name SET DCPROPERTIES (property_name=property_value, ...);
>ALTER CONNECTOR connector_name SET URL new_url;
>ALTER CONNECTOR connector_name SET OWNER [USER|ROLE] user_or_role;
OWNER更改在hive中连接器对象的拥有关系。
4、TABLE操作
4.1【CREATE】
1).创建新表
>CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)
>[(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
>[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]
> [SKEWED BY (col_name, col_name, ...) ON ((col_value, col_value, ...), (col_value, col_value, ...), ...) [STORED AS DIRECTORIES]
> [ROW FORMAT row_format]
> [STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]]
> [LOCATION hdfs_path]
> [TBLPROPERTIES (property_name=property_value, ...)]
> [AS select_statement];
2).根据已有表创建
>CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
>LIKE existing_table_or_view_name
>[LOCATION hdfs_path];
3).可选项解释
>data_type
: primitive_type(NUMERIC/STRING/DATE ect.)
| array_type
| map_type
| struct_type
| union_type -- (Note: Available in Hive 0.7.0 and later)
>primitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
| STRING
| BINARY -- (Note: Available in Hive 0.8.0 and later)
| TIMESTAMP -- (Note: Available in Hive 0.8.0 and later)
| DECIMAL -- (Note: Available in Hive 0.11.0 and later)
| DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later)
| DATE -- (Note: Available in Hive 0.12.0 and later)
| VARCHAR -- (Note: Available in Hive 0.12.0 and later)
| CHAR -- (Note: Available in Hive 0.13.0 and later)
>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, ... >
>row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] [NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later)
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
>file_format:
: SEQUENCEFILE
| TEXTFILE -- (Default, depending on hive.default.fileformat configuration)
| RCFILE -- (Note: Available in Hive 0.6.0 and later)
| ORC -- (Note: Available in Hive 0.11.0 and later)
| PARQUET -- (Note: Available in Hive 0.13.0 and later)
| AVRO -- (Note: Available in Hive 0.14.0 and later)
| JSONFILE -- (Note: Available in Hive 4.0.0 and later)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
>column_constraint_specification:
: [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
>default_value:
: [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ]
>constraint_specification:
: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
[, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE
[, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
[, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
1).Hive0.2之前,列名和表名之允许字符数字和下划线,Hive0.13以后,可以支持除了(.)和(:)之外所有unicode字符。在语句中,列名中使用(“`”)均表示文本意思,不表示特殊意义,而在(“`”)之内可以使用(“``”)代表嵌套,可以使得表和列名使用保留关键字;
2).表和列的COMMENT都是字符串文本(单引号);
3).可以通过describe extended table_name查看表是内还是外部表;
4).TBLPROPERTIES:给表贴上标签以及定义自己的元数据–键值对
预定义标签,默认添加
:last_modified_user,last_modified_time
:“comment”=“table_comment”
:“hbase.table.name”=“table_name”
:“immutable”=“true” | “immutable”=“false”
:“orc.compress”=“ZLIB” | “orc.compress”=“SNAPPY” | “orc.compress”=“NONE”
:“transactional”=“true” | “transactional”=“false” (默认)
:“NO_AUTO_COMPACTION”=“true” | “NO_AUTO_COMPACTION”=“false” (默认)
:“compactor.mapreduce.map.memory.mb”=“mapper_memory”
:“compactorthreshold.hive.compactor.delta.num.threshold”=“threshold_num”
:“compactorthreshold.hive.compactor.delta.pct.threshold”=“threshold_pct”
:“auto.purge”=“true” | “auto.purge”=“false”
:“EXTERNAL”=“TRUE” ,反之则FALSE
:“external.table.purge”=“true”
4). Row Formats和SerDe(Serialize/Deserilize)
自定义或者使用本地的SerDe策略(如果行格式未指定或者指定为DELIMITED)。
使用SerDe语句可以创建一个自定义SerDe的表,必须指定使用一个本地SerDe的表字段列表,Hive会查询SerDe来确定这个表真实的字段列表。
修改表的SerDe和SERDEPROPERTIES,使用ALTER TABLE语句。
下表为DDL表操作时使用的SerDe:
Row Format | 语法 | 描述 |
---|---|---|
Regex | ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.RegexSerDe’ WITH SERDEPROPERTIES(“input.regex” = “regex”) STORED AS TEXTFILE; | 存储为普通文本,通过正则表达式来转换,将每一个括号内正则表达式作为一行"regex"="([^]*) ([^]*) ([^]*) (-|\\[^\\]*\\]) “ |
JSON | ROW FORMAT SERDE ‘org.apache.hive.hcatalog.data.JsonSerDe’ STORED AS TEXTFILE | 以json格式存储为普通文本文件 |
CSV/TSV | ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.OpenCSVSerde’ WITH SERDEPROPERTIES ( “separatorChar” = “\t”, “quoteChar” = " ’ ", “escapeChar” = “\”) STORED AS TEXTFILE; | 以CSV/TSV形式存储为普通文本文件 |
5).分区表
通过PARTITIONED BY语句创建分区表。一个表可以有一个或者多个分区字段,并且由"分区字段.分区值"共同组成分区数据目录。分区表还可以进一步Bucket 通过CLUSTERED BY coloumns,数据还可以排序通过SORT BY columns,这样可以提高某些查询的效率。
创建分区表错误:"FAILED: Error in semantic analysis: Column repeated in partitioning columns,"表示创建的分区字段已经存在分区定义,需要将分区字段重新选定为其他字段。
6).外部表
Hive可以不用使用默认的目录来存储这个表,可以指定任意目录存储。这在有数据产生时非常方便,内部表不支持从文件系统中删除数据,也可以设置external.table.purge=true。
7).将查询结果创建表(Create Table As Select–CTAS)
在表未填入所有查询数据前,其他用户不会看到表的存在。Selece部分可以是任何select声明,Create部分能够将select部分作为查询表的结果。
目标表:不能为外部表,不能是一个Buketing 表的一部分列表。
如果select部分字段未设置别名,直接使用原字段名。
>CREATE TABLE new_value_store
>xxxxx
>SELECT XXXXX
8).Like创建表
只复制结构,不复制数据,相当于创建一个已有结构的空表。
9). Buketed Sorted Table
Clustered by 和Sorted by:创建Buket 表。
不影响数据的插入,只是影响数据的读操作。
10).Skewed表(倾斜表)
针对于其中一个或者多个字段具有倾斜值(也就是字段值大部分都在一个值聚集),通过Skewed能够提高表的性能,Hive自动根据具有倾斜值的字段分成单独的文件或目录,并在查询中能够根据这些文件跳过或者包括整个文件。
也可以通过表的ALTER操作进行声明。
11). Temporary临时表
临时表,只对于当前会话的用户可见,将数据存储在用户指定的目录中,并在会话结束时删除表。
注意:如果临时表和永久表名字相同,引用时只会引用临时表。应该重命名表名。
限制:不支持分区,不支持索引。
12).Transactional事务表
支持ACID语义的表。
>CREATE TRANSACTIONAL TABLE transactional_table_test(key string, value string) PARTITIONED BY(ds string) STORED AS ORC;
13).Constraints约束
Hive支持主键和外键,约束可以使得SQL变得更加有效率,这些约束是无验证的,当数据要加载到Hive时,上传系统需要确保数据的完整性。
Default在复合数据例如map、struct、array上是不支持的。
4.2 【Drop】
1)语法
>DROP TABLE [IF EXISTS] table_name [PURGE];
2).内部表会删除元数据和数据,如果.Trash/Current目录存在且PURGE未指定时会删除该目录,元数据会完完全全的删除。
外部表不会删除数据,除非设置 external.table.purge=true。
3).删除一个表时,引用该表的视图处于无效状态,必须被删除或者重新创建。
4).PURGE设定时,表数据不会去到.Trash/Current目录里,因此不能够在错误的DROP操作正确获取数据。
4.3 【TRUNCATE】
1).语法
>TRUNCATE [TABLE] table_name [PARTITION partition_spec];
partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value, ...)
2).如果文件系统的Trash是开启的,移除表的所有行到文件系统的Trash中,当auto.purge为true时,不会移动到Trash中。目标表应该时本地、内部表,或者可以移除部分或者保留分区字段。
5、ALTER操作
5.1 【ALTER TABLE】
1).重命名表名
>ALTER TABLE table_name RENAME TO new_table_name; //重命名表
2).改变表属性
>ALTER TABLE table_name SET TBLPROPERTIES table_properties;
table_properties:
: (property_name = property_value, property_name = property_value, ... )
3). 改变表备注
>ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);
4).添加SerDe属性
>ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties];
>ALTER TABLE table_name [PARTITION partition_spec] SET/UNSET SERDEPROPERTIES serde_properties;
serde_properties:
: (property_name = property_value, property_name = property_value, ... )
5).更改存储性质
>ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS; //更改了表的物理存储性质,仅仅只改变元数据,不会重新组织已存在的数据
6).更改表的Skewed或者是否存储为目录
>ALTER TABLE table_name SKEWED BY (col_name1, col_name2, ...) ON ([(col_name1_value, col_name2_value, ...) [, (col_name1_value, col_name2_value), ...] [STORED AS DIRECTORIES];//DIRECTORIES决定skewed表是否使用Buketing特征列表,即创建子目录存储skewed值
>ALTER TABLE table_name NOT SKEWED;//表取消skewed关闭buketing特征列表,影响ALTER声明之后的分区创建,不影响ALTER前的分区创建。
>ALTER TABLE table_name NOT STORED AS DIRECTORIES;//关闭buketing特征列表,虽然保留skewed
>ALTER TABLE table_name SET SKEWED LOCATION (col_name1="location1" [, col_name2="location2", ...] );//更改skewed的存储目录
7).更改表约束
在这里插入代码片
八、HiveQL DML(Data Manipulation Language)
九、HiveQL DRQ(Data Retrieval Query)
十、锁
十一、认证