Loading

22-Hive函数应用

1. 多字节分隔符

1.1 问题与需求

【默认规则】Hive 默认序列化类是 LazySimpleSerDe，其只支持使用单字节分隔符（char）来加载文本数据，例如逗号、制表符、空格等等，默认的分隔符为”\001”。根据不同文件的不同分隔符，我们可以通过在创建表时使用 row format delimited 来指定文件中的分割符，确保正确将表中的每一列与文件中的每一列实现一一对应的关系。

之前的案例都是单字节分隔符的数据加载，但实际工作中，我们遇到的数据往往不是非常规范化的数据，例如我们会遇到以下的两种情况：

基于上述的两种特殊数据，我们如果使用正常的加载数据的方式将数据加载到表中，就会出以下两种错误：

（1）每一行数据的分隔符是多字节分隔符，例如：”||”、“--”等，尝试直接使用 LazysimpleSerDe 处理。

  • 问题：数据发生了错位，没有正确的加载每一列的数据；
  • 原因：Hive 中默认只支持单字节分隔符，无法识别多字节分隔符。

（2）数据的字段中包含了分隔符

  • 问题：时间字段被切分成了两个字段，后面所有的字段出现了错位；
  • 原因：时间数据中包含了分隔符，导致 Hive 认为这是两个字段，但实际业务需求中为一个字段。

【需求】基于上面两种情况的测试发现，当数据中出现了多字节分隔符或者数据中的某个字段包含了分隔符，就会导致数据加载错位的问题。基于出现的问题，我们需要通过特殊的方法来解决该问题，即使当数据中出现多字节分隔符等情况时，Hive 也能正确的加载数据，实现列与数据的一一对应。

1.2 替换源数据分隔符

a. 方案概述

面对情况一，如果数据中的分隔符是多字节分隔符，可以使用程序提前将数据中的多字节分隔符替换为单字节分隔符，然后使用 Hive 加载，就可以实现正确加载对应的数据。

b. 操作步骤

package io.tree6x7.mapreduce.hive;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.conf.Configured;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.Mapper;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.input.TextInputFormat;
import org.apache.hadoop.mapreduce.lib.output.TextOutputFormat;
import org.apache.hadoop.util.Tool;
import org.apache.hadoop.util.ToolRunner;

import java.io.IOException;

/**
 * MapReduce实现将多字节分隔符转换为单字节符
 */
public class ChangeSplitCharMR extends Configured implements Tool {
    public int run(String[] arg) throws Exception {
        // 构建Job
        Job job = Job.getInstance(this.getConf(), "changeSplit");
        job.setJarByClass(ChangeSplitCharMR.class);

        // input：读取需要转换的文件
        job.setInputFormatClass(TextInputFormat.class);
        Path inputPath = new Path("datas/split/test01.txt");
        FileInputFormat.setInputPaths(job, inputPath);

        // map：调用Mapper
        job.setMapperClass(ChangeSplitMapper.class);
        job.setMapOutputKeyClass(Text.class);
        job.setMapOutputValueClass(NullWritable.class);

        // reduce：不需要Reduce过程
        job.setNumReduceTasks(0);

        // output
        job.setOutputFormatClass(TextOutputFormat.class);
        Path outputPath = new Path("datas/output/changeSplit");
        TextOutputFormat.setOutputPath(job, outputPath);

        // 提交Job
        return job.waitForCompletion(true) ? 0 : -1;
    }

    public static void main(String[] args) throws Exception {
        Configuration conf = new Configuration();
        int status = ToolRunner.run(conf, new ChangeSplitCharMR(), args);
        System.exit(status);
    }


    public static class ChangeSplitMapper extends Mapper<LongWritable, Text, Text, NullWritable> {
        private Text outputKey = new Text();
        private NullWritable outputValue = NullWritable.get();

        @Override
        protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {
            // 获取每条数据
            String line = value.toString();
            // 将里面的 || 转换为 |
            String newLine = line.replaceAll("\\|\\|", "|");
            // 替换后的内容作为Key
            this.outputKey.set(newLine);
            // 输出结果
            context.write(this.outputKey, this.outputValue);
        }
    }
}

程序执行结果如下：

此时再重新建表加载数据，即可得到正确的数据结果。

c. 小结

在 ETL 阶段可以直接对数据进行分隔符的替换，通过替换分隔符将多字节分隔符更改为单字节分隔符，就可以解决数据加载的问题，但是这种方式有对应的优缺点，并不是所有的场景适用于该方法。

  • 优点：实现方式较为简单，基于字符串替换即可
  • 缺点：无法满足情况 2 的需求

1.3 RegexSerDe 正则加载

a. 方案概述

[1] 什么是 SerDe？

Hive 的 SerDe 提供了序列化和反序列化两个功能，SerDe 是英文 Serialize 和 Deserilize 的组合缩写，用于实现将 Hive 中的对象进行序列化和将数据进行反序列化。

Serialize 就是序列化，用于将 Hive 中使用的 Java Object 转换成能写入 HDFS 的字节序列，或其他系统能识别的流文件。Hive 中的 insert 语句用于将数据写入 HDFS，所以就会调用序列化实现。Hive 中的调用过程如下：

Deserilize 就是反序列化，用于将字符串或者二进制数据流转换成 Hive 能识别的 Java Object 对象。所有 Hive 中的 select 语句在查询数据时，需要将 HDFS 中的数据解析为 Hive 中对象，就需要进行反序列化。Hive 可以方便的将数据加载到表中而不需要对数据进行转换，这样在处理海量数据时可以节省大量的时间。Hive 中的调用过程如下：

[2] Hive 中包含的 SerDe

官网地址：https://cwiki.apache.org/confluence/display/Hive/SerDe

除了使用最多的 LazySimpleSerDe，Hive 还内置了很多 SerDe 类。多种 SerDe 用于解析和加载不同类型的数据文件，常用的有 ORCSerDe 、RegexSerDe、JsonSerDe 等。

[3] RegexSerDe 的功能

RegexSerDe 是 Hive 中专门为了满足复杂数据场景所提供的正则加载和解析数据的接口，使用 RegexSerDe 可以指定正则表达式加载数据，根据正则表达式匹配每一列数据。面对上述情况 1 和情况 2 的问题，都可以通过 RegexSerDe 使用正则表达式来加载实现。

官网地址：https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-ApacheWeblogData

b. 操作步骤

（1）针对情况 1，分析数据格式，构建正则表达式

  • 原始数据格式：01||周杰伦||中国||台湾||男||七里香
  • 正则表达式定义每一列：([0-9]*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)
  • 使用在线正则校验工具测试

（2）基于正则表达式，使用 RegexSerde 建表

-- 1. 创建表
drop table if exists singer;
create table singer
(
    id       string, --歌手id
    name     string, --歌手名称
    country  string, --国家
    province string, --省份
    gender   string, --性别
    works    string  --作品
)
-- 指定使用 RegexSerde 加载数据
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
-- 指定正则表达式
WITH SERDEPROPERTIES (
    "input.regex" = "([0-9]*)\\|\\|([^}]*)\\|\\|([^}]*)\\|\\|([^}]*)\\|\\|([^}]*)\\|\\|([^}]*)"
);

-- 2. 加载数据
load data local inpath '/home/liujiaqi/hivedata/test01.txt' into table singer;

-- 3. 验证数据，会发现每一列的数据都被正常的加载，没有错位～
select * from singer;

（3）针对情况 2，分析数据格式，构建正则表达式

  • 原始数据格式：192.168.88.100 [08/Nov/2020:10:44:33 +0800] "GET /hpsk_sdk/index.html HTTP/1.1" 200 328
  • 正则表达式定义每一列：([^ ]*) ([^}]*) ([^ ]*) ([^ ]*) ([^ ]*) ([0-9]*) ([^ ]*)
  • 使用在线正则校验工具测试

（4）基于正则表达式，使用 RegexSerde 建表

-- 1. 创建表
drop table if exists apachelog;
create table apachelog
(
    ip     string, --IP地址
    stime  string, --请求时间
    mothed string, --请求方式
    url    string, --请求地址
    policy string, --请求协议
    stat   string, --请求状态
    body   string  --字节大小
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    "input.regex" = "([^ ]*) ([^}]*) ([^ ]*) ([^ ]*) ([^ ]*) ([0-9]*) ([^ ]*)"
);

-- 2. 加载数据
load data local inpath '/home/liujiaqi/hivedata/apache_web_access.log' into table apachelog;

-- 3. 验证数据，会发现时间字段不再被分割为两个字段，整体作为一个字段被加载
select * from apachelog;

c. 小结

RegexSerDe 使用简单，对于各种复杂的数据场景，都可以通过正则定义匹配每行中的每个字段，基本上可以满足大多数场景的需求，工作中推荐使用该方式来实现对于复杂数据的加载。

1.4 自定义 InputFormat

a. 方案概述

Hive 中也允许使用自定义 InputFormat 来解决以上问题，通过在自定义 InputFormat，来自定义解析逻辑实现读取每一行的数据。

b. 操作步骤

（1）自定义 InputFormat

public class MyInputFormat extends TextInputFormat {
    @Override
    public RecordReader<LongWritable, Text> getRecordReader(InputSplit genericSplit, JobConf job,
                                                            Reporter reporter) throws IOException {
        reporter.setStatus(genericSplit.toString());
        MyRecordReader reader = new MyRecordReader(job, (FileSplit) genericSplit);
        return reader;
    }
}

（2）自定义 RecordReader

/**
 * 用于自定义读取器，在自定义 InputFormat 中使用，将读取到的每行数据中的 || 替换为 |
 */
public class MyRecordReader implements RecordReader<LongWritable, Text> {
    private static final Log LOG = LogFactory.getLog(LineRecordReader.class.getName());
    int maxLineLength;
    private CompressionCodecFactory compressionCodecs = null;
    private long start;
    private long pos;
    private long end;
    private LineReader in;
    private Seekable filePosition;
    private CompressionCodec codec;
    private Decompressor decompressor;

    public MyRecordReader(Configuration job, FileSplit split) throws IOException {
        this.maxLineLength = job.getInt("mapred.linerecordreader.maxlength", Integer.MAX_VALUE);
        start = split.getStart();
        end = start + split.getLength();
        final Path file = split.getPath();
        compressionCodecs = new CompressionCodecFactory(job);
        codec = compressionCodecs.getCodec(file);
        FileSystem fs = file.getFileSystem(job);
        FSDataInputStream fileIn = fs.open(split.getPath());
        if (isCompressedInput()) {
            decompressor = CodecPool.getDecompressor(codec);
            if (codec instanceof SplittableCompressionCodec) {
                final SplitCompressionInputStream cIn = ((SplittableCompressionCodec) codec).createInputStream(
                        fileIn, decompressor, start, end, SplittableCompressionCodec.READ_MODE.BYBLOCK);
                in = new LineReader(cIn, job);
                start = cIn.getAdjustedStart();
                end = cIn.getAdjustedEnd();
                filePosition = cIn; // take pos from compressed stream
            } else {
                in = new LineReader(codec.createInputStream(fileIn, decompressor), job);
                filePosition = fileIn;
            }
        } else {
            fileIn.seek(start);
            in = new LineReader(fileIn, job);
            filePosition = fileIn;
        }
        if (start != 0) {
            start += in.readLine(new Text(), 0, maxBytesToConsume(start));
        }
        this.pos = start;
    }

    private boolean isCompressedInput() {
        return (codec != null);
    }

    private int maxBytesToConsume(long pos) {
        return isCompressedInput() ? Integer.MAX_VALUE : (int) Math.min(Integer.MAX_VALUE, end - pos);
    }

    private long getFilePosition() throws IOException {
        long retVal;
        if (isCompressedInput() && null != filePosition) {
            retVal = filePosition.getPos();
        } else {
            retVal = pos;
        }
        return retVal;
    }

    public LongWritable createKey() {
        return new LongWritable();
    }

    public Text createValue() {
        return new Text();
    }

    public synchronized boolean next(LongWritable key, Text value) throws IOException {
        while (getFilePosition() <= end) {
            key.set(pos);
            int newSize = in.readLine(value, maxLineLength, Math.max(maxBytesToConsume(pos), maxLineLength));
            String str = value.toString().replaceAll("\\|\\|", "\\|");
            value.set(str);
            pos += newSize;
            if (newSize == 0) {
                return false;
            }
            if (newSize < maxLineLength) {
                return true;
            }
            LOG.info("Skipped line of size " + newSize + " at pos " + (pos - newSize));
        }
        return false;
    }

    public float getProgress() throws IOException {
        if (start == end) {
            return 0.0f;
        } else {
            return Math.min(1.0f, (getFilePosition() - start) / (float) (end - start));
        }
    }

    public synchronized long getPos() throws IOException {
        return pos;
    }

    public synchronized void close() throws IOException {
        try {
            if (in != null) {
                in.close();
            }
        } finally {
            if (decompressor != null) {
                CodecPool.returnDecompressor(decompressor);
            }
        }
    }

    public static class LineReader extends org.apache.hadoop.util.LineReader {
        LineReader(InputStream in) {
            super(in);
        }

        LineReader(InputStream in, int bufferSize) {
            super(in, bufferSize);
        }

        public LineReader(InputStream in, Configuration conf) throws IOException {
            super(in, conf);
        }
    }
}

（3）打成 jar 包，添加到 Hive 的 classpath 中

add jar /.../hive-inputformat-pro.jar;

（4）基于自定义 Input 创建表

-- 创建表
create table singer
(
    id       string,
    name     string,
    country  string,
    province string,
    gender   string,
    works    string
)
-- 指定使用分隔符为 |
row format delimited fields terminated by '|'
-- 指定使用自定义的类实现解析
stored as
    inputformat 'io.tree6x7.hive.mr.MyInputFormat'
    outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

-- 加载数据
load data local inpath '/home/liujiaqi/hivedata/test01.txt' into table singer;

c. 小结

当数据文件中出现多字节分隔符或者数据中包含了分隔符时，会导致数据加载与实际表的字段不匹配的问题，基于这个问题我们提供了 3 种方案：

  1. 替换分隔符
  2. 正则加载 RegexSerde
  3. 自定义 InputFormat

其中替换分隔符无法解决数据中存在分隔符的问题，自定义 InputFormat 的开发成本较高，所以整体推荐使用「正则加载」的方式来实现对于特殊数据的处理。

2. URL 解析函数及侧视图

2.1 引入

实际工作需求中，我们经常需要对用户的访问、用户的来源进行分析，用于支持运营和决策。例如我们经常对用户访问的页面进行统计分析，分析热门受访页面的 Top10，观察大部分用户最喜欢的访问最多的页面等。又或者我们需要分析不同搜索平台的用户来源分析，统计不同搜索平台中进入网站的用户个数，根据数据进行精准的引导和精准的广告投放。

要想实现上面的受访分析、来源分析等业务，必须在实际处理数据的过程中，对用户访问的 URL 和用户的来源 URL 进行解析处理，获取用户的访问域名、访问页面、用户数据参数、来源域名、来源路径等信息。

在对 URL 进行解析时，我们要先了解 URL 的基本组成部分，再根据实际的需求从 URL 中获取对应的部分，例如一条 URL 由以下几个部分组成：

2.2 URL 解析函数

Hive 中为了实现对 URL 的解析，专门提供了解析 URL 的函数 parse_url 和 parse_url_tuple。

-- 准备数据
$ cat url.txt 
1       http://facebook.com/path/p1.php?query=1
2       http://tongji.baidu.com/news/index.jsp?uuid=allen&age=18
3       http://www.jdwz.com/index?source=baidu
4       http://www.itcast.cn/index?source=alibaba

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

-- 加载数据
load data local inpath '/home/liujiaqi/hivedata/url.txt' into table tb_url;

基于当前的数据，实现对 URL 进行分析，从 URL 中获取每个 ID 对应 HOST、PATH 以及 QUERY，最终实现效果如下：

a. parse_url

parse_url 函数是 Hive 中提供的最基本的 url 解析函数，可以根据指定的参数，从 URL 解析出对应的参数值进行返回，函数为普通的 UDF 函数类型。

将函数应用到本案例当中：

使用 parse_url 函数每次只能解析一个参数，导致需要经过多个函数调用才能构建多列，开发角度较为麻烦，实现过程性能也相对较差，需要对同一列做多次计算处理，我们希望能实现调用一次函数，就可以将多个参数进行解析，得到多列结果。

b. parse_url_tuple

parse_url_tuple 函数是 Hive 中提供的基于 parse_url 的 url 解析函数，可以通过一次指定多个参数，从 URL 解析出多个参数的值进行返回多列，函数为特殊的一对多函数类型，即通常所说的 UDTF 函数类型。

2.3 结合 Lateral View

【UDTF 函数的问题】

Hive 中的一对多的 UDTF 函数可以实现高效的数据转换，但是也存在着一些使用中的问题，UDTF 函数对于很多场景下有使用限制，例如：select 时不能包含其他字段、不能嵌套调用、不能与 GROUP BY 等放在一起调用等等。

UDTF 函数的调用方式，主要有以下两种方式：

  1. 直接在 select 后单独使用
  2. 与 Lateral View 放在一起使用

【Lateral View】

Lateral View 是一种特殊的语法，主要用于搭配 UDTF 类型功能的函数一起使用，用于解决 UDTF 函数的一些查询限制的问题。一般只要使用 UDTF，就会固定搭配 Lateral View 使用。

侧视图的原理是将 UDTF 的结果构建成一个类似于视图的表，然后将原表中的每一行和 UDTF 函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了 UDTF 的使用限制问题。

使用 Lateral View 时也可以对 UDTF 产生的记录设置字段名称，产生的字段可以用于 group by、order by 、limit 等语句中，不需要再单独嵌套一层子查询。

select ... from <tabelName & tableAlias> lateral view [outer] UDTF(xxx) <tableAlias> as col1,col2,col3...

应用到本案例中：

3. 行列转换应用和实现

3.1 多行转多列

CASE WHEN 用于实现对数据的判断，根据条件，不同的情况返回不同的结果，类似于 Java 中的 switch case 功能。

案例实现：

3.2 多行转单列

字符串拼接函数：

案例实现：

3.3 多列转多行

UNION、UNION ALL 关键字：

案例实现：

3.4 单列转多行

explode 函数：

案例实现：

4. JSON 数据处理

4.1 概述

JSON 数据格式是数据存储及数据处理中最常见的结构化数据格式之一，很多场景下公司都会将数据以 JSON 格式存储在 HDFS 中，当构建数据仓库时，需要对 JSON 格式的数据进行处理和分析，那么就需要在 Hive 中对 JSON 格式的数据进行解析读取。

例如，当前我们 JSON 格式的数据如下，每条数据都以 JSON 形式存在，每条数据中都包含 4 个字段，分别为设备名称 device、设备类型 deviceType、信号强度 signal 和信号发送时间 time，现在我们需要将这 4 个字段解析出来，在 Hive 表中以每一列的形式存储，最终得到以下 Hive 表：

Hive 中为了实现 JSON 格式的数据解析，提供了两种解析 JSON 数据的方式，在实际工作场景下，可以根据不同数据，不同的需求来选择合适的方式对 JSON 格式数据进行处理。

（1）使用 JSON 函数进行处理

Hive 中提供了两个专门用于解析 JSON 字符串的函数：get_json_object、json_tuple，这两个函数都可以实现将 JSON 数据中的每个字段独立解析出来，构建成表。

（2）使用 Hive 内置的 JSONSerDe 加载数据

Hive 中除了提供 JSON 的解析函数以外，还提供了一种专门用于加载 JSON 文件的 SerDe 来实现对 JSON 文件中数据的解析，在创建表时指定 SerDe，加载文件到表中，会自动解析为对应的表格式。

4.2 JSON 函数

get_json_object 使用示例：

json_tuple 使用示例：

4.3 JSONSerDe

上述解析 JSON 的过程中是将数据作为一个 JSON 字符串加载到表中，再通过 JSON 解析函数对 JSON 字符串进行解析，灵活性比较高，但是对于如果整个文件就是一个 JSON 文件，在使用起来就相对比较麻烦。Hive 中为了简化对于 JSON 文件的处理，内置了一种专门用于解析 JSON 文件的 SerDe 解析器，在创建表时，只要指定使用 JSONSerDe 解析表的文件，就会自动将 JSON 文件中的每一列进行解析。

不论是 Hive 中的 JSON 函数还是自带的 JSONSerDe，都可以实现对于 JSON 数据的解析，工作中一般根据数据格式以及对应的需求来实现解析。如果数据中每一行只有个别字段是 JSON 格式字符串，就可以使用 JSON 函数来实现处理，但是如果数据加载的文件整体就是 JSON 文件，每一行数据就是一个 JSON 数据，那么建议直接使用 JSONSerDe 来实现处理最为方便。

5. 窗口函数 · 应用

5.1 连续登陆用户

a. 需求&分析

当前有一份用户登录数据如下图所示：

userId 表示唯一的用户 ID，唯一标识一个用户，loginTime 表示用户的登录日期，例如第一条数据就表示 A 在 2021 年 3 月 22 日登录了。

现在需要对用户的登录次数进行统计，得到连续登陆 N（N>=2）天的用户。

  • 例如统计连续两天的登录的用户，需要返回 A 和 C，因为 A 在 22/23/24 都登录了，所以肯定是连续两天登录，C 在 22 和 23 号登录了，所以也是连续两天登录的；
  • 例如统计连续三天的登录的用户，只能返回 A，因为只有 A 是连续三天登录的。

基于以上的需求根据数据寻找规律，要想得到连续登陆用户，必须找到两个相同 userId 的行之间登陆日期之间的关系。

例如：统计连续登陆两天的用户，只要 userId 相等，并且登陆日期之间相差 1 天即可。基于这个规律，我们有两种方案可以实现该需求：

  1. 实现表中的数据自连接，构建笛卡尔积，在结果中找到符合条件的 userId 即可；
  2. 使用「窗口函数 LEAD」来实现。

b. 自连接过滤实现

如果现在需要统计连续 3 天的用户个数，如何实现呢？或者说需要统计连续 5 天、连续 7 天、连续 10 天、连续 30 天登陆的用户如何进行计算呢？

使用自连接的方式会非常的麻烦才能实现统计连续登陆两天以上的用户，并且性能很差，所以我们需要使用第二种方式来实现。

c. 窗口函数实现

当前数据中记录了每个用户每一次登陆的日期，一个用户在一天只有 1 条信息，我们可以基于用户的登陆信息，找到如下规律：

  • 连续两天登陆 ： 用户下次登陆时间 = 本次登陆以后的第二天
  • 连续三天登陆 ： 用户下下次登陆时间 = 本次登陆以后的第三天
  • 依次类推 ……

我们可以对 userId 进行分区，按照登陆时间进行排序，通过 LEAD 函数计算出用户下次登陆时间。通过日期函数计算出登陆以后第二天的日期，如果相等即为连续两天登录。

连续 3 天登陆：

统计连续 N 天登录：

select userid,
       logintime,
       date_add(logintime, N - 1)                                              as nextday,
       lead(logintime, N - 1, 0) over (partition by userid order by logintime) as nextlogin
from tb_login;

5.2 级联累加求和

a. 需求&分析

当前有一份消费数据如下，记录了每个用户在每个月的所有消费记录，数据表中一共有三列：

现在需要基于用户每个月的多次消费的记录进行分析，统计得到每个用户在每个月的消费总金额以及当前累计消费总金额，要求最后结果如下：

以用户 A 为例：

  • A 在 2021 年 1 月份，共 4 次消费，分别消费 5 元、15 元、8 元、5 元，所以本月共消费 33 元，累计消费 33 元；
  • A 在 2021 年 2 月份，共 2 次消费，分别消费 4 元、6 元，所以本月共消费 10 元，累计消费 43 元。

如果要实现以上需求，首先要统计出每个用户每个月的消费总金额，分组实现聚合，然后又需要按照用户 ID 将该用户这个月以及之前的所有月份的消费总金额进行累加。该需求可以通过两种方案来实现：

  1. 分组统计每个用户每个月的消费金额，然后构建自连接，根据条件分组聚合；
  2. 分组统计每个用户每个月的消费金额，然后使用「窗口聚合函数」实现。

b. 自连接分组实现

c. 窗口函数实现

5.3 分组 TopN

a. 需求&分析

工作中经常需要实现 TopN 的需求，例如热门商品 Top10、热门话题 Top20、热门搜索 Top10、地区用户 Top10 等等，TopN 是大数据业务分析中最常见的需求。

普通的 TopN 只要基于数据进行排序，然后基于排序后的结果取前 N 个即可，相对简单，但是在 TopN 中还有一种特殊的 TopN 计算，叫做分组 TopN，其指的是基于数据进行分组，从每个组内取 TopN，不再基于全局取 TopN。如果要实现分组取 TopN 就相对麻烦。

现在有一份数据如下，记录这所有员工的信息：

如果现在有一个需求是查询每个部门薪资最高的员工的薪水，那么可以直接基于表中数据简单分组查询得到。

select deptno, max(salary) from tb_emp group by deptno;

但如果现在需求修改为：统计查询每个部门薪资最高的前两名员工的薪水，这时候应该如何实现呢？

根据上述需求，这种情况下是无法根据 GROUP BY 分组聚合实现的，因为分组聚合只能实现返回一条聚合的结果，但是需求中需要每个部门返回薪资最高的前两名，有两条结果，这时候就需要用到「窗口排序函数」来实现了。

b. 窗口函数实现

  • row_number：对每个分区的数据进行编号，如果值相同，继续编号；
  • rank：对每个分区的数据进行编号，如果值相同，编号相同，但留下空档；
  • dense_rank：对每个分区的数据进行编号，如果值相同，编号相同，不留下空档。

基于 row_number 实现，按照部门分区，每个部门内部按照薪水降序排序：

6. 拉链表的设计与实现

6.1 数据同步问题

Hive 在实际工作中主要用于构建离线数据仓库，定期的从各种数据源中同步采集数据到 Hive 中，经过分层转换提供数据应用。例如，每天需要从 MySQL 中同步最新的订单信息、用户信息、店铺信息等到数据仓库中，进行订单分析、用户分析。

假若 MySQL 中有一张用户表 tb_user，每个用户注册完成以后，就会在用户表中新增该用户的信息，记录该用户的 id、手机号码、用户名、性别、地址等信息。

每天都会有用户注册，产生新的用户信息，我们每天都需要将 MySQL 中的用户数据同步到 Hive 数据仓库中，在做用户分析时，需要对用户的信息做统计分析，例如统计新增用户的个数、总用户个数、用户性别分布、地区分布、运营商分布等指标。

在实现数据仓库数据同步的过程中，我们必须保证 Hive 中的数据与 MySQL 中的数据是一致的，这样才能确保我们最终分析出来的结果是准确的，没有问题的，但是在实现同步的过程中，这里会面临一个问题：如果 MySQL 中的数据发生了修改，Hive 中如何存储被修改的数据？

【解决方案】

（1）在 Hive 中用新的 addr 覆盖 008 的老的 addr，直接更新；

  • 优点：实现最简单，使用起来最方便；
  • 缺点：没有历史状态，008 的地址是 1 月 2 日在 sh，但是 1 月 2 日之前是在 gz 的，如果要查询 008 的 1 月 2 日之前的 addr 就无法查询，也不能使用 sh 代替。

（2）每次数据改变，根据日期构建一份全量的快照表，每天一张表；

  • 优点：记录了所有数据在不同时间的状态
  • 缺点：冗余存储了很多没有发生变化的数据，导致存储的数据量过大

（3）构建拉链表，通过时间标记发生变化的数据的每种状态的时间周期

6.2 拉链表设计

a. 功能与应用

拉链表专门用于解决在数据仓库中数据发生变化如何实现数据存储的问题：如果直接覆盖历史状态，会导致无法查询历史状态；如果将所有数据单独切片存储，会导致存储大量非更新数据的问题。

拉链表的设计是将更新的数据进行状态记录，没有发生更新的数据不进行状态存储，用于存储所有数据在不同时间上的所有状态，通过时间进行标记每个状态的生命周期，查询时根据需求可以获取指定时间范围状态的数据，默认用 9999-12-31 等最大值来表示最新状态。

b. 实现过程

整体实现过程一般分为三步：

  1. 先增量采集所有新增数据（增加的数据和发生变化的数据）放入一张增量表；
  2. 创建一张临时表，用于将老的拉链表与增量表进行合并；
  3. 最后将临时表的数据覆盖写入拉链表中。

对应到本案例：

6.3 案例实现

（1）数据准备

（2）合并数据

（3）覆盖生成最新拉链表

insert overwrite table dw_zipper
select * from tmp_zipper;
posted @ 2023-07-29 23:44  tree6x7  阅读(31)  评论(0)  编辑  收藏  举报