I'm working with RisingWave and trying to create a table that ingests data from a Kinesis stream. The data is in nested JSON format, and I want to set a primary key for the table using a field from the nested JSON. I've managed to create a table and access the fields using the STRUCT
type, but I'm not sure how to define a primary key based on a field within the nested JSON. Here's an example of my table creation statement:
CREATE TABLE IF NOT EXISTS test (
data STRUCT<
"cob_dt" DATE,
...
>,
)
WITH (
connector='kinesis',
stream='dev-dwh-rockset-ods-bpm_data_entry',
aws.region='ap-southeast-1',
aws.credentials.access_key_id='xxx',
aws.credentials.secret_access_key='yyy'
)
ROW FORMAT JSON;
I want to set the primary key to (data).id
. How can I achieve this?
Bui Tien
Asked on May 05, 2023
Do you want the later events to overwrite the existing records, when they share the same (data).id
?
If so, currently I recommend you to create a SOURCE instead, and then create a materialized view on that source with DISTINCT ON (column1)
syntax.