hive sql map 转多列 hive sql mapreduce

Hive1.2.1_hive介绍和三种交互方式

1、Hive基本概念

1.1 Hive简介

Hive本质是将SQL转换为MapReduce的任务进行运算，底层由HDFS来提供数据存储，说白了hive可以理解为一个将SQL转换为MapReduce的任务的工具，甚至更近一步说hive就是一个MapReduce客户端。

思考：计算文件user.txt中张三出现几次，使用mapreduce怎么写，然后再比照下图的hive实现过程

为什么使用Hive?

如果直接使用hadoop的话，人员学习成本太高，项目要求周期太短，MapReduce实现复杂查询逻辑开发难度太大。如果使用hive的话，可以操作接口采用类SQL语法，提高开发能力，免去了写MapReduce，减少开发人员学习成本，功能扩展很方便（比如：开窗函数）。

Hive的特点：

1、可扩展性

Hive可以自由的扩展集群的规模，一般情况下不需要重启服务

2、延申性

Hive支持自定义函数，用户可以根据自己的需求来实现自己的函数

3、容错

即使节点出现错误，SQL仍然可以完成执行

Hive的优缺点：

优点：

1、操作接口采用类sql语法，提供快速开发的能力（简单、容易上手）

2、避免了去写MapReduce,减少开发人员的学习成本

3、Hive的延迟性比较高，因此Hive常用于数据分析，适用于对实时性要求不高的场合

4、Hive 优势在于处理大数据，对于处理小数据没有优势，因为 Hive 的执行延迟比较高。（不断地开关JVM虚拟机）

5、Hive 支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

6、集群可自由扩展并且具有良好的容错性，节点出现问题SQL仍可以完成执行

缺点：

1、Hive的HQL表达能力有限

（1）迭代式算法无法表达 （反复调用，mr之间独立，只有一个map一个reduce，反复开关）

（2）数据挖掘方面不擅长

2、Hive 的效率比较低

（1）Hive 自动生成的 MapReduce 作业，通常情况下不够智能化

（2）Hive 调优比较困难，粒度较粗 （hql根据模板转成mapreduce，不能像自己编写mapreduce一样精细，无法控制在map处理数据还是在reduce处理数据）

Hive和传统数据库对比

Hive应用场景

日志分析：大部分互联网公司使用hive进行日志分析，如百度、淘宝等。

统计一个网站一个时间段内的pv,uv，SKU,SPU（sku表示确定的一个商品黑色的40码的布鞋子，spu表示鞋子）

多维度数据分析

海量结构化数据离线分析

构建数据仓库

PV（Page View）访问量, 即页面浏览量或点击量，衡量网站用户访问的网页数量；在一定统计周期内用户每打开或刷新一个页面就记录1次，多次打开或刷新同一页面则浏览量累计。

UV（Unique Visitor）独立访客，统计1天内访问某站点的用户数(以cookie为依据);访问网站的一台电脑客户端为一个访客。可以理解成访问某网站的电脑的数量。网站判断来访电脑的身份是通过来访电脑的cookies实现的。如果更换了IP后但不清除cookies，再访问相同网站，该网站的统计中UV数是不变的。如果用户不保存cookies访问、清除了cookies或者更换设备访问，计数会加1。00:00-24:00内相同的客户端多次访问只计为1个访客。

1.2 Hive架构

1.2.1 Client

Hive允许client连接的方式有三个CLI（hive shell）、JDBC/ODBC(java访问hive)、WEBUI（浏览器访问 hive）。JDBC访问时中间件Thrift软件框架，跨语言服务开发。DDL DQL DML,整体仿写一套SQL语句。

1）client–需要下载安装包

2）JDBC/ODBC 也可以连接到Hive
现在主流都在倡导第二种 HiveServer2/beeline
做基于用户名和密码安全的一个校验

3）Web Gui
hive给我们提供了一套简单的web页面
我们可以通过这套web页面访问hive 做的太简陋了

1.2.2 Metastore

元数据包括表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是 外部表）、表的数据所在目录等。

一般需要借助于其他的数据载体（数据库）

主要用于存放数据库的建表语句等信息

推荐使用Mysql数据库存放数据

连接数据库需要提供：uri username password driver

1.2.3 Driver

元数据存储在数据库中，默认存在自带的derby数据库（单用户局限性）中，推荐使用Mysql进行存储。

1） 解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完 成，比如ANTLR；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。

2） 编译器（Physical Plan）：将AST编译生成逻辑执行计划。

3） 优化器（Query Optimizer）：对逻辑执行计划进行优化。

4） 执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是 MR/Spark。

1.2.4 数据处理

Hive的数据存储在HDFS中，计算由MapReduce完成。HDFS和MapReduce是源码级别上的整合，两者结合最佳。解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。

注意，下面安装，非高亮的上片博客已经写配置过了！！！配照片的没改

1.3 Hive的安装

在之前博客中我有记录安装JDK和Hadoop和Mysql的过程，如果还没有安装，请先进行安装配置好，对应的随笔我也提供了百度云下载连接。

1.3.1 上传压缩包并解压

tar -zxvf apache-hive-1.2.1-bin.tar.gz

1.3.2 修改目录名称

mv apache-hive-1.2.1-bin hive-1.2.1

1.3.3 备份配置文件

cp hive-env.sh.template hive-env.sh

cp hive-default.xml.template hive-site.xml

1.3.4 修改配置hive的配置文件（在conf目录下）

修改hive-env,sh

加入三行内容（大家根据自己的情况来添加,每个人安装路径可能有所不同）

HADOOP_HOME=/usr/local/soft/hadoop-2.7.6
JAVA_HOME=/usr/local/soft/jdk1.8.0_171
HIVE_HOME=/usr/local/soft/hive-1.2.1

修改hive-site.xml (找到对应的键对值进行修改，注意！！！是修改，而不是全部直接复制粘贴)

===============第一个是新增的配置========================
<！--数据存储位置就是我们在HDFS上看的目录-->
<property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>
</property>
    
=======================================================
(注意：修改自己安装mysql的主机地址）
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://192.168.40.110:3306/hive?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=utf8&useSSL=false</value>
</property>

(固定写法，mysql驱动类的位置)
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>

（mysql的用户名）
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>


（mysql的用户密码）
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>

 
（你的hive安装目录的tmp目录）
<property>
<name>hive.querylog.location</name>
<value>/usr/local/soft/hive-1.2.1/tmp</value>
</property>

 
（同上）
<property>
<name>hive.exec.local.scratchdir</name>
<value>/usr/local/soft/hive-1.2.1/tmp</value>
</property>


（同上）
<property>
<name>hive.downloaded.resources.dir</name>
<value>/usr/local/soft/hive-1.2.1/tmp</value>
</property>
====================新增的==============================
<!--指定这个的时候，为了启动metastore服务的时候不用指定端口-->
<!--hive --service metastore -p 9083 & | hive --service metastore-->
<property>
    <name>hive.metastore.uris</name>
    <value/>
    <description>thrift://master:9083</description>
</property>

修改hadoop中的配置文件 core-site.xml 直接改，改完重启就行，为后面beeline连接做准备

注意：三个节点上的都要改。

<!--该参数表示可以通过httpfs接口hdfs的ip地址限制-->
<property>
 <name>hadoop.proxyuser.hadoop.hosts</name>
 <value>*</value>
</property>
<!--通过httpfs接口访问的用户获得的群组身份-->
<property>
 <name>hadoop.proxyuser.hadoop.groups</name>
 <value>*</value>
</property>

注意：每个两个从节点也需要配置

1.3.5 拷贝mysql驱动到$HIVE_HOME/lib目录下

cp /usr/local/soft/mysql-connector-java-5.1.49.jar ../lib/

1.3.6 将hadoop的jline-0.9.94.jar的jar替换成hive的版本。

cp /usr/local/soft/hive-1.2.1/lib/jline-2.12.jar /usr/local/soft/hadoop-2.7.6/share/hadoop/yarn/lib/

1.3.7 将hive的bin目录配置到环境变量中去

export HIVE_HOME=/usr/local/soft/hive-1.2.1
export PATH=.:$HIVE_HOME/bin

1.3.8 source命令让环境变量生效

1.3.9 拷贝到其他两个节点中去，因为可能我们会在其他的节点上当作客户端访问hive，注意，也需要配置环境变量，增加驱动jar包，将hadoop的jline-0.9.94.jar的jar替换成hive的版本

配置环境变量：

1.3.10 启动

启动hadoop

start-all.sh

启动hive

hive --service metastore

nohup hive --service metastore >/dev/null &

hive

启动HiveServer2

hiveserver2

nohup hiveserver2 >/dev/null &

beeline -u jdbc:hive2://master:10000 -n root

1.4 Hive的三种交互方式

1）第一种交互方式

shell交互Hive，用命令hive启动一个hive的shell命令行，在命令行中输入sql或者命令来和Hive交互。

服务端启动metastore服务（后台启动）：nohup hive --service metastore > /usr/local/soft/mylogs 2>&1 &
进入命令:hive
退出命令行：quit;

2)第二种交互方式

Hive启动为一个服务器，对外提供服务，其他机器可以通过客户端通过协议连接到服务器，来完成访问操作，这是生产环境用法最多的

服务端启动hiveserver2服务：
nohup hive --service metastore >/dev/null &
nohup hiveserver2 >/dev/null &

需要稍等一下，启动服务需要时间：
进入命令:1)先执行： beeline ，再执行： !connect jdbc:hive2://master:10000 
        2)或者直接执行：  beeline -u jdbc:hive2://master:10000 -n root
退出命令行：！exit

然后复制回话，启动；hiveserver2

最后在在 node1 使用上使用 客户端 也就是界面好看一点！

断开连接后：服务器端就会报错

3）第三种交互方式

使用 –e 参数来直接执行hql的语句

bin/hive -e "show databases;"

使用 –f 参数通过指定文本文件来执行hql的语句

特点：执行完sql后，回到linux命令行。

vim hive.sql

use myhive;
select * from test;

hive -f hive.sql

编辑的操作hive 的文件

结果如下：

4）hive cli和beeline cli的区别

1.5 Hive元数据

Hive元数据库中一些重要的表结构及用途，方便Impala、SparkSQL、Hive等组件访问元数据库的理解。

1、存储Hive版本的元数据表(VERSION)，该表比较简单，但很重要,如果这个表出现问题，根本进不来Hive-Cli。比如该表不存在，当启动Hive-Cli的时候，就会报错“Table 'hive.version' doesn't exist”

2、Hive数据库相关的元数据表(DBS、DATABASE_PARAMS)

DBS：该表存储Hive中所有数据库的基本信息。

DATABASE_PARAMS：该表存储数据库的相关参数。

3、Hive表和视图相关的元数据表

主要有TBLS、TABLE_PARAMS、TBL_PRIVS，这三张表通过TBL_ID关联。
TBLS:该表中存储Hive表，视图，索引表的基本信息。
TABLE_PARAMS:该表存储表/视图的属性信息。
TBL_PRIVS：该表存储表/视图的授权信息。
4、Hive文件存储信息相关的元数据表

主要涉及SDS、SD_PARAMS、SERDES、SERDE_PARAMS，由于HDFS支持的文件格式很多，而建Hive表时候也可以指定各种文件格式，Hive在将HQL解析成MapReduce时候，需要知道去哪里，使用哪种格式去读写HDFS文件，而这些信息就保存在这几张表中。
SDS：该表保存文件存储的基本信息，如INPUT_FORMAT、OUTPUT_FORMAT、是否压缩等。TBLS表中的SD_ID与该表关联，可以获取Hive表的存储信息。
SD_PARAMS: 该表存储Hive存储的属性信息。
SERDES:该表存储序列化使用的类信息。
SERDE_PARAMS:该表存储序列化的一些属性、格式信息，比如:行、列分隔符。
5、Hive表字段相关的元数据表

主要涉及COLUMNS_V2：该表存储表对应的字段信息。