GZ033 大数据应用开发赛题第01套--任务B：离线数据处理（25分）

抽取shtd_store库中user_info的增量数据进入Hive的ods库中表user_info。根据ods.user_info表中operate_time或create_time作为增量字段(即MySQL中每条数据取这两个时间中较大的那个时间作为增量字段去和ods里的这两个字段中较大的时间进行比较)，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前比赛日的前一天日期（分区字段格式为yyyyMMdd）。使用hive cli执行show partitions ods.user_info命令，将结果截图粘贴至客户端桌面【Release\任务B提交结果.docx】中对应的任务序号下；

环境准备
拷贝MySQL驱动到spark目录
cp /opt/apache-hive-3.1.3-bin/lib/mysql-connector-java.jar  /opt/spark-3.2.3-bin-hadoop3.2/jars/
修改MapReduce配置文件

vi /opt/hadoop-3.2.4/etc/hadoop/mapred-site.xml
增加如下配置：

<property>
<name>mapreduce.application.classpath</name>
<value>/opt/hadoop-3.2.4/etc/hadoop:/opt/hadoop-3.2.4/share/hadoop/common/lib/*:/opt/hadoop-3.2.4/share/hadoop/common/*:/opt/hadoop-3.2.4/share/hadoop/hdfs:/opt/hadoop-3.2.4/share/hadoop/hdfs/lib/*:/opt/hadoop-3.2.4/share/hadoop/hdfs/*:/opt/hadoop-3.2.4/share/hadoop/mapreduce/lib/*:/opt/hadoop-3.2.4/share/hadoop/mapreduce/*:/opt/hadoop-3.2.4/share/hadoop/yarn:/opt/hadoop-3.2.4/share/hadoop/yarn/lib/*:/opt/hadoop-3.2.4/share/hadoop/yarn/*</value>
</property>

启动hadoop、spark、hive

/opt/hadoop-3.2.4/sbin/start-all.sh

/opt/spark-3.2.3-bin-hadoop3.2/sbin/start-all.sh

/opt/apache-hive-3.1.3-bin/bin/hive   --service metastore &

导入初始化数据到MySQL

mysql -uroot -p123456 < ./init.sql

mysql -uroot  -p123456
#在MySQL终端执行如下命令查看数据是否导入成功
show databases;
exit


创建ods数据库

cd  /rgsoft/Desktop/Study/task
spark-sql -e "create database ods"

连接spark
spark-shell 


创建ods.user_info表
建表之前需要判断一下表是否存在，如果不存在才创建。涉及到两个概念，一个是ODS。

在数据仓库（Data Warehouse）建模中， ODS（Operational Data Store，操作性数据存储）层 作为数据仓库架构的一部分，通常用于整合来自各种操作性系统的数据。这一层的主要目的是将这些数据整合到一个一致的、标准化的格式中，并进行清洗、转换以确保数据的质量。 一般来说ODS层只需要保存从数据库中抽取来的原始数据，不做任何的更改。 DWD （Data Warehouse Detailed Data） 层用于存储原始、详细的事务性数据，这些数据通常来自于企业的各个操作系统和数据源。这一层包含了数据仓库的底层原子数据，记录了每个操作的细节。 DWS层是DWD层的上一层，用于存储已经汇总和聚合过的数据。这一层的数据是经过处理、加工和聚合的，通常用于支持高层次的分析和决策。
任务中要求分区字段为etl_date，类型为String，且值为当前比赛日的前一天日期（分区字段格式为yyyyMMdd）。也就是说要根据etl_date时间不同，将抽取的数据放到对应etl时间的分区里，那么建表的时候就需要增加etl_date字段，类型为String。 建表语句如下：


spark.sql(
  """
    |create table if not exists `ods`.`user_info` (
    |   `id` int,
    |  `login_name` string,
    |  `nick_name`  string,
    |  `passwd`  string,
    |  `name`  string,
    |  `phone_num`  string,
    |  `email`  string,
    |  `head_img`  string,
    |  `user_level`  string,
    |  `birthday` date,
    |  `gender`  string,
    |  `create_time` timestamp,
    |  `operate_time` timestamp,
    |  `etl_date` string
    |  )
    |partitioned by (`etl_date`)
    |""".stripMargin)

如何在spark中查询MySQL中的数据
需要将MySQL中表映射到sparksql中。

spark.sql(
  """
    | create temporary view user_info_tmp
    |    using org.apache.spark.sql.jdbc
    |options(
    |    url 'jdbc:mysql://127.0.0.1:3306/shtd_store?useSSL=false&useUnicode=true&characterEncoding=utf8',
    |    dbtable 'user_info',
    |    user 'root1',
    |    password '123456'
    |)
    |""".stripMargin)

create temporary view user_info_tmp： 这部分创建了一个临时视图（temporary view）叫做 "user_info_tmp"。这个视图用于在 Spark 中表示和查询 "user_info" 表的数据。

using org.apache.spark.sql.jdbc： 这表明使用了 JDBC 数据源来读取外部数据。具体地，这里使用的是 Spark SQL 的 JDBC 数据源。

options(...)： 这里指定了连接 JDBC 数据源时的一些配置选项。

url 'jdbc:mysql://127.0.0.1:3306/shtd_store?useSSL=false&useUnicode=true&characterEncoding=utf8'： 指定了 MySQL 数据库的连接信息，包括数据库地址、端口以及其他连接参数。
dbtable 'user_info'： 指定了要读取的数据库表，这里是 "user_info"。
user 'root1'： 指定了连接数据库所使用的用户名。
password '123456'： 指定了连接数据库所使用的密码。
stripMargin： 这是 Scala 语言中的一个特性，用于在多行字符串中去掉每行前面的空格。


如何确定更新多少数据
思考两个问题： 第一次导入的时候ods层中没有历史数据，如何处理？ 某段时间没有定时导入数据到ods层中，新增和更新的数据时间使用数据抽取的时间是否合理？ 以上两种问题就会导致数据导入不完整，故而任务要求中说“根据ods_ds_hudi.user_info表中operate_time或create_time作为增量字段(即MySQL中每条数据取这两个时间中较大的那个时间作为增量字段去和ods里的这两个字段中较大的时间进行比较)，只将新增的数据抽入”。意思就是说要根据ODS层中历史数据的最近的操作时间（operate_time或create_time谁最新）来确定从MySQL中抽取哪段时间的数据。所以需要从ods层中查询出最大的操作时间。

select
  case
    when max(`operate_time`) > max(`create_time`) 
    then max(`operate_time`)
    else max(`create_time`)
  end
from `ods_ds_hudi`.`user_info`


case when max(operate_time) > max(create_time) then max(operate_time) else max(create_time) end： 这是一个 CASE 语句，用于根据条件判断选择不同的值。具体地，它比较 operate_time 列的最大值和 create_time 列的最大值，如果 operate_time 大于 create_time，则选择 operate_time 的最大值，否则选择 create_time 的最大值。

如果ods层的表中没有数据，那么这时候查到的值为空，这时候需要给个默认值，sql如下：

select
    coalesce(
      case
        when max(`operate_time`) > max(`create_time`) then max(`operate_time`)
        else max(`create_time`)
      end, ''
    )
from `ods_ds_hudi`.`user_info`

coalesce(..., '')：COALESCE 函数用于返回参数列表中的第一个非空表达式。在这里，如果 CASE 语句的结果为空，那么返回空字符串 '' 在sql中日期的比较可以转换成字符串来比较，将查询到的最大时间转换为字符串，sql如下：

select
  cast(
    coalesce(
      case
        when max(`operate_time`) > max(`create_time`) then max(`operate_time`)
        else max(`create_time`)
      end, ''
    ) as string
  )
from `ods_ds_hudi`.`user_info`

cast(... as string)：CAST 函数用于将结果转换为指定的数据类型。在这里，将前面的结果强制转换为字符串类型。
综合起来，整个查询的目的是从 ods_ds_hudi 数据库的 user_info 表中选择一个字符串值，该值是根据比较 operate_time 和 create_time 列的最大值而确定的。如果 operate_time 的最大值大于 create_time 的最大值，则选择 operate_time 的最大值，否则选择 create_time 的最大值。如果这个值为空，那么返回一个空字符串。

从MySQL中查询操作时间大于等于ODS层中最大时间的数据
将查询的ods层中最大的时间作为查询更新数据的参数，将最大时间查询作为子查询放到where条件中，sql如下：

spark.sql(
  """
    |select
    |   `id`,
    |  `login_name`,
    |  `nick_name`,
    |  `passwd`,
    |  `name`,
    |  `phone_num`,
    |  `email`,
    |  `head_img`,
    |  `user_level`,
    |  `birthday`,
    |  `gender`,
    |  `create_time`,
    |  `operate_time`
    |  from user_info_tmp
    |  where
    |  cast(coalesce(`operate_time`,`create_time`) as string) >=(select cast(coalesce(case when max(`operate_time`) > max(`create_time`) then max(`operate_time`) else max(`create_time`)  end,'') as string)  from `ods`.`user_info` )
    |""".stripMargin).show(false)


where cast(coalesce(operate_time, create_time) as string) >= ...： 这是一个 WHERE 子句，用于过滤数据。它选择了满足条件的行，条件是 operate_time 列或 create_time 列的非空最大值（作为字符串形式）要大于等于子查询的结果。
select cast(coalesce(case when max(operate_time) > max(create_time) then max(operate_time) else max(create_time) end, '') as string) from ods_ds_hudi.user_info``： 这是一个子查询，它计算了 ods_ds_hudi 数据库中 user_info 表中的 operate_time 和 create_time 列的非空最大值。这个值将被用于与外部查询中的条件比较。

将查询到的更新数据插入到ods.user_info表中
结合上面的操作，只需要将查询结果直接插入到ods层的表中即可，sql如下：

spark.sql(
  """
    |insert overwrite`ods`.`user_info` partition(etl_date = '20230901')
    |select
    |   `id`,
    |  `login_name`,
    |  `nick_name`,
    |  `passwd`,
    |  `name`,
    |  `phone_num`,
    |  `email`,
    |  `head_img`,
    |  `user_level`,
    |  `birthday`,
    |  `gender`,
    |  `create_time`,
    |  `operate_time`
    |  from user_info_tmp
    |  where
    |  cast(coalesce(`operate_time`,`create_time`) as string) >=(select cast(coalesce(case when max(`operate_time`) > max(`create_time`) then max(`operate_time`) else max(`create_time`)  end,'') as string)  from `ods`.`user_info` )
    |""".stripMargin)

insert into ods.user_info partition(etl_date = '20230901')： 这部分指定了数据插入的目标表，即 ods_ds_hudi.user_info，并指定了插入的分区为 etl_date = '20230901'。这表示数据将插入到指定分区中。

在hive cli中执行如下命令，查看分区

show partitions `ods`.`user_info`;