1. Hive的基本概念
2. Hive的安装
3. MySQL安装
4. Hive的数据类型
5. DDL定义和操作数据
6. DML数据操作
7. 查询
链接：https://pan.baidu.com/s/1b9dzXpDGAFd2PIpyYOJrKA 提取码：5sdq
1.1 什么是Hive
（1） hive 简介
Hive：由 Facebook 开源用于解决海量问题结构化日志的数据统计工具。
Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化数据文件映射为表，并提供类 SQL（HQL）查询功能。
（2） Hive 本质：将 HQL 转化成 MapReduce 程序
(3)主要用途:直接使用离线数据分析MapReduce开发效率更高
1.2 Hive的优缺点：
优点：
(1)操作接口采用类型SQL语法，提供快速发展的能力(简单易用)。
(2)避免写作MapReduce，降低开发人员的学习成本。
（3）Hive因此，执行延迟相对较高，Hive常用于对实时性要求较低的数据分析场合。
（4）Hive优点是处理大数据，因为处理小数据没有优势Hive执行延迟相对较高。
（5）Hive支持用户自定义函数，用户可以根据自己的需要实现自己的函数。
缺点：
1）Hive的HQL表达能力有限
(1)迭代算法无法表达
(2)不擅长数据挖掘，因为MapReduce由于数据处理过程的限制，高效算法无法实现。SparkFlink
2）Hive效率相对较低
（1）Hive自动生成的MapReduce通常情况下，作业不够智能
（2）Hive调整难度大，粒度粗
1.3 Hive架构原理
1）用户接口：Client
CLI（command-lineinterface）即Shell命令行、JDBC/ODBC(java访问hive)、WEBUI(浏览器访问hive）
2）元数据：Metastore
元数据包括：表名和表所属数据库（默认为default）、表的所有者、列/分区字段、表的类型(是否为外表)、表的数据目录等。
默认存储在自带中derby推荐在数据库中使用MySQL存储Metastore
3）Hadoop
使用HDFS进行存储，使用MapReduce进行计算。
4）驱动器：Driver
（1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。这一步做了准备工作。
（2）编译器（Physical Plan）：将AST编译生成逻辑执行计划，也就是将SQL编译成MR程序。
（3）优化器（Query Optimizer）：对MR逻辑执行计划进行优化。
（4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。
Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。
1.4 Hive和数据库比较
由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，因此很容易将 Hive 理解为数据库。其实从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处。下列将从多个方面来阐述 Hive 和数据库的差异。数据库可以用在 Online 的应用中，但是Hive 是为数据仓库而设计的，清楚这一点，有助于从应用角度理解 Hive 的特性。
1.4.1 查询语言
由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。
1.4.2 数据更新
由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多修改的少。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。
而数据库中的数据通常是需要经常进行修改的。
1.4.3 执行延迟
Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce框架。由于MapReduce 本身具有较高的延迟，因此在利用MapReduce 执行Hive查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出优势。
1.4.4 数据规模
由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。
1）把apache-hive-3.1.2-bin.tar.gz上传到linux的/opt/software目录下
2）解压apache-hive-3.1.2-bin.tar.gz到/opt/module/目录下面
tar -zxvf /opt/software/apache-hive-3.1.2-bin.tar.gz -C /opt/module/
3）修改apache-hive-3.1.2-bin.tar.gz的名称为hive
mv /opt/module/apache-hive-3.1.2-bin/ /opt/module/hive
4）修改/etc/profile.d/my_env.sh，添加环境变量
sudo vim /etc/profile 输入： export HIVE_HOME=/opt/module/hive export PATH=$PATH:$HIVE_HOME/bin 退出！ source /etc/profile
5）初始化数据库
bin/schematool -dbType derby -initSchema
bin/hive
7）查看hive日志信息
# 如果hive启动报错，可以去查看它的日志 cat /tmp/[用户名]/hive.log
注意：Hive 默认使用的元数据库为 derby，开启 Hive 之后就会占用元数据库，且不与
3.1 安装包准备
1）卸载自带的Mysql-libs（如果之前安装过mysql，要全都卸载掉）
rpm -qa|grep mariadb sudo rpm -e --nodeps mariadb-libs
2）将安装包和JDBC驱动上传到/opt/software，共计6个（下列这五个一解压mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar就能得到，可以查看一下）
mysql-community-common-5.7.29-1.el7.x86_64.rpm
mysql-community-libs-5.7.29-1.el7.x86_64.rpm
mysql-community-libs-compat-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
mysql-connector-java-5.1.48.jar
3.2 安装MySql
快速安装命令（进入到software目录下执行，确保无其他.rpm文件）
ls *.rpm | xargs -n1 sudo rpm -ivh
或者如下操作一个一个安装：并作出相应rpm简单解释
1）安装mysql依赖
sudo rpm -ivh 01_mysql-community-common-5.7.29-1.el7.x86_64.rpm sudo rpm -ivh 02_mysql-community-libs-5.7.29-1.el7.x86_64.rpm sudo rpm -ivh 03_mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpm
2）安装mysql-client
sudo rpm -ivh 04_mysql-community-client-5.7.29-1.el7.x86_64.rpm
3）安装mysql-server
sudo rpm -ivh 05_mysql-community-server-5.7.29-1.el7.x86_64.rpm
如报错，使用yum安装libail
sudo yum install libaio
4）初始化并启动mysql
sudo -u mysql mysqld --initialize sudo systemctl start mysqld
5）查看mysql密码
sudo cat /var/log/mysqld.log | grep password
3.3 配置MySql
配置只要是root用户+密码，在任何主机上都能登录MySQL数据库。
1）用刚刚查到的密码进入mysql（如果报错，给密码加单引号）
mysql -uroot -p’password’
2）设置简单好记的密码
set password=password("000000");
3）进入msyql库：use mysql
4）查询user表
select user, host from user;
5）修改user表，把Host表内容修改为%
update user set host="%" where user="root";
8）刷新：flush privileges;
9）退出：quit;
简单小总结：Hive中出错去：/tmp/[用户名]/hive.log，如果MySQL出错了去：/var/log/mysql.log
3.4 Hive元数据配置到MySql
3.4.1 将MySQL的JDBC驱动拷贝到Hive的lib目录下
cp /opt/software/mysql-connector-java-5.1.48.jar $HIVE_HOME/lib
3.4.2 配置Metastore到MySql
vim $HIVE_HOME/conf/hive-site.xml
添加一下内容
<?xml version="1.0"?> <?xml-stylesheet type="text/xsl" href="configuration.xsl"?> <configuration> <!-- jdbc 连接的 URL --> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://hadoop100:3306/hive?useSSL=false</value> </property> <!-- jdbc 连接的 Driver--> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> </property> <!-- jdbc 连接的 username--> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value> </property> <!-- jdbc 连接的 password --> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>000000</value> </property> <!-- Hive 元数据存储版本的验证 --> <property> <name>hive.metastore.schema.verification</name> <value>false</value> </property> <!--元数据存储授权--> <property> <name>hive.metastore.event.db.notification.api.auth</name> <value>false</value> </property> <!-- Hive 默认在 HDFS 的工作目录 --> <property> <name>hive.metastore.warehouse.dir</name> <value>/user/hive/warehouse</value> </property> </configuration>
mysql -uroot -p000000
3.4.4 新建 Hive 元数据库
create database metastore; quit;
schematool -initSchema -dbType mysql -verbose
hive> show databases; hive> show tables; hive> create table test (id int); hive> insert into test values(1); hive> select * from test;
4.1 Hive基本的数据类型
4.2 集合数据类型
5.1 数据库操作
# 其中if not表示创建XXX表的时候如果已经存在就不执行该命令 # location表示将库创建到指定的目录下边 create database if not exists XXX location '/XXX'; # 查询所有数据库 show databases; # like参数代表查找以XXX开头的 show databases like 'XXX*'; # 查询当前数据库的详细信息 desc database XXX; # 切换数据库 use XXX; # 修改数据库 alter database XXX set dfproperties("XXX":"XXX") # 删除数据库,只能删除空的库 drop database XXX; # 如果不为空但是想要删除使用 drop database XXX cascade;
5.2 建表语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path] [TBLPROPERTIES (property_name=property_value, ...)] [AS select_statement]
5.2.1 字段解释说明
（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
（3）COMMENT：为表和列添加注释。
（4）PARTITIONED BY创建分区表
（5）CLUSTERED BY创建分桶表
（6）SORTED BY不常用，对桶中的一个或多个列另外排序
（7）ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称， hive使用Serde进行行对象的序列与反序列化。
（8）STORED AS指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
（9）LOCATION ：指定表在HDFS上的存储位置。
（10）AS：后跟查询语句，根据查询结果创建表。
（11）LIKE允许用户复制现有的表结构，但是不复制数据。
5.2.2 管理表
1）理论
默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。
2）案例实操
（1）普通创建表
create table if not exists student2( id int, name string ) row format delimited fields terminated by '\t' stored as textfile location '/user/hive/warehouse/student2';
（2）根据查询结果创建表（查询的结果会添加到新创建的表中）
create table if not exists student3 as select id, name from student;
（3）根据已经存在的表结构创建表
create table if not exists student4 like student;
（4）查询表的类型
desc formatted student2; Table Type: MANAGED_TABLE # 这个类型就是内部表
5.2.3 外部表
因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。
2）管理表和外部表的使用场景
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。
3）案例实操
分别创建部门和员工外部表，并向表中导入数据。
（1）上传数据到HDFS
（2）建表语句，创建外部表
hive (default)> create external table stu_external2( id int, name string) row format delimited fields terminated by '\t' location '/student';
（3）查看创建的表
hive (default)> select * from stu_external; OK stu_external.id stu_external.name 1001 lisi 1002 wangwu 1003 zhaoliu
（4）查看表格式化数据
hive (default)> desc formatted dept; Table Type: EXTERNAL_TABLE
（5）删除外部表
hive (default)> drop table stu_external;
外部表删除后，hdfs中的数据还在，但是metadata中stu_external的元数据已被删除
5.2.4 管理表与外部表的互相转换
（1）查询表的类型
hive (default)> desc formatted student2; Table Type: MANAGED_TABLE
（2）修改内部表student2为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
（3）查询表的类型
hive (default)> desc formatted student2; Table Type: EXTERNAL_TABLE
（4）修改外部表student2为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
（5）查询表的类型
注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！
6.1 数据导入
load data [local] inpath '数据的 path' [overwrite] into table student [partition (partcol1=val1,…)]; （1）load data:表示加载数据 （2）local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表 （3）inpath:表示加载数据的路径 （4）overwrite:表示覆盖表中已有数据，否则表示追加 （5）into table:表示加载到哪张表 （6）student:表示具体的表 （7）partition:表示上传到指定分区
6.1.2 实操案例 （0）创建一张表
create table student(id string, name string) row format delimited fields terminated by '\t';
（1）加载本地文件到 hive
load data local inpath '/opt/module/hive/datas/student.txt' into table default.student;
（2）加载 HDFS 文件到 hive 中
# 上传文件到 HDFS hadoop fs -put /opt/module/hive/data/student.txt /user/XXX/hive; # 加载 HDFS 上数据 load data inpath '/user/XXX/hive/student.txt' into table default.student;
发现从HDFS上加载数据到Hive要比从本地往上传快很多。 （3）加载数据覆盖表中已有的数据
# 上传文件到 HDFS hadoop fs -put /opt/module/data/student.txt /user/XXX/hive; # 加载数据覆盖表中已有的数据 load data inpath '/user/XXX/hive/student.txt' overwrite into table default.student;
6.2 通过查询语句向表中查数据（Insert）
6.2.1 从一张表的查询结果直接写到另一张表上
# 从student这张表上的查询数据再插入到student这个表上，打印出来结果是两份student的数据 insert into student select * from student; # 从student这张表上的查询数据再插入到student这个表上，overwriter会覆盖原来表的内容 insert overwrite table student select * from student;
6.2.2 在建表的时候，从一个表中查数据并且建到另一个新表上
create table if not exists student5 as select id,name from student;
6.2.3 创建表时通过 Location 指定加载数据路径
# 上传数据到 hdfs 上 hadoop fs -mkdir /student; hadoop fs -put /opt/module/datas/student.txt /student; # 创建表，并指定在 hdfs 上的位置 create external table if not exists student5(id int, name string) row format delimited fields terminated by '\t' location '/student; # 查询数据 select * from student5;
6.3 数据导出
# 将hdfs上的表通过Hive导下来 # 其中添加row format是为了导出来的数据是以‘，’分割的 insert overwrite local directory '/opt/module/hive/student' row format delimited fields terminated by ',' select * from student; # 导出到HDFS上的其他路径的话，去掉local参数就可以 insert overwrite directory '/student' row format delimited fields terminated by ',' select * from student;
6.3.1 通过hadoop命令拉下来
hadoop fs -get /user/hive/warehouse/student/student.txt /opt/module/data/export/student3.txt;
6.3.2 通过Shell命令导出
bin/hive -e 'select * from default.student;' >/opt/module/hive/data/export/student4.txt;
6.4 清空表
# 只能清空内部表 truncate table student;
7.1 基本查询
（0）原始数据 dept.txt: 10 ACCOUNTING 1700 20 RESEARCH 1800 30 SALES 1900 40 OPERATIONS 1700 emp.txt： 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 （1）创建部门表 create table if not exists dept( deptno int, dname string, loc int ) row format delimited fields terminated by '\t'; （2）创建员工表 create table if not exists emp( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int) row format delimited fields terminated by '\t'; （3）导入数据 load data local inpath '/opt/module/datas/dept.txt' into table dept; load data local inpath '/opt/module/datas/emp.txt' into table emp;
7.1.1 全表查询
select * from emp;
7.1.2 特定列查询
select empno, ename from emp; # 也可以为列取别名，取别名的时候，用不用AS都可以。 select ename AS name, deptno dn from emp;
注意SQL对大小写不敏感，SQL尽量不要写Tab缩进。关键字不能分行写。
7.1.3 算术运算符
# 实现所有源共的薪水 +1 显示 select ename sal + 1000 from emp;
7.1.4 常用函数
# 求总行数（count） hive (default)> select count(*) from emp; # 求工资的最大值（max） hive (default)> select max(sal) from emp; # 求工资的最小值（min） hive (default)> select min(sal) from emp; # 求工资的总和（sum） hive (default)> select sum(sal) from emp; # 求工资的平均值（avg） hive (default)> select avg(sal) from emp;
7.1.5 Limit语句
# 限制输出的行数 select * from emp limit 2;
7.1.6 where语句
# 查看工资大于1000的 select * from emp where sal > 1000; # 查询出薪水等于 5000 的所有员工 select * from emp where sal =5000; # 查询工资在 500 到 1000 的员工信息 select * from emp where sal between 500 and 1000; # 查询 comm 为空的所有员工信息 select * from emp where comm is null; # 查询工资是 1500 或 5000 的员工信息 select * from emp where sal IN (1500, 5000); # 查找名字以 A 开头的员工信息 select * from emp where ename LIKE 'A%'; # 查找名字中第二个字母为 A 的员工信息 select * from emp where ename LIKE '_A%'; # 查找名字中带有 A 的员工信息 select * from emp where ename RLIKE '[A]';
7.1.7 逻辑运算符
# 查询薪水大于 1000，部门是 30 select * from emp where sal>1000 and deptno=30; # 查询薪水大于 1000，或者部门是 30 select * from emp where sal>1000 or deptno=30; # 查询除了 20 部门和 30 部门以外的员工信息 select * from emp where deptno not IN(30, 20)
7.2 聚合函数Group By
GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
# 计算 emp 表每个部门的平均工资 select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno; # 计算 emp 每个部门中每个岗位的最高薪水 select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;
7.2.1 Having语句
（1）where 后面不能写分组函数，而 having 后面可以使用分组函数。
（2）having 只用于 group by 分组统计语句。
# 求每个部门的平均薪水大于 2000 的部门 # 求每个部门的平均工资 select deptno, avg(sal) from emp group by deptno; # 求每个部门的平均薪水大于 2000 的部门 select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000; # 上述语句等同于where： select deptno, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno)t1 where avg_sal > 2000; # 建议还是使用having好了！
注意：where后边不能写聚合函数！！！
标签： 2sdq固态继电器
