Create Sink to Clickhouse table

[reidlai]

Describe the bug

I tried to create a sink pointing to Cllickhouse table with ReplacingMergeTree but seems cannot see the table.

E.g. I create the table xxx using clickhouse client

(base) ➜ ~ clickhouse client --host localhost --port 9000 --user clickhouse --password ClickHouse client version 24.7.3.42 (official build). Password for user (clickhouse): Connecting to localhost:9000 as user clickhouse. Handling connection for 9000 Connected to ClickHouse server version 24.8.4.

ClickHouse client version is older than ClickHouse server. It may lack support for new features.

Warnings:

• Delay accounting is not enabled, OSIOWaitMicroseconds will not be gathered. You can enable it using echo 1 > /proc/sys/kernel/task_delayacct or by using sysctl.

Handling connection for 9000 halo-clickhouse-dev-shard0-1.halo-clickhouse-dev-headless.halo-clickhouse-dev.svc.cluster.local :) show table organization_geolocationhierarchy

SHOW CREATE TABLE organization_geolocationhierarchy

Query id: 828d61cc-a269-4a21-813e-241170821045

CREATE TABLE default.organization_geolocationhierarchy ( record_id String, treenode_code String, treenode_desc String, treenode_level_recid Nullable(String), parent_treenode_recid Nullable(String), updated_at DateTime64(3) ) ENGINE = ReplacingMergeTree(updated_at) ORDER BY (treenode_code, treenode_desc) SETTINGS index_granularity = 8192

1 row in set. Elapsed: 0.002 sec.

Then I tried to create sink as below:

ERROR:create_risingwave_cickhouse_sink_test:Error executing query - Query:

CREATE SINK IF NOT EXISTS organization_geolocationhierarchy_sink FROM organization_geolocationhierarchy WITH ( connector='clickhouse', clickhouse.url='http://:8123', clickhouse.user='clickhouse', clickhouse.password='xxxxxx', clickhouse.database='default', clickhouse.table='organization_geolocationhierarchy', type='append-only', force_append_only='true', primary_key='record_id' ) ;

, Error: Failed to run the query Caused by these errors (recent errors listed first): 1: gRPC request to meta service failed: Internal error 2: failed to validate sink 3: ClickHouse error: table "default"."organization_geolocationhierarchy" is not find in clickhouse

Error message/log

Error: Failed to run the query
Caused by these errors (recent errors listed first):
  1: gRPC request to meta service failed: Internal error
  2: failed to validate sink
  3: ClickHouse error: table "default"."organization_geolocationhierarchy" is not find in clickhouse

To Reproduce

No response

Expected behavior

I expected the sink was successfully created.

How did you deploy RisingWave?

using helm chart

The version of RisingWave

docker.risingwave.com/risingwavelabs/risingwave:v1.10.1

Additional context

No response

[yuhao-su]

cc @xxhZs

[xxhZs]

hi can you run select distinct database,name from system.tables where name = 'organization_geolocationhierarchy' in your clickhouse

[reidlai]

xxx.xxx.svc.cluster.local :) select distinct database,name from system.tables where name = 'organization_geolocationhierarchy'

SELECT DISTINCT database, name FROM system.tables WHERE name = 'organization_geolocationhierarchy'

Query id: f1873292-dccf-4fb2-830b-60c9b24d7c6c

Ok.

0 rows in set. Elapsed: 0.003 sec.

[reidlai]

after i move the table to another database , the query can show:

:) SELECT DISTINCT database, name FROM system.tables WHERE name = 'organization_geolocationhierarchy'

SELECT DISTINCT database, name FROM system.tables WHERE name = 'organization_geolocationhierarchy'

Query id: fffa00a9-6fa1-4372-851a-76029591a49c

┌─database─┬─name──────────────────────────────┐

1. │ │ organization_geolocationhierarchy │ └──────────┴───────────────────────────────────┘

1 row in set. Elapsed: 0.003 sec.

But Risingwave still cannot see the table

[reidlai]

ERROR:create_**_cickhouse_sink_test:Error executing query - Query:

CREATE SINK IF NOT EXISTS legalentity_holdingrelation_sink FROM legalentity_holdingrelation WITH (

connector='**********',

**********.url='http://**********.**********.svc:8123',

**********.user='**********',

**********.password='**********',

**********.database='********',

**********.table='legalentity_holdingrelation',

type='append-only',

force_append_only='true',

primary_key='record_id'

) ;

, Error: Failed to run the query

Caused by these errors (recent errors listed first):

1: gRPC request to meta service failed: Internal error

2: failed to validate sink

3: ClickHouse error: table "****"."legalentity_holdingrelation" is not find in ****

[xxhZs]

Hi, could you make sure your sink info is ok, the problem is because rw is running this query and not getting the data. I'll try it with your version as well, may be a setup item error or compatibility issue

[xxhZs]

Hello, testing your version works fine. Can you check if the databases and tables in your rw sink are the same as the ones found in the ck?

[fuyufjh]

Close due to inactivity. @reidlai Please feel free to reopen if the problem persists