12.12.2023 в 18:16

Упрощение конвейеров ETL с помощью SQL: Три совета по обработке данных

Недавно меня спросили, как упростить наш конвейер ETL, чтобы клиенты могли быстро представить себе этапы обработки данных, прежде чем строить сложные ML-модели. Вот три совета, которые вы можете немедленно внедрить в свой рабочий процесс, чтобы сделать данные более прозрачными.

Создавайте таблицы входных данных с помощью SQL-запросов, не изменяя базу данных.
Реализуйте простые шаги вычислений с помощью функций SQL.
Задавайте переменные для этапов вычислений ETL.

Польза очевидна:

Упрощение рабочего процесса: Устраните необходимость передачи данных между SQL, Python или JavaScript для проверки качества данных.
Сократите время рабочего цикла: Выявляйте потенциальные проблемы с данными с помощью панелей мониторинга данных на основе SQL в рамках конвейера.
Бесшовная интеграция пользовательских данных (CSV или Excel) в рабочий процесс экономит время и средства.

Совет 1: Создание таблиц ввода

Этот метод оказывается весьма полезным в различных пользовательских сценариях, включая:

Тестирование синтаксиса запросов без получения данных из реальной базы данных: Вы можете проверять и настраивать запросы, не затрагивая реальную базу данных.
Объединение таблиц данных о клиентах из CSV или Excel с таблицами из баз данных: Объединение данных из разных источников позволяет проводить всесторонний анализ и получать необходимые сведения.
Создание таблицы входных параметров для последующих запросов: Создайте специальную таблицу для хранения входных параметров, что упростит выполнение последующих запросов.

-- 1. create meta data table
WITH devmap AS (
  SELECT *
  FROM (VALUES
('John', 'New York', '10001'),
('Emma', 'California', '90001'),
('Michael', 'Texas', '75001'),
('Sophia', 'Florida', '32003'),
('James', 'Illinois', '60601'),
('Olivia', 'Ohio', '44101'),
('William', 'Georgia', '30301'),
('Ava', 'Washington', '98101')
) AS t(first_name, state, zip_code)
)
SELECT first_name, state, zip_code
FROM devmap;

Подсказка: Вы можете попросить ChatGPT переформатировать таблицу csv в таблицу запросов, как показано здесь, или сгенерировать макет таблицы для тестирования ваших запросов.

Совет 2: Реализация этапов ETL в SQL

Хотя SQL не предназначен в первую очередь для сложных научных расчетов, его можно эффективно использовать для решения многих задач ETL (Extract, Transform, Load) ((Извлечение, преобразование, загрузка)). Реализация этапов ETL с помощью SQL-запросов имеет ряд преимуществ.

Для демонстрации идей мы используем закон всемирного тяготения Ньютона: сила тяготения между двумя объектами определяется следующим образом:

F = (G * m1 * m2) / r²

где: F - сила гравитации между объектами; G - гравитационная постоянная (приблизительно 6,67430e-11 Н(м/кг)²); m1 и m2 - массы двух объектов; r - расстояние между центрами двух объектов.

Предположим, что таблица исходных данных выглядит следующим образом:

SELECT m1, m2, distance FROM  objects

Выполните расчет гравитации и оберните результаты с помощью CTE (Common Table Expression). Причина обертывания расчета с помощью CTE заключается в том, что оно позволяет заключить все шаги расчета в один запрос, называемый запросом "gravity_calculation". Такой подход позволяет легко выбирать и отбирать нужные конечные столбцы для представления конечным пользователям.

WITH gravity_calculation AS (
  SELECT
    m1,
    m2,
    distance,
    (6.67430e-11 * m1 * m2) / POWER(distance, 2) AS gravity
  FROM
    objects
)
SELECT
  m1,
  m2,
  distance,
  gravity
FROM
  gravity_calculation;

Совет 3: Изменение параметров переменных с помощью подзапросов

Для повышения наглядности во время отладки или экспериментов полезно задавать переменные для этапов вычислений. Например, гравитационную постоянную можно определить как "g_coeff"в CTE. Такой подход позволяет управлять длинным списком переменных по мере необходимости.

WITH vars AS (
  6.67430e-11 AS g_coeff 
),

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

((SELECT g_coeff as from vars) * m1 * m2) / POWER(distance, 2) AS gravity

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

- set up variable Gravitational coefficient
WITH vars AS (
  6.67430e-11 AS g_coeff 
),
-- pull m1, m2,. distance from tables
gravity_calculation AS (
  SELECT
    m1,
    m2,
    distance,
    POWER(distance, 2), dist_square
    ((SELECT g_coeff as from vars) * m1 * m2) / POWER(distance, 2) AS gravity
-- pull m1, m2,. distance from tables
SELECT
  m1,
  m2,
  distance,
  dist_square
  gravity
FROM
  gravity_calculation;

Спасибо за прочтение! Счастливого кодинга!

Источник:

#SQL

Войти на сайт

Упрощение конвейеров ETL с помощью SQL: Три совета по обработке данных

Совет 1: Создание таблиц ввода

Совет 2: Реализация этапов ETL в SQL

Совет 3: Изменение параметров переменных с помощью подзапросов

Источник: