Connecting to Clickhouse

[sisense-nuna]

I was recently speaking with @Kat about certified connectors and related status for those connectors – specifically related to Clickhouse, which is listed as a Certified data connector for Sisense.

Are there any Sisense customers out there that are using Clickhouse successfully with Sisense? To us, "successfully" would at a minimum include:

1. Being able to connect using a Live Connection for interactive query and aggregation via Sisense Notebooks
2. Being able to load data from Clickhouse into Elasticubes

Thanks all!

[Learner]

Hello NUNA,
I want to connect the Sisense with Clickhouse DB. can you please share the steps or any detail link ? 
Thanks

[sisense-nuna]

We'll share what we can once we have this sorted out. Ideally we get updated instructions published on the Sisense docs vs. community threads.

According to Sisense's website, Clickhouse is a "certified connector" – however it's listed as requiring use of the generic JDBC driver elsewhere in the Sisense docs here.

Still waiting on the official response / support for getting properly connected to Clickhouse.

[Learner]

Thanks for the reply. 
I have been following link :
https://docs.sisense.com/main/SisenseLinux/connecting-to-custom-connectors-with-jdbc-drivers.htm#CDa... 

according to this link Cdata is official partner of Sisense and you can download the driver from the website. but I unable to find "Clickhouse" in the list.
https://www.cdata.com/solutions/bi/sisense/ 

[alex-frisbie]

Clickhouse supports both MySql and PostgreSql wire protocols. Setting these up on the Clickhouse side allows you to use the MySql or Postgres connectors to connect to Clickhouse. As Sisense can go live to Postgres, you can also go live to Clickhouse using the Postgres connector.

[sisense-nuna]

MySQL worked for you? We get an error - "text": "{\"error\":{\"moreInfo\":{\"why\":{\"name\":\"InvalidParametersException\",\"message\":\"InvalidParametersException\",\"errorCode\":8,\"errorMessage\":\"Code: 47. DB::Exception: Missing columns: 'TABLE_COMMENT' while processing query: 'SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, multiIf(TABLE_TYPE = 'BASE TABLE', multiIf((TABLE_SCHEMA = 'mysql') OR (TABLE_SCHEMA = 'performance_schema'), 'SYSTEM TABLE', 'TABLE'), TABLE_TYPE = 'TEMPORARY', 'LOCAL_TEMPORARY', TABLE_TYPE) AS TABLE_TYPE, TABLE_COMMENT AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION FROM (SELECT \",\"wrongParameter\":null,\"dbSourceErrorMessage\":null,\"trace\":null}},\"code\":22002,\"message\":\"Cannot establish connection with provided parameters.\",\"status\":400,\"httpMessage\":\"Bad Request\"}}"

[sisense-nuna]

And if we try from postgres we get "ERROR: ClickHouse doesn't support extended query mechanism"

[sisense-nuna]

And if I set "preferQueryMode=simple" it then complains that pg_database doesnt exist.

[sisense-nuna]

I tried against an the most recent version of Clickhouse and it still fails. Are you doing some magic with the INFORMATION_SCHEMA DB or pg_database tables to get MySQL and PostgreSQL to connect?

[Learner]

Hello,
I am able to connect with Clickhouse and create a dashboard on the this data. 
Simple Steps : 
1. Go to Data Sources.
2. Click on Other JDBC from the list. 
3.  Then fill the information :
      jdbc:clickhouse://"IP of Clickhouse DB":8123/"DatabaseName"
     <user-name>User Name</user-name>
     <password>YourPassword</password>
     com.clickhouse.jdbc.ClickHouseDriver
after this you will able to connect with Clickhouse.