Hive的安装

一 安装环境

  • Hadoop 2.7.2
  • JDK 1.7 U79
  • Hive 2.1.0
  • Mysql(apt-get 安装)
  • 192.168.1.166为Mysql server meta server安装位置
  • 192.168.1.159为Hive数据仓库安装位置

二 Hive的安装-MySQL作为元数据库

  • 安装JDK-略过
  • 安装Hadoop-略过
  • 安装Mysql-略过

三 在192.168.1.166上建立Hive meta数据库,用户,赋予权限

mysql虚拟机的默认密码,在我做试验的时候是123456

  2. $mysql -u root -p
  3. mysql>grant all privileges on *.* to hive@"%" identified by "hive" with grant option;
  4. mysql>flush privileges;
  5. Mysql在Ubuntu中默认安装后,只能在本机访问,如果要开启远程访问,需要做以下两个步骤:
  6. $nano /etc/mysql/my.cnf
  8. 找到bind-address=127.0.0.1 ,把这一行注释掉
  9. $service mysql restart

四 在192.168.1.159上安装Hive

1 安装Hive 

  1. hadoop@hadoopmaster:~$ sudo tar xvfz apache-hive-2.1.0-bin.tar.gz 
  2. hadoop@hadoopmaster:~$ sudo cp -R apache-hive-2.1.0-bin /usr/local/hive 
  3. hadoop@hadoopmaster:~$ sudo chmod -R 775 /usr/local/hive/
  4. hadoop@hadoopmaster:~$ sudo chown -R hadoop:hadoop /usr/local/hive

2 修改/etc/profile加入HIVE_HOME的变量

  1. export HIVE_HOME=/usr/local/hive
  2. export PATH=$PATH:$HIVE_HOME/bin
  3. export CLASSPATH=.:${JAVA_HOME}/lib:${JRE_HOME}/lib:/usr/local/hive/lib
  4. $source /etc/profile

3 修改hive/conf下的几个template模板并重命名为其他

  1. cp hive-env.sh.template hive-env.sh
  2. cp hive-default.xml.template hive-site.xml

配置hive-env.sh文件，指定HADOOP_HOME

  1. HADOOP_HOME=/usr/local/hadoop

4 修改hive-site.xml文件，指定MySQL数据库驱动、数据库名、用户名及密码，修改的内容如下所示

  1. <property>
  2.   <name>javax.jdo.option.ConnectionURL</name>
  3.   <value>jdbc:mysql://192.168.1.178:3306/hive?createDatabaseIfNotExist=true</value>
  4.   <description>JDBC connect string for a JDBC metastore</description>
  5. </property>
  6. <property>
  7.   <name>javax.jdo.option.ConnectionDriverName</name>
  8.   <value>com.mysql.jdbc.Driver</value>
  9.   <description>Driver class name for a JDBC metastore</description>
  10. </property>
  11. <property>
  12.   <name>javax.jdo.option.ConnectionUserName</name>
  13.   <value>hive</value>
  14.   <description>username to use against metastore database</description>
  15. </property>
  16. <property>
  17.   <name>javax.jdo.option.ConnectionPassword</name>
  18.   <value>hive</value>
  19.   <description>password to use against metastore database</description>
  20. </property>
  22. 其中：
  23. javax.jdo.option.ConnectionURL参数指定的是Hive连接数据库的连接字符串；
  24. javax.jdo.option.ConnectionDriverName参数指定的是驱动的类入口名称；
  25. javax.jdo.option.ConnectionUserName参数指定了数据库的用户名；
  26. javax.jdo.option.ConnectionPassword参数指定了数据库的密码。

5 缓存目录的问题,如果不配置也会出错的

  1.  <property> 
  2.  <name>hive.exec.local.scratchdir</name>
  3.  <value>/home/hadoop/iotmp</value>
  4.  <description>Local scratch space for Hive jobs</description>
  5.  </property>
  6.  <property>
  7.  <name>hive.downloaded.resources.dir</name>
  8.  <value>/home/hadoop/iotmp</value>
  9.  <description>Temporary local directory for added resources in the remote file system.</description>
  10.  </property>

并且需要对目录进行权限设定 

  1. mkdir -p /home/hadoop/iotmp 
  2. chmod -R 775 /home/hadoop/iotmp

五修改hive/bin下的hive-config.sh文件，设置JAVA_HOME,HADOOP_HOME

  1. export JAVA_HOME=/usr/lib/jvm
  2. export HADOOP_HOME=/usr/local/hadoop
  3. export HIVE_HOME=/usr/local/hive

六 下载mysql-connector-java-5.1.27-bin.jar文件，并放到$HIVE_HOME/lib目录下

可以从Mysql的官方网站下载,但是记得一定要解压呀,下载的是一个tar.gz文件

七 在HDFS中创建/tmp和/user/hive/warehouse并设置权限

  1. hadoop fs -mkdir /tmp
  2. hadoop fs -mkdir -p /user/hive/warehouse
  3. hadoop fs -chmod g+w /tmp
  4. hadoop fs -chmod g+w /user/hive/warehouse

1 初始化meta数据库

进入之前需要初始化数据库

  1. schematool -initSchema -dbType mysql
  3. hadoop@hadoopmaster:/usr/local/hive/lib$ schematool -initSchema -dbType mysql 
  4. SLF4J: Class path contains multiple SLF4J bindings.
  5. SLF4J: Found binding in [jar:file:/usr/local/hive/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  6. SLF4J: Found binding in [jar:file:/usr/local/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  7. SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
  8. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
  9. Metastore connection URL: jdbc:mysql://192.168.1.166:3306/hive?createDatabaseIfNotExist=true
  10. Metastore Connection Driver : com.mysql.jdbc.Driver
  11. Metastore connection User: hive
  12. Starting metastore schema initialization to 2.1.0
  13. Initialization script hive-schema-2.1.0.mysql.sql
  14. Initialization script completed
  15. schemaTool completed

2 测试hive shell

  1. hive
  2. show databases;
  3. show tables;

3可以在hadoop中查看hive生产的文件

  1. hadoop dfs -ls /user/hive/warehouse

七 Hive shell使用实例

在正式讲解HiveQL之前,先在命令行下运行几样命令是有好处的,可以感受一下HiveQL是如何工作的,也可以自已随便探索一下.

1 创建数据(文本以tab分隔)

  1. ~ vi /home/cos/demo/t_hive.txt
  3. 16      2       3
  4. 61      12      13
  5. 41      2       31
  6. 17      21      3
  7. 71      2       31
  8. 1       12      34
  9. 11      2       34

2 创建新表

  1. hive> CREATE TABLE t_hive (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
  2. OK
  3. Time taken: 0.121 seconds

3 导入数据t_hive.txt到t_hive表

  1. hive> LOAD DATA LOCAL INPATH '/tmp/t_hive.txt' OVERWRITE INTO TABLE t_hive ;
  2. Loading data to table default.t_hive
  3. OK
  4. Time taken: 0.609 seconds

4 查看表

  1. hive> show tables;
  2. OK
  3. t_hive
  4. Time taken: 0.099 seconds

5 正则匹配表名

  1. hive>show tables '*t*';
  2. OK
  3. t_hive
  4. Time taken: 0.065 seconds

6 查看表数据

  1. hive> select * from t_hive;
  2. OK
  3. 16      2       3
  4. 61      12      13
  5. 41      2       31
  6. 17      21      3
  7. 71      2       31
  8. 1       12      34
  9. 11      2       34
  10. Time taken: 0.264 seconds

7 查看表结构

  1. hive> desc t_hive;
  2. OK
  3. a       int
  4. b       int
  5. c       int
  6. Time taken: 0.1 seconds

8 增加一个字段

  1. hive> ALTER TABLE t_hive ADD COLUMNS (new_col String);
  2. OK
  3. Time taken: 0.186 seconds
  4. hive> desc t_hive;
  5. OK
  6. a       int
  7. b       int
  8. c       int
  9. new_col string
  10. Time taken: 0.086 seconds

9 重命令表名

  1. ~ ALTER TABLE t_hive RENAME TO t_hadoop;
  2. OK
  3. Time taken: 0.45 seconds
  4. hive> show tables;
  5. OK
  6. t_hadoop
  7. Time taken: 0.07 seconds

10 删除表

  1. hive> DROP TABLE t_hadoop;
  2. OK
  3. Time taken: 0.767 seconds
  5. hive> show tables;
  6. OK
  7. Time taken: 0.064 seconds

八 使用beeline

HiveServer2提供了一个新的命令行工具Beeline，它是基于SQLLine CLI的JDBC客户端。
关于SQLLine的的知识，可以参考这个网站：http://sqlline.sourceforge.net/#manual

Beeline工作模式有两种，即本地嵌入模式和远程模式。嵌入模式情况下，它返回一个嵌入式的Hive（类似于Hive CLI）。而远程模式则是通过Thrift协议与某个单独的HiveServer2进程进行连接通信。下面给一个简单的登录Beeline的使用实例：

1 首先把驱动拷贝到Lib中

  1. sudo cp jdbc/hive-jdbc-2.1.0-standalone.jar /usr/local/hive/lib/

2 启动hiveserver2的服务

  1. 命令行模式:
  3. hive --service hiveserver2 --hiveconf hive.server2.thrift.port=10001
  5. 服务模式：
  7. hiveserver2 start

3 执行操作

  1. % bin/beeline
  2. Hive version 0.11.0-SNAPSHOT by Apache
  3. beeline> !connect jdbc:hive2://localhost:10000/default 
  4. !connect jdbc:hive2://localhost:10000/default 
  5. Connecting to jdbc:hive2://localhost:10000/default
  6. Connected to: Hive (version 0.10.0)
  7. Driver: Hive (version 0.10.0-SNAPSHOT)
  8. Transaction isolation: TRANSACTION_REPEATABLE_READ
  9. 0: jdbc:hive2://localhost:10000> show tables;
  10. show tables;
  11. +-------------------+
  12. |     tab_name      |
  13. +-------------------+
  14. | primitives        |
  15. | src               |
  16. | src1              |
  17. | src_json          |
  18. | src_sequencefile  |
  19. | src_thrift        |
  20. | srcbucket         |
  21. | srcbucket2        |
  22. | srcpart           |
  23. +-------------------+
  24. 9 rows selected (1.079 seconds)

九 FAQ

出错信息1

  1. hadoop@hadoopmaster:/usr/local/hive/conf$ hive 
  2. SLF4J: Class path contains multiple SLF4J bindings.
  3. SLF4J: Found binding in [jar:file:/usr/local/hive/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  4. SLF4J: Found binding in [jar:file:/usr/local/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  5. SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
  6. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
  8. Logging initialized using configuration in jar:file:/usr/local/hive/lib/hive-common-2.1.0.jar!/hive-log4j2.properties Async: true
  9. Exception in thread "main" java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: org.apache.hadoop.hive.ql.metadata.HiveException: MetaException(message:Hive metastore database is not initialized. Please use schematool (e.g. ./schematool -initSchema -dbType ...) to create the schema. If needed, don't forget to include the option to auto-create the underlying database in your JDBC connection string (e.g. ?createDatabaseIfNotExist=true for mysql))
  10.  at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:578)
  11.  at org.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:518)
  12.  at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:705)
  13.  at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:641)
  14.  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  15.  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
  16.  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  17.  at java.lang.reflect.Method.invoke(Method.java:606)
  18.  at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
  19.  at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
  20. Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: org.apache.hadoop.hive.ql.metadata.HiveException: MetaException(message:Hive metastore database is not initialized. Please use schematool (e.g. ./schematool -initSchema -dbType ...) to create the schema. If needed, don't forget to include the option to auto-create the underlying database in your JDBC connection string (e.g. ?createDatabaseIfNotExist=true for mysql))
  21.  at org.apache.hadoop.hive.ql.metadata.Hive.registerAllFunctionsOnce(Hive.java:226)
  22.  at org.apache.hadoop.hive.ql.metadata.Hive.<init>(Hive.java:366)
  23.  at org.apache.hadoop.hive.ql.metadata.Hive.create(Hive.java:310)
  24.  at org.apache.hadoop.hive.ql.metadata.Hive.getInternal(Hive.java:290)
  25.  at org.apache.hadoop.hive.ql.metadata.Hive.get(Hive.java:266)
  26.  at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:545)
  27.  ... 9 more
  28. Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: MetaException(message:Hive metastore database is not initialized. Please use schematool (e.g. ./schematool -initSchema -dbType ...) to create the schema. If needed, don't forget to include the option to auto-create the underlying database in your JDBC connection string (e.g. ?createDatabaseIfNotExist=true for mysql))
  29.  at org.apache.hadoop.hive.ql.metadata.Hive.getAllFunctions(Hive.java:3593)
  30.  at org.apache.hadoop.hive.ql.metadata.Hive.reloadFunctions(Hive.java:236)
  31.  at org.apache.hadoop.hive.ql.metadata.Hive.registerAllFunctionsOnce(Hive.java:221)
  32.  ... 14 more
  33. Caused by: MetaException(message:Hive metastore database is not initialized. Please use schematool (e.g. ./schematool -initSchema -dbType ...) to create the schema. If needed, don't forget to include the option to auto-create the underlying database in your JDBC connection string (e.g. ?createDatabaseIfNotExist=true for mysql))
  34.  at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3364)
  35.  at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3336)
  36.  at org.apache.hadoop.hive.ql.metadata.Hive.getAllFunctions(Hive.java:3590)
  37.  ... 16 more

没有执行

  1. schematool -initSchema -dbType mysql

执行之后搞定

出错信息2

  1.  hadoop@hadoopmaster:/usr/local/hive/lib$ hive 
  2. SLF4J: Class path contains multiple SLF4J bindings.
  3. SLF4J: Found binding in [jar:file:/usr/local/hive/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  4. SLF4J: Found binding in [jar:file:/usr/local/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
  5. SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
  6. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
  8. Logging initialized using configuration in jar:file:/usr/local/hive/lib/hive-common-2.1.0.jar!/hive-log4j2.properties Async: true
  9. Exception in thread "main" java.lang.IllegalArgumentException: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D
  10.  at org.apache.hadoop.fs.Path.initialize(Path.java:205)
  11.  at org.apache.hadoop.fs.Path.<init>(Path.java:171)
  12.  at org.apache.hadoop.hive.ql.session.SessionState.createSessionDirs(SessionState.java:631)
  13.  at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:550)
  14.  at org.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:518)
  15.  at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:705)
  16.  at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:641)
  17.  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  18.  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
  19.  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  20.  at java.lang.reflect.Method.invoke(Method.java:606)
  21.  at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
  22.  at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
  23. Caused by: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D
  24.  at java.net.URI.checkPath(URI.java:1804)
  25.  at java.net.URI.<init>(URI.java:752)
  26.  at org.apache.hadoop.fs.Path.initialize(Path.java:202)
  27.  ... 12 more

hive-site.xml中没有配置合理临时目录的问题

  1.  <property> 
  2.  <name>hive.exec.local.scratchdir</name>
  3.  <value>/home/hadoop/iotmp</value>
  4.  <description>Local scratch space for Hive jobs</description>
  5.  </property>
  6.  <property>
  7.  <name>hive.downloaded.resources.dir</name>
  8.  <value>/home/hadoop/iotmp</value>
  9.  <description>Temporary local directory for added resources in the remote file system.</description>
  10.  </property>
  11.  <property>

并且需要对目录进行权限设定

  1. mkdir -p /home/hadoop/iotmp
  2. chmod -R 775 /home/hadoop/iotmp

参考文档