SQL Connection Configuration

See Connector Setup and Configuration for general configuration guidance.

Consult the troubleshooting article for connectors for further guidance if you have any problems after configuration.

Database Connection Configuration

To enable the SQL connector, log in to the machine hosting the Seeq Server and locate the Seeq data folder

Connector Version

File Location

Version line of file

Connector Version

File Location

Version line of file

SQL Connector V1

configuration\link\SQL Connector V1 Legacy.json

"Version" : "com.seeq.link.connectors.sql.config.SqlConnectorV1LegacyConfigV1",

SQL Connector V2

configuration\link\SQL Connector V2.json

"Version" : "com.seeq.link.connectors.sql2.config.SqlConnectorV2ConfigV1",

Using the table above, locate the existing file for your connector and open the configuration file in Notepad or any other preferred text editor. Note that the 2nd line of the file, the Version line, will differ depending on which connector and release you are using. The other configuration fields shown in this article are the same across the connectors and releases.

SQL Configuration File
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 { "Version" : "com.seeq.link.connectors.sql.config.SqlConnectorV1LegacyConfigV1", "Connections" : [ { "Name" : "MyData Folder", "Type" : "CSV", "Location" : "mydata", "Hostname" : null, "Port" : 0, "DatabaseName" : null, "Id" : "94ee02f2-bc29-4c4a-9198-eadb85d5bca1", "Username" : null, "Password" : null, "Enabled" : true, "UseWindowsAuth" : false, "InitialSql" : null, "TimeZone" : null, "PrintRows" : false }, { "Name" : "MySQL", "Type" : "MYSQL", "Location" : null, "Hostname" : "localhost", "Port" : 3306, "DatabaseName" : "test", "Id" : "3a832e44-5f31-4514-be2a-f617a8397362", "Username" : "root", "Password" : "$hFS23c9dk", "Enabled" : true, "UseWindowsAuth" : false, "UseSSL" : false, "InitialSql" : null, "TimeZone" : "America/New_York", "PrintRows" : false }, { "Name" : "Microsoft SQL Server", "Type" : "MSSQLSERVER", "Location" : null, "Hostname" : "localhost", "Port" : 1433, "DatabaseName" : "Maintenance System", "Id" : "b10e5430-ee9e-469d-8962-ee209d3c167e", "Username" : null, "Password" : null, "Enabled" : true, "UseWindowsAuth" : true, "InitialSql" : null, "TimeZone" : null, "PrintRows" : false }, { "Name" : "Oracle", "Type" : "ORACLE", "Location" : null, "Hostname" : "localhost", "Port" : 1521, "DatabaseName" : "XE", "Id" : "a8a29dc6-2828-4e04-8ceb-24e6e6eb848d", "Username" : "work", "Password" : "mdb-fWcS5B", "Enabled" : true, "UseWindowsAuth" : false, "InitialSql" : null, "TimeZone" : "+00:00", "PrintRows" : false }, { "Name" : "Postgres", "Type" : "POSTGRES", "Location" : null, "Hostname" : "localhost", "Port" : 5432, "DatabaseName" : "test", "Id" : "6031622a-5bc2-4f53-9f2d-bf5a1c7440dc", "Username" : "work", "Password" : "work", "Enabled" : true, "UseWindowsAuth" : false, "InitialSql" : null, "TimeZone" : null, "PrintRows" : false }, { "Name" : "Amazon Redshift", "Type" : "REDSHIFT", "Location" : null, "Hostname" : "localhost", "Port" : 5439, "DatabaseName" : "dev", "Id" : "04eb30af-91dd-4d62-b503-64e955e4385f", "Username" : "master", "Password" : "SecretPa33word", "Enabled" : true, "UseWindowsAuth" : false, "InitialSql" : null, "TimeZone" : null, "PrintRows" : false },{ "Name" : "Vertica", "Id" : "c5232d0a-039c-4d1e-b1af-4af6837c4bce", "Enabled" : true, "Type" : "VERTICA", "Location" : null, "Hostname" : "localhost", "Port" : 5433, "DatabaseName" : "test", "Username" : "verticauser", "Password" : "Verticauserpw", "UseWindowsAuth" : false, "InitialSql" : null, "TimeZone" : "UTC", "PrintRows" : false }, { "Name" : "SAP HANA", "Id" : "d3672d0a-039c-4d1e-b1af-4af6837c4bdd", "Enabled" : true, "Type" : "SAPHANA", "Location" : null, "Hostname" : "localhost", "Port" : 39013, "DatabaseName" : null, "Username" : "saphana_user", "Password" : "saphana_pw", "UseWindowsAuth" : false, "InitialSql" : null, "TimeZone" : "UTC", "PrintRows" : false }, { "Name" : "Athena", "Id" : "dc728d73-8830-4446-ba09-0c6603dd2e47", "Enabled" : true, "Type" : "ATHENA", "Location" : null, "Hostname" : null, "Port" : null, "DatabaseName" : null, "Username" : "TESTACCESSKEY", "Password" : "123432/2sdf224t545dk", "UseWindowsAuth" : false, "AwsRegion" : "us-east-1", "S3OutputLocation" : "s3://path-to-bucket/queryResults/", "InitialSql" : null, "TimeZone" : "America/Los_Angeles", "PrintRows" : false, }, { "Name" : "Snowflake", "Id" : "0b7fa2e8-0cdd-4c2b-a1ff-75f2df196a1f", "Enabled" : true, "Type" : "SNOWFLAKE", "Location" : null, "Hostname" : "testaccount.us-east-1.snowflakecomputing.com", "Port" : null, "DatabaseName" : "testdb", "Username" : "demouser", "Password" : "demopassword", "UseWindowsAuth" : false, "Account" : "testaccount", "Warehouse" : "test_wh", "Schema": "dbo", "InitialSql" : null, "TimeZone" : "America/Los_Angeles", "PrintRows" : false, } ] }


If you are storing username / passwords in the configuration file, consider securing the files appropriately by following Security Configuration / Configuration File Authorization.

Postgres

To configure a Postgres connection, you will be modifying the connection pattern where the Type field is POSTGRES. The other entries can be ignored or removed. Update the following fields:

  1. Name:  What you want displayed in Seeq Workbench when searching for items. This is the name that will be displayed in the Connections table.

  2. Hostname: The name or IP address of the machine that is hosting the Postgres database. If your hostname is of the form "abc\def", you'll have to escape the backslash like so: "abc\\def".

  3. Port: 5432 is the Postgres default but it is possible that your Postgres was configured to use a different port.

  4. Database Name: The database name.

  5. Set the Username and Password fields appropriately.

  6. Enabled: Set to true to enable this connection.

  7. UseWindowsAuth:  Set to false.

  8. InitialSql: Optional.  An SQL query/command that will be run once upon establishing a connection.

  9. TimeZone: The time zone to use for Postgres DATE and TIMESTAMP columns types that have no time zone information of their own.  This field does not affect the data from columns of type TIMESTAMP WITH TIME ZONE. See Time Zone for more information.

  10. PrintRows: Default is false. If true, the rows from the SQL query will be printed to the jvm-link log.  This is for debugging purposes only and should not be set to true in general as it could significantly impact performance.

Leave all other fields with their default values.

TimescaleDB

TimescaleDB is an extension to Postgres. Connect to TimescaleDB just as you would a Postgres database.

Oracle

Seeq uses the JDBC thin driver and does not need any additional Oracle Client libraries installed on the Seeq machine. However, we have observed faster data access when a Remote Seeq Agent is installed on the same machine as the Oracle database.

When the database you are connecting to is an Oracle Container Database CDB or other database that requires a TNS connection the customer must have the parameter "USE_SID_AS_SERVICE_listener=on" configured on the database server listener.  Setting this parameter to "on" instructs the listener to use the SID in the JDBC thin connect descriptor as a service name allowing connection to the specified database.

To configure an Oracle connection, you will be modifying the connection pattern where the Type field is ORACLE. The other entries can be ignored or removed. Update the following fields:

  1. Name:  What you want displayed in Seeq Workbench when searching for items. This is the name that will be displayed in the Connections table.

  2. Hostname: The name or IP address of the machine that is hosting the Oracle database. If your hostname is of the form "abc\def", you'll have to escape the backslash like so: "abc\\def". When using a hostname, be sure to use the actual hostname and not a TNS alias name.

  3. Port: 1521 is the Oracle default but it is possible that your Oracle was configured to use a different port.

  4. Database Name: The Oracle service name (not the service id, SID) which is likely "xe" for Oracle Express and "orcl" for Oracle Standard. One of the following SQL commands may help you determine the service name:

    • select * from global_name;

    • select value from v$parameter where name='service_names';   (If this query returns the error "table or view does not exist", you don't have access to the data dictionary views.  Ask your DBA for access, or better yet for the result of this query.)

  5. Username:  Sometimes Oracle refers to this as the schema.

  6. Password:  The password for that username/schema.

  7. Enabled: Set to true to enable this connection.

  8. UseWindowsAuth:  Set to false.

  9. InitialSql: Optional.  An SQL query/command that will be run once upon establishing a connection.

  10. TimeZone: The time zone to use for Oracle DATE and TIMESTAMP columns types that have no time zone information of their own.  This field does not affect the data from columns of type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE. See Time Zone for more information.

  11. PrintRows: Default is false. If true, the rows from the SQL query will be printed to the jvm-link log.  This is for debugging purposes only and should not be set to true in general as it could significantly impact performance.

Leave all other fields with their default values.

If using LDAP, hostname, port, and database name will look something like this:

LDAP example
1 2 3 "Hostname" : "ldap://ldap.acme.com", "Port" : 389, "DatabaseName" : "sales,cn=OracleContext,dc=com",

Microsoft SQL Server

To configure a Microsoft SQL Server connection, you will be modifying the connection pattern where the Type field is MSSQLSERVER. The other entries can be ignored or removed. Take the following steps:

  1. Name:  What you want displayed in Seeq Workbench when searching for items. This is the name that will be displayed in the Connections table.

  2. Hostname: The name or IP address of the machine that is hosting Microsoft SQL Server. If your hostname is of the form "abc\def", you'll have to escape the backslash like so: "abc\\def"

  3. Port: 1433 is the Microsoft SQL Server default but it is possible that your Microsoft SQL Server was configured to use a different port. This article has tips (Method 3 in particular) for discovering the port being used.

  4. Database Name: The database name.

  5. Configure the authentication method appropriately:

    1. If you wish to use Windows Integrated Authentication, which utilizes a Windows domain account for authentication, you must run Seeq Server as a Windows Service. Consult Secure Configuration Options (SSL/TLS) to configure the service to use an appropriate domain account. Set the UseWindowsAuth field in the configuration file to true. Ignore the Username and Password fields in the configuration file.

    2. If you wish to use SQL Authentication, set the Username and Password fields appropriately. Make sure the UseWindowsAuth field is set to false.

  6. Enabled: Set to true to enable this connection.

  7. InitialSql: Optional.  An SQL query/command that will be run once upon establishing a connection.

  8. TimeZone: The time zone to use for Microsoft SQL Server DATE, SMALLDATETIME, DATETIME, and DATETIME2 columns types that have no time zone information of their own.  This field does not affect the data from columns of type DATETIMEOFFSET. See Time Zone for more information.

  9. PrintRows: Default is false. If true, the rows from the SQL query will be printed to the jvm-link log.  This is for debugging purposes only and should not be set to true in general as it could significantly impact performance.

Leave all other fields with their default values.

MySQL

In order to connect to a MySQL database, you must download and install the Oracle MySQL Connector/J driver.

  1. Using the Seeq Launcher user interface, shut down Seeq Server.

  2. Download the latest 5.1.x version of the MySQL JDBC from https://downloads.mysql.com/archives/c-j/. The drop-down text box near the top will list version possibilities. Choose either the tar.gz or zip file.

  3. Extract the contents of the download, and copy the .JAR file inside (e.g. mysql-connector-java-5.1.47-bin.jar) into the plugins\lib folder within the Seeq data folder.

  4. Using the Seeq Launcher user interface, start Seeq Server again.

If you forget to install the MySQL Connector/J driver, the MySQL connection will fail and you will see errors in the jvm-link logs related to loading the mysql-connector jar file.

To configure a MySQL connection, you will be modifying the connection pattern where the Type field is MYSQL. The other entries can be ignored or removed. Take the following steps:

  1. Name:  What you want displayed in Seeq Workbench when searching for items. This is the name that will be displayed in the Connections table.

  2. Hostname: The name or IP address of the machine that is hosting MySql. If your hostname is of the form "abc\def", you'll have to escape the backslash like so: "abc\\def".

  3. Port: 3306 is the MySql default but it is possible that your MySql was configured to use a different port.

  4. Database Name: The database name.

  5. Set the Username and Password fields appropriately.

  6. Enabled: Set to true to enable this connection.

  7. InitialSql: Optional.  An SQL query/command that will be run once upon establishing a connection.

  8. TimeZone: The time zone to use for MySql YEAR, DATE, and DATETIME columns types that have no time zone information of their own. This field does not affect the data from columns of type TIMESTAMP. See Time Zone for more information.

  9. PrintRows: Default is false. If true, the rows from the SQL query will be printed to the jvm-link log. This is for debugging purposes only and should not be set to true in general as it could significantly impact performance.

  10. UseSSL: Optional. If true, the connection will use the SSL configuration as set up for the MySQL server and the JDBC client. If false, does not use SSL and will suppress SSL requirement errors found in newer MySQL versions.

Leave all other fields with their default values.

Amazon Redshift

In order to connect to Amazon Redshift, you must download and install the Amazon Redshift JDBC driver.

  1. Using the Seeq Launcher user interface, shut down Seeq Server.

  2. Download the Amazon Redshift JDBC 4.2-compatible driver version 1.2.15.1025 from https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/1.2.15.1025/RedshiftJDBC42-1.2.15.1025.jar. If that driver version no longer exists, download the newest JDBC 4.2-compatible driver from https://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html.

  3. Place the downloaded .JAR file in the plugins\lib folder within the Seeq data folder.

  4. Using the Seeq Launcher user interface, start Seeq Server again.

If you forget to install the Amazon Redshift JDBC driver, all Redshift connections will fail and you will see errors in the jvm-link logs related to loading the Redshift jar file.

To configure an Amazon Redshift connection, you will be modifying the connection pattern where the Type field is REDSHIFT. The other entries can be ignored or removed. Update the following fields:

  1. Name:  What you want displayed in Seeq Workbench when searching for items. This is the name that will be displayed in the Connections table.

  2. Hostname: The name or IP address of the Redshift cluster.

  3. Port: The port number to use when connecting to the Redshift cluster. 5439 is the default Redshift port.

  4. Database Name: The database name.

  5. Set the Username and Password fields appropriately.

  6. Enabled: Set to true to enable this connection.

  7. UseWindowsAuth:  Set to false.

  8. InitialSql: Optional.  An SQL query/command that will be run once upon establishing a connection.

  9. TimeZone: The time zone to use for Redshift DATE and TIMESTAMP columns types that have no time zone information of their own.  This field does not affect the data from columns of type TIMESTAMP WITH TIME ZONE. See Time Zone for more information.

  10. PrintRows: Default is false. If true, the rows from the SQL query will be printed to the jvm-link log.  This is for debugging purposes only and should not be set to true in general as it could significantly impact performance.

Leave all other fields with their default values.

Vertica

In order to connect to Vertica, you must download and install the Vertica JDBC driver.

  1. Using the Seeq Launcher user interface, shut down Seeq Server.

  2. Download the Vertica JDBC client driver from Vertica Client Drivers

  3. Place the downloaded .JAR file in the plugins\lib folder within the Seeq data folder.

  4. Using the Seeq Launcher user interface, start Seeq Server again.

If you forget to install the Vertica JDBC driver, all Vertica connections will fail and you will see errors in the jvm-link logs related to loading the Vertica jar file.

To configure an Vertica connection, you will be modifying the connection pattern where the Type field is VERTICA. The other entries can be ignored or removed. Update the following fields:

  1. Name:  What you want displayed in Seeq Workbench when searching for items. This is the name that will be displayed in the Connections table.

  2. Hostname: The name or IP address of the Vertica DB cluster

  3. Set the Username and Password fields appropriately.

  4. Enabled: Set to true to enable this connection.

  5. UseWindowsAuth:  Set to false.

  6. InitialSql: Optional.  An SQL query/command that will be run once upon establishing a connection.

  7. TimeZone: The time zone to use for Vertica DATE and TIMESTAMP columns types that have no time zone information of their own.  This field does not affect the data from columns of type TIMESTAMP WITH TIME ZONE. See Time Zone for more information.

  8. PrintRows: Default is false. If true, the rows from the SQL query will be printed to the jvm-link log.  This is for debugging purposes only and should not be set to true in general as it could significantly impact performance.

Leave all other fields with their default values.

Athena (R21.0.44.0+)

Depending on the Athena configuration, performance can vary

To configure an Athena connection, you will be modifying the connection pattern where the Type field is ATHENA. The other entries can be ignored or removed. Update the following fields:

  1. Name:  What you want displayed in Seeq Workbench when searching for items. This is the name that will be displayed in the Connections table.

  2. Username: Set this to your AWS access key.

  3. Password: Set this to your AWS secret key.

  4. Enabled: Set to true to enable this connection.

  5. InitialSql: Optional.  An SQL query/command that will be run once upon establishing a connection.

  6. TimeZone: The time zone to use for Athena TIMESTAMP column types that have no time zone information of their own.  This field does not affect the data from columns of type TIMESTAMP. See Time Zone for more information.

  7. PrintRows: Default is false. If true, the rows from the SQL query will be printed to the jvm-link log.  This is for debugging purposes only and should not be set to true in general as it could significantly impact performance.

  8. AWSRegion: The region of your S3 bucket where your data resides.

  9. S3OutputLocation: The bucket in S3 where you want your output queries to go.

Leave all other fields with their default values.

Snowflake (R22.0.50.0+)

Seeq uses the JDBC driver and does not need any additional Snowflake libraries installed on the Seeq machine. 

To configure Snowflake connection, you will be modifying the connection pattern where the Type field is SNOWFLAKE. The other entries can be ignored or removed. Update the following fields:

  1. Name:  What you want displayed in Seeq Workbench when searching for items. This is the name that will be displayed in the Connections table.

  2. Hostname: The URL provided by Snowflake for data access.

  3. Database Name: Similar to MS SQL, the “Database” property is optional. It can be provided as part of connector configuration or be defined as part of fully qualified table name in SELECT statement. 

  4. Set the Username and Password fields appropriately

  5. Account:  Set this property if specific Snowflake edition requires account in connection string. In general, the account can be obtained from Url specified in Hostname as sub-string before data-center region name.

  6. Warehouse: Set the name of the warehouse to service the query requests. The warehouse is required to perform any SELECT query. Although, there are two ways to provide it: either in SQL Connector V2 configuration or as part of the query.

  7. Schema: Set the schema name for the query requests. The "Schema" property is optional. It can be provided as part of connector configuration or be defined as part of table name definition in SELECT statement.

  8. Enabled: Set to true to enable this connection.

  9. UseWindowsAuth:  Set to false.

  10. InitialSql: Optional.  An SQL query/command that will be run once upon establishing a connection.

  11. TimeZone: The time zone to use for Snowflake DATE, DATETIME, TIMESTAMP and TIMESTAMP_NTZ columns types that have no time zone information of their own.  This field does not affect the data from columns of type TIMESTAMP_TZ or TIMESTAMP_LTZ. See Time Zone for more information.

  12. PrintRows: Default is false. If true, the rows from the SQL query will be printed to the jvm-link log.  This is for debugging purposes only and should not be set to true in general as it could significantly impact performance.

Leave all other fields with their default values.

 

SAP HANA (R54+)

Seeq uses the JDBC driver provided by SAP HANA and does not need any additional libraries installed on the Seeq machine. 

To configure SAP HANA connection, you will be modifying the connection pattern where the Type field is SAPHANA. The other entries can be ignored or removed. Update the following fields:

  1. Name:  What you want displayed in Seeq Workbench when searching for items. This is the name that will be displayed in the Connections table.

  2. Hostname: The URL for the SAP HANA database server.

  3. Port: The port used by the SAP HANA database for connection. Default port based upon installation instructions is 39013.

  4. Database Name:  Ignored as database connectivity is governed by user access. 

  5. Set the Username and Password fields appropriately

  6. Enabled: Set to true to enable this connection.

  7. UseWindowsAuth:  Set to false.

  8. InitialSql: Optional.  An SQL query/command that will be run once upon establishing a connection.

  9. TimeZone: The time zone to use for SAP HANA DATE, TIME, TIMESTAMP and SECONDDATE columns types that have no time zone information of their own.  

  10. PrintRows: Default is false. If true, the rows from the SQL query will be printed to the jvm-link log.  This is for debugging purposes only and should not be set to true in general as it could significantly impact performance.

Leave all other fields with their default values.

Time Zone 

Some SQL date and/or time column types have no zone information. The TimeZone field is available to specify the time zone that Seeq should use for data coming from columns types that have no time zone information of their own. UTC offsets (+01:00, -10:30, etc.) and IANA regions (America/Los_Angeles) are accepted. If no time zone is specified, Seeq defaults to the local region of the Seeq server. If your data was stored in UTC time, set this field to "UTC" or "+00:00". If your data was entered using a "wall clock", set this to the IANA time region of the "wall clock". Note that offsets are constant throughout the year whereas a region may observe daylight savings time. If you used a wall clock in a location that observes daylight savings time, a region is a better choice than an offset for this field. A list of IANA regions (tz database time zones) can be found here.

Confirming the Connection

A successful connection to Microsoft SQL Server or MySQL will show a green checkmark under the ‘Connected’ tab in Seeq Workbench.  It will show “0 items indexed”, but that is okay since the signals are created via Seeq Formula as you will see later.

Adding Multiple Connections

  1. Copy an existing connection block within the Connections list field. (See below for an example of how to put the new block in the right place and add an appropriate comma.)

  2. Replace the ID field in the new connection block with a brand new "GUID". You can create a new GUID at https://www.guidgen.com.

  3. Change the NameHostnameUsername and Password fields appropriately.

  4. Save JSON configuration file.

Do not forget to generate a new ID field as described above.

Example using Microsoft SQL Server:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 { "Version" : "com.seeq.link.connectors.sql.config.SqlConnectorV1LegacyConfigV1", "Connections" : [ { "Name" : "LIMS Database", "Type" : "MSSQLSERVER", "Location" : null, "Hostname" : "LIMSSVR2", "Port" : 1433, "DatabaseName" : "lims", "Id" : "3acdc486-fc80-4e5a-8dbf-8ae140ef2cd7", "Username" : null, "Password" : null, "Enabled" : true, "UseWindowsAuth" : true, "InitialSql" : null, "TimeZone" : null }, { "Name" : "Operations Database", "Type" : "MSSQLSERVER", "Location" : null, "Hostname" : "OPDBSQLSVR83", "Port" : 1433, "DatabaseName" : "operations", "Id" : "855a0a77-676d-4813-93ef-22f5d1772e8a", "Username" : "seeqconn", "Password" : "8a@AR34Qa", "Enabled" : true, "UseWindowsAuth" : false, "InitialSql" : null, "TimeZone" : null } ] }

Security

Starting with Seeq R52, "Password" field may be specified using an external secret file.