"Sets" seems not to be recognized with this last connector version (ClickHouse by Altinity Inc)

[Fred5778]

Hi,

I have tried your new connector with the hope it would correct limitations because Tableau does not understand the ClickHouse SQL dialect.
Before I had a several limitations and issues with JOIN ON syntax and LOD expressions as FIXED, INCLUDE etc.

for example :
Une erreur s'est produite lors de la communication avec la source de données Autres bases de données (ODBC) 'qoe_fixe'
Problème de connexion : Tableau n'a pas pu se connecter à la source de données.
Code d'erreur : B19090E0
HTTP status code: 501
Received error:
Code: 48, e.displayText() = DB::Exception: JOIN ON does not support OR. Unexpected '(zipcode = t0.zipcode) OR (isNull(zipcode) AND isNull(t0.zipcode))': While processing (zipcode = t0.zipcode) OR (isNull(zipcode) AND isNull(t0.zipcode)) (version 21.3.15.4 (official build))

but there were no errors displayed on calculated fields, even if they were based on "sets" etc..

Now, If I used your new connector, I encounter another problem:
It seems that "sets" are not recognized??
For exemple :
In this calculated field I use a set called « Dsl_Indispo_One_Day »

SUM(IIF(CONTAINS([Infra],'o1'),IIF([Dsl_Indispo_One_Day], 1, 0),0)) / SUM(IIF(CONTAINS([Infra],'o1'),1,0))

With the new connector, you can see that it has been replaced by [test] (Why ?) and it is writen that le calculation contains errors.

If I change in the [test] in _%NbIndispoDsl to use a condition instead of the « set », namely :

Instead of

The error disappears.

But, it doesn’t work with an other field.

If I do the same, it leads to an invalid calculation

and my dashboards are not displayed...

Thanks for your help.

Fred

[dima-altinity]

@Fred5778
could you take a look into Tableau log files, find SQL statement that it generates and send to ClickHouse and post it here.?

[Fred5778]

Hi,

the problem is that there is not SQL generated, even if a refresh the data source!

In fact, as I tried to explain, when I use "ClickHouse by Altinity Inc", I have access to "sets", but all the calculated fields based on "sets" contains errors.

I'am going to use the older odbc connexion, to show you that the calculation based on "sets" are OK :
With this odbc connexion, the SQL is generated and have an error on the Dashboard

but the calculated fields based on "sets" doesn't show errors :

In attach file, you will see my SQL Statement (Performance recording) with this last configuration

first_query.txt
second_query.txt

Regards and thanks for your help.

[Fred5778]

Hi Dima,

Could you have a look to the issue I raised?
Have you been able to reproduce it?

If I can help you with other tests, do not hesitate.

Regards,

Fred

[dima-altinity]

@Fred5778
Tableau generates JOIN that uses "OR" as part of join condition and CH doesnt support that.
Need to figure our - why?

can you check if "cubedsl_derivecounters_daily_ndi.zipcode" is defined in CH as NULLABLE (field that could contain NULL values)?
show create table cubedsl_derivecounters_daily_ndi;

[Fred5778]

Hi,

I'am going to answer you, but I think that you don't focus on the right issue!
I followed this blog :
https://altinity.com/blog/introducing-the-altinity-tableau-connector-for-clickhouse

It describes the tow way of connexion.
"There are two ways to connect Tableau to ClickHouse. First is via the ClickHouse ODBC driver. This works but has significant limitations because Tableau does not understand the ClickHouse SQL dialect.

The second is via the Altinity ClickHouse Tableau Connector. The connector provides metadata that enables Tableau to translate native queries properly to ClickHouse SQL. With the connector, Tableau visualization features can be correctly implemented in ClickHouse."

The problem :
Code: 48, e.displayText() = DB::Exception: JOIN ON does not support OR. Unexpected '(zipcode = t0.zipcode) OR (isNull(zipcode) AND isNull(t0.zipcode))': While processing (zipcode = t0.zipcode) OR (isNull(zipcode) AND isNull(t0.zipcode)) (version 21.3.15.4 (official build))
occurs with the first way of connexion : (see below the connexion in TABLEAU SW)

If I use the second way of connexion :
(see below the connexion in TABLEAU SW) :

No SQL Query is sent!. I have this problem I tried to explain you namely, all the calculated fields based on declared sets are in errors!
This is on this error, with the second way of connexion, I would like you work on!

Perhaps this is because, I set a wrong port in the connexion definition : 8123 instead of 8443.

If I set the port 8443, I get this message :

witch port do I have to set in the connexion?

Nevertheless, I give you what you asked me :

CREATE TABLE qoe_fixe.cubedsl_derivecounters_daily_ndi
(
idur String,
cdate Date,
sum_deltaiaduptime_1day_old Nullable(Int64),
sum_reboot_1day_old Nullable(Int64),
sum_deltadsluptime_1day_old Nullable(Int64),
sum_deltadslsynctotal_1day_old Nullable(Int64),
sum_deltadslcountersunavailabletime_1day_old Nullable(Int64),
sum_deltadslcounterscrcdown_1day_old Nullable(Int64),
moy_dslbitratedown_1day_old Nullable(Float64),
moy_dslatteignabledown_1day_old Nullable(Float64),
moy_dslsnrdown_1day_old Nullable(Float64),
percent_indispo_dsl_1day_old Nullable(Float64),
nbpushtotal_1day_old Nullable(Int64),
net00_bas Nullable(String),
net01_routerco Nullable(String),
net02_rescollecte Nullable(String),
net03_boucleura Nullable(String),
net04_piedgrappe Nullable(String),
net05_ura Nullable(String),
net06_switchcaa Nullable(String),
net07_iam Nullable(String),
net08_dslam Nullable(String),
net09_rack Nullable(String),
net10_chassis Nullable(String),
net11_carte Nullable(String),
infra Nullable(String),
sum_deltaiaduptime_15days_old Nullable(Int64),
sum_reboot_15days_old Nullable(Int64),
sum_deltadsluptime_15days_old Nullable(Int64),
sum_deltadslsynctotal_15days_old Nullable(Int64),
sum_deltadslcountersunavailabletime_15days_old Nullable(Int64),
sum_deltadslcounterscrcdown_15days_old Nullable(Int64),
moy_dslbitratedown_15days_old Nullable(Float64),
moy_dslatteignabledown_15days_old Nullable(Float64),
moy_dslsnrdown_15days_old Nullable(Float64),
percent_indispo_dsl_15days_old Nullable(Float64),
sum_deltaiaduptime_5days_old Nullable(Int64),
sum_reboot_5days_old Nullable(Int64),
sum_deltadsluptime_5days_old Nullable(Int64),
sum_deltadslsynctotal_5days_old Nullable(Int64),
sum_deltadslcountersunavailabletime_5days_old Nullable(Int64),
sum_deltadslcounterscrcdown_5days_old Nullable(Int64),
moy_dslbitratedown_5days_old Nullable(Float64),
moy_dslatteignabledown_5days_old Nullable(Float64),
moy_dslsnrdown_5days_old Nullable(Float64),
percent_indispo_dsl_5days_old Nullable(Float64),
year Int16,
month Int16,
day Int16,
ndi Nullable(String),
ndi_9t Nullable(String),
code_insee Nullable(String),
nom_ville Nullable(String),
zipcode Nullable(String),
code_departement Nullable(String),
departement Nullable(String),
code_region Nullable(String),
region Nullable(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(cdate)
ORDER BY (cdate, idur)
SETTINGS index_granularity = 8192

Regards,

Fred

[dima-altinity]

Hi @Fred5778

sorry, I didnt get initially where the problem is. I thought problem was in wrong SQL.
It seems that problem is that Tableau doesnt send any query to CH in case if you use connector.
Tableau Connector only provides info to Tableau how properly generate SQL query to ClickHouse.
If Tableau doesnt send query to CH - not sure that I could help here.

I would offer you to go this way. You already posted table DDL. If you could send me

1. data sample for this table
2. Tableau workbook with just 1 calculation or set that cause this problem ( or step by step instruction how to create it)
3. Tableau version
4. Tableau Connector version
5. ClickHouse ODBC version

then I will try to reproduce it on my local env.
And if problem could be reproduced - I'll collect all info and send it to Tableau support.

[Fred5778]

Hi,

I succeed to make it worked.
I just replaced "sets" in calculated fileds with the full condition and it works as I told you in the first message. It shows that with Altinity lastest connector, you can't uses "sets" in calculated fields. That is a problem but there is a workarround.
For exemple :

replace

for the field Code Postal KO which works for example when connecting to Impala database:

I have created this field :

and I replaced CODE Postal KO by :

but It becomes a mesure that you can't convert to dimension so that I can't use it as i wanted in my dashboard.

Regards,