Руководство по проектированию панели мониторинга для отображения данных в реальном времени с помощью Базы данных Azure для PostgreSQL с решением "Гипермасштабирование (Citus)"Tutorial: Design a real-time analytics dashboard by using Azure Database for PostgreSQL – Hyperscale (Citus)

В этом учебнике используется решение "Гипермасштабирование (Citus)" в Базе данных Azure для PostgreSQL, чтобы продемонстрировать следующее:In this tutorial, you use Azure Database for PostgreSQL - Hyperscale (Citus) to learn how to:

  • создание группы серверов Hyperscale (Citus);Create a Hyperscale (Citus) server group
  • Использование служебной программы psql для создания схемыUse psql utility to create a schema
  • Сегментирование таблиц по узламShard tables across nodes
  • Создание примера данныхGenerate sample data
  • выполнение сведения данных;Perform rollups
  • выполнение запроса к необработанным и агрегированным данным;Query raw and aggregated data
  • удаление устаревших данных.Expire data

Предварительные требованияPrerequisites

создание группы серверов Hyperscale (Citus);Create a Hyperscale (Citus) server group

Если у вас еще нет подписки Azure, создайте бесплатную учетную запись Azure, прежде чем начинать работу.If you don't have an Azure subscription, create a free account before you begin.

Вход на портал AzureSign in to the Azure portal

Войдите на портал Azure.Sign in to the Azure portal.

Чтобы создать базу данных Azure для сервера PostgreSQL, сделайте следующее:Follow these steps to create an Azure Database for PostgreSQL server:

  1. Щелкните Создать ресурс в верхнем левом углу окна портала Azure.Click Create a resource in the upper left-hand corner of the Azure portal.

  2. На странице создания выберите Базы данных, а на странице Базы данных щелкните Azure Database for PostgreSQL (База данных Azure для PostgreSQL).Select Databases from the New page, and select Azure Database for PostgreSQL from the Databases page.

  3. Чтобы выполнить развертывание, нажмите кнопку Создать в разделе Группа серверов с гипермасштабированием (Citus).For the deployment option, click the Create button under Hyperscale (Citus) server group.

  4. Заполните форму для создания сервера, указав следующую информацию:Fill out the new server details form with the following information:

    • Под текстовым полем в разделе "Группа ресурсов" щелкните ссылку Создать.Resource group: click the Create new link below the text box for this field. Присвойте группе ресурсов имя, например myresourcegroup.Enter a name such as myresourcegroup.
    • Имя группы серверов. Введите уникальное имя для новой группы серверов, которое будет использоваться и для поддомена сервера.Server group name: enter a unique name for the new server group, which will also be used for a server subdomain.
    • Имя пользователя администратора: в настоящее время должно использоваться значение citus, и изменить его нельзя.Admin username: currently required to be the value citus, and can't be changed.
    • Пароль. Должен содержать минимум восемь символов из таких трех категорий: латинские прописные и строчные буквы, цифры (0–9) и другие символы (!, $, #, % и т. д.).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.)
    • Расположение. Задайте ближайшее к пользователям расположение, чтобы предоставить им максимально быстрый доступ к данным.Location: use the location that is closest to your users to give them the fastest access to the data.

    Важно!

    Указанный здесь пароль администратора сервера необходим, чтобы войти на сервер и в его базу данных.The server admin password that you specify here is required to log in to the server and its databases. Запомните или запишите эту информацию для последующего использования.Remember or record this information for later use.

  5. Щелкните Настройка группы серверов.Click Configure server group. Не изменяйте настройки в этом разделе и щелкните Сохранить.Leave the settings in that section unchanged and click Save.

  6. Щелкните Далее: Сеть > в нижней части экрана.Click Next : Networking > at the bottom of the screen.

  7. На вкладке Сеть щелкните переключатель Общедоступная конечная точка.In the Networking tab, click the Public endpoint radio button. Выбранная общедоступная конечная точкаPublic endpoint selected

  8. Щелкните ссылку + Добавить текущий IP-адрес клиента.Click the link + Add current client IP address. Добавленный IP-адрес клиентаAdded client IP

    Примечание

    Сервер PostgreSQL Azure обменивается данными через порт 5432.Azure PostgreSQL server communicates over port 5432. Если вы пытаетесь подключиться из корпоративной сети, исходящий трафик через порт 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. В таком случае вы не сможете подключиться к кластеру Hyperscale (Citus), пока ваш ИТ-отдел не откроет порт 5432.If so, you cannot connect to your Hyperscale (Citus) cluster unless your IT department opens port 5432.

  9. Щелкните Просмотр и создание, а затем — Создать, чтобы подготовить сервер к работе.Click Review + create and then Create to provision the server. Подготовка занимает несколько минут.Provisioning takes a few minutes.

  10. Произойдет переход на страницу отслеживания развертывания.The page will redirect to monitor deployment. Когда состояние изменится с Развертывание выполняется на Развертывание выполнено, щелкните пункт меню Выходные данные в левой части страницы.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.

  11. На странице выходных данных будет указано имя узла-координатора, кнопка рядом с которым позволяет скопировать это значение в буфер обмена.The outputs page will contain a coordinator hostname with a button next to it to copy the value to the clipboard. Сохраните эту информацию на будущее.Record this information for later use.

Подключение к базе данных с помощью psqlConnect to the database using psql

При создании базы данных Azure для сервера PostgreSQL создается база данных по умолчанию с именем citus.When you create your Azure Database for PostgreSQL server, a default database named citus is created. Чтобы подключиться к серверу базы данных, требуется строка подключения и пароль администратора.To connect to your database server, you need a connection string and the admin password.

  1. Получите строку подключения.Obtain the connection string. На странице группы серверов щелкните пункт меню Строки подключенияIn the server group page click the Connection strings menu item. (Он находится в разделе Параметры.) Найдите строку, помеченную как psql.(It's under Settings.) Find the string marked psql. Значение будет представлено в формате:It will be of the form:

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

    Скопируйте строку.Copy the string. При этом потребуется заменить "{your_password}" выбранным ранее паролем администратора.You will need to replace "{your_password}" with the administrative password you chose earlier. Система не хранит ваш пароль в виде открытого текста и поэтому не отображает его автоматически в строке подключения.The system doesn't store your plaintext password and so can't display it for you in the connection string.

  2. Откройте окно терминала на локальном компьютере.Open a terminal window on your local computer.

  3. Подключитесь к базе данных Azure для сервера PostgreSQL с помощью служебной программы psql.At the prompt, connect to your Azure Database for PostgreSQL server with the psql utility. Передайте строку подключения в кавычках, предварительно убедившись, что она содержит пароль:Pass your connection string in quotes, being sure it contains your password:

    psql "host=..."
    

    Например, следующая команда устанавливает подключение к узлу-координатору группы серверов 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"
    

Использование служебной программы psql для создания схемыUse psql utility to create a schema

Подключившись с помощью psql к решению "Гипермасштабирование (Citus)" в Базе данных Azure для PostgreSQL, вы сможете выполнить некоторые несложные задачи.Once connected to the Azure Database for PostgreSQL - Hyperscale (Citus) using psql, you can complete some basic tasks. В этом руководстве показано, как выполнить прием данных из средства веб-аналитики и сведение данных для их представления в реальном времени на панели мониторинга.This tutorial walks you through ingesting traffic data from web analytics, then rolling up the data to provide real-time dashboards based on that data.

Давайте создадим таблицу, в которую будут поступать необработанные данные.Let's create a table that will consume all of our raw web traffic data. В окне терминала psql выполните следующие команды:Run the following commands in the psql terminal:

CREATE TABLE http_request (
  site_id INT,
  ingest_time TIMESTAMPTZ DEFAULT now(),

  url TEXT,
  request_country TEXT,
  ip_address TEXT,

  status_code INT,
  response_time_msec INT
);

Кроме того, мы собираемся создать таблицу, в которой будут находиться поминутно агрегированные данные, и таблицу со сведениями о последнем сведении данных.We're also going to create a table that will hold our per-minute aggregates, and a table that maintains the position of our last rollup. В psql выполните также следующие команды:Run the following commands in psql as well:

CREATE TABLE http_request_1min (
  site_id INT,
  ingest_time TIMESTAMPTZ, -- which minute this row represents

  error_count INT,
  success_count INT,
  request_count INT,
  average_response_time_msec INT,
  CHECK (request_count = error_count + success_count),
  CHECK (ingest_time = date_trunc('minute', ingest_time))
);

CREATE INDEX http_request_1min_idx ON http_request_1min (site_id, ingest_time);

CREATE TABLE latest_rollup (
  minute timestamptz PRIMARY KEY,

  CHECK (minute = date_trunc('minute', minute))
);

Созданные таблицы можно отобразить в виде списка с помощью этой команды psql:You can see the newly created tables in the list of tables now with this psql command:

\dt

Сегментирование таблиц по узламShard tables across nodes

При гипермасштабируемом развертывании строки таблиц распределяются по разным узлам на основе значения столбца, назначенного пользователем.A hyperscale deployment stores table rows on different nodes based on the value of a user-designated column. Этот столбец определяет, как данные распределяются между узлами.This "distribution column" marks how data is sharded across nodes.

Назначим столбец распределения ключом сегментирования site_id.Let's set the distribution column to be site_id, the shard key. Выполните в psql такие функции:In psql, run these functions:

SELECT create_distributed_table('http_request',      'site_id');
SELECT create_distributed_table('http_request_1min', 'site_id');

Важно!

Распределение таблиц необходимо, чтобы использовать преимущества Гипермасштабирования для повышения производительности.Distributing tables is necessary to take advantage of Hyperscale performance features. Если таблицы не распределены, рабочие узлы не смогут выполнять запросы, охватывающие эти таблицы.If you don't distribute tables then worker nodes can't help run queries involving those tables.

Создание примера данныхGenerate sample data

Теперь наша группа серверов должна быть готова к приему данных.Now our server group should be ready to ingest some data. Следующие команды можно выполнить локально в psql для постоянного добавления данных.We can run the following locally from our psql connection to continuously insert data.

DO $$
  BEGIN LOOP
    INSERT INTO http_request (
      site_id, ingest_time, url, request_country,
      ip_address, status_code, response_time_msec
    ) VALUES (
      trunc(random()*32), clock_timestamp(),
      concat('http://example.com/', md5(random()::text)),
      ('{China,India,USA,Indonesia}'::text[])[ceil(random()*4)],
      concat(
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2), '.',
        trunc(random()*250 + 2)
      )::inet,
      ('{200,404}'::int[])[ceil(random()*2)],
      5+trunc(random()*150)
    );
    COMMIT;
    PERFORM pg_sleep(random() * 0.25);
  END LOOP;
END $$;

Запрос выполняет вставку примерно восьми строк каждую секунду.The query inserts approximately eight rows every second. Строки сохраняются на разных рабочих узлах, как указано в столбце распределения site_id.The rows are stored on different worker nodes as directed by the distribution column, site_id.

Примечание

Оставьте выполняться запрос на создание данных и откройте второй сеанс psql для ввода оставшихся команд из этого руководства.Leave the data generation query running, and open a second psql connection for the remaining commands in this tutorial.

ЗапросQuery

Гипермасштабируемое размещение позволяет обрабатывать запросы на нескольких узлах в параллельном режиме, чтобы повысить скорость.The hyperscale hosting option allows multiple nodes to process queries in parallel for speed. Так, база данных вычисляет агрегаты, такие как SUM и COUNT, на рабочих узлах и объединяет результаты в итоговый ответ.For instance, the database calculates aggregates like SUM and COUNT on worker nodes, and combines the results into a final answer.

Ниже показан запрос для подсчета веб-запросов в минуту вместе с некоторой статистикой.Here's a query to count web requests per minute along with a few statistics. Попробуйте выполнить его в psql и просмотрите результаты.Try running it in psql and observe the results.

SELECT
  site_id,
  date_trunc('minute', ingest_time) as minute,
  COUNT(1) AS request_count,
  SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) as success_count,
  SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) as error_count,
  SUM(response_time_msec) / COUNT(1) AS average_response_time_msec
FROM http_request
WHERE date_trunc('minute', ingest_time) > now() - '5 minutes'::interval
GROUP BY site_id, minute
ORDER BY minute ASC;

Сведение данныхRolling up data

Предыдущий запрос эффективен на ранних этапах, но его производительность падает по мере масштабирования.The previous query works fine in the early stages, but its performance degrades as your data scales. Даже при распределенной обработке будет быстрее предварительно вычислить эти данные, чем пересчитывать их каждый раз.Even with distributed processing, it's faster to pre-compute the data than to recalculate it repeatedly.

Быстродействие панели мониторинга можно обеспечить за счет регулярного сведения необработанных данных в таблицу агрегированных данных.We can ensure our dashboard stays fast by regularly rolling up the raw data into an aggregate table. Вы можете поэкспериментировать с длительностью статистической обработки.You can experiment with the aggregation duration. Мы использовали таблицу поминутной статистической обработки, но вместо этого вы можете разбить данные на интервалы в 5, 15 или 60 минут.We used a per-minute aggregation table, but you could break data into 5, 15, or 60 minutes instead.

Для простого сведения мы собираемся поместить ее в функцию plpgsql.To run this roll-up more easily, we're going to put it into a plpgsql function. Выполните следующие команды в psql для создания функции rollup_http_request.Run these commands in psql to create the rollup_http_request function.

-- initialize to a time long ago
INSERT INTO latest_rollup VALUES ('10-10-1901');

-- function to do the rollup
CREATE OR REPLACE FUNCTION rollup_http_request() RETURNS void AS $$
DECLARE
  curr_rollup_time timestamptz := date_trunc('minute', now());
  last_rollup_time timestamptz := minute from latest_rollup;
BEGIN
  INSERT INTO http_request_1min (
    site_id, ingest_time, request_count,
    success_count, error_count, average_response_time_msec
  ) SELECT
    site_id,
    date_trunc('minute', ingest_time),
    COUNT(1) as request_count,
    SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) as success_count,
    SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) as error_count,
    SUM(response_time_msec) / COUNT(1) AS average_response_time_msec
  FROM http_request
  -- roll up only data new since last_rollup_time
  WHERE date_trunc('minute', ingest_time) <@
          tstzrange(last_rollup_time, curr_rollup_time, '(]')
  GROUP BY 1, 2;

  -- update the value in latest_rollup so that next time we run the
  -- rollup it will operate on data newer than curr_rollup_time
  UPDATE latest_rollup SET minute = curr_rollup_time;
END;
$$ LANGUAGE plpgsql;

Используйте функцию, чтобы выполнить сведение данных.With our function in place, execute it to roll up the data:

SELECT rollup_http_request();

Используя неагрегированные данные, можно выполнить запрос к таблице сведения для получения такого же отчета, как и ранее.And with our data in a pre-aggregated form we can query the rollup table to get the same report as earlier. Выполните следующий запрос:Run the following query:

SELECT site_id, ingest_time as minute, request_count,
       success_count, error_count, average_response_time_msec
  FROM http_request_1min
 WHERE ingest_time > date_trunc('minute', now()) - '5 minutes'::interval;

Удаление устаревших данныхExpiring old data

Сведение позволяет быстрее обрабатывать запросы, но нам также нужно удалять устаревшие данные, чтобы избежать расходов на хранение постоянно растущего объема данных.The rollups make queries faster, but we still need to expire old data to avoid unbounded storage costs. Определите, как долго требуется хранить данные разной степени детализации и используйте стандартные запросы для удаления данных с истекшим сроком хранения.Decide how long you’d like to keep data for each granularity, and use standard queries to delete expired data. В следующем примере мы решили хранить необработанные данные за один день, а поминутно агрегированные данные — за один месяц.In the following example, we decided to keep raw data for one day, and per-minute aggregations for one month:

DELETE FROM http_request WHERE ingest_time < now() - interval '1 day';
DELETE FROM http_request_1min WHERE ingest_time < now() - interval '1 month';

В рабочей среде эти запросы можно реализовать в виде функции и вызывать ее каждую минуту с помощью задания cron.In production, you could wrap these queries in a function and call it every minute in a cron job.

Очистка ресурсовClean up resources

На предыдущих шагах вы создали ресурсы Azure в группе серверов.In the preceding steps, you created Azure resources in a server group. Если эти ресурсы вам больше не нужны, удалите группу серверов.If you don't expect to need these resources in the future, delete the server group. Нажмите кнопку Удалить на странице Обзор, чтобы удалить группу серверов.Press the Delete button in the Overview page for your server group. При появлении запроса на всплывающей странице проверьте имя группы серверов и нажмите кнопку Удалить.When prompted on a pop-up page, confirm the name of the server group and click the final Delete button.

Дальнейшие действияNext steps

Из этого руководства вы узнали, как подготовить к работе группу серверов с подключенным решением Hyperscale (Citus).In this tutorial, you learned how to provision a Hyperscale (Citus) server group. Вы подключились к ней с помощью psql, создали схему и выполнили распределение данных.You connected to it with psql, created a schema, and distributed data. Вы научились выполнять запросы к необработанным данным и регулярно агрегировать их, выполнять запрос к таблице агрегированных данных и удалять устаревшие данные.You learned to query data in the raw form, regularly aggregate that data, query the aggregated tables, and expire old data.