Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Create Sink to Clickhouse table #18457

Closed
reidlai opened this issue Sep 9, 2024 · 8 comments
Closed

Create Sink to Clickhouse table #18457

reidlai opened this issue Sep 9, 2024 · 8 comments
Labels
type/bug Something isn't working

Comments

@reidlai
Copy link

reidlai commented Sep 9, 2024

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://<clickhouse_host>: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

@reidlai reidlai added the type/bug Something isn't working label Sep 9, 2024
@github-actions github-actions bot added this to the release-2.1 milestone Sep 9, 2024
@yuhao-su
Copy link
Contributor

yuhao-su commented Sep 9, 2024

cc @xxhZs

@xxhZs
Copy link
Contributor

xxhZs commented Sep 9, 2024

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

@reidlai
Copy link
Author

reidlai commented Sep 9, 2024

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
Copy link
Author

reidlai commented Sep 11, 2024

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. │ <db_name_was_hided> │ organization_geolocationhierarchy │
    └──────────┴───────────────────────────────────┘

1 row in set. Elapsed: 0.003 sec.

But Risingwave still cannot see the table

@reidlai
Copy link
Author

reidlai commented Sep 11, 2024

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
Copy link
Contributor

xxhZs commented Sep 11, 2024

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
Copy link
Contributor

xxhZs commented Sep 12, 2024

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 fuyufjh removed this from the release-2.1 milestone Oct 17, 2024
@fuyufjh
Copy link
Member

fuyufjh commented Oct 17, 2024

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

@fuyufjh fuyufjh closed this as not planned Won't fix, can't repro, duplicate, stale Oct 17, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
type/bug Something isn't working
Projects
None yet
Development

No branches or pull requests

4 participants