Apache Hive

3/3/2017来源:C/C++教程人气:3576

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依赖 [[email protected] local]# rpm -e –nodeps rpm -qa | grep mysql 3°、安装mysql [[email protected] local]# yum -y install mysql-server 4°、启动mysql的服务 [[email protected] local]# service mysqld start 5°、将mysql的服务加入到开机启动项里 [[email protected] local]# chkconfig mysqld on 6°、mysql的配置 [[email protected] 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目录下 [[email protected] soft]# tar -zvxf apache-hive-0.14.0-bin.tar.gz -C /usr/local/ [[email protected] local]# mv apache-hive-0.14.0-bin/ hive-0.14.0 2°、备份配置文件(在$HIVE_HOME/conf目录下) [[email protected] conf]$ cp hive-env.sh.template hive-env.sh [[email protected] 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目录下 [[email protected] bin]$ cp $JAVA_HOME/lib/mysql-connector-java-5.1.17.jar $HIVE_HOME/lib/ 5°、启动Hive进入hive cli终端 [[email protected] bin]./hive或者 [[email protected] 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) [[email protected] soft]$ tar -zxvf apache-hive-0.14.0-src.tar.gz [[email protected] soft]$ cd apache-hive-0.14.0-src/hwi 2°、将hwi下面的web/打成一个war包 [[email protected] hwi]$ jar cvfM0 hive-hwi-0.14.0.war -C web/ . 3°、将2°中的war拷贝到$HIVE_HOME/lib目录下 [[email protected] hwi]$ cp hive-hwi-0.14.0.war /usr/local/hive-0.14.0/lib/ 4°、拷贝$JAVA_HOME/lib/tools.jar到$HIVE_HOME/lib目录下 [[email protected] 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 [[email protected] 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°、备份日志文件 [[email protected] conf]$ cp hive-exec-log4j.properties.template hive-exec-log4j.properties [[email protected] 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