ClickHouse columnar storage engine and sparse indexing make it suitable for big-data BI reporting. It handles massive datasets with lightning-fast query speeds, which makes it a good choice if you have billion-scale datasets and need to offer near real-time ad-hoc analytics for end-users.
ClickHouse can be used as a live SQL-compatible data source for SeekTable reports. There are 2 ways how clients may connect to ClickHouse:
clickhouse-client CLI uses this kind of connections.SeekTable supports both ways with 2 different ClickHouse drivers.
Connection String example (ClickHouse.Client .NET driver):
Driver=ClickHouse.Client;Compression=True;Protocol=http;Host=hostName;Port=8123;Database=default;Username=default;Password=
| Driver |
Should be ClickHouse.Client if you want to connect to CH with HTTP(S) interface.
|
|---|---|
| Protocol |
Can be http or https.
|
| Host | Specifies the host name of the machine on which the ClickHouse is running. |
| Port | Port of ClickHouse HTTP interface listener (8123 by default). |
| Database | The ClickHouse database to connect to. |
| Username | The username to connect with. |
| Password | The password to connect with. |
| Compression | if true gzip compression is used. |
Important: with this driver parameters placeholders in "Select Query" should be formatted like in CLI (as {<name>:<data type>}
For example:
@paramName[ column={{ {0}:Int32 }} ]
where {{ is resolved to { and {0} inserts a parameter name (which is "paramName" in this sample).
Note that data type specifier refers to ClickHouse data types like
Int32, Int64, String, Decimal, String, Bool, Date, DateTime etc.
Connection String example (for ClickHouse.Ado .NET driver):
Host=hostName;Port=9000;Database=default;Compress=True;Compressor=lz4;BufferSize=8192;User=default;Password=
| Host | Specifies the host name of the machine on which the ClickHouse is running. |
|---|---|
| Port | Port of ClickHouse TCP listener (9000 by default in non-TLS mode or 9440 in TLS). |
| Encrypt |
Specify true if your CH listener operates in TLS mode.
|
| Database | The ClickHouse database to connect to. |
| BufferSize | TCP client receive buffer size. Default value is 1024 which may be to small if you need to load thousands of rows for your reports. |
| User | The username to connect with. |
| Password | The password to connect with. |
| Compress | if true compression is used for packets sent between SeekTable and ClickHouse server. |
To define date-part dimensions (date's "year", "month", "day" etc) you may add dimensions defined with an appropriate SQL expression:
FieldtoYear(date_column)toMonth(date_column) + Format={0:MMM} (Jan, Feb etc) or {0:MMMM} (January, February etc)toDayOfMonth(date_column)toDayOfWeek(date_column)-1 + Format={0:ddd} (Mon, Tue etc) or {0:dddd} (Monday, Tuesday etc)toQuarter(date_column)toISOWeek(date_column)If your report uses a measure with Type=FirstValue (defined with a custom SQL aggregate expression) you still can get empty totals even if "GROUP BY CUBE" option is enabled. This may happen because ClickHouse sets 0 or empty string values in the subtotals rows, and if grouping columns have empty values (or 0 in case of number-type column) SeekTable cannot determine which rows to use as totals.
If you want to get totals for FirstValue measures you need to guarantee that report's dimensions don't have empty strings or 0 values. For this purpose you can define these dimensions with custom SQL expressions like that:
if(empty(str_column),'(empty)',str_column)
Note that dimension's name cannot remain str_column in this case because this causes an SQL error when this expression is used in the "GROUP BY" (one more ClickHouse-specific thing).