Используете ли вы Python для извлечения необработанных данных из базы данных? Это может быть огромным узким местом в ваших приложениях. Вместо этого используйте SQL.

Новички в науке о данных, включая меня пару лет назад, страдают от той же проблемы. Они используют Python для всего, от сбора до хранения и обработки данных. Конечно, современные языки программирования могут справиться со всем, но действительно ли это лучшая стратегия? Это не так, и сегодня вы поймете, почему.

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

Сегодня мы рассмотрим следующий сценарий. Компания хранит десятки миллионов строк в локальной базе данных Postgres. Они хотят знать, насколько быстрее происходит агрегирование данных в базе данных по сравнению с получением всех данных с помощью Python и выполнением агрегирования там.

Не хочется читать? Я освещал ту же тему в формате видео:

Создайте синтетический набор данных с помощью Python

Прежде всего, нам нужно создать набор данных. Вам понадобятся установленные Numpy, Pandas и Psycopg2 (для подключения к Postgres). Вот импорт:

import random
import string
import warnings
import psycopg2
import numpy as np
import pandas as pd
from datetime import datetime
np.random.seed = 42
warnings.filterwarnings('ignore')

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

def get_department() -> str:
    x = np.random.rand()
    if x < 0.2: return 'A'
    elif 0.2 <= x < 0.4: return 'B'
    elif 0.4 <= x < 0.6: return 'C'
    elif 0.6 <= x < 0.8: return 'D'
    else: return 'E'
def gen_random_string(length: int = 32) -> str:
    return ''.join(random.choices(
        string.ascii_uppercase + string.digits, k=length)
    )
date_range = pd.date_range(
    start=datetime(1920, 1, 1),
    end=datetime(2020, 1, 1),
    freq='120s'
)
df_size = len(date_range)

Мы можем использовать их для создания набора данных:

df = pd.DataFrame({
    'datetime': date_range,
    'department': [get_department() for x in range(df_size)],
    'items_sold': [np.random.randint(low=0, high=100) for x in range(df_size)],
    'string': [gen_random_string() for x in range(df_size)],
})

Вот как это выглядит:

Более 26 миллионов строк распределены по четырем столбцам. Это не самая реалистичная таблица, я дам вам это, но это все же приличный объем данных. Скопируем его в CSV-файл:

df.to_csv('df.csv', index=False)

Места на диске занимает 1,52 Гб, что по нынешним меркам не считается большим.

Загрузите набор данных в базу данных Postgres

Следующим шагом будет создание таблицы в Postgres и загрузка CSV-файла. Сначала на таблицу — у нее будет столбец первичного ключа в дополнение к остальным четырем. Мне нравится добавлять префиксы к именам столбцов, но вам не обязательно:

CREATE TABLE department_sales(
	dsl_id SERIAL PRIMARY KEY,
	dsl_datetime TIMESTAMP,
	dsl_department CHAR(1),
	dsl_items_sold SMALLINT,
	dsl_string VARCHAR(32)
);

Введите следующую команду, чтобы скопировать содержимое CSV-файла в нашу таблицу Postgres — только не забудьте изменить путь:

COPY department_sales(dsl_datetime, dsl_department, dsl_items_sold, dsl_string)
FROM '/Users/dradecic/Desktop/df.csv'
DELIMITER ','
CSV HEADER;

И вот оно — более 26 миллионов строк загружаются менее чем за 50 секунд. Давайте запустим оператор SELECT, чтобы посмотреть, все ли в порядке:

SELECT * FROM department_sales;

Да, так что давайте загрузим данные с помощью Python.

Вариант № 1 — загрузить всю таблицу с помощью Python

Используйте метод connect() из Psycopg2, чтобы установить соединение с базой данных через Python:

conn = psycopg2.connect(
    user='<username>',
    password='<password>',
    host='127.0.0.1',
    port=5432,
    database='<db>'
)

Теперь мы можем выполнить тот же оператор SELECT, что и ранее, через СУБД:

%%time
df_department_sales = pd.read_sql("SELECT * FROM department_sales", conn)

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

Давайте теперь агрегируем данные. Сгруппируем по отделам и посчитаем сумму проданных товаров:

%%time
df_pd_sales_by_department = (
    df_department_sales
        .groupby('dsl_department')
        .sum()
        .reset_index()
)[['dsl_department', 'dsl_items_sold']]
df_pd_sales_by_department

Меньше секунды, что и ожидалось. Я запускаю ноутбук на M1 Pro MacBook Pro 16', который невероятно быстр, поэтому результаты меня не удивляют.

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

%%time
df_pd_sales_by_year = (
    df_department_sales
        .groupby(df_department_sales['dsl_datetime'].dt.year)
        .sum()
        .reset_index()
)[['dsl_datetime', 'dsl_items_sold']]
df_pd_sales_by_year

Почти идентичные результаты. В целом, время выполнения можно округлить до 77 секунд. Далее рассмотрим производительность базы данных.

Вариант № 2 — загрузить подготовленные представления с помощью Python

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

Создание представлений на основе агрегации данных в базе данных Postgres

Этот агрегирует количество товаров, проданных по отделам:

CREATE OR REPLACE VIEW v_sales_by_department AS (
	SELECT 
		dsl_department AS "Department",
		SUM(dsl_items_sold) AS "Total items sold"
	FROM department_sales
	GROUP BY dsl_department
	ORDER BY 2 DESC
);

Давайте посмотрим, как это выглядит:

SELECT * FROM v_sales_by_department;

Как и ожидалось, она идентична нашей первой операции агрегирования в Python. А пока давайте создадим второе представление, которое суммирует объемы продаж по годам:

CREATE OR REPLACE VIEW v_sales_by_year AS (
	SELECT 
		EXTRACT(YEAR FROM dsl_datetime) AS "Year",
		SUM(dsl_items_sold) AS "Total items sold"
	FROM department_sales 
	GROUP BY "Year"
);

Просто быстрая проверка:

SELECT * FROM v_sales_by_year;

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

Загрузите данные из представлений с помощью Python

Во-первых, давайте получим данные о продажах по отделам:

%%time
df_sales_by_department = pd.read_sql("SELECT * FROM v_sales_by_department", conn)
df_sales_by_department

Три. Черт. Секунды. Давайте сделаем то же самое для просмотра продаж по годам:

%%time
df_sales_by_year = pd.read_sql("SELECT * FROM v_sales_by_year", conn)
df_sales_by_year

Чуть дольше, но все же в пределах разумного. Мы можем округлить время выполнения до 10 секунд для обоих.

Вердикт

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

Дайте мне знать, если это сравнение вы хотите увидеть, и я буду рад рассказать об этом в следующей статье.

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

Узнать больше

Оставайся на связи

Первоначально опубликовано на https://betterdatascience.com 17 февраля 2022 г.