Amazon RDS

JVM AGENT

Item Type Support

SIGNALS SCALARS CONDITION

Overview

The SQL Connector enables Seeq to access data from Amazon RDS Instances.

Prerequisites

You must gather some information to configure a connection to your Amazon RDS Instance.

Connecting to Amazon RDS Instances

When connecting to RDS Instances, you will most likely have to include a new certificate into the Java Keystore.

You will need to get the certificate from here: https://s3.amazonaws.com/rds-downloads. Download the pem file specific to your region/deployment. It has been observed that by default rds-ca-2019-root.pem works for all regions (documented here:https://lightsail.aws.amazon.com/ls/docs/en_us/articles/amazon-lightsail-download-ssl-certificate-for-managed-database)

To add the certificate to cacerts you will use the following command in a command prompt with administrator access on the Seeq Remote Agent Machine.

keytool -import -trustcacerts -file "c:\rds-ca-2019-root.pem" -alias mydomain -keystore "C:\Program Files\Seeq Server\jdk\files\lib\security\cacerts

Configuration

The SQL connector supports connecting to the following RDS Instance types:

• Amazon Aurora with MySQL Compatibility - MySQL

• Amazon Aurora with PostgreSQL Compatibility - Postgres

• MySQL

• Postgres

• Oracle

• Microsoft SQL Server

Known Issues

There are no known issues for the SQL Connector. Please report any issues you find to our support portal.

Troubleshooting

A couple of errors that you may encounter are:

String-valued samples are prohibited in numeric-valued signal

If the y-axis value of the signal is a string, then the Value Unit Of Measure property is required and must be set to "string". See Example 2. 

Since the Value Unit Of Measure is different for string and numeric signals, it may be easiest to write one query definition for the numeric signals and write another for the string signals. Alternatively, the Value Unit Of Measure property could be set according to an SQL IF statement similar to the technique used in Example 13.

Samples must be ordered by their keys

If this is occurring when trending near the daylight savings transition, this is an indication that the TimeZone is not configured properly. For example, if TimeZone is set to "America/Los_Angeles", this means that the timestamp data in the SQL table was recorded using "America/Los_Angeles" time (Pacific) which observes daylight savings. During the spring daylight savings transition, time skips from 01:59:59.9 to 03:00:00.0 which means that the 02:00 hour doesn't exist and therefore there should be no data in the SQL table during that 02:00 hour. Any data in the 02:00 hour is interpreted as being in the 03:00 hour. If there is also data in the 03:00 hour, the samples will be out of order. 

If data exists in the 02:00 hour, it must mean it was either recorded in error or was recorded in a time zone that doesn't observe daylight savings such as UTC or a constant offset from UTC.

For more information, see the TimeZone field in the Configuration section above.

If you are running into issues with connecting to or access data from SQL Connector, view our guide for troubleshooting datasource issues.

Performance considerations

• Ensure you have indexes on columns that are used for filtering in a WHERE clause

• Ensure you are using timestamp types for your time columns vs having your timestamps as strings or the results of concatenations.

View our guide on optimizing datasource performance for general guidance.