How to correctly set up a Kafka topic table with Avro schema and primary key in RisingWave?
I'm trying to set up a simple table on a Kafka topic with Avro schema in RisingWave. I'm encountering an error related to the primary key specification. Here's the SQL command I used:
CREATE TABLE IF NOT EXISTS accounts
INCLUDE KEY AS account_number
WITH (
connector='kafka',
topic='accounts',
properties.bootstrap.server='redpanda-0:9092',
scan.startup.mode='earliest'
) FORMAT UPSERT ENCODE AVRO (
schema.registry = '<http://redpanda-0:8081>',
message = 'KafkaSeed.Models.account'
);The error message I receive is:
ERROR: Failed to run the query
Caused by:
Protocol error: Primary key must be specified to account_number when creating source with FORMAT UPSERT ENCODE AvroI'm not sure how to resolve this issue and correctly specify the primary key. Can you help me with the correct syntax and approach?
Tim Gitchel
Asked on Mar 27, 2024
To resolve the primary key issue when setting up a Kafka topic table with Avro schema in RisingWave, you need to include the primary key in the table schema and ensure it aligns with the Avro schema. Here's the corrected SQL command:
CREATE TABLE IF NOT EXISTS accounts (
primary key (account_key)
)
INCLUDE KEY AS account_key
WITH (
connector='kafka',
topic='accounts',
properties.bootstrap.server='redpanda-0:9092',
scan.startup.mode='earliest'
)
FORMAT UPSERT ENCODE AVRO (
schema.registry = '<http://redpanda-0:8081>',
message = 'KafkaSeed.Models.account'
);In this command, account_key is used as the primary key for the table, and it is also specified in the INCLUDE KEY AS clause. This aligns the primary key in the table with the key used in the Kafka topic. Make sure that the account_key is consistent with the key defined in your Avro schema for the Kafka topic.