Движок таблицы SQLite

Движок позволяет импортировать данные из SQLite и экспортировать их в SQLite, а также выполнять запросы к таблицам SQLite напрямую из ClickHouse.

Создание таблицы

    CREATE TABLE [IF NOT EXISTS] [db.]table_name
    (
        name1 [type1],
        name2 [type2], ...
    ) ENGINE = SQLite('db_path', 'table')

Параметры движка

db_path — Путь к файлу SQLite с базой данных.
table — Имя таблицы в базе данных SQLite или запрос, передаваемый в SQLite как есть (см. Передача запроса вместо имени таблицы).

Передача запроса вместо имени таблицы

Вместо имени таблицы в аргументе table можно указать SELECT-запрос, который передаётся в SQLite без изменений. Структура таблицы выводится из результата запроса. SQLite сообщает объявленный тип только для тех столбцов результата, которые непосредственно являются столбцами таблицы; для выражения, литерала или агрегата тип не сообщается. Объявленный тип, который сопоставляется с String (см. сопоставление типов), используется как есть. Столбцу без объявленного типа тип назначается по классу хранения его значения в первой строке результата запроса: значение INTEGER даёт Int64, значение REAL — Float64, а любое другое значение (TEXT, BLOB, NULL), как и пустой результат, — String. Числовой объявленный тип также проверяется по этой первой строке, поскольку SQLite сообщает объявленный тип и для составного SELECT: тип берётся из одной из ветвей, а строки поступают из всех. Он сохраняется, если класс хранения значения с ним согласуется (INTEGER для целочисленного типа, INTEGER или REAL для типа с плавающей точкой), а также если значение равно NULL или результат пуст, поскольку ни в том, ни в другом случае нет информации о типе, которая могла бы ему противоречить. В противном случае тип столбца определяется по классу хранения, как для столбца без объявленного типа. Таким образом, выведенный тип может только расширяться, но никогда не сужается. Для вывода типа такого столбца запрос выполняется в SQLite. При этом первая строка ничего не говорит об остальных: SQLite может возвращать в каждой строке свой класс хранения (например, CASE WHEN id = 1 THEN 1 ELSE 1.5 END), поэтому чтение на основе запроса работает по принципу fail-closed для каждого столбца, читаемого через числовой тип, а не только для столбцов без объявленного типа. Если класс хранения значения не соответствует этому типу или значение не может быть в нём точно представлено (ячейка INTEGER со значением 300 в столбце UInt8, ячейка REAL со значением 16777217 в столбце Float32), чтение завершается ошибкой, а не выполняется неявное приведение. Чтобы прочитать столбец со значениями разных классов хранения, объявите его как String или приведите к тексту в самом запросе SQLite. Все выведенные столбцы имеют тип Nullable. Запрос можно записать как подзапрос или обернуть в функцию query:

CREATE TABLE sqlite_table ENGINE = SQLite('sqlite.db', (SELECT col1, col2 FROM table1 WHERE col2 > 1));
CREATE TABLE sqlite_table ENGINE = SQLite('sqlite.db', query('SELECT col1, col2 FROM table1 WHERE col2 > 1'));

Передача запроса поддерживается начиная с версии 26.7. Перед отправкой в SQLite ClickHouse оборачивает запрос в SELECT ... FROM (<query>), поэтому запрос не должен заканчиваться точкой с запятой. Такая таблица доступна только для чтения: выполнять в неё INSERT нельзя. Тот же синтаксис поддерживается табличной функцией sqlite.

Форма подзапроса (SELECT ...) разбирается ClickHouse и повторно сериализуется перед отправкой в SQLite. Поэтому она должна быть корректным ClickHouse SQL. Чтобы передать специфичный для SQLite синтаксис, который ClickHouse не разбирает, используйте форму query('...'), текст которой отправляется в SQLite дословно.Любые внешние WHERE, LIMIT, агрегирование и т. д. из окружающего запроса к ClickHouse не проталкиваются в передаваемый запрос — они применяются в ClickHouse после получения полного результата запроса. Чтобы ограничить объём данных, считываемых из SQLite, поместите фильтр внутрь передаваемого запроса. При external_table_strict_query = 1 внешний фильтр по столбцам таблицы отклоняется с исключением вместо локального применения, поскольку его нельзя протолкнуть в передаваемый запрос. Проверка охватывает предикат верхнего уровня WHERE и каждый конъюнкт верхнего уровня AND. PREWHERE по столбцам этой таблицы не относится к данному параметру: этот движок таблицы не поддерживает PREWHERE, и такой запрос отклоняется с ILLEGAL_PREWHERE независимо от параметра. Проверка выполняется только там, где фильтр вообще может быть протолкнут: когда эта таблица является единственной таблицей запроса, находится по обе стороны INNER JOIN или на сохраняющей стороне внешнего JOIN (левой стороне LEFT JOIN, правой стороне RIGHT JOIN). На несохраняющей стороне LEFT/RIGHT JOIN и по обе стороны FULL JOIN ничего не проталкивается и не проверяется, поэтому фильтр по столбцам этой таблицы применяется локально после JOIN даже в строгом режиме. Там, где выполняется проверка, предикат, ссылающийся на другие таблицы, присоединённые в окружающем запросе, не проталкивается и исключается из проверки — независимо от того, ссылается ли он только на присоединённую сторону или смешивает её с этой таблицей в одном выражении, не содержащем AND (например, в OR); такой предикат сохраняет обычную точку вычисления в ClickHouse (WHERE после JOIN, PREWHERE до него) и не отклоняется.

Поддержка типов данных

Если в определении таблицы явно указать типы столбцов ClickHouse, из столбцов SQLite TEXT можно разобрать следующие типы ClickHouse:

Date, Date32
DateTime, DateTime64
UUID
Enum8, Enum16
Decimal32, Decimal64, Decimal128, Decimal256
FixedString
Все целочисленные типы (UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256)
Float32, Float64

Сопоставление типов по умолчанию см. в разделе движок базы данных SQLite. Если значение SQLite NULL считывается в столбец ClickHouse, явно объявленный без Nullable, возникает исключение CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN. Чтобы считывать значения NULL, объявите столбец как Nullable. Выведенная схема соответствует метаданным SQLite: столбец получает тип Nullable, если он не объявлен как NOT NULL.

Пример использования

Показан запрос на создание таблицы SQLite:

SHOW CREATE TABLE sqlite_db.table2;

CREATE TABLE SQLite.table2
(
    `col1` Nullable(Int64),
    `col2` Nullable(String)
)
ENGINE = SQLite('sqlite.db','table2');

Возвращает данные из таблицы:

SELECT * FROM sqlite_db.table2 ORDER BY col1;

┌─col1─┬─col2──┐
│    1 │ text1 │
│    2 │ text2 │
│    3 │ text3 │
└──────┴───────┘

См. также

SQLite движок
sqlite табличная функция

Справочник по SQL

Типы данных

Движки

Функции

Форматы

Настройки

Системные таблицы

Озера данных

Создание таблицы

Передача запроса вместо имени таблицы

Поддержка типов данных

Пример использования

​Создание таблицы

​Передача запроса вместо имени таблицы

​Поддержка типов данных

​Пример использования

Создание таблицы

Передача запроса вместо имени таблицы

Поддержка типов данных

Пример использования