This documentation page assumes that you already have a SeekTable account. Create your free account by signing up.

ClickHouse pivot table reports

ClickHouse can be used as a data source with SQL-compatible database connector. There are 2 ways how clients may connect to ClickHouse:

SeekTable supports both ways with 2 different ClickHouse drivers.

Native TCP/IP Protocol

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.

HTTP(S) Interface

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).

Date-part dimensions calculated with SQL expressions

To define date-part dimensions (date's "year", "month", "day" etc) you may add dimensions defined with an appropriate SQL expression:

  1. add new dimension with Type=Field
  2. fill Name with some unique value: say, "date_column_year"
  3. add one Parameter which should be a date-part ClickHouse SQL expression:
    • For year: toYear(date_column)
    • For month: toMonth(date_column) + Format={0:MMM} (Jan, Feb etc) or {0:MMMM} (January, February etc)
    • For day: toDayOfMonth(date_column)
    • For day-of-week: toDayOfWeek(date_column)-1 + Format={0:ddd} (Mon, Tue etc) or {0:dddd} (Monday, Tuesday etc)
    • For quarter: toQuarter(date_column)
    • For week-of-year: toISOWeek(date_column)

FirstValue measures and "GROUP BY CUBE" specifics

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).



Looking for an embedded solution? Try PivotData microservice which can be seamlessly integrated into any web application for pivot tables generation by ClickHouse with simple web API.