SQLite 확장
SQLite 확장
SQLite 확장은 DuckDB가 SQLite 데이터베이스 파일의 데이터를 직접 읽고 쓸 수 있게 해줘요. 밑바탕 SQLite 테이블의 데이터를 직접 쿼리할 수 있고, SQLite 테이블의 데이터를 DuckDB 테이블로(또는 그 반대로) 로드할 수 있답니다.
출처: 문서
본문
설치와 로드
sqlite 확장은 첫 사용 시 공식 확장 저장소에서 자동 로드돼요. 수동으로 설치·로드하려면 다음을 실행하세요.
INSTALL sqlite;
LOAD sqlite;
사용법
SQLite 파일을 DuckDB에서 접근 가능하게 하려면 sqlite 또는 sqlite_scanner 타입으로 ATTACH 문을 사용해요. Attached된 SQLite 데이터베이스는 읽기와 쓰기 연산을 모두 지원합니다.
예를 들어 sakila.db 파일에 attach하려면 다음을 실행하세요.
ATTACH 'sakila.db' (TYPE sqlite);
USE sakila;
파일의 테이블은 일반 DuckDB 테이블처럼 읽을 수 있지만, 밑바탕 데이터는 쿼리 시점에 파일 안의 SQLite 테이블에서 직접 읽혀요.
SHOW TABLES;
| name |
|---|
| actor |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
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는 약한 타입(weakly typed) 데이터베이스 시스템이에요. 그래서 SQLite 테이블에 데이터를 저장할 때 타입이 강제되지 않아요. 다음은 SQLite에서 유효한 SQL이에요.
CREATE TABLE numbers (i INTEGER);
INSERT INTO numbers VALUES ('hello');
DuckDB는 강한 타입의 데이터베이스 시스템이라 모든 컬럼이 정의된 타입을 가져야 하고, 시스템이 데이터의 정확성을 엄격히 검사해요.
SQLite를 쿼리할 때 DuckDB는 특정 컬럼 타입 매핑을 추론해야 해요. DuckDB는 SQLite의 타입 친화성(type affinity) 규칙을 약간의 확장과 함께 따릅니다.
- 선언된 타입에
INT문자열이 포함되면BIGINT타입으로 변환돼요. - 컬럼의 선언된 타입에
CHAR,CLOB,TEXT중 어떤 문자열이라도 포함되면VARCHAR로 변환돼요. - 컬럼에 선언된 타입에
BLOB문자열이 포함되거나 타입이 지정되지 않으면BLOB으로 변환돼요. - 컬럼의 선언된 타입에
REAL,FLOA,DOUB,DEC,NUM중 어떤 문자열이라도 포함되면DOUBLE로 변환돼요. - 선언된 타입이
DATE면DATE로 변환돼요. - 선언된 타입에
TIME문자열이 포함되면TIMESTAMP로 변환돼요. - 위 어디에도 해당하지 않으면
VARCHAR로 변환돼요.
DuckDB는 해당 컬럼에 올바른 타입의 값만 들어 있도록 강제하므로, 위 "numbers" 테이블의 컬럼에는 "hello" 문자열을 BIGINT 타입으로 로드할 수 없어요. 그래서 "numbers" 테이블을 읽을 때 오류가 발생합니다.
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;
| first_name |
|---|
| PENELOPE |
| NICK |
| ED |
SQLite에 데이터 쓰기
SQLite에서 데이터를 읽는 것 외에도, 이 확장은 표준 SQL 쿼리로 새 SQLite 데이터베이스 파일을 만들고, 테이블을 만들고, 데이터를 SQLite에 넣고, SQLite 데이터베이스 파일에 다른 수정을 가하는 것을 허용해요.
이를 통해 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 데이터베이스를 동시에 읽을 수 있지만, 한 번에 하나의 스레드나 프로세스만 데이터베이스에 쓸 수 있어요. 데이터베이스 잠금은 DuckDB가 아니라 SQLite 라이브러리가 처리해요. 같은 프로세스 안에서는 SQLite가 뮤텍스를 사용하고, 다른 프로세스에서 접근하면 SQLite는 파일시스템 잠금을 사용해요. 잠금 메커니즘은 WAL 모드 같은 SQLite 구성에도 의존해요. 자세한 내용은 SQLite 잠금 문서를 참고해 주세요.
경고 — 같은 애플리케이션에 SQLite 라이브러리의 여러 복사본을 링크하면 애플리케이션 오류가 발생할 수 있어요. 자세한 내용은 sqlite_scanner 이슈 #82를 참고해 주세요.
설정
이 확장은 다음 구성 파라미터를 노출해요.
| 이름 | 설명 | 기본값 |
|---|---|---|
sqlite_debug_show_queries |
DEBUG 설정: SQLite로 보내는 모든 쿼리를 stdout에 출력 | false |
지원 연산
지원되는 연산 목록이에요.
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;
| id | name |
|---|---|
| 42 | DuckDB |
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;
| i |
|---|
| 42 |
ROLLBACK;
SELECT * FROM sqlite_db.tmp;
| i |
|---|
폐기됨 — 옛
sqlite_attach함수는 폐기됐어요. 새ATTACH문법으로 전환하는 것을 권장해요.
호환성
SQLite 확장은 SQLite의 Rust 재작성인 Turso가 쓴 데이터베이스를 읽을 수 있어요.
더 알아보기 (Learn more)
- 확장 자동 로드와
ATTACH문법은extensions/overview,sql/statements/attach문서를 참고해 주세요.