合并来自不同数据库的数据

如何在 Metabase 中合并来自不同数据库的表。

让我们来谈谈为什么 Metabase 不允许人们合并来自多个数据库的数据（并为您提供一些想法和变通方法，如果您绝对需要这样做的话）。

为什么 Metabase 不进行跨数据库连接？

Metabase 不是存储引擎或查询引擎。Metabase 连接到您的数据库，将查询发送到数据库；然后数据库本身执行查询，Metabase 拉取结果并将其可视化。您的数据保留在数据库中，所有处理都在数据库内部进行。

大多数数据库都经过优化，可以高效地处理自身数据的查询，但它们没有原生方式与其他数据库通信。要合并来自两个不同数据库的数据，Metabase 需要将多个数据库的数据拉取到自己的内存中或写入磁盘，然后对这些数据运行查询。这对于小型表可能**可行**，但扩展性不佳（更不用说 Metabase 需要将您的数据存储在数据库**外部**的缺点）。

想象一下，数百人运行需要连接多个数据库的查询，每个数据库都有大量的行，将所有这些数据放入 Metabase 的内存中，并对这些合并后的数据运行未经优化的查询。那会变得非常慢（而且非常昂贵）。

一些其他 BI 工具通过在数据库和 BI 之间创建一个中间层来存储数据，从而解决了这个问题，这通常意味着需要大型、复杂且**昂贵**的部署。

为了保持 Metabase 的轻量和简单，我们尚未将该功能直接内置到我们的产品中（但是！永远不要说永远——时不时查看Metabase 产品路线图）。但这里有一些方法可以设置您的数据，以允许涉及多个数据库的查询，同时保持性能意识并控制您的数据。

最佳解决方案：使用数据仓库

（或数据湖。或数据湖仓。世界任您驰骋。）

要点是：您建立一个 ETL（或 ELT，或 ELTL，您懂的）过程，定期将您使用的所有不同数据库和第三方系统中的数据汇集到一个地方——数据仓库——并使用该数据仓库来支持您的所有分析需求。

优点是：

• 所有数据都已集中在一个地方，您无需通过网络将数据发送到另一个数据库或从另一个数据库接收数据。
• 现代数据仓库的架构针对分析查询进行了优化。
• 您可以完全控制您的数据。

Metabase 可以连接到所有最流行的数据仓库，例如 Snowflake、Redshift、BigQuery、Databricks 等。请查看我应该使用哪个数据仓库？。

要构建数据仓库，您至少需要设置基础设施、构建复制数据的管道以及将数据建模成适合分析的形状——这是一项不小的初始投资。但它创建了一个一致、高性能且可扩展的环境来支持您的分析，因此它将在未来获得回报。

如果您暂时无法投资构建数据仓库，还有一些替代方案

合并仪表板卡片上的系列

如果所有您需要做的是在单个图表上合并两个系列，并且每个系列都基于来自单个数据库的数据，您可以将两个系列添加到仪表板卡片中。

例如，假设您想在同一图表上显示来自“用户”数据库的每月用户数和来自“财务”数据库的每月平均支付金额。您只需将这些系列添加到单个仪表板卡片中即可——无需进行跨数据库连接。

请参阅我们的文档以了解如何合并已保存的问题。

PostgreSQL：使用外部数据包装器

如果您想合并两个 Postgres 数据库（或一个 Postgres 数据库和一些其他数据库）中的数据，并且不想构建数据仓库，您可以使用 外部数据包装器 (FDW)。外部数据包装器允许您的 Postgres 数据库从远程数据存储（包括其他数据库）读取数据。

要从一个 Postgres 数据库查询另一个，您可以使用官方 PostgreSQL 发行版中的 postgres_fdw 扩展。

假设我们有

1. 一个数据库 db1，在 public 模式下有一个表 table1，
2. 一个数据库 db2，在 public 模式下有一个表 table2，

并且我们希望将 table1 和 table2 的数据一起查询。

我们将把 db2 中的表映射到 db1 中的一个模式，这样就可以连接到 db1（例如，使用 Metabase）并运行查询来访问 db2 中的数据。

1. 在您的数据库管理工具中（而不是在 Metabase 中），在 db1 上运行此脚本。请务必替换为您自己的名称和密码。

   ------ Run on DB1: ------
   
   -- Add the postgres_fdw extension
   CREATE EXTENSION postgres_fdw;
   
   -- Create a server object to represent the foreign database
   -- Specify the connection information for DB2 in OPTIONS
   -- In this script we're connecting to a database inside the same server and that's why we use 'localhost'
   CREATE SERVER db2_server
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (host 'localhost', dbname 'db2');
   
    -- Create a user mapping for the foreign server
    -- It maps the user accessing DB1 (for example, metabase_user) to a user accessing DB2 (your_db2_user)
    -- The user in DB1 will use that role to access the remote DB2 server
    CREATE USER MAPPING FOR metabase_user
        SERVER db2_server
        OPTIONS (user 'your_db2_user', password 'your_db2_password');
   
    -- Import public.table2 from DB2 into public schema of DB1.
    -- You can use other schemas or create a new schema specifically for the foreign tables
    IMPORT FOREIGN SCHEMA public
        LIMIT TO (table2)
        FROM SERVER db2_server INTO public;
   
    -- You should be able to query table2 from DB1 now
    SELECT * FROM table2;
   
   

   如果遇到任何问题，请查阅 postgres_fdw 的文档

2. 将 Metabase 连接到您的 PostgreSQL 数据库 db1（您将外部表镜像**到**的那个）。

   建立连接后，您应该在 Metabase 导航侧边栏的“浏览数据”中看到外部表 table2 出现在 db1 中（与 table1 一起）。

3. 现在您可以从连接到 db1 的连接中查询 table1 和 table2，您应该能够使用查询构建器或 SQL 创建连接 table1 和 table2 数据的查询。

还有其他的外部数据包装器——例如从 Postgres 查询 Oracle 或 MySQL 数据库的 FDW——作为第三方工具提供，但如果您决定使用第三方 FDW，请检查它们是否仍在积极维护。查看 PostgreSQL wiki 上的外部数据包装器列表。

MySQL：创建视图

MySQL 提供了一种原生方式来查询同一服务器上不同数据库的数据，使用 database.schema.table.field 等语法。但在 Metabase 中，当您连接到一个数据库（我们称之为 db1）时，Metabase 不会知道在同一服务器上存在另一个数据库 db2，因此您将无法在 db1 上运行引用 db2 的查询。

解决方法是在 db1 内部创建一个视图，镜像来自 db2 的数据。

所以假设您有

1. 一个数据库 db1，其中包含表 table1，
2. 一个数据库 db2，其中包含表 table2，

并且您想连接 table1 和 table2 中的数据。

1. 在您的数据库管理工具中（而不是在 Metabase 中），在 db1 中创建一个视图，该视图从 db2 中选择 table2。

   ------ Run on DB1: ------
   
   -- Create a view for db2.table2 inside db1.
   CREATE VIEW table2 AS
       SELECT * FROM db2.table2;
   

2. 将 Metabase 连接到您的 MySQL 数据库 db1（您在其中创建视图的那个）。

   建立连接后，您应该在 Metabase 导航侧边栏的“浏览数据”中看到视图 table2 出现在 db1 中（与 table1 一起）。

3. 现在您可以从连接到 db1 的连接中访问 table1 和 table2，您应该能够使用查询构建器或 SQL 创建连接 table1 和 table2 数据的查询。

Snowflake：创建视图

就像 MySQL 一样，Snowflake 提供了一种使用 database.schema.table 等语法从不同数据库查询数据的原生方式。但在 Metabase 中，当您连接到一个数据库（我们称之为 db1）时，Metabase 不会知道存在另一个数据库 db2，因此您将无法在 db1 上运行引用 db2 的查询。

解决方法是在 db1 内部创建一个视图，镜像来自 db2 的数据。所以假设您有

1. 一个数据库 db1，在 public 模式下有一个表 table1，
2. 一个数据库 db2，在 public 模式中有一个表 table2，

并且您想连接 table1 和 table2 中的数据。

1. 在您的数据库管理工具中（而不是在 Metabase 中），在 db1 中创建一个视图，该视图从 db2 中选择 table2。

   ------ Run on DB1: ------
   
   -- Create a view for db2.public.table2 inside db1.
   CREATE VIEW table2 AS
       SELECT * FROM db2.public.table2;
   

2. 将 Metabase 连接到您的 Snowflake 数据库 db1（您创建视图的那个）。

   建立连接后，您应该在 Metabase 导航侧边栏的“浏览数据”中看到视图 table2 出现在 db1 中（与 table1 一起）。

3. 现在您可以从连接到 db1 的连接中访问 table1 和 table2，您应该能够使用查询构建器或 SQL 创建连接 table1 和 table2 数据的查询。

使用联邦查询引擎

联邦查询引擎的作用是为您提供一个统一的接口来查询和分析来自不同数据源的数据。您可以将联邦查询引擎连接到多个数据库，然后将您的 Metabase 连接到联邦引擎，并通过它查询所有数据，就像它们都在一个数据库中一样。

它不会像专用数据仓库那样快，因为数据仓库可以更有效地存储数据并优化查询，但它可能是一个不错的中间解决方案。

Metabase 可以连接到几个流行的联邦查询引擎：Presto、Trino 和 Starburst，以及 Athena。

检查您的数据库功能

如果您使用的数据库不是 PostgresSQL 或 MySQL，并且您无法使用联合查询引擎或构建数据仓库，请咨询您的特定数据库——它可能已经具备您所需的功能来解决您的特定用例。

想法是：如果您的数据库有一种方法可以从其他数据库读取数据，那么您可以使用该功能将数据从 db2 获取到 db1 中，然后将您的 Metabase 连接到 db1，db2 中的数据就会在 db1 中显示出来，可供查询（和连接）。

例如，BigQuery 外部表或 Redshift 联合查询可能适用于某些用例。Databricks 和 ClickHouse 也提供类似的功能。