Purpose of this component of the ER-Demo is populate a relational PostgreSQL database (with a de-normalized schema) that serves as a datawarehouse. This datawarehouse is then exposed via a GrafanaDashboards
This component is written using Quarkus. This component listens on the following AMQ Streams topics in order:
-
topic-mission-command : Initial command (with processInstanceId) from process-service to create a mission
-
topic-incident-event : IncidentUpdatedEvent (produced by incident-service) with number of people actually picked up
-
topic-responder-location-update : Consumes a location-update message (with status of PICKEDUP and lat/lon of pickup location)
-
topic-mission-event : Mission Completed message from mission-service
-
Clone this project:
git clone https://github.com/Emergency-Response-Demo/datawarehouse $ER_DEMO_HOME/datawarehouse
-
Build:
cd $ER_DEMO_HOME/datawarehouse mvn clean install -DskipTests
The following assumes that you’ve already built and deployed all the other components of ER-Demo to your OCP4 environment.
cd $ER_DEMO_HOME/install/ansible
ansible-playbook -i inventories/inventory playbooks/datawarehouse.yml \
-e namespace_sso=user-sso \
-e project_admin=$ERDEMO_USER \
-e project_name=$ERDEMO_USER-er-demo \
-e install_datawarehouse=True
-
Build and push to Quay
cd $ER_DEMO_HOME/datawarehouse podman build -f src/main/docker/Dockerfile.jvm -t quay.io/emergencyresponsedemo/datawarehouse:2.1 . podman push quay.io/emergencyresponsedemo/datawarehouse:2.1
-
Refresh IS
oc import-image datawarehouse --all=true --from=quay.io/jbride/datawarehouse --confirm
-
OCP resources
cd $ER_DEMO_HOME/datawarehouse vi src/main/resources/application.properties # Review runtime properties oc create configmap datawarehouse --from-file=src/main/resources/application.properties oc create serviceaccount datawarehouse oc policy add-role-to-user view system:serviceaccount:$(oc project --short):datawarehouse oc apply -f src/main/ocp/service/datawarehouse-imagestream.yml oc apply -f src/main/ocp/service/datawarehouse-service.yml
-
In terminal 1; start-up zookeeper, kafka and postgresql
podman-compose -f etc/docker-compose.yaml up
-
In terminal 2
podman pod ps # check on status of previously started pod psql -h 127.0.01 -U naps -d datawarehouse -a -f ../sql-scripts/dw_database_ddl.sql # initialize postgresql less config/application.properties # review / edit properties that over-ride props in src/main/resources/application.properties mvn clean install -DskipTests \ && java -jar target/datawarehouse-*.*.*-runner.jar : build and start app mvn clean install -DskipTests \ && java -agentlib:jdwp=transport=dt_socket,server=y,suspend=y,address=8484 \ -jar target/datawarehouse-*.*.*-runner.jar : build and start app with debugger enabled : https://developers.redhat.com/blog/2017/12/19/debug-java-openshift-vscode-cdk/ java -jar target/datawarehouse-*.*.*-runner.jar > /tmp/dw.log 2>&1 & -
In terminal 3:
/opt/agile_int/kafka_2.12-2.4.0/bin/kafka-topics.sh --list --bootstrap-server localhost:9092 # List existing topics /opt/agile_int/kafka_2.12-2.4.0/bin/kafka-console-producer.sh \ --broker-list localhost:9092 \ --topic topic-mission-command < src/test/resources/CreateMissionCommand2.json # Send a CreateMissionCommand event to a kafka topic /opt/agile_int/kafka_2.12-2.4.0/bin/kafka-console-producer.sh \ --broker-list localhost:9092 \ --topic topic-mission-event < src/test/resources/MissionStartedEvent2.json # Send an MissionStarted event to a kafka topic /opt/agile_int/kafka_2.12-2.4.0/bin/kafka-console-producer.sh \ --broker-list localhost:9092 \ --topic topic-responder-location-update \ < src/test/resources/topicResponderLocationUpdate_PICKEDUP2.json # Send a ResponderLocationUpdate event to kafka topic /opt/agile_int/kafka_2.12-2.4.0/bin/kafka-console-producer.sh \ --broker-list localhost:9092 \ --topic topic-incident-event \ < src/test/resources/TopicIncidentEventPickedup2.json # Send an IncidentUpdateEvent to kafka topic /opt/agile_int/kafka_2.12-2.4.0/bin/kafka-console-producer.sh \ --broker-list localhost:9092 \ --topic topic-mission-event < src/test/resources/MissionCompletedEvent2.json # Send an MissionCompleted event to a kafka topic
-
Create psql_driver driver
class name: org.postgresql.Driver groupId: org.postgresql artifactId: postgresql version: 42.2.5
-
Create dw_postgresql datasource
connectionUrl: jdbc:postgresql://dw-postgresql.user3-er-demo.svc:5432/datawarehouse user: naps passwd: naps
-
Create erdemo_pam_datasource datasource
connectionUrl: jdbc:postgresql://user3-process-service-postgresql.user3-er-demo.svc:5432/user3-rhpam user: user3-rhpam passwd: user3-rhpam
-
Database queries
# psql user1-rhpam # \copy (select EXTRACT(EPOCH FROM log_date)*1000, nodeinstanceid, nodename, type from nodeinstancelog where processinstanceid=2 order by log_date ASC) TO '/tmp/incident_with_existing_responder.csv' CSV HEADER
From jboss-eap-modules:7.2.x-openjdk11 os-eap-datasource/1.0/added/launch/datasource-common.sh
- name: JAVA_OPTS_APPEND
value: -Dorg.uberfire.nio.git.ssh.algorithm=RSA -Dorg.kie.server.persistence.ds=java:jboss/datasources/erdemopam_postgresql
- name: DB_SERVICE_PREFIX_MAPPING
value: erdemopam-postgresql=ERPAM
- name: erdemopam-postgresql_SERVICE_HOST
value: user3-process-service-postgresql.user3-er-demo.svc
- name: erdemopam-postgresql_SERVICE_PORT
value: '5432'
- name: ERPAM_NONXA
value: "true"
- name: ERPAM_URL
value: jdbc:postgresql://user3-process-service-postgresql.user3-er-demo.svc:5432/user3-rhpam
- name: ERPAM_VALIDATION
value: "true"
- name: ERPAM_DATABASE
value: user3-rhpam
- name: ERPAM_DRIVER
value: postgresql
- name: ERPAM_PASSWORD
value: user3-rhpam
- name: ERPAM_USERNAME
value: user3-rhpam
Top Responders: - select now() as time, sum(numberrescued), responderfullname from missionreport group by responderfullname order by sum desc limit 5
Quickest Responses: - select (responderdistancetotal / responsetimesecondstotal * 3.6) as response_velocity_km_hr, responderfullname, incidentId, processinstanceid from MissionReport order by response_velocity_km_hr desc limit 10;