怎么连接大数据hive库大数据 hive

转载

mob64ca13f9e726 2023-09-18 03:22:26

文章标签 怎么连接大数据hive库大数据 hive hadoop Hive 文章分类 Hive 大数据

大数据之Hive简介第一部分

一、Hive基本概念

Hive简介
Hive系统架构

二、Hive的安装

安装Hive
安装MySQL
配置Hive元数据到MySQL
访问Hive

使用元数据方式访问Hive
使用JDBC方式访问Hive

Hive常用交互命令
Hive常见属性配置
Hive常见参数配置

三、Hive的数据类型

基本数据类型
集合数据类型
类型转换

四、HiveDDL数据定义

创建数据库
创建表

五、HiveDML数据操作

数据导入

加载数据
插入数据

数据导出

六、Hive数据查询

查询语法

七、Hive分区表和分桶表（重点）

分区表
动态分区
分桶表

八、Hive的函数
九、数据压缩

开启压缩
文件存储格式

一、Hive基本概念

Hive简介

Hive 是Facebook开源用于解决海量结构化日志的数据统计工具。Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类 SQL 查询功能。Hive的本质如下，所以Hive比较适合处理大数据，小数据没有优势，时效性也是比较差的，所以Hive适合离线数据分析处理的场景，并且Hive自动生成MapReduce程序，因为效率比较低，同时调优也是相对比较困难。
（1）Hive存储的数据存储在HDFS上
（2）Hive分析数据底层的逻辑实现是MapReduce
（3）执行程序运行在yarn上面

Hive系统架构

Hive的系统架构可以看成是客户端-驱动器-作业执行器（MapReduce/Spark）-文件存储器（HDFS）构成。
Hive架构：
（1）用户接口，用户访问Hive的方式，主要有以下三种

CLI（command-line interface）命令行窗口模式、
JDBC/ODBC（Jdbc 访问 Hive）数据库连接模式，可在本地远程连接
WEBUI（浏览器访问 Hive）网页访问模式

（2）元数据（Metastore）
元数据包括：表名，数据库的库名（默认是default），表的拥有者，列/分区字段、字段类型、表类型、表所在的目录等表的和数据库的信息等。元数据信息默认存储在自带的derby数据库中，但是这个数据库不支持多人同时访问，因此一般需要配置使用MySql数据库在来存储元数据（Metastore）
（3）驱动器
驱动器包含解析器（SQL Parser），编译器（Physical Plan），优化器（Query Optimizer）和执行器（Execution）

解析器（SQL Parser）：将SQL字符串转换成抽象语法树 AST，这一步一般都用第三方工具库完成，比如antlr；再对AST进行语法分析，比如表是否存在、字段是否存在、SQL 语义是否有误等。
编译器（Physical Plan）：将 AST 编译生成逻辑执行计划。
优化器（Query Optimizer）：对逻辑执行计划进行优化。
执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于 Hive 来说，就是MR作业或者是Spark作业，具体视可执行的环境而定。

Hive 通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的 Driver，结合元数据(MetaStore)，将这些指令翻译成 MapReduce作业，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。
注意：部分数据在元数据的信息里直接查询，不会经过MapReduce程序。

二、Hive的安装

Hive下载地址，安装前需要先下载，请注意对应的版本号：http://archive.apache.org /dist/hive/

安装Hive

Hive安装流程如下：
（1）把 apache-hive-3.1.2-bin.tar.gz 上传到 linux 的/opt/software 目录下
（2）解压 apache-hive-3.1.2-bin.tar.gz 到/opt/module/目录下面

[mrlin@hadoop102 software]$ tar -zxvf /opt/software/apache-hive-3.1.2- bin.tar.gz -C /opt/module/

（3）将 apache-hive-3.1.2-bin的名称修改为 hive，方便后续操作

[mrlin@hadoop102 software]$ mv /opt/module/apache-hive-3.1.2-bin/ /opt/module/hive

（4）修改/etc/profile.d/my_env.sh，添加环境变量

[mrlin@hadoop102 software]$ sudo vim /etc/profile.d/my_env.sh

（5）环境变量后面添加以下内容，添加Hive的环境变量

#HIVE_HOME
export HIVE_HOME=/opt/module/hive
export PATH=$PATH:$HIVE_HOME/bin

（6）解决日志 Jar 包的冲突，修改包名

mv /opt/module/hive/lib/log4j-slf4j-impl-2.10.0.jar /opt/module/hive/lib/log4j-slf4j-impl-2.10.0.bak

（7）初始化元数据库，第一次需要初始化

[mrlin@hadoop102 hive]$ bin/schematool -dbType derby -initSchema

（8）启动hive，启动之前必须先启动HDFS集群

[mrlin@hadoop102 hive]$ bin/hive

安装MySQL

因为Hive默认数据库是derby，该数据库仅支持单用户操作，实际工作中都是多用户操作，因此需要修改为多用户操作的mysql。
（1）检查当前系统是否安装过 MySQL，有安装需要先卸载

[mrlin@hadoop102 ~]$ rpm -qa|grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
# 如果存在通过如下命令卸载
[mrlin@hadoop102 ~]$ sudo rpm -e --nodeps mariadb-libs

（2）将 MySQL 安装包拷贝到/opt/software 目录下

[mrlin @hadoop102 software]# ll
# 总用量 528384
-rw-r--r--. 1 root root 609556480 3 月 21 15:41 mysql-5.7.28- 1.el7.x86_64.rpm-bundle.tar

（3）解压 MySQL 安装包

tar -xf mysql-5.7.28-1.el7.x86_64.rpm- bundle.tar

（4）在安装目录下执行 rpm 安装，安装必须按照顺序安装，因为有依赖关系

[mrlin @hadoop102 software]$
sudo rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
sudo rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

（5）删除/etc/my.cnf 文件中 datadir 指向的目录下的所有内容，如果有内容的情况下先查看 datadir 的值：

cat /etc/my.cnf
[mysqld] datadir=/var/lib/mysql
# 删除/var/lib/mysql 目录下的所有内容:
[mrlin @hadoop102 mysql] cd /var/lib/mysql
[mrlin @hadoop102 mysql] sudo rm -rf ./*	# 该命令是删除目录下所有文件，注意执行命令的位置，否则会导致误删

（6）初始化mysql数据库

[mrlin @hadoop102 opt]$ sudo mysqld --initialize --user=mysql

（7）查看临时生成的 root 用户的密码

[mrlin @hadoop102 opt]$ sudo cat /var/log/mysqld.log

在最后面的root@localhost:XXXXXXXX，XXX部分即为密码，先拷贝复制
（8）启动 MySQL 服务，首次安装完需要手动启动，之后系统会添加进开机自启动，以后不用再次启动

[mrlin @hadoop102 opt]$ sudo systemctl start mysqld

（9）登录 MySQL 数据库

[mrlin @hadoop102 opt]$ mysql -uroot -p
Enter password:	# 输入临时生成的密码，粘贴上面拷贝复制的密码
# 登录成功.

（10）必须先修改 root 用户的密码,否则执行其他的操作会报错，且此处新密码必须与先设置的密码保持一致

mysql > set password = password("新密码");

（11）修改 mysql 库下的 user 表中的 root 用户允许任意 ip 连接，否则的话只能本机连接，实际生产使用具体用户修改，不使用root账户，因为root账户具有最高权限，不安全

# 查看用户表的信息
mysql > select host,user from mysql.user;
# 更新host为%，%为通配符，表示所有都可以
mysql > update mysql.user set host='%' where user='root';
# 刷新配置
mysql > flush privileges;

到这里只是安装了mysql数据库，并且配置了部分信息，现在需要将mysql跟Hive的元数据作连接，才能将Hive连接到MySQL中来。

配置Hive元数据到MySQL

Hive中元数据的概念，元数据其实就是数据库、数据表和字段信息等，HDFS里面才真正存储数据，它们是分开的，有时候删除了元数据里面的表，但是真正的数据还是存在的。
（1）拷贝驱动，将 MySQL 的 JDBC 驱动拷贝到 Hive 的 lib 目录下

[mrlin@hadoop102 software]$ cp /opt/software/mysql-connector-java- 5.1.37.jar $HIVE_HOME/lib

（2）配置元数据（Metastore）到 MySQL

在/opt/module/hive/conf 目录下新建 hive-site.xml 文件

[mrlin@hadoop102 software]$ vim /opt/module/hive/conf/hive-site.xml

文件添加如下内容

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
	<!-- jdbc 连接的 URL -->
	<property>
		<name>javax.jdo.option.ConnectionURL</name>
		<value>jdbc:mysql://hadoop102:3306/metastore?useSSL=false</value>
	</property>
	<!-- jdbc 连接的 Driver-->
	<property>
		<name>javax.jdo.option.ConnectionDriverName</name>
		<value>com.mysql.jdbc.Driver</value>
	</property>
	<!-- jdbc 连接的 username-->
	<property>
		<name>javax.jdo.option.ConnectionUserName</name>
		<value>root</value>
	</property>
	<!-- jdbc 连接的 password -->
	<property>
		<name>javax.jdo.option.ConnectionPassword</name>
		<value>000000</value>
	</property>
	<!-- Hive 元数据存储版本的验证 -->
	<property>
		<name>hive.metastore.schema.verification</name>
		<value>false</value>
	</property>
	<!--元数据存储授权-->
	<property>
		<name>hive.metastore.event.db.notification.api.auth</name>
		<value>false</value>
	</property>
	<!-- Hive 默认在 HDFS 的工作目录 -->
	<property>
		<name>hive.metastore.warehouse.dir</name>
		<value>/user/hive/warehouse</value>
	</property>
</configuration>

（2）登陆 MySQL，这里会报密码明文显示的警告，需要去除，只需将密码在下一行输入即可（推荐也是密文比较好）

[mrlin@hadoop102 software]$ mysql -uroot -p000000

（3）新建 Hive 元数据库

mysql> create database metastore; 
mysql> quit;

（4）初始化 Hive 元数据库，schematool命令在/opt/module/hive/bin/目录下的schematool

[mrlin@hadoop102 software]$ schematool -initSchema -dbType mysql -verbose

初始化完成后，此时就可以多用户多窗口访问Hive了。

访问Hive

使用元数据方式访问Hive

（1）在 hive-site.xml 文件中添加如下配置信息

<!-- 指定存储元数据要连接的地址 -->
<property>
	<name>hive.metastore.uris</name>
	<value>thrift://hadoop102:9083</value>
</property>

（2）启动 metastore

[mrlin@hadoop202 hive]$ hive --service metastore
2020-04-24 16:58:08: Starting Hive Metastore Server

注意: 启动后窗口不能再操作，需打开一个新的 shell 窗口做别的操作
（3）启动 hive

[mrlin@hadoop202 hive]$ bin/hive

使用JDBC方式访问Hive

（1）在 hive-site.xml 文件中添加如下配置信息

<!-- 指定 hiveserver2 连接的 host -->
<property>
	<name>hive.server2.thrift.bind.host</name>
	<value>hadoop102</value>
</property>

<!-- 指定 hiveserver2 连接的端口号 -->
<property>
	<name>hive.server2.thrift.port</name>
	<value>10000</value>
</property>

（2）启动 hiveserver2

[mrlin@hadoop102 hive]$ bin/hive --service hiveserver2

（3）启动 beeline 客户端（需要多等待一会）

[mrlin@hadoop102 hive]$ bin/beeline -u jdbc:hive2://hadoop102:10000 -n mrlin

（4）看到如下界面，即为连接成功

Connecting to jdbc:hive2://hadoop102:10000
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.2 by Apache Hive
0: jdbc:hive2://hadoop102:10000>

简单使用Hive
（1）启动Hive

[mrlin@hadoop102 hive]$ bin/hive

（2）使用Hive

hive> show databases; 
hive> show tables;
hive> create table student (id int); 
hive> insert into student values(1); 
hive> select * from student ;

（3）另外开启另一个窗口开启 Hive

hive> show databases; 
hive> show tables; 
hive> select * from aa;

（4）编写hive服务启动脚本
在上面的启动Hive中，我们总是需要在前台开启多个shell窗口，其实这很不方便。最好的方法就是将Hive启动到后台运行。
nohup: 放在命令开头，表示不挂起,也就是关闭终端进程也继续保持运行状态
/dev/null：是 Linux 文件系统中的一个文件，被称为黑洞，所有写入改文件的内容都会被自动丢弃
2>&1 : 表示将错误重定向到标准输出上
&: 放在命令结尾,表示后台运行
一般会组合使用: nohup [xxx 命令操作]> file 2>&1 &，表示将 xxx 命令运行的结果输出到 file 中，并保持命令启动的进程在后台运行，如下：

[mrlin@hadoop202 hive]$ nohup hive --service metastore 2>&1 & 
[mrlin@hadoop202 hive]$ nohup hive --service hiveserver2 2>&1 &

为了方便使用，也可以通过直接编写脚本的方式来管理服务的启动和开启：

[mrlin@hadoop102 hive]$ vim $HIVE_HOME/bin/hiveservices.sh 
# 里面的内容如下：
#!/bin/bash HIVE_LOG_DIR=$HIVE_HOME/logs if [ ! -d $HIVE_LOG_DIR ] then
mkdir -p $HIVE_LOG_DIR
fi
#检查进程是否运行正常，参数 1 为进程名，参数 2 为进程端口
function check_process()
{
	pid=$(ps -ef 2>/dev/null | grep -v grep | grep -i $1 | awk '{print$2}')
	ppid=$(netstat -nltp 2>/dev/null | grep $2 | awk '{print $7}' | cut - d '/' -f 1)
	echo $pid
	[[ "$pid" =~ "$ppid" ]] && [ "$ppid" ] && return 0 || return 1
}

function hive_start()
{
	metapid=$(check_process HiveMetastore 9083)
	cmd="nohup hive --service metastore >$HIVE_LOG_DIR/metastore.log 2>&1 &"
	[ -z "$metapid" ] && eval $cmd || echo "Metastroe 服务已启动" server2pid=$(check_process HiveServer2 10000)
	cmd="nohup hiveserver2 >$HIVE_LOG_DIR/hiveServer2.log 2>&1 &"
	[ -z "$server2pid" ] && eval $cmd || echo "HiveServer2 服务已启动"
}

function hive_stop()
{
	metapid=$(check_process HiveMetastore 9083)
	[ "$metapid" ] && kill $metapid || echo "Metastore 服务未启动" server2pid=$(check_process HiveServer2 10000)
	[ "$server2pid" ] && kill $server2pid || echo "HiveServer2 服务未启动"
}

case $1 in "start")
	hive_start
	;;
"stop")
	hive_stop
	;;
"restart")
	hive_stop sleep 2 hive_start
	;;
"status")
	check_process HiveMetastore 9083 >/dev/null && echo "Metastore 服务运行 正常" || echo "Metastore 服务运行异常"
	check_process HiveServer2 10000 >/dev/null && echo "HiveServer2 服务运 行正常" || echo "HiveServer2 服务运行异常"
	;;
*)
	echo Invalid Args!
	echo 'Usage: '$(basename $0)' start|stop|restart|status'
	;;
esac

再添加执行权限，并在后台启动服务进程：

chmod +x $HIVE_HOME/bin/hiveservices.sh
hiveservices.sh start

Hive常用交互命令

[mrliin@hadoop102 hive]$ bin/hive -help
usage: hive
 -d,--define <key=value>          Variable substitution to apply to Hive
								  commands. e.g. -d A=B or --define A=B
	--database <databasename>     Specify the database to use
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
	--hiveconf <property=value>   Use value for given property
	--hivevar <key=value>         Variable substitution to apply to Hive
								  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
								  console)

（1）“-e”不进入 hive 的交互窗口执行 sql 语句

[mrlin@hadoop102 hive]$ bin/hive -e "select id from student;"

（2）“-f”执行脚本中的 sql 语句

在/opt/module/hive/下创建 datas 目录并在 datas 目录下创建 hivef.sql 文件

[mrlin@hadoop102 datas]$ touch hivef.sql

文件中写入正确的 sql 语句

select *from student;

执行文件中的 sql 语句

[mrlin@hadoop102 hive]$ bin/hive -f /opt/module/hive/datas/hivef.sql

执行文件中的 sql 语句并将结果写入文件中

[mrlin@hadoop102 hive]$ bin/hive -f /opt/module/hive/datas/hivef.sql > /opt/module/datas/hive_result.txt

（3）退出命令，可以输入以下命令，或者直接ctrl+c

hive(default)>exit;
hive(default)>quit;

（4）在 hive cli 命令窗口中查看 hdfs 文件系统

hive(default)>dfs -ls /;

（5）查看在 hive 中输入的所有历史命令

进入到当前用户的根目录 /root 或/home/mrlin
查看. hivehistory 文件

Hive常见属性配置

（1）修改Hive的log默认存放路径
Hive 的 log 默认存放在/tmp/mrlin/hive.log 目录下（当前用户名下），将其修改存放日志到/opt/module/hive/logs目录下
先修改/opt/module/hive/conf/hive-log4j2.properties.template 文件名称为hive-log4j2.properties：

[mrlin@hadoop102 conf]$ pwd
/opt/module/hive/conf
[mrlin@hadoop102 conf]$ mv hive-log4j2.properties.template hive- log4j2.properties

再在 hive-log4j2.properties 文件中修改 log 存放位置

vim /opt/module/hive/conf/hive-log4j2.properties
hive.log.dir=/opt/module/hive/logs

（2）设置打印当前库和表头，默认Hive是不打印数据库的名称和表名称的，在多库多用户的情况下，为了避免混淆，将数据库和表的信息一同打印出来，在 hive-site.xml 中加入如下两个配置:

<property>
	<name>hive.cli.print.header</name>
	<value>true</value>
</property>

<property>
	<name>hive.cli.print.current.db</name>
	<value>true</value>
</property>

Hive常见参数配置

查看当前配置信息命令如下：

hive>set;

参数的配置有三种方式，分别如下：
（1）根据配置文件配置，默认配置文件：hive-default.xml，用户自定义配置文件：hive-site.xml
用户自定义配置会覆盖默认配置。另外，Hive 也会读入 Hadoop 的配置，因为 Hive 是作为 Hadoop 的客户端启动的，Hive 的配置会覆盖 Hadoop 的配置。配置文件的设定对本机启动的所有 Hive 进程都有效。配置文件的读取的顺序依次为hadoop配置 ==> 默认配置hive-default.xml ==> 用户自定义配置hive-site.xml，因为后读取的会覆盖前面的配置所以越往后，优先级越高。
（2）命令行参数方式，启动 Hive 时，可以在命令行添加-hiveconf param=value 来设定参数
例如：

[mrlin@hadoop103 hive]$ bin/hive -hiveconf mapred.reduce.tasks=10;

注意：仅对本次 hive 启动有效查看参数设置：

hive (default)> set mapred.reduce.tasks;

（3）参数声明方式，可以在 HQL 中使用 SET 关键字设定参数
例如：

hive (default)> set mapred.reduce.tasks=100;

注意：仅对本次 hive 启动有效。查看参数设置

hive (default)> set mapred.reduce.tasks;

上述三种设定方式的优先级依次递增。即配置文件<命令行参数<参数声明。注意某些系统级的参数，例如 log4j 相关的设定，必须用前两种方式设定，因为那些参数的读取在会话建立以前已经完成了。

三、Hive的数据类型

基本数据类型

基本常用类型包括：TINYINT（1byte），SMALINT（2byte），INT，BIGINT，BOOLEAN，FLOAT，DOUBLE，STRING，TIMESTAMP(时间类型)，BINARY(字节数组)，其中string类型，相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储 2GB 的字符数。

集合数据类型

Hive 有三种复杂数据类型 ARRAY、MAP 和 STRUCT。ARRAY 和 MAP 与 Java 中的 Array 和 Map 类似，而 STRUCT 与 C 语言中的 Struct 类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

结构体Struct
和 c 语言中的 struct 类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是 STRUCT{first STRING, last STRING},那么第 1 个元素可以通过字段.first 来引用。

键值对Map
MAP 是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是 MAP，其中键
->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素

数组Array
数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第 2 个元素可以通过数组名[1]进行访问。

简单演示：
（1）基于上述数据结构，我们在 Hive 里创建对应的表，并导入数据。

# 创建本地测试文件 test.txt
[mrliin@hadoop102 hive]$ mkdir datas
[mrliin@hadoop102 hive]$ cd datas
[mrliin@hadoop102 datas]$ vim test.txt
# 输入以下内容：
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

注意：MAP，STRUCT 和 ARRAY 里的元素间关系都可以用同一个字符表示，这里用“_”。
（2）在Hive上创建测试表 test

[mrliin@hadoop102 hive]$ bin/hive
hive (default)> create table test(
             > 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 by ':'
             > lines terminated by '\n';

字段解释：

row format delimited fields terminated by ‘,’ 列分隔符为逗号 ‘,’
collection items terminated by '’ MAP、STRUCT 和 ARRAY元素间的分隔符为下划线 ‘’
map keys terminated by ‘:’ MAP 中的 key 与 value 的分隔符为冒号 ‘:’
lines terminated by ‘\n’; 每行数据间的分隔符为换行符
（3）导入文本数据到测试表

hive (default)> load data local inpath '/opt/module/hive/datas/test.txt' into table test;

（4）访问三种集合列里的数据，以下分别是 ARRAY，MAP，STRUCT 的访问方式

hive (default)> select friends[1],children['xiao song'],address.city from test where name="songsong";

类型转换

Hive 的原子数据类型是可以进行隐式转换的，类似于 Java 的类型转换，例如某表达式使用 INT 类型，TINYINT 会自动转换为 INT 类型，但是 Hive 不会进行反向转化，例如，某表达式使用 TINYINT 类型，INT 不会自动转换为 TINYINT 类型，它会返回错误，除非使用 CAST 操作。
（1）隐式类型转换规则如下：

任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成INT，INT 可以转换成 BIGINT。
所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。
TINYINT、SMALLINT、INT 都可以转换为 FLOAT。
BOOLEAN 类型不可以转换为任何其它的类型。

（2）可以使用CAST进行强制类型转换：
例如 CAST(‘1’ AS INT)将把字符串’1’ 转换成整数 1；如果强制类型转换失败，如执行 CAST(‘X’ AS INT)，表达式返回空值 NULL。

四、HiveDDL数据定义

数据定义语言，一般用的比较少，用时可以理解，查表即可。

创建数据库

基本语法：

CREATE DATABASE [IF NOT EXISTS] database_name 
	[COMMENT 'database_comment']
	[LOCATION 'hdfs_path']
	[WITH DBPROPERTIES ('property_name'='property_value', ...)];

简单说明：
[] 里面为可选项，最简单的创建数据库语句为： create database abc; – 创建名为abc的数据库
[IF NOT EXISTS] 当数据库名称已经存在时，不加 if not exists 系统会报错；
COMMENT 后面接的是数据库的注释
LOCATION 后面接的是数据库存储的位置
DBPROPERTIES dbpropertier接的是数据库的额外属性，可以有name，date等，说明创建数据的用户和日期等，这个属性不存储在原数据的DBS表里面，而是存储在元数据表DATABASE_PARAMS里面

简单示例：

create database if not exists abc
	comment 'first test database'
	location '/user/hive/warehouse/abc.db'
	with dbproperties ('name'='mrlin','date'='2022-11-11');

数据库常用操作：

# 显示数据库
hive (default)> show databases;
# 显示数据库可以加模糊匹配
hive (default)> show databases like 'ab%';
# 查看数据库的描述属性
hive (default)> desc database abc;
# 通过 dbpropertier 添加的属性需要用下面的扩展命令才能查看
hive (default)> desc database extended abc;
# 切换使用的数据库
hive (default)> use abc;
# 使用完之后命令行的前面会变成：
hive (abc)>
# dbproperties 设置的值也可以通过后续进行修改
hive (abc)> alter database abc set dbproperties('name'='lin');
# 删除空数据库
hive (abc)>drop database abcd;
# 上面如果数据库不存在，这会报错
hive (abc)>drop database if exists abcd;
hive (abc)>drop database if exists abc;
# 删除非空数据库必须使用强制指令(谨慎使用**)
drop database abc cascade;

创建表

基本语法：

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]
	[TBLPROPERTIES (property_name=property_value, ...)]
	[AS select_statement]

简单说明：
其中IF NOT EXISTS，COMMENT跟创建数据库时一致，为存在时不报错和对应的注释，相同的情况不再说明。
EXTERNAL 外部表关键字，加上外部表关键字，hive认为改表数据不为hive独有，所有执行drop table删除表的操作时，只会删除数据库里面的元数据，真正的数据仍然保存在hdfs中，生产中多为外部表。不加此关键字则是，删除表drop table时会把元数据和hdfs的真实数据一起删除；
col_name 列名，后面再接列的类型data_type，和该列的注释col_comment，列可以多个，用’,'分隔开；
PARTITIONED BY 创建分区表，后面会详细解释；
CLUSTERED BY 创建分桶表，后面也会详细解释；
SORTED BY 不常用，对桶中的一个或多个列另外排序；
ROW FORMAT SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化；
STORED AS 指定存储文件类型常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）。如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
LOCATION 指定表在HDFS上面的存储位置
AS 后跟查询语句，根据查询结果创建表；
LIKE 允许用户复制现有的表结构，但是不复制数据。

简单示例：

# 实例，创建员工表：
create external table if not exists emp( empno int,
ename string, job string, mgr int,
hiredate string, sal double,
comm double, deptno int)
row format delimited fields terminated by '\t';
# 创建员工表2，只复制结构
hive (default)> create table is not exists emp2 like tmp;

表常用操作：

# 查看有哪些表
hive (default)>show tables; 
# 查看表的类型
hive (default)> desc formatted emp;
# 查看表的结构
hive (default)> desc emp;
# 修改表的类型为管理表（也就是内部表）
hive (default)> alter table student2 set tblproperties('EXTERNAL'='FALSE');
# 修改表的类型为外部表
hive (default)> alter table student2 set tblproperties('EXTERNAL'='TRUE'); 	
# 重命名表
alter table table_name rename to new_table_name;
# 更新列语法
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
# 增加和替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
hive (default)> create table abc(id int);
hive (default)> desc abc;
hive (default)> alter table abc add columns(name string);
hive (default)> alter table abc replace columns(deptno string, dname string, loc string);
# 删除表
hive (default)> drop table abc; 
# 清空表数据，清空只能清空内部表（管理表），无法清空外部表。
hive (abc)> truncate table student;

注：(‘EXTERNAL’=‘FALSE’)和(‘EXTERNAL’=‘TRUE’)的写法为固定写法，区分大小写，不可更改
注：ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)，REPLACE 则是表示替换表中所有字段。

五、HiveDML数据操作

数据导入

加载数据

基本语法：

hive> load data [local] inpath '数据的 path' [overwrite] into table student [partition (partcol1=val1,…)];

简单说明：
load data:表示加载数据
local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表
inpath:表示加载数据的路径
overwrite:表示覆盖表中已有数据，否则表示追加
into table:表示加载到哪张表
student:表示具体的表
partition:表示上传到指定分区

简单示例：

# 创建student.txt文件，并输入以下内容
[mrliin@hadoop102 hive]$ vim datas/student.txt
1001,aa
1002,bbb
1003,cc

# 进入数据库，
[mrliin@hadoop102 hive]$ bin/hive
hive (default)> use abc;
# 并创建表student
hive (abc)> create table student(id string,name string) row format delimited fields terminated by ',';
# 加载本地表的数据到abc下的student表
hive (default)> load data local inpath '/opt/module/hive/datas/student.txt' into table abc.student;
# 将数据上到HDFS上，HDFS上创建datas目录，并上传文件
hive (abc)> dfs -mkdir /user/hive/datas;
hive (abc)> dfs -put /opt/module/hive/datas/student.txt /user/hive/datas/;
# 加载数据到abc下的student表
hive (abc)> load data inpath '/user/hive/datas/student.txt' into table abc.student;
# 查看student表里面的数据
hive (abc)> select * from student;
# 再次覆盖上传
hive (abc)> load data local inpath '/opt/module/hive/datas/student.txt' overwrite into table abc.student;

注：执行load命令后，本地的文件仍然存在，但是HDFS上面的文件会删除。

插入数据

# 创建临时表：
hive (abc)> create table student_tmp(id string,name string) row format delimited fields terminated by ',';
# 向临时表插入数据：
hive (abc)> insert into table student_tmp values('1004','wanggu'),('1004','wangli');

因为上面的修改都是修改元数据，或者直接数据跟表关联（load），一直都没有用到MapReduce，这里的数据插入需要用到MapReduce，所以插入很慢。加载数据一定不能使用MapReduce，使用本地加载才最好，MapReduce只能用于分析，不然，效率极低。

# 查询向表中插入数据：
insert overwrite table student_tmp select id,name from student;
# 其中insert into是追加的方式添加数据，insert overwrite是覆盖的方式添加数据。
# 通过查询一个表建表
create table if not exists student2
	as select id,name from student where xxx;
# 通过在hdfs指定位置创建表，会默认将该路径的表数据导入
# 上传数据
hive (abc)> dfs -put /opt/module/hive/datas/student.txt /user/hive/datas/
# 在该位置创建表student2
hive (abc)> create external table if not exists student2(id string,name string)
         > row format delimited fields terminated by ','
         > location '/user/hive/datas';
# 查询表数据
hive (abc)> select * from student2;

数据导出

（1）export导出和import导入，导入导出的路径都是HDFS上面的路径，如果导出的路径不存在，则会自动创建，expoer和import主要用于两个hadoop集群之前的Hive表迁移。

hive (abc)> export table student2 to '/opt/module/hive/datas/student2';
hive (abc)> import table student3 from '/opt/module/hive/datas/student2';

（2）可以用insert将数据导出到本地，不过不推荐使用这种方式，因为要用MapReduce

#  将查询结果导出到本地
hive (abc)> insert overwrite  local directory '/opt/module/hive/datas/export/student' 
         > select * from student;
# 将查询的结果格式化再导出到本地
hive(default)>insert overwrite local directory '/opt/module/hive/data/export/student1'
			ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
			select * from student;
# 将查询的结果导出到HDFS上(没有接local关键字)
hive (default)> insert overwrite directory '/user/mrlin/student2' 
				ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
				select * from student;

（3）使用hadoop方式导出到本地

hive (default)> dfs -get /user/hive/warehouse/student/student.txt
			/opt/module/data/export/student3.txt;

（4）Hive shell的命令导出

[mrliin@hadoop102 hive]$ bin/hive -e 'select * from abc.student;' > /opt/module/hive/datas/export/student1.txt

六、Hive数据查询

查询语法

基本语法：

SELECT [ALL | DISTINCT] select_expr [AS expr_name], select_expr, ...
	FROM table_reference 
	[WHERE where_condition] 
	[GROUP BY col_list] 
	[ORDER BY col_list] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]
	[LIMIT number]

简单说明：
DISTRIBUTE BY 用于分区显示，类似于MR程序中的分区
SORT BY 用于分区内排序，因为往往不需要总的排序，只需部分有序
CLUSTER BY 当DISTRIBUTE BY和SORT BY的字段一致时，也就是分区和排序的字段一致时，那么就可以使用CLUSTER BY代替。
limit 用于限制返回的行数
语法注意：

SQL 语言大小写不敏感。
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写。
使用缩进提高语句的可读性。

基本的查询跟SQL语句一致，这里不再赘述。

用法示例：
基本查询、简单函数查询例如：count(),max(),min(),sum(),avg()、比较运算符如>,<,=,<>、逻辑运算符如and,or,not、分组group by having、left join,right join,full join等跟SQL基本都是一样的，这里都不再介绍。
（1）RLIKE模糊匹配
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
RLIKE 子句是 Hive 中模糊匹配功能的一个扩展，它可以通过 Java 的正则表达式这个更强大的功能来指定匹配条件。除非是能头部匹配，使用Like，否则都使用Rlike。

# 查找名字中带有A的学生信息
hive (abc)> select * from student where name RLIKE '[A]'; 
# 可以使用LIKE
hive (abc)> select * from student where name LIKE 'A%'; 
# 这种情况直接用上面的RLIKE，不推荐使用LIKE
hive (abc)> select * from student where name LIKE '%A%';

（2）Join连接
Join连接的写法其实跟SQL是一样的，但当对 3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接字段的话，那么只会产生一个 MapReduce job，可以达到优化的效果。
（3）全局排序和局部排序
正常的order by只会产生一个Reduce进行排序，这在分布式效率极低。但在大多数情况下，并不需要全局排序，所以Sort by是为每一个reducer排序，每个reducer内部有序，但是全局来说不是有序。

# 设置 reduce 个数为 3
hive (abc)> set mapreduce.job.reduces=3; 	
# 查看设置 reduce 个数
hive (abc)> set mapreduce.job.reduces; 	
# 根据部门编号降序查看员工信息
hive (abc)> select * from emp sort by deptno desc; 
# 将查询结果导入到文件中（按照部门编号降序排序）
hive (abc)> insert overwrite local directory '/opt/module/data/sortby-result'
	select * from emp sort by deptno desc;

（4）分区
Distribute By：在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作，distribute by 子句可以做这件事。distribute by 类似MR中 partition（自定义分区），进行分区，结合 sort by 使用。对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。

# 先按照部门编号分区，再按照员工编号降序排序。
hive (default)> set mapreduce.job.reduces=3; 
hive (default)> insert overwrite local directory '/opt/module/data/distribute-result'
	select * from emp distribute by deptno sort by empno desc;

注意：

distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，余数相同的分到一个区。
Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

Cluster By：
当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。

# 以下两种写法等价
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;

七、Hive分区表和分桶表（重点）

分区表

分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。
创建分区表：

hive (abc)> create table dept_partition(deptno int,dname string) 
	partitioned by (day string) row format delimited fields terminated by '\t';

创建分区的日志文件：

# 在/opt/module/hive/datas/下面创建dept_20200401.log，dept_20200402.log，dept_20200403.log文件
[mrliin@hadoop102 datas]$ vim dept_20220401.log
[mrliin@hadoop102 datas]$ vim dept_20220402.log
[mrliin@hadoop102 datas]$ vim dept_20220403.log

导入分区表数据：

hive (abc)> load data local inpath '/opt/module/hive/datas/dept_20220401.log' 
	into table dept_partition partition(day='20220401');
hive (abc)> load data local inpath '/opt/module/hive/datas/dept_20220402.log' 
	into table dept_partition partition(day='20220402');
hive (abc)> load data local inpath '/opt/module/hive/datas/dept_20220403.log' 
	into table dept_partition partition(day='20220403');

注意：分区表加载数据时，必须指定分区。

# 数据查询
hive (abc)> select * from dept_partition where day='20220401';

增加分区

hive (abc)> alter table dept_partition add partition(day='20200404');
# 同时添加多个分区，注意这里间隔是空格，只能是空格，其他间隔报错
hive (abc)> alter table dept_partition add partition(day='20200405') partition(day='20200406');

删除分区

hive (abc)> alter table dept_partition drop partition (day='20200406');
# 同时删除多个分区，注意这里间隔是逗号，只能是逗号，其他间隔报错
hive (abc)> alter table dept_partition drop partition (day='20200404'), partition(day='20200405');

查看分区表有多少分区

hive (abc)> show partitions dept_partition;

查看分区表结构

hive (abc)> show formatted dept_partition;

二级分区
当数据量很大时，可以考虑实行二级分区，进一步降低每个分区的数据量。

# 创建二级分区表
hive (abc)> create table dept_partition2(deptno int,dname string) 
	partitioned by (day string,hour string);
# 加载数据
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' 
	into table dept_partition2 partition(day='20200401', hour='12');
# 查询数据
hive (default)> select * from dept_partition2 where day='20200401' and hour='12';

数据上传到分区目录上，让分区表和数据产生关联的三种方式。其实就是元数据里面没有该分区的目录信息，下面的方式可以将分区的信息更新到元数据上面去。
（1）上传数据后修复

# 创建分区目录
hive (abc)> dfs -mkdir -p /user/hive/warehouse/abc.db/dept_partition2/day=20220401/hour=13;
# 上传数据到分区目录
hive (abc)> dfs -put /opt/module/hive/datas/dept_20220401.log /user/hive/warehouse/abc.db/dept_partition2/day=20220401/hour=13;
# 执行修复命令，不执行此命令，下面的语句是查不到数据的，因为分区数据跟分区目录还没有关联上
hive (abc)> msck repair table dept_partition2;
# 查询数据
hive (abc)> select * from dept_partition2 where day='20220401' and hour='13';

（2）上传数据后添加分区

# 创建分区目录
hive (abc)> dfs -mkdir -p /user/hive/warehouse/abc.db/dept_partition2/day=20220401/hour=14;
# 上传数据到分区目录
hive (abc)> dfs -put /opt/module/hive/datas/dept_20220401.log /user/hive/warehouse/abc.db/dept_partition2/day=20220401/hour=14;
# 执行修复命令，不执行此命令，下面的语句是查不到数据的，因为分区数据跟分区目录还没有关联上
hive (abc)> alter table dept_partition2 add partition(day='20220401',hour='14');
# 查询数据
hive (abc)> select * from dept_partition2 where day='20220401' and hour='14';

（3）创建文件夹后加载数据到分区

# 创建分区目录
hive (abc)> dfs -mkdir -p /user/hive/warehouse/abc.db/dept_partition2/day=20220401/hour=15;
# load数据到分区
hive (abc)> load data local inpath '/opt/module/hive/datas/dept_20220401.log' into table dept_partition2 partition(day='20220401',hour='15');
# 查询数据
hive (abc)> select * from dept_partition2 where day='20220401' and hour='15';

动态分区

关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用 Hive 的动态分区，需要进行相应的配置。
开启动态分区参数设置：
（1）开启动态分区功能（默认 true，开启）

hive.exec.dynamic.partition=true

（2）设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）

hive.exec.dynamic.partition.mode=nonstrict

（3）在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000

hive.exec.max.dynamic.partitions=1000

（4）在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。

hive.exec.max.dynamic.partitions.pernode=100

（5）整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000

hive.exec.max.created.files=100000

（6）当有空分区生成时，是否抛出异常。一般不需要设置。默认 false

hive.error.on.empty.partition=false

动态分区字段指定：

# 创建分区表，并指定deptno为分区字段
create table dept_no_par(dname string,loc string) partition by (deptno int) 
	row format delimited fields terminated by '\t';

（1）静态插入数据，表dept里面没有deptno=70的数据

insert into table dept_no_par partition(deptno='70') select dname,loc from dept;

（2）动态插入数据，直接写分区字段，deptno，不用加其他，在查询处的最后一个字段作为分区的数据来源

insert into table dept_no_par partition(deptno) select dname,loc,deptno from dept;

（3）动态插入数据3.0最新写法，可以不写分区的字段，直接查询即可，注意也是最后一个字段作为分区的数据来源，此时不改为非严格模式也是可以的。

insert into table dept_no_par select dname,loc,deptno from dept;

分桶表

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive可以进一步组织成桶，也就是更为细粒度的数据范围划分。分桶是将数据集分解成更容易管理的若干部分的另一个技术。分区针对的是数据的存储路径；分桶针对的是数据文件。
创建分桶表

create table stu_buck(id int,name string) clustered by(id) into 4 buckets 
	row format delimited fields terminated by '\t';

注意：分区表的字段，表里面不能有；分桶表的字段，表里面必须有

# 查看分桶表结构
desc formatted stu_buck;
# 创建数据，并导入分桶表，因为是分桶，需要经过哈希索引，所以此处一定是走mapreduce通道
load data inpath '/student.txt' into table stu_buck; 
# insert 方式将数据导入分桶表
hive(default)>insert into table stu_buck select * from student_insert; 
# 查询分桶的数据
select * from stu_buck;

分桶规则：
Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。比如是：1001,1002,1003,1004,1005,1006 分3桶的话，是按照余数相同分一个桶，就会1001,1004在一个桶，1002,1005在一个桶，1003，1006在一个桶。

分桶表注意事项：
（1）reduce 的个数设置为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个数设置为大于等于分桶表的桶数。设置为-1之后，reduce会自动根据桶的数量来划分reduce
（2）从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题
（3）不要使用本地模式

抽样调查：
对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive 可以通过对表进行抽样来满足这个需求。
语法：Tablesample(bucket x out of y)，其中x的值必须小于等于y的值，否则报错

# 此处4和1的意思是，分为4等分，从第1份开始抽，
hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

八、Hive的函数

（1）查看函数用法：

# 查看系统自带的函数
hive (abc)> show functions;
# 显示自带函数的用法
hive (abc)> desc function upper;
# 详细显示自带函数的用法
hive (abc)> desc function extended upper;

（2）行转列

concat(string A/col,string B/col…) 返回输入字符串连接后的结果，支持任意个输入字符串或字段，如concat(%,abm,%) 结果为： %abm%
concat_ws(separator, str1, str2,…) concat_ws是一个特殊格式的concat，第一个是分隔符，将后面的连接起来，如concat_ws(‘|’,df,df,df,df) 结果为 df|df|df|df。concat_ws 后面的参数还能接数组。
collect_set(col) 函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段，聚合函数。
collect_list(col) 函数返回数组，不去重。
（3）列转行
explode(col) 将一行，数组中的多个元素，返回多行，每行一个元素。
lateral view explode(col) 将hive一系列复杂的Array或者Map结构拆成多行。

列转行简单示例：

SELECT movie, category_name FROM movie_info 
lateral VIEW
explode(split(category,",")) movie_info_tmp AS category_name;

（4）自定义函数
Hive提供了用户可以自定义函数的接口，可以自己编写函数，主要有以下3种。

UDF（User-Defined-Function) 一进一出，如upper
UDAF(User-Defined Aggregation Function) 多进一出，如max
UDTF(User-Defined Table-Generation Functions) 一进多出，如lateral view explode()
详细情况可以查看官网最新的网址信息：https://cwiki.apache.org/confluence/display/Hive/HivePlugins

一般编程步骤如下：

继承 Hive 提供的类

org.apache.hadoop.hive.ql.udf.generic.GenericUDF 
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

实现类中的抽象方法
在 hive 的命令行窗口创建函数添加 jar

add jar linux_jar_path

创建 function

create [temporary] function [dbname.]function_name AS class_name;

在 hive 的命令行窗口删除函数

drop [temporary] function [if exists] [dbname.]function_name;

自定义UDF函数示例：（一进一出）
自定义一个 UDF 实现计算给定字符串的长度，例如：

hive(default)> select my_len("abcd");

（1）导入依赖

<dependency>
	<groupId>org.apache.hive</groupId>
	<artifactId>hive-exec</artifactId>
	<version>3.1.2</version>
</dependency>

（2）创建类

package com.mrlin.udf;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

public class my_len extends GenericUDF {
    //校验数据参数个数
    @Override
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
        if (arguments.length != 1) {
            throw new UDFArgumentException("参数个数不为1");
        }
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }
    //处理数据
    @Override
    public Object evaluate(DeferredObject[] arguments) throws HiveException {
        //1.取出输入数据
        String input = arguments[0].get().toString();
        //2.判断输入数据是否为null
        if (input == null) {
            return 0;
        }
        //3.返回输入数据的长度
        return input.length();
    }
    @Override
    public String getDisplayString(String[] children) {
        return "";
    }
}

（3）打成 jar 包上传到服务器/opt/module/data/myudf.jar
（4）将 jar 包添加到 hive 的 classpath

hive (default)> add jar /opt/module/data/myudf.jar;

（5）创建临时函数与开发好的 java class 关联

hive (default)> create temporary function my_len as "com.mrlin.udf.my_len";

（6）即可在 hql 中使用自定义的函数

hive (default)> select ename,my_len(ename) ename_len from emp;

自定义UDTF函数示例：
自定义一个 UDTF 实现将一个任意分割符的字符串切割成独立的单词，例如：

hive(default)> select myudtf("hello,world,hadoop,hive", ",");
hello 
world 
hadoop
hive

（1）创建类

package com.mrlin.udf;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import java.util.ArrayList;
import java.util.List;

public class MyUDTF extends GenericUDTF {
    //输出数据的集合
    private ArrayList<String> outPutList = new ArrayList<>();
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        //输出数据的默认列名,可以别别名覆盖
        List<String> fieldNames = new ArrayList<>();
        fieldNames.add("word");
        //输出数据的类型
        List<ObjectInspector> fieldOIs = new ArrayList<>();
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        //最终返回值
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }
    //处理输入数据：hello,atguigu,hive
    @Override
    public void process(Object[] args) throws HiveException {
        //1.取出输入数据
        String input = args[0].toString();
        //2.按照"，"分割字符串
        String[] words = input.split(",");
        //3.遍历数据写出
        for (String word : words) {
            //清空集合
            outPutList.clear();
            //将数据放入集合
            outPutList.add(word);
            //输出数据
            forward(outPutList);
        }
    }
    //收尾方法
    @Override
    public void close() throws HiveException {
    }
}

（2）打成 jar 包上传到服务器/opt/module/hive/data/myudtf.jar
（3）将 jar 包添加到 hive 的 classpath 下

hive (default)> add jar /opt/module/hive/data/myudtf.jar;

（4）创建临时函数与开发好的 java class 关联

hive (default)> create temporary function myudtf as "com.mrlin.udf.MyUDTF";

5）使用自定义的函数
hive (default)> select myudtf(“hello,world,hadoop,hive”,“,”);

九、数据压缩

hadoop支持的压缩包含以下：deflate,Gzip,bzip2,LZO,Snappy，其中支持切分的有bzip2和LZO，使用以下命令可以查看Hadoop支持哪些数据压缩格式。

#  可以查看支持哪些压缩
hadoop checknative

开启压缩

（1）压缩参数配置
要在Hadoop中启用压缩，首先需要在配置文件mapred-site.xml中配置。其中

mapreduce.map.output.compress 控制是否启用Mapper阶段的压缩
mapreduce.map.output.com press.codec 控制Mapper阶段使用哪种压缩格式
mapreduce.output.fileoutputformat.compress 控制是否启用Reducer输出阶段的压缩
mapreduce.output.fileoutput format.compress.codec 控制Reducer输出阶段的压缩使用哪种压缩格式

（2）开启Map输出阶段的压缩

# 开启 hive 中间传输数据压缩功能
hive (default)>set hive.exec.compress.intermediate=true; 
# 开启 mapreduce 中 map 输出压缩功能
hive (default)>set mapreduce.map.output.compress=true; 	
# 设置 mapreduce 中 map 输出数据的压缩方式为Snappy
hive (default)>set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;
# 执行查询语句
hive (default)> select count(ename) name from emp;

查看是否按照设置的压缩执行，可以查看历史日志

（3）开启Reduce输出阶段的压缩

# 开启 hive 最终输出数据压缩功能
hive (default)>set hive.exec.compress.output=true; 	
# 开启 mapreduce 最终输出数据压缩
hive (default)>set mapreduce.output.fileoutputformat.compress=true; 
# 设置 mapreduce 最终数据输出压缩方式
hive (default)> set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
# 设置 mapreduce 最终数据输出压缩为块压缩
hive (default)> set mapreduce.output.fileoutputformat.compress.type=BLOCK;
# 默认是行压缩，可以先查看默认的压缩模式
hive (default)> set mapreduce.output.fileoutputformat.compress.type;
# 测试一下输出结果是否是压缩文件
hive (default)> insert overwrite local directory '/opt/module/data/distribute-result' select * from emp distriute by deptno sort by empno desc;

文件存储格式

Hive支持的存储数据的格式主要有：TextFile，SequenceFile，ORC和 parquet 四种。假设有表数据如下：

a	b	c
a1	b1	c1
a2	b2	c2
a3	b3	c3

行存储的格式为：a1,b1,c1,a2,b2,c2,a3,b3,c3，每一行的数据是在一起的。
列存储的格式为：a1,a2,a3,b1,b2,b3,c1,c2,c3，每一列的数据是在一起的。
行存储的特点：
查询条件满足一整行的时候，因为都是顺序连着的所以很快，需要获取表里一整行数据的时候，比较快，TextFile，SequenceFile为行存储。
列存储的特点：
因为每个字段的数据聚集存储，在查询只要少数几个字段的时候，能减少读取的数据量；同时同类型的数据一起存储，可以设计更好的压缩算法，ORC，parquet为列存储。

TextFile文件格式：
默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合 Gzip、Bzip2 使用，但使用 Gzip 这种方式，hive 不会对数据进行切分，从而无法对数据进行并行操作。

ORC文件格式：
ORC格式（Optimized Row Columnar），每个ORC文件由 1 个或多个stripe组成，而每个Stripe由Index Data，Row Data，Stripe Footer三部分组成。每个Stripe一般为HDFS的块大小。
（1）Index Data：一个轻量级的 index，默认是每隔 1W 行做一个索引。这里做的索引只是记录某行的各字段在 Row Data 中的 offset。
（2）Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个 Stream 来存储。
（3）Stripe Footer：存的是各个 Stream 的类型，长度等信息。
每个文件有一个File Footer，这里面存的是每个Stripe的行数，每个Column 的数据类型信息等；每个文件的尾部是一个PostScript，这里面记录了整个文件的压缩类型以及FileFooter 的长度信息等。在读取文件时，会seek到文件尾部读PostScript，从里面解析到File Footer长度再读 FileFooter从里面解析到各个 Stripe 信息，再读各个Stripe，即从后往前读。

Parquet 文件格式：
Parquet格式，最适合需要从大型表中读取某些列的查询，spark默认的文件存储格式。Parquet 文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet 格式文件是自解析的。主要由以下三部分组成：
（1）行组(Row Group)：每一个行组包含一定的行数，在一个 HDFS 文件中至少存储一个行组，类似于 orc 的 stripe 的概念。
（2）列块(Column Chunk)：在一个行组中每一列保存在一个列块中，行组中的所有列连续的存储在这个行组文件中。一个列块中的值都是相同类型的，不同的列块可能使用不同的算法进行压缩。
（3）页(Page)：每一个列块划分为多个页，一个页是最小的编码的单位，在同一个列块的不同页可能使用不同的编码方式。
通常情况下，在存储 Parquet 数据的时候会按照 Block 大小设置行组的大小，由于一般情况下每一个 Mapper 任务处理数据的最小单位是一个 Block，这样可以把每一个行组由一个 Mapper 任务处理，增大任务执行并行度。

简单存储格式示例：

# 存储格式为textfile
create table log_text ( 
	name string,
	url string
)
row format delimited fields terminated by '\t' stored as textfile;
# 存储格式为ORC
create table log_orc ( 
	name string,
	url string
)
row format delimited fields terminated by '\t' 
stored as orc
tblproperties("orc.compress"="NONE"); -- 设置 orc 存储不使用压缩
# 存储格式为parquet
create table log_parquet ( 
	name string,
	url string
)
row format delimited fields terminated by '\t' 
stored as parquet;

添加数据：

hive (default)> load data local inpath '/opt/module/hive/datas/log.data' into table log_text ;
hive (default)> insert into table log_orc select * from log_text;
hive (default)> insert into table log_parquet select * from log_text;

其中ORC，Parquet这两种存储数据的格式不能用load直接添加，需要使用select insert 的方式，因为load不会进入MR
压缩率： ORC > Parquet > textFile
查询速度：ORC,Parquet,textFile 查询速度相近

简单压缩和存储示例：

# 创建一个ZLIB压缩的ORC存储方式，ORC默认的压缩方式就是 deflate 压缩算法
create table log_orc_zlib( 												-- 建表
	track_time string,url string)
row format delimited fields terminated by '\t' 							-- 按\t分行
stored as orc 															-- orc存储
tblproperties("orc.compress"="ZLIB");									-- ZLIB压缩
# 插入数据
insert into log_orc_snappy select * from log_text; 
# 查看插入后的数据
dfs -du -h /user/hive/warehouse/log_orc_zlib/;

# 创建一个Snappy压缩的ORC存储方式的表
create table log_orc_snappy( 
	track_time string,url string)
row format delimited fields terminated by '\t' 
stored as orc 
tblproperties("orc.compress"="SNAPPY");
# 创建一个 SNAPPY 压缩的 parquet 存储方式
create table log_parquet_snappy(
	track_time string,url string)
row format delimited fields terminated by '\t' 
stored as parquet 
tblproperties("parquet.compression"="SNAPPY");

在实际的项目开发当中，hive 表的数据存储格式一般选择：orc 或 parquet。压缩方式一般选择 snappy，lzo。snappy跟lzo读取和压缩速度快，lzo适合切片的场景。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：pytorch安装失败要删除重新安装吗 pytorch装不上

下一篇：Android 视频推流插件 android视频推流拉流

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯