hive是数据仓库工具,解释器,编译器,忧化器,是非java程序员使用hdfs数据。
hive是将sql语句转换成MapReduce任务执行,源数据是HDFS,目标数据在关系型数据库中。
1.下载安装hive
[root@node1 ~]# wget http://mirrors.shu.edu.cn/apache/hive/hive-2.3.4/apache-hive-2.3.4-bin.tar.gz
[root@node1 ~]# tar xvf apache-hive-2.3.4-bin.tar.gz -C /opt/
[root@node1 ~]# cd /opt/
[root@node1 opt]# mv apache-hive-2.3.4-bin/ hive-2.3.4
2.配置环境变量
[root@node1 opt]# vim /etc/profile
export JAVA_HOME=/usr/java/jdk1.8.0_202-amd64
export HIVE_HOME=/opt/hive-2.3.4
export PATH=$PATH:$HIVE_HOME/bin
[root@node1 opt]# source /etc/profile
[root@node1 opt]# hive --version
Hive 2.3.4
Git git://daijymacpro-2.local/Users/daijy/commit/hive -r 56acdd2120b9ce6790185c679223b8b5e884aaf2
Compiled by daijy on Wed Oct 31 14:20:50 PDT 2018
From source with checksum 9f2d17b212f3a05297ac7dd40b65bab0
[root@node1 opt]#
3.修改配置文件
[root@node1 opt]# cd hive-2.3.4/conf/
[root@node1 conf]# cp -a hive-default.xml.template hive-site.xml
[root@node1 conf]# vim hive-site.xml --添加mysql连接信息
javax.jdo.option.ConnectionUserName --以下配置只需要在配置文件修改即可,里面全部包含了 hive javax.jdo.option.ConnectionPassword system javax.jdo.option.ConnectionURL jdbc:mysql://172.16.9.100:3306/hive?createDatabaseIfNotExist=true javax.jdo.option.ConnectionDriverName com.mysql.jdbc.Driver
[root@node1 conf]# mv mysql-connector-java-5.1.32.jar /opt/hive-2.3.4/lib/
[root@node1 conf]# mkdir /opt/hive-2.3.4/tmp
[root@node1 conf]# vim hive-site.xml
用/opt/hive-2.3.4/tmp替换"system:java.io.tmpdir"的配置项
[root@node1 conf]# schematool -initSchemaTo 2.2.0 -dbType mysql --初使化mysql,可以选择版本2.2.0,默认安装的2.3.0会报错
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/hive-2.3.4/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/hadoop-2.8.5/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Metastore connection URL: jdbc:mysql://172.16.9.100:3306/hive?createDatabaseIfNotExist=true --请注意信息是否与配置文件相附合
Metastore Connection Driver : com.mysql.jdbc.Driver --请注意信息是否与配置文件相附合
Metastore connection User: hive --请注意信息是否与配置文件相附合
Starting metastore schema initialization to 2.2.0
Initialization script hive-schema-2.2.0.mysql.sql
Initialization script completed
schemaTool completed
[root@node1 conf]#
4.创建数据库和表
[root@node1 conf]# hive
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/hive-2.3.4/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/hadoop-2.8.5/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Logging initialized using configuration in jar:file:/opt/hive-2.3.4/lib/hive-common-2.3.4.jar!/hive-log4j2.properties Async: true
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
hive> create database tong;
OK
Time taken: 8.08 seconds
hive> use tong;
OK
Time taken: 0.144 seconds
hive> create table t(a int,b int);
OK
Time taken: 1.253 seconds
hive>
5.在mysql中查看数据库和表信息
[root@tongc-yum162 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 975614
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> use hive
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [hive]> select * from DBS;
+-------+-----------------------+----------------------------------------------+---------+------------+------------+
| DB_ID | DESC | DB_LOCATION_URI | NAME | OWNER_NAME | OWNER_TYPE |
+-------+-----------------------+----------------------------------------------+---------+------------+------------+
| 1 | Default Hive database | hdfs://mycluster/user/hive/warehouse | default | public | ROLE |
| 2 | NULL | hdfs://mycluster/user/hive/warehouse/tong.db | tong | root | USER |
+-------+-----------------------+----------------------------------------------+---------+------------+------------+
2 rows in set (0.00 sec)
MySQL [hive]> select * from TBLS;
+--------+-------------+-------+------------------+-------+-----------+-------+----------+---------------+--------------------+--------------------+--------------------+
| TBL_ID | CREATE_TIME | DB_ID | LAST_ACCESS_TIME | OWNER | RETENTION | SD_ID | TBL_NAME | TBL_TYPE | VIEW_EXPANDED_TEXT | VIEW_ORIGINAL_TEXT | IS_REWRITE_ENABLED |
+--------+-------------+-------+------------------+-------+-----------+-------+----------+---------------+--------------------+--------------------+--------------------+
| 1 | 1552831024 | 2 | 0 | root | 0 | 1 | t | MANAGED_TABLE | NULL | NULL | |
+--------+-------------+-------+------------------+-------+-----------+-------+----------+---------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)
MySQL [hive]>
错误信息:
Logging initialized using configuration in jar:file:/opt/hive-2.3.4/lib/hive-common-2.3.4.jar!/hive-log4j2.properties Async: true
Exception in thread "main" java.lang.IllegalArgumentException: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D
解决方法:
[root@node1 conf]# mkdir /opt/hive-2.3.4/tmp
[root@node1 conf]# vim hive-site.xml
用/opt/hive-2.3.4/tmp替换"system:java.io.tmpdir"的配置项
[root@node1 conf]#
错误信息:
Error: Syntax error: Encountered "" at line 1, column 64. (state=42X01,code=30000)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
解决方法:
[root@node1 derby]# vim hive-schema-2.3.0.derby.sql --注释以下两行
--CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME
'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ;
--CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME
'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ;
[root@node1 derby]# schematool -initSchema -dbType derby createDatabaseIfNotExist=true
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/hive-2.3.4/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/hadoop-2.8.5/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Metastore connection URL: jdbc:derby:;databaseName=metastore_db;create=true
Metastore Connection Driver : org.apache.derby.jdbc.EmbeddedDriver
Metastore connection User: APP
Starting metastore schema initialization to 2.3.0
Initialization script hive-schema-2.3.0.derby.sql
Initialization script completed
schemaTool completed
[root@node1 derby]#
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25854343/viewspace-1415529/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25854343/viewspace-1415529/