Description
TheSQLite format reads and writes a SQLite database file.
On output, ClickHouse writes the query result into a single table in the SQLite database. On input, ClickHouse reads a single table from the SQLite database.
For local regular files, ClickHouse opens the SQLite database file directly. For other input and output streams, ClickHouse materializes the SQLite database in memory.
On input, ClickHouse reads the first table from the SQLite database by default. You can change it with the input_format_sqlite_table_name setting. On output, the default table name is table; you can change it with the output_format_sqlite_table_name setting.
Example usage
Data types matching
When ClickHouse writes data in theSQLite format, it creates a SQLite table with the following declared types:
For
Float32 and Float64 columns, non-NaN values are written using SQLite native storage classes, while NaN values are written using ClickHouse text serialization. Bool and ordinary integer values are also written using SQLite native storage classes. Wide integers and complex types are written using ClickHouse text serialization. NULL values are written as SQLite NULL.
When ClickHouse infers a schema from SQLite input, it uses the same mapping as the SQLite database engine:
By default, ClickHouse wraps the inferred type in
Nullable if the corresponding SQLite column allows NULL; NOT NULL columns remain non-Nullable. You can override this behavior with the schema_inference_make_columns_nullable setting.
Schema inference does not preserve the original ClickHouse data types. For example, Bool is written as SQLite INTEGER and inferred as Int64, while Date, DateTime, Decimal, UUID, IPv4, IPv6, Enum, Array, Tuple, and Map are written as SQLite TEXT and inferred as String.
When the ClickHouse table structure is specified explicitly, floating-point values are read as SQLite REAL values, with a text fallback for NaN. Other SQLite values are read as text and parsed into the requested ClickHouse types.