Датасет слов английского языка

Обнаружил, что у Оксфордского университета есть списки распространенных слов и выражений английского языка. Доступны в традиционно «удобном» формате — html-амбразуре на сайте либо PDF.

Извлек их и сделал нормальные наборы данных в CSV.

Атрибутика:

— слово
— часть речи
— уровень (A1, A2, B1, B2, C1)
— ссылка на подробное определение
— ссылка на озвучку в ogg

https://github.com/nalgeon/words

Если вы написали отличную статью, о которой никто не знает

В русскоязычном айти есть несколько «селебрити», которых все читают и обсуждают. И намного больше малоизвестных ребят, которые пишут классные статьи. У селебрити и так все отлично, а вот остальным я бы хотел помочь найти свою аудиторию.

Поэтому провожу эксперимент! Готов опубликовать ссылку на вашу статью, если она мне понравится. Бесплатно. Знаменитостью это вас не сделает, но статью точно увидит больше людей.

Все условия

Быстрый поиск похожих слов на SQL

Написал статью о том, как искать похожие слова и исправлять опечатки на большом словаре (1.5 млн записей).

рассчет → расчет
сонце → солнце
абривиатура → аббревиатура

Попутно вы узнаете, как считать расстояние между словами, что такое фонетические коды, и как использовать фонетику и расстояния для моментального поиска (<50 мс).

https://antonz.ru/similar-words/

SQLite GUI

По запросу «sqlite gui» в гугле на первой позиции стоит программа «DB Browser for SQLite». На мой взгляд, она плоха примерно всем, и такая популярность ничем не оправдана.

Если вы работаете под Windows, обратите внимание на sqlite-gui. Она легкая, быстрая, удобная, и с кучей приятных дополнительных фич вроде настраиваемых шорткатов, генератора данных и даже встроенного REST API сервера.

А еще автор (к сожалению, не знаю даже его имени) пишет классные статьи про SQLite на хабре.

Читаем и пишем файлы прямо из SQLite

У CLI-утилиты sqlite есть встроенные функции writefile и readfile. Я их немного доработал и оформил в виде расширения fileio.

Пишем в файл:

select writefile('hello.txt', 'hello world');

Читаем:

select readfile('hello.txt');

Выводим содержимое каталога:

select * from lsdir('.');

Для любителей делать все на свете, не покидая SQL ツ

Unix timestamp

SQLite 3.38 получит функцию даты/времени в формате unix timestamp! А пока он не вышел, можно попробовать в моем расширении isodate:

select unixepoch('2021-12-22 12:34:45');
1640176485

https://github.com/nalgeon/sqlean/issues/27#issuecomment-998138191

Все расширения SQLite

Мне очень нравится SQLite. Это миниатюрная встраиваемая база, которая отлично подходит как для исследовательского анализа данных, так и в качестве хранилища для небольших приложений.

Но есть у нее один недостаток: маловато встроенных функций по сравнению с PostgreSQL или Oracle.

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

Мне хотелось большой системности.

Так появился проект sqlean — в нем я собираю вместе недостающие в SQLite функции, распределяю их по модулям, рефакторю код, пишу тесты и документацию. Получается что-то вроде стандартной библиотеки, как в Python или Go, только для SQLite.

Там есть регулярные выражения, работа с файлами, мат. статистика, поддержка CSV. Даже массивы есть! (почти как в постгресе).

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

https://antonz.ru/sqlean/

Стрелочка для JSON

В SQLite может появиться специальный синтаксис для JSON, как в постгресе!

Было:

select json_extract(person,'$.name') ...

Стало:

select person->'name' ...

Не 100%, но весьма вероятно.

https://sqlite.org/src/doc/json-enhancements/doc/json-enhancements.md

Гибкие типы данных

Люди часто не понимают, что за ерунда происходит в SQLite с типами данных. Давайте разберемся.

SQLite использует пять типов данных:

— INTEGER — целые числа,
— REAL — действительные числа,
— TEXT — строки,
— BLOB — бинарные данные,
— NULL — пустое значение.

Пока ничего необычного (только типов маловато по сравнению с другими СУБД).

А как вам такое:

SQLite может хранить данные любого типа — вне зависимости от того, какой тип указан на столбце.

Хотите записать число 3.14 в integer-поле? Пожалуйста (и никакого округления). Или, может быть, 'привет' в поле типа real? Без проблем.

Дело в том, что SQLite хранит тип не только на столбце, но и на каждом значении в таблице. Именно поэтому в одном столбце без проблем хранятся значения разных типов. Тип на столбце используется как рекомендация: при вставке SQLite пытается привести значение к рекомендуемому типу, но если не получилось — сохраняет «как есть».

Это удобно для анализа данных — можно сначала все загрузить, а потом средствами SQL разбираться с проблемными значениями. Любая другая СУБД выдаст ошибку при импорте и заставит вас «шерстить» файл скриптами или вручную.

С другой стороны, это неожиданное поведение, за которое многие недолюбливают SQLite и даже прозвали его «JavaScript в мире баз данных». Со временем разработчики SQLite прислушались и сделали альтернативный механизм «строгих» таблиц — но о нем в другой раз.

Люди и код

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

Послушайте, если вам интересно про sqlite, мои проекты или открытый код в целом.

Конкретно sqlite обсуждаем с 12:15.

https://we.fo/1604736632

P.S. Поскольку к записи мы специально не готовились, звучу я как из ведра. Но если потерпеть минуту, вы привыкнете 😁

Новости песочницы

Обновил SQLite-песочницу! Самое главное — теперь можно сохранять сниппеты, не указывая API-ключ гитхаба. Жмакнули на «share» и готово. Так что песочницей смогут пользоваться люди, далекие от гитхаба 🎉

Еще обновил SQLite до свежайшей версии 3.37.2. И включил пачку стандартных расширений вроде R*Tree. А в планах еще приделать все расширения из sqlean, вот тогда заживем вообще.

https://sqlime.org/

Как хранить дату

В SQLite нет отдельных типов для даты и времени. Поэтому у вас есть только два варианта, каждый со своими достоинствами и недостатками. Рассмотрим их вкратце.

ISO 8601

Международный стандарт, который записывает дату как 2022-01-27, а дату-время как 2022-01-27 19:30:15. Иногда добавляют еще миллисекунды: 2022-01-27 19:30:15.123.

Стандарт включает кучу нюансов, но основные формы записи именно такие.

Дата при этом хранится в SQLite как текст. Занимает 12 байт (без времени), 21 байт (до секунд) или 25 байт (с миллисекундами).

➕ Человекочитаемая дата
➕ Удобно сравнивать ('2021-12-30' < '2022-01-27')
➖ Неудобно считать разницу между датами
➖ Занимает много места

Unix Time

Unix Time — это количество секунд, прошедшее с 01.01.1970. Например unix time для 2022-01-27 19:30:15 = 1643311815, а для 1960-03-25 = -308361600.

Дата при этом хранится в SQLite как число: целое, если достаточно точности до секунды, или действительное — если нужны миллисекунды. Занимает 8 байт.

➕ Удобно сравнивать
➕ Удобно считать разницу между датами
➕ Компактное представление
➖ Непонятна человеку

Часовые пояса

Если вы работали раньше с «международными» датами в любом виде, то знаете, как больно бывает от часовых поясов. В SQLite с ними все просто: часовые пояса не поддерживаются.

Поэтому, какой бы формат вы не выбрали (ISO 8601 или Unix Time), соблюдайте главное правило:

Хранить даты только в часовом поясе UTC

Иначе никогда концов не найдете.

⚫ В О Й Н А

Курс «SQLite на практике»

Запускаю новый курс! Подойдёт всем, кто знаком с основами SQL, и хочет узнать о возможностях и особенностях SQLite.

Начал с трех модулей, хорошо знакомых выпускникам другого моего курса, «SQLite для аналитики»:

— Импорт, анализ и экспорт данных.
— Типы данных в SQLite.
— Работа с JSON.

В планах и другие модули, посвященные специфике SQLite.

Курс пока бесплатный, но это не навсегда.

Записывайтесь, если хотите прокачать понимание SQLite и не боитесь командной строки:

SQLite на практике

История команд в консоли

Если вы используете консоль SQLite (sqlite3), то она помнит последние 2000 выполненных команд. Чтобы повторить последнюю команду, достаточно нажать клавишу ↑, а поиск работает по сочетанию Ctrl+R.

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

История записывается в файл при штатном выходе из консоли, так что если «моргнет» электричество или еще что — команды, выполненные с последнего запуска sqlite3, потеряются.

Конечно, запись истории — не единственная фича консоли. Например, через нее удобно импортировать и экспортировать данные или работать с несколькими базами одновременно. А вот отлаживать многострочные запросы — не слишком удобно 🤷‍♀️

Многострочные запросы в консоли

Стоило вчера мне написать, что отлаживать многострочные запросы в консоли SQLite неудобно, как интернеты принесли классный трюк.

Достаточно вместо Enter использовать для перехода на новую строку сочетание Ctrl+V, Ctrl+J — и тогда многострочный запрос редактируется одним куском!

Вычисляемые столбцы

Иногда поле в запросе рассчитывают на основе других столбцов таблицы.

Например, есть столбец income с годовым доходом и tax_rate с налоговой ставкой. Тогда можно посчитать годовой налог:

select
id,
income * tax_rate as tax
from people;

А чтобы не таскать везде эти вычисления, удобно создать виртуальный столбец.

В общем виде синтаксис такой:

alter table ТАБЛИЦА
add column СТОЛБЕЦ ТИП as (ВЫРАЖЕНИЕ);

Для налогов будет так:

alter table people
add column tax real as (
income * tax_rate
);

После этого столбец можно использовать в запросах точно так же, как обычные столбцы:

select id, tax
from people;

Виртуальные столбцы не хранятся в базе и рассчитываются «на лету». Но по ним вполне можно построить индекс, если захотите ускорить выборку.

Строго говоря, в SQLite есть виртуальные (virtual) вычисляемые столбцы и хранимые (stored). Хранимые сохраняются на диске, но создать их через alter table невозможно, поэтому в основном пользуются виртуальными.

Вычисляемые столбцы могут использовать любые столбцы таблицы, но не другие таблицы и не результаты подзапросов. Оно и к лучшему: для более сложных комбинаций есть представления (views) и временные таблицы (temp tables). Но о них как-нибудь в другой раз.

JSON и виртуальные столбцы

Допустим, вы решили вести журнал событий, которые происходят в системе. События бывают разных типов, у каждого свой набор полей. Например, вход в систему:

{
  "timestamp": 1652614531,
  "object": "user",
  "object_id": 11,
  "action": "login",
  "details": {
    "ip": "192.168.0.1"
  }
}

Или пополнение счета:

{
  "timestamp": 1652614584,
  "object": "account",
  "object_id": 12,
  "action": "deposit",
  "details": {
    "amount": "1000",
    "currency": "USD"
  }
}

Вы решаете не заниматься нормализацией по таблицам, а хранить прямо в JSON. Заводите таблицу events с единственным полем value:

select value from events;

{"timestamp":1652614531,...
{"timestamp":1652614584,...
{"timestamp":1652614644,...

И выбираете события по конкретному объекту:

select
  json_extract(value, '$.object'),
  json_extract(value, '$.action')
from events
where json_extract(value, '$.object_id') = 11;

┌────────┬────────┐
│ object │ action │
├────────┼────────┤
│ user   │ login  │
└────────┴────────┘

Все здорово, но json_extract() при вызове каждый раз парсит текст, так что на сотне тысяч записей запрос будет работать медленно. Что делать?

Создать виртуальные столбцы:

alter table events
add column object_id integer
as (json_extract(value, '$.object_id'));

Построить индекс:

create index events_object_id on events(object_id);

Теперь запрос работает моментально:

select object, action
from events
where object_id = 11;

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

песочница

Временные таблицы

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

select v.*, e.name, a.name
from vacancy as v
  join employer as e on e.id = v.employer_id
  join area as a on a.id = v.area_id

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

1) Common Table Expression (CTE)
2) Представление (view)
3) Временная таблица (temporary table)

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

Представление — это такой именованный селект, к которому можно обращаться как к обычной таблице:

create view vacancy_view as
select ... from ... join ...;

select * from vacancy_view;

Очень похоже на CTE, только не приходится каждый раз повторять селект в запросе — достаточно написать название представления. Как и CTE, представление тоже вычисляется каждый раз.

Во многих СУБД есть материализованные представления, которые сохраняют данные на диск — но не в SQLite.

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

create temporary table vacancy_temp as
select ... from ... join ...;

select * from vacancy_temp;

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

песочница

Про курсы

Как знают давние подписчики канала, у меня есть три курса на Степике:

— Оконные функции SQL
— SQLite на практике
— SQLite для аналитики

Первые два частично охватывают материал третьего. Поэтому я решил, что будет правильно давать скидку на «SQLite для аналитики» для выпускников «окошек» и «на практике».

Так что если вы:

— успешно решили все задачки в «SQLite на практике» (скидка 500₽),
— или получили сертификат с отличием в «Оконные функции SQL» (скидка 500₽),
— и хотите пройти «SQLite для аналитики»,

то присылайте ссылку на свой Степик-профиль мне @nalgeon, чтобы получить промо-код.