Работа с таблицами в Hive

В Hive есть два типа таблиц:
🔘internal (managed)
🔘external (not managed)

Managed означает, что Hive в ответе за тех, кого приручил🌹

😑Internal таблица похожа на обычную таблицу из любой СУБД - она хранит данные и метаданные (в Hive Metastore) о них. Туда можно вставлять данные, из неё можно читать. А ещё она поддерживает кэширование (переиспользование уже посчитанного запроса в других) и, начиная с определённой версии движка, ACID-транзакции. Если удалить такую таблицу, данные тоже удалятся, тут как обычно.

Пример создания таблицы, партиционированной по дате, которая хранится в виде паркет-файлов:

CREATE TABLE web.visits (
  visitid int,
  url string
)
PARTITIONED BY (dt string)
STORED AS PARQUET;

🤗А еще есть External таблица, которая хранит только метаданные о том, где лежат файлики и как их оттуда читать. Многие функции вроде ACID-транзакций и кэширования не поддерживаются. Если таблицу удалить, данные останутся лежать на дисках.

По созданию то же самое, нужно только дописать ключевое слово external:

CREATE EXTERNAL TABLE web.visits (...)

Кстати, на днях мы пытались считать данные из таблицы через оркестратор и столкнулись с ошибкой "Your client does not appear to support insert-only tables". Оказалось, что по дефолту у нас на кластере таблицы создаются транзакционными и нужно добавить опцию:

ALTER TABLE web.visits SET TBLPROPERTIES('transactional'='false');

😶При этом эти настройки не сработали с managed табличкой, вероятно, при настройке кластера добавили параметр hive.strict.managed.tables=true, который запрещает создание управляемых и нетранзакционных таблиц. Но с external этот метод сработал!

👫Если вы хотите узнать побольше о том, какие полезные команды используются в Hive почти каждый день, смотрите вторую часть поста https://news.1rj.ru/str/rzv_de/132
А еще можете пройтись и по другим заметкам)

#hive

Выходные прошли, погнали снова работать)

Есть ли разница в запросах?

--1
SELECT *
FROM table1 t1
  JOIN table2 t2
    ON t1.id = t2.id or t1.name = t2.name;

--2
SELECT *
FROM table1 t1
  JOIN table2 t2
    ON t1.id = t2.id
UNION ALL
SELECT *
FROM table1 t1
  JOIN table2 t2
    ON t1.name = t2.name;

Опрос ниже👇

💡Ответ 💡

👨‍💻Немного поресерчила, погоняла запросы в песочнице в постгре и на кластере на спарке. Оказалось, что не все однозначно (как и показали результаты нашего опроса). Это зависит от:
🔘базы и работы оптимизатора
🔘количества данных
🔘логики данных (может, юнион нам вообще не подходит)

Смотрим план!

1️⃣: Nested Loop/Cartesian Product
2️⃣: Hash Join/SortMergeJoin

Кажется, что union сильно лучше, т.к. не происходит комбинаторного взрыва. Когда я работала на MS SQL, то сталкивалась с подобными запросами с OR в условии джойна, и они подвисали конкретно. Но на медиуме есть статейки/комменты для обеих сторон.

В общем, it depends)
Смотрите планы запросов, замеряйте время, понимайте логику данных, выбирайте то, что лучше при ваших условиях😌

Про личный бренд

😯Недавно у нас стартанул курс по амбассадорству "Академия амбассадоров Сбера", где я буду учиться на протяжении 6 недель, и это моя домашка)

Там есть два трека: блогер и эксперт, - оказалось, что я эксперт с блогом. Буду хорошо учиться, чтобы постить сюда еще более крутой контент💪

Планирую пробовать новые форматы (даже если некомфортно) и прокачивать себя. Как говорится, "Иди туда, где страшно"🌝

Нас ждут двухчасовые встречи с экспертами😮 Надеюсь, они помогут осознать себя в пространстве и дадут крутые наводки к публичным выступлениям на проф. конфах.

▶️Так что когда-нибудь обязательно ждите на экранах ваших смартфонов!
Пока все!

💡Ответ💡

Правильные варианты - со 2 по 7.

При конвертации 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
  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;

🤭Вредные советы

Недавно ходила на сходку 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 или много символов

🔟 Чем больше подзапросов - тем выше ваша зарплата💵

💡Сортировка в 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

Всем привет!

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

Сколько у вас опыта работы ✨в сфере данных✨?

Как уменьшить размер бд?

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

DBCC SHRINKDATABASE([db_name], 0);  
GO

Диалект - MS SQL Server.
Вторым аргументом указывается процент свободного места для бд в диапазоне 0-100. Чем его больше, тем быстрее происходит вставка данных, есть запас для изменения структуры и наполнения полей и т.д.

➡️Операция сжатия на больших объемах:
- супер долго выполняется
- снижает эффективность индексов и производительность (т.к. под капотом страницы с данными перемещаются)

✏️Но если в бд не пишется много данных или вообще не пишется, а дела с местом на диске прямо критичны, то это как вариант.

#sql_tips