Connector Creation Guide
Creating Dashboards for Reporting involves the following steps:
Understanding what database tables are required to be indexed to OpenSearch for the dashboard.
Creating a pipeline for the data flow to OpenSearch. This pipeline involves:
Creating one Debezium Connector containing the database table.
Creating one OpenSearch Connector for the database table.
Creating a dashboard on OpenSearch
Follow the guides on this page to learn more about each step in the process above.
This document contains instructions for the developers (or dashboard creators) on creating the required connectors and dashboards to visualize reports on OpenSearch.
Follow the Installation guide to install/update the connector configuration.
Prerequisites
Create a GitHub repository (or create a new directory in an existing repository) which is going to store the configuration for the connectors and the dashboards for OpenSearch.
Create a directory in the repository with these three folders
debezium-connectors
,opensearch-connectors
andopensearch-dashboards
.Identify the tables from the database whose data will be required for the reports.
Debezium connector creation
One debezium connector is sufficient for indexing all the required tables of one database. So create one connector for each database (rather than one for each table).
Create a json file in the
debezium-connectors
. Each json file corresponds to one debezium connector. With the following contents:
Each $
in the json file will be treated as an environment variable. Environment variables will be automatically picked up during installation. If you want to use a dollar in the file and not parse it as env variable during installation, replace your $
with ${dollar}
.
Add the list of all tables required from this database into the
table.include.list
field (in no particular order) (Accepts regex). For exampleThis list needs to include relationship tables of the current table. For example: if you want to index
g2p_program_membership
but would also like to retrieve the name of the program in which the beneficiary belongs, then you have to addg2p_program
as well.
This will index all the columns into OpenSearch by default. Every column that you don't want to index into OpenSearch has to be explicitly mentioned in the
column.exclude.list
(Accepts regex). For example PII fields like name, phone number, address, etc. As a general rule, fields that are not required for dashboards must be excluded explicitly.Debezium PostgreSQL Connector Reference.
OpenSearch connector creation
Each json file in the
opensearch-connectors
folder will be considered a connector. Create one connector file for each table with the following content:Replace
name
with the appropriate table names.Replace
topics
field with the name of table.To stop capturing changes to records and to maintain only the the latest data of a record on OpenSearch, set
key.ignore
to false. With this config, whenever there is a change to a record on the registry, the same change will be applied to the data on OpenSearch (rather than creating a new entry for the change.).Also if you want the data to get deleted from OpenSearch, when the record is deleted on the Registry, set
behavior.on.null.values
todelete
.
After the base file is configured, you can now add transformations to your connector at the end of the file (denoted by
...
in the above example). Each transformation (SMT) will apply some change to the data or a particular field from the table, before pushing the entry to OpenSearch.Add the following transformations to your connector based on the data available in the table.
For every Datetime field / Date field in the table add the following transform.
At the end of all the transformations, add a TimestampSelector transform, which creates a new
@timestamp_gen
field whose value can be selected as any of the available Datetime fields in the table. This will be useful while creating a Dashboard on OpenSearch, where we can use this new@timestamp_gen
field as the IndexPattern timestamp.If you want to pull data from another table (which is already indexed into OpenSearch) into this table that the connector is pointing to, use the DynamicNewField transform. For example;
g2p_program_membership
contains the beneficiary list. But the demographic info of the beneficiary is present inres_partner
table. Say you want to pull gender, and address of the beneficiary, and name of the program that the beneficiary is part of, then create two transforms like this:After configuring all the transforms, add the names of all transforms, in the order in which they have to be applied, in the
transforms
field.
Each $
in the json file will be treated as an environment variable. Environment variables will be automatically picked up during installation. If you want to use a dollar in the file and not parse it as env variable during installation, replace your $
with ${dollar}
.
For more info on basic connector configuration, refer to Apacha Kafka Connect.
For detailed transform configuration, refer to Apache Kafka Connect Transformations doc.
For a list of all available SMTs and their configs, refer to Reporting Kafka Connect Transforms.
OpenSearch dashboard creation
Refer to OpenSearch Dashboard Creation Guide.
Last updated