原文：
towardsdatascience.com/database-data-transformation-for-data-engineers-6404ed8e6000
使用Kandinsky生成的 AI 图像
在这个故事中，我想讨论我们如何转换数据。无论是数据库、数据仓库还是报告解决方案，我们都是基于数据模型进行数据转换，但我们如何组织它们呢？我想谈谈您使用的现代数据转换工具。我们将触及模块化方法、调度和数据转换测试的一些细微差别。在本文的结尾，我将提供一个示例应用程序，用于运行具有数据溯源和自文档功能的建模任务。我非常想了解您对此的看法。
我见证了数十种运行数据转换的不同方法。在我的超过十五年的大数据和数据分析职业生涯中，我使用不同的设计模式构建了数据管道，我相信还有更多。这就是为什么我如此喜欢技术世界。它提供的众多可能性简直令人惊叹。
您使用哪种操作系统来运行您的数据仓库？
现代数据转换工具
现代数据转换工具，也称为数据建模工具或数据仓库（DWH）操作系统，旨在简化 SQL 数据操作任务，以创建数据集、视图和表。通常，它们使用类似 SQL 的方言来运行我们可能需要的任何可能的数据定义（DDL）和操作（DML），包括数据转换测试和开发模式下的自定义数据集创建。
市场上丰富的 ANSI-SQL 数据仓库解决方案使这些工具变得极其有用。例如，考虑下面这个 dbt 适配器列表。所有市场领导者都在其中。
使用 dbt 创建新的连接。图片由作者提供。
dbt代表数据库构建工具，它本质上是一个可以在本地或服务器上运行的调度应用程序，用于运行数据转换任务。例如，考虑下面这个简单的模型。它在我们数据库中创建一个视图，我们可以每 5 分钟将其物化一次，以保留数据供分析使用。在文件顶部，我们有一些元数据配置，dbt 将在相关数据库和相关模式中运行任务时使用它，即materialized='view'告诉 dbt 它将是一个物化视图。
-- ./models/example/table_a.sql
{{ config(
materialized='view',
tags=["finance"]
) }}
with source_data as (
select 1 as id
union all
select null as id
)
select *
from source_data
拥有此文件后，我们可以简单地从命令行运行调度程序，如下所示：
dbt run --select tag:finance
19:27:51 Running with dbt=1.7.7
19:28:02 Registered adapter: snowflake=1.7.1
19:28:04 Found 2 models, 4 tests, 0 sources, 0 exposures, 0 metrics, 430 macros, 0 groups, 0 semantic models
19:28:04
19:28:12 Concurrency: 1 threads (target='dev')
19:28:12
19:28:12 1 of 1 START sql view model events.table_a ..................................... [RUN]
19:28:16 1 of 1 OK created sql view model events.table_a ................................ [SUCCESS 1 in 3.98s]
19:28:19
19:28:19 Finished running 1view models in 0 hours 0 minutes and 15.42 seconds (15.42s).
19:28:19
19:28:19 Completed successfully
19:28:19
19:28:19 Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
当然，其中还有很多关于实际设置和项目设置的内容。
实际上，它们只是具有模板功能的调度程序
然而，这只是这类工具对我们加载到数据库中的数据所做的大致想法。尽管如此，数据加载是一个完全不同的主题，我之前在这里写过[1]。
这里是另一个类似工具的例子，名为Dataform。它也使用其.sqlx 模板格式创建视图。它还会使用一些元数据来告诉系统如何处理文件，例如使用哪个模式、描述、标签等。
--./definitions/test_v.sqlx
config {
type: "view",
schema: "analytics",
disabled: false,
tags: ["test", "view"],
description: "A view with ."
}
select 2+3 as v
因此，这些工具提供了一系列针对标准 SQL 运行方式的改进。这包括以下内容：
-
SQL 模板 – 我们可以在不同的数据环境中使用相同的模板
-
元数据 – 有用的模板设置，例如标签以及我们可能想要应用到我们的数据库对象或运行的任务上的任何其他内容。
-
调度功能 – 即每 15 分钟运行一些表更新
-
SQL 转换单元测试 – 这是我的个人最爱之一。实际上，并不多见公司测试他们的分析查询，但我认为这是必须的。
-
模式定制 – 对于开发模式非常有用
-
数据血缘和自动生成的文档。
如何使用模板
因此，我们可以看到 SQL 模板是任何数据转换工具的一个基本组成部分。它的目的是将我们的 SQL 查询组织成模块，并在不同的环境或数据库中根据 DRY 方法重复使用。
DRY – 不要重复自己
让我们回到我们第一个“dbt”视图的例子。你可能注意到我们没有在那个视图定义中提到任何模式或数据库。所以这就是这些工具的工作方式 – 他们使用配置文件来应用这些设置！例如，根据我们的目标环境，这个视图表 _a 可以根据我们的数据仓库设计模式[2]在预生产数据库或生产数据库中创建。这是一个非常有用且强大的技术，因为我们可以在完全隔离的开发（预生产）分支中运行我们的 SQL 数据转换开发。所以例如，如果我们使用两个主要数据库前缀（_dev 和 _prod）构建我们的数据仓库，我们的视图将仅在预生产中创建，直到我们将其提升到生产。
DWH 数据库级别。图由作者提供。
应用环境前缀将导致以下 DWH 结构：
生产环境中的数据库。图由作者提供。
DATABASE_NAME SCHEMA_NAME
-------------------------------
RAW_DEV SERVER_DB_1 -- mocked data
RAW_DEV SERVER_DB_2 -- mocked data
RAW_DEV EVENTS -- mocked data
RAW_PROD SERVER_DB_1 -- real production data from pipelines
RAW_PROD SERVER_DB_2 -- real production data from pipelines
RAW_PROD EVENTS -- real production data from pipelines
...
BASE_PROD EVENTS -- enriched data
BASE_DEV EVENTS -- enriched data
...
ANALYTICS_PROD REPORTING -- materialised queries and aggregates
ANALYTICS_DEV REPORTING
ANALYTICS_PROD AD_HOC -- ad-hoc queries and views
因此，通过使用这个简单的模板，我们可以在不同的数据库中创建视图，这使得它成为开发/生产环境分离的理想解决方案。这个话题通常还会涉及到使用 CI/CD 技术进行 SQL 自动化测试。一些非常详细的例子可以在我的某个故事中找到[3]。
数据建模还是数据转换？
这些概念密切相关，因为任何数据转换工具都需要一个模型作为抽象来运行并产生（或实现）一些数据。任何数据模型都可以被视为维度和度量的组合，我们通常使用 SQL 来描述这种组合。例如，在 dbt 中，它实际上被称为模型。考虑以下示例 dbt 项目文件夹结构。我们可以看到我们的视图位于该模型的文件夹中，该模型名为"example"。它提供了一个 schema.yml 文件定义的架构定义，该文件将说明对象、表和视图之间的关系。
.
├── README.md
├── analyses
├── dbt_project.yml
├── logs
│ └── dbt.log
├── macros
├── models
│ └── example
│ ├── schema.yml
│ ├── table_a.sql
│ └── table_b.sql
├── profiles.yml
├── seeds
├── snapshots
├── target
│ ├── compiled
│ ├── graph.gpickle
│ ├── graph_summary.json
│ ├── manifest.json
│ ├── partial_parse.msgpack
│ ├── run
│ ├── run_results.json
│ └── semantic_manifest.json
└── tests
如果我们选择在这个文件夹中添加另一个依赖于我们的第一个视图的视图，我们可以使用模板来描述这种依赖关系。例如，考虑下面 table_b.sql 中的这个 sql。我们将使用 ref 函数告诉我们的系统，table_a 是这个视图或表的依赖。
{{ config(
tags=["example"]
) }}
select *
from {{ ref('table_a') }}
where id = 1
现在，如果我们通过命令行运行dbt docs generate，dbt 将为我们的项目生成带有数据谱系图的文档。dbt docs serve实际上会提供文档网站：
11:29:17 Running with dbt=1.7.7
11:29:17 Registered adapter: snowflake=1.7.1
11:29:17 Found 2 models, 4 tests, 0 sources, 0 exposures, 0 metrics, 430 macros, 0 groups, 0 semantic models
11:29:17
11:29:18 Concurrency: 1 threads (target='dev')
11:29:18
11:29:18 Building catalog
11:29:22 Catalog written to ...
自动生成的谱系图。图片由作者提供。
我认为这是现代数据转换工具我最喜欢的功能之一。它极大地帮助了文档工作，并且经常作为我们数据仓库中所有工作的单一事实来源。数据转换工具使依赖图自动生成，并作为数据目录供内部用户使用。这是一个非常强大的功能，旨在提高数据团队在做什么以及如何解释给任何人的透明度。一个可以搜索表名的依赖图比一千个文档更有价值！我认为这正是整个现代数据工程的核心所在[4]
Dataform 的依赖图和元数据。图片由作者提供。
如何安排表格更新
让我们想象一下，我们有一个表格，我们希望每 15 分钟更新一次其中的新数据。我们可以使用 SQL 中的 MERGE 语句，在我们的模板中定义如下：
config {
type: "operations",
hasOutput: true,
schema: "production",
disabled: false,
name: "user_reputation",
dependencies: ["reputation_data", "reputation_data_v"],
tags: ["user_reputation"],
description: "user_reputation based on reputation_data from firehose"
}
create table if not exists ${self()} (
user_id int64
,updated_at timestamp
)
PARTITION BY DATE(updated_at)
;
merge ${self()} t
using (
select
user_id
, latest_updated_at
from
(
select
user_id
, max(updated_at) as latest_updated_at
from
${ref("reputation_data_v")}
group by
user_id
) y
) s
on t.user_id = s.user_id
when matched then
update set updated_at = s.latest_updated_at, user_id = s.user_id
when not matched then
insert (updated_at, user_id) values (latest_updated_at, user_id)
;
这是我为我的一个表格编写的一个 Dataform 增量表更新示例[5]，但我们是怎样安排它的？
简而言之，像 Dataform 和 dbt 这样的工具是调度器，我们可以通过简单地定义它们的配置文件来使用它们的内置功能来安排某些任务。例如，在 Dataform 中，我们可能希望在名为 environments.json 的文件中创建一个 "cron": "15 * * * *" 调度：
{
"environments": [
{
"name": "test",
"configOverride": {
"defaultSchema": "analytics",
"defaultDatabase": "mydwh-data-tests"
},
"schedules": [
{
"name": "test_workflow_id",
"cron": "00 07 * * *",
"tags": [
"test",
"test"
],
"options": {
"includeDependencies": false
},
"disabled": true,
"gcp": true,
"notify": [
{
"channel": "dataTeamEmails",
"statuses": [
"FAILURE"
]
}
]
}
],
"gitRef": "master"
},
{
"name": "production",
"configOverride": {
"defaultSchema": "analytics",
"defaultDatabase": "mydwh-data"
},
"schedules": [...]
},
{
"name": "staging",
"configOverride": {
"defaultSchema": "analytics",
"defaultDatabase": "mydwh-data"
},
"schedules": [...]
}
]
}
实际上，这是一个数据环境设置的好例子，我们可以看到调度器将使用哪些环境来运行 SQL 脚本，引用每个 git 分支以及可能需要的配置覆盖。在 dbt 中，我们可以使用 dbt cloud 来安排运行，这是一个托管服务，我们可以在其中使用 UI 创建调度，也可以使用 API 发送调度更新的请求。
不仅限于表…
这样我们就可以为任何环境中的数据仓库实例安排创建和更新操作。[6]
好的，这很棒，但如果我们想创建自己的定制调度应用程序来运行数据建模任务怎么办？我们可以有几种方法来实现。一种方法就是使用现有的 dbt 或 Dataform 库来构建一个可以通过 API 调用触发的 API 服务。然后它将运行通过配置文件，选择一个调度并执行它。我们甚至可以使用无服务器架构，使用 Cloud Functions 或 AWS Lambda 来实现。
dbt 示例与 AWS Lambda 和 Terraform
这只是使用 AWS Lambda 和 dbt-core Python 库来完成此操作的示例。需要注意的是，dbt 将在执行过程中生成日志，并且我们需要确保我们的 dbt 项目位于 /tmp/ Lambda 环境文件夹中——这是 Lambda 中唯一的可写位置。
对，没错。AWS Lambda 和 dbt！
另一件需要考虑的事情是，dbt-core 使用多线程 [7]，而 Lambda 不支持多线程，因此我们需要在我们的代码中对任务运行器进行修补，限制它只能使用单个线程。我将使用 Snowflake 作为数据仓库，但这种方法也可以适用于其他任何东西，例如 BigQuery、Redshift 等。创建 Docker 镜像后，我们可以使用 Terraform [8] 进行部署。
我们的 dbt 运行器项目结构如下：
.
├── dbt-runner
│ ├── Dockerfile
│ ├── app.py -- Lambda handler
│ ├── auth.py
│ ├── config
│ ├── dbt_env
│ ├── dbt_packages
│ ├── env_dev.json
│ ├── env_prod.json
│ ├── event.json
│ ├── lambda_config.py
│ ├── package
│ ├── my_snowflake_dwh -- DBT project folder
│ ├── requirements.txt
│ ├── test
│ └── tmp
├── readme.md
└── terraform
├── environment
└── module
我们需要的库在 requirements.txt 中：
dbt-core==1.7.7
dbt-snowflake==1.7.1
python-lambda-local==0.1.13
# boto3==1.34.29
dbt-extractor==0.5.1
dbt-semantic-interfaces==0.4.3
pyyaml==6.0
pytest==8.0.0
我们的主要应用程序文件 app.py 可能看起来像这样：
"""Main app to run data transformations using dbt-core
Can be deployed as AWS Lambda
Can be triggered by any event with cli_args
Can be run locally using event payload from event.json
"""
# Patch multi-threading for Lambda
import queue
import threading
from concurrent.futures import ThreadPoolExecutor
import dbt.flags as dbt_flags
# Override multiprocessing ThreadPool with
# a ThreadPoolExecutor that doesnt use any
# shared memory semaphore locks
class CustomThreadPool:
def __init__(self, num_threads):
self.pool = ThreadPoolExecutor(max_workers=num_threads)
# provide the same interface expected by dbt.task.runnable
def apply_async(self, func, args, callback):
def future_callback(fut):
return callback(fut.result())
self.pool.submit(func, *args).add_done_callback(future_callback)
# we would need to actually keep a "closed"
# attribute lying around and properly check it
def close(self):
pass
# shutdown(wait=True) mimics "join", whereas
# shutdown(wait=False) mimics "terminate"
def join(self):
self.pool.shutdown(wait=True)
import multiprocessing.dummy
multiprocessing.dummy.Pool = CustomThreadPool
# Replace Multiprocessing context with threaded context
# The objects mostly have the same api
class ThreadedContext:
Process = threading.Thread
Lock = threading.Lock
RLock = threading.RLock
Queue = queue.Queue
def get_threaded_context():
return ThreadedContext()
# override both just in case :)
dbt_flags._get_context = get_threaded_context
dbt_flags.MP_CONTEXT = ThreadedContext()
# Third-party dependencies
import os
import logging
from dbt.cli.main import dbtRunner, dbtRunnerResult
# from dbt.logger import log_manager
from distutils.dir_util import copy_tree
# Local dependencies
from auth import get_private_key
from lambda_config import get_work_dir, get_settings
# Environment varibales
ENV = os.environ['ENV']
# provide any other env variables you might need
# ...
def lambda_handler(event, context):
try:
# Copy dbt project folder to /tmp/ Lambda folder to make it writable:
copy_tree('./my_snowflake_dwh', '/tmp/my_snowflake_dwh')
# Get credentials
creds = get_creds(ENV)
# Set dbt secrets defined as secret variables in dbt ./profiles.yml
# i.e.
# profile:
# target: dev
# outputs:
# dev:
# type: snowflake
# ....
#
# user: "{{ env_var('DBT_USER') }}"
# password: "{{ env_var('DBT_PASSWORD') }}"
# ....
os.environ['DBT_USER'] = creds['user']
os.environ['DBT_PASSWORD'] = creds['password']
# Tell dbt what to run using event payload
# create CLI args as a list of strings
env_arg = [f"-t{ENV}"] # environment
# cli_args = ["run", "--select", tags] + env_arg
cli_args = event['cli_args'] + env_arg
print(f'cli_args= {cli_args}')
# Init dbtRunner
dbt = dbtRunner()
# Run
# inspect the results
res: dbtRunnerResult = dbt.invoke(cli_args)
for r in res.result:
print(f"{r.node.database}.{r.node.schema}.{r.node.name}: {r.status}") # noqa E501
except Exception as e:
print(e)
return {
'statusCode': 500,
'body': {'result': e}
}
message = 'Successfully ran dbt models in {}.'.format(ENV)
return {
'statusCode': 200,
'body': {'result': message}
}
现在，我们希望构建一个镜像并将其推送到我们的 AWS ECR 存储库。我们可以使用 shell 脚本来完成这个任务：
REPO_NAME=dbt-runner-staging
AWS_ACCOUNT=0123456789 # you aws account number
SERVICE_NAME_1=dbt-runner
# Get date and time to create unique s3-key for deployment package:
date
TIME=`date +"%Y%m%d%H%M%S"`
# cd ./stack
cd ./dbt-runner
pwd
base=${PWD##*/}
zp=$base".zip"
echo $zp
rm -f $zp
# Install virtualenv and requirements
pip3 install virtualenv==20.17.1
virtualenv dbt_env -p python3.9
source dbt_env/bin/activate
pip3 install -r requirements.txt
echo "VIRTUAL ENV:" $VIRTUAL_ENV
## Alternatively use this (preferable in Python 3):
# cd ./stack
# python3 -m venv stack_env
# source stack_env/bin/activate
# echo "VIRTUAL ENV:" $VIRTUAL_ENV
# source stack_env/bin/activate
# pip install -r stack_requirements.txt
# Login to ECR
aws ecr get-login-password
--region eu-west-1
| docker login
--username AWS
--password-stdin $AWS_ACCOUNT.dkr.ecr.eu-west-1.amazonaws.com
# Create new ecr repo if not exists
ECR_REPO=$(aws ecr describe-repositories --repository-names ${REPO_NAME} --output text)
if [[ $? -eq 254 ]]; then
echo "Creating ECR repository ..."
CREATE_REPO=$(aws ecr create-repository --repository-name ${REPO_NAME} --image-scanning-configuration scanOnPush=true --image-tag-mutability MUTABLE --output text)
echo ${CREATE_REPO}
fi
# 0123456789.dkr.ecr.eu-west-1.amazonaws.com/dbt-runner-staging
# build
docker build --no-cache --platform=linux/amd64 $base .
# # for M1 chip users:
# docker buildx build --platform linux/amd64 -f ./Dockerfile -t $base .
# # keep in mind that AWS Lambda architecture must be set to architectures = ["x86_64"]
# tag and push
docker tag ${base}:latest ${AWS_ACCOUNT}.dkr.ecr.eu-west-1.amazonaws.com/${REPO_NAME}:${TIME}
docker push ${AWS_ACCOUNT}.dkr.ecr.eu-west-1.amazonaws.com/${REPO_NAME}:${TIME}
# Use this as an image URI
echo ${base}:latest ${AWS_ACCOUNT}.dkr.ecr.eu-west-1.amazonaws.com/${REPO_NAME}:${TIME}
现在我们只需要使用这个 Docker 镜像和 AWS Lambda 来部署解决方案。我们的 Terraform Lambda 模块可能看起来像这样：
resource "aws_lambda_function" "dbt_runner" {
depends_on = [
null_resource.ecr_image # create using .sh script above
]
function_name = "dbt-runner-${var.env}"
timeout = 60
runtime = "python3.9"
architectures = ["x86_64"]
image_uri = "${aws_ecr_repository.repo.repository_url}@${data.aws_ecr_image.lambda_image.id}"
package_type = "Image"
role = aws_iam_role.lambda_exec.arn
environment {
variables = {
ENV = var.env
DBT_PROFILES_DIR = "/tmp/my_snowflake_dwh/"
DBT_PROJECT_DIR = "/tmp/my_snowflake_dwh/"
}
}
}
现在，我们可以通过在 event.json 中提供 cli_args 来调用我们的 Lambda 函数，以便 dbt 在 Lambda 中运行：
{
"cli_args": ["run", "--select", "tag:finance tag:example"]
}
调用：
aws lambda invoke
--region=eu-west-1
--function-name dbt-runner-dev out
--cli-binary-format raw-in-base64-out
--payload file://dbt-runner/event.json
--log-type Tail
--query 'LogResult' --output text | base64 -d
执行成功。图片由作者提供。
结论
在数据库、数据仓库或 BI 工具中运行数据模型有各种方法。通常使用标准 SQL 和 cron 作业来完成，这通常被认为不是最佳实践。另一方面，现代数据转换工具与传统的 SQL 技术相比，提供了广泛的功能改进。这包括 SQL 模板、单元测试、自定义模式后缀、数据血缘和自动生成的文档。依赖图和数据血缘是我最喜欢的功能，因为它们在文档编制中非常有帮助，并且经常作为我们数据仓库中所有工作的单一事实来源。这使事情变得简单明了。
话虽如此，现代数据转换工具可以提供一种简单而极其可靠的方法，通过为每个数据环境采用模块化方法来测试和转换数据。数据可以通过多种方式使用多种工具进行管理和转换。使用哪种工具——取决于你的堆栈和业务需求。我见证了许多不同的用例，甚至可能还有更多。这就是为什么我喜欢这项技术。它提供的多种可能性简直令人惊叹。
推荐阅读
[1] medium.com/towards-data-science/advanced-etl-techniques-for-beginners-03c404f0f0ac
[2] medium.com/towards-data-science/data-warehouse-design-patterns-d7c1c140c18b
[3] towardsdatascience.com/unit-tests-for-sql-scripts-with-dependencies-in-dataform-847133b803b7
[4] medium.com/towards-data-science/modern-data-engineering-e202776fb9a9
[5] towardsdatascience.com/advanced-sql-techniques-for-beginners-211851a28488
[7] stackoverflow.com/questions/71255224/how-to-run-dbt-in-aws-lambda
[8] medium.com/towards-data-science/a-guide-to-data-engineering-infrastructure-cb074e0d3f99
扫一扫
2159
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
