九、大数据技术之Hive

1） hive简介 Hive：由Facebook开源用于解决大量结构化日志的数据统计工具。 Hive是基于Hadoop数据仓库工具可以将结构化数据文件映射成表，并提供类别SQL查询功能。

2） Hive本质：将HQL转化成MapReduce程序

（1）Hive存储处理的数据HDFS （2）Hive分析数据底层的实现是MapReduce (3)执行程序运行Yarn上

1)用户接口：Client CLI（command-line interface）、JDBC/ODBC(jdbc访问hive)、WEBUI(浏览器访问hive） 2）元数据：Metastore 元数据包括：表名、表所属的数据库（默认是default）、表的所有者、列/分区字段、表的类型(是否为外表)、表的数据目录等。 默认存储在自带中derby推荐在数据库中使用MySQL存储Metastore 3）Hadoop 使用HDFS存储和使用MapReduce进行计算。 4）驱动器：Driver （1）解析器（SQL Parser）：将SQL字符串转换为抽象语法树AST，这一步通常由第三方工具库完成，例如antlr；对AST语法分析，如表是否存在，字段是否存在，SQL语义是否错误。 （2）编译器（Physical Plan）：将AST编译生成逻辑执行计划。 （3）优化器（Query Optimizer）：优化逻辑执行计划。 （4）执行器（Execution）：将逻辑执行计划转化为可操作的物理计划。Hive来说，就是MR/Spark。

Hive接收用户的指令，通过为用户提供一系列交互接口(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop最后，将执行返回的结果输出到用户交互接口。

由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，所以很容易 Hive 理解为数据库。其实从结构上看，Hive 除了类似的查询语言，与数据库没有类似之处。本文将从多个方面进行阐述 Hive 与数据库不同。可用于数据库 Online 但是Hive 它是为数据仓库设计的。从应用的角度理解这一点有助于理解 Hive 的特性。 1.4.1 查询语言 由于SQL它广泛应用于数据仓库，因此专门针对Hive特征设计类别SQL的查询语言HQL。熟悉SQL开发人员可以很方便地使用Hive进行开发。 1.4.2 数据更新 由于Hive它是为数据仓库的应用而设计的，而数据仓库的内容是读多写少。Hive不建议重写数据。加载时确定所有数据。数据库中的数据通常需要经常修改，因此可以使用 INSERT INTO … VALUES 添加数据，使用 UPDATE … SET修改数据。 1.4.3 执行延迟 Hive 查询数据时，由于没有索引，需要扫描整个表，延迟较高。另一个原因 Hive 执行延迟高的因素是 MapReduce框架。由于MapReduce 它本身有很高的延迟，所以它被使用MapReduce 执行Hive查询时，也会有较高的延迟。数据库的执行延迟相对较低。当然，这种低是有条件的，即数据规模较小，当数据规模超过数据库的处理能力时，Hive并行计算显然反映了优势。 1.4.4 数据规模 由于Hive它可以用于集群MapReduce进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。

1）Hive官网地址 http://hive.apache.org/ 2)查看文档地址 https://cwiki.apache.org/confluence/display/Hive/GettingStarted 3）下载地址 http://archive.apache.org/dist/hive/ 4）github地址 https://github.com/apache/hive

0)为什么需要？Mysql 原因在于Hive默认使用的元数据库是derby，开启Hive之后，元数据库将被占用，数据将不会与其他客户共享。如果您想要更多的窗口操作，您将报告错误，操作相对有限。我们需要它Hive元数据地址改为MySQL，可支持多窗操作。 1)检查当前系统是否安装过Mysql

[atguigu@hadoop102 ~]$ rpm -qa|grep mariadb 

mariadb-libs-5.5.56-2.el7.x86_64 //如果通过以下命令卸载，

[atguigu @hadoop102 ~]$ sudo rpm -e --nodeps  mariadb-libs   ///用这个命令卸载mariadb 

2）将MySQL将安装包复制到/opt/software目录下 [atguigu @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安装包

[atguigu @hadoop102 software]# tar -xf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar 

4)执行安装目录rpm安装

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

注：按顺序执行： 如果Linux最小化安装，安装mysql-community-server-5.7.28-1.el7.x86_64.rpm时可能会出 现如下错误

[atguigu@hadoop102 software]$ sudo rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm 

警告：mysql-community-server-5.7.28-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY 错误：依赖检测失败： libaio.so.1()(64bit) 被 mysql-community-server-5.7.28-1.el7.x86_64 需要 libaio.so.1(LIBAIO_0.1)(64bit) 被 mysql-community-server-5.7.28-1.el7.x86_64 需要 libaio.so.1(LIBAIO_0.4)(64bit) 被 mysql-community-server-5.7.28-1.el7.x86_64 需要 通过yum缺乏安装依赖，然后重新安装mysql-ommunity-server-5.7.28-1.el7.x86_64 即可

[atguigu@hadoop102 software] yum install -y libaio

5）删除/etc/my.cnf文件中datadir指向的目录下的所有内容,如果有内容的情况下: 查看datadir的值： [mysqld] datadir=/var/lib/mysql 删除/var/lib/mysql目录下的所有内容:

[atguigu @hadoop102 mysql]# cd /var/lib/mysql
[atguigu @hadoop102 mysql]# sudo rm -rf ./*    //注意执行命令的位置

6）初始化数据库

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

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

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

8）启动MySQL服务

[atguigu @hadoop102 opt]$ sudo systemctl start mysqld

9）登录MySQL数据库

[atguigu @hadoop102 opt]$ mysql -uroot -p

Enter password: 输入临时生成的密码 登录成功. 10）必须先修改root用户的密码,否则执行其他的操作会报错

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

11）修改mysql库下的user表中的root用户允许任意ip连接

mysql> update mysql.user set host='%' where user='root';
mysql> flush privileges;

1）把apache-hive-3.1.2-bin.tar.gz上传到linux的/opt/software目录下 2）解压apache-hive-3.1.2-bin.tar.gz到/opt/module/目录下面

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

3）修改apache-hive-3.1.2-bin.tar.gz的名称为hive

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

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

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

5）添加内容

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

6）解决日志Jar包冲突

[atguigu@hadoop102 software]$ mv $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.jar $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.bak

2.4.1 拷贝驱动 将MySQL的JDBC驱动拷贝到Hive的lib目录下

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

2.4.2 配置Metastore到MySql 在$HIVE_HOME/conf目录下新建hive-site.xml文件

[atguigu@hadoop102 software]$ vim $HIVE_HOME/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>123456</value>
</property>

    <!-- Hive默认在HDFS的工作目录 -->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/user/hive/warehouse</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>
</configuration>

2.5.1 初始化元数据库 1）登陆MySQL

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

2）新建Hive元数据库

mysql> create database metastore;
mysql> quit;

3）初始化Hive元数据库

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

2.5.2 启动Hive 0）先启动hadoop集群 1）启动Hive

[atguigu@hadoop102 hive]$ bin/hive

2）使用Hive

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

3）开启另一个窗口测试开启hive

[atguigu@hadoop102 hive]$ bin/hive

2.5.3 使用元数据服务的方式访问Hive 1）在hive-site.xml文件中添加如下配置信息

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

2）启动metastore

[atguigu@hadoop202 hive]$ hive --service metastore

2020-04-24 16:58:08: Starting Hive Metastore Server 注意: 启动后窗口不能再操作，需打开一个新的shell窗口做别的操作 3）启动 hive

[atguigu@hadoop202 hive]$ bin/hive

2.5.4 使用JDBC方式访问Hive 1）在hive-site.xml文件中添加如下配置信息

2）启动hiveserver2

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

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

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

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>

2.5.5 编写启动metastore和hiveserver2脚本(了解) 1）Shell命令介绍 前台启动的方式导致需要打开多个shell窗口，可以使用如下方式后台方式启动 nohup: 放在命令开头，表示不挂起,也就是关闭终端进程也继续保持运行状态 0:标准输入 1:标准输出 2:错误输出 2>&1 : 表示将错误重定向到标准输出上 &: 放在命令结尾,表示后台运行 一般会组合使用: nohup [xxx命令操作]> file 2>&1 & ， 表示将xxx命令运行的 结果输出到file中，并保持命令启动的进程在后台运行。 如上命令不要求掌握。

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

2）编写脚本

[atguigu@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 &"
    cmd=$cmd" sleep 4; hdfs dfsadmin -safemode wait >/dev/null 2>&1"
    [ -z "$metapid" ] && eval $cmd || echo "Metastroe服务已启动"
    server2pid=$(check_process HiveServer2 10000)
    cmd="nohup hive --service 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

3）添加执行权限

[atguigu@hadoop102 hive]$ chmod u+x $HIVE_HOME/bin/hiveservices.sh

4）启动Hive后台服务

[atguigu@hadoop102 hive]$ hiveservices.sh start

2.6 Hive常用交互命令

[atguigu@hadoop102 hive]$ bin/hive -help

usage: hive -d,–define <key=value> Variable subsitution to apply to hive commands. e.g. -d A=B or --define A=B –database Specify the database to use -e SQL from command line -f SQL from files -H,–help Print help information –hiveconf <property=value> Use value for given property –hivevar <key=value> Variable subsitution to apply to hive commands. e.g. --hivevar A=B -i Initialization SQL file -S,–silent Silent mode in interactive shell -v,–verbose Verbose mode (echo executed SQL to the console) 1）“-e”不进入hive的交互窗口执行sql语句

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

2）“-f”执行脚本中sql语句 （1）在/opt/module/hive/下创建datas目录并在datas目录下创建hivef.sql文件

[atguigu@hadoop102 datas]$ touch hivef.sql

（2）文件中写入正确的sql语句

select *from student;

（3）执行文件中的sql语句

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

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

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

2.7 Hive其他命令操作 1）退出hive窗口：

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

在新版的hive中没区别了，在以前的版本是有的： exit:先隐性提交数据，再退出； quit:不提交数据，退出； 2）在hive cli命令窗口中如何查看hdfs文件系统

hive(default)>dfs -ls /;

3）查看在hive中输入的所有历史命令 （1）进入到当前用户的根目录/root或/home/atguigu （2）查看. hivehistory文件

[atguig2u@hadoop102 ~]$ cat .hivehistory

2.8 Hive常见属性配置 2.8.1 hive窗口打印默认库和表头 1）打印 当前库 和 表头 在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>

2.8.2 Hive运行日志信息配置 1）Hive的log默认存放在/tmp/atguigu/hive.log目录下（当前用户名下） 2）修改hive的log存放日志到/opt/module/hive/logs （1）修改/opt/module/hive/conf/hive-log4j2.properties.template文件名称为 hive-log4j2.properties

[atguigu@hadoop102 conf]$ pwd

/opt/module/hive/conf

[atguigu@hadoop102 conf]$ mv hive-log4j2.properties.template hive-log4j2.properties

（2）在hive-log4j.properties文件中修改log存放位置

property.hive.log.dir=/opt/module/hive/logs

2.8.2 参数配置方式 1）查看当前所有的配置信息

hive>set;

2）参数的配置三种方式 （1）配置文件方式 默认配置文件：hive-default.xml 用户自定义配置文件：hive-site.xml 注意：用户自定义配置会覆盖默认配置。另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。配置文件的设定对本机启动的所有Hive进程都有效。 （2）命令行参数方式 启动Hive时，可以在命令行添加-hiveconf param=value来设定参数。 例如：

[atguigu@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的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。 1）案例实操 （1）假设某表有如下一行，我们用JSON格式来表示其数据结构。在Hive下访问的格式为

{
    "name": "songsong",
    "friends": ["bingbing" , "lili"] ,       //列表Array, 
    "children": {                      //键值Map,
        "xiao song": 19 ,
        "xiaoxiao song": 18
    }
    "address": {                      //结构Struct,
        "street": "hui long guan" ,
        "city": "beijing" 
    }
}

（2）基于上述数据结构，我们在Hive里创建对应的表，并导入数据。 创建本地测试文件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里的元素间关系都可以用同一个字符表示，这里用“_”。 （3）Hive上创建测试表test

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';				-- 行分隔符

（4）导入文本数据到测试表

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

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

hive (default)> select friends[1],children['xiao song'],address.city from test
where name="songsong";
OK
_c0     _c1     city
lili    18      beijing
Time taken: 0.076 seconds, Fetched: 1 row(s)

Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。 1）隐式类型转换规则如下 （1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。 （2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。 （3）TINYINT、SMALLINT、INT都可以转换为FLOAT。 （4）BOOLEAN类型不可以转换为任何其它的类型。 2）可以使用CAST操作显示进行数据类型转换 例如CAST(‘1’ AS INT)将把字符串’1’ 转换成整数1；如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL。

CREATE DATABASE [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, …)]; 1）创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db。

hive (default)> create database db_hive;

2）避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）

hive (default)> create database db_hive;

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database db_hive already exists

hive (default)> create database if not exists db_hive;

3）创建一个数据库，指定数据库在HDFS上存放的位置

hive (default)> create database db_hive2 location '/db_hive2.db';

4.2.1 显示数据库 1）显示数据库

hive> show databases;

2）过滤显示查询的数据库

hive> show databases like 'db_hive*';

OK db_hive db_hive_1 4.2.2 查看数据库详情 1）显示数据库信息

hive> desc database db_hive;

OK db_hive hdfs://hadoop102:9000/user/hive/warehouse/db_hive.db atguiguUSER 2）显示数据库详细信息，extended

hive> desc database extended db_hive;

OK db_hive hdfs://hadoop102:9000/user/hive/warehouse/db_hive.db atguiguUSER 4.2.3 切换当前数据库

hive (default)> use db_hive;

4.3 修改数据库 用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。

hive (default)> alter database db_hive set dbproperties('createtime'='20170830');

在hive中查看修改结果

hive> desc database extended db_hive;

db_name comment location owner_name owner_type parameters db_hive hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db atguigu USER {createtime=20170830}

1）删除空数据库
hive>drop database db_hive2;

2）如果删除的数据库不存在，最好采用 if exists判断数据库是否存在

hive> drop database db_hive;

FAILED: SemanticException [Error 10072]: Database does not exist: db_hive

hive> drop database if exists db_hive2;

3）如果数据库不为空，可以采用cascade命令，强制删除

hive> drop database db_hive;

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)

hive> drop database db_hive cascade;

4.6.1 重命名表 1）语法

ALTER TABLE table_name RENAME TO new_table_name

2）实操案例

hive (default)> alter table dept_partition2 rename to dept_partition3;

4.6.2 增加、修改和删除表分区 详见7.1章分区表基本操作。 4.6.3 增加/修改/替换列信息 1）语法 （1）更新列

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

（2）增加和替换列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。 2）实操案例 （1）查询表结构

hive> desc dept;

（2）添加列

hive (default)> alter table dept add columns(deptdesc string);

（3）查询表结构

hive> desc dept;

（4）更新列

hive (default)> alter table dept change column deptdesc desc string;

（5）查询表结构

hive> desc dept;

（6）替换列

hive (default)> alter table dept replace columns(deptno string, dname
 string, loc string);

（7）查询表结构

hive> desc dept;

4.7 删除表

hive (default)> drop table dept;

5.1.1 向表中装载数据（Load） 1）语法

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

（1）load data:表示加载数据 （2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表 （3）inpath:表示加载数据的路径 （4）overwrite:表示覆盖表中已有数据，否则表示追加 （5）into table:表示加载到哪张表 （6）student:表示具体的表 （7）partition:表示上传到指定分区

2）实操案例 （0）创建一张表

hive (default)> create table student(id string, name string) row format delimited fields terminated by '\t';

（1）加载本地文件到hive

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

（2）加载HDFS文件到hive中 上传文件到HDFS

hive (default)> dfs -put /opt/module/hive/datas/student.txt /user/atguigu/hive;

加载HDFS上数据

hive (default)> load data inpath '/user/atguigu/hive/student.txt' into table default.student;

（3）加载数据覆盖表中已有的数据 上传文件到HDFS

hive (default)> dfs -put /opt/module/datas/student.txt /user/atguigu/hive;

加载数据覆盖表中已有的数据

hive (default)> load data inpath '/user/atguigu/hive/student.txt' overwrite into table default.student;

5.1.2 通过查询语句向表中插入数据（Insert） 1）创建一张表

hive (default)> create table student_par(id int, name string) row format delimited fields terminated by '\t';

2）基本插入数据

hive (default)> insert into table  student_par values(1,'wangwu'),(2,'zhaoliu');

3）基本模式插入（根据单张表查询结果）

hive (default)> insert overwrite table student_par 
             select id, name from student ; 

insert into：以追加数据的方式插入到表或分区，原有数据不会删除 insert overwrite：会覆盖表中已存在的数据 注意：insert不支持插入部分字段 4）多表（多分区）插入模式（根据多张表查询结果）

hive (default)> from student
              insert overwrite table student partition(month='201707')
              select id, name where month='201709'
              insert overwrite table student partition(month='201706')
              select id, name where month='201709';

5.1.3 查询语句中创建表并加载数据（As Select） 详见4.5.1章创建表。 根据查询结果创建表（查询的结果会添加到新创建的表中）

create table if not exists student3
as select id, name from student;

5.1.4 创建表时通过Location指定加载数据路径 1）上传数据到hdfs上

hive (default)> dfs -mkdir /student;
hive (default)> dfs -put /opt/module/datas/student.txt /student;

2）创建表，并指定在hdfs上的位置

hive (default)> create external table if not exists student5(
              id int, name string
              )
              row format delimited fields terminated by '\t'
              location '/student;

3）查询数据

hive (default)> select * from student5;

5.1.5 Import数据到指定Hive表中 注意：先用export导出后，再将数据导入。

hive (default)> import table student2  from
 '/user/hive/warehouse/export/student';

5.2.1 Insert导出 1）将查询的结果导出到本地

hive (default)> insert overwrite local directory '/opt/module/hive/datas/export/student'
            select * from student;

2）将查询的结果格式化导出到本地

hive(default)>insert overwrite local directory '/opt/module/hive/datas/export/student1'
           ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             select * from student;

3）将查询的结果导出到HDFS上(没有local)

hive (default)> insert overwrite directory '/user/atguigu/student2'
             ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
             select * from student;

5.2.2 Hadoop命令导出到本地

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

5.2.3 Hive Shell 命令导出 基本语法：（hive -f/-e 执行语句或者脚本 > file）

[atguigu@hadoop102 hive]$ bin/hive -e 'select * from default.student;' >
 /opt/module/hive/datas/export/student4.txt;

5.2.4 Export导出到HDFS上

(defahiveult)> export table default.student to
 '/user/hive/warehouse/export/student';

export和import主要用于两个Hadoop平台集群之间Hive表迁移。 5.2.5 Sqoop导出 后续课程专门讲。 5.2.6 清除表中数据（Truncate） 注意：Truncate只能删除管理表，不能删除外部表中数据

hive (default)> truncate table student;

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select 查询语句语法：

SELECT [ALL | DISTINCT] select_expr, 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]

6.1.1 全表和特定列查询 0）数据准备 （0）原始数据

dept:
10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700
emp：
7369	SMITH	CLERK	7902	1980-12-17	800.00		20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-4-2	2975.00		20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.00		30
7782	CLARK	MANAGER	7839	1981-6-9	2450.00		10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.00		20
7839	KING	PRESIDENT		1981-11-17	5000.00		10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00		20
7900	JAMES	CLERK	7698	1981-12-3	950.00		30
7902	FORD	ANALYST	7566	1981-12-3	3000.00		20
7934	MILLER	CLERK	7782	1982-1-23	1300.00		10

（1）创建部门表

create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

（2）创建员工表

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

（3）导入数据

load data local inpath '/opt/module/datas/dept.txt' into table
dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;

1）全表查询

hive (default)> select * from emp;
hive (default)> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp ;

2）选择特定列查询

hive (default)> select empno, ename from emp;

注意： （1）SQL 语言大小写不敏感。 （2）SQL 可以写在一行或者多行 （3）关键字不能被缩写也不能分行 （4）各子句一般要分行写。 （5）使用缩进提高语句的可读性。 6.1.2 列别名 1）重命名一个列 2）便于计算 3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’ 4）案例实操 查询名称和部门

hive (default)> select ename AS name, deptno dn from emp;

6.1.3 算术运算符 运算符 描述

A+B	A和B 相加
A-B	A减去B
A*B	A和B 相乘
A/B	A除以B
A%B	A对B取余
A&B	A和B按位取与
A|B	A和B按位取或
A^B	A和B按位取异或
~A	A按位取反

案例实操：查询出所有员工的薪水后加1显示。

hive (default)> select sal +1 from emp;

6.1.4 常用函数

1）求总行数（count）
hive (default)> select count(*) cnt from emp;
2）求工资的最大值（max）
hive (default)> select max(sal) max_sal from emp;
3）求工资的最小值（min）
hive (default)> select min(sal) min_sal from emp;
4）求工资的总和（sum）
hive (default)> select sum(sal) sum_sal from emp; 
5）求工资的平均值（avg）
hive (default)> select avg(sal) avg_sal from emp;
6.1.5 Limit语句
典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
hive (default)> select * from emp limit 5;
hive (default)> select * from emp limit 2,3;
6.1.6 Where语句
1）使用WHERE子句，将不满足条件的行过滤掉
2）WHERE子句紧随FROM子句
3）案例实操
查询出薪水大于1000的所有员工
hive (default)> select * from emp where sal >1000;
注意：where子句中不能使用字段别名。
6.1.7 比较运算符（Between/In/ Is Null）

1）下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。 2）案例实操

（1）查询出薪水等于5000的所有员工
hive (default)> select * from emp where sal =5000;
（2）查询工资在500到1000的员工信息
hive (default)> select * from emp where sal between 500 and 1000;
（3）查询comm为空的所有员工信息
hive (default)> select * from emp where comm is null;
（4）查询工资是1500或5000的员工信息
hive (default)> select * from emp where sal IN (1500, 5000);

6.1.8 Like和RLike 1）使用LIKE运算选择类似的值 2）选择条件可以包含字符或数字: % 代表零个或多个字符(任意个字符)。 _ 代表一个字符。 3）RLIKE子句 RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。 4）案例实操

（1）查找名字以A开头的员工信息
hive (default)> select * from emp where ename LIKE 'A%';
（2）查找名字中第二个字母为A的员工信息
hive (default)> select * from emp where ename LIKE '_A%';
（3）查找名字中带有A的员工信息
hive (default)> select * from emp where ename  RLIKE '[A]';

6.1.9 逻辑运算符（And/Or/Not） 操作符 含义 AND 逻辑并 OR 逻辑或 NOT 逻辑否 1）案例实操

（1）查询薪水大于1000，部门是30
hive (default)> select * from emp where sal>1000 and deptno=30;
（2）查询薪水大于1000，或者部门是30
hive (default)> select * from emp where sal>1000 or deptno=30;
（3）查询除了20部门和30部门以外的员工信息
hive (default)> select * from emp where deptno not IN(30, 20);

6.2.1 Group By语句 GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。 1）案例实操：

（1）计算emp表每个部门的平均工资
hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
（2）计算emp每个部门中每个岗位的最高薪水
hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by
 t.deptno, t.job;

6.2.2 Having语句 1）having与where不同点 （1）where后面不能写分组函数，而having后面可以使用分组函数。 （2）having只用于group by分组统计语句。 2）案例实操

（1）求每个部门的平均薪水大于2000的部门
求每个部门的平均工资
hive (default)> select deptno, avg(sal) from emp group by deptno;
求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having
 avg_sal > 2000;

6.3.1 等值Join Hive支持通常的SQL JOIN语句。 1）案例实操 （1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；

hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

6.3.2 表的别名 1）好处 （1）使用别名可以简化查询。 （2）使用表名前缀可以提高执行效率。 2）案例实操 合并员工表和部门表

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
 = d.deptno;

6.3.3 内连接 内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
 = d.deptno;

6.3.4 左外连接 左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

6.3.5 右外连接 右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

6.3.6 满外连接 满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno
 = d.deptno;

6.3.7 多表连接 注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。 数据准备 1700 Beijing 1800 London 1900 Tokyo

1）创建位置表
create table if not exists location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';
2）导入数据
hive (default)> load data local inpath '/opt/module/datas/location.txt' into table location;
3）多表连接查询
hive (default)>SELECT e.ename, d.dname, l.loc_name
FROM   emp e 
JOIN   dept d
ON     d.deptno = e.deptno 
JOIN   location l
ON     d.loc = l.loc;

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。 注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。 优化：当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。 6.3.8 笛卡尔积 1）笛卡尔积会在下面条件下产生 （1）省略连接条件 （2）连接条件无效 （3）所有表中的所有行互相连接 2）案例实操

hive (default)> select empno, dname from emp, dept;

6.4.1 全局排序（Order By） Order By：全局排序，只有一个Reducer 1）使用 ORDER BY 子句排序 ASC（ascend）: 升序（默认） DESC（descend）: 降序 2）ORDER BY 子句在SELECT语句的结尾 3）案例实操

（1）查询员工信息按工资升序排列
hive (default)> select * from emp order by sal;
（2）查询员工信息按工资降序排列
hive (default)> select * from emp order by sal desc;

6.4.2 按照别名排序

按照员工薪水的2倍排序
hive (default)> select ename, sal*2 twosal from emp order by twosal;
6.4.3 多个列排序
按照部门和工资升序排序
hive (default)> select ename, deptno, sal from emp order by deptno, sal ;
6.4.4 每个Reduce内部排序（Sort By）

Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。 Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。

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

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

hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory '/opt/module/hive/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

注意： distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。 Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。 6.4.6 Cluster By 当distribute by和sort by字段相同时，可以使用cluster by方式。 cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。 （1）以下两种写法等价

hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;

注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。 7.1.1 分区表基本操作 1）引入分区表（需要根据日期对日志进行管理, 通过部门信息模拟）

dept_20200401.log
dept_20200402.log
dept_20200403.log

…… 2）创建分区表语法

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

注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。 3）加载数据到分区表中 （1）数据准备

dept_20200401.log
10	ACCOUNTING	1700
20	RESEARCH	1800
dept_20200402.log
30	SALES	1900
40	OPERATIONS	1700
dept_20200403.log
50	TEST	2000
60	DEV	1900

（2）加载数据

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200402.log' into table dept_partition partition(day='20200402');
hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200403.log' into table dept_partition partition(day='20200403');

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

4）查询分区表中数据 单分区查询

hive (default)> select * from dept_partition where day='20200401';

多分区联合查询

hive (default)> select * from dept_partition where day='20200401'
              union
              select * from dept_partition where day='20200402'
              union
              select * from dept_partition where day='20200403';
hive (default)> select * from dept_partition where day='20200401' or
                day='20200402' or day='20200403' ;	

5）增加分区
创建单个分区
hive (default)> alter table dept_partition add partition(day='20200404') ;
同时创建多个分区
hive (default)> alter table dept_partition add partition(day='20200405') partition(day='20200406');
6）删除分区
删除单个分区
hive (default)> alter table dept_partition drop partition (day='20200406');
同时删除多个分区
hive (default)> alter table dept_partition drop partition (day='20200404'), partition(day='20200405');
7）查看分区表有多少分区
hive> show partitions dept_partition;
8）查看分区表结构
hive> desc formatted dept_partition;

7.1.2 二级分区 思考: 如何一天的日志数据量也很大，如何再将数据拆分? 1）创建二级分区表

hive (default)> create table dept_partition2(
               deptno int, dname string, loc string
               )
               partitioned by (day string, hour string)
               row format delimited fields terminated by '\t';

2）正常的加载数据 （1）加载数据到二级分区表中

hive (default)> load data local inpath '/opt/module`/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401', hour='12');

（2）查询分区数据

hive (default)> select * from dept_partition2 where day='20200401' and hour='12';

3）把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式 （1）方式一：上传数据后修复 上传数据

hive (default)> dfs -mkdir -p
 /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
hive (default)> dfs -put /opt/module/datas/dept_20200401.log  /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;

查询数据（查询不到刚上传的数据）

hive (default)> select * from dept_partition2 where day='20200401' and hour='13';

执行修复命令

hive> msck repair table dept_partition2;

再次查询数据

hive (default)> select * from dept_partition2 where day='20200401' and hour='13';

（2）方式二：上传数据后添加分区 上传数据

hive (default)> dfs -mkdir -p
 /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=14;
hive (default)> dfs -put /opt/module/hive/datas/dept_20200401.log  /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=14;

执行添加分区
	hive (default)> alter table dept_partition2 add partition(day='201709',hour='14');
查询数据
hive (default)> select * from dept_partition2 where day='20200401' and hour='14';

（3）方式三：创建文件夹后load数据到分区 创建目录

hive (default)> dfs -mkdir -p
 /user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=15;

上传数据

hive (default)> load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
 dept_partition2 partition(day='20200401',hour='15');

查询数据

hive (default)> select * from dept_partition2 where day='20200401' and hour='15';

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

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）整