Оконные функции SQL это, пожалуй, самая мистическая часть SQL для многих веб-разработчиков. Нередко встретишь и тех, кто и вовсе никогда о них не слышал. Да что греха таить, я сам продолжительное время не знал об их существовании, решая задачи далеко не самым оптимальным способом.
Оконные функции это функции применяемые к набору строк так или иначе связанных с текущей строкой. Наверняка всем известны классические агрегатные функции вроде AVG, SUM, COUNT, используемые при группировке данных. В результате группировки количество строк уменьшается, оконные функции напротив никак не влияют на количество строк в результате их применения, оно остаётся прежним.
Привычные нам агрегатные функции также могут быть использованы в качестве оконных функций, нужно лишь добавить выражение определения "окна". Область применения оконных функций чаще всего связана с аналитическими запросами, анализом данных.
Из чего состоит оконная функция
<название функции>(<выражение>) OVER (
<окно>
<сортировка>
<границы окна>
)
Лучше всего понять как работают оконные функции на практике. Представим, что у нас есть таблица с зарплатами сотрудников по департаментам. Вот как она выглядит:
Свершилось! Я закончил создание курса по построению дата-пайплайнов на Python, используя Luigi — Введение в Data Engineering: дата-пайплайны. Курс размещён на самописной, его стоимость составляет всего 590 рублей.
О чем он? В октябре 2017 года здесь была размещена статья про замечательный инструмент Luigi — Строим Data Pipeline на Python и Luigi. На тот момент это был первый материал на русском языке. Статья ничто иное как базовое введение в инструмент, плюс небольшая мотивация почему он лучше чем кастомные скрипты на коленке. С тех пор я активно использую Luigi в своей работе, и сейчас у нас в облаке AWS крутится более 1000+ дата-пайплайнов, написанных на нём. О выборе в пользую Luigi я ни разу не пожалел, даже несмотря на то, что инструменты вроде Apache Airflow комплексно выглядят круче и масштабно. Сила Luigi в простоте. А простота зачастую ключ к успешному построению надёжных и быстрых систем. Очень сложно понять проблему, когда она зарыта под тонной кода с множеством зависимостей. Да-да, речь о монстре вроде Airflow. Я ни сколько не умаляю комплексные workflow менеджеры, но к выбору того или иного инструмента нужно подходить исходя из потребностей, которые хочется удовлетворить.
Если вы data scientist, data engineer или backend-разработчик, который часто сталкивается с задачами по обработке, анализу и хранению данных, пожалуйста, обратите внимание на Luigi. Это тёмная лошадка, которая может значительно упростить вашу жизнь, а также правильно структурировать ваш код для удобства его дальнейшего сопровождения и развития.
Курс я постарался сделать максимально практичным, получился микс из текста и видео. В нём я разбираю 5 практических примеров (планирую добавить ещё как минимум 2): от записи Hello World до оповещения об ошибках в пайплайнах в Telegram через бота. Не обошел стороной и тему деплоя. В ней я затронул сборку Docker контейнера, а также уникальный материал про построение serverless дата-пайплана на Amazon Web Services через Docker, Fargate, Cloud Map. Такой дата-пайплайн, во-первых, будет максимально дешевым, т.к. оплата идёт только за время выполнения кода. А во-вторых, масштабируемым — вам не нужно настраивать и поднимать сервера, чтобы увеличить количество воркеров.
С развитием мобильных устройств, дешевого и доступного мобильного Интернета, объём генерируемых данных пользователями значительно увеличился. IoT устройства уже реалии нашего времени, а не удел фантастов прошлого века. Большая часть имеющихся данных была произведена в течение последнего десятилетия, мне страшно представить что будет в следующие 10 лет.
Инфографика ниже показывает масштабы этой дата-эпидемии.читать дальше
Apache Airflow — это продвинутый workflow менеджер и незаменимый инструмент в арсенале современного дата инженера. Если смотреть открытые вакансии на позицию data engineer, то нередко встретишь опыт работы с Airflow как одно из требований к позиции.
Airflow был разработан в 2014 году в компании Airbnb, автор Maxime Beauchemin. Позже инструмент был передан под опеку в организацию Apache, а в январе 2019 получил статус Top-Level проекта. В этой статье я расскажу про установку, настройку и запуск первого дата пайплайна средствами Apache Airflow. К слову, в 2017 году я уже писал про не менее классный и простой инструмент Luigi от компании Spotify. По своей сути эти два инструмента похожи — оба предназначены для запуска цепочек задач (дата пайплайнов), но есть у них и ряд различий о которых я говорил во время своего выступления на PyCON Russia 2019:
В этой статье я постараюсь рассказать о необходимом минимуме для работы с Airflow. Для начала давайте рассмотрим основные сущности инструмента.
Релиз Python 3.8 намечен на октябрь 2019 года, но уже сейчас у каждого есть возможность пощупать набор новых фишек языка. Пока пишу этот пост, на официальном сайте доступна версия python 3.8b2.
Итак, что же нам готовит релиз грядущий?
f-string =
Теперь выводить debug информацию стало ещё проще и красивее. Достаточно в f-string передать знак =:
>> a = 123
>> b = 456
>> print(f'{a=} and {b=}')
a=123 and b=456
Assignment Expressions или :=, он же walrus operator
Легендарный PEP572 из-за которого Гвидо сложил полномочия диктатора Python. Оператор выполняет присваивание значения переменной в выражении. Например, вам хочется проверить есть ли ключ в словаре, а также присвоить значение этого ключа переменной. В версиях Python до 3.8 необходимо сделать:
12 апреля 2019 года в городе Алматы прошла первая международная IT конференция, организованная объединенной компанией Колёса. Крыша. Маркет.
В этот раз мне удалось выступить в секции Data Science & Analytics в темой ETL на Python, или Построение идемпотентных дата пайплайнов. Цель доклада - познакомить слушателей с инструментами построения batch processing задач в экосистеме Python. В презентации я рассказал про две наиболее популярных тулзы: Luigi и Apache Airflow.
В видео проблематично разглядеть слайды, поэтому смотрите тут:читать дальше
Ранее я уже писал статью про работу с PostgreSQL из Python. Сегодняшний пост будет посвящен другой популярной базе данных MySQL. Мой путь в веб-программирование был классическим: PHP, MySQL и Apache. Среди php-разработчиков MySQL пользуется большей популярностью чем PostgreSQL, хотя последняя предоставляет функционал намного богаче. MySQL до сих пор остаётся лидером среди реляционных open source баз данных, поэтому давайте узнаем как с ней работать через Python.
Установка
В статье речь пойдёт про пакет PyMySQL, это реализация mysql-клиента на чистом Python, поэтому никакие дополнительный Си-библиотеки ставить не придётся. Пакет поддерживает работу с Python 2.7 и Python >= 3.5.
Для установки библиотеки выполняем стандартную команду:
Сейчас специализация в области data engineering активно набирает обороты. Судя по отчёту компании hired.com, спрос на data engineer специалистов вырос на 38%, и рост продолжится. Средняя зарплата у Data Engineer в Нью-Йорке составляет $132 тысячи, а в Сан-Франциско $151 тысячу. Что касается рынка СНГ, то спрос на дата инженеров только начинает расти. В России зарплатная вилка варьируется от 100 тысяч рублей до 250 тысяч. Эту информацию я получил из небольшого анализа открытых вакансий на ресурсах Мой Круг и HeadHunter.
Что такое Data Engineering
Из названия понятно, что область data engineering связана с данными, а именно с их доставкой, хранением и обработкой. Главная задача дата инженеров - обеспечить надёжную инфраструктуру для данных. Если обратиться к пирамиде AI, то data engineering занимает в ней первые 2-3 ступени: Collect, Move & Store, Data Preparation. Из этого следует вывод, что любой data-driven организации жизненно необходим data engineer, чтобы добраться до вершины. читать дальше
PostgreSQL, пожалуй, это самая продвинутая реляционная база данных в мире Open Source Software. По своим функциональным возможностям она не уступает коммерческой БД Oracle и на голову выше собрата MySQL.
Если вы создаёте на Python веб-приложения, то вам не избежать работы с БД. В Python самой популярной библиотекой для работы с PostgreSQL является psycopg2. Эта библиотека написана на Си на основе libpq.
Установка
Тут всё просто, выполняем команду:
pip install psycopg2
Для тех, кто не хочет ставить пакет прямо в системный python, советую использовать pyenv для отдельного окружения. В Unix системах установка psycopg2 потребует наличия вспомогательных библиотек (libpq, libssl) и компилятора. Чтобы избежать сборки, используйте готовый билд:
pip install psycopg2-binary
Но для production среды разработчики библиотеки рекомендуют собирать библиотеку из исходников.
Начало работы
Для выполнения запроса к базе, необходимо с ней соединиться и получить курсор:
Через курсор происходит дальнейшее общение в базой.
cursor.execute('SELECT * FROM airport LIMIT 10')
records = cursor.fetchall()
...
cursor.close()
conn.close()
После выполнения запроса, получить результат можно несколькими способами:
cursor.fetchone() — возвращает 1 строку
cursor.fetchall() — возвращает список всех строк
cursor.fetchmany(size=5) — возвращает заданное количество строк
Также курсор является итерируемым объектом, поэтому можно так:
for row in cursor:
print(row)
Хорошей практикой при работе с БД является закрытие курсора и соединения. Чтобы не делать это самому, можно воспользоваться контекстным менеджером:
from contextlib import closing
with closing(psycopg2.connect(...)) as conn:
with conn.cursor() as cursor:
cursor.execute('SELECT * FROM airport LIMIT 5')
for row in cursor:
print(row)
По умолчанию результат приходит в виде кортежа. Кортеж неудобен тем, что доступ происходит по индексу (изменить это можно, если использовать NamedTupleCursor). Если хотите работать со словарём, то при вызове .cursor передайте аргумент cursor_factory:
from psycopg2.extras import DictCursor
with psycopg2.connect(...) as conn:
with conn.cursor(cursor_factory=DictCursor) as cursor:
...
Формирование запросов
Зачастую в БД выполняются запросы, сформированные динамически. Psycopg2 прекрасно справляется с этой работой, а также берёт на себя ответственность за безопасную обработку строк во избежание атак типа SQL Injection:
cursor.execute('SELECT * FROM airport WHERE city_code = %s', ('ALA', ))
for row in cursor:
print(row)
Метод execute вторым аргументом принимает коллекцию (кортеж, список и т.д.) или словарь. При формировании запроса необходимо помнить, что:
Плейсхолдеры в строке запроса должны быть %s, даже если тип передаваемого значения отличается от строки, всю работу берёт на себя psycopg2.
Не нужно обрамлять строки в одинарные кавычки.
Если в запросе присутствует знак %, то его необходимо писать как %%.
Именованные аргументы можно писать так:
>>> cursor.execute('SELECT * FROM engine_airport WHERE city_code = %(city_code)s',
{'city_code': 'ALA'})
...
Модуль psycopg2.sql
Начиная с версии 2.7, в psycopg2 появился модуль sql. Его цель — упростить и обезопасить работу при формировании динамических запросов. Например, метод execute курсора не позволяет динамически подставить название таблицы.
>>> cursor.execute('SELECT * FROM %s WHERE city_code = %s', ('airport', 'ALA'))
psycopg2.ProgrammingError: ОШИБКА: ошибка синтаксиса (примерное положение: "'airport'")
LINE 1: SELECT * FROM 'airport' WHERE city_code = 'ALA'
Это можно обойти, если сформировать запрос без участия psycopg2, но есть высокая вероятность оставить брешь (привет, SQL Injection!). Чтобы обезопасить строку, воспользуйтесь функцией psycopg2.extensions.quote_ident, но и про неё легко забыть.
from psycopg2 import sql
...
>>> with conn.cursor() as cursor:
columns = ('country_name_ru', 'airport_name_ru', 'city_code')
stmt = sql.SQL('SELECT {} FROM {} LIMIT 5').format(
sql.SQL(',').join(map(sql.Identifier, columns)),
sql.Identifier('airport')
)
cursor.execute(stmt)
for row in cursor:
print(row)
('Французская Полинезия', 'Матайва', 'MVT')
('Индонезия', 'Матак', 'MWK')
('Сенегал', 'Матам', 'MAX')
('Новая Зеландия', 'Матамата', 'MTA')
('Мексика', 'Матаморос', 'MAM')
Транзакции
По умолчанию транзакция создаётся до выполнения первого запроса к БД, и все последующие запросы выполняются в контексте этой транзакции. Завершить транзакцию можно несколькими способами:
закрыв соединение conn.close()
удалив соединение del conn
вызвав conn.commit() или conn.rollback()
Старайтесь избегать длительных транзакций, ни к чему хорошему они не приводят. Для ситуаций, когда атомарные операции не нужны, существует свойство autocommit для connection класса. Когда значение равно True, каждый вызов execute будет моментально отражен на стороне БД (например, запись через INSERT).
with conn.cursor() as cursor:
conn.autocommit = True
values = [
('ALA', 'Almaty', 'Kazakhstan'),
('TSE', 'Astana', 'Kazakhstan'),
('PDX', 'Portland', 'USA'),
]
insert = sql.SQL('INSERT INTO city (code, name, country_name) VALUES {}').format(
sql.SQL(',').join(map(sql.Literal, values))
)
cursor.execute(insert)
В последнее время в экосистеме Python часто стали появляться инструменты для управления зависимостями. Оно понятно, стандартный pip уже не отвечает современным требованиям: неудобная работа с зависимостями, много ручной работы при подготовке пакетов, проблемы при установке и обновлении и много чего другого.
С недавних пор я начал использовать новый менеджер под названием Poetry. Именно о нём сегодня пойдёт речь.
Функциональные возможности Poetry:
Управление зависимостями через toml файл (прощай, requirements.txt)
Автоматическое создание изолированного виртуального окружения Python (теперь не нужно для этого вызывать virtualenv)
Удобное создание пакетов (отныне не нужно копипастить создавать setup.py каждый раз)
poetry.lock файл для фиксирования версий зависимостей
А особенно радует тандем при работе с pyenv. О pyenv я писал три года назад.
