Skip to main content
Skip to main content
Edit this page

SQLite table engine

Not supported in ClickHouse Cloud

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

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.

Note

The subquery form (SELECT ...) is parsed by ClickHouse and re-serialized before being sent to SQLite. It must therefore be valid ClickHouse SQL. To pass SQLite-specific syntax that ClickHouse does not parse, use the query('...') form, whose text is sent to SQLite verbatim.

Any outer WHERE, LIMIT, aggregation, etc. of the surrounding ClickHouse query is not pushed down into the passed query — it is applied in ClickHouse after the full query result is fetched. To restrict the data read from SQLite, put the filter inside the passed query. With external_table_strict_query = 1 an outer filter that cannot be pushed down is rejected with an exception instead of being applied locally.

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:

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