search

ClickHouse / clickhouse-odbc

ODBC driver for ClickHouse
https://clickhouse.tech
Apache License 2.0
239 stars 84 forks source link

Date columns not working in Tableau (Delta table) #421

Open kutny opened 1 year ago

kutny commented 1 year ago

Hi, I have a delta table on AWS S3 created using your tutorial https://clickhouse.com/docs/en/engines/table-engines/integrations/deltalake

When using date columns in my Tableau dashboard, an error is triggered:

An error occurred while communicating with the Altinity ODBC ClickHouse by Altinity Inc data source 'jaffle_shop_orders+ (default)'
Bad Connection: Tableau could not connect to the data source.
Error Code: B19090E0
HTTP status code: 500
Received error:
Poco::Exception. Code: 1000, e.code() = 0, Exception: Cannot load time zone yyyy-MM-dd (version 23.4.2.11 (official build))

Connector Class: clickhouse_odbc, Version: 1.3
SELECT SUM(1) AS "cnt:jaffle_shop_orders_242FEC23DF364F49BAFF64BFECA94E47:ok",
  CAST(toYear((CASE WHEN (NOT isNull(toDate(parseDateTimeBestEffortOrNull(CAST("jaffle_shop_orders"."ORDER_DATE" AS Nullable(String)), 'yyyy-MM-dd')))) THEN toDate(parseDateTimeBestEffortOrNull(CAST("jaffle_shop_orders"."ORDER_DATE" AS Nullable(String)), 'yyyy-MM-dd')) WHEN (NOT isNull(toDate(parseDateTimeBestEffortOrNull(CAST("jaffle_shop_orders"."ORDER_DATE" AS Nullable(String)))))) THEN toDate(parseDateTimeBestEffortOrNull(CAST("jaffle_shop_orders"."ORDER_DATE" AS Nullable(String)))) ELSE NULL END)) AS Nullable(INTEGER)) AS "yr:ORDER_DATE:ok"
FROM "jaffle_shop_orders"
GROUP BY CAST(toYear((CASE WHEN (NOT isNull(toDate(parseDateTimeBestEffortOrNull(CAST("jaffle_shop_orders"."ORDER_DATE" AS Nullable(String)), 'yyyy-MM-dd')))) THEN toDate(parseDateTimeBestEffortOrNull(CAST("jaffle_shop_orders"."ORDER_DATE" AS Nullable(String)), 'yyyy-MM-dd')) WHEN (NOT isNull(toDate(parseDateTimeBestEffortOrNull(CAST("jaffle_shop_orders"."ORDER_DATE" AS Nullable(String)))))) THEN toDate(parseDateTimeBestEffortOrNull(CAST("jaffle_shop_orders"."ORDER_DATE" AS Nullable(String)))) ELSE NULL END)) AS Nullable(INTEGER))

Tableau schreenshot:

tablea_error

Delta table schema (from Databricks):

jaffle_shop_orders

kutny commented 1 year ago

Ok, works fine with the JDBC driver mentioned in your tutorial https://clickhouse.com/docs/en/integrations/tableau