SQLite table engine

The engine allows to import and export data to SQLite and supports queries to SQLite tables directly from ClickHouse.

Creating a table

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

Engine Parameters

• db_path — Path to SQLite file with a database.
• table — Name of a table in the SQLite database, or a query passed to SQLite as is (see Passing a query instead of a table name).

Passing a query instead of a table name

Instead of a table name, the table argument can be a SELECT query that is passed to SQLite as is. The structure of the table is inferred from the query result. The query can be written either as a subquery, or wrapped into the query function:

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'));

Such a table is read-only: INSERT into it is not allowed. The same syntax is supported by the sqlite table function.

Data types support

When you explicitly specify ClickHouse column types in the table definition, 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

See SQLite database engine for the default type mapping.

Usage example

Shows a query creating the SQLite table:

SHOW CREATE TABLE sqlite_db.table2;

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

Returns the data from the table:

SELECT * FROM sqlite_db.table2 ORDER BY col1;

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

See Also

• SQLite engine
• sqlite table function