Data Science. SQL hub

💻Как получить текстовый дамп базы данных Postgres в Heroku

Для создания дампа базы данных PostgreSQL в текстовом формате на Heroku выполните два действия:

1️⃣Создайте и загрузите резервную копию:

   heroku pg:backups:capture && heroku pg:backups:download
   

2️⃣Преобразуйте загруженный файл в SQL-формат с помощью pg_restore:

   pg_restore -f mydb.sql latest.dump
   

Здесь mydb.sql — это конечный файл с вашими данными. До начала работы убедитесь, что у вас установлен Postgres для использования команды pg_restore.

🔘Вот в принципе и всё, больше деталей и похожих кейсов тут

@sqlhub

💻Исправление клиентской кодировки PostgreSQL с LATIN1 на UTF8

▶️Итак, реальный кейс. На сайте был один баг, который проявлялся при попытке прикрепления к сообщениям файлов, названия которых были на кириллице. В данном случае сайт выдавал ошибку 503. Это происходило несмотря на то, что кодировка базы данных была в UTF8. Тогда как Django выдавал следующее сообщение:

'latin-1' codec can't encode characters in position 55-64: ordinal not in range(256)

Проблема заключалась в том, что кодировка сервера была первоначально выставлена в LATIN1. То есть следующие команды давали следующий вывод:

postgres=# show server_encoding; server_encoding ----------------- LATIN1(1 row) postgres=# show client_encoding; client_encoding ----------------- LATIN1(1 row) postgres=# \encoding  LATIN1

Выполнение команды SET CLIENT_ENCODING TO 'utf8'; не давало результата, после выхода из psql кодировка возвращалась в LATIN1 .


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

sudo apt-get install language-pack-ru

Выполнение процесса русификации

sudo update-locale LANG=ru_RU.UTF-8

И перезагрузка сервера

sudo reboot

После этого кодировка клиента сервера стала UTF8

postgres=# show server_encoding; server_encoding ----------------- LATIN1(1 row) postgres=# show client_encoding; client_encoding ----------------- UTF8(1 row) postgres=# \encoding  UTF8

Вот такие дела, этого оказалось достаточно для исправления бага.
Возможно, кому-то будет особенно актуально

@sqlhub

💻Как создать процедуру в PostgreSQL?

⏩Итак, для создания процедуры в PostgreSQL используется язык PL/pgSQL, который является расширением языка SQL. Вот пример создания простой хранимой процедуры в PostgreSQL:

CREATE OR REPLACE FUNCTION 'имя_процедуры(параметры)' RETURNS 'тип_результата' AS $$
DECLARE
    -- переменные_локальные тип_переменной
BEGIN
    -- Тело процедуры
    -- Может содержать SQL запросы, управляющие конструкции и другие операторы
END;
$$ LANGUAGE plpgsql;

⏩Пример простой процедуры, которая выводит текстовое сообщение:

CREATE OR REPLACE FUNCTION hello_world()
RETURNS VOID AS $$
BEGIN
    RAISE NOTICE 'Hello, World!';
END;
$$ LANGUAGE plpgsql;

⏩После выполнения этого запроса, процедура hello_world будет создана в вашей базе данных. Чтобы вызвать эту процедуру, используйте следующий запрос:

SELECT hello_world();

Таким образом, вы можете создавать и вызывать процедуры в PostgreSQL с помощью PL/pgSQL.

@sqlhub

💻PostgreSQL: исполнение запроса из Bash-скрипта

⏩Для выполнения запроса в PostgreSQL от имени пользователя 'postgres' используйте команду:

sudo -u postgres psql -d dbname -c "SELECT * FROM table;"

Здесь dbname — это название вашей БД, table – имя таблицы.
Разумеется, используем права суперпользователя sudo для переключения пользователя на postgres, psql является инструментом командной строки PostgreSQL, а ключ -c нужен для выполнения конкретного запроса.

⏩Для более аккуратного форматирования вывода можно использовать ключ -t, который выводит данные построчно, и -X для того, чтобы .psqlrc не обрабатывался командой psql.

⏩Подобное выполнение SQL-запросов прямо из оболочки может быть особенно актуальным, если вы настраиваете какую-нибудь систему по ssh.
Пишите, кстати, если сталкивались с такими кейсами

@sqlhub

💻Коллекция готовых SQL-запросов для PostgreSQL

Нереально полезная подборка SQL-запросов, количество запросов вы и сами видите, это покрывает большую часть того, что может встретиться в практике

В том числе здесь:
⏩Обсуждаются различные функции и операторы для выполнения запросов и модификации данных

⏩Рассматриваются способы разбиения больших таблиц на N тысяч записей и распараллеливания запросов

⏩Обсуждаются особенности сравнения record и NULL и способы быстрого получения количества записей в большой таблице

⏩Рассматриваются рекурсивные запросы, модификация пользовательских данных (UPSERT) и журналирование изменений таблицы

⏩Рассматриваются модификации схемы данных (DDL) и способы добавления ограничений таблицы и изменения ограничений внешнего ключа без блокирования таблицы

📎 Коллекция

@sqlhub

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

Очевидно, что это будет первая, либо последняя запись в этом столбце.

База данных Oracle знает об этом, поэтому может оптимизировать данный запрос:

INDEX FULL SCAN (MIN/MAX)

или

INDEX RANGE SCAN (MIN/MAX)

При этом запросе считывается только одна необходимая запись.

@sqlhub

💻Исследователи обнаружили, что GitHub Copilot генерирует уязвимые SQL-запросы (да и не только SQL-запросы)

⏩Не так давно исследователи компании Snyk, специализирующейся на безопасности, рассказали, что ИИ-помощник GitHub Copilot генерирует уязвимый код, если в проекте уже есть такой. Это связано с тем, что нейросеть просто анализирует кодовую базу, в том числе и плохие практики.

⏩Snyk провела эксперимент, чтобы доказать способность GitHub Copilot генерировать уязвимый код на основе такого же в проекте. На 1 этапе исследователи попросили ИИ-помощника сгенерировать SQL-запрос и получили это:

// create query to match input with the description or product name
var query = em.createQuery("SELECT p FROM Product p WHERE LOWER(p.description) like  OR lower(p.productName) like :input", Product.class);

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

⏩После этого в соседнем файле проекта самостоятельно написали уязвимый SQL-запрос и снова попросили нейросеть написать код. Во второй раз GitHub Copilot сгенерировал следующий фрагмент:

// create query to match input with the description or product name
String query = "Select * from Product where lower(description) like '%" + lowerInput + "%' OR lower(product_name) like '%" + lowerInput + "%'";

Для контекста нейросеть использовала уязвимый код и на его основе сгенерировала ещё одну ошибку.

Факторы, усугубляющие использование GitHub Copilot:

— Закрепление плохого подхода. Начинающие разработчики, использующие ИИ-помощников, не замечают своих ошибок и начинают думать, что если код сгенерировала нейросеть, то он верный.

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

— Устаревшие шаблоны. GitHub Copilot может предлагать фрагменты, которые в сообществе уже признаны неверными и содержащими ошибки.

Будьте аккуратнее, в общем)

@sqlhub

Яндекс подготовит на треть больше  топовых специалистов по ML и Data Science

Школа анализа данных Яндекса открыла набор на следующий учебный год. Школа решила увеличить число своих студентов — на обучение примут 400 человек, что обеспечит приток новых датасаентистов на рынок. Конкурс при приёме в ШАД остаётся стабильно высоким (17 человек на место в 2023 году), но добавляется новая возможность поступить — теперь абитуриенты смогут поступить также через очную олимпиаду.
В недавнем исследований ШАД выяснил, что 77% выпускников устраиваются в лидирующие  технологические компании, а каждый четвёртый работает в научной сфере.

Больше о карьерных треках выпускников ищите в карточках.

@sqlhub

▶️7 типов баз данных, понимание которых пригодится специалисту Data Science

⏩Иерархические БД — DNS, LDAP
В иерархических БД каждая запись имеет одного «родителя». Это создаёт древовидную структуру, в которой записи классифицируются по их отношениям с цепочкой родительских записей.

⏩Сетевые БД — IDMS
Сетевые БД расширяют функциональность иерархических: записи могут иметь более одного родителя. А значит, можно моделировать сложные отношения.

⏩Реляционные БД — PostgreSQL, MariaDB, SQLite, MySQL
Реляционные БД – старейший тип до сих пор широко используемых БД общего назначения. Данные и связи между данными организованы с помощью таблиц. Каждый столбец в таблице имеет имя и тип. Каждая строка представляет отдельную запись или элемент данных в таблице, который содержит значения для каждого из столбцов.

⏩Базы данных «ключ-значение» — Redis, Memcached
В БД «ключ-значение» для хранения информации вы предоставляте ключ и объект данных, который нужно сохранить. Например, JSON-объект, изображение или текст. Чтобы запросить данные, отправляете ключ и получаете blob-объект.

⏩Документная база данных — MongoDB, RethinkDB
Документные БД совместно используют базовую семантику доступа и поиска хранилищ ключей и значений. Такие БД также используют ключ для уникальной идентификации данных. Разница между хранилищами «ключ-значение» и документными БД заключается в том, что вместо хранения blob-объектов, документоориентированные базы хранят данные в структурированных форматах – JSON, BSON или XML.

⏩Графовая база данных — Neo4j, JanusGraph, Dgraph
Вместо сопоставления связей с таблицами и внешними ключами, графовые БД устанавливают связи, используя узлы, рёбра и свойства.

⏩Колоночные БД — Cassandra, HBase
Колоночные БД принадлежат к семейству NoSQL БД, но внешне похож на реляционные БД. Как и реляционные, колоночные БД хранят данные, используя строки и столбцы, но с иной связью между элементами.

📎 Читать подробнее

@sqlhub