Tutorial: Diseño de una base de datos multiinquilino con Azure Database for PostgreSQL Hiperscala (Citus) (versión preliminar)Tutorial: design a multi-tenant database by using Azure Database for PostgreSQL – Hyperscale (Citus) (preview)

En este tutorial, usará Azure Database for PostgreSQL Hiperscala (Citus) (versión preliminar) para aprender lo siguiente:In this tutorial, you use Azure Database for PostgreSQL - Hyperscale (Citus) (preview) to learn how to:

  • Creación de un grupo de servidores Hiperescala (Citus)Create a Hyperscale (Citus) server group
  • Uso de la utilidad psql para crear un esquemaUse psql utility to create a schema
  • Particiones de tablas entre nodosShard tables across nodes
  • Ingesta de datos de ejemploIngest sample data
  • Consulta de datos de inquilinoQuery tenant data
  • Datos compartidos entre los inquilinosShare data between tenants
  • Personalización de esquema por inquilinoCustomize the schema per-tenant

Requisitos previosPrerequisites

Si no tiene una suscripción a Azure, cree una cuenta gratuita antes de empezar.If you don't have an Azure subscription, create a free account before you begin.

Inicio de sesión en Azure PortalSign in to the Azure portal

Inicie sesión en el Azure Portal.Sign in to the Azure portal.

Creación de una instancia de Azure Database for PostgreSQL: Hiperescala (Citus)Create an Azure Database for PostgreSQL - Hyperscale (Citus)

Para crear un servidor de Azure Database for PostgreSQL, siga estos pasos:Follow these steps to create an Azure Database for PostgreSQL server:

  1. Haga clic en Crear un recurso de la esquina superior izquierda de Azure Portal.Click Create a resource in the upper left-hand corner of the Azure portal.

  2. En la página Nuevo, seleccione Bases de datos y, en la página Bases de datos, seleccione Azure Database for PostgreSQL.Select Databases from the New page, and select Azure Database for PostgreSQL from the Databases page.

  3. Para la opción de implementación, haga clic en el botón Crear en Hyperscale (Citus) server group - PREVIEW (Grupo de servidores Hiperescala (Citus): VERSIÓN PRELIMINAR).For the deployment option, click the Create button under Hyperscale (Citus) server group - PREVIEW.

  4. Rellene el formulario del nuevo servidor con la siguiente información:Fill out the new server details form with the following information:

    • Grupo de recursos: haga clic en el vínculo Crear nuevo que está debajo de este cuadro de texto para este campo.Resource group: click the Create new link below the text box for this field. Escriba un nombre como myresourcegroup.Enter a name such as myresourcegroup.
    • Nombre del grupo de servidores: escriba un nombre exclusivo para el nuevo grupo de servidores, que también se usará para un subdominio de servidor.Server group name: enter a unique name for the new server group, which will also be used for a server subdomain.
    • Nombre de usuario de administrador: actualmente, se necesita el valor citus y no se puede cambiar.Admin username: currently required to be the value citus, and can't be changed.
    • Contraseña: tiene que tener al menos ocho caracteres de las tres categorías siguientes: letras mayúsculas del alfabeto inglés, letras minúsculas del alfabeto inglés, números (0-9) y caracteres no alfanuméricos (!, $, # o %, entre otros)Password: must be at least eight characters long and contain characters from three of the following categories – English uppercase letters, English lowercase letters, numbers (0-9), and non-alphanumeric characters (!, $, #, %, and so on.)
    • Ubicación: use la ubicación más cercana a los usuarios para que puedan acceder de la forma más rápida posible a los datos.Location: use the location that is closest to your users to give them the fastest access to the data.

    Importante

    Se precisa la contraseña de administrador del servidor que especifique aquí para iniciar sesión en el servidor y sus bases de datos.The server admin password that you specify here is required to log in to the server and its databases. Recuerde o grabe esta información para su uso posterior.Remember or record this information for later use.

  5. Haga clic en Configurar grupo de servidores.Click Configure server group. No modifique los valores de esa sección y haga clic en Guardar.Leave the settings in that section unchanged and click Save.

  6. Haga clic en Revisar y crear y luego en Crear para aprovisionar el servidor.Click Review + create and then Create to provision the server. El aprovisionamiento tarda unos minutos.Provisioning takes a few minutes.

  7. La página irá a la supervisión de la implementación.The page will redirect to monitor deployment. Cuando el estado activo cambia de La implementación está en curso a Se completó la implementación, haga clic en el elemento de menú Salidas que se encuentra a la izquierda de la página.When the live status changes from Your deployment is underway to Your deployment is complete, click the Outputs menu item on the left of the page.

  8. La página de resultados incluirá un nombre de host de coordinación junto a un botón para copiar el valor en el Portapapeles.The outputs page will contain a coordinator hostname with a button next to it to copy the value to the clipboard. Anote esta información para usarla más adelante.Record this information for later use.

Configuración de una regla de firewall de nivel de servidorConfigure a server-level firewall rule

El servicio Azure Database for PostgreSQL Hiperescala (Citus) (versión preliminar) usa un firewall en el nivel de servidor.The Azure Database for PostgreSQL – Hyperscale (Citus) (preview) service uses a firewall at the server-level. De manera predeterminada, el firewall impide que todas las herramientas y aplicaciones externas se conecten al nodo de coordinación y a cualquier base de datos interna.By default, the firewall prevents all external applications and tools from connecting to the coordinator node and any databases inside. Tenemos que agregar una regla para abrir el firewall en un intervalo específico de direcciones IP.We must add a rule to open the firewall for a specific IP address range.

  1. Desde la sección Salidas donde anteriormente copió el nombre de host del nodo de coordinación, haga clic en Atrás para volver al elemento de menú Información general.From the Outputs section where you previously copied the coordinator node hostname, click back into the Overview menu item.

  2. Busque el nombre del grupo de servidores de la implementación y haga clic en él.Find the name of your deployment's server group and click it. (El nombre del grupo de servidores no tendrá un sufijo.(The server group name will not have a suffix. Los elementos con nombres que terminan en, por ejemplo, "-c", "-w0" o "-w1" no son el grupo de servidores).Items with names ending in, for example, "-c", "-w0", or "-w1" are not the server group.)

  3. Haga clic en Firewall en Seguridad, en el menú de la izquierda.Click Firewall under Security in the left-hand menu.

  4. Haga clic en el vínculo + Agregar regla de firewall para la dirección IP del cliente actual.Click the link + Add firewall rule for current client IP address.

  5. Por último, haga clic en el botón Guardar.Finally, click the Save button.

    Nota

    El servidor Azure PostgreSQL se comunica a través de puerto 5432.Azure PostgreSQL server communicates over port 5432. Si intenta conectarse desde una red corporativa, es posible que el firewall de la red no permita el tráfico saliente a través del puerto 5432.If you are trying to connect from within a corporate network, outbound traffic over port 5432 may not be allowed by your network's firewall. En ese caso, no puede conectarse al servidor de Azure SQL Database, salvo que el departamento de TI abra el puerto 5432.If so, you cannot connect to your Azure SQL Database server unless your IT department opens port 5432.

Conexión a la base de datos mediante psqlConnect to the database using psql

Al crear el servidor de Azure Database for PostgreSQL, también se crea la base de datos predeterminada denominada citus.When you create your Azure Database for PostgreSQL server, a default database named citus is created. Para conectarse al servidor de bases de datos, necesita una cadena de conexión y la contraseña de administrador.To connect to your database server, you need a connection string and the admin password.

  1. Obtenga la cadena de conexión.Obtain the connection string. En la página del grupo de servidores, haga clic en el elemento de menú Cadenas de conexión.In the server group page click the Connection strings menu item. (Se encuentra en Configuración). Busque la cadena marcada como C++ (libpq) .(It's under Settings.) Find the string marked C++ (libpq). Tendrá el formato:It will be of the form:

    host=hostname.postgres.database.azure.com port=5432 dbname=citus user=citus password={your_password} sslmode=require
    

    Copie la cadena.Copy the string. Deberá reemplazar "{su_contraseña}" por la contraseña administrativa que eligió anteriormente.You will need to replace "{your_password}" with the administrative password you chose earlier. El sistema no almacena la contraseña de texto no cifrado y, por lo tanto, no puede mostrarla en la cadena de conexión.The system doesn't store your plaintext password and so can't display it for you in the connection string.

  2. Abra una ventana del terminal en el equipo local.Open a terminal window on your local computer.

  3. En el símbolo de sistema, conéctese al servidor de Azure Database for PostgreSQL con la utilidad psql.At the prompt, connect to your Azure Database for PostgreSQL server with the psql utility. Pase la cadena de conexión entre comillas, asegurándose de que contiene la contraseña:Pass your connection string in quotes, being sure it contains your password:

    psql "{connection_string}"
    

    Por ejemplo, el siguiente comando se conecta al nodo de coordinación del grupo de servidores mydemoserver:For example, the following command connects to the coordinator node of the server group mydemoserver:

    psql "host=mydemoserver-c.postgres.database.azure.com port=5432 dbname=citus user=citus password={your_password} sslmode=require"
    

Uso de la utilidad psql para crear un esquemaUse psql utility to create a schema

Una vez conectado a Azure Database for PostgreSQL Hiperescala (Citus) (versión preliminar) mediante psql, puede completar algunas tareas básicas.Once connected to the Azure Database for PostgreSQL - Hyperscale (Citus) (preview) using psql, you can complete some basic tasks. Este tutorial explica cómo crear una aplicación web que permite a los anunciantes realizar un seguimiento de sus campañas.This tutorial walks you through creating a web app that allows advertisers to track their campaigns.

Varias empresas pueden usar la aplicación, así que crearemos una tabla que contenga las empresas y otra para sus campañas.Multiple companies can use the app, so let's create a table to hold companies and another for their campaigns. En la consola de psql, ejecute estos comandos:In the psql console, run these commands:

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)
);

Cada campaña pagará por anunciarse.Each campaign will pay to run ads. Agregue también una tabla para anuncios, ejecutando el siguiente código en psql después del código anterior:Add a table for ads too, by running the following code in psql after the code above:

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)
);

Por último, realizaremos un seguimiento de las estadísticas de clics e impresiones por cada anuncio:Finally, we'll track statistics about clicks and impressions for each ad:

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)
);

Ahora puede ver las tablas recién creadas en la lista de tablas con este comando de psql:You can see the newly created tables in the list of tables now in psql by running:

\dt

Las aplicaciones multiinquilino pueden exigir exclusividad solo por inquilino, por lo que todas las claves principales y externas incluyen el identificador de la empresa.Multi-tenant applications can enforce uniqueness only per tenant, which is why all primary and foreign keys include the company ID.

Particiones de tablas entre nodosShard tables across nodes

Una implementación de hiperescala almacena filas de tablas en distintos nodos en función del valor de una columna designada por el usuario.A hyperscale deployment stores table rows on different nodes based on the value of a user-designated column. Esta "columna de distribución" indica qué filas pertenecen a qué inquilino.This "distribution column" marks which tenant owns which rows.

Vamos a establecer la columna de distribución como company_id, el identificador del inquilino.Let's set the distribution column to be company_id, the tenant identifier. En psql, ejecute estas funciones:In psql, run these functions:

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');

Ingesta de datos de ejemploIngest sample data

Ahora, fuera de psql, en la línea de comandos normal, descargue conjuntos de datos de ejemplo:Outside of psql now, in the normal command line, download sample data sets:

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

De vuelta en psql, realice una carga masiva de los datos.Back inside psql, bulk load the data. No olvide ejecutar psql en el mismo directorio donde descargó los archivos de datos.Be sure to run psql in the same directory where you downloaded the data files.

\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

Estos datos ahora se distribuirán entre nodos de trabajo.This data will now be spread across worker nodes.

Consulta de datos de inquilinoQuery tenant data

Cuando la aplicación solicita datos de un único inquilino, la base de datos puede ejecutar la consulta en un único nodo de trabajo.When the application requests data for a single tenant, the database can execute the query on a single worker node. Las consultas de un único inquilino filtran por un único identificador de inquilino.Single-tenant queries filter by a single tenant ID. Por ejemplo, la siguiente consulta filtra company_id = 5 para anuncios e impresiones.For example, the following query filters company_id = 5 for ads and impressions. Intente ejecutarla en psql y observe los resultados.Try running it in psql to see the results.

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;

Datos compartidos entre los inquilinosShare data between tenants

Hasta ahora company_id ha distribuido todas las tablas, pero algunos datos no "pertenecen" de manera natural a ningún inquilino en particular y se pueden compartir.Until now all tables have been distributed by company_id, but some data doesn't naturally "belong" to any tenant in particular, and can be shared. Por ejemplo, es posible que todas las empresas de la plataforma de anuncios del ejemplo deseen obtener información geográfica sobre su audiencia según las direcciones IP.For instance, all companies in the example ad platform might want to get geographical information for their audience based on IP addresses.

Cree una tabla que contenga información geográfica compartida.Create a table to hold shared geographic information. Ejecute los siguientes comandos en psql:Run the following commands in 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);

A continuación convierta geo_ips en una "tabla de referencia" para almacenar una copia de la tabla en cada nodo de trabajo.Next make geo_ips a "reference table" to store a copy of the table on every worker node.

SELECT create_reference_table('geo_ips');

Cargue en ella datos de ejemplo.Load it with example data. No olvide ejecutar este comando en psql desde dentro del directorio donde descargó el conjunto de datos.Remember to run this command in psql from inside the directory where you downloaded the dataset.

\copy geo_ips from 'geo_ips.csv' with csv

La combinación de la tabla de clics con geo_ips es eficaz en todos los nodos.Joining the clicks table with geo_ips is efficient on all nodes. Esta es una combinación para encontrar las ubicaciones de todas las personas que hicieron clic en el anuncio.Here is a join to find the locations of everyone who clicked on ad 290. Intente ejecutar la consulta en psql.Try running the query in 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;

Personalización de esquema por inquilinoCustomize the schema per-tenant

Cada inquilino puede necesitar almacenar información especial que otros no necesitan.Each tenant may need to store special information not needed by others. Sin embargo, todos los inquilinos comparten una infraestructura común con un esquema de base de datos idéntico.However, all tenants share a common infrastructure with an identical database schema. ¿Dónde pueden ubicarse los datos adicionales?Where can the extra data go?

Un truco consiste en usar un tipo de columna abierto, como JSONB de PostgreSQL.One trick is to use an open-ended column type like PostgreSQL's JSONB. Nuestro esquema tiene un campo JSONB en clicks llamado user_data.Our schema has a JSONB field in clicks called user_data. Una empresa (por ejemplo, la número cinco) puede usar la columna para realizar un seguimiento que determine si el usuario utiliza un dispositivo móvil.A company (say company five), can use the column to track whether the user is on a mobile device.

Esta es una consulta para determinar qué usuarios hacen más clics: los visitantes tradicionales o los usuarios de dispositivos móviles.Here's a query to find who clicks more: mobile, or traditional visitors.

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;

Podemos optimizar esta consulta para una empresa mediante la creación de un índice parcial.We can optimize this query for a single company by creating a partial index.

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

Por lo general, podemos crear un índices GIN en cada clave y valor de la columna.More generally, we can create a GIN indices on every key and value within the column.

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;

Limpieza de recursosClean up resources

En los pasos anteriores, creó recursos de Azure en un grupo de servidores.In the preceding steps, you created Azure resources in a server group. Si no cree que vaya a necesitar estos recursos en el futuro, elimine el grupo de servidores.If you don't expect to need these resources in the future, delete the server group. Presione el botón Eliminar en la página Información general del grupo de servidores.Press the Delete button in the Overview page for your server group. Cuando aparezca una página emergente en la que se le pida hacerlo, confirme el nombre del grupo de servidores y haga clic en el botón Eliminar final.When prompted on a pop-up page, confirm the name of the server group and click the final Delete button.

Pasos siguientesNext steps

En este tutorial, aprendió a aprovisionar un grupo de servidores Hiperescala (Citus).In this tutorial, you learned how to provision a Hyperscale (Citus) server group. Se conectó a él con psql, creó un esquema y distribuyó datos.You connected to it with psql, created a schema, and distributed data. Ha aprendido a consultar los datos dentro de los inquilinos y entre ellos, así como a personalizar el esquema por inquilino.You learned to query data both within and between tenants, and to customize the schema per tenant.

A continuación, conozca los conceptos de hiperescala.Next, learn about the concepts of hyperscale.