Querying data from Clickhouse - ValueError: no field of name

[etadelta222]

Installed the clickhouse-sqlalchemy driver per Altinity blog

Created user in Clickhouse and granted select on db per Altinity blog.

Clickhouse is single cluster version

Connected to Clickhouse via Superset using URI:

clickhouse://{username}:{password}@{hostname}:{port}/{database}

Successfully connected and am able to see the schema and tables. When I tried to query a table I did not see any result. When I query same table using DBeaver, logged in as the same user the query returns results. When I tried to use the PREVIEW: [table name] I get an error (below).

I checked the superset log and see this error:

I was able to query the system.errors table in Clickhouse but the result set didn't show the header.
It's capturing error code 192, UNKNOWN_USER: There is no user superset user in users.xml

Expected results

I expected the query to return result set.

How to reproduce the bug

create user:

CREATE ROLE IF NOT EXISTS ro_role;
GRANT SELECT ON clickhouse_etl.* to ro_role;

CREATE USER IF NOT EXISTS superset_user IDENTIFIED WITH SHA256_PASSWORD BY 'password' DEFAULT ROLE ro_role;

create table:

CREATE TABLE clickhouse_etl.data	
(	
`column_1`	UInt64,
`column_2`	String,
`column_3`	String,
`column_4`	String,
`column_5`	String,
`column_6`	String,
`column_7`	DateTime,
`column_8`	Decimal(14, 2),
`column_9`	Decimal(14, 2),
`column_10`	String,
`column_11`	Decimal(14, 2),
`column_12`	Decimal(14, 2),
`column_13`	Decimal(14, 2),
`column_14`	Decimal(14, 2),
`column_15`	Decimal(14, 2),
`column_16`	Float64,
`column_17`	Float64,
`column_18`	UInt64,
`column_19`	String,
`column_20`	String,
`column_21`	String,
`column_22`	String,
`column_23`	String,
`column_24`	String,
`column_25`	String,
`column_26`	String,
`column_27`	String,
`column_28`	String,
`column_29`	String,
`column_30`	String,
`column_31`	String,
`column_32`	DateTime,
`column_33`	DateTime,
`column_34`	DateTime,
`column_35`	DateTime,
`column_36`	DateTime,
`column_37`	DateTime,
`column_38`	DateTime,
`column_39`	DateTime,
`column_40`	UInt64,
`column_41`	String,
`column_42`	String,
`column_43`	String,
`column_44`	String,
`column_45`	String,
`column_46`	String,
`column_47`	UInt64,
`column_48`	UInt64,
`column_49`	Decimal(14, 2),
`column_50`	Decimal(14, 2),
`column_51`	Decimal(14, 2),
`column_52`	Decimal(14, 2),
`column_53`	Float64,
`column_54`	String,
`column_55`	String,
`column_56`	String,
`column_57`	String,
`column_58`	UInt64,
`column_59`	DateTime,
`column_60`	String,
`column_61`	String,
`column_62`	Decimal(14, 2),
`column_63`	Decimal(14, 2),
`column_64`	Decimal(14, 2),
`column_65`	String,
`column_66`	String,
`column_67`	String,
`column_68`	String,
`column_69`	UInt64,
`column_70`	DateTime,
`column_71`	String,
`column_72`	UInt64,
`column_73`	UInt64,
`column_74`	UInt64,
`column_75`	String
)	
ENGINE = MergeTree	
PARTITION BY toYYYYMM(column_33)	
ORDER BY column_1	
SETTINGS index_granularity = 8192

Environment

(please complete the following information):

• superset version: Superset 1.1.0
• python version: 3.8.5
• node.js version: v10.19.0
• Clickhouse version: 21.6.5.37
• Drivers:
  clickhouse-driver==0.2.1
  clickhouse-sqlalchemy==0.1.6

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
• I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

I posted about this in Superset slack channel and Robert Hodges suggested i create an issue. It might actually be a Clickhouse bug.

[hodgesrm]

Hi @etadelta222,

Based on your URL you are using the sqlalchemy-clickhouse driver, which is not maintained. Please switch to the clickhouse-sqlalchemy driver by changing to the correct URL. (Yes the name is confusing.) Your URL should look like the following:

clickhouse+native://{username}:{password}@{hostname}:{port}/{database}

Let me know if you still see the error there.

[etadelta222]

Thanks @hodgesrm, I actually did install clickhouse-sqlalchemy (copied the command directly from the blog during install). I did expect the URI to be clichouse+native: but that didn't work so I had to switch to using just clickhouse://.
I am able to connect to the clickhouse-public example from blog without issues using clickhouse+native://`

[hodgesrm]

When you say clickhouse+native "didn't work" what exactly does that mean? Did the connection fail and if so what error message did you see in the log?

You are able to connect to the github endpoint so that indicates the packages are correctly installed and Superset is seeing them.

[etadelta222]

I'm seeing these errors in the Superset UI:

And the Superset log is showing this:
Jul 27 20:34:18 ip-10-0-7-125 superset[2393803]: DEBUG:clickhouse_driver.connection:Connecting. Database: clickhouse_etl. User: superset_user
Jul 27 20:34:18 ip-10-0-7-125 superset[2393803]: DEBUG:clickhouse_driver.connection:Connecting to 10.x.x.xxx:8123
Jul 27 20:34:18 ip-10-0-7-125 superset[2393803]: INFO:werkzeug:35.170.38.74 - - [27/Jul/2021 20:34:18] "POST /api/v1/database/test_connection HTTP/1.1" 422 -

Jul 27 20:34:42 ip-10-0-7-125 superset[2393803]: DEBUG:clickhouse_driver.connection:Connecting. Database: clickhouse_etl. User: superset_user
Jul 27 20:34:42 ip-10-0-7-125 superset[2393803]: DEBUG:clickhouse_driver.connection:Connecting to 10.x.x.xxx:8123
Jul 27 20:34:42 ip-10-0-7-125 superset[2393803]: INFO:werkzeug:35.170.38.74 - - [27/Jul/2021 20:34:42] "POST /api/v1/database/ HTTP/1.1" 422 -

[save-my-heart]

See this:
xzkostyan/clickhouse-sqlalchemy/

[etadelta222]

Thanks for sharing that @save-my-heart! We were able to install clickhouse-sqlalchemy from master with the fix (in your link) and now I'm getting result in Superset. One thing which is still strange is that the clickhouse+native option is not working. I'm just using default http.

[michael-liumh]

Thanks for sharing that @save-my-heart! We were able to install clickhouse-sqlalchemy from master with the fix (in your link) and now I'm getting result in Superset. One thing which is still strange is that the clickhouse+native option is not working. I'm just using default http.

when you are using clickhouse+native:// , you need to set the port to 9000

[rusackas]

Based on the comments above, this one sounds resolved. Let me know if that's not the case, and we can re-open with updated context.