你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站,请访问 https://docs.azure.cn

教程:通过使用 Azure Database for PostgreSQL - 超大规模 (Citus)设计多租户数据库

适用于:Azure Database for PostgreSQL - 超大规模 (Citus)

在本教程中,你将了解如何使用 Azure Database for PostgreSQL - 超大规模 (Citus) 来执行以下操作:

  • 创建 Hyperscale (Citus) 服务器组
  • 使用 psql 实用工具创建架构
  • 在节点之间将表分片
  • 引入示例数据
  • 查询租户数据
  • 租户之间共享数据
  • 自定义每租户架构

先决条件

如果没有 Azure 订阅,请在开始之前创建一个免费帐户。

登录到 Azure 门户

登录到 Azure 门户

可以按照以下步骤创建用于 PostgreSQL 的 Azure 数据库:

  1. 在门户左上角选择“创建资源”(+)。

  2. 选择“数据库”>“用于 PostgreSQL 的 Azure 数据库”。 create a resource menu

  3. 选择“超大规模(Citus)服务器组”部署选项。 deployment options

  4. 使用以下信息填写“基本信息”表单:basic info form

    设置 说明
    订阅 要用于服务器的 Azure 订阅。 如果你有多个订阅,请选择要计费的资源所在的订阅。
    资源组 新的资源组名称,或订阅中的现有资源组。
    服务器组名称 标识超大规模服务器组的唯一名称。 域名 postgres.database.azure.com 将追加到所提供的服务器组名称。 服务器名称只能包含小写字母、数字和连字符 (-) 字符。 它的长度必须小于 40 个字符。
    位置 最靠近用户的位置。
    管理员用户名 当前要求为值 citus,并且无法更改。
    密码 服务器管理员帐户的新密码。 该密码必须包含 8 到 128 个字符。 密码必须包含以下三个类别的字符:英文大写字母、英文小写字母、数字 (0 到 9)和非字母数字字符(!, $, #, % 等)。
    版本 除非另有特定的要求,否则为最新 PostgreSQL 主版本。
    计算 + 存储 新服务器的计算、存储和层配置。 选择“配置服务器组”。

    compute and storage

  5. 在本快速入门中,对于“层”,可以接受默认值“基本”。 另一个选项“标准层”创建工作器节点,以实现更大的总数据容量和查询并行性。 有关更深入的比较,请参阅

  6. 在屏幕底部选择“下一步: 网络 >”。

  7. 在“网络”选项卡中,选择“允许从 Azure 内的 Azure 服务和资源公开访问此服务器组”。 然后选择“+ 添加当前客户端 IP 地址”。

    networking configuration

    注意

    Azure PostgreSQL 服务器通过端口 5432 进行通信。 如果尝试从企业网络内部进行连接,则该网络的防火墙可能不允许经端口 5432 的出站流量。 如果是这样,则除非 IT 部门打开端口 5432,否则无法连接到超大规模 (Citus) 群集。

  8. 依次选择“审阅并创建”、“创建”以创建服务器。 预配需要数分钟。

  9. 页面会重定向,以监视部署。 当实时状态从“部署正在进行”变为“部署已完成”时,选择页面左侧的“输出”菜单项。

  10. 输出页将包含协调器主机名,主机名旁边有一个按钮,用于将值复制到剪贴板。 记录此信息以供将来使用。

使用 psql 实用工具创建架构

使用 psql 连接到 Azure Database for PostgreSQL - 超大规模 (Citus)后,可以完成一些基本任务。 本教程将指导你创建 Web 应用,该应用允许广告厂商跟踪他们的广告系列。

多家公司可以使用该应用,让我们创建一个表来记录这些公司,创建另一个表来记录他们的广告系列。 在 psql 控制台中,运行这些命令:

CREATE TABLE companies (
  id bigserial PRIMARY KEY,
  name text NOT NULL,
  image_url text,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL
);

CREATE TABLE campaigns (
  id bigserial,
  company_id bigint REFERENCES companies (id),
  name text NOT NULL,
  cost_model text NOT NULL,
  state text NOT NULL,
  monthly_budget bigint,
  blacklisted_site_urls text[],
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id)
);

注意

本文包含对术语“方块列表”的引用,Microsoft 不再使用该术语。 在从软件中删除该术语后,我们会将其从本文中删除。

每个广告系列会支付运行广告的费用。 此外,添加用于广告的一个表,方法是在以上代码后,运行以下代码:

CREATE TABLE ads (
  id bigserial,
  company_id bigint,
  campaign_id bigint,
  name text NOT NULL,
  image_url text,
  target_url text,
  impressions_count bigint DEFAULT 0,
  clicks_count bigint DEFAULT 0,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, campaign_id)
    REFERENCES campaigns (company_id, id)
);

最后,我们将跟踪有关每个广告的点击次数和展示次数的统计信息:

CREATE TABLE clicks (
  id bigserial,
  company_id bigint,
  ad_id bigint,
  clicked_at timestamp without time zone NOT NULL,
  site_url text NOT NULL,
  cost_per_click_usd numeric(20,10),
  user_ip inet NOT NULL,
  user_data jsonb NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, ad_id)
    REFERENCES ads (company_id, id)
);

CREATE TABLE impressions (
  id bigserial,
  company_id bigint,
  ad_id bigint,
  seen_at timestamp without time zone NOT NULL,
  site_url text NOT NULL,
  cost_per_impression_usd numeric(20,10),
  user_ip inet NOT NULL,
  user_data jsonb NOT NULL,

  PRIMARY KEY (company_id, id),
  FOREIGN KEY (company_id, ad_id)
    REFERENCES ads (company_id, id)
);

现在,你可以在 psql 中看到表列表中新建的表,方法是运行:

\dt

多租户应用程序仅可以对每个租户强制实施唯一性,因此所有主键和外键包含公司 ID。

在节点之间将表分片

超大规模部署基于用户指定的列的值存储不同节点上的表行。 此“分布列”标记哪个租户拥有哪些行。

让我们将分布列设置为 company_id,即租户标识符。 在 psql 中运行以下函数:

SELECT create_distributed_table('companies',   'id');
SELECT create_distributed_table('campaigns',   'company_id');
SELECT create_distributed_table('ads',         'company_id');
SELECT create_distributed_table('clicks',      'company_id');
SELECT create_distributed_table('impressions', 'company_id');

重要

若要利用超大规模性能功能,需要分发表。 如果不分发表,则工作器节点无法帮助运行涉及这些表的查询。

引入示例数据

现在,在 psql 外部,在常规命令行中,下载示例数据集:

for dataset in companies campaigns ads clicks impressions geo_ips; do
  curl -O https://examples.citusdata.com/mt_ref_arch/${dataset}.csv
done

返回到 psql 内部,大容量加载数据。 请务必在下载数据文件的目录中运行 psql。

SET CLIENT_ENCODING TO 'utf8';

\copy companies from 'companies.csv' with csv
\copy campaigns from 'campaigns.csv' with csv
\copy ads from 'ads.csv' with csv
\copy clicks from 'clicks.csv' with csv
\copy impressions from 'impressions.csv' with csv

现在,此数据将跨工作器节点分布。

查询租户数据

当应用程序请求单个租户的数据时,数据库可以在单个工作器节点上执行查询。 单租户查询根据单个租户 ID 进行筛选。 例如,以下查询针对 company_id = 5 筛选广告和展示次数。 尝试在 psql 中运行它并查看结果。

SELECT a.campaign_id,
       RANK() OVER (
         PARTITION BY a.campaign_id
         ORDER BY a.campaign_id, count(*) desc
       ), count(*) as n_impressions, a.id
  FROM ads as a
  JOIN impressions as i
    ON i.company_id = a.company_id
   AND i.ad_id      = a.id
 WHERE a.company_id = 5
GROUP BY a.campaign_id, a.id
ORDER BY a.campaign_id, n_impressions desc;

租户之间共享数据

到目前为止,已按照 company_id 分发所有表,但一些数据不会“自然地”属于任何租户,并且这些数据可以共享。 例如,示例广告平台中的所有公司可能需要获取基于 IP 地址的受众地理位置信息。

创建一个表来记录共享地理位置信息。 在 psql 中运行以下命令:

CREATE TABLE geo_ips (
  addrs cidr NOT NULL PRIMARY KEY,
  latlon point NOT NULL
    CHECK (-90  <= latlon[0] AND latlon[0] <= 90 AND
           -180 <= latlon[1] AND latlon[1] <= 180)
);
CREATE INDEX ON geo_ips USING gist (addrs inet_ops);

接下来,将 geo_ips 设置为“引用表”,存储每个工作器节点上的表副本。

SELECT create_reference_table('geo_ips');

将它和示例数据一同加载。 请记住要从下载数据集的目录内部运行此命令。

\copy geo_ips from 'geo_ips.csv' with csv

将单击次数表和 geo_ips 联接,这在所有节点上都是高效的。 以下是一个用于查找所有在 ad 290 上单击的人的位置的联接。尝试在 psql 中运行查询。

SELECT c.id, clicked_at, latlon
  FROM geo_ips, clicks c
 WHERE addrs >> c.user_ip
   AND c.company_id = 5
   AND c.ad_id = 290;

自定义每租户架构

每个租户可能需要存储他人不需要的特殊信息。 但是,所有租户都共享具有相同数据库架构的同一基础结构。 额外的数据可以到哪儿去?

一个技巧是使用开放式列类型,例如 PostgreSQL 的 JSONB。 我们的架构在 clicks 中有一个 JSONB 字段,称为 user_data。 一家公司(例如 company five)可以使用列来跟踪用户是否使用移动设备。

以下查询用于发现是移动设备访客还是传统访客的点击次数更高。

SELECT
  user_data->>'is_mobile' AS is_mobile,
  count(*) AS count
FROM clicks
WHERE company_id = 5
GROUP BY user_data->>'is_mobile'
ORDER BY count DESC;

可以为单个公司优化此查询,方法是创建部分索引

CREATE INDEX click_user_data_is_mobile
ON clicks ((user_data->>'is_mobile'))
WHERE company_id = 5;

更概括地说,我们可以在列中的每个键和值上创建 GIN 索引

CREATE INDEX click_user_data
ON clicks USING gin (user_data);

-- this speeds up queries like, "which clicks have
-- the is_mobile key present in user_data?"

SELECT id
  FROM clicks
 WHERE user_data ? 'is_mobile'
   AND company_id = 5;

清理资源

在前面的步骤中,你已在服务器组中创建了 Azure 资源。 如果你认为以后不需要这些资源,请删除该服务器组。 在服务器组的“概述”页中,按“删除”按钮 。 弹出页面上出现提示时,请确认服务器组的名称,然后单击最后一个“删除”按钮 。

后续步骤

在本教程中,你学习了如何预配 Hyperscale (Citus) 服务器组。 你已使用 psql 连接到该组,创建了架构并分布了数据。 你已了解如何在租户中和租户之间查询数据,以及如何自定义每租户架构。