【大数据】Hadoop 体系（三）

Hadoop

四、数据仓库基础与Apache Hive入门

1. 数据仓库基本概念

1.1 数据仓库概念

1.1.1 数仓概念

• 数据仓库（英语：Data Warehouse，简称数仓、DW）,是一个用于存储、分析、报告的数据系统。
• 数据仓库的目的是构建面向分析的集成化数据环境，分析结果为企业提供决策支持（Decision Support）。

1.1.2 数仓专注分析

• 数据仓库本身并不“生产”任何数据，其数据来源于不同外部系统；
• 同时数据仓库自身也不需要“消费”任何的数据，其结果开放给各个外部应用使用；
• 这也是为什么叫“仓库”，而不叫“工厂”的原因。
  

1.2 场景案例：数据仓库为何而来

• 数据仓库为何而来，解决什么问题的？
  • 先下结论：为了分析数据而来，分析结果给企业决策提供支撑。
  • 下面以中国人寿保险公司（chinalife）发展为例，阐述数据仓库为何而来？

1.2.1 业务数据的存储问题

• 中国人寿保险（集团）公司下辖多条业务线，包括：人寿险、财险、车险，养老险等。各业务线的业务正常运营需要记录维护包括客户、保单、收付费、核保、理赔等信息。这么多业务数据存储在哪里呢？
• 联机事务处理系统（OLTP）正好可以满足上述业务需求开展, 其主要任务是执行联机事务处理。其基本特征是前台接收的用户数据可以立即传送到后台进行处理，并在很短的时间内给出处理结果。
  
• 关系型数据库（RDBMS）是OLTP典型应用，比如：Oracle、MySQL、SQL Server等。
  

1.2.2 分析型决策的制定

• 随着集团业务的持续运营，业务数据将会越来越多。由此也产生出许多运营相关的困惑：
  • 能够确定哪些险种正在恶化或已成为不良险种？
  • 能够用有效的方式制定新增和续保的政策吗？
  • 理赔过程有欺诈的可能吗？
  • 现在得到的报表是否只是某条业务线的？集团整体层面数据如何？
• 为了能够正确认识这些问题，制定相关的解决措施，瞎拍桌子是肯定不行的。
• 最稳妥办法就是：基于业务数据开展数据分析，基于分析的结果给决策提供支撑。也就是所谓的数据驱动决策的制定。

1.2.3 OLTP环境开展分析可行吗？

• 可以，但是没必要
• OLTP系统的核心是面向业务，支持业务，支持事务。所有的业务操作可以分为读、写两种操作，一般来说读的压力明显大于写的压力。如果在OLTP环境直接开展各种分析，有以下问题需要考虑：
  • 数据分析也是对数据进行读取操作，会让读取压力倍增；
  • OLTP仅存储数周或数月的数据；
  • 数据分散在不同系统不同表中，字段类型属性不统一；

1.2.4 数据仓库面世

• 当分析所涉及数据规模较小的时候，在业务低峰期时可以在OLTP系统上开展直接分析。
• 但为了更好的进行各种规模的数据分析，同时也不影响OLTP系统运行，此时需要构建一个集成统一的数据分析平台。该平台的目的很简单：面向分析，支持分析，并且和OLTP系统解耦合。
• 基于这种需求，数据仓库的雏形开始在企业中出现了。

1.2.5 数据仓库的构建

• 如数仓定义所说，数仓是一个用于存储、分析、报告的数据系统，目的是构建面向分析的集成化数据环境。我们把这种面向分析、支持分析的系统称之为OLAP（联机分析处理）系统。当然，数据仓库是OLAP系统的一种实现。
• 中国人寿保险公司就可以基于分析决策需求，构建数仓平台。
  

1.3 数据仓库主要特征

• 面向主题性（Subject-Oriented）
  • 主题是一个抽象的概念，是较高层次上企业信息系统中的数据综合、归类并进行分析利用的抽象。在逻辑意义上，它是对应企业中某一宏观分析领域所涉及的分析对象。
  • 传统OLTP系统对数据的划分并不适用于决策分析。而基于主题组织的数据则不同，它们被划分为各自独立的领域，每个领域有各自的逻辑内涵但互不交叉，在抽象层次上对数据进行完整、一致和准确的描述。
• 集成性（Integrated）
  • 主题相关的数据通常会分布在多个操作型系统中，彼此分散、独立、异构。
  • 因此在数据进入数据仓库之前，必然要经过统一与综合，对数据进行抽取、清理、转换和汇总，这一步是数据仓库建设中最关键、最复杂的一步，所要完成的工作有：
    • 要统一源数据中所有矛盾之处；
      • 如字段的同名异义、异名同义、单位不统一、字长不一致等等。
    • 进行数据综合和计算。
      • 数据仓库中的数据综合工作可以在从原有数据库抽取数据时生成，但许多是在数据仓库内部生成的，即进入数据仓库以后进行综合生成的。
  • 下图说明了保险公司综合数据的简单处理过程，其中数据仓库中与“承保”主题有关的数据来自于多个不同的操作型系统。
  • 这些系统内部数据的命名可能不同，数据格式也可能不同。把不同来源的数据存储到数据仓库之前，需要去除这些
    不一致。

• 非易失性、非异变性（Non-Volatile）
  • 数据仓库是分析数据的平台，而不是创造数据的平台。我们是通过数仓去分析数据中的规律，而不是去创造修改其中的规律。因此数据进入数据仓库后，它便稳定且不会改变。
  • 数据仓库的数据反映的是一段相当长的时间内历史数据的内容，数据仓库的用户对数据的操作大多是数据查询或比较复杂的挖掘，一旦数据进入数据仓库以后，一般情况下被较长时间保留。
  • 数据仓库中一般有大量的查询操作，但修改和删除操作很少。
• 时变性（Time-Variant）
  • 数据仓库包含各种粒度的历史数据，数据可能与某个特定日期、星期、月份、季度或者年份有关。
  • 当业务变化后会失去时效性。因此数据仓库的数据需要随着时间更新，以适应决策的需要。
  • 从这个角度讲，数据仓库建设是一个项目，更是一个过程。

1.4 数据仓库主流开发语言–SQL

1.4.1 数仓开发语言概述

• 数仓作为面向分析的数据平台，其主职工作就是对存储在其中的数据开展分析，那么如何读取数据分析呢？
• 理论上来说，任何一款编程语言只要具备读写数据、处理数据的能力，都可以用于数仓的开发。比如大家耳熟能详的C、java、Python等；
• 关键在于编程语言是否易学、好用、功能是否强大。遗憾的是上面所列出的C、Python等编程语言都需要一定的时间进行语法的学习，并且学习语法之后还需要结合分析的业务场景进行编码，跑通业务逻辑。
• 不管从学习成本还是开发效率来说，上述所说的编程语言都不是十分友好的。
• 在数据分析领域，不得不提的就是SQL编程语言，应该称之为分析领域主流开发语言。

1.4.2 SQL语言介绍

• 结构化查询语言（Structured Query Language）简称SQL，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理数据。
• SQL语言使我们有能力访问数据库，并且SQL是一种ANSI（美国国家标准化组织）的标准计算机语言，各大数据库厂商在生产数据库软件的时候，几乎都会去支持SQL的语法，以使得用户在使用软件时更加容易上手，以及在不同厂商软件之间进行切换时更加适应，因为大家的SQL语法都差不多。
• SQL语言功能很强，十分简洁，核心功能只用了9个动词。语法接近英语口语，所以，用户很容易学习和使用。

1.4.3 数仓与SQL

• 虽然SQL语言本身是针对数据库软件设计的，但是在数据仓库领域，尤其是大数据数仓领域，很多数仓软件都会去支持SQL语法；
• 原因在于一是用户学习SQL成本低，二是SQL语言对于数据分析真的十分友好，爱不释手。

1.4.4 结构化数据

• 结构化数据也称作行数据，是由二维表结构来逻辑表达和实现的数据，严格地遵循数据格式与长度规范，主要通过关系型数据库进行存储和管理。
• 与结构化数据相对的是不适于由数据库二维表来表现的非结构化数据，包括所有格式的办公文档、XML、HTML、各类报表、图片和音频、视频信息等。
• 通俗来说，结构化数据会有严格的行列对齐，便于解读与理解。

1.4.5 二维表结构

• 表由一个名字标识（例如“客户”或者“订单”），叫做表名。表包含带有数据的记录（行）。
• 下面的例子是一个名为 “Persons” 的表，包含三条记录（每一条对应一个人）和五个列（Id、姓、名、地址和城市）。
  

1.4.6 SQL语法分类

• SQL主要语法分为两个部分：数据定义语言 (DDL)和数据操纵语言 (DML) 。
  • DDL语法使我们有能力创建或删除表，以及数据库、索引等各种对象，但是不涉及表中具体数据操作：
  • CREATE DATABASE - 创建新数据库
  • CREATE TABLE - 创建新表
• DML语法是我们有能力针对表中的数据进行插入、更新、删除、查询操作：
  • SELECT - 从数据库表中获取数据
  • UPDATE - 更新数据库表中的数据
  • DELETE - 从数据库表中删除数据
  • INSERT - 向数据库表中插入数据

2. Apache Hive入门

2.1 Apache Hive概述

2.1.1 什么是Hive

• Apache Hive是一款建立在Hadoop之上的开源数据仓库系统，可以将存储在Hadoop文件中的结构化、半结构化数据文件映射为一张数据库表，基于表提供了一种类似SQL的查询模型，称为Hive查询语言（HQL），用于访问和分析存储在Hadoop文件中的大型数据集。
• Hive核心是将HQL转换为MapReduce程序，然后将程序提交到Hadoop群集执行。
• Hive由Facebook实现并开源。

2.1.2 为什么使用Hive

• 使用Hadoop MapReduce直接处理数据所面临的问题
  • 人员学习成本太高 需要掌握java语言
  • MapReduce实现复杂查询逻辑开发难度太大
• 使用Hive处理数据的好处
  • 操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）
  • 避免直接写MapReduce，减少开发人员的学习成本
  • 支持自定义函数，功能扩展很方便
  • 背靠Hadoop，擅长存储分析海量数据集

2.1.3 Hive和Hadoop关系

• 从功能来说，数据仓库软件，至少需要具备下述两种能力：
  • 存储数据的能力、分析数据的能力
• Apache Hive作为一款大数据时代的数据仓库软件，当然也具备上述两种能力。只不过Hive并不是自己实现了上述两种能力，而是借助Hadoop。
  • Hive利用HDFS存储数据，利用MapReduce查询分析数据。
• 这样突然发现Hive没啥用，不过是套壳Hadoop罢了。其实不然，Hive的最大的魅力在于用户专注于编写HQL，Hive帮您转换成为MapReduce程序完成对数据的分析。

2.2 场景设计：如何模拟实现Hive功能

• 在HDFS文件系统上有一个文件，路径为/data/china_user.txt；
• 需求：统计来自于上海年龄大于25岁的用户有多少个？
  

2.2.1 场景目的

• 重点理解下面两点：
  • Hive能将数据文件映射成为一张表，这个映射是指什么？
  • Hive软件本身到底承担了什么功能职责？

2.2.2 映射信息记录

• 映射在数学上称之为一种对应关系，比如y=x+1，对于每一个x的值都有与之对应的y的值。
• 在hive中能够写sql处理的前提是针对表，而不是针对文件，因此需要将文件和表之间的对应关系描述记录清楚。
• 映射信息专业的叫法称之为元数据信息（元数据是指用来描述数据的数据 metadata）。
  
• 具体来看，要记录的元数据信息包括：
  • 表对应着哪个文件（位置信息）
  • 表的列对应着文件哪一个字段（顺序信息）
  • 文件字段之间的分隔符是什么

2.3 SQL语法解析、编译

• 用户写完sql之后，hive需要针对sql进行语法校验，并且根据记录的元数据信息解读sql背后的含义，制定执行计划。
• 并且把执行计划转换成MapReduce程序来具体执行，把执行的结果封装返回给用户。

2.4 对Hive的理解

• Hive能将数据文件映射成为一张表，这个映射是指什么？
  • 文件和表之间的对应关系
• Hive软件本身到底承担了什么功能职责？
  • SQL语法解析编译成为MapReduce

2.5 最终效果

• 基于上述分析，最终要想模拟实现的Hive的功能，大致需要下图所示组件参与其中。
• 从中可以感受一下Hive承担了什么职责，当然，也可以把这个理解为Hive的架构图。
  

2.3 Apache Hive架构、组件

2.3.1 Hive 架构图

2.3.2 Hive组件

• 用户接口
  • 包括 CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)为shell命令行；Hive中的Thrift服务器允许外部客户端通过网络与Hive进行交互，类似于JDBC或ODBC协议。WebGUI是通过浏览器访问Hive。
• 元数据存储
  • 通常是存储在关系数据库如 mysql/derby中。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。
• Driver驱动程序，包括语法解析器、计划编译器、优化器、执行器
  • 完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有执行引擎调用执行。
• 执行引擎
  • Hive本身并不直接处理数据文件。而是通过执行引擎处理。当下Hive支持MapReduce、Tez、Spark3种执行引擎。

3. Apache Hive安装部署

3.1 Apache Hive元数据

3.1.1 什么是元数据

• 元数据（Metadata），又称中介数据、中继数据，为描述数据的数据（data about data），主要是描述数据属性（property）的信息，用来支持如指示存储位置、历史数据、资源查找、文件记录等功能。

3.1.2 Hive Metadata

• Hive Metadata即Hive的元数据。
• 包含用Hive创建的database、table、表的位置、类型、属性，字段顺序类型等元信息。
• 元数据存储在关系型数据库中。如hive内置的Derby、或者第三方如MySQL等。

3.1.3 Hive Metastore

• Metastore即元数据服务。Metastore服务的作用是管理metadata元数据，对外暴露服务地址，让各种客户端通过连接metastore服务，由metastore再去连接MySQL数据库来存取元数据。
• 有了metastore服务，就可以有多个客户端同时连接，而且这些客户端不需要知道MySQL数据库的用户名和密码，只需要连接metastore 服务即可。某种程度上也保证了hive元数据的安全。
  

3.1.4 metastore配置方式

• metastore服务配置有3种模式：内嵌模式、本地模式、远程模式。
• 区分3种配置方式的关键是弄清楚两个问题：
  • Metastore服务是否需要单独配置、单独启动？
  • Metadata是存储在内置的derby中，还是第三方RDBMS,比如MySQL。
    

3.1.5 metastore远程模式

• 在生产环境中，建议用远程模式来配置Hive Metastore。在这种情况下，其他依赖hive的软件都可以通过Metastore访问hive。由于还可以完全屏蔽数据库层，因此这也带来了更好的可管理性/安全性。
  

3.2 Apache Hive部署实战

3.2.1 安装前准备

• 由于Apache Hive是一款基于Hadoop的数据仓库软件，通常部署运行在Linux系统之上。因此不管使用何种方式配置Hive Metastore，必须要先保证服务器的基础环境正常，Hadoop集群健康可用。
• 服务器基础环境
  • 集群时间同步、防火墙关闭、主机Host映射、免密登录、JDK安装
• Hadoop集群健康可用
  • 启动Hive之前必须先启动Hadoop集群。特别要注意，需等待HDFS安全模式关闭之后再启动运行Hive。
  • Hive不是分布式安装运行的软件，其分布式的特性主要借由Hadoop完成。包括分布式存储、分布式计算。

3.2.2 Hadoop与Hive整合

• 因为Hive需要把数据存储在HDFS上，并且通过MapReduce作为执行引擎处理数据；
• 因此需要在Hadoop中添加相关配置属性，以满足Hive在Hadoop上运行。
• 修改Hadoop中core-site.xml，并且Hadoop集群同步配置文件，重启生效。

<!-- 整合hive -->
<property>
	<name>hadoop.proxyuser.root.hosts</name>
	<value>*</value>
</property>
<property>
	<name>hadoop.proxyuser.root.groups</name>
	<value>*</value>
</property>

3.2.3 Step1：MySQL安装

• MySQL只需要在一台机器安装并且需要授权远程访问
• 卸载Centos7自带的mariadb

[root@node3 ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.64-1.el7.x86_64

[root@node3 ~]# rpm -e mariadb-libs-5.5.64-1.el7.x86_64 --nodeps
[root@node3 ~]# rpm -qa|grep mariadb                            
[root@node3 ~]# 

• 安装mysql

mkdir /export/software/mysql

#上传mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar 到上述文件夹下  解压
tar xvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

#执行安装
yum -y install libaio

[root@node3 mysql]# rpm -ivh mysql-community-common-5.7.29-1.el7.x86_64.rpm mysql-community-libs-5.7.29-1.el7.x86_64.rpm mysql-community-client-5.7.29-1.el7.x86_64.rpm mysql-community-server-5.7.29-1.el7.x86_64.rpm 

warning: mysql-community-common-5.7.29-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-common-5.7.29-1.e################################# [ 25%]
   2:mysql-community-libs-5.7.29-1.el7################################# [ 50%]
   3:mysql-community-client-5.7.29-1.e################################# [ 75%]
   4:mysql-community-server-5.7.29-1.e################                  ( 49%)

• mysql初始化设置

#初始化
mysqld --initialize

#更改所属组
chown mysql:mysql /var/lib/mysql -R

#启动mysql
systemctl start mysqld.service

#查看生成的临时root密码
cat /var/log/mysqld.log

[Note] A temporary password is generated for root@localhost: o+TU+KDOm004

• 修改root密码 授权远程访问 设置开机自启动

[root@node2 ~]# mysql -u root -p
Enter password:     #这里输入在日志中生成的临时密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.29

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 


#更新root密码  设置为hadoop
mysql> alter user user() identified by "hadoop";
Query OK, 0 rows affected (0.00 sec)


#授权
mysql> use mysql;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'hadoop' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES; 

#mysql的启动和关闭 状态查看 （这几个命令必须记住）
systemctl stop mysqld
systemctl status mysqld
systemctl start mysqld

#建议设置为开机自启动服务
[root@node2 ~]# systemctl enable  mysqld                             
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

#查看是否已经设置自启动成功
[root@node2 ~]# systemctl list-unit-files | grep mysqld
mysqld.service                                enabled 

3.2.4 Step2：上传解压Hive安装包（node1安装即可）

# 上传安装包 解压
tar zxvf apache-hive-3.1.2-bin.tar.gz
mv apache-hive-3.1.2-bin/ hive
# 解决Hive与Hadoop之间guava版本差异
cd /export/server/apache-hive-3.1.2-bin/
rm -rf lib/guava-19.0.jar
cp /export/server/hadoop-3.3.0/share/hadoop/common/lib/guava-27.0-jre.jar \
./lib/

3.2.5 Step3：修改hive-env.sh

cd /export/server/apache-hive-3.1.2-bin/conf
mv hive-env.sh.template hive-env.sh

vim hive-env.sh
export HADOOP_HOME=/export/server/hadoop-3.3.0
export HIVE_CONF_DIR=/export/server/apache-hive-3.1.2-bin/conf
export HIVE_AUX_JARS_PATH=/export/server/apache-hive-3.1.2-bin/lib

3.2.6 Step4：新增hive-site.xml

<configuration>
	<!-- 存储元数据mysql相关配置 -->
	<property>
		<name>javax.jdo.option.ConnectionURL</name>
		<value>jdbc:mysql://node1:3306/hive3?createDatabaseIfNotExist=true&amp;u
		seSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
	</property>
	<property>
		<name>javax.jdo.option.ConnectionDriverName</name>
		<value>com.mysql.jdbc.Driver</value>
	</property>
	<property>
		<name>javax.jdo.option.ConnectionUserName</name>
		<value>root</value>
	</property>
	<property>
		<name>javax.jdo.option.ConnectionPassword</name>
		<value>hadoop</value>
	</property>
	<!-- H2S运行绑定host -->
	<property>
		<name>hive.server2.thrift.bind.host</name>
		<value>node1</value>
	</property>
	<!-- 远程模式部署metastore metastore地址 -->
	<property>
		<name>hive.metastore.uris</name>
		<value>thrift://node1:9083</value>
	</property>
	<!-- 关闭元数据存储授权 -->
	<property>
		<name>hive.metastore.event.db.notification.api.auth</name>
		<value>false</value>
	</property>
</configuration>

3.2.7 Step5：添加驱动、初始化

• 上传MySQL JDBC驱动到Hive安装包lib路径下 mysql-connector-java-5.1.32.jar
• 初始化Hive的元数据。

cd /export/server/apache-hive-3.1.2-bin/
bin/schematool -initSchema -dbType mysql -verbos
#初始化成功会在mysql中创建74张表

3.2.8 metastore服务启动方式

• 前台启动，进程会一直占据终端，ctrl + c结束进程，服务关闭。
  可以根据需求添加参数开启debug日志，获取详细日志信息，便于排错。

#前台启动
/export/server/apache-hive-3.1.2-bin/bin/hive --service metastore
#前台启动开启debug日志
/export/server/apache-hive-3.1.2-bin/bin/hive --service metastore --hiveconf 
hive.root.logger=DEBUG,console 
#前台启动关闭方式 ctrl+c结束进程

• 后台启动，输出日志信息在/root目录下nohup.out

nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore &
#后台挂起启动 结束进程
使用jps查看进程 使用kill -9 杀死进程
#nohup 命令，在默认情况下（非重定向时），会输出一个名叫 nohup.out 的文件到当前目录下

3.3 Apache Hive客户端使用

3.3.1 Hive自带客户端

• bin/hive、bin/beeline
  
• Hive发展至今，总共历经了两代客户端工具。
• 第一代客户端（deprecated不推荐使用）：$HIVE_HOME/bin/hive, 是一个 shellUtil。主要功能：一是可用于以交互或批处理模式运行Hive查询；二是用于Hive相关服务的启动，比如metastore服务。
• 第二代客户端（recommended 推荐使用）：$HIVE_HOME/bin/beeline，是一个JDBC客户端，是官方强烈推荐使用的Hive命令行工具，和第一代客户端相比，性能加强安全性提高。

3.3.2 HiveServer2服务介绍

• 远程模式下beeline通过 Thrift 连接到单独的HiveServer2服务上，这也是官方推荐在生产环境中使用的模式。
• HiveServer2支持多客户端的并发和身份认证，旨在为开放API客户端如JDBC、ODBC提供更好的支持。

3.3.3 关系梳理

• HiveServer2通过Metastore服务读写元数据。所以在远程模式下，启动HiveServer2之前必须先首先启动metastore服务。
• 特别注意：远程模式下，Beeline客户端只能通过HiveServer2服务访问Hive。而bin/hive是通过Metastore服务访问的。具体关系如下：
  

3.3.4 bin/beeline客户端使用

• 在hive安装的服务器上，首先启动metastore服务，然后启动hiveserver2服务。

#先启动metastore服务 然后启动hiveserver2服务
nohup /export/servers/hive/bin/hive --service metastore &
nohup /export/servers/hive/bin/hive --service hiveserver2 &

• 在node3上使用beeline客户端进行连接访问。需要注意hiveserver2服务启动之后需要稍等一会才可以对外提供服务。
• Beeline是JDBC的客户端，通过JDBC协议和Hiveserver2服务进行通信，协议的地址是：jdbc:hive2://node1:10000

[root@node3 ~]# /export/server/hive/bin/beeline 
Beeline version 3.1.2 by Apache Hive
beeline> ! connect jdbc:hive2://node1:10000
Connecting to jdbc:hive2://node1:10000
Enter username for jdbc:hive2://node1:10000: root
Enter password for jdbc:hive2://node1:10000: 
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://node1:10000> 

3.3.5 beeline连接配置说明

3.3.6 Hive可视化客户端

• DataGrip、Dbeaver、SQuirrel SQL Client等
  • 可以在Windows、MAC平台中通过JDBC连接HiveServer2的图形界面工具；
  • 这类工具往往专门针对SQL类软件进行开发优化、页面美观大方，操作简洁，更重要的是SQL编辑环境优雅；
  • SQL语法智能提示补全、关键字高亮、查询结果智能显示、按钮操作大于命令操作；
    
• DataGrip
  • DataGrip是由JetBrains公司推出的数据库管理软件，DataGrip支持几乎所有主流的关系数据库产品，如DB2、Derby、MySQL、Oracle、SQL Server等，也支持几乎所有主流的大数据生态圈SQL软件，并且提供了简单易用的界面，开发者上手几乎不会遇到任何困难。
• 使用DataGrip软件远程连接Hiveserver2服务（DataGrip 使用教程）
  • step1：windows创建工程文件夹
    
  • step2：DataGrip中创建新Project
    
  • step3：关联本地工程文件夹
    
  • step4：DataGrip连接Hive
    
  • step5：配置Hive JDBC连接驱动
    
    
  • step6：返回，配置Hiveserver2服务连接信息
    

4. Hive SQL语言：DDL建库、建表

4.1 Hive SQL之数据库与建库

4.1.1 Hive数据模型总览

4.1.2 SQL中DDL语法的作用

• 数据定义语言 (Data Definition Language, DDL)，是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database、table等。
• DDL核心语法由CREATE、ALTER与DROP三个所组成。DDL并不涉及表内部数据的操作。

4.1.3 Hive中DDL语法的使用

• Hive SQL（HQL）与标准SQL的语法大同小异，基本相通；
• 基于Hive的设计、使用特点，HQL中create语法（尤其create table）将是学习掌握Hive DDL语法的重中之重。
  • 建表是否成功直接影响数据文件是否映射成功，进而影响后续是否可以基于SQL分析数据。通俗点说，没有表，表没有数据，你用Hive分析什么呢？

4.1.4 数据库database

• 在Hive中，默认的数据库叫做default，存储数据位置位于HDFS的/user/hive/warehouse下。
• 用户自己创建的数据库存储位置是/user/hive/warehouse/database_name.db下。

4.1.5 create database

• create database用于创建新的数据库
  • COMMENT：数据库的注释说明语句
  • LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouse/dbname.db
  • WITH DBPROPERTIES：用于指定一些数据库的属性配置。

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

• 例子：创建数据库itcast
  • 注意：如果需要使用location指定路径的时候，最好指向的是一个新创建的空文件夹。

create database if not exists itcast
comment "this is my first db"
with dbproperties ('createdBy'='Allen');

4.2 Hive SQL之表与建表

4.2.1 表Table

• 一个数据库通常包含一个或多个表。每个表由一个名字标识（例如“客户”或者“订单”）。
• 表包含带有数据的记录（行）。
  

4.2.2 建表语法树（基础）

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
(col_name data_type [COMMENT col_comment], ... )
[COMMENT table_comment]
[ROW FORMAT DELIMITED …];

• 最低限度必须包括的语法为：
  • CREATE TABLE table_name (col_name data_type);

4.2.3 数据类型

• Hive数据类型指的是表中列的字段类型；
  • 整体分为两类：原生数据类型（primitive data type）和复杂数据类型（complex data type）。
  • 最常用的数据类型是字符串String和数字类型Int。
    

4.2.4 分隔符指定语法

• ROW FORMAT DELIMITED语法用于指定字段之间等相关的分隔符，这样Hive才能正确的读取解析数据。
• 或者说只有分隔符指定正确，解析数据成功，我们才能在表中看到数据。
• LazySimpleSerDe是Hive默认的，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。
• 在建表的时候可以根据数据的特点灵活搭配使用。
  

4.3 Hive默认分隔符

• Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符；
• 默认的分割符是’\001’，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的。
  
• 在vim编辑器中，连续按下Ctrl+v/Ctrl+a即可输入’\001’ ，显示^A

4.4 Hive建表基础语法练习

4.4.1 数据类型、分隔符练习

• 文件archer.txt中记录了手游《王者荣耀》射手的相关信息，包括生命、物防、物攻等属性信息，其中字段之间分隔符为制表符\t,要求在Hive中建表映射成功该文件。

• 数据文件

  • 字段含义：id、name（英雄名称）、hp_max（最大生命）、mp_max（最大法力）、attack_max（最高物攻）、defense_max（最大物防）、attack_range（攻击范围）、role_main（主要定位）、role_assist（次要定位）。
  • 分析一下：字段都是基本类型，字段的顺序需要注意一下。
  • 字段之间的分隔符是制表符，需要使用row format语法进行指定。
    

• 建表语句

  --创建数据库并切换使用
  create database if not exists honor;
  use honor;
  --ddl create table
  create table t_archer(
  id int comment "ID",
  name string comment "英雄名称",
  hp_max int comment "最大生命",
  mp_max int comment "最大法力",
  attack_max int comment "最高物攻",
  defense_max int comment "最大物防",
  attack_range string comment "攻击范围",
  role_main string comment "主要定位",
  role_assist string comment "次要定位"
  ) comment "王者荣耀射手信息"
  row format delimited
  fields terminated by "\t";
  

  • 建表成功之后，在Hive的默认存储路径下就生成了表对应的文件夹；
  • 把archer.txt文件上传到对应的表文件夹下。

  #在node机器上进行操作
  cd ~
  mkdir hivedata
  cd hivedata/
  #把文件从课程资料中首先上传到node1 linux系统上
  #执行命令把文件上传到HDFS表所对应的目录下
  hadoop fs -put archer.txt /user/hive/warehouse/honor.db/t_archer
  

• 结果验证

  • 执行查询操作，可以看出数据已经映射成功。
  • 核心语法：row format delimited fields terminated by 指定字段之间的分隔符。
    

4.4.2 默认分隔符使用

• 文件team_ace_player.txt中记录了手游《王者荣耀》主要战队内最受欢迎的王牌选手信息，字段之间使用的是\001作为分隔符,要求在Hive中建表映射成功该文件。

• 数据文件

  • 字段：id、team_name（战队名称）、ace_player_name（王牌选手名字）
  • 分析一下：数据都是原生数据类型，且字段之间分隔符是\001，因此在建表的时候可以省去row format语句，因为hive默认的分隔符就是\001。
    

• 建表语句

  create table t_team_ace_player (
  	id int,
  	team_name string,
  	ace_player_name string
  );
  

  • 建表成功后，把team_ace_player.txt文件上传到对应的表文件夹下。

  #在node机器上进行操作
  cd ~
  cd hivedata/
  #把文件从课程资料中首先上传到node1 linux系统上
  #执行命令把文件上传到HDFS表所对应的目录下
  hadoop fs -put team_ace_player.txt 
  /user/hive/warehouse/honor.db/t_team_ace_player
  

• 结果验证

  • 执行查询操作，可以看出数据已经映射成功。
  • 字段以\001分隔建表时很方便，那么采集、清洗数据时优先考虑\001分隔符
    

4.5 Hive Show语法

• show语法功能
  • Show相关的语句可以帮助用户查询相关信息。
  • 比如我们最常使用的查询当前数据库下有哪些表 show tables.
• 常用show语句

--1、显示所有数据库 SCHEMAS和DATABASES的用法 功能一样
show databases;
show schemas;
--2、显示当前数据库所有表
show tables;
SHOW TABLES [IN database_name]; --指定某个数据库
--3、查询显示一张表的元数据信息
desc formatted t_team_ace_player;