import CloudNotSupportedBadge from '@theme/badges/CloudNotSupportedBadge';

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 {#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.

Data types support {#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 {#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