实训笔记8.21

8.21笔记

一、Hive数据仓库技术的基本概念和组成

Hive数据仓库其实本质上就是Hadoop技术框架的一个“客户端”，Hive数据仓库可以用来进行海量数据的存储和海量数据的计算（海量数据主要指的就是结构化数据），Hive存储的数据底层是基于HDFS存放的（Hive只是以二维表格的形式进行数据的组织，数据默认都是在HDFS的/user/hive/warehouse）,Hive进行数据计算采用了一种类SQL语句–HQL语句来进行的（HQL语言底层默认会翻译成为MapReduce程序进行运行），Hive底层翻译的分布式计算程序（MR、Spark、Tez）默认需要借助Hadoop中YARN（Mesos也是apache开源的一个分布式资源调度系统）进行资源的调度。

1.1 Hive的组成架构

1.1.1 Hive的客户端

Hive编写HQL语句的一个客户端

（1）Hive的命令行客户端 hive命令

hive 进入的一个交互式命令行窗口（repl）,进入一个窗口，在这个窗口可以循环的编写多条指令执行，每一次执行只需要敲回车

hive -e "HQL语句;HQL语句"

不进入repl，也可以执行HQL语句的相关命令，一般使用在单条HQL语句执行

hive -f xxx.sql --hiveconf key=value --hiveconf key=value .... --hivevar key=value

不进入repl，也可以执行多条HQL语句，多条HQL语句必须放到一个SQL文件中，并且可以向SQL文件传递动态参数

只需要把Hadoop的HDFS、YARN启动即可，不需要启动任何Hive相关的服务弊端：命令行客户端只能在Hive的安装节点上使用

（2）Hive的JDBC的客户端（Java API）

Hive的命令行客户端只能使用在hive的安装节点上，远程操作Hive，无法使用命令行客户端完成，此时使用Hive的JDBC客户端来完成

JDBC客户端如果要使用，必须启动Hive的一个远程连接操作服务hiveserver2，和hiveserver2服务默认会启动一个10000端口 hive-site.xml文件中做配置，还需要在Hadoop的core-site.xml、hdfs-site.xml文件中放行hive服务用户的权限 url: jdbc:hive2://ip:port username password

hive的JDBC客户端又有多种使用方式

1. 编写JDBC代码来进行操作
2. 使用DBeaver工具来进行操作
3. 使用阿里云开发的chat2db工具来进行操作

（3）Hive的web客户端

【注意】hiveserver2的启动和关闭必须复杂的，通过自定义shell脚本实现hiveserver2的快速启动和关闭

1.1.2 Hive的驱动程序

Hive底层会把类SQL语句-HQL语句转换成为MR程序执行，核心就是Hive的驱动程序

1. 解析器
2. 编译器
3. 优化器
4. 执行器

1.1.3 Hive的元数据库

Hive只是以类似于数据表的形式进行数据的存放，但是Hive本身不存储任务的表结构和表数据，表结构全部都在Hive的一个核心–元数据库metastore存放着，每一个数据表底层在HDFS存放的数据都在元数据库中做了地址映射。

默认Hive的元数据库在derby数据库存放，但是derby数据库存放hive元数据存在问题–多客户端无法同时操作的问题

指定hive的元数据库存放到其他的RDBMS关系型数据库中，MySQL

二、Hive的HQL语句操作

Hive中提供了一种类似于SQL的HQL语句进行数据库、数据表的管理以及各项表数据的操作，其中格局操作类型不同，HQL语句也分为DDL、DML、DQL

2.1 DDL语言

用来管理数据库、数据表的工具（创建、删除、修改、查询）

2.1.1 数据库的管理

1. 创建数据库：

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

2. 查询数据库

   show databases;
   desc database database_name
   desc database extended datanase_name
   

3. 修改数据库

   只能修改数据库的dbproperties、所属用户、在hdfs上存储位置

4. 删除数据库

   特点：如果数据库下有数据表，无法直接删除数据库

   drop database if exists database_name [cascade]

2.1.2 数据表的管理

1. 创建数据表

   1. CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name      [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]  [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, ...)] 
      

   2. 数据表的分类

      1. 内部表/管理表：删除表，表数据在HDFS上也会同步删除

      2. 外部表：删除表，表数据在HDFS上依然存在

      3. 分区表：将表数据以指定的分区字段，将数据存储到表目录的不同文件夹下

         【注意】分区表需要指定分区字段，分区字段不能是表字段

      4. 分桶表：将表数据最终记录在文件当中，默认情况下只会存在一个文件记录，分桶表可以指定表数据以指定的字段按照hash分区机制将数据存储到不同的文件中

         【注意】分桶表的字段一定是表字段

         1、Hive中一张表一定是内部表或者是外部表的一种

         2、分区表和分桶表是在内外表的基础之上的衍生表格

         3、表数据文件底层的存储机制

   3. 数据表常用字段类型

      基本类型	集合处理
      整数型	数组类型
      浮点数型	map类型
      字符串	struct类型
      布尔类型
      时间类型

   4. 约束的问题–hive支持不良好 不建议使用：在Hive中使用没有在MySQL使用多

      1. 主键约束 primary key
      2. 外键约束
      3. 唯一约束
      4. 检查约束

   5. 底层文件分隔符的问题

      row_format DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

   6. 底层文件的存储格式

      STORED AS 【SEQUENCEFILE】|【TEXTFILE】|

2. 查询数据表

   show tables
   desc table_name
   desc formatted table_name
   show partitions table_name
   

3. 修改数据表修改数据表的名字、字段名、字段的类型、增加分区、删除分区、修改表的talproperties

4. 删除数据表

   drop table if exists table_name;

2.2 DML语言

管理表数据的语言，主要用来负责增加、删除、修改表数据。【Hive存放的数据是海量的大数据】 在hive中不支持删除部分数据或者是修改数据，只支持增加或者删除所有数据或者删除某一个分区的数据

2.2.1 增加数据

1. 普通的增加语法：

   insert into/overwrite table table_name(字段列表)  partition(分区字段=分区值) values(字段值列表)
   

2. 查询语句增加数据：

   INSERT OVERWRITE/into TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
   

3. load转载数据

根据一个文件，将一个文件的数据直接装载到Hive的数据表当中，要求文件的分隔符必须和创建表的时候指定的分隔符一致

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

如果数据文件是在HDFS上，会把源文件移动到数据表的所在目录下

2.2.2 删除数据

Hive不支持删除部分数据，只支持清空表数据或者删除某一个分区的数据

清空表数据/某个分区：

TRUNCATE [TABLE] table_name [PARTITION partition_spec];

这个命令严格意义属于DDL语法

2.2.3 Hive的数据导入和导出

Hive中底层是以文件的形式在HDFS上进行数据存放，因此Hive在向表中增加数据，可以使用文件的形式进行数据的导入，同时Hive一般做统计分析的，统计分析的结果一般都是一个指标表，指标表的数据我们一般会导出到MySQL或者HDFS用作下一步的数据可视化或者数据挖掘使用。

1. 数据导入

   1. load命令
   2. insert命令
   3. create table table_name as select 查询语句
   4. create tale table_name(字段列表) location “文件的位置”
   5. import命令—结合export命令使用，一般是用于Hive的跨版本、跨集群的复制备份操作

2. 数据导出

   1. insert命令

      insert overwrite [local] directory "目录" select查询语句

   2. hadoop命令下载数据

      hdfs dfs -get hive数据表的文件路径 linux文件路径

   3. hive的的客户端命令进行数据的导出（借助了Linux的重定向命令）

      hive -e|-f "HQL查询语句"|文件 >|>> linux文件路径

   4. export命令–将hive一个数据表的数据导出到HDFS的指定目录下

      export table default.student to '/user/hive/warehouse/export/student';

3. sqoop软件实现将hive中的数据和RDBMS关系型数据库之前的数据进行来回迁移

2.3 DQL语言–Hive中最核心最重要的语法

DQL语言是一个数据操作语言，Hive提供的DQL语言是Hive实现统计分析的核心。hive可以通过DQL语言的基本连接查询、条件查询、分组查询等等操作可以实现我们以前MR程序复杂的计算逻辑。

2.3.1 Hive的DQL语法和MySQL的DQL查询语法是非常像的 查询语句的分类

1. SELECT [ALL | DISTINCT] 查询列表（表达式、常量、函数、表字段）  FROM table_reference  as 别名  [inner join | left join | right join | full join  other_table  as 别名 on  连接条件]  [WHERE where_condition]  [GROUP BY col_list]  [ORDER BY col_list]  [CLUSTER BY col_list  | [DISTRIBUTE BY col_list] [SORT BY col_list] ]  [LIMIT [offset,] rows]  [union | union all  other_select]
   

2. 单表查询

   1. SELECT [ALL | DISTINCT] 查询列表（表达式、常量、函数、表字段）  FROM table_reference  [WHERE where_condition]  [GROUP BY col_list]  [ORDER BY col_list]  [CLUSTER BY col_list    | [DISTRIBUTE BY col_list] [SORT BY col_list]  ] [LIMIT [offset,] rows]
      

   2. 基本查询

      1. select 查询列表 from table_name

      2. 如果查询的是表中的所有列的数据，查询列表可以使用 * 来代替

         【注意】* 虽然简单，但是能不用就不用

         （1）HQL 语言大小写不敏感。

         （2) HQL 可以写在一行或者多行

         （3）关键字不能被缩写也不能分行

         （4）各子句一般要分行写。

         （5）使用缩进提高语句的可读性。

   3. 条件查询

      1. SELECT 查询列表 from table_name where 查询条件
      2. 查询条件可以是条件表达式、逻辑表达式、模糊查询
         1. 条件表达式： > < >= <= = !=
         2. 逻辑表达式： is null | is not null | and | or | in | not in | ! | between a and b
         3. 模糊查询：like 两个特殊的符号 % _ rlike 正则表达式

   4. 分组查询

      select 查询列表（只能是常量、表达式、聚合函数、每一组分组的字段） from table_name [where 查询条件] group by 分组字段,...., having 分组后的筛选

   5. 排序查询

      1. 全局排序：order by 排序字段 asc | desc

         Hive的HQL的复杂查询语句底层会转换成为MR程序进行运行，查询的过程中如果我们需要对查询的结果进行排序，那么我们可以使用order by进行排序，order by是全局排序，一旦使用order by 那么HQL语句转换的MR程序底层的reduce任务只有一个，这样的话会把所有的map任务的数据拉取过来，输出一个结果文件，结果文件全局有序。

         全局排序因为只有一个reduce任务，如果处理数据量过多，那么就会导致reduce计算缓慢甚至崩溃

      2. 局部排序：sort by 排序字段 asc|desc

         对HQL语句转换的MR程序，可以指定多个reduceTask,然后map输出的数据会按照hash分区机制随机分区（sort by无法控制的），但是每一个分区的数据最终会根据sort by排序

         【注意】sort by使用的时候，必须指定设置reduceTask的任务数大于1，如果=1，那么sort by和order by就没有任何的区别。

         1. 局部排序指定分区字段：MR中自定义分区机制

            1. sort by是对每一个分区的数据进行局部排序，但是sort by排序的时候不负责分区的数据到底如何划分，每一个分区的数据我们无法控制。
            2. Distribute By 分区字段 sort by 排序字段 asc |desc 先按照Distribute By的分区字段对数据进行分区（分区字段的hash码和reduce任务个数取余数），在按照排序字段对每隔分区的数据进行排序
            3. 设置reduceTask的任务数

         2. CLUSTER BY

            1. 当Distribute By 和sort by的字段一致的时候，可以使用cluster by 分区排序字段

            2. select * from a cluster by age 等同于

               select * from a distribute by age sort by age

            3. cluster by 无法指定排序规则是升序和降序，只能是升序

   6. 分页查询

      1. limit num：查询回来num条数据
      2. limit offset,num：从第offset条数据开始，查询回来num条数据 offset从0开始

   7. 【特殊的两个语法】

      1. 起别名
      2. 去重

   8. 【注意】：分区表特殊在存储上以分区字段值为文件夹形式进行存放，使用的时候可以当作表字段来使用

3. 连接查询

   使用场景：查询的数据来自于多张数据表，并且多张数据表存在“外键”关系。

   Hive和MySQL一样 只支持等值连接。

   1. 连接查询分类（Hive四类连接查询全部支持

      1. 内连接查询 inner join

      2. 左外连接查询 left join

      3. 右外连接查询 right join

         mysql只支持这前三类连接查询

      4. 全外连接查询 full join

   2. 多表连接问题

      select * from a xxx join b on a.xx=b.xx xxx join c on xxx=xxx

   3. 笛卡尔乘积问题（一定要避免这个问题）

      1. a join b a m条数据 b n条数据 ，最后join完成得到m*n条数据 出现效果就是a表的每一条数据和b表的每一条数据都匹配上了
      2. 产生原因：没有写连接条件或者连接条件写错了

4. 联合查询

   1. 使用场景：将多条HQL查询语句的结果通过union|union all连接起来
   2. union 、union all union去重数据 union all不会去重数据
   3. 限制：多条查询语句的查询列表（查询列表的个数、类型和顺序）必须保持一致

5. 子查询

   1. 和MySQL的子查询一模一样的
   2. 查询里面嵌套了一个查询，子查询可以出现from子语句、where子语句…

2.3.2 DQL查询语句中常用函数–Hive最核心的知识点

1. UDF函数：一对一函数，输入一个数据，输出一个数据
2. UDTF函数：一对多函数，输入一个数据，输出多个数据
3. UDAF函数：多对一函数，输入多个数据，输出一个数据
4. 自定义函数
5. 侧视图函数：专门用来搞笛卡尔乘积的

三、启动脚本

#!/bin/sh
# 调用这个脚本需要传递一个参数 start/stop start开启所有hadoop相关服务  stop关闭所有的hadoop服务
if [[ "$1" = "start" ]];then
	echo "====================开启HDFS.....====================="
	start-dfs.sh
	echo "====================HDFS开启成功======================"
        echo "====================开启YARN.... ====================="
	start-yarn.sh
        echo "====================开启YARN成功======================"
        echo "====================开启历史日志服务器JobHistory..===="
	mapred --daemon start historyserver
        echo "====================开启历史日志服务器成功============"
        echo "====================开启hiveserver2服务.....=========="
        hs2.sh start
        echo "====================开启hivsserver2服务成功======================"
elif [[ "$1" = "stop" ]];then
	echo "stop..."
	stop-dfs.sh
        stop-yarn.sh
        mapred --daemon stop historyserver
        hs2.sh stop
	echo "ok"
else
	echo "参数有误"
fi