GaussDB数据库SQL系列-SQL与ETL浅谈

风晓 2024-01-13 09:04:28  57386 赞同 0 反对 0

分类：资源标签：国产数据库

GaussDB数据库SQL系列-SQL与ETL浅谈

一、前言

在SQL语言中，ETL（抽取、转换和加载）是一种用于将数据从源系统抽取到目标系统的过程。ETL过程通常包括三个阶段：抽取（Extract）、转换（Transform）和加载（Load）。但这些其实都脱离不了数据库系统，本节主要从GaussDB数据库生态出发，给大家简单讲一下SQL 与 ETL的过程与关系。

二、SQL与ETL的概述

SQL（结构化查询语言）

SQL是一种用于管理关系数据库系统的标准编程语言（例如、MySql、GaussDB等）。它用于查询、插入、更新和删除数据库中的数据。SQL语言主要用于数据库管理系统的交互，它并不是一种通用的编程语言，而是专门设计用于操作关系数据库的。

ETL（Extract-Transform-Load）

ETL是一个过程，用于从源系统提取数据，将其转换为目标系统所需的格式，然后将其加载到目标系统库。ETL是数据集成的一部分，用于将分散的、不一致的数据整合到一起，然后通过统一的接口将数据传输到目标系统库进行分析和应用。

ETL是数据库处理数据的重要环节，当在ETL过程中使用SQL时，通常涉及如下图操作。

三、ETL过程中的SQL示例（GaussDB）

本章节涉及到的SQL适用于GaussDB等数据库。

1、提取（Extract）

在ETL过程中，抽取是将数据从源系统中获取并传输到目标系统的第一步。这可能涉及到连接到数据库、读取文件、调用API等操作。在抽取数据时，需要考虑以下几个方面：

数据源的选择：根据具体业务需求选择数据源，并考虑数据量、数据质量、数据类型等因素。
抽取方式的选择：可以选择增量、全量更新等不同的抽取方式。
数据抽取的调度：需要考虑时间、频率、并发等因素，以确保数据的及时性和准确性。

常用SQL语句示例：

1）全量（表）提取

SELECT * FROM source_table;

2）增量提取（例如，根据日期字段，按天、月、年提取，或其他维度）

SELECT * FROM source_table WHERE t_date=’20230907’;

Tip：根据业务需求提取全字段或者指定字段。

2、转换（Transform）

在ETL过程中，转换是对抽取的数据进行清洗、转换、过滤和格式化等操作，以满足目标系统的需求。转换的主要操作包括：

数据清洗：包括去重、填充缺失值、异常值处理等操作，以确保数据的质量和准确性。
数据转换：包括数据类型转换、字段计算、格式化等操作，以使数据符合目标系统的数据结构和数据类型。

常用SQL语句示例：

1）数据行去重

--数据行去重（随机保留或者优先保留）

SELECT order_id, user, product, number

FROM (

SELECT * ,ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY proctime ASC) as row_num FROM Orders)

WHERE row_num = 1;

参数说明：

ROW_NUMBER(): 从第一行开始，依次为每一行分配一个唯一且连续的号码。

PARTITION BY col1[, col2...]: 指定分区的列，例如去重的键。

ORDER BY time_attr [asc|desc]: 指定排序的列。升序（ ASC ）排列指只保留第一行，而降序排列（ DESC ）则指保留最后一行。

WHERE rownum = 1: 取ROW_NUMBER()生成的编号1。

可参：https://blog.csdn.net/GaussDB/article/details/132752614

2）字段清洗（例如：去空格）

通过TRIM()、REPLACE()、CASE WHEN … THEN … END等关键字或函数进行异常字符处理。

--清洗空格

SELECT length(' 去空格 ')

,length(TRIM(' 去空格 '))

,length(REPLACE(' 去空格 ',' ',''))

,length(CASE WHEN ' 去空格 ' <>'去空格' THEN '去空格' END);

说明：

Trim()，通过去空格函数进行清洗

Replace(), 通过替换清洗

case when … then …end 与字典表比对进行清洗，此处的与字典表比对省略，具体根据业务需求进行。

3）非法日期清洗

创建日历表calendar，存储19000101到30001231的所有日期，通过比对判断是否为合规的日期格式。

--与字典表比对

SELECT *,CASE WHEN create_date NOT IN (SELECT c_date FROM calendar) THEN 0 ELSE 1 END status FROM T1

--剔除所有非法日期行

DELETE FROM T1 WHERE status =0;

Tip: 上文写法适合GaussDB等关系型数据库，且都是比较基础的示意说明，具体需要根据业务需要进行编写。

3、加载（Load）

在ETL过程中，加载是将转换后的数据加载到目标系统中，通常是数据仓库或数据集市。加载的主要操作包括：

数据映射。将转换后的数据映射到目标系统中，包括表、字段等。
数据加载。将转换后的数据加载到目标系统中，并进行数据校验、数据整合等操作。

常用SQL语句示例：

1）增量表（累加，字段、表一一映射）

INSERT INTO target_table (column1, column2, column3) SELECT column1, column2, column3 FROM source_table;

2）全量表（全删全插，字段、表一一映射）

--情况目标表

TRUNCATE table target_table;

--全量插入

INSERT INTO target_table (column1,column2,…) SELECT column1,column2,… FROM source_table;

3）作业重跑，清空指定分区数据，重新加载

--清理表分区的数据

--清空分区etl_date

ALTER TABLE orders TRUNCATE PARTITION etl_date;

--或者清空分区etl_date=20230911。

ALTER TABLE orders TRUNCATE PARTITION for (20230911);

--插入新数据

INSERT INTO target_table (column1,column2,…,etl_date) SELECT column1,column2,…,etl_date FROM source_table;

Tip:数据加载涉及到的算法及表设计非常复杂，例如，涉及历史拉链表（关链、开链）、全量表（全删全插）、增量表（累加）等。设计时需要从数仓/数据集市的全局架构出发，确保合理、准确、高效等。

四、附DataArts Studio介绍

华为云GaussDB相关的生态工具DataArts Studio数据治理中心是一个强大的ETL工具和技术，它可以帮助开发人员设计、编写和管理ETL脚本。以下是DataArts Studio在这些方面的主要功能和优势：

可视化的ETL设计：DataArts Studio提供了一个直观的可视化界面，使开发人员能够以图形化方式设计和配置ETL流程。通过拖放组件和连接线，开发人员可以轻松定义数据提取、转换和加载的步骤，而无需编写复杂的代码。
内置的数据转换和处理功能：DataArts Studio提供了丰富的内置转换和处理组件，如数据清洗、数据格式转换、数据合并、数据计算等。开发人员可以直接使用这些组件，而无需自行编写转换逻辑，从而加快开发速度并减少错误。
强大的数据连接和集成能力：DataArts Studio支持与各种数据源的连接和集成，包括关系型数据库、文件系统、云存储、API接口等。开发人员可以轻松地配置数据源连接，并直接从这些数据源中提取数据。
可扩展的脚本编写和管理：虽然DataArts Studio提供了可视化的ETL设计界面，但它也支持自定义脚本编写。开发人员可以使用内置的脚本编辑器编写自定义的ETL脚本，以满足特定的需求。此外，DataArts Studio还提供了ETL脚本的版本控制和管理功能，方便团队协作和脚本的维护。
实时监控和调试：DataArts Studio提供了实时监控和调试功能，开发人员可以实时查看ETL流程的执行状态、数据处理的结果和错误信息。这有助于快速发现和解决问题，提高ETL脚本的质量和可靠性。

五、小结

SQL与ETL的关系在于，SQL语言通常用于ETL过程中的数据提取和转换阶段。通过使用SQL查询语句，可以从源数据库中提取所需的数据，然后使用SQL语句对数据进行必要的转换和处理，以便将其加载到目标系统。

当然了，现在好多企业都有专门的ETL工具，但其实后台都是通过类似“PYTHON + SQL”、“PERL + SQL”等方式实现的，其重点在于ETL过程中的SQL处理。同样，在GaussDB数据库生态中也是不可或缺的，掌握GaussDB数据库相关的SQL写法必不可少。

如果您发现该资源为电子书等存在侵权的资源或对该资源描述不正确等，可点击“私信”按钮向作者进行反馈；如作者无回复可进行平台仲裁，我们会在第一时间进行处理！

评价 0 条

GaussDB数据库SQL系列-SQL与ETL浅谈

二、SQL与ETL的概述

三、ETL过程中的SQL示例（GaussDB）

1、提取（Extract）

2、转换（Transform）

3、加载（Load）

四、附DataArts Studio介绍

五、小结

相关资源

关注我们