Skip to content

Latest commit

 

History

History
56 lines (44 loc) · 2.96 KB

ksql-server_for_data-dashboard-backend.md

File metadata and controls

56 lines (44 loc) · 2.96 KB

Kafka-data transformer (ksql-server) for data-dashboard-backend service

Reference: https://docs.ksql-server.io/

The data-dashboard-backend service uses data derived from Kafka topics imported into the observation table data-dashboard-backend service database. The data in the Kafka topics is transformed by the ksql-server data transformer to be imported into the observation table.

The ksql-server data transformer is able to register Consumer/Producers to Kafka that transform data in a topic and publish the results to another topic.

The provided ksql-server questionnaire_response_observations.sql and questionnaire_app_events_observation.sql SQL files transform, respectively, the questionnaire_response and questionnaire_app_event topics and publish the data to the ksql_observations topic. The ksql_observations topic is consumed by the radar-jdbc-connector service deployed for the data-dashboard-backend service (see: 20-data-dashboard.yaml).

When transformation of other topics is required, new SQL files can be added to this directory. These new files should be referenced in the cp-ksql-server -> ksql -> queries_ section of the etc/base.yaml.gotmpl file. New ksql-server SQL files should transform towards the following format of the ksql_observations topic:

    TOPIC KEY:
      PROJECT: the project identifier
      SOURCE: the source identifier
      SUBJECT: the subject/study participant identifier
    TOPIC VALUE:
      TOPIC: the topic identifier
      CATEGORY: the category identifier (optional)
      VARIABLE: the variable identifier
      OBSERVATION_TIME: datetime of the observation
      OBSERVATION_TIME_END: the end datetime of the observation (optional)
      TYPE: the type of the observation (STRING, STRING_JSON, INTEGER, DOUBLE)
      VALUE_TEXTUAL: the textual value of the observation (optional, must be set when VALUE_NUMERIC is NULL)
      VALUE_NUMERIC: the numeric value of the observation (optional, must be set when VALUE_TEXTUAL is NULL)

Conventions

The mapping of topic fields is straightforward. The CATEGORY and VARIABLE fields are flexible that map to:

Topic CATEGORY VARIABLE VALUE
questionnaire_reponse questionnaire name (e.g., follow_up_a.1.0.3) questionId (e.g, blwbg_lt2w_nervous) answer to question
questionnaire_app_event questionnaire name (e.g., follow_up_a.1.0.3) eventType (e.g., QUESTIONNAIRE_STARTED) event metadata

New messages are added to the ksql_observations topic by inserting into the observations stream ( see _base_observations_stream.sql):

INSERT INTO observations
SELECT
...
PARTITION BY q.projectId, q.userId, q.sourceId
EMIT CHANGES;