Apache Hive

spark，hadoop交流群，群QQ号：521066396，欢迎加入共同学习，一起进步~

一、Apache Hive简介

官方网址：https://hive.apache.org/
The Apache Hive ™ data warehouse software facilitates reading, writing, and managing large datasets residing in distributed storage using SQL. Structure can be projected onto data already in storage. A command line tool and JDBC driver are provided to connect users to Hive.
（Apache Hive™数据仓库软件使用SQL语句便于读取，写入和管理驻留在分布式存储中的大型数据集。操作结构化数据。提供给用户命令行工具和JDBC驱动程序以连接到Hive）

1、概念

（1）Hive 是建立在 Hadoop上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据提取转化加载（ETL），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 HQL ，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。
（2）Hive是SQL解析引擎，它将SQL语句转译成MR Job然后在Hadoop执行。
（3）Hive的表其实就是HDFS的目录，按表名把文件夹分开。如果是分区表，则分区值是子文件夹，可以直接在MR Job里使用这些数据。
（4）Hive相当于hadoop的客户端工具，部署时不一定放在集群管理节点中，可以放在某个节点上。
（5）Hive中存储结构和HDFS里面的存储结构的对应关系
Hive的表———-HDFS的目录
Hive的数据——–HDFS的目录下面的（数据）文件
Hive中行列——–HDFS的目录下面的数据文件的行列
（6）Hive相当于hadoop的客户端工具，部署时不一定放在集群管理节点中，可以放在某个节点上。

2、数据存储

（1）Hive的数据存储基于Hadoop HDFS
（2）Hive没有专门的数据存储格式
（3）存储结构主要包括：数据库、文件、表、视图、索引
（4）Hive默认可以直接加载文本文件（TextFile），还支持SequenceFile、RCFile
（5）创建表时，指定Hive数据的列分隔符与行分隔符，Hive即可解析数据

3、Hive的体系结构

（1）用户接口主要有三个：CLI，JDBC/ODBC和 WebUI
（2）CLI，即Shell命令行
（3）JDBC/ODBC 是 Hive 的Java，与使用传统数据库JDBC的方式类似
（4）WebGUI是通过浏览器访问 Hive
（5）Hive 将元数据存储在数据库中(metastore)，目前只支持 mysql、derby。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等
（6）解释器、编译器、优化器完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划（plan）的生成。生成的查询计划存储在 HDFS 中，并在随后由 MapReduce 调用执行
（7）Hive 的数据存储在 HDFS 中，大部分的查询由 MapReduce 完成（包含 * 的查询，比如 select * from table 不会生成 MapRedcue 任务）

4、Hive的元数据

（1）metastore是hive元数据的集中存放地。
（2）metastore默认使用内嵌的derby数据库作为存储引擎
（3）Derby引擎的缺点：一次只能打开一个会话
（4）使用MySQL作为外置存储引擎，多用户同时访问

二、Apache Hive安装

hive的下载地址http://hive.apache.org/downloads.html
JAVA_HOME=/usr/local/jdk1.7.0_55
HADOOP_HOME=/usr/local/hadoop-2.6.0
HIVE_HOME=/usr/local/hive-0.14.0
Ubuntu操作系统安装步骤http://www.tuicool.com/articles/bmUjAjj
以下是Centos操作系统

1、Mysql的安装（推荐在线安装）

http://www.cnblogs.com/liuchangchun/p/4099003.html
1°、查看mysql依赖
rpm -qa | grep mysql
2°、删除mysql依赖
[root@hive local]# rpm -e –nodeps rpm -qa | grep mysql
3°、安装mysql
[root@hive local]# yum -y install mysql-server
4°、启动mysql的服务
[root@hive local]# service mysqld start
5°、将mysql的服务加入到开机启动项里
[root@hive local]# chkconfig mysqld on
6°、mysql的配置
[root@hive local]# /usr/bin/mysql_secure_installation
7°、授予远程指定用户的登陆权限
mysql -h hive.teach.crxy.cn -uroot -proot
问题：ERROR 1130 (HY000): Host ‘hive.teach.crxy.cn’ is not allowed to connect to this MySQL server
解决办法：
mysql> grant all privileges on . to ‘root’@’%’ identified by ‘root’;
mysql> flush privileges;

2、安装Hive

    前提是：hadoop必须已经启动了*****
    1°、解压hive安装包到/usr/local目录下
        [root@hive soft]# tar -zvxf apache-hive-0.14.0-bin.tar.gz -C /usr/local/
        [root@hive local]# mv apache-hive-0.14.0-bin/ hive-0.14.0
    2°、备份配置文件（在$HIVE_HOME/conf目录下）
        [root@hive conf]$ cp hive-env.sh.template hive-env.sh
        [root@hive conf]$ cp hive-default.xml.template hive-site.xml
    3°、配置hive的配置文件
        1）、修改hive-env.sh
            加入三行内容（大家根据自己的情况来添加）
                JAVA_HOME=/usr/local/jdk1.7.0_55
                HADOOP_HOME=/usr/local/hadoop-2.6.0
                HIVE_HOME=/usr/local/hive-0.14.0
        2）、修改hive-site.xml

                <property>
                    <name>javax.jdo.option.ConnectionURL</name>             
                     <value>jdbc:mysql://scbnode1:3306/hivedb?createDatabaseIfNotExist=true</value>
                </property>
                <property>
                    <name>javax.jdo.option.ConnectionDriverName</name>
                    <value>com.mysql.jdbc.Driver</value>
                </property>
                <property>
                    <name>javax.jdo.option.ConnectionUserName</name>
                    <value>yourpassword</value>
                </property>
                <property>
                    <name>javax.jdo.option.ConnectionPassword</name>
                    <value>yourpassword</value>
                </property>
                <property>
                    <name>hive.querylog.location</name>
                    <value>/usr/local/hive-0.14.0/tmp</value>
                </property>
                <property>
                    <name>hive.exec.local.scratchdir</name>
                    <value>/usr/local/hive-0.14.0/tmp</value>
                </property>
                <property>
                    <name>hive.downloaded.resources.dir</name>
                    <value>/usr/local/hive-0.14.0/tmp</value>
                </property>

    4°、拷贝mysql驱动到$HIVE_HOME/lib目录下
        [crxy@master bin]$ cp $JAVA_HOME/lib/mysql-connector-java-5.1.17.jar $HIVE_HOME/lib/    
    5°、启动Hive进入hive cli终端
        [root@hive bin]./hive或者
        [root@hive bin]./hive --service cli

三、Apache Hive常用命令

（1）登录数据库，-u后面跟用户名，回车之后输入密码即可
mysql -u root -p
（2）查看数据库字符集
show variables like ‘character%’;
（3）创建数据库
create database <数据库名>
（4）显示所有数据库
show databases;
（5）删除数据库
drop database <数据库名>
（6）链接数据库
use <数据库名>
（7）查看当前使用的数据库
select database()
（8）查看当前数据库表信息
show tables
（9）退出
exit

四、Apache Hive操作技巧

1、Hive CLI终端如何和Linux/hadoop fs进行交互

    Linux：
    hive>! linux命令;
    eg.
        hive>!pwd;
    hadoop：
    hive> dfs option args; 
    eg.
        hive> dfs -ls /;

2、Hive的使用方式

命令行方式cli：控制台模式
    上课一直在用
脚本文件方式：实际生产中用的最多的方式

3、两种操作的方式

    　　1°、linux终端
    　　sh $HIVE_HOME/bin/hive -f hive.hql(的路径)
    　　2°、hive终端
    　　hive> source hive.hql(的路径)

4、JDBC方式：hiveserver

5、web GUI接口 hwi方式

    安装Web GUI访问方式的步骤
        1、解压hive源代码到某个目录，并进入到解压后的一个子文件夹hwi(Hive web interface)
            [root@hive soft]$ tar -zxvf apache-hive-0.14.0-src.tar.gz
            [root@hive soft]$ cd apache-hive-0.14.0-src/hwi
        2°、将hwi下面的web/打成一个war包    
            [root@hive hwi]$ jar cvfM0 hive-hwi-0.14.0.war -C web/ .
        3°、将2°中的war拷贝到$HIVE_HOME/lib目录下
            [root@hive hwi]$ cp hive-hwi-0.14.0.war /usr/local/hive-0.14.0/lib/
        4°、拷贝$JAVA_HOME/lib/tools.jar到$HIVE_HOME/lib目录下
            [root@hive hwi]$ cp /usr/local/jdk1.7.0_55/lib/tools.jar /usr/local/hive-0.14.0/lib/
        5°、修改hive的配置文件hive-site.xml

                <property>
                    <name>hive.hwi.listen.host</name>
                    <value>hive.teach.crxy.cn</value>
                    <description>This is the host address the Hive Web Interface will listen on</description>
                </property>
                <property>
                    <name>hive.hwi.listen.port</name>
                    <value>9999</value>
                    <description>This is the port the Hive Web Interface will listen on</description>
                </property>
                <property>
                    <name>hive.hwi.war.file</name>
                    <value>lib/hive-hwi-0.14.0.war</value>
                    <description>This sets the path to the HWI war file, relative to ${HIVE_HOME}. </description>
                </property>

  6°、启动hwi
  [crxy@master bin]$ ./hive --service hwi &
            查询9999端口进程
                netstat -tunlp | grep 9999
 7°、浏览器访问hwi
            http://<IP>:9999/hwi/
            http://hive.teach.crxy.cn:9999/hwi/

6、单词统计:

use default;
create table t(line string);
load data local inpath ‘hello’ into table t;(如果数据在hdfs上，去掉local)
set hive.exec.mode.local.auto;
set hive.exec.mode.local.auto-true;

五、Hive的日志信息相关

1、如何去掉hive启动时候的日志信息

启动时发现
    SLF4J: Found binding in [jar:file:/usr/local/hive-0.14.0/lib/hive-jdbc-0.14.0-standalone.jar
解决方案：
    hive> !mv /usr/local/hive-0.14.0/lib/hive-jdbc-0.14.0-standalone.jar /usr/local/hive-0.14.0/lib/hive-jdbc-0.14.0-standalone.jar.bak;

2、hive的日志

1°、备份日志文件
    [root@hive conf]$ cp hive-exec-log4j.properties.template hive-exec-log4j.properties
    [root@hive conf]$ cp hive-log4j.properties.template hive-log4j.properties
2°、查看日志配置文件
    hive.log.threshold=ALL
    hive.root.logger=INFO,DRFA
    hive.log.dir=${java.io.tmpdir}/${user.name}
    hive.log.file=hive.log
    通过SystemInfo.java可以知道
    ${java.io.tmpdir}=/tmp
    ${user.name}=root

六、Hive的数据类型

int、boolean、date、array、map、struct等等。
Hive的数据库、表，及其数据库、表与hdfs、metastore中的对应信息

1、Hive数据库，DDL

1°、查看数据库列表
    hive> show databases;
查看数据库详细信息
desc database mydb;
查看当前数据库
set hive.cli.print.current.db=true;
set 
2°、使用db
    hive> use dbName;
    eg.
    hive> use default;
3°、创建db
    hive> create database dbName;
    eg.
    hive> create database mydb1;
4°、删除
    hive> drop database dbName;
    eg.
    hive> drop database mydb1;
5°、数据库在hdfs上面的位置
    默认数据库在hdfs上面的位置
          <property>
            <name>hive.metastore.warehouse.dir</name>
            <value>/user/hive/warehouse</value>
            <description>location of default database for the warehouse</description>
          </property>
        也可以通过hive> set hive.metastore.warehouse.dir;来获取
    在metastore中查看
        有在表DBS中可以看到default的hdfs_uri：hdfs://hive.teach.crxy.cn:9000/user/hive/warehouse
    普通的数据库放在了/user/hive/warehouse下面，在metastore中查看==》
    我们创建的mydb1的hdfs_uri：hdfs://hive.teach.crxy.cn:9000/user/hive/warehouse/mydb1.db
    创建制定存储位置的数据库
    hive> create database mydb2 location hdfs_uri;
===》
    我们删除，修改hive数据库的定义的时候，对应的hdfs相应的目录和metastore中的相应的列就发生了变化，是同步的。

2、hive中的表

设置查看表的头信息
    hive> set hive.cli.print.header;
    hive.cli.print.header=false
    hive> set hive.cli.print.header=true;
1°、表的DDL
    查看表
        hive> show tables;
    创建表
        hive> create table t1(id int);
    查看表结构
        hive> desc [extended] t1;
            extended是可选的，是扩展的表的信息
    查看表的创建语句
        hive> show create table t1;
            LOCATION
                'hdfs://hive.teach.crxy.cn:9000/user/hive/warehouse/t1'
            说了创建的表在hdfs中的位置
    在metastore中查看
        在表TBLS中有创建的表的信息
    删除表
        hive> drop table t1;
    重命名表的名称
        hive> alter table t1 rename to t1_ddl;
    修改表中的某一列
        hive> alter table t1_ddl change id t_id int;
    增加列
        mysql:alter table add column colname coltype;
        hive> alter table add columns (colname coltype...);可以增加多列
        hive> alter table t1_ddl add columns(name string comment 'name', age int); 
    替换整个表列
        hive> alter table t1_ddl replace columns(name string comment 'name', age int);
    移动某一列的位置
        将某一列放在最前面
            hive> alter table t1_ddl add columns(id int);（增加原有的数据）
            hive> alter table t1_ddl change id id int first;
        将某一列移动到另外一列的后面或前面
            hive> alter table t1_ddl change age age int after id;（将age放在id的后面或name的前面）
2°、hive加载数据
    一）、使用hive命令load data
        hive> alter table t1_ddl replace columns(id int);
        hive> alter table t1_ddl rename to t1;  
        hive> load data local inpath 'data/t1' into table t1;
        查看表中的数据
            hive> select * from t1;
    二）、使用hadoop fs命令
        把数据直接放到hdfs中hive对应的目录下面，hive表会不会感知到呢？
        hive> dfs -put data/t1 /user/hive/warehouse/t1/t1_1; 
        这样hive也是可以感知到加载上来的数据的。
3°、数据加载的模式及其hive表列的分隔符
    create table t2(
        id int comment "ID",
        name string comment "name",
        birthday date comment 'birthday',
        online boolean comment "is online"
    );
    load data local inpath 'data/t2' into table t2;
    Hive有默认的行列分隔符
        行分隔符和linux下面的行分隔符一直都是'\n'
        列分隔符是八进制的\001,是不可见的ASCII，怎么输入呢ctrl+v ctrl+a
    创建表的时候如何制定行列的分隔符呢？
        create table t2_1(
            id int comment "ID",
            name string comment "name",
            birthday date comment 'birthday',
            online boolean comment "is online"
        ) comment "test table's seperator"
        row format delimited
        fields terminated by '\t'
        lines terminated by '\n';
        load data local inpath 'data/t2_1' into table t2;
    ====》有问题，读取错误数据为NULL
    ====》两种数据的加载模式
    读模式
        数据库加载数据的时候不进行数据的合法性校验，在查询数据的时候将不合法的数据显示为NULL 
        好处：加载速度快，适合大数据的加载。
    写模式
        数据库加载数据的时候要进行数据的合法性校验，在数据库里面的数据都是合法的
        好处：适合进行查询，不会担心有不合法的数据存在。
    我们的Hive采用的是读模式，加载数据的时候不进行数据的合法性校验，在查询数据的时候将不合法的数据显示为NULL。

3、Hive表中加载数据的两种方式

load data local inpath linux_fs_path into table tblName;
hadoop fs -put linux_path hdfs_uri;

4、Hive中复合数据类型和各种默认的分隔符

复合数据类型：array、map、struct
array元素的引用：arrayName[index]
map元素的引用：mapName[‘key’]
struct元素的引用：structName.item
默认分隔符：
行的默认分隔符：’\n’
列的默认分隔符：’\001’ –> ctrl+v ctrl+a
集合元素的默认分隔符：’\002’ –> ctrl+v ctrl+b
map k-v的默认分隔符：’\003’ –> ctrl+v ctrl+c
在创建表写分隔符的时候，是有顺序要求的，如下：
create table tblName(
columns columns_type comment ‘comment’
) row format delimited—>指明要使用自定义添加分隔符
fields terminated by ‘列分隔符’ –>读取数据的时候是一行一行的，读取好了一行之后，对数据解析，所以要用列分隔符拆分每一列
collection items terminated by ‘集合元素分割符’ –>如果列是集合的话，就要跟着拆分集合元素
map keys terminated by ‘map-key分割符’ –>map 也是集合中的元素，碰到是map的类型了再解析map
lines terminated by ‘行分隔符’; –>解析完一行需要知道下一行，所以这个时候要知道行分隔符了

5、Hive的函数

顾名思义，Hive中的函数，就是为了完成某一操作的某一个功能的抽象，和MySQL中的Hive是一样一样的。
1、浏览所有的函数
    show functions;
2、查看具体的注释函数
    desc function functionName;
    查看具体的注释信息
    desc function extended functionName;
3、常用的函数
    case     -----条件显示，有点类似java中的if else/switch，需要和when结合起来使用
    split    -----类似java中String类中split()方法
    collect_set -----类似java中的list，
    collect_list-----类似java中的set，
    concat_ws----字符串的连接，和mysql中concat类似
    explode    ----把一个list转化多行显示
4、常用函数举例
    1°、条件显示---case when
        select id, case id
            when 1 then 'teacher'
            when 2 then 'seller'
            when 3 then 'student'
            else 'others'
        end
        from tblName;
    2°、单词统计---split explode 
        第一步：文本切割split
            select split(word, ' ') from tblName;
        第二步：集合元素转多行explode
            select explode(split(word, ' ')) from tblName;
        第三步：使用聚合函数统计
            select word, count(1) as count from (select explode(split(word, ' ')) word from tblName) w group by count;
    3°、行转列
        第一步：做表关联，分析结果
            select u.id, u.name, a.address from t11_user u join t11_address a on u.id = a.uid;
        第二步：对出现的多个结果按照id来拼接字符串
            select u.id, max(u.name), concat_ws("," collect_set(a.address)) as addr 
                from t11_user u join t11_address a
                on u.id = a.id group by u.id;
    4°、列转行
        准备数据
        create table t_user_addr as
            select u.id, max(u.name), concat_ws("," collect_set(a.address)) as addr 
                    from t11_user u join t11_address a
                    on u.id = a.id group by u.id;            
        就使用explode行数就可以了
        select explode(split(addr, ",") from t_user_addr;
        查看多个字段
        select id, name, address from t12_user_addr lateral view explode(split(addr, ",")) a as address;
5、自定义函数
    hive内嵌的函数，虽然说功能非常的强大，但是我们的业务可能是千变万化的，所以需要针对业务自定义函数！
    步骤：
        1°、自定义UDF extends org.apache.hadoop.hive.ql.exec.UDF
        2°、需要实现evaluate函数，evaluate函数支持重载
        3°、把程序打包放到目标机器上去
        4°、进入hive客户端，添加jar包：hive>add jar jar路径
        5°、创建临时函数：hive> create temporary function 自定义名称 AS '自定义UDF的全类名'
        6°、执行HQL语句；
        7°、销毁临时函数：hive> drop temporary function 自定义名称
        package cn.crxy.teach.hive.udf;
        import org.apache.hadoop.hive.ql.exec.Description;
        import org.apache.hadoop.hive.ql.exec.UDF;
        import org.apache.hadoop.io.Text;
        /*下面的注解是对函数的注释 可以通过desc function fName获取*/
        @Description(name = "MyUpper",
                value = " _FUNC_(str) - Returns str with all characters changed to uppercase",
                extended = "Example:\n"
                    + " > SELECT _FUNC_(name) FROM src;")
        public class MyUpper extends UDF {
            public Text evaluate(Text text) {
                if(text != null) {
                    return new Text(text.toString().toUpperCase());
                } else {
                    return null;
                }
            }
        }
    注意，如果用到的是日期Date类型的话，必须是java.sql.Date

6、Hive的JDBC连接方式

首先需要启动咱们的jdbc服务Hive Thrift Server，
    sh $HIVE_HOME/bin/hive --service hiveserver2
    可以放到后台执行
    nohup sh $HIVE_HOME/bin/hive --service hiveserver2 --hiveconf hive.server2.thrift.port=10002 >/dev/null 2>&1 &
其次在eclipse中编写代码，java中的jdbc
    package cn.crxy.teach.hive.jdbc;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    public class HiveJdbc {
        public static void main(String[] args) throws Exception {
            //Class.forName("org.apache.hadoop.hive.jdbc.HiveDriver");//老版本hiveserver的驱动名称
            //Connection con = DriverManager.getConnection("jdbc:hive://hive.teach.crxy.cn:10002");//老版本
            Class.forName("org.apache.hive.jdbc.HiveDriver");
            Connection con = DriverManager.getConnection("jdbc:hive2://hive.teach.crxy.cn:10002/default", "root", "");
            PreparedStatement ps = con.prepareStatement("select wc.word, count(1) as count " +
                "from (select explode(split(word, ' ')) as word from t10_wc) wc " +
                "group by wc.word order by count desc");
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                String word = rs.getString(1);
                int count = rs.getInt(2);
                System.out.println(word + ", " + count);
            }
            rs.close();
            ps.close();
            con.close();
        }
    }
在windows下可能会报错：
    java.io.IOException: Could not locate executable null\bin\winutils.exe in the Hadoop binaries.
    但是可以忽略这个错误，咱们在windows上并没有安装hadoop，所以有问题。
    如何解决：
        在windows下面配置HADOOP_HOME环境变量，同时将我给大家发下去的hadoop2.6_util(x64).zip解压，
        覆盖掉原来的HADOOP_HOME/bin目录，这样在eclipse中执行的时候就没有问题了。
在linux下面执行是不会有问题的。
    将程序打成一个jar包，扔到hive服务器上面执行
    java -cp hive-0.0.1-SNAPSHOT-jar-with-dependencies.jar cn.crxy.teach.hive.jdbc.HiveJdbc