抽取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`;