Integrating the backend service

How to link backend services with your SEP cluster for enhanced functionality

This guide provides a walkthrough for connecting various backend services databases to your SEP cluster (you will choose one method). These services are essential for managing and storing:

Query completion details and events
Cluster metrics
Data products
Built-in role-based access control
Managed statistics

Note that for the optimal functioning of SEP, integrating backend services is required. Be sure to check the infrastructure guide to check for the latest RDBMS source requirements.

Initial Steps

Begin by adding specific parameters to your sep-prod-setup.yaml file to activate the backend services. The required parameters are:

Property name	Description
`insights.jdbc.url`	The JDBC connection string for your chosen external database.
`insights.jdbc.user`	Database user credentials with full read-write access to the selected external database.
`insights.jdbc.password`	The corresponding password for the database user.
`insights.persistence-enabled`	Activates the query history functionality. Persistence must be explicitly enabled for Insights to utilize the stored data in the backend database. Defaults to `false`.
`insights.metrics-persistence-enabled`	Activates the usage metrics functionality. Defaults to `false`.
`insights.query-history.store-query-plan`	Toggles the storage of query plans in the database. Disabling this can significantly reduce storage needs if storing query plans is not required. Defaults to `true`.

The following examples illustrate how to add these backend services to the coordinator section of your sep-prod-setup.yaml file.

Using a Postgres database

Here's how to configure a Postgres database for backend services. Replace the placeholder values with your actual database details:

yaml

coordinator:
  additionalProperties: |
    insights.persistence-enabled=true
    insights.metrics-persistence-enabled=true
    insights.jdbc.url=jdbc:postgresql://<host>:<port>/<database>
    insights.jdbc.user=<user>
    insights.jdbc.password=<password>

Using an Oracle database

Similarly, for Oracle database integration, replace the placeholders with your specific database information:

yaml

coordinator:
  additionalProperties: |
    insights.persistence-enabled=true
    insights.metrics-persistence-enabled=true
    insights.jdbc.url=jdbc:oracle:thin:@<host>:<port>:<database>
    insights.jdbc.user=<user>
    insights.jdbc.password=<password>

Kerberos authentication

Oracle supports Kerberos authentication. The following parameters are needed for this setup:

Property name	Description
`insights.jdbc.kerberos.client.principal`	Identifies the Kerberos principal name for client authentication within the Kerberos system.
`insights.jdbc.kerberos.client.keytab`	Specifies the path to the keytab file containing the encrypted keys for the client principal.
`iinsights.jdbc.kerberos.config`	Directs to the Kerberos configuration file, detailing settings like KDC locations and domain realm mappings.

For implementation, modify your configuration as per this Kerberos example:

yaml

coordinator:
  additionalProperties: |
    insights.persistence-enabled=true
    insights.metrics-persistence-enabled=true
    insights.jdbc.url=jdbc:oracle:thin:@<host>:<port>:<database>
    insights.jdbc.authentication-type=KERBEROS
    insights.jdbc.kerberos.client.principal=example@STARBURSTDATA.COM
    insights.jdbc.kerberos.client.keytab=/etc/kerberos/example.keytab
    insights.jdbc.kerberos.config=/etc/kerberos/krb5.conf

Using a MySQL database

Here's the configuration for integrating a MySQL database as your backend service. Ensure to replace the placeholders with actual database details:

yaml

coordinator:
  additionalProperties: |
    insights.persistence-enabled=true
    insights.metrics-persistence-enabled=true
    insights.jdbc.url=jdbc:mysql://<host>:<port>/<database>?sessionVariables=sql_mode=ANSI
    insights.jdbc.user=<user>
    insights.jdbc.password=<password>

For MySQL databases, set sql_mode=ANSI in the insights.jdbc.url parameter. The configured user should have adequate privileges for table creation and data insertion.

IAM authentication

MySQL also supports IAM authentication. Here is an example configuration:

yaml

coordinator:
  additionalProperties: |
    insights.persistence-enabled=true
    insights.metrics-persistence-enabled=true
    insights.jdbc.authentication-type=AWS_IAM
    insights.jdbc.connection-user=db_user
    insights.jdbc.aws.region-name=us-east-2
    insights.jdbc.aws.iam-role=${ENV:RDS_ROLE_ARN}
    insights.jdbc.aws.external-id=for_product_test
    insights.jdbc.aws.token-expiration-timeout=1s

Conclusion

Your SEP cluster should now be successfully connected to the backend services. A good way to verify this is by accessing the SEP UI and checking the Query overview or Cluster history tabs. The Query overview might display no data while Cluster history should present an overview of your worker nodes' activity.