Skip to main content

Creating a SQL Datasource

To connect Informer to a relational database, create a SQL Datasource. From the Datasource listing page, click New Datasource and choose a SQL database type.

The New menu with the SQL section listing Generic JDBC, Google Cloud BigQuery, IBM DB2, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, and Progress OpenEdge
The SQL Datasource options.

Connecting​

Select the database type, and a connection form appears with fields specific to that type. A Generic JDBC Datasource can connect to any database that has a compatible JDBC driver installed on the Informer server (see Installing JDBC drivers).

The common fields across SQL types are:

  • Name: a name for the Datasource.
  • Connection String: points to the database, including the connection type, server, and port, for example jdbc:driver://localhost:1433.
  • Driver Class: the Java class for the JDBC driver. For SQL Server it defaults to com.microsoft.sqlserver.jdbc.SQLServerDriver.
  • Connection Properties:
    • user / password: the credentials to connect with.
    • databaseName: the database name. This is case-sensitive; a non-matching case will not scan.
    • selectMethod: mostly affects performance. The default, cursor, is usually best.
    • sendTimeAsDateTime: true or false (defaults to false).
    • encrypt: set to false to disable TLS. For a Microsoft SQL Server Datasource, set encrypt = true to enable encryption (it defaults to false). For a Generic JDBC Datasource connecting to SQL Server, encrypt is not set to false by default, so the driver behaves as if it were true; set encrypt = false explicitly to disable encryption.
  • Schema Name: the schema, if any. MySQL has no schema, so it defaults to the database name.
  • Multiple Schemas: check this if there are several schemas, then use Add Schemas.
  • Transaction Isolation Level: defaults to Read Uncommitted, so queried records are not locked.
The Microsoft SQL Server connection form with a Driver Class and Connection Properties for user, password, databaseName, selectMethod=cursor, and sendTimeAsDatetime=false
A Microsoft SQL Server Datasource.

Other types, like PostgreSQL and MySQL, use a simpler form with Server, Port, User, Password, and Database fields.

The PostgreSQL connection form with Server, Port, User, Password, Database, Schema Name, and Statement Timeout fields
A PostgreSQL Datasource.

Generic JDBC options​

A Generic JDBC Datasource has a few unique fields:

  • Connection String: the base connection string Informer uses, for example jdbc:sqlserver://servername:1433.
  • Driver Class: the Java class for the JDBC driver (from the driver's documentation).
  • Use Prepared Statements: enable or disable prepared statements (enabled by default).
  • Connection Properties: Key/Value pairs appended to the connection string. Drivers typically need username, password, and databaseName; some need more. Click Add Property for each pair.
The Generic JDBC connection form with Name, Connection String, Driver Class, Connection Properties Key/Value pairs, Schema Name, and Use Prepared Statements
A Generic JDBC Datasource.
Snowflake

When connecting to Snowflake via Generic JDBC, add the connection property CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX with a value of true. Without it, Snowflake searches your entire account for metadata during a scan instead of just the connected database, which can cause scan failures and slow performance.

After entering the details, click Test Connection to verify, then save. Then scan the Datasource to bring in its Mappings and Fields. See Scanning a Datasource.

For connection properties per database, see the vendor documentation: Microsoft SQL Server, Oracle, MySQL, PostgreSQL, IBM DB2, Progress OpenEdge, and Snowflake.

Google Cloud BigQuery​

Google Cloud BigQuery is a serverless data warehouse, and connecting to it needs different information. First download a key as JSON from a Google Cloud service account, then open it in a text editor to get the values.

In Informer, click + New, select Datasource, then Google Cloud BigQuery. Give it a name, then copy the Project ID, Client Email, Location, and Private Key from the key file into the matching fields. Add any optional information (Schema Names, a Timeout), then click Save.

The Google Cloud BigQuery connection form with Project ID, Client Email, Location, Private Key, Statement Timeout, and Schema Name fields
The Google Cloud BigQuery connection form.
Also available through the REST API
Everything on this page is built on Informer's public REST API, the same one the product uses, so your scripts and integrations can do it too. Developer reference: Datasources.