SQLite

Allows to connect to SQLite databases and perform INSERT and SELECT queries to exchange data between ClickHouse and SQLite.

Allows to connect to SQLite database and perform INSERT and SELECT queries to exchange data between ClickHouse and SQLite.

Creating a database

    CREATE DATABASE sqlite_database
    ENGINE = SQLite('db_path')

Engine Parameters

• db_path — Path to a file with SQLite database.

Data types support

The table below shows the default type mapping when ClickHouse automatically infers schema from SQLite:

SQLite	ClickHouse
INTEGER	Int32
REAL	Float32
TEXT	String
TEXT	UUID
BLOB	String

When you explicitly define a table with specific ClickHouse types using the SQLite table engine, the following ClickHouse types can be parsed from SQLite TEXT columns:

• Date, Date32
• DateTime, DateTime64
• UUID
• Enum8, Enum16
• Decimal32, Decimal64, Decimal128, Decimal256
• FixedString
• All integer types (UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64)
• Float32, Float64

SQLite has dynamic typing, and its type access functions perform automatic type coercion. For example, reading a TEXT column as an integer will return 0 if the text cannot be parsed as a number. This means that if a ClickHouse table is defined with a different type than the underlying SQLite column, values may be silently coerced rather than causing an error.

Specifics and recommendations

SQLite stores the entire database (definitions, tables, indices, and the data itself) as a single cross-platform file on a host machine. During writing SQLite locks the entire database file, therefore write operations are performed sequentially. Read operations can be multi-tasked. SQLite does not require service management (such as startup scripts) or access control based on GRANT and passwords. Access control is handled by means of file-system permissions given to the database file itself.

Usage example

Database in ClickHouse, connected to the SQLite:

CREATE DATABASE sqlite_db ENGINE = SQLite('sqlite.db');
SHOW TABLES FROM sqlite_db;

┌──name───┐
│ table1  │
│ table2  │
└─────────┘

Shows the tables:

SELECT * FROM sqlite_db.table1;

┌─col1──┬─col2─┐
│ line1 │    1 │
│ line2 │    2 │
│ line3 │    3 │
└───────┴──────┘

Inserting data into SQLite table from ClickHouse table:

CREATE TABLE clickhouse_table(`col1` String,`col2` Int16) ENGINE = MergeTree() ORDER BY col2;
INSERT INTO clickhouse_table VALUES ('text',10);
INSERT INTO sqlite_db.table1 SELECT * FROM clickhouse_table;
SELECT * FROM sqlite_db.table1;

┌─col1──┬─col2─┐
│ line1 │    1 │
│ line2 │    2 │
│ line3 │    3 │
│ text  │   10 │
└───────┴──────┘