Расширение SQLite

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

Установка и загрузка

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

INSTALL sqlite;
LOAD sqlite;

Использование

Чтобы предоставить доступ к файлу SQLite для DuckDB, используйте инструкцию ATTACH с типом SQLITE или SQLITE_SCANNER. Присоединённые базы данных SQLite поддерживают операции чтения и записи.

Например, чтобы подключиться к файлу sakila.db, выполните:

ATTACH 'sakila.db' (TYPE SQLITE);
USE sakila;

Таблицы в файле могут быть прочитаны как обычные таблицы DuckDB, но основополагающие данные читаются непосредственно из таблиц SQLite в файле во время запроса.

SHOW TABLES;

Вы можете запрашивать таблицы с помощью SQL, например, используя примеры запросов из sakila-examples.sql:

SELECT
    cat.name AS category_name,
    sum(ifnull(pay.amount, 0)) AS revenue
FROM category cat
LEFT JOIN film_category flm_cat
       ON cat.category_id = flm_cat.category_id
LEFT JOIN film fil
       ON flm_cat.film_id = fil.film_id
LEFT JOIN inventory inv
       ON fil.film_id = inv.film_id
LEFT JOIN rental ren
       ON inv.inventory_id = ren.inventory_id
LEFT JOIN payment pay
       ON ren.rental_id = pay.rental_id
GROUP BY cat.name
ORDER BY revenue DESC
LIMIT 5;

Типы данных

SQLite — это система баз данных со слабой типизацией. Поэтому при хранении данных в таблице SQLite типы не проверяются. Следующее является корректным SQL-запросом в SQLite:

CREATE TABLE numbers (i INTEGER);
INSERT INTO numbers VALUES ('hello');

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

При запросе к SQLite DuckDB должен вывести соответствие типов столбцов. DuckDB следует правилам родственности типов SQLite с некоторыми расширениями.

1. Если объявленный тип содержит строку INT , то он преобразуется в тип BIGINT
2. Если объявленный тип столбца содержит любую из строк CHAR, CLOB, или TEXT, то он преобразуется в VARCHAR.
3. Если объявленный тип столбца содержит строку BLOB или тип не указан, то он преобразуется в BLOB.
4. Если объявленный тип столбца содержит любую из строк REAL, FLOA, DOUB, DEC или NUM, то он преобразуется в DOUBLE.
5. Если объявленный тип — DATE, то он преобразуется в DATE.
6. Если объявленный тип содержит строку TIME, то он преобразуется в TIMESTAMP.
7. Если ни одно из вышеперечисленных условий не выполняется, то он преобразуется в VARCHAR.

Поскольку DuckDB требует, чтобы соответствующие столбцы содержали только значения правильного типа, мы не можем загрузить строку “hello” в столбец типа BIGINT. В связи с этим при чтении из таблицы “numbers” выше выбрасывается ошибка:

Error: Mismatch Type Error: Invalid type in column "i": column was declared as integer, found "hello" of type "text" instead.

Эту ошибку можно избежать, установив опцию sqlite_all_varchar:

SET GLOBAL sqlite_all_varchar = true;

При установке эта опция переопределяет правила преобразования типов, описанные выше, и вместо этого всегда преобразует столбцы SQLite в столбец типа VARCHAR. Обратите внимание, что это значение должно быть установлено до вызова sqlite_attach.

Открытие баз данных SQLite напрямую

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

Например, в оболочке база данных SQLite может быть открыта следующим образом:

duckdb sakila.db

SELECT first_name
FROM actor
LIMIT 3;

Запись данных в SQLite

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

Это позволяет использовать DuckDB для, например, экспорта данных, хранящихся в базе данных SQLite, в Parquet или для чтения данных из файла Parquet в SQLite.

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

ATTACH 'new_sqlite_database.db' AS sqlite_db (TYPE SQLITE);
CREATE TABLE sqlite_db.tbl (id INTEGER, name VARCHAR);
INSERT INTO sqlite_db.tbl VALUES (42, 'DuckDB');

Полученная база данных SQLite затем может быть прочитана из SQLite.

sqlite3 new_sqlite_database.db

SQLite version 3.39.5 2022-10-14 20:58:05
sqlite> SELECT * FROM tbl;

id  name  
--  ------
42  DuckDB

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

Конкурентность

DuckDB может читать или изменять базу данных SQLite, в то время как DuckDB или SQLite читают или изменяют ту же базу данных из другого потока или отдельного процесса. Несколько потоков или процессов могут одновременно читать базу данных SQLite, но только один поток или процесс может одновременно записывать в базу данных. Блокировка базы данных обрабатывается библиотекой SQLite, а не DuckDB. В пределах одного процесса SQLite использует мьютексы. При доступе из разных процессов SQLite использует блокировки файловой системы. Механизмы блокировки также зависят от конфигурации SQLite, например, от режима WAL. Для получения дополнительной информации обратитесь к документации SQLite по блокировкам.

Предупреждение Связывание нескольких копий библиотеки SQLite в одно приложение может привести к ошибкам приложения. См. проблему sqlite_scanner #82 для получения дополнительной информации.

Поддерживаемые операции

Ниже приведен список поддерживаемых операций.

CREATE TABLE

CREATE TABLE sqlite_db.tbl (id INTEGER, name VARCHAR);

INSERT INTO

INSERT INTO sqlite_db.tbl VALUES (42, 'DuckDB');

SELECT

SELECT * FROM sqlite_db.tbl;

COPY

COPY sqlite_db.tbl TO 'data.parquet';
COPY sqlite_db.tbl FROM 'data.parquet';

UPDATE

UPDATE sqlite_db.tbl SET name = 'Woohoo' WHERE id = 42;

DELETE

DELETE FROM sqlite_db.tbl WHERE id = 42;

ALTER TABLE

ALTER TABLE sqlite_db.tbl ADD COLUMN k INTEGER;

DROP TABLE

DROP TABLE sqlite_db.tbl;

CREATE VIEW

CREATE VIEW sqlite_db.v1 AS SELECT 42;

Транзакции

CREATE TABLE sqlite_db.tmp (i INTEGER);

BEGIN;
INSERT INTO sqlite_db.tmp VALUES (42);
SELECT * FROM sqlite_db.tmp;

ROLLBACK;
SELECT * FROM sqlite_db.tmp;

Устаревшая Функция sqlite_attach устарела. Рекомендуется перейти к новой ATTACH синтаксису.