how to configure "Official Clickhouse DB Connector"

[harshal852001]

How to use DB connector for Qliksense to access Clikchouse database ..Kindly share me detail for configuration would be greatly help.

If someone aware kindly share me the detail would be greatly help me a lot.

as I can see the database table in the Dbeaver but not able to configure sameway as did for MySQl to access Clickhouse database.

[jprdonnelly]

@harshal852001 - you will need to use one of the ClickHouse ODBC drivers (if using Sense Enterprise on Windows, or Qlik Cloud with the latest Data Gateway - Direct Access release).

Alternatively, I have had great success using the REST connector to query ClickHouse as well.

[harshal852001]

I tried using Rest connector but Im not able to see the tables..

and regards to ODBC connector I tried but DSN server detail not appeared in ODBC 64bit ...Its  Enterprise qliksense logins.

[jprdonnelly]

@harshal852001 - for Sense Enterprise on Windows, you will need to have the System DSN created with the same driver and name/connection string on each node for it to be reliably visible when attempting to create a connection or perform a reload with a defined connection. The "ODBC" option for a data connection acts as a passthrough to the drivers that are installed on the node your session, or a scheduled reload, is running on.

For REST, you are not going to be able to browse the tables and databases like an ODBC connection. You will rather create the REST connection to https://{your-cluster-name}/ping. This will always return "OK" or HTTP 200. Authentication is probably via headers X-ClickHouse-User and X-ClickHouse-Key (password). The connection will need to have "WITH CONNECTION" access enabled.

From there, each actual ClickHouse query can be passed using the WITH CONNECTION function as an additional QUERY parameter. Below is a very simple example using the full "ontime" dataset, of which a tutorial exists on the ClickHouse website. Take note where "FORMAT CSVWithNames" is used, other output formats would need additional formatting context during the Qlik script SELECT/LOAD section.

LIB CONNECT TO 'rest_clickhouse';

[ontime]:
LOAD	
    [Year] as [ontime.year],
	[Reporting_Airline] as [ontime.airline],
	[Tail_Number] as [ontime.tailNum],
	[Origin] as [ontime.origin],
	[OriginCityName] as [ontime.originCity],
	[Dest] as [ontime.dest],
	[DestCityName] as [ontime.destCity];
    
SQL SELECT 
	"Year",
	"Reporting_Airline",
	"Tail_Number",
	"Origin",
	"OriginCityName",
	"Dest",
	"DestCityName"
FROM CSV (header on, delimiter ",", quote """") "ontime"

WITH CONNECTION (
  URL "https://clickhouse.example.com/",
  QUERY "query" "select Year,Reporting_Airline,Tail_Number,Origin,OriginCityName,Dest,DestCityName from ontime ORDER BY Year FORMAT CSVWithNames;"
);

[harshal852001]

Thanks jprdonnelly, I wil check and update you..

[harshal852001]

Hello Jprdonnelly,

I got this error 

The following error occurred:

Connector reply error: Unexpected lexem 'LiteralMultiline' ('SELECT id, company_id, user_id, search_table, search_key, search_value, preferred_vendor, `date`, amount_found, ip_address, referer, service_source, search_exec_time, `filter`, `offset`, `limit`, maxmatches, cutoff, exact, created_at, updated_at FROM search_logs;') at (13,282). Expected 'Literal'. Check SELECT STATEMENT syntax with documentation.

Script :

LIB CONNECT TO 'Clickhouse_db (asgu_loc.qliksense)';

[ontime]:

LOAD

    [id] as [ontime.id],

[company_id] as [ontime.company_id],

[user_id] as [ontime.user_id],

[search_table] as [ontime.search_table],

[search_key] as [ontime.search_key],

[search_value] as [ontime.search_value],

[preferred_vendor] as [ontime.preferred_vendor];

SQL SELECT 

"id",

"company_id",

"user_id",

"search_table",

"search_key",

"search_value",

"preferred_vendor"

FROM CSV (header on, delimiter ",", quote """") "ontime"

WITH CONNECTION (

  URL "http://192.168.222.161:8123/play?user=default#c2hvdyBkYXRhYmFzZXM7",

  QUERY "query" "SELECT id, company_id, user_id, search_table, search_key, search_value, preferred_vendor, `date`, amount_found, ip_address, referer, service_source, search_exec_time, `filter`, `offset`, `limit`, maxmatches, cutoff, exact, created_at, updated_at 

  FROM search_logs;"

);

[jprdonnelly]

@harshal852001 - hrm.

I suspect that the QUERY line has carriage returns in it. Try to ensure that the script line with QUERY has no line breaks in it and try again.

Also, note that you will want to add FORMAT AS CSVWithNames to the end of your QUERY string. Meaning, it would read:

QUERY "query" "SELECT id, company_id, user_id, search_table, search_key, search_value, preferred_vendor, `date`, amount_found, ip_address, referer, service_source, search_exec_time, `filter`, `offset`, `limit`, maxmatches, cutoff, exact, created_at, updated_at FROM search_logs FORMAT CSVWithNames;"

 on one line, as you are doing a FROM CSV in your SELECT statement.