Data Platform CX

Prerequisites for EF Data Platform Deployment

Fully Qualified Domain Name (FQDN)
A dedicated FQDN is required for CX, EF Data Platform (Transflux), to ensure proper routing and secure communication.

Database Setup

For MYSQL

Edit the file kubernetes/pre-deployment/reportingConnector/dbScripts/dbcreation/_historical_reports_db_creation_script_MySQL.sql and update the following, then execute

CODE

-- ----------------------------------------------------------------------------
-- NOTE : CX-4.9 creation script  
-- ----------------------------------------------------------------------------
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `<tenant-name>`;
USE `<tenant-name>`;

-- In case of username other then 'root', update the following with current database username (at line 396)

CREATE DEFINER=<username>@%  PROCEDURE InsertWeekDays(IN start_year INT, IN end_year INT ,IN week_start_day VARCHAR(10))

For MSSQL

Edit the file kubernetes/pre-deployment/reportingConnector/dbScripts/dbcreation/_historical_reports_db_creation_script_MSSQL.sql and update the following then execute

CODE

USE master
GO
IF NOT EXISTS (
    SELECT [name]
        FROM sys.databases
        WHERE [name] = N'<tenant-name>'
)
CREATE DATABASE "<tenant-name>"
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE "<tenant-name>"
GO

Ensure that the executing user has sufficient privileges to create databases and tables.

Follow this guide to create a database

Resource Requirements (For EF Cloud):
- Minimum CPU: 4 cores
- Minimum Memory: 12 GB RAM
- These resources are essential for optimal performance of the EF Data Platform components during data processing and ETL operations.

Deployment

Clone CX-Transflux Repository

CODE

git clone -b 5.0 https://efcx:RecRpsuH34yqp56YRFUb@gitlab.expertflow.com/cim/transflux.git transflux

CODE

cd transflux

Add the Expertflow Helm charts repository.

CODE

helm repo add expertflow https://expertflow.github.io/charts

Update the charts repository

CODE

helm repo update expertflow

Create a folder to save the Helm chart’s values

CODE

mkdir helm-values

Customise the deployment by creating the custom-values.yaml file and add the custom configurations as per the requirements.

CODE

vi helm-values/cx-transflux-custom-values.yaml

Use the following command to see the default values.yaml

CODE

helm show values expertflow/transflux --version 5.0

Open the file helm-values/cx-transflux-custom-values.yaml and edit it according to the given information, which is required for the CX Transflux to work properly.

The airflow metadata database is already created when PostgreSQL is deployed

Value	Updated Value
ingressRouter	Dedicated Fully Qualified Domain Name (FQDN)
tag	`5.0`
MONGODB_PASSWORD	Update the local MongoDB password when using a non-default password
AIRFLOW__CORE__SQL_ALCHEMY_CONN	Update the local PostgreSQL password when using a non-default password as below postgresql+psycopg2://sa:<your-non-default-password>@ef-postgresql.ef-external.svc:5432/airflow?sslmode=verify-ca&sslrootcert=/postgresql/ca.crt

Below is the sample snippet after editing helm-values/cx-transflux-custom-values.yaml

CODE

global:
  ingressRouter: "transflux-rm-02.expertflow.com"
image:
  tag: "5.0"

Use the following to export TLS certificates from the MongoDB pod. The certificates will be exported to /tmp/mongodb_certs

CODE

mkdir /tmp/mongodb_certs
CERTFILES=($(kubectl get secret mongo-mongodb-ca -n ef-external -o go-template='{{range $k,$v := .data}}{{$k}}{{"\n"}}{{end}}'))
for f in ${CERTFILES[*]}; do   kubectl get secret mongo-mongodb-ca  -n ef-external -o go-template='{{range $k,$v := .data}}{{ if eq $k "'$f'"}}{{$v  | base64decode}}{{end}}{{end}}' > /tmp/mongodb_certs/${f} 2>/dev/null; done

Create a directory for TLS certificates

CODE

mkdir -p certificates/mongo_certs
mkdir -p certificates/mysql_certs

Copy all the certificate files present in /tmp/mongodb_certs directory and place them in the newly created directory certificates/mongo_certs in the transflux directory. These certificate files should include the following:

mongodb-ca-key.pem
mongodb-ca-cert.pem
client-pem.pem

For a non-SSL target database, the certificates/mysql_certs directory will remain empty, but still, the secrets will be created as per the given command

Place all certificate files in the certificates/mysql_certs directory and create a ConfigMap for MySQL certificates to enable TLS encryption. The certificates should include the following files:

ca.pem
client-cert.pem
client-key.pem

CODE

kubectl -n expertflow create secret generic  ef-transflux-mysql-certs-secret  --from-file=certificates/mysql_certs

For On-Prem (Single tenant) Deployment

Edit the file transflux/config/tenants.yaml and configure the following

By default, the single tenant’s name is expertflow
FQDN_URL in api
TARGET_TYPE (mysql or mssql) as per the dedicated target database

For selected target type, update the dedicated MYSQL or MSSQL credentials

CODE

tenants:
  expertflow:       
    
    mongodb:

      SOURCE_HOST: "mongo-mongodb.ef-external.svc"
      SOURCE_PORT: "27017"
      SOURCE_USERNAME: "root"
      SOURCE_PASSWORD: "Expertflow123"
      SOURCE_TLS_ENABLED: true
      SOURCE_DATABASE: "expertflow"

    postgre:

      SOURCE_HOST: "ef-postgresql.ef-external.svc"
      SOURCE_PORT: "5432"
      SOURCE_USERNAME: "sa"
      SOURCE_PASSWORD: "Expertflow123"
      SOURCE_DATABASE: "qm_db"
    
    api:

      FQDN_URL: "<FQDN URL>"
      REALM: "expertflow"       

    TARGET_TYPE: "mysql"

    MYSQL:
      TARGET_HOST: "192.168.2.18"
      TARGET_PORT: "3306"
      TARGET_USERNAME: "monty"
      TARGET_PASSWORD: "Expertflow#143"
      TARGET_SSL_ENABLED: false
      TARGET_DATABASE: "hold_db"

    MSSQL:
      TARGET_HOST: "192.168.1.77"
      TARGET_PORT: "1433"
      TARGET_USERNAME: "sa"
      TARGET_PASSWORD: "Expertflow464"
      TARGET_SSL_ENABLED: false
      TARGET_DATABASE: "hold_db"

For Cloud (Multi-tenant) Deployment

In order to register new tenants, replicate the previous tenant configuration and append the new settings afterward, set the TARGET_TYPE as per the dedicated target database, and configure the database credentials accordingly. You can also update the existing tenant's information as per the following setup

CODE

  <tenant_name>:

    mongodb:
    
      SOURCE_HOST: "mongo-mongodb.ef-external.svc"
      SOURCE_PORT: "27017"
      SOURCE_USERNAME: "root"
      SOURCE_PASSWORD: "Expertflow123"
      SOURCE_TLS_ENABLED: true
      SOURCE_DATABASE: "<tenant_name>"

    postgre:

      SOURCE_HOST: "ef-postgresql.ef-external.svc"
      SOURCE_PORT: "5432"
      SOURCE_USERNAME: "sa"
      SOURCE_PASSWORD: "Expertflow123"
      SOURCE_DATABASE: "<tenant_name>"

    api:
    
      FQDN_URL: "https://<tenant_name>.expertflow.com"
      REALM: "<tenant_name>"

    TARGET_TYPE: "mysql"

    MYSQL:
      TARGET_HOST: "82.208.20.221"
      TARGET_PORT: "30801"
      TARGET_USERNAME: "root"
      TARGET_PASSWORD: "Expertflow123$#"
      TARGET_SSL_ENABLED: false
      TARGET_DATABASE: "<tenant_name>"

    MSSQL:
      TARGET_HOST: "192.168.1.77"
      TARGET_PORT: "1433"
      TARGET_USERNAME: "sa"
      TARGET_PASSWORD: "Expertflow464"
      TARGET_SSL_ENABLED: false
      TARGET_DATABASE: "<tenant_name>"

For Cisco Sync Job Configuration

Edit the file transflux/config/qm_cisco_team_sync_config.yaml and update the endpoint with FQDN on which cisco sync job is configured

CODE

endpoint: "https://{FQDN}/cisco-sync-service/api/v1/sync"

Create configuration ConfigMaps for CX-Transflux pipelines with multi-tenancy configurations.

CODE

kubectl -n expertflow create configmap ef-transflux-config-cm --from-file=config

Create configuration ConfMaps for dbt_schema in transflux

CODE

kubectl -n expertflow create configmap ef-transflux-dbt-schema-cm --from-file=dbt_schema

Finally, deploy CX-Transflux.

BASH

helm upgrade --install --namespace expertflow   --set global.efCxReleaseName="ef-cx"  cx-transflux --debug --values helm-values/cx-transflux-custom-values.yaml  expertflow/transflux --version 5.0

Follow the User Manual to control pipelines from the Data Platform