使用 dbt、Snowflake 和 Preset 分析 CAZy
如何使用现代数据堆栈进行生物信息学
碳水化合物活性酶 (CAZy) 数据库是一个门户网站，提供有关合成、修饰或降解碳水化合物的酶 (CAZymes) 的信息。对于基因组科学家来说，它是非常宝贵的资源。他们使用 CAZy 数据库来识别新的 CAZymes，预测它们的功能并研究新的基因组。在我之前的文章 Graph Database, GraphQL and Machine Learning for Carbohydrate-Active Enzymes 中，我将 CAZy 放入 Neo4j 图中，对其进行分析并使用图机器学习来获得一些新的见解。[0][1]
但是，从 CAZy 下载数据非常困难。它的前身是一个纯文本网站。尽管最新的更新在其网页界面中添加了下载按钮，但可下载的内容仍然只是我们在网站上看到的部分内容。此外，CAZy 团队准备了不错的克朗图，但没有告诉我们如何重现它们。[0]
让我们把它变成一个很好的生物信息学练习。在本文中，我将从 CAZy 下载数据，将它们导入 Snowflake，通过数据构建工具 (dbt) 提取一些 CAZy 系列的出租车，并在 Snowflake 和 Preset 中生成克朗图和仪表板。[0][1]
是的，对于这么小的任务，dbt+Snowflake 看起来有点矫枉过正。我们完全可以使用 Jupyter notebook 或 SQLite 项目，甚至只是几个 Bash 命令来完成同样的事情。但我不禁陷入了数据仓库和现代数据堆栈的热门话题。在我完成了 Toth 等人在 Udemy 的优秀 dbt 课程后，我想探索数据仓库到底是什么，以及我是否可以将它用于我未来的项目。此外，完全在云上处理大量数据也有一定的吸引力。 CAZy 数据是一个很好的入门资料。您可以在我的 GitHub 存储库中找到此项目的代码。[0]
1. Snowflake
首先，您需要一个 Snowflake 帐户。您可以在此处注册试用。选择标准版和 AWS 作为您的云提供商。通过电子邮件激活您的帐户并创建您自己的用户名和密码。然后雪花会再次向您发送电子邮件。在两封电子邮件中，您都会找到以下格式的帐户 URL：[0]
https://[random_string.region].snowflakecomputing.com请注意，[random_string.region]为您的帐户名。所以，复制和在笔记本贴上您的帐户名，因为你将有很多会用到。
进入 Snowflake 的界面后，单击 + Worksheet。
在工作表中，您现在可以执行 SQL 查询。让我们首先使用以下 SQL 查询为我们的 Snowflake 数据库创建一个用户及其角色。
上面的查询创建了一个密码为 dbtPassword123 的用户 dbt。它具有角色转换，数据仓库称为 COMPUTE_WH。然后查询创建一个名为 CAZY 的数据库和一个模式 RAW。
留在同一个工作表中，粘贴并执行以下查询以将数据导入数据库。
-- Set up the defaults
USE WAREHOUSE COMPUTE_WH;
USE DATABASE cazy;
USE SCHEMA RAW;
CREATE OR REPLACE TABLE raw_cazy_data
(cazy string,
superkingdom string,
name string,
accession string);
COPY INTO raw_cazy_data (cazy,
superkingdom,
name,
accession)
from 's3://datathon-medium-file/cazy_data_formatted.csv.gz'
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"' skip_header = 1);
CREATE OR REPLACE TABLE raw_cazy_genome
(name string,
taxid string,
superkingdom string,
phylum string,
class string,
"ORDER" string,
family string,
genus string,
species string);
COPY INTO raw_cazy_genome (name,
taxid,
superkingdom, phylum, class, "ORDER", family, genus, species)
from 's3://datathon-medium-file/cazy_genome.csv.gz'
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"' skip_header = 1);
这些查询创建两个表 RAW_CAZY_DATA 和 RAW_CAZY_GENOME。然后他们从我的 S3（公共）中获取两个 CSV 文件并将它们导入表中。您可以在 Snowflake 中预览这两个表。第一个文件是从 CAZy 下载的。在第二个文件中，我在 pyphy 的帮助下将 taxed 扩展为 NCBI 分类，它的云版本在我的第一篇文章中进行了描述。[0]
2. dbt
dbt 是一个数据转换器。您首先将 dbt 与您的数据仓库（如 Snowflake）连接起来。然后，您开始通过在模型文件中编写 SQL SELECT 查询来提取、组合和转换数据。每个模型通常会在称为物化的过程中转换为数据仓库中的视图或表（还有其他两种称为“增量”和“短暂”的物化形式）。虽然开发人员在本地编写 dbt 代码，但计算完全在云端完成。最后，您可以使用仪表板可视化转换后的数据。
在这个项目中，我们想要研究糖苷水解酶家族 17 (GH17) 的分类组成。也就是说，我们要计算每个分类单元中 GH17 基因的数量。因为我们将在克朗图和仪表板中可视化结果，所以我们将在 dbt 中编写两个模型：family_taxid 和 family_taxon。
在本地计算机上安装 dbt-snowflake。运行以下命令来启动一个 dbt 项目：[0]
dbt init cazy按照提示完成注册。您将需要您的帐户名称。然后你可以填写你的用户名dbt、密码dbtPassword123、角色转换、仓库COMPUTE_WH、数据库cazy和模式dev。我用了4个线程。完成后，dbt 将创建两个文件夹：cazy 和 log。删除模型文件夹下的示例文件夹。同时删除 dbt_project.yml 中的最后两行（例如：+materialized: view）。
此外，我想为表 RAW_CAZY_DATA 定义一个别名“gene”，为 RAW_CAZY_GENOME 定义一个别名“genome”。将以下内容复制并粘贴到名为 source.yml 的文件中，并将其放在模型文件夹下。
version: 2
sources:
- name: cazy
schema: raw
tables:
- name: gene
identifier: raw_cazy_data
- name: genome
identifier: raw_cazy_genome
2.1 Krona 的family_taxid 模型
现在在模型文件夹下创建一个名为 family_taxid.sql 的文件。它应该有以下内容。
{{ config(alias=var("family") + "_taxid") }}
WITH
gene AS (
SELECT accession, cazy, name
FROM
{{ source('cazy', 'gene') }}
),
genome AS (
SELECT taxid, name
FROM {{ source('cazy', 'genome') }}
)
SELECT
genome.taxid,
gene.accession
FROM genome, gene
WHERE genome.name = gene.name AND gene.cazy = '{{ var("family") }}' AND genome.taxid IS NOT NULL该模型在调用时需要用户提供 CAZy 家族名称。然后它将在 Snowflake 的 DEV 模式中创建一个名为 [CAZy_family]_taxid（第 1 行）的视图。 SQL 查询以两个“WITH”子句开始。他们从两个表中选择必要的列：基因和基因组（第 3 到 12 行）。这两个表通过名称匹配在“WHERE”子句中连接。 WHERE 子句仅保留来自用户指定的 CAZy 系列的数据。它还确保 Taxid 不为空（第 17 行）。因为 Krona 可以单独使用出租车，所以我的 SELECT 子句只选择出租车和加入作为输出（第 14 和 15 行）。
2.2 family_taxon 模型
同样，taxid_taxon.sql模型看起来是这样的。
{{ config(alias=var("family") + "_taxon") }}
WITH
gene AS (
SELECT name, cazy, accession
FROM
{{ source('cazy', 'gene') }}
),
genome AS (
SELECT name, superkingdom, phylum, class, genus
FROM {{ source('cazy', 'genome') }}
)
SELECT
gene.accession,
genome.superkingdom,
genome.phylum,
genome.class,
genome.genus
FROM genome, gene
WHERE genome.name = gene.name AND gene.cazy = '{{ var("family") }}' AND genome.superkingdom IS NOT NULL AND genome.phylum IS NOT NULL AND genome.class IS NOT NULL AND genome.genus IS NOT NULL与family_taxid模型相比，family_taxon模型需要superkingdom、phylum、class和genus数据来代替taxid。 Snowflake 和 Preset 都将使用这些列来生成图表。
2.3 Run dbt
现在是时候运行 dbt 并创建视图了。在 cazy 文件夹中运行以下命令。
#Linux and Mac users run this
dbt run --vars '{"family": "GH17"}'
#windows users run this
dbt run --vars '{"family":"GH17"}' 当一切顺利时，您应该会看到这样的控制台输出。
现在去你的雪花并刷新。您应该在 DEV 模式下看到两个视图。您也可以预览数据（图 6）。
3. Krona plot
现在从这里下载最新的 KronaTools 版本。您需要运行它的 updateTaxonomy.sh。它将从 NCBI 下载 taxdump.tar.gz 并对其进行预处理。但是，我建议您在这里自己下载文件并将其放在 KronaTools 的分类文件夹下，因为脚本每次都无法下载。[0][1]
我们现在需要将我们的 GH17_TAXID 视图从 Snowflake 下载到我们的本地计算机。据我所知，从 Snowflake 下载数据的唯一方法是运行这样的 Python 脚本。
import snowflake.connector
import pandas as pd
import sys
family = sys.argv[1]
con = snowflake.connector.connect(
user='dbt',
password='dbtPassword123',
account='[your_snowflake_account_name]',
database='cazy',
warehouse='COMPUTE_WH',
role='transform',
schema='dev'
)
cur = con.cursor()
sql = f'select * from {family}_TAXID'
cur.execute(sql)
df = cur.fetch_pandas_all()
df.to_csv(f'{family}.tsv', index=False, sep="\t")你可以像这样运行它。
python download_snowflake.py GH17此运行会在当前文件夹下生成一个名为 GH17.tsv 的 TSV 文件。
现在我们使用以下命令生成克朗图。 “-q 2”表示我们的查询标识符在第二列，而“-t 1”表示出租车在我们的 GH17.tsv 文件的第一列。
perl [path to scripts/ImportTaxonomy.pl] [path to GH17.tsv] -q 2 -t 1您可以在 KronaTools 文件夹内的脚本文件夹下找到 ImportTaxonomy.pl。此命令将生成一个 HTML 文件。打开该 HTML 文件，您可以看到 GH17 的克朗图。
从图中可以看出，大约一半的 GH17 酶来自真核生物，另一半来自细菌。变形菌，特别是伽玛变形菌，占据了最大的份额。值得一提的是，我们的克朗图与 CAZy 中的官方图不同。我们的图表计算了每个分类单元下不同的 CAZymes，而官方图表只计算了不同的分类单元。当然，我们也可以在我们的 dbt 模型中使用 DISTINCT 关键字来重现官方的。
4. Dashboard
或者，我们可以在云上完全可视化我们的数据。 Snowflake 本身有一个仪表板选项卡，我们可以在其中快速生成一些图表。
4.1 Dashboard in Snowflake
单击在 Snowflake 中打开仪表板选项卡并添加一个名为“cazy”的仪表板。然后添加一个新的倾斜。在查询编辑器中，输入并执行 SQL 查询，例如：
SELECT genome.class from CAZY.DEV.GH17_TAXON as genome选择图表并确保图表类型为条形。您应该会看到一个条形图，其中显示了类级别的分类细分。
然而，Snowflake 的图表对我来说有点初级。所以让我们转向预设以获得更多选项。
4.2 Preset
登录或注册您的预设帐户。进入内部后，创建一个名为 GH17 的空仪表板。然后创建一个 Workspace 并将您的 Snowflake 数据库添加到其中。[0]
然后通过单击 Data/Datasets 下的 +DATASET 添加数据集。在 DATABASE、SCHEMA 和 TABLE 下输入正确的值。
选择新创建的数据集，创建一个 Sunburst 图。将超级王国、门、类和属拖放到 HIERARCHY 下，将 COUNT(*) 拖放到 PRIMARY METRIC 下。更新图表，保存并将其添加到您的仪表板。您可以看到，当 Preset 尝试将长名称压缩到面包屑中时，Preset 在仪表板模式下仍有一些视觉错误需要修复。
为什么停在那里？您可以在 GH17 仪表板中添加更多图表，以显示数据的不同方面。您甚至可以在树状图中启用仪表板交叉过滤器。
Conclusion
本文只是触及了dbt、Snowflake和Preset的一点点皮毛。 dbt 将模块化、可移植性、CI/CD 和文档等软件工程最佳实践引入数据工程。有很多地方可以改进这个项目。例如，我们应该为数据转换和数据可视化创建单独的角色。我们也可以在 dbt 中创建仪表板。我们可以通过曝光将 Preset 仪表板插入 dbt 仪表板。我们可以使用“dbt docs”命令为我们的项目创建一个文档页面。如果您没有 Preset 帐户但拥有 Tableau，您也可以将 Tableau 与 Snowflake 驱动程序一起使用来分析 Snowflake 数据。
所以请尝试这个项目并分享你的经验。
文章出处登录后可见！
