About ClickHouse

ClickHouse is one of the remote sources that Coco Alemana supports natively. You can read data from existing tables, as well as save data to new tables entirely, without using the ClickHouse Console. All of the actions within Coco Alemana also apply to ClickHouse tables.

Note

You’ll need to connect at least one ClickHouse account to continue.

For more information, see Connect to Clickhouse

Load a Table from Clickhouse

1. Open a new source

Once your credentials are configured, you’re able to load data from ClickHouse. To start, you can create a new source via ⌘ N, and select “ClickHouse”.

2. Select a Table

See your available databases and select a table you’d like to import. You can also search for a table in your database.

Modifying ClickHouse Tables

You can modify ClickHouse frames with the same actions as any other frame. The SQL used in Coco Alemana will translate into ClickHouse SQL on your behalf. This means you can work with a massive table without downloading it.

Exporting Data

You export data to ClickHouse just like any other frame. You can also export a ClickHouse frame to a local file. This process will take slightly longer, as it requires downloading everything onto your device.

Compatibility

Coco Alemana is generally extremely compatible with ClickHouse’s types, functions and semantic behavior. However, there are certain nuances and limitations within ClickHouse which make it difficult, and in some cases impossible, to have full compatibility. Below are some settings and limitations.

Session Settings

For every query, we provide two session SETTINGS to ensure ClickHouse returns results compatible with DuckDB (CocoSQL).

Setting	Value	Reasoning
join_use_nulls	1	Ensures that JOINs use NULLS for default values instead of the type’s lowest value.
group_by_use_nulls	1	Ensures that GROUP BY statements use NULLs for default values or groups instead of the type’s lowest value.

See documentation for join_use_nulls and group_by_use_nulls for more information.

Known Limitations and Inconsistencies

1. Time types are not supported. While ClickHouse version 25.6 supports Time types, they are still experimental and not universally supported. They may be supported in the future on a per-version basis.

2. The function split produces an empty array when encountering a NULL value in a String column type. This is because ClickHouse does not support Array types that are Nullable. This is a serious limitation in ClickHouse and it is not clear if we’ll be able to fix this in the future.

3. Boolean types in ClickHouse do not consistently convert to Booleans in DuckDB. This is due to ClickHouse using Boolean as a pure alias, and using UTINYINT under the hood. This shouldn’t affect logic, but may affect display.

4. Dates and Timestamps have different minimum and maximum values which are not in alignment with DuckDB.

   • The maximum date in DuckDB is December 31st, 9999, while ClickHouse’s is December 31, 2299.
   • The minimum date in DuckDB is January 1st, 0001, while ClickHouse is January 1st, 1900.

   This is very unlikely to affect your analyses day-to-day, but it is important to note in case you work with dates on the end of these ranges. This applies the Timestamps as well.