👩‍💻 Временная таблица в базе данных SQL

〰️〰️〰️〰️〰️〰️〰️〰️

🔜 Временная таблица SQL (temp table) — это такая таблица, которую ты создаёшь и используешь в контексте определённого сеанса или транзакции в СУБД. Она предназначена для хранения временных данных, которые понадобятся на короткое время и не требуют постоянного хранения.

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

🔜 Чтобы создать временную таблицу, можешь использовать инструкцию CREATE TABLE с ключевым словом TEMPORARY или TEMP перед именем таблицы:

CREATE TEMPORARY TABLE my_temp_table (
    id INT,
    name VARCHAR(50),
    age INT
);

Детали:

🔜 Инструкция CREATE TEMPORARY TABLE используется для создания временной таблицы.

🔜 my_temp_table — это имя, которое присваивается временной таблице. Имя можно выбрать любое.

🔜 Внутри круглых скобок мы определяем столбцы временной таблицы.

🔜 В примере временная таблица my_temp_table имеет три столбца: id типа INT, name типа VARCHAR(50) и age типа INT.

🔜 При необходимости ты можешь добавить дополнительные столбцы, указав их имена и типы данных.

🔜 Временная таблица автоматически удаляется в конце сеанса или при завершении сеанса.

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

🔜 Создаёшь временную таблицу с подмножеством данных

CREATE TEMPORARY TABLE subset_data AS
SELECT column1, column2, column3
FROM original_table
WHERE condition;

🔜 Анализируешь подмножества данных

SELECT column1, AVG(column2) AS average_value
FROM subset_data
GROUP BY column1;

🔜 Удаляешь временную таблицу

DROP TABLE subset_data;

👩‍💻 Повышение производительности запросов с использованием временных таблиц

〰️〰️〰️〰️〰️〰️〰️〰️

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

Такое повышение происходит за счёт уменьшения объема данных, обрабатываемых на каждом этапе, или за счёт предварительного вычисления промежуточных результатов.

Временные таблицы позволяют хранить и повторно использовать промежуточные результаты запроса, избегая лишних вычислений. Пример:

➡️ Создаёшь временную таблицу для хранения промежуточных результатов

CREATE TEMPORARY TABLE temp_results AS
SELECT column1, COUNT(*) AS count_value
FROM large_table
WHERE condition1
GROUP BY column1;

➡️ Используешь временную таблицу для оптимизации итогового запроса

SELECT column1, column2
FROM temp_results
WHERE count_value > 10
ORDER BY column1;

➡️ Удаляешь временную таблицу

DROP TABLE temp_results;

👩‍💻 Подготовка и преобразование данных во временных таблицах

〰️〰️〰️〰️〰️〰️〰️〰️

Временные таблицы также полезны для подготовки и преобразования данных перед их загрузкой в постоянные таблицы.

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

Временные таблицы обеспечивают гибкий и эффективный способ обработки и манипуляцией данными без изменения исходного набора. Пример:

➡️ Создаёшь временную таблицу для подготовки данных

CREATE TEMPORARY TABLE staging_table (
    id INT,
    name VARCHAR(50),
    quantity INT
);

➡️ Импортируешь и преобразовываешь данные в стейджинг таблицу

INSERT INTO staging_table (id, name, quantity)
SELECT id, UPPER(name), quantity * 2
FROM external_source;

➡️ Валидируешь и проводишь манипуляции с данными в стейджинг таблице

UPDATE staging_table
SET quantity = 0
WHERE quantity < 0;

➡️ Вставляешь преобразованные данные в таргет таблицу

INSERT INTO final_table (id, name, quantity)
SELECT id, name, quantity
FROM staging_table;

➡️ Удаляешь временную таблицу

DROP TABLE staging_table;

👩‍💻 Кортежи


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

📌 Если хочешь явно узнать, является ли значение кортежа, да и любого другого объекта, фиксированным, можешь использовать встроенную функцию hash и сделать на её основе функцию is_fixed, например такую:

def is_fixed(obj) -> bool:
    try:
        hash(obj)
    except TypeError:
        return False
    return True

#python #trix

👩‍💻 Кортежи


К посту выше. Важно не забывать, что неизменность кортежа относится только к хранящимся в нём ссылкам - их нельзя ни удалить, ни изменить. Но если какая-то ссылка указывает на изменяемый объект и этот объект будет изменён, то значение кортежа изменится.

Пример, изначально два кортежа равны:

a = (1, 'data engi', [8, 9])
b = (1, 'data engi', [8, 9])

print(a == b)

Получим результат:

True

Но при внесении изменений в последний элемент кортежа b:

b[-1].append(10)

print(a == b)

Получим результат:

False

👩‍💻 Рекомендации при работе с PostgreSQL

▶️ Используй отдельные схемы для разных модулей

Скажем, тебе необходимо реализовать систему рассылки пуш-уведомлений на android-устройства клиентов. Ты пишешь сервис, который будет работать с FCM. Пишешь функции в своей БД, которые будут выдавать списки token-текст (да там много полей вообще).
Так вот, необходимо, чтобы такие ручки для разных модулей (бэкендов) лежали в отдельных схемах.

▶️ Используй группы ролей и назначай гранты именно на роли

Предположим, что ты реализовал пункт выше, теперь твой сервис должен подключаться к БД и крутить свои запросы. Но под каким пользователем? Не будет же он бегать под postgres или admin?)

▶️ Используй расширения

PostgreSQL — это мощная СУБД. Но и она не может быть универсальной. Не надо полагать, что всё делается штатными средствами PostgreSQL. Так-же не надо пробовать писать свои системы чего-либо.

▶️ Храни дататайм в timestamptz

Возьми за привычку в БД всегда хранить timestampTZ и когда-нибудь Тебе скажут большое спасибо за это.

▶️ Используй метрики

Пользуйся pg_stat_statements
Так-же рекомендую сразу установить расширения для мониторинга запросов (например pg_stat_statements)

▶️ В функциях лучше использовать CTE и длинные запросы, чем временные таблицы

PostgreSQL всегда временные таблицы кладёт на диск, это занимает много времени. Используй табличные выражения. Да и лучше всё писать одним оператором.

▶️ Не делай партиции везде, где только можно

Да да, партиции — это круто. Но вот их использовать я бы рекомендовал только в некоторых случаях

▶️ Не забывай делать дополнительную статистику

Да, когда планировщик не так представляет выхлоп — у тебя могут получиться очень медленные запросы.

▶️ Старайся не пихать всё в TOAST

Наверное сразу стоит добавить и тот пункт, что не надо хранить в TOAST, например json/text с дефолтным параметром хранения, аналитические данные.

#postgres

📚 Книги по некоторым технологиям обработки данных
〰️〰️〰️〰️〰️〰️〰️〰️

📎 Spark: The Definitive Guide
В книге рассказывается, как использовать, развёртывать и поддерживать Apache Spark с помощью этого всеобъемлющего руководства, написанного создателями фреймворка кластерных вычислений с открытым исходным кодом.

📎 Hadoop. Подробное руководство
Книга, в которой подробно и доходчиво описаны все возможности Apache Hadoop.

📎 Apache Kafka. Потоковая обработка и анализ данных
В книге описаны принципы проектирования брокера больших данных Kafka, гарантии надёжности, ключевые API и архитектурные детали

📎 Kubernetes в действии
В книге подробно рассказывается о Kubernetes - софт Google с открытым исходным кодом для автоматизации развёртывания, масштабирования и управления приложениями, масштабированием и управлением приложениями с большими данными

📎 Cassandra The Definitive Guide: Distributed Data at Web Scale
В этом руководстве объясняется, как СУБД Cassandra обрабатывает сотни терабайт данных, поддерживая высокую доступность данных в нескольких центрах обработки данных

📎 MongoDB: полное руководство В этой книге подробно рассматривается MongoDB, мощная СУБД. Здесь ты сможешь узнать, как эта безопасная, высокопроизводительная система обеспечивает гибкие модели данных, высокую доступность данных и горизонтальную масштабируемость.

👩‍💻 match/case

Для версий Python 3.10 и выше.

Деструктуризация с помощью образцов настолько выразительна, что иногда даже наличие единственной ветви case может сделать код проще. Гвидо ван Россум собрал коллекцию примеров match/case, один из которых назвал «Очень глубокий итерируемый объект и сравнение типа с выделением».

Это здорово, но меня больше поразил тот факт, что можно проверять типы и насколько выразительно это выглядит, ниже смотри пример.

❤️‍🩹 Классическая версия:

 def write_value(self, value):
    if isinstance(value, str):
        self.simple_element("string", value)

    elif value is True:
        self.simple_element("true")

    elif value is False:
        self.simple_element("false")

    elif isinstance(value, int):
        if -1 << 63 <= value < 1 << 64:
            self.simple_element("integer", "%d" % value)
        else:
            raise OverflowError(value)

    elif isinstance(value, float):
        self.simple_element("real", repr(value))

    elif isinstance(value, dict):
        self.write_dict(value)

    elif isinstance(value, (bytes, bytearray)):
        self.write_bytes(value)

    elif isinstance(value, datetime.datetime):
        self.simple_element("date", _date_to_string(value))

    elif isinstance(value, (tuple, list)):
        self.write_array(value)

    else:
        raise TypeError("unsupported type: %s" % type(value))

🔥 Версия с использованием match/case:

 def write_value(self, value):
    match value:
        case str():
            self.simple_element("string", value)

        case True:
            self.simple_element("true")

        case False:
            self.simple_element("false")

        case int():
            if -1 << 63 <= value < 1 << 64:
                self.simple_element("integer", "%d" % value)
            else:
                raise OverflowError(value)

        case float():
            self.simple_element("real", repr(value))

        case dict():
            self.write_dict(value)

        case bytes() | bytearray():
            self.write_bytes(value)

        case datetime.datetime():
            self.simple_element("date", _date_to_string(value))

        case tuple() | list():
            self.write_array(value)

        case _:
            raise TypeError("unsupported type: %s" % type(value))

#python #casematch

👩‍💻 Автогенерация первичного ключа

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

⏩ Автогенерация работает по следующим принципам:
🔘 Внутри базы создаётся отдельный счетчик, который привязывается к каждой таблице
    🔘 Счётчик увеличивается на единицу при вставке новой строки
    🔘 Получившееся значение записывается в поле, которое помечается как автогенерируемое

⏩ Автогенерацию первичного ключа часто называют автоинкрементом (autoincrement). Что переводится как автоматическое увеличение и напоминает операцию инкремента из программирования ++.

⏩ До определённого момента механизм автоинкремента был реализован в каждой СУБД разными способами. Это создавало проблемы при переходе от одной СУБД к другой и усложняло реализацию программного слоя доступа к БД.

⏩ Эта функциональность добавлена в стандарт SQL:2003, то есть довольно давно. И только в 2018 году PostgreSQL в версии 10 стал его поддерживать. Такой автоинкремент известен под именем GENERATED AS IDENTITY:

CREATE TABLE colors (
  -- Одновременное использование и первичного ключа и автогенерации
  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name varchar(255)
);

INSERT INTO colors (name) VALUES ('Red'), ('Blue');

SELECT * FROM colors;

-- id    name
-- 1     Red
-- 2     Blue

Если удалить запись с id равным двум и вставить ещё одну запись, то значением поля id будет 3. Автогенерация не связана с данными в таблице. Это отдельный счётчик, который всегда увеличивается. Так избегаются вероятные коллизии и ошибки, когда один и тот же идентификатор принадлежит сначала одной записи, а потом другой.

⏩ Структура из документации:

column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

👩‍💻 Используй объект slice для именованных срезов:

invoice = """
0.....6.................................40........52...55........
1909 Pimoroni PiBrella                      $17.50    3    $52.50
1489 6mm Tactile Switch x20                  $4.95    2    $9.90
1510 Panavise Jr. - PV-201                  $28.00    1    $28.00
1601 PiTFT Mini Kit 320x240                 $34.95    1    $34.95
"""

SKU = slice(0, 5)
DESCRIPTION = slice(5, 40)
UNIT_PRICE = slice(40, 52)
QUANTITY = slice(52, 55)
ITEM_TOTAL = slice(55, None)

line_items = invoice.split('\n')[2:]

for item in line_items:
    print(item[UNIT_PRICE], item[DESCRIPTION])

Результат:

$17.50   Pimoroni PiBrella                  
 $4.95   6mm Tactile Switch x20             
$28.00   Panavise Jr. - PV-201              
$34.95   PiTFT Mini Kit 320x240             

#python #slice

⚡️ ClickHouse vs Greenplum


Clickhouse и GreenPlum - известные СУБД для анализа больших данных, которые пользуются большой популярностью. Однако существуют критерии, по которым необходимо однозначно выбирать, какую из СУБД использовать в той или иной ситуации. Для этого рассмотрим их основные преимущества и недостатки.


🙂 Преимущества ClickHouse:

1️⃣ Высокая производительность:

ClickHouse предназначена для решения аналитических задач и обладает высокой скоростью выполнения запросов на чтение больших объемов данных. Это делает его идеальным выбором для аналитики данных и OLAP (Online Analytical Processing).

2️⃣ Эффективное сжатие данных:

ClickHouse использует различные методы сжатия данных, которые позволяют значительно сократить объем хранимой информации без потери производительности.

3️⃣ Горизонтальное масштабирование:

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

☹️ Недостатки ClickHouse:

1️⃣ Ограниченная поддержка транзакций:

ClickHouse ориентирован в основном на аналитические задачи и не имеет полной поддержки транзакций, что может быть проблемой для некоторых приложений.

2️⃣ Ограниченный набор функций:

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


😍 Преимущества Greenplum:

1️⃣ Поддержка транзакций:

Greenplum обеспечивает полную поддержку транзакций и ACID (Atomicity, Consistency, Isolation, Durability), что делает его идеальным выбором для OLTP (Online Transactional Processing) и OLAP-приложений.

2️⃣ Широкий спектр возможностей:

Greenplum предлагает богатый набор встроенных функций и возможностей аналитической обработки, что делает его пригодным для решения различных типов аналитических задач.

3️⃣ Поддержка распределённых транзакций:

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

🙃 Недостатки Greenplum:

1️⃣ Сложность управления:

Greenplum может потребовать больше усилий и опыта для управления и настройки, особенно при работе с большими кластерами.

2️⃣ Менее эффективное сжатие данных:

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

〰️〰️〰️〰️〰️〰️〰️〰️

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

📄 MAD 2024

https://mattturck.com/landscape/mad2024.pdf

🖼️ Загадка

t = (1, 2, [3, 4])
t[2] += [5, 6]