ClickHouse can be used as an SQL-compatible data source. 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.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). |
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. |
Connection String example (ClickHouse.Client .NET driver):
Driver=ClickHouse.Client;Compression=True;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.
|
---|---|
Host | Specifies the host name of the machine on which the ClickHouse is running. |
Port | Port of ClickHouse HTTP interface listener (8123 by default). |
Protocol |
Can be http or https .
|
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. For example:
@paramName[ column={{ {0}:Int32 }} ]where
{{
is resolved to {
and {0}
inserts a parameter name (which is "paramName" in this sample).
To define date-part dimensions (date's "year", "month", "day" etc) you may add dimensions defined with an appropriate SQL expression:
Field
toYear(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).