Hive

http://hive.apache.org/

 

 

支持的：sql和hql长的很像，sql查询延迟低，hive通过sql转为mr或者spark作业在集群上运行，用于离线批处理

都支持insert，update，不建议hive使用，性能低

区别的：mysql和hive都是支持分布式。mysql集群小构建在专用机器，昂贵的，hive构建在hadoop上面数据仓库，成千上完个hadoop节点上，廉价的机器上。mysql处理pb最大了。

Hadoop 3.1.3伪分布式环境安装Hive 3.1.2的异常总结, https://www.cnblogs.com/mengrennwpu/p/11892306.html

1、Hive3.1.2下载路径：https://mirror.bit.edu.cn/apache/hive/，jdk使用：jdk-8u65-linux-x64.tar.gz

下载hive和mysql-connector-java wget http://mirror.bit.edu.cn/apache/hive/hive-3.1.2/apache-hive-3.1.2-bin.tar.gz

3、配置~/.bash_profile

export HIVE_HOME=/root/app/apache-hive-3.1.2-bin export PATH=$PATH:$HIVE_HOME/bin

4.修改配置hive-env.sh

cp hive-env.sh.template hive-env.sh export HADOOP_HOME=/root/app/hadoop-3.1.3 # Hive Configuration Directory can be controlled by: # export HIVE_CONF_DIR= export HIVE_CONF_DIR=/root/app/apache-hive-3.1.2-bin/conf # Folder containing extra libraries required for hive compilation/execution can be controlled by: # export HIVE_AUX_JARS_PATH= export HIVE_AUX_JARS_PATH=/root/app/apache-hive-3.1.2-bin/lib

hive-site.xml

<configuration> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://10.179.210.71:3306/hadoop_hive?createDatabaseIfNotExist=true</value> <description> JDBC connect string for a JDBC metastore. To use SSL to encrypt/authenticate the connection, provide database-specific SSL flag in the connection URL. For example, jdbc:postgresql://myhost/db?ssl=true for postgres database. </description> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> <description>Driver class name for a JDBC metastore</description> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value> <description>Username to use against metastore database</description> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>liuqi</value> <description>password to use against metastore database</description> </property> </configuration>

5.拷贝mysql驱动到$HIVE_HOME/lib

wget https://gitee.com/chenshiba/code/blob/master/mysql-connector-java-5.1.39.jar mysql-connector-java-5.1.47.jar 将jar放到$HIVE_HOME/lib目录

6.安装数据库

https://blog.csdn.net/INGNIGHT/article/details/105107228

https://www.cnblogs.com/julyme/p/5969626.html

7.创建metada schema

schematool -initSchema -dbType mysql

Mysql报错java.sql.SQLException:null,message from server:"Host '27,45,38,132' is not allowed to connect

use mysql;select host from user;update user set host ='%' where user ='root';

重新启动mysql服务

8.hive即可对hive命令行进行测试

替换hive/lib/guava https://mvnrepository.com/artifact/com.google.guava/guava/27.0-jre

关于guava的异常：java.lang.NoSuchMethodError: com.google.common.base.Preconditions.checkArgument(ZLjava/lang/String;Ljava/lang/Object;)V

解决方案：hive 3.1.2中默认的guava包为guava-19.0.jar，在https://mvnrepository.com/artifact/com.google.guava/guava下载比较新的guava包替换即可，博主使用的是guava-27.0-jre.jar

创建一个helloworld.txt文件以\t分隔

create database test_db; use test_db; create table helloworld(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; show table; load data local inpath '/root/app/apache-hive-3.1.2-bin/helloworld.txt' overwrite into table helloworld; select * from helloworld; select count(1) from helloworld; desc database test_db; desc database extended test_db;

 

DDL：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];