SQLite extension
The sqlite
extension allows you to attach SQLite databases to Kuzu.
Usage
INSTALL sqlite;LOAD sqlite;
Example SQLite database
First, create a sample SQLite database of university students in Python.
import sqlite3conn = sqlite3.connect('university.db')cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS person (name VARCHAR, age INTEGER);")cursor.execute("INSERT INTO person (name, age) VALUES ('Alice', 30);")cursor.execute("INSERT INTO person (name, age) VALUES ('Bob', 27);")cursor.execute("INSERT INTO person (name, age) VALUES ('Carol', 19);")cursor.execute("INSERT INTO person (name, age) VALUES ('Dan', 25);")conn.commit()conn.close()
Attach a SQLite database
To attach a SQLite database, use the ATTACH
statement.
ATTACH <DB_PATH> AS <alias> (dbtype sqlite, skip_unsupported_table = <OPTION>)
DB_PATH
: Path to the SQLite database instance (can be relative or absolute path)alias
: Database alias to use in Kuzu. If not provided, the database name from SQLite will be used. When attaching multiple databases, we recommend using aliases.skip_unsupported_table
: A boolean flag to indicate whether Kuzu should exit with an error or skip tables with unsupported data types. Defaults toFalse
.
For example, the university.db
SQLite database can be attached to Kuzu using the alias uw
:
ATTACH 'university.db' AS uw (dbtype sqlite);
SQLite to Kuzu type mapping
The following table shows the mapping from SQLite’s data types to Kuzu’s data types:
SQLite Storage Class / Data Type | Corresponding Data Type in Kuzu |
---|---|
NULL | BLOB |
INTEGER | INT64 |
REAL | DOUBLE |
TEXT | STRING |
BLOB | BLOB |
BOOLEAN | INT64 |
DATE | DATE |
TIME | TIMESTAMP |
Scan a SQLite table
Use the LOAD FROM
statement to scan the person
table.
LOAD FROM uw.personRETURN *;
---------------| name | age |---------------| Alice | 30 |---------------| Bob | 27 |---------------| Carol | 19 |---------------| Dan | 25 |---------------
Note: SQLite uses a dynamic type system,
meaning that columns in SQLite can store values with different types.
Set sqlite_all_varchar_option
to True
to scan such columns in Kuzu.
CALL sqlite_all_varchar_option=<OPTION>
If set to True
, all SQLite columns will be treated and scanned as STRING
columns.
If set to False
, trying to scan a column with values incompatible with the specified data type will result in a runtime exception.
Use a default database name
You can set a default database name to avoid having to specify the full database name as a prefix in every query.
The above example can be simplified as:
ATTACH 'university.db' AS uw (dbtype sqlite);USE uw;LOAD FROM personRETURN *;
Copy data from SQLite tables
You can use the COPY FROM
statement to import data from a SQLite table into Kuzu.
First, create a Person
table in Kuzu, with the same schema as the one defined in SQLite:
CREATE NODE TABLE Person (name STRING PRIMARY KEY, age INT32);
When the schemas are the same, you can copy the data from the external DBMS table to the Kuzu table simply as:
COPY Person FROM uw.person;
If the schemas are not the same, e.g., Person
contains only the name
property while the external uw.person
contains
name
and age
, you can still use COPY FROM
but with a subquery as:
COPY Person FROM (LOAD FROM uw.person RETURN name);
You can verify that the data was successfully imported:
MATCH (p:Person) RETURN p.*;
------------------| p.name | p.age |------------------| Alice | 30 |------------------| Bob | 27 |------------------| Carol | 19 |------------------| Dan | 25 |------------------
Clear the schema cache
To avoid redundantly retrieving schema information from attached databases, Kuzu maintains a schema cache
including table names and their respective columns and types. Should modifications occur in the schema
via an alternate connection to attached RDBMSs, such as creation or deletion of tables, the cached
schema data may become obsolete. You can use the clear_attached_db_cache()
function to refresh the cached
schema information in such cases.
CALL clear_attached_db_cache();
Detach a database
To detach a database, use DETACH [ALIAS]
as follows:
DETACH uw;