дата инженеретта

Теория или практика? 📖 Наконец-то прочитала книжку "Spark в действии". Очень понравился вот этот рисунок: он супер понятно описывает суть ленивых вычислений) Spark копит все преобразования над данными, пока что-то не заставит его запустить весь пайплайн (например, show). Отметила для себя пару новых моментов: 1️⃣ Можно создать табличку через sql и сразу перевести её в датафрейм (я думала, что такое работает только для реальных таблиц) Например:

spark.sql(
  'select id from users'
) \
.createOrReplaceTempView('view_users')
df_users = spark.table('view_users')

2️⃣ Есть интересные методы сортировки asc_nulls_first, asc_nulls_last (для desc так же), чтобы обозначить порядок для нуллов 🐰 Ещё позабавили некоторые формулировки)

"Агрегации - ... в известной степени приближение к методам машинного обучения и искусственного интеллекта".

"Во время моей работы со Spark я встречал два типа людей: тех, кто имел опыт работы с Apache Hadoop, и тех, у кого не было такого опыта".

Надеюсь, что это трудности перевода)) 🐱 Вообще пришло осознание, что теоретические книжки легко читаются, когда ты уже пощупал это на практике. А вы как считаете, когда нужно читать умные книжки? #spark

Написала статью про планы запросов! Пару месяцев лежала в бэклоге, месяц собиралась в голове, 3 дня писалась, и теперь она здесь) Сесть и сделать сподвигла последняя дз по курсу амбассадоров. Там был выбор между статьёй и подкастом. Но до подкастов я как-нибудь тоже доберусь) https://habr.com/ru/articles/807421/

Логика с json'ами На прошлой неделе я вкидывала задачу. Мы ещё раз пообсуждали и вот на чем остановились: 1️⃣Делаем фулл джойн исходных витрин: view1, view1_1, ... Идею с кросс джойнами мы решили оставить) 2️⃣Делим фулловую таблицу на n вьюшек, где n - это количество шагов. Начинаем с конца (последнего заполненного). Пример: when step1_2 is not null => v_step1_2 - новая вьюшка для строк, у которых есть степ1_2: 3 null null json 5 json null json 6 null json json 7 json json json 3️⃣Делаем left anti join фулловой таблицы с созданной вьюшкой, получаем строки, которые не вошли: 1 json null null 2 null json null 4 json json null Повторяем шаги 2 и 3 n раз. NB! В следующих вьюшках количество столбцов уменьшается (исключаем уже выделенный степ) 1) when step1_1 is not null => v_step1_1 2 null json 4 json json После left anti join обновленная фулловая: 1 json null null На последнем этапе просто берем id и первый столбец. Итак, у нас 3 вьюшки с разным количеством столбцов. ️4️⃣Делаем coalesce(stepn, default_json) 5️⃣Делаем array(всех шагов) => получаем одинаковую структуру для всех вьюшек с полями id и array 6️⃣Union 🕺🕺🕺🕺🕺🕺🕺

👫Про нетворкинг На этих выходных я сходила на сходку аналитиков, где мы пообщались на разные темы и поделились своими мнениями. 👋Сначала мы все знакомились: удивительно, но знакомство 12 человек друг с другом заняло больше часа) Рассказали, кто где работает, чем занимается, кратенькую историю жизни. Потом обсудили несколько резюме. На всякий случай я писала заметки себе на будущее, и вот что туда попало: 1️⃣ Работу гораздо проще найти, написав напрямую тимлидам из телеграм-чатиков, чем через hh. 2️⃣ Нужно уметь хорошо списывать) Например, если формат и наполнение резюме всем заходит, то не надо прикручивать велосипеды. 3️⃣ Очень удобно, если есть стек и он сгруппирован по тегам: языки программирования, базы данных и т.д. (мнение от hr) 4️⃣ Ключевая структура: - что делал - чем делал (инструменты) - какая была цель - какой результат и вэлью Результатом может быть увеличение хорошей метрики, уменьшение плохой метрики или стабилизация/сохранение какой-то метрики. Я сформулировала это так: все continuous задачи перевести в done. 5️⃣ Можно рассказывать про свои пет-проекты на собесах (и даже шерить экран!) 6️⃣ Если проходили курсы, то можно их вписать в место работы с пометкой "учебный опыт", но обязательно нужно уйти от шаблонности других выпускников курса. Возможно, докрутить на проекты что-то свое, что будет выгодно отличать вас от остальных. ☕️Обсудили, что у рабочих задач, в отличие от учебных, может не быть ответа, что любая задача становится нерешимой, если провести достаточное количество совещаний, и top management driven development) Обсудили свои планы, идеи, решения, что я прямо замотивировалась встать и начать делать! Всего мы просидели 6 часов, но я немного огорчилась, что так мало!

Помогите закрыть таску!😘 💻Исходные данные У нас есть три вьюшки с id и json-полем. Вьюшки собираются по разным фильтрам. 📌Цель: собрать массив степов, при этом дополнить предыдущие недостающие степы значением default_json. 🎥Пример: для id = 1: [json] для id = 2: [default_json, json] для id = 3: [default_json, default_json, json] и т.д. При этом в default_json прокидывается текущий степ, поэтому нам нужно не потерять это название. 🚀Пока идея такая: - делаем full join - делаем unpivot - добавляем флаг is_missed на основе null - итерируемся с конца в рамках id и удаляем все is_missed = 1, пока не найдём is_missed = 0 Лично мне это кажется слишком громоздкий и малочитаемым, поэтому хочется двинуться в сторону unuon, но тогда теряется текущий степ. Или после джойна в рамках строки как-то найти последнее заполненное поле. 🤩Any ideas?

💡Ответ💡 💻На скринах ответы ChatGPT 3.5 на английском и русском. В общем, иногда дает правильный ответ, но потом его заносит даже в новом диалоге. На русском получить норм ответ у меня так и не получилось. 4й дает примерно такие же результаты, за исключением нейминга и кучи переносов строк)) Домашку проверила) В целом, у всех плюс-минус похожие варианты❤️ Отметила несколько моментов: 1️⃣ агрегирующую функцию можно совмещать с оконкой 2️⃣ чтобы учесть одинаковые метрики, я бы использовала rank/dense_rank вместо row_number 3️⃣ косячные фильтры на дату

--тут не войдет 31 число
WHERE datetime >= '2024-03-01' AND datetime < '2024-03-31' 

--тут за 31.03 войдет только 00:00:00
WHERE datetime  between '2024-03-01' AND '2024-03-31'

--поэтому так правильнее
WHERE datetime > '2024-03-01' AND datetime < '2024-04-01'

Ну, и всякие extract/date_part тоже подойдут.

Задачка Есть 3 таблички: 1️⃣ Cities (id, city, region) 2️⃣ Couriers (id, fio) 3️⃣ Orders (id, datetime, cost, city_id, courier_id) Найти топ 10 курьеров для каждого города, которые доставили больше всего заказов за март.

👍Полезные советы В продолжение поста про вредные советы есть еще несколько лайфхаков (уже нормальных!): 1️⃣ Бывает полезно сделать такое форматирование, когда мы играемся с полями, чтобы удобнее закомментить ненужные:

SELECT 
    e.emp_no
    , e.first_name
    --, e.last_name
    , s.salary
FROM employees e
  JOIN salaries s
    ON e.emp_no = s.emp_no

2️⃣ Добавлять элиасы таблиц к полям (sql или spark - неважно), чтобы под конец огромного запроса понимать, откуда тянутся данные. То же самое 👆на спарке:

final_df = (
  employees.alias('e')
  .join(salaries.alias('s'), F.col('e.emp_no') == F.col('s.emp_no'), 'inner')
  .select(
    'e.emp_no',
    'e.first_name',
    's.salary'
  )
)

3️⃣ А когда мы играемся с условиями, я частенько добавляю 1=1, чтобы тоже быстро закомментить и прогнать:

SELECT *
FROM employees
WHERE 1=1
     --AND date_joined <= '2024-01-01'
     AND is_deleted = 0
     AND username != 'system'

Если бы WHERE date_joined <= '2024-01-01' шло на первом месте, то пришлось бы убирать AND перед вторым условием, удалять лишние пробелы - в общем, неудобно, если часто так делать. #sql_tips #spark

✨День демо✨ Сегодня у нас прошло очередное демо трайба! 🏫Вкратце о структуре Команда ➡️ Кластер ➡️ Трайб В кластере несколько команд, а в трайбе несколько кластеров (всего где-то 15 команд +-). Что было? 🎙Спикеры из команд рассказывали про итоги первого квартала 2024 года, какие мы молодцы, что получилось, что не получилось, какие планы на следующие периоды. Кейсы, графики и цифры, Цифры, ЦИФРЫ... ⌨️В перерыве работали тестовые стенды, где можно было сходить к нашим коллегам, пощупать их продукты и понять, чем они вообще занимаются) Ну, и вкусный фуршетик🍿

Как из деления int/int получить double? 🤓Вам нужно что-то посчитать: поделить одно количество на другое и округлить результат до 2х знаков после запятой. Так как количество - это целое число, если написать просто count1/count2, то в результате мы получим тоже целое число: 15/26 = 0. А мы хотим 0.57692307692307692308 - вот оно самое. ✨Поэтому умножаем на 1.0, и вуаля✨

SELECT round(count1 * 1.0 / count2, 2)
FROM test

#sql_tips

count(), F.count(), F.countDistinct(), distinct() В спарке есть несколько функций count, которые меня вначале путали, и тут я хочу расставить все точки над i. 1️⃣ count() - это действие Оно запустит весь пайплайн расчетов и выведет на экран одно число - количество строк в датафрейме:

df.count()

Также можно использовать в агрегации, но нельзя поменять название столбца (будет count):

df.groupBy('category')
  .count()
  .orderBy('count')
  .show()

2️⃣ F.count() - это трансформация, агрегирующая функция

df.select(F.count('id')).show()

В отличие от первого способа в агрегации можно поменять название столбца:

df.groupBy('category')
  .agg(F.count('id').alias('cnt'))
  .orderBy('cnt')
  .show()

Отличия count() и F.count():

# результат - датафрейм с одной ячейкой
df1.select(F.count('id')).show()
# +---------+
# |count(id)|
# +---------+
# |        3|
# +---------+

# результат - число
df1.select('id').count()
# 3

3️⃣ F.countDistinct() - тоже агрегирующая функция

df.select(F.countDistinct('id')).show()

Тут вариант агрегации только один, потому что df.countDistinct() не существует:

df.groupBy('category')
  .agg(F.countDistinct('id'))
  .show()

При этом в спарке есть еще функция distinct(), которая тоже вносит некую путаницу. Отличия distinct().count() и F.countDistinct():

# результат - датафрейм с одной ячейкой
df1.select(F.countDistinct('id')).show()
# +------------------+
# |count(DISTINCT id)|
# +------------------+
# |                 2|
# +------------------+

# результат - число
df1.select('id').distinct().count()
# 2

#spark

Про SQL Wildcards Я решила поэкспериментировать с форматом и посмотреть, что из этого выйдет) ⭐Все знают про %, но про остальные я думала, что они существуют в регулярках в программировании, но никак не в эскуэль. Пока не увидела в коде запросов, например:

--эквивалентны
and Action_Code like 'K[HS]'

and Action_Code = 'KH' or Action_Code = 'KS'

--тоже эквивалентны
and Assignment_Value like '%CRE[12]%'

and Assignment_Value like '%CRE1%' or Assignment_Value like '%CRE2%'

Для любителей почитать вот ссылки на оф. доки: Postgres MS SQL Server MySQL #sql_tips

Как уменьшить размер бд? 💿На одном проекте в разгар сезона часто не хватало места на диске, чтобы создать новую базу данных. В таких крайних случаях можно сжать существующие (редко используемые) базы командой:

DBCC SHRINKDATABASE([db_name], 0);  
GO

Диалект - MS SQL Server. Вторым аргументом указывается процент свободного места для бд в диапазоне 0-100. Чем его больше, тем быстрее происходит вставка данных, есть запас для изменения структуры и наполнения полей и т.д. ➡️Операция сжатия на больших объемах: - супер долго выполняется - снижает эффективность индексов и производительность (т.к. под капотом страницы с данными перемещаются) ✏️Но если в бд не пишется много данных или вообще не пишется, а дела с местом на диске прямо критичны, то это как вариант. #sql_tips

Всем привет! Кажется, у нас набралось достаточно данных для анализа моих любимых подписчиков, поэтому предлагаю потыкать)

💡Сортировка в Spark💡 Правильные ответы - все, кроме 'id'.desc() sort vs orderBy - что вам больше нравится, никакой разницы Способов тоже много на ваш выбор: 1️⃣ F.col().desc() Через метод столбца. Мы можем навесить функцию только на объект типа колонка, на обычную строку - нет:

df.orderBy(F.col('id').desc())

2️⃣ ascending=False Через свойство, в сортировке - строка или колонка:

df.orderBy('id', ascending=False)
df.orderBy(F.col('id'), ascending=False)

3️⃣ F.desc() Через функцию, аргумент - строка или колонка:

df.orderBy(F.desc('id'))
df.orderBy(F.desc(F.col('id')))

4️⃣ spark.sql Для особо ленивых)

spark.sql('select * from my_table order by id desc')

#spark

🤭Вредные советы Недавно ходила на сходку ODS (Open Data Science) и познакомилась с одним дата аналитиком. В общих чертах состоялся такой диалог: - Чем вы занимаетесь? - Шатаю таблички. - И каким образом? - Да просто select *, и все) Так вот этот пост про do и don'ts в сфере работы с данными: 1️⃣ Всегда делайте SELECT *, а не только нужные поля - вдруг они пригодятся в будущем? И никаких LIMIT - мы не хотим делать выводы на крошечной выборке 2️⃣ Никогда не навешивайте индексы, они просто забивают память 3️⃣ Добавляйте как можно больше джойнов в один запрос - надо экономить место 4️⃣ Никогда не проверяйте, какие типы данных сопоставляются в ON, WHERE и т.д. - лучше сделайте побыстрее и идите отдыхать 5️⃣ Считайте агрегации несколько раз вместо использования cte или temp табличек - это чисто синтаксический сахар 6️⃣ Cross join - наше все 7️⃣ Вставляйте как можно больше OR, не пытайтесь заменить на IN, UNION и т.д. 8️⃣ Если нужен DISTINCT, он должен быть в каждом подзапросе - для нашей 200% уверенности 9️⃣ Навешивайте на фильтруемые поля кучу функций - UPPER, LOWER, LEFT, RIGHT... Ну а

WHERE UPPER(name) LIKE '_Mary%'

- вообще песня! _ ⏩1 символ %⏩0 или много символов 🔟 Чем больше подзапросов - тем выше ваша зарплата💵

💡Ответ💡 Правильные варианты - со 2 по 6. При конвертации UTC -> UTC+3 добавляется 3ч ко всем строкам, поэтому период начинается с 21:00 (UTC) = 00:00 (UTC+3) и заканчивается тоже в 21:00, потому что в between самое крайнее время - это не конец дня, а начало дня:

--это одинаковые условия
BETWEEN '2024-02-24' AND '2024-02-25'

BETWEEN '2024-02-24 00:00:00' AND '2024-02-25 00:00:00'

Зачем? 🍊Как-то раз мне нужно было проверить, долетают ли данные из одной системы в другую. Но каунты тотально не сходились. Потом я увидела, что день начинался в 21:00 предыдущего дня и заканчивался в 20:59 текущего. Так, разница в 3 часа, значит, связано с конвертацией дат в UTC - но это было не совсем так! 😜Много времени на выяснение обстоятельств, а суть заключалась в использовании идешки. Я работала в DBeaver, а когда написала тот же запрос в PyCharm - даты отображались корректно, с 00:00 до 23:59. Возможно, во всем виноваты настройки, но так и не получилось с ними разобраться. PyCharm люблю всей душой👩‍💻 Код потестить (ClickHouse):

CREATE TABLE dates (
  `datetime` datetime('UTC')
)
ENGINE = MergeTree()
ORDER BY `datetime`;

INSERT INTO dates VALUES
('2024-02-23 20:59:00'), ('2024-02-23 21:00:00'), ('2024-02-23 23:59:00'), ('2024-02-24 00:00:00'), ('2024-02-24 02:59:00'), ('2024-02-24 20:59:00'), ('2024-02-24 21:00:00'), ('2024-02-24 22:00:00'), ('2024-02-25 00:00:00'), ('2024-02-25 02:59:00');

SELECT
  `datetime`,
  toDateTime(`datetime`, 'Europe/Moscow'),
  CASE
    WHEN toDateTime(`datetime`, 'Europe/Moscow') BETWEEN '2024-02-24' AND '2024-02-25' THEN 1
    ELSE 0
  END AS flag
FROM dates
ORDER BY 1;