CODEWITHSUNDEEP
apache-kafkaksqldb
Renu
Renu

Reputation: 1

Split data to respective topics using KSQL

This is source topic ->>

{
  "subscriber": {
    "mem_ID": "587994622344",
    "mem_PIN": "07066",
    "case_NUMBER": "21307P019",
    "mem_FIRST_NAME": "Henry",
    "mem_MIDDLE_NAME": "S",
    "mem_LAST_NAME": "Taylor",
    "mem_ADD_1": "1255",
    "mem_ADD_2": "New Street",
    "mem_CITY": "Clark,NJ"
  },
  "patient": {
    "pat_ID": "133235413144",
    "pat_SEX": "M",
    "pat_DOB": "198760321",
    "case_NUMBER": "21307P019",
    "pat_FIRST_NAME": "Olive",
    "pat_MIDDLE_NAME": "",
    "pat_LAST_NAME": "Taylor",
    "pat_PLANE_TYPE": "HealthCare",
    "pat_PLAN_NAME": "HC"
  },
  "case": {
    "CASE_NUMBER": "21307P019",
    "CASE_TYPE": "Medical",
    "CASE_CODE": "MC",
    "CASE_START_DATE": "20220723",
    "CASE_END_DATE": "20220831",
    "CASE_AUTH_TYPE": "Insurance",
    "CASE_STATUS": "Initiated"
  },
  "service": {
    "svc_ID": "134324564678",
    "case_NUMBER": "21307P019",
    "svc_TYPE": "Day Care",
    "svc_CODE": "DC",
    "svc_FAC_ID": "HC",
    "svc_FAC_NAME": "Heart Center",
    "svc_PHY_ID": "34343555",
    "svc_PHY_NAME": "Dr.Charles"
  }
}

how to split the above source topic to different 4 topics using KSQL. For e.g. >> Subscriber topic should have only Subscriber Data like this

{
  "mem_ID": "587994622344",
  "mem_PIN": "07066",
  "mem_MIDDLE_NAME": "S",
  "mem_LAST_NAME": "Taylor",
  "mem_CITY": "Clark,NJ",
  "mem_ADD_2": "New Street",
  "mem_ADD_1": "1255",
  "case_NUMBER": "21307P019",
  "mem_FIRST_NAME": "Henry"
}

Similarly the other 3 topics Patient, Case and Service

Upvotes: 0

Views: 213

Answers (1)

OneCricketeer
OneCricketeer

Reputation: 191681

Create a Stream from your original data, then select those fields...

CREATE STREAM subscribers WITH(KAFKA_TOPIC="subscribers", VALUE_FORMAT="JSON") AS 
    SELECT subscriber.* FROM source_stream;

https://docs.ksqldb.io/en/latest/developer-guide/ksqldb-reference/create-stream-as-select/

Alternatively, start with 4 separate topics in your producer, and later join on the case number, for example

Upvotes: 1

Related Questions