大数据笔记29—Hadoop基础篇12(Hive特殊使用与函数)

最新推荐文章于 2022-12-15 12:32:44 发布
最新推荐文章于 2022-12-15 12:32:44 发布
阅读量271 收藏 1
点赞数
分类专栏： hadoop hive 文章标签： 数据库 数据仓库 大数据 python hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liaobuqidewo/article/details/116407217
版权

Hive特殊使用与函数

苟有恒，何必三更眠五更起；
最无益，莫过一日曝十日寒。

知识点01：Hive回顾

  1. Hive与HDFS的映射关系是什么？

    • Hive：数据库、表、表中的数据
    • HDFS：数据库目录、表的目录、文件

  2. 元数据的功能是什么，存储在什么位置？

    • 功能
      • 存储了Hive所有对象的信息：数据库、表、列
      • 存储了Hive表与HDFS的映射关系
    • 位置
      • 默认：derby数据库
      • 自定义：MySQL
    • 方式
      • 嵌入式：直接访问Derby
      • 本地：直接访问MySQL
      • 远程：通过metastore服务来访问元数据

  3. metastore服务的功能是什么，怎么启动，端口是什么，Hive的服务端如何启动，端口是什么？

    • metastore
      • 功能：实现元数据的共享
      • 启动：hive --service metastore
      • 端口：9083
    • hiveserver2
      • 功能：Hive的服务端
      • 启动：hiveserver2 / hive --service hiveserver2
      • 端口：10000

  4. 如何封装Hive的SQL脚本？

    • 客户端

      • Hive Shell：Old Client：用于封装SQL脚本
      • Beeline：一般作为交互式的查询客户端
      • JDBC：一般用于封装查询接口

    • 封装SQL

      • hive -e ：执行命令行的SQL语句，少量的SQL语句

      • hive -f：执行SQL文件

      • 传递参数：–hiveconf

  5. Hive中load与insert的命令的功能是什么？

    • DDL、DQL：基本与MySQL一致的

    • DML

      • Hive支持行级的insert、update、delete

      • 数据写入：将数据构建表的结构，通过SQL对数据进行处理

      • 数据来源：大量的数据文件、分析处理的结果

      • 写入方式

        • load：将文件关联到Hive表中

        • insert：保存Select语句的结果

  6. Hive中表的类型有几种，关键字和特点分别是什么？

    create [temporary] [external] table

    • 管理表：默认表结构，必须手动删除，不会自动删除，如果删除，既删除元数据也删除对应的数据

    • 临时表：temporary，如果创建的客户端断开，临时表会自动删除

    • 外部表：external，如果删除，只删除元数据

      • 为了保证数据安全以及共享数据文件，一般都建外部表

知识点02：本篇目标

  1. 表的结构==【重点】==

    • 普通表结构
    • 分区表结构
    • 分桶表结构

  2. Hive中特殊语法

    • Join
      • 逻辑：左外连接、右外连接、内连接、全连接full join
      • 实现：Map Join，Reduce Join
    • xxx By
      • order by 、sort by 、distribute by 、cluster by
    • 特殊数据类型
      • Array
      • Map
    • 特殊数据格式

  3. Hive中的函数【重点】

    • 内置函数：字符串函数、日期函数、聚合函数、特殊函数、窗口函数

    • 自定义函数

    • URL解析函数

    • 侧视图的使用

    • 集合展开函数

知识点03：Hive表结构：普通表结构

  • 引入：建表语句

    CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
(
    col1Name col1Type [COMMENT col_comment],
    co21Name col2Type [COMMENT col_comment],
    co31Name col3Type [COMMENT col_comment],
    co41Name col4Type [COMMENT col_comment],
    co51Name col5Type [COMMENT col_comment],
    ……
    coN1Name colNType [COMMENT col_comment]

)
[PARTITIONED BY (col_name data_type ...)]		--分区表结构
[CLUSTERED BY (col_name...) [SORTED BY (col_name ...)] INTO N BUCKETS] --分桶表结构
[ROW FORMAT row_format] -- 指定数据文件的分隔符
	row format delimited fields terminated by '列的分隔符' -- 列的分隔符，默认为\001
	lines terminated by '行的分隔符' --行的分隔符，默认\n
[STORED AS file_format] -- 指定文件的存储格式
[LOCATION hdfs_path] -- 用于指定表的目录所在位置，默认表的目录在数据库的目录下面

  • 目标：了解Hive中普通表结构的特点及应用场景

  • 路径

    • step1：Hive与HDFS的映射关系
    • step2：MapReduce处理的规则
    • step3：普通表结构的特点

  • 实施

    • Hive与HDFS的映射关系

      HiveHDFS
      数据库目录
      表目录
      数据文件

    • MapReduce处理的规则

      select count(*) from table where id > 30;

      • step1：检索元数据，找到表对应的HDFS目录

      • step2：将**表的最后一级目录作为MapReduce程序的输入**

        TextInputFormat.setInputPaths(job,path1,path2……)

    • 普通表结构的特点及应用

      • 结构

        Hive数据仓库目录/数据库目录/表的目录/数据文件
/user/hive/warehouse/db_emp.db/tb_emp/emp.txt

在这里插入图片描述

- 特点

  - **表的最后一级目录是表的目录**

  - 问题：如果是一张普通表的结构，手动将文件通过HDFS命令放入表的目录下，在表中能否读到？

    - 可以

      

- 应用

  - 默认创建的表都是普通表结构
  - 一般用于将原始的一个整体的数据文件构建成表的结构

  • 小结

    • MapReduce处理Hive表数据的规则？

      • 将Hive表的最后一级目录作为底层的输入目录

    • 普通表结构的特点和应用是什么？

      • 语法

        create table (
);

      • 特点：表的最后一级目录是表的目录

      • 应用：一般用于将原始的大数据文件映射成表的结构

知识点04：Hive表结构：分区表设计

  • 目标：掌握Hive中分区表结构的特点及应用场景

  • 路径

    • step1：普通表结构的问题
    • step2：分区表结构的设计

  • 实施

    • 普通表结构的问题

      • 需求：现在有一张普通表，记录一年365天的数据，每天一个1G文件，现在需要对其中某一天的数据进行处理

      • 创建普通表，加载数据到普通表中

        /user/hive/warehouse /db_nginx.db /tb_nginx /2020-01-01.log
                                             2020-01-02.log
                                             ……
                                             2020-12-31.log

      • 实现

        select count(*) from tb_nginx where daystr='2020-05-01';

      • 问题

        • 底层MapReduce执行
        • Input：/user/hive/warehouse /db_nginx.db /tb_nginx
          • 365GB
          • Map
            • 过滤：日期等于5月1号
            • 输出：1GB
          • Shuffle & Reduce & Output
            • 聚合得到条数
        • Map阶段要做大量无意义的过滤操作，导致大量的资源浪费

      • 解决思想：如果只读取5月1号的数据文件，就不用过滤，直接处理得到结果

    • 分区表结构的设计

      • 设计思想

        • 将数据按照一定规则条件划分**不同的目录**进行分区存储
        • 在查询时，可以根据查询条件在目录层次进行过滤，直接由MapReduce加载需要处理的数据的目录

      • 本质

        • 提前将数据划分到不同的目录中存储
        • 通过查询条件减少底层MapReduce的输入的数据量，避免无用的过滤，提高性能

      • 结构

        • 普通表

          数据仓库目录/数据库目录/表的目录/数据文件
/user/hive/warehouse /db_nginx.db /tb_nginx /2020-01-01.log
                                             2020-01-02.log
                                             ……
                                             2020-12-31.log

        • 分区表

          数据仓库目录/数据库目录/表的目录/分区目录/分区数据文件
/user/hive/warehouse /db_nginx.db /tb_nginx /daystr=2020-01-01 /2020-01-01.log
                                            /daystr=2020-01-02 /2020-01-02.log
                                             ……
                                            /daystr=2020-12-31 /2020-12-31.log

          • 通过SQL进行过滤查询

            select count(*) from tb_nginx where daystr='2020-05-01';

          • MapReduce：按照日期分区，过滤条件也是日期

            • Input：/user/hive/warehouse /db_nginx.db /tb_nginx /daystr=2020-05-01
              • 1GB
            • Map
              • 1GB

      • 特点

        • 表的最后一级目录就是分区目录

      • 应用

        • 工作中离线数据仓库的业务一般是按照时间进行处理
          • 这个小时处理上个小时的数据
          • 今天处理昨天的数据
          • 这个月处理上个月的数据
          • 今年处理去年的数据
          • 工作离线数据仓库最常用的表的结构：分区表结构
        • 最常用的表：分区外部表
        • 分区的划分：一般都是按照时间划分

  • 小结

    • 分区表的结构、特点和应用场景是？
    • 结构：数据仓库/数据库/表/分区/分区数据文件
      • 特点：表的最后一级是分区目录
    • 应用：最常见的表的结构

知识点05：Hive表结构：分区表实现

  • 目标：实现分区表的构建及应用

  • 路径

    • step1：静态分区
    • step2：动态分区
    • step3：多级分区

  • 实施

    • 静态分区

      • 应用：数据文件本身就是按照分区的规则划分好的，直接创建分区表，加载每个分区的数据即可

      • 步骤

        • step1：直接创建分区表
        • step2：加载每个文件到对应的分区中即可

      • 实现

        • 当前已有按照每个部分分区好的数据文件

在这里插入图片描述

  - 创建分区表

    ```sql
    create table tb_emp_part1(
     empno string,
     ename string,
     job string,
     managerno string,
     hiredate string,
     salary double,
     jiangjin double,
     deptno string
    ) 
    partitioned by (department int)
    row format delimited fields terminated by '\t';
    
    ```

  - 加载对应数据文件到对应的分区

    ```sql
    load data local inpath '/export/data/emp10.txt' into table tb_emp_part1 partition (department=10);
    load data local inpath '/export/data/emp20.txt' into table tb_emp_part1 partition (department=20);
    load data local inpath '/export/data/emp30.txt' into table tb_emp_part1 partition (department=30);
    
    ```

    

  - 查询数据

    - 观察HDFS存储结构

在这里插入图片描述

在这里插入图片描述

    - 测试SQL的执行计划：explain

      - 普通表

        ```
        explain extended select count(*) as numb from tb_emp where deptno = 20;
        
        ```

在这里插入图片描述

      - 分区表

        ```
        explain extended select count(*) as numb from tb_emp_part1 where department = 20;
        
        ```

在这里插入图片描述

    - 查看元数据

      - PARTITIONS

在这里插入图片描述

      - SDS

在这里插入图片描述

  • 动态分区

    • 应用：数据本身没有按照分区的规则划分，需要通过程序实现自动动态划分

    • 步骤

      • step1：先创建一个普通表，加载整体的数据
      • step2：再创建一个分区表
      • step3：将普通表的数据写入分区表，实现动态分区

    • 实现

      • 创建普通表

        • tb_emp：普通表，所有部门的员工信息都在一个目录文件中

      • 创建分区表

        create table tb_emp_part2(
  empno string,
  ename string,
  job string,
  managerno string,
  hiredate string,
  salary double,
  jiangjin double
) 
partitioned by (dept string)
row format delimited fields terminated by '\t';

      • 修改属性

        --开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict;


      - 写入分区表

  ```sql
insert into table tb_emp_part2 partition(dept)
  select * from tb_emp ;
|
  insert into table tb_emp_part2 partition(dept)
  select ……,deptno from tb_emp ;

      • 问题：上面这个代码是如何知道按照哪个字段进行自动分区的呢？

        • 按照查询语句的最后一个字段

      • 要求：查询语句一般不用select *，强制要求查询语句的最后一个字段作为分区字段的

      • 查询数据

        • HDFS

在这里插入图片描述

    - 数据

在这里插入图片描述

  • 多级分区

    • 创建多级分区表

      create table tb_ds_source(
id                      string,
url                     string,
referer                 string,
keyword                 string,
type                    string,
guid                    string,
pageId                  string,
moduleId                string,
linkId                  string,
attachedInfo            string,
sessionId               string,
trackerU                string,
trackerType             string,
ip                      string,
trackerSrc              string,
cookie                  string,
orderCode               string,
trackTime               string,
endUserId               string,
firstLink               string,
sessionViewNo           string,
productId               string,
curMerchantId           string,
provinceId              string,
cityId                  string,
fee                     string,
edmActivity             string,
edmEmail                string,
edmJobId                string,
ieVersion               string,
platform                string,
internalKeyword         string,
resultSum               string,
currentPage             string,
linkPosition            string,
buttonPosition          string
)
partitioned by (daystr string,hourstr string)
row format delimited fields terminated by '\t';

    • 加载多级分区数据

      load data local inpath '/export/data/2015082818' into table tb_ds_source partition (daystr='20150828',hourstr='18');
load data local inpath '/export/data/2015082819' into table tb_ds_source partition (daystr='20150828',hourstr='19');

    • 结果
      在这里插入图片描述
      在这里插入图片描述

    • 查看分区的命令

      show partitions  tbname;

在这里插入图片描述

  • 小结

    • 如何实现构建分区表？
      • 静态分区：数据本身就是按照分区规则分好的
      • 动态分区：数据本身没有分好，通过程序实现自动分区
      • 关键字：partitioned by （分区的字段）
    • 分区是什么级别的设计？
      • 目录级别的，一个目录代表一个分区
      • 目录的名称：分区字段 = 分区的值
    • 分区的字段是逻辑还是物理的？
      • 逻辑的

知识点06：Hive表结构：分桶表设计及实现

  • 目标：理解分桶表的设计思想及实现分桶表的测试

  • 路径

    • step1：Join的问题
    • step2：分桶表的设计
    • step3：分桶表的实现

  • 实施

    • Join的问题

      • Map Join：性能比较好，适合于小表join大表
      • Reduce Join：通过Shuffle的分组来实现的，适合于大表join大表
      • 问题：怎么解决Reduce Join性能非常差的问题？

    • 分桶表的设计

      • 思想：将大的数据按照规则划分为多份小数据，每份小的数据都走Map Join，减少每条数据的比较次数，提高性能

      • 功能

        • 优化大表join大表的问题
        • 分桶采样

      • 本质：通过MapReduce底层的分区【Reduce的划分规则】将数据划分到多个文件中

        • 每个文件 = 每个桶 = 每个Reduce
        • 划分规则：Hash取余

      • 结构

        数据仓库目录/数据库目录/表的目录/分桶的文件数据

      • 设计

在这里插入图片描述

  - 将两张大表按照相同的规则进行分桶
  - 实现分桶Join，桶与桶之间直接进行Map Join，减少比较次数，提高性能

- 流程

  - step1：将两张表进行分桶
  - step2：实现Bucket Join
    - Hive会自动判断是否满足分桶Join的条件，如果满足就自动实现分桶Join

  • 分桶表的实现

    • 语法

      • clustered by col [sorted by col] into N buckets

    • clustered by ：按照哪一列分桶

      • sorted by：每个桶的内部按照哪一列进行排序

    • N：分几个桶，代表底层写入数据就有几个reduce

    • 实现

    • 开启配置

      set hive.optimize.bucketmapjoin = true;
  set hive.optimize.bucketmapjoin.sortedmerge = true;

      • 创建分桶表

        create table tb_emp_bucket(
empno string,
ename string,
job string,
managerno string,
hiredate string,
salary double,
jiangjin double,
deptno string
) 
clustered by (deptno) into 3 BUCKETS
row format delimited fields terminated by '\t';

    • 写入分桶表

      insert overwrite table tb_emp_bucket
select * from tb_emp cluster by (deptno);
      • 查看结果

在这里插入图片描述
在这里插入图片描述

  • 小结

    • 分桶表的功能和设计思想是什么？
      • 功能：优化大表join大表的性能
      • 设计：将数据按照划分规则划【多个Reduce划分】分多个小文件中，每个小文件可以实现Map Join，降低了比较次数，提高性能
    • 分桶表是什么级别的设计？
      • 文件级别
    • 分桶字段是物理的还是逻辑的？
      • 物理的
    • 注意：分桶表的数据不能使用load加载

知识点07：Hive中的Join：Join逻辑

  • 目标：了解Hive中的join语法的使用

  • 实施

    • inner join：内连接

      select
    a.empno,
    a.ename,
    b.deptno,
    b.dname
from
tb_emp a join tb_dept b on a.deptno = b.deptno;
      • 两边都有结果才有

    • left outer join：左外连接

      • 左边有，结果就有

        select
    a.empno,
    a.ename,
    b.deptno,
    b.dname
from
tb_emp a left join tb_dept b on a.deptno = b.deptno;

    • right outer join：右外连接

      • 右边有，结果就有

        select
    a.empno,
    a.ename,
    b.deptno,
    b.dname
from
tb_emp a  right join tb_dept b on a.deptno = b.deptno;

    • full join：全连接

      • 两边任意一边又，结果就有

        explain select
    a.empno,
    a.ename,
    b.deptno,
    b.dname
from
tb_emp a  full join tb_dept b on a.deptno = b.deptno;

    • 笛卡尔积

      select a.*,b.* from a,b;
select a.*,b.* from a join b;
      • Hive中严禁产生大数据量的笛卡尔积

  • 小结

    • 基本与MySQL一致

知识点08：Hive中的Join：Join实现

  • 目标：了解Hive中Join的底层实现
  • 路径
    • step1：Map Join
    • step2：Reduce Join
    • step3：Bucket Join
  • 实施
    • Map Join
      • 特点：将小表的数据放入分布式缓存，与大表的每个部分进行Join，发生在Map端，不用经过shuffle
      • 场景：小表 join 小表 ，小表 join 大表
      • 要求：必须有一张表是小表
      • 实现规则
        • Hive中默认会优先判断是否满足Map Join的条件
          • 判断表的文件大小：小于25MB
        • 如果满足，就自动走Map Join
        • 如果不符合，自动走Reduce Join
    • Reduce Join
      • 特点：利用Shuffle的分组来实现Join过程，发生在Reduce端，需要经过Shuffle
      • 场景：大表join大表
      • 要求：Hive中默认如果不满足Map Join，就自动走Reduce Join
    • Bucket Join
      • 特点：将大的数据划分成多份小的数据，每个小数据就是一个桶，实现分桶join
      • 场景：大表join大表，优化这个过程，如果你的Join多次
      • 要求
        • Bucket Map Join：普通的分桶join
          • 两张表都是桶表
          • 桶的个数成倍数
          • Join字段 = 分桶的字段
        • Bucket Sort Merge Map Join：基于排序的分桶Join
          • 两张表都是桶表
          • 桶的个数成倍数
          • Join字段 = 分桶的字段 = 排序的字段
  • 小结
    • 了解Hive中底层Join实现的方案及各自的特点和场景

知识点09：Select语法：order by与sort by

  • 目标：掌握Select语法中order by与sort by的功能与区别

  • 路径

    • step1：Hive中ReduceTask个数
    • step2：order by的功能
    • step3：sort by的功能

  • 实施

    • Hive中ReduceTask个数

      Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):\
 #每个Reduce处理的数据量大小
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
#最多允许启动的reduce的个数
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
#设置reduce的个数
  set mapreduce.job.reduces=<number>

在这里插入图片描述

```
set mapreduce.job.reduces = 2;

```

在这里插入图片描述

  • order by的功能

    • 功能：全局排序

      select empno,ename,deptno,salary from tb_emp order by salary desc;

在这里插入图片描述

- 问题：如果Reduce有多个，能否实现全局排序？

  - 不能有多个Reduce，使用了order by，只会启动一个reduce

  • sort by的功能

    • 功能：局部排序

      • 多个Reduce的场景下，每个reduce内部有序

    • 用法：与order by是一致的

      select empno,ename,deptno,salary from tb_emp sort by salary desc;

在这里插入图片描述

  ```
  insert overwrite local directory '/export/data/sort' row format delimited fields terminated by '\t'
  select empno,ename,deptno,salary from tb_emp sort by salary desc;
  
  ```

在这里插入图片描述

  • 小结

    • order by与sort by的功能与区别是？
      • order by：全局排序，只能有1个reduce
      • sort by：局部排序，多个reduce每个reduce局部排序

知识点10：Select语法：distribute by 与 cluster by

  • 目标：了解distribute by与cluster by语法的使用

  • 路径

    • step1：distribute by
    • step2：cluster by

  • 实施

    • distribute by

      • 功能：用于干预底层的MapReduce，指定某个字段作为K2

      • 语法

        insert overwrite local directory '/export/data/distby' row format delimited fields terminated by '\t'
select empno,ename,deptno,salary from tb_emp distribute by deptno;

在这里插入图片描述

  ```
  insert overwrite local directory '/export/data/distby' row format delimited fields terminated by '\t'
  select empno,ename,deptno,salary from tb_emp distribute by deptno sort by salary desc;
  
  ```

在这里插入图片描述

- 应用

  ```
  distribute by 1					=>		用于将所有数据进入一个Reduce中
  distribute by rand()			=> 		实现随机分区，避免数据倾斜
  
  ```

  • cluster by

    • 功能：如果distribute by与sort by是同一个字段，可以使用cluster by代替

  • 小结

    • distribute by的功能是？
      • 用于干预MapReduce的K2，指定K2这一列

知识点11：数据类型：Array

  • 目标：了解Hive表中数组类型的定义及使用

  • 实施

    • 数据

      vim /export/data/array.txt


      zhangsan	beijing,shanghai,tianjin
wangwu	shanghai,chengdu,wuhan,haerbin

    • 建表

      create database db_complex;
use db_complex;

create table if not exists complex_array(
name string,
work_locations array<string>
)
row format delimited fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ',';

COLLECTION ITEMS TERMINATED BY ',' --用于指定数组中每个元素的分隔符

    • 加载

      load data local inpath '/export/data/array.txt' into table complex_array;

    • 取值

      --统计每个用户工作过的城市个数
select name,size(work_locations) as numb from complex_array;
--取出数组中单独的元素
select name,work_locations[0],work_locations[1] from complex_array;

在这里插入图片描述

  • 小结

    • 了解基本的定义与使用即可

知识点12：数据类型：Map

  • 目标：了解Hive表中Map集合类型的定义及使用

  • 实施

    • 数据

      vim /export/data/map.txt


      1,zhangsan,唱歌:非常喜欢-跳舞:喜欢-游泳:一般般
2,lisi,打游戏:非常喜欢-篮球:不喜欢

    • 建表

      create table if not exists complex_map(
id int,
name string,
hobby map<string,string>
)
row format delimited fields terminated by ','
COLLECTION ITEMS TERMINATED BY '-' MAP KEYS TERMINATED BY ':';

COLLECTION ITEMS TERMINATED BY '-'   --用于划分每个KV对
MAP KEYS TERMINATED BY ':';			--用户划分K和V的

    • 加载

      load data local inpath '/export/data/map.txt' into table complex_map;

    • 取值

      --统计每个人有几个兴趣爱好
select name,size(hobby) as numb from complex_map;
--取出每个人对唱歌的喜好程度
select name,hobby["唱歌"] as deep from complex_map;

在这里插入图片描述

  • 小结

    • 了解基本的定义与使用即可

知识点13：特殊案例：正则加载

  • 目标：了解Hive中分隔符的问题及实现正则加载数据

  • 路径

    • step1：分隔符的问题
    • step2：处理方案
    • step3：正则加载

  • 实施

    • 分隔符的问题

      • 场景1：数据中的列的分隔符||，Hive不支持多字节分隔符

        01||周杰伦||中国||台湾||男||七里香
02||刘德华||中国||香港||男||笨小孩
03||汪  峰||中国||北京||男||光明
04||朴  树||中国||北京||男||那些花儿
05||许  巍||中国||陕西||男||故乡
06||张靓颖||中国||四川||女||画心
07||黄家驹||中国||香港||男||光辉岁月
08||周传雄||中国||台湾||男||青花
09||刘若英||中国||台湾||女||很爱很爱你
10||张  杰||中国||四川||男||天下

      • 场景2：数据中每列的分隔符不一致

        1 zhangsan,18-male
2 lisi,20-female

      • 场景3：数据字段中包含了分隔符

        192.168.88.1 - - [24/Dec/2019:15:54:58 +0800] "POST /set/report/ HTTP/1.1" 404 1093

    • 处理方案

      • 方案一：先做ETL，需要先开发一个程序来实现数据的处理，将处理的结果再加载到Hive表中
        • 通过程序替换分隔符
      • 方案二：Hive官方提供了正则加载的方式
        • 通过正则表达式来匹配每一条数据中的每一列

    • 正则加载

      • 数据

        vim /export/data/regex.txt


        2019-08-28 00:03:00 tom
2019-08-28 10:00:00 frank
2019-08-28 11:00:00 jack
2019-08-29 00:13:23 tom
2019-08-29 10:00:00 frank
2019-08-30 10:00:00 tom
2019-08-30 12:00:00 jack

      • 建表

        • 正常创建

          create table regex1(
timestr string,
name string
) row format delimited fields terminated by ' ';

load data local inpath '/export/data/regex.txt' into table regex1;

在这里插入图片描述

  - 正则加载

    ```sql
    create table regex2(
    timestr string,
    name string
    )
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
    WITH SERDEPROPERTIES (
      "input.regex" = "([^}]*) ([^ ]*)"
    )
    STORED AS TEXTFILE;
    
    
    ```

load data local inpath '/export/data/regex.txt' into table regex2;
    ```

```

在这里插入图片描述

```

​

  • 小结

    • 了解分隔符存在的问题及正则解决方案即可

知识点14：Hive中的函数：内置函数

  • 目标：掌握Hive中常用的内置函数

  • 路径

    • step1：查看函数
    • step2：常用函数

  • 实施

    • 查看函数

      • 列举

        show functions;

      • 查看函数的用法

        desc function  [extended] funName;

在这里插入图片描述

  • 常用函数

    • 聚合函数：count、sum、avg、max、min
    • 条件函数：if、case when
    • 字符串函数
      • 截取：substring，substr
      • 拼接：concat、concat_ws
      • 分割：split
      • 查找：instr
      • 替换：regex_replace
      • 长度：length
    • 日期函数
      • 转换：unix_timestamp、from_unixtime
      • 日期：current_date，date_sub，date_add
      • 获取：year、month、day、hour
    • 特殊函数
      • JSON：json_tuple，get_json_object
      • URL：parse_url，parse_url_tuple
      • 窗口函数
        • 聚合窗口：sum、count……
        • 位置窗口：lag、lead、first_value，last_value
        • 分析函数：row_number、rank、dense_rank

  • 小结

    • 了解基本常用的内置函数

知识点15：Hive中的函数：自定义函数

  • 目标：了解Hive中如何实现自定义函数

  • 路径

    • step1：函数分类
    • step2：自定义UDF函数
    • step3：自定义UDAF与UDTF

  • 实施

    • 函数分类

      • UDF：一对一函数，类似于substr
      • UDAF：多对一函数，类似于聚合函数：count
      • UDTF：一对多函数，explode将一行中的每个元素变成一行

    • 自定义UDF函数

      • 需求：24/Dec/2019:15:55:01 -> 2019-12-24 15:55:01

      • step1：自定义一个类，继承UDF类

      • step2：在类中至少实现一个evaluate方法定义处理数据的逻辑

      • step3：打成jar包，添加到hive的环境变量中

        add jar /export/data/udf.jar;

      • step4：将类注册为函数

        create temporary function transFDate as 'bigdata.itcast.cn.hive.udf.UserUDF';

      • step5：调用函数

        select transFDate("24/Dec/2019:15:55:01");

在这里插入图片描述

  • 自定义UDAF与UDTF

    • UDAF

      • step1：将类注册为函数

        create temporary function  userMax as 'bigdata.itcast.cn.hive.udaf.UserUDAF';

      • step2：调用函数

        select userMax(cast(deptno as int)) from db_emp.tb_dept;

        • cast：强制类型转换函数

          cast(列 as 类型)

    • UDTF

      • step1：将类注册为函数

        create temporary function  transMap as 'bigdata.itcast.cn.hive.udtf.UserUDTF';

      • step2：调用函数

        select transMap("uuid=root&url=www.taobao.com") as (userCol1,userCol2);

  • 小结

    • Hive中的自定义函数分为几类？
      • UDF：一对一
      • UDAF：多对一
      • UDTF：一对多
    • 如何实现自定义UDF函数？
      • step1：继承类实现方法
      • step2：打成jar包，添加到Hive中
      • step3：指定类创建函数即可

知识点16：Hive中的函数：parse_url_tuple

  • 目标：掌握parse_url_tuple函数的使用

  • 路径

    • step1：URL的分析需求
    • step2：URL的组成
    • step3：URL的解析

  • 实施

    • URL的分析需求

      • 一般需要对用户访问和来源的url来做分析
      • 访问分析：用户当前正在访问的页面是什么
        • 分析当前网站热门访问的top10分类
          • 对所有用户当前正在访问域名进行分组聚合排序
      • 来源分析：用户是从哪个页面跳转到我们的平台的
        • 分析网站所有用户都是从什么地方进入网站的

    • URL的组成

      https://

www.jd.com

/index.jsp 

?

cu=true&utm_source=baidu-pinzhuan&utm_medium=cpc&utm_campaign=t_288551095_baidupinzhuan&utm_term=0f3d30c8dba7459bb52f2eb5eba8ac7d_0_d4905eaf846c46baa9a57cf05415fb29
      image-20210502211051884

    • URL的解析函数

      • 数据

        vim /export/data/lateral.txt


        1	http://facebook.com/path/p1.php?query=1
2	http://www.baidu.com/news/index.jsp?uuid=frank
3	http://www.jd.com/index?source=baidu


        create table tb_url(
id int,
url string
) row format delimited fields terminated by '\t';

--加载数据
load data local inpath '/export/data/lateral.txt' into table tb_url;

      • 函数

        • parse_url：用于解析URL，每次只能解析一个元素
        • parse_url_tuple：用于解析URL，是一个UDTF函数，一次解析多个元素

      • 使用

        0: jdbc:hive2://node3:10000> select parse_url(url,'HOST') from tb_url;
+----------------+--+
|       c0       |
+----------------+--+
| facebook.com   |
| www.baidu.com  |
| www.jd.com     |
+----------------+--+
3 rows selected (0.22 seconds)
0: jdbc:hive2://node3:10000> select parse_url(url,'PATH') from tb_url;
+------------------+--+
|        c0        |
+------------------+--+
| /path/p1.php     |
| /news/index.jsp  |
| /index           |
+------------------+--+
3 rows selected (0.062 seconds)
0: jdbc:hive2://node3:10000> select parse_url(url,'QUERY') from tb_url;
+---------------+--+
|      c0       |
+---------------+--+
| query=1       |
| uuid=frank    |
| source=baidu  |
+---------------+--+
3 rows selected (0.09 seconds)

0: jdbc:hive2://node3:10000> select parse_url_tuple(url,'HOST','PATH','QUERY') from tb_url;
+----------------+------------------+---------------+--+
|       c0       |        c1        |      c2       |
+----------------+------------------+---------------+--+
| facebook.com   | /path/p1.php     | query=1       |
| www.baidu.com  | /news/index.jsp  | uuid=frank    |
| www.jd.com     | /index           | source=baidu  |
+----------------+------------------+---------------+--+
3 rows selected (0.157 seconds)

  • 小结

    • parse_url与parse_url_tuple函数的功能与区别？
      • 功能：解析url
      • 区别：parse_url_tuple是一个UDTF函数，可以一次性解析多个元素

知识点17：Hive中的函数：lateral view

  • 目标：掌握Hive中lateral view侧视图的使用

  • 路径

    • step1：UDTF函数的问题
    • step2：lateral view

  • 实施

    • UDTF函数的问题

      • udtf只能直接select中使用
      • 不可以添加其他字段使用
      • 不可以嵌套调用
      • 不可以和group by/cluster by/distribute by/sort by一起使用

在这里插入图片描述

```
select id,parse_url_tuple(url,'HOST','PATH','QUERY') from tb_url;

```

  • lateral view

    • 功能：UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表

    • 语法

      select …… from tabelA lateral view UDTF(xxx) 别名 as col1,col2,col3……

    • 实现

      select 
  a.id, 
  b.host,
  b.path,
  b.query
from tb_url a lateral view parse_url_tuple(url,'HOST','PATH','QUERY') b as host,path,query;

在这里插入图片描述

  • 小结

    • lateral view的功能是？
      • 搭配UDTF函数使用，将UDTF函数的结果变成类似于视图的表，方便与原表进行操作
    • UDTF函数用法
      • 用法一：单独在select后面使用，select中不能包含别的字段
      • 用法二：搭配lateral view使用

知识点18：Hive中的函数：explode

  • 目标：掌握explode函数的用法

  • 实施

    • 功能

      • 将集合类型中集合的每个元素变成一行

    • 语法

      explode( Map | Array)

    • 实现

      • 单独使用

        select explode(work_locations) as loc from complex_array;
select explode(hobby) from complex_map;

在这里插入图片描述

- 侧视图连用

  ```sql
  select 
    a.id,
    a.name,
    b.*
  from complex_map a lateral view explode(hobby) b as hobby,deep;
  
  ```

  • 小结

    • explode的功能及语法？

附录一：Hive Maven 依赖

    <properties>
        <hadoop.version>2.7.5</hadoop.version>
        <mysql.version>5.1.38</mysql.version>
        <hive.version>2.1.0</hive.version>
    </properties>

    <dependencies>
        <!--引入单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
        <!-- Hadoop Client 依赖 -->
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>${hadoop.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>${hadoop.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-hdfs</artifactId>
            <version>${hadoop.version}</version>
        </dependency>
        <!-- MySQL Client 依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <!-- Hive依赖 -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>${hive.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-common</artifactId>
            <version>${hive.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-cli</artifactId>
            <version>${hive.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>${hive.version}</version>
        </dependency>
    </dependencies>
了不起的我阿
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
大数据笔记、hadoop、hive、flink等
03-29
Hive是基于Hadoop的数据仓库工具，它允许用户使用SQL-like语言（称为HQL）对大数据进行查询和分析。Hive将SQL查询转换为一系列MapReduce任务，简化了大数据分析的复杂性，使非程序员也能方便地处理大数据。Hive还...
大数据技术分享 Hadoop集群监控与Hive高可用方案 共17页.pdf
06-18
Hadoop集群监控与Hive高可用方案 本文档主要介绍了Hadoop集群监控和Hive高可用方案两个方面的内容。下面是对这两个方面的详细说明： 一、Hadoop集群监控 Hadoop集群监控是指对Hadoop集群的运行状态进行实时监控，...
hive-手动清除hive任务的临时数据
DCHAO的博客
08-21 978
https://cloud.tencent.com/developer/article/1363423
删除临时表
秋芝林的专栏
04-03 2459
SELECT sid FROM v$lock                                                                                          WHERE id1 = (SELECT object_id  FROM user_objects                                        ...
Hive总结 --hive表的创建，删除和修改
qq_42740899的博客
12-12 4245
hive 常用语句总结
dataworks删除大量从hive拉过来的临时表
最新发布
weixin_47699191的博客
12-15 425
dataworks删除大量从hive拉过来的临时表
hadoop12--hive的概念, 配置,基本操作
大数据开发工程师
11-08 511
文章目录hivehive的基本概念hive的基本操作数据库的操作数据表的操作管理表与外部表的区别hive常用交互命令hive常见属性配置创建自定义配置文件的步骤配置查询信息的显示（头信息）配置hive的日志信息参数的配置方式hive数据类型DDL数据定义1. 创建数据库,创建的数据库存储在 HDFS 上的默认路径是/user/hive/warehouse/*.db2. 删除数据库 以下官网提供...
大数据笔记，包含Hadoop、Spark、Flink、Hive、Kafka、Flume、ZK......
05-08
大数据笔记，包含Hadoop、Spark、Flink、Hive、Kafka、Flume、ZK...... 大数据笔记，包含Hadoop、Spark、Flink、Hive、Kafka、Flume、ZK...... 大数据笔记，包含Hadoop、Spark、Flink、Hive、Kafka、Flume、ZK.......
大数据云计算技术 Hadoop集群监控与Hive高可用-暴风影音技术团队分享（共17页）.ppt
09-04
Hadoop集群监控Cacti 优点 监控每台服务器的详细数据 SNMP采集数据可以自己定义 相比zabbix：无需客户端 安装配置简单 spine采集速度快 模板众多 支持IPMI 缺点 SNMPD为UDP协议，不够稳定 无法获知集群整体...
Hive操作——删除表(drop、truncate)
AcceptedLin的博客
05-18 1万+
Hive操作——删除表(drop、truncate) Hive删除操作主要分为几大类：删除数据（保留表）、删除库表、删除分区。我将以下图为例清空iot_devicelocation中的数据，之后再删除表、库等。 首先来看一下iot_deivcelocation中的数据。 select * fromiot_deivcelocation 一、仅删除表中数据，保留表结构 hive> truncate table iot_devicelo...
sql 创建临时表的两种写法和删除临时表
幽灵
06-15 1834
--创建、删除临时表 --第一种方式 create table #tmp(name varchar(255),id int) --第二种方式 select count(id) as storyNum , sum(convert(numeric(10,2),case when isnumeric(code)=1 then code else 0 end)) as codeNum...
临时表删除操作
12-15 1047
删除SQL Server临时表和一般表并不相同，下面将为您为别示例错误和正确的删除操作，供您参考，希望对您能够有所帮助。 临时表与一般的表不同，它是保存到tempDb表中。临时表的表名与你所建的表名也不一样，因为他要为不同人的相同操作创建不同的临时表。 1、错误的删除操作： --错误的临时表删除操作，因为所在数据库不同 IF  EXISTS (SELECT * FROM sys
hive集群中间（临时）表定时清理shell脚本
zcb_data的博客
06-05 1936
    在使用hive集群的过程中，经常会生成一些一次性使用的中间表，这种中间表的使用率不高，而已可以再次生成，但大量的中间表会占用很多的集群存储资源，下面我教大家写一个定时清理hive集群中间表的shell脚本，但中间表必须有固定格式，例如：博主的集群中间表全是vra开头，并且中间含有res，并以tmp结尾。那么该脚本如下：#!/bin/bash #######hive集群中间表定时清理脚本##...
Hive中删除表数据的几种方式
热门推荐
qq_42456324的博客
11-01 2万+
在内部表中 仅删除表中数据，保留表结构 方法一 truncate table 表名;（truncate用于删除所有的行，这个行为在hive元存储删除数据是不可逆的） truncate 不能删除外部表！因为外部表里的数据并不是存放在Hive Meta store中 方法二或delete from 表名 where 1 = 1 ; （delete用于删除特定条件下的行，使用where 1=1 删除所有行 SQL中where 1 = 1 的使用） 在外部表中 insert ove...
hive删除表语句
jtpython666的博客
01-11 5004
-- 删除库 drop database if exists db_name; -- 强制删除库 drop database if exists db_name cascade; -- 删除表 drop table if exists employee; -- 清空表 truncate table employee; -- 清空表，第二种方式 insert overwrite table employee select * from employee where 1=0; -- 删除分区
Hive创建/删除/截断表
飞朋的博客
10-22 2708
目录 创建表 内部表和外部表 存储格式 行格式& SerDe Partitioned Tables External Tables Create Table As Select (CTAS) Create Table Like Bucketed Sorted Tables Skewed Tables(倾斜表) Temporary Tables（临时表） Transa...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值