Руководство. Разработка панели мониторинга аналитики в режиме реального времени с помощью эластичных кластеров

В этом руководстве вы используете эластичные кластеры в Azure Database для PostgreSQL, чтобы узнать, как разрабатывать в режиме реального времени панель мониторинга и параллелизировать запросы.

• Предпосылки
• Использование служебной программы psql для создания схемы
• Сегментирование таблиц по узлам
• Создание примера данных
• выполнение агрегирования данных
• выполнение запроса к необработанным и агрегированным данным;
• истечение срока данных.

Предпосылки

Создайте эластичные кластеры одним из следующих способов:

• Создание эластичного кластера с помощью портала
• Создание эластичного кластера с помощью Bicep
• Создание эластичного кластера с помощью шаблона ARM

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

После подключения к эластичному кластеру с помощью psql можно настроить эластичные кластеры. В этом руководстве показано, как выполнить поглощение данных о трафике из веб-аналитики и их объединение для создания панелей мониторинга в реальном времени, основанных на этих данных.

Давайте создадим таблицу, которая использует все необработанные данные веб-трафика. В окне терминала psql выполните следующие команды:

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

Мы также создадим таблицу, содержащую наши статистические данные в минуту, и таблицу, которая сохраняет положение нашего последнего накопительного пакета. В psql выполните также следующие команды:

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:

\dt

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

Развертывание эластичного кластера сохраняет строки таблиц на разных узлах на основе значения столбца, указанного пользователем. Этот столбец определяет, как данные распределяются между узлами.

Давайте установим столбец распространения (ключ сегмента) как site_id. Выполните в psql такие функции:

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

Замечание

Распространение таблиц или использование сегментирования на основе схемы необходимо для использования эластичных кластеров функций производительности Базы данных Azure для PostgreSQL. Пока вы не распределяете таблицы или схемы, узлы кластера не будут запускать распределенные запросы с их данными.

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

Теперь наш кластер должен быть готов к приему некоторых данных. Следующие команды можно выполнить локально из нашего подключения psql для постоянного добавления данных.

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

Запрос добавляет примерно восемь строк каждую секунду. Строки хранятся на разных рабочих узлах в соответствии с их столбцом распределения, site_id.

Замечание

Оставьте выполняться запрос на создание данных и откройте второй сеанс psql для ввода оставшихся команд из этого руководства.

Query

Эластичные кластеры Базы данных Azure для PostgreSQL позволяют нескольким узлам параллельно обрабатывать запросы на скорость. Так, база данных вычисляет агрегаты, такие как SUM и COUNT, на рабочих узлах и объединяет результаты в итоговый ответ.

Ниже показан запрос для подсчета веб-запросов в минуту вместе с некоторой статистикой. Попробуйте выполнить его в psql и просмотрите результаты.

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;

Агрегация данных

Предыдущий запрос эффективен на ранних этапах, но его производительность падает по мере масштабирования. Даже при распределенной обработке быстрее предварительно компьютировать данные, чем пересчитывать их многократно.

Быстродействие панели мониторинга можно обеспечить за счет регулярного сведения необработанных данных в таблицу агрегированных данных. Вы можете поэкспериментировать с длительностью статистической обработки. Мы использовали таблицу поминутной статистической обработки, но вместо этого вы можете разбить данные на интервалы в 5, 15 или 60 минут.

Для более легкого выполнения этой свертки, мы собираемся поместить её в функцию plpgsql. Выполните следующие команды в psql для создания функции rollup_http_request.

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

Теперь, когда наша функция готова, выполните её, чтобы свернуть данные.

SELECT rollup_http_request();

И с нашими данными в предварительно созданной форме мы можем запросить таблицу свертки, чтобы получить тот же отчет, что и ранее. Выполните приведенный ниже запрос:

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;

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

Агрегация позволяет быстрее обрабатывать запросы, но нам также нужно удалять устаревшие данные, чтобы избежать неограниченных расходов на хранение. Определите, как долго требуется хранить данные разной степени детализации и используйте стандартные запросы для удаления данных с истекшим сроком хранения. В следующем примере мы решили хранить необработанные данные за один день, а поминутно агрегированные данные — за один месяц.

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

В рабочей среде эти запросы можно реализовать в виде функции и вызывать ее каждую минуту с помощью задания cron.

Следующий шаг

В этом руководстве вы узнали, как создать эластичные кластеры. Вы подключились к ней с помощью psql, создали схему и выполнили распределение данных. Вы научились выполнять запросы к необработанным данным и регулярно агрегировать их, выполнять запрос к таблице агрегированных данных и удалять устаревшие данные.

Дополнительные сведения об эластичных кластерах