Использование MySQL в качестве кэша-прослойки для BigQuery

Использование MySQL в качестве кэша-прослойки для BigQuery
Андрей Волков

Андрей Волков

Системное, сетевое администрирование +DBA. И немного программист!))  Профиль автора.

В этой статье мы научимся, как использовать MySQL в качестве слоя кеша для BigQuery.

BigQuery отлично справляется с обработкой больших наборов данных, но никогда не даст вам ответ за секунду, даже на небольших наборах данных. Это приводит к времени ожидания на информационных панелях и диаграммах, особенно динамических, где пользователи могут выбирать разные диапазоны дат или менять фильтры. Это почти всегда хорошо для внутренних BI, но не для аналитики, ориентированной на клиента. Мы допускаем много вещей, таких как плохой пользовательский интерфейс и производительность внутренних инструментов, но не тех, которые мы отправляем клиентам.

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

Дашборды, инфопанели

Cube.js использует уровень предварительной агрегации как часть своей двухуровневой системы кэширования. Недавно выпустили поддержку внешних предварительных агрегаций для целевых вариантов использования, где пользователи могут объединять несколько баз данных и извлекать максимум пользы из двух миров. Схема ниже показывает типичную настройку для Cube.js с BigQuery и MySQL.

Cube.js with BigQuery and MySQL typical setup

Чтобы использовать функцию внешнего накопления, нам нужно настроить Cube.js для подключения к BigQuery и MySQL, а также указать, какую предварительную агрегацию мы хотим построить снаружи. Если вы новичок в Cube.js, я рекомендую сначала проштудировать этот учебник в стиле "101 совет", а затем вернуться сюда. В качестве примера приложения мы собираемся использовать общедоступный набор данных Hacker News от BigQuery.

Давайте сначала установим Cube.js CLI и создадим новое приложение.

$ npm install -g cubejs-cli
$ cubejs create external-rollups -d bigquery

Если Вы используете любимый многими Linux RedHat или CentOS, то для установки выполните:

$ yum install cubejs-cli

Опцией -d bigquery мы установили, что нашей основной базой данных является BigQuery. Затем перейдите в папку bigquery-mysql и настройте .env с правильными настройками:

CUBEJS_DB_TYPE=bigquery
CUBEJS_DB_BQ_PROJECT_ID=<BIGQUERY PROJECT ID>
CUBEJS_DB_BQ_CREDENTIALS=<BIGQUERY BASE64-ENCODED KEY>
CUBEJS_EXT_DB_NAME=preags
CUBEJS_EXT_DB_HOST=localhost
CUBEJS_EXT_DB_USER=root
CUBEJS_EXT_DB_PASS=12345

Здесь мы устанавливаем учетные данные для основной БД (BigQuery) и внешней БД для предварительной агрегации (MySQL). Подробнее о получении учетных данных BigQuery вы можете узнать в документации Cube.js здесь. Кроме того, для создания предварительных агрегаций внутри MySQL, Cube.js должен иметь доступ на запись к схеме stb_pre_aggregations, где будут храниться таблицы предварительной агрегации.

Теперь давайте установим драйвер MySQL Cube.js:

$ npm install @cubejs-backend/mysql-driver --save

Как только у нас это получится, последний шаг настройки - предоставить связанные с MySQL опции через свойства externalDbType и externalDriverFactory. Замените содержимое файла index.js следующим:

const CubejsServer = require("@cubejs-backend/server");
const MySQLDriver = require('@cubejs-backend/mysql-driver');
const server = new CubejsServer({
  externalDbType: 'mysql',
  externalDriverFactory: () => new MySQLDriver({
    host: process.env.CUBEJS_EXT_DB_HOST,
    database: process.env.CUBEJS_EXT_DB_NAME,
    user: process.env.CUBEJS_EXT_DB_USER,
    password: process.env.CUBEJS_EXT_DB_PASS.toString()
  })
});
server.listen().then(({ port }) => {
  console.log(`Cube.js server is listening on ${port}`);
});

Это все, что нам нужно, чтобы Cube.js подключался как к BigQuery, так и к MySQL. Теперь мы можем создать наш первый файл схемы данных Cube.js. Cube.js использует схему данных для генерации кода SQL, который будет выполняться в вашей базе данных.

Создайте файл schema/Stories.js со следующим содержимым:

cube(`Stories`, {
  sql: `select * from \`fh-bigquery.hackernews.full_partitioned\` WHERE type = 'story'`,
  measures: {
    count: {
      type: `count`,
    }
  },
  dimensions: {
    category: {
      type: `string`,
      case: {
        when: [
          { sql: `STARTS_WITH(title, "Show HN")`, label: `Show HN` },
          { sql: `STARTS_WITH(title, "Ask HN")`, label: `Ask HN` }
        ],
        else: { label: `Other` }
      }
    },
    time: {
      sql: `timestamp`,
      type: `time`
    }
  }
});

Теперь запустите сервер Cube.js, запустив узел index.js. Для этого перейдите на тестовую площадку разработки по адресу http://localhost:4000.

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

Диаграмма

 Если мы проверим сгенерированный SQL, нажав кнопку SQL, то увидим следующее:

SELECT
  CASE
    WHEN STARTS_WITH(title, "Show HN") THEN 'Show HN'
    WHEN STARTS_WITH(title, "Ask HN") THEN 'Ask HN'
    ELSE 'Other'
  END `stories__category`,
  DATETIME_TRUNC(DATETIME(`stories`.timestamp, 'UTC'), MONTH) `stories__time_month`,
  count(*) `stories__count`
FROM
  (
    select
      *
    from
      `fh-bigquery.hackernews.full_partitioned`
    WHERE
      type = 'story'
  ) AS `stories`
GROUP BY
  1,
  2
ORDER BY
  2 ASC
LIMIT
  10000

Этот SQL показывает нам, что этот запрос выполняется к необработанным данным внутри BigQuery. Теперь давайте заставим его работать с предварительно агрегированной таблицей внутри MySQL. Для этого мы собираемся определить предварительную агрегацию. Обычно это делается внутри того же куба (cube), но ради примера, чтобы иметь возможность сравнить производительность с предварительной агрегацией и без нее, давайте создадим новый куб. Мы можем сделать это внутри одного файла. Добавьте следующий код в файл schema/Stories.js:

cube(`StoriesPreAgg`, {
  extends: Stories,
  preAggregations: {
    main: {
      type: `rollup`,
      measureReferences: [count],
      dimensionReferences: [category],
      granularity: `month`,
      timeDimensionReference: time,
      external: true
    }
  }
});

В приведенном выше коде мы объявляем предварительную агрегацию с типом свертки и указываем, какие меры и измерения следует включить в таблицу агрегирования. Также обратите внимание на строку external: true; Это заставляет Cube.js загрузить эту предварительную агрегацию в MySQL.

Теперь перейдите на тестовую площадку разработки и выберите те же показатели и измерения, что и раньше: количество, категорию и время, сгруппированные по месяцам, но на этот раз выберите их в кубе Stories PreAgg. При первом запросе Cube.js сгенерирует таблицу агрегирования и загрузит ее в MySQL. Все последующие запросы будут идти непосредственно к сводной таблице внутри MySQL. Вы можете проверить сгенерированный SQL, и он должен выглядеть следующим образом.

SELECT
  `stories_pre_agg__category` `stories_pre_agg__category`,
  `stories_pre_agg__time_month` `stories_pre_agg__time_month`,
  sum(`stories_pre_agg__count`) `stories_pre_agg__count`
FROM
  stb_pre_aggregations.stories_pre_agg_main
GROUP BY
  1,
  2
ORDER BY
  2 ASC
LIMIT
  10000

Как видите, теперь он запрашивает данные из таблицы stb_pre_aggregations.stories_pre_agg_main внутри MySQL. Вы можете поиграться с фильтрами, чтобы увидеть повышение производительности агрегированного запроса по сравнению с необработанным.

Вы также можете посмотреть эту демонстрационную панель с несколькими графиками и сравнить ее производительность с предварительной агрегацией и без нее здесь. Исходный код примера панели инструментов доступен на GitHub.

Вас заинтересует / Intresting for you:

MyISAM: подсистема хранения ба...
MyISAM: подсистема хранения ба... 2646 просмотров Ирина Светлова Wed, 04 Aug 2021, 19:39:29
InnoDB: подсистема хранения ба...
InnoDB: подсистема хранения ба... 4897 просмотров Ирина Светлова Mon, 07 Jan 2019, 06:34:07
Оператор SELECT в MySQL 8: пол...
Оператор SELECT в MySQL 8: пол... 7312 просмотров Дэйзи ак-Макарова Sat, 07 Aug 2021, 10:47:45
Обзор версий MySQL - какой рел...
Обзор версий MySQL - какой рел... 10567 просмотров Ирина Светлова Fri, 05 Feb 2021, 17:19:41
Войдите чтобы комментировать

apv аватар
apv ответил в теме #9792 3 года 3 мес. назад
Здорово! Классный практический пример использования базы данных MySQL в качестве буера для BigQuery. На вес Золота, так сказать..))