HIVE搭建和使用

2020-07-01 17:32:45

HIVE是一个基于Hadoop的数据仓库，适用于一些高延迟性的应用（离线开发）。如果对延迟性要求比较高，则可以选择Hbase。

Hive的工作原理简单的说就是一个查询引擎，接收到一个SQL，然后解析到集群能处理的程序（MR），后返回结果。

前提：需要已经安装配置好hadoop参考：

高威：大数据环境搭建zhuanlan.zhihu.com

Hive介绍

hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行。Hive的优点是学习成本低，可以通过类似SQL语句实现快速MapReduce统计，使MapReduce变得更加简单，而不必开发专门的MapReduce应用程序。hive是十分适合数据仓库的统计分析和Windows注册表文件。(来源：百度百科)

Hive基本上是所有大数据开发必须掌握的一个中间件，HQL也是必须要掌握的一门快速开发语言，目前所对的大数据开发，除了Java、Scala、python等项目式或者脚本式的开发模式外，对于即席查询和快速查询一般都是用hive的比较多。

目前hive计算引擎目前有三种：

set hive.execution.engine=mr;
set hive.execution.engine=spark;
set hive.execution.engine=spark;

Hive的用户可以通过hive.execution.engine来设置计算引擎，目前该参数可选的值为mr和tez。为了实现Hive on Spark，我们将spark作为该参数的第三个选项。要开启Hive on Spark模式，用户仅需将这个参数设置为spark即可。

Hive安装

下载hive：

https://mirrors.tuna.tsinghua.edu.cn/apache/hive/hive-2.3.6/apache-hive-2.3.6-bin.tar.gzmirrors.tuna.tsinghua.edu.cn

2、解压

gunzip apache-hive-2.3.6-bin.tar.gz
tar -zxvf apache-hive-2.3.6-bin.tar

3、安装mysql和创建hive元数据库

创建hive库
create database hive default character set latin1;
grant all privileges on hive.* to hive@'%' identified by '123456';
flush privileges;

4、修改环境配置

source .bash_profile

5、修改hive配置文件

复制几个配置文件
cp hive-default.xml.template hive-site.xml
cp hive-env.sh.template hive-env.sh
cp hive-log4j2.properties.template hive-log4j2.properties              
cp hive-exec-log4j2.properties.template hive-exec-log4j2.properties

修改hive-site.xml的配置文件
（主要是修改mysql的链接）
1、
<property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>Driver class name for a JDBC metastore</description>
  </property>
2、
<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://自己IP:3306/hive?createDatabaseIfNotExist=true&amp;characterEncoding=UTF-8&amp;useSSL=false</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>
3、
<property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>hive</value>
    <description>Username to use against metastore database</description>
  </property>
4、
<property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>123456</value>
    <description>password to use against metastore database</description>
  </property>

6、复制mysql的驱动程序到hive/lib下面

mysql-connector-java-5.1.48-bin.jar

7、在mysql中hive的schema（初始化）

./schematool -dbType mysql -initSchema

注意在重启hive的时候，需要先看mysql中是否有hive的库，如果存在，会报错：

解决方案：

mysql> drop database hive;

8、启动hive

9、验证

10、使用场景

上一篇搭建Hadoop集群之后，测试worldcount的结果数据如下：

将这份数据写到hive表里面

CREATE EXTERNAL TABLE IF NOT EXISTS test
(
    `shop_code` string COMMENT '店铺Code',
    `zt_customer_risk_cn` int COMMENT '近30天的自拓客户数'
)
COMMENT '店铺对应近30天数据指标'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY "\t"
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION 'hdfs://localhost:9000/data/output';

执行之后：

11、spark连接hive

修改hive的配置文件

<property>
  <name>hive.metastore.schema.verification</name>
  <value>true</value>
</property>
改为false即可

将cong/hive-site.xml和lib/mysql-connector-java-5.1.48-bin.jar分别拷贝到spark的conf和JAR文件夹下面：

cp /Users/gaowei/Package/apache-hive-2.3.6-bin/lib/mysql-connector-java-5.1.48-bin.jar  /Users/gaowei/Package/spark-2.1.1-bin-hadoop2.7/jars

cp /Users/gaowei/Package/apache-hive-2.3.6-bin/conf/hive-site.xml /Users/gaowei/Package/spark-2.1.1-bin-hadoop2.7/conf

启动spark-shell

安装出现的问题

1、在hive重新进行初始化时，出现以下错误

Schema initialization FAILED! Metastore state would be inconsistent !!

在出现以上错误时，解决方法如下

删除metastore_db文件
删除数据库中对应的database

进行以上操作后，进行重新初始化

2、启动hive的时候报错

hive启动时报错 java.lang.IllegalArgumentException: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D at org.apache.hadoop.fs.Path.initialize - 嘣嘣嚓 - 博客园www.cnblogs.com

修改hive-site.xml配置

<property>
    <name>hive.exec.local.scratchdir</name>
    <value>/Users/gaowei/Package/apache-hive-2.3.6-bin/tmp/${system:user.name}</value>
    <description>Local scratch space for Hive jobs</description>
  </property>
  <property>
    <name>hive.downloaded.resources.dir</name>
    <value>/Users/gaowei/Package/apache-hive-2.3.6-bin/tmp/${hive.session.id}_resources</value>
    <description>Temporary local directory for added resources in the remote file system.</description>
  </property>

<property>
    <name>hive.server2.logging.operation.log.location</name>
    <value>/Users/gaowei/Package/apache-hive-2.3.6-bin/tmp/root/operation_logs</value>
    <description>Top level directory where operation logs are stored if logging functionality is enabled</description>
  </property>

3、在hive输入一些命令，例如（show databases ，show tables)

如果出现报错：

Failed with exception java.io.IOException:java.lang.IllegalArgumentException: java.net.URISyntaxException: Relative path in absolute URI: ${system:user.name%7D

修改：hive-site.xml配置

HIVE的基本语法

1、创建hive表

建表，一般建立的外部表，由于表是外部的，所以hive并非认为其完全拥有这份数据，删除外部表的时候，并不会删除这份数据，而内部表也叫管理表，删除后，数据一起被删除。

DROP TABLE IF EXISTS db_business.test;
CREATE EXTERNAL TABLE IF NOT EXISTS db_business.test
(
    `shop_code` string COMMENT '店铺Code',
    `zt_customer_risk_cn` int COMMENT '近30天的自拓客户数'
)
COMMENT '店铺对应近30天数据指标'
partitioned by (ds string comment '分区')
STORED AS PARQUET;

一般都是动态分区，存储模式：parquet

parque格式采用列存储，不仅存储小，读的速度也比较快，spark、impala均对parquet有很好的支持
存储空间特别小的表(整个表小于1G，包括预计增长的数据），可以采用textfile格式
存储空间特别大的表，如果读不频繁的，应该采用SNAPPY格式压缩

非动态分区建表规则：

CREATE EXTERNAL TABLE IF NOT EXISTS db_business.test
(
    `shop_code` string COMMENT '店铺Code',
    `zt_customer_risk_cn` int COMMENT '近30天的自拓客户数'
)
COMMENT '店铺对应近30天数据指标'
partitioned by (ds string comment '分区')
ROW FORMAT DELIMITED
NULL DEFINED AS ""
STORED AS TEXTFILE
LOCATION 'hdfs://NameNodeHACluster/xxx/db_business.db/test';

2、加载数据

利用HQL语句，向指定的表中加载数据，一般来讲有两种方式，种是本地现有的数据，可以通过load data local inpath (overwrite) into来实现，其中，对于外部表来说，我们一般利用overwrite，这样就可以覆盖以前表的内容。第二种则是，通过HQL跑出来的数据，insert overwrite （local） directory 来直接加载数据，可以跑出来在本地或者在HDFS集群。

1、直接写HDFS路径的非动态分区表，在执行执行需要加载分区

insert overwrite directory '/APPLICATION/WEISHICHUANG/PACKAGE/cdpi_config/${hivevar:dateStrday}/${hivevar:dateStrhour}'
select b.msisdn,a.MEID,a.protocolidentifier,a.StartTimeStr,a.EndTimeStr,a.InputOctets,a.OutputOctets,a.wholeuri
from t_cdpi a
join db_shujgl.config b
where a.day=${hivevar:dateStrday}
and a.hour=${hivevar:dateStrhour}
and substr(a.msisdn,3,11)=b.msisdn;

alter table db_shujgl.cdpi_config add partition(day=${hivevar:dateStrday},hour=${hivevar:dateStrhour}) location '/APPLICATION/WEISHICHUANG/PACKAGE/cdpi_config/${hivevar:dateStrday}/${hivevar:dateStrhour}';

2、直接加栽到分区

set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;

INSERT OVERWRITE TABLE db_business.test1 partition(ds='{{yesterday}}')

select d.shop_code, 
d.name as shop_name,
from db_business.ods_test d

3、动态分区通过其他途径加载

例如：spark(sparksql)

val spark = SparkSession
       .builder()
       .appName("test")
       .getOrCreate()
    val sc = spark.sparkContext
    val hiveContext = new org.apache.spark.sql.hive.HiveContext(sc)
    hiveContext.sql("SET hive.exec.dynamic.partition = true")
    hiveContext.sql("SET hive.exec.dynamic.partition.mode = nonstrict ")
    hiveContext.sql("SET mapreduce.input.fileinputformat.input.dir.recursive = true")
    hiveContext.sql("SET hive.input.dir.recursive = true")
    hiveContext.sql("SET hive.mapred.supports.subdirectories = true")
    hiveContext.sql("SET hive.supports.subdirectories = true")

DF.withColumn("ds",lit(inputTime)).write.mode("overwrite").
       insertInto("db_business.test1")

mysql(sqoop)

hadoop fs -mkdir /user/hive/warehouse/dw.db/dws_middle_item_operationdata_di/${dateStrDay}

sqoop import --connect jdbc:mysql://XXXXX:3306/warehouse?tinyInt1isBit=false  --username XX --password XXXXXXX  --table xxxxx --compression-codec=snappy   --as-parquetfile  -m 2  --target-dir "$target_dir"

hive -e "alter table dw.dws_middle_item_operationdata_di   drop if exists partition (ds='${dateStr}') ; alter table dw.dws_middle_item_operationdata_di add partition  (ds='${dateStr}') location '${target_dir}'"

4、其他操作

1、加载UDF：
hive> add jar /data1/push_flow_monitor/shell/MR20141216.jar;
Added /data1/push_flow_monitor/shell/MR20141216.jar to class path
Added resource: /data1/push_flow_monitor/shell/MR20141216.jar
hive> create temporary  function decode as 'com.zjhcsoft.hive.Base64EncodeUDF';
OK

2、新增列：
注意新增的列一定是在后一列，不同于mysql
alter table testx add columns(`c1` int COMMENT '测试1',`c2` int COMMENT '测试2',`c3` int COMMENT '测试3')

3、删除分区：
ALTER TABLE db_business.test1 DROP IF EXISTS PARTITION (ds='2020-04-02');

5、数仓分层