Clickhouse: Table schema.ibis_clickhouse_metadata_randomcode doesn't exist. (UNKNOWN_TABLE)

[rara011088]

I am trying to connect WrenAI to Clickhouse, but I got the error:
[ERROR] DataSourceResolver - Failed to get project tables
"message": Code: 60. DB:: Exception: Table schema.ibis_clickhouse_metadata_randomcode doesn't exist. (UNKOWN_TABLE)

The error happens in WrenAI/wren-ui/src/apollo/server/adaptors/ibisAdaptor.ts
where it calls the API /v2/connector/{data_source}/metadata/tables

In my Clickhouse database I get different results when I run show tables. This is related to replicated Clickhouse clusters

And the table it is trying to find (schema.ibis_clickhouse_metadata_randomcode) is in one of the nodes.

How can I modify the code so that it can create the metadata tables on cluster 'default'?
I couldn't find the part where it has CREATE TABLE statement

[wwwy3y3]

@goldmedal or @douenergy, could you take a look ?

[goldmedal]

Hi @rara011088
In the engine side (the endpoint of /v2/connector/{data_source}/metadata/tables), we use the following SQL to get the table metadata:

            SELECT
                c.database AS table_schema,
                c.table AS table_name,
                t.comment AS table_comment,
                c.name AS column_name,
                c.type AS data_type,
                c.comment AS column_comment
            FROM
                system.columns AS c
            JOIN
                system.tables AS t
                ON c.database = t.database
                AND c.table = t.name
            WHERE
                c.database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema', 'pg_catalog');

https://github.com/Canner/wren-engine/blob/47ca29ebba291100ba5d70ce1790f9887eaed7a0/ibis-server/app/model/metadata/clickhouse.py#L18

I'm not sure where you got the error. Could you provide more steps for reproducing?

How can I modify the code so that it can create the metadata tables on cluster 'default'?
I couldn't find the part where it has CREATE TABLE statement

Wren AI is responsible for the read-only thing. I think we never do CREATE TABLE for the data source.

[rara011088]

Thanks for the response @goldmedal

I looked through the ibis-project repository, and found the code where the create view statement seems to be the problem:
https://github.com/ibis-project/ibis/blob/0f23dc5fda6765e66a93f711fb1f46a21fa0348c/ibis/backends/clickhouse/__init__.py#L549

It generates the view name starting with 'clickhouse_metadata'

So Wren AI doesn't create table/view for the data source, but ibis does?

Error log:

File "/app/app/routers/v2/connector.py", line 199, in get_table_list
    ).get_table_list()
File "/app/app/model/metadata/clickhouse.py", line 22, in get_table_list
    response = self.connection.sql(sql).to_pandas().to_dict(orient="records")
File "/app/.venv/lib/python3.11/site-packages/ibis/backends/sql/__init__.py", line 179, in sql
    schema = self._get_schema_using_query(query)
 File "/app/.venv/lib/python3.11/site-packages/ibis/backends/clickhouse/__init__.py", line 551, in _get_schema_using_query
    with closing(self.raw_sql(f"CREATE VIEW {name} AS {query}")):

For reproducing the error:
In Clickhouse, create all tables by using cluster
CREATE TABLE table_name on cluster 'default'
Connect Clickhouse to Wren AI, and you will get error:
Table schema_name.ibis_clickhouse_metadata_324hcuhcis24h2chu doesn't exist (UNKNOWN TABLE)

[rara011088]

UPDATE: I overrided _get_schema_using_query with my own custom_get_schema_using_query to add 'ON CLUSTER' when creating the clickhouse_metadata tables and it worked

[goldmedal]

It generates the view name starting with 'clickhouse_metadata'

So Wren AI doesn't create table/view for the data source, but ibis does?

Indeed. ibis tries to get the query result schema using the create-view way.

Error log:

File "/app/app/routers/v2/connector.py", line 199, in get_table_list
    ).get_table_list()
File "/app/app/model/metadata/clickhouse.py", line 22, in get_table_list
    response = self.connection.sql(sql).to_pandas().to_dict(orient="records")
File "/app/.venv/lib/python3.11/site-packages/ibis/backends/sql/__init__.py", line 179, in sql
    schema = self._get_schema_using_query(query)
 File "/app/.venv/lib/python3.11/site-packages/ibis/backends/clickhouse/__init__.py", line 551, in _get_schema_using_query
    with closing(self.raw_sql(f"CREATE VIEW {name} AS {query}")):

For reproducing the error: In Clickhouse, create all tables by using cluster CREATE TABLE table_name on cluster 'default' Connect Clickhouse to Wren AI, and you will get error: Table schema_name.ibis_clickhouse_metadata_324hcuhcis24h2chu doesn't exist (UNKNOWN TABLE)

I haven't looked in detail for this issue, but I guess it could be an issue of ibis. Maybe we can file an issue for them.

UPDATE: I overrided _get_schema_using_query with my own custom_get_schema_using_query to add 'ON CLUSTER' when creating the clickhouse_metadata tables and it worked

Thanks. I think it could be a workaround we can do before the ibis fix it.

[goldmedal]

@rara011088 Are you willing to submit a PR to wren-engine for the workaround? Or maybe you can file an issue to describe how you override the Ibis method. We will put it in our backlog to fix it.

[rara011088]

@goldmedal I have provided the steps here #1189