SQL Connector

The SQL Connector allows you to extract data from SQL databases, with support for PostgreSQL and MySQL.

You will need to ask the developers who work with the database administrator (DBA) who is maintaining the database about the schema of data stored there.

Prerequisites
Scheduling
Switchboard Script Syntax
Parameters

Prerequisites

SQL Connection

Host: name of server hosting the database, e.g. “db.mycompany.com”
Port: port, if needing to override the default (3306 for MySQL, 5432 for Postgres)
User: user to login as Password; user’s password
DB Name: name of the database
SQL Dialect: “postgres” or “mysql”
Charset: charset, needed for some mysql db

SSH Tunnel

SSH Tunnel Host: name of the server hosting the SSH tunnel
SSH Tunnel Port: port, if needing to override the default of 22
SSH Tunnel User: user to log into the SSH server as
SSH Tunnel Password: user’s password; use this or RSA key
SSH Tunnel RSA Key: user’s private RSA key; use this or password

If you use an RSA key for authenticating to the SSH tunnel, that will look something like

SSL

Needed if we must connect to your DB using SSL; for mysql

Client Key Pem: client’s private key. Will look like an RSA key, similar to SSH above
Client Cert Pem: client’s certificate
Server CA Pem: certificate authority’s certificate

The Client Cert and Server CA certificates will look something like:

Scheduling

The connector can be scheduled on an hourly basis.

Only snapshots are supported.

Using Switchboard Static IP

If necessary due to IT or security policy, this connector can be configured to route traffic through one of Switchboard’s static IP addresses. To do so, include the parameter static_ip: true; in the Switchboard Script import statement.

Switchboard Script Syntax

import connector_name from {
  type: "sql_ng";
  key: "connector_key";
  table: <string, required>;
  schema: <string, needed for postgres if the table is not part of the default schema>;
  filter: <string, optional>;
  use_whitelisted_ip: <boolean; optional>;
} using {
   <must match to the schema in the source database>
};

Parameters

table: table to download. The connector will download all columns and all rows.
schema: name of the schema the table is a part of; this is for Postgres only.
filter: SQL where clause to limit what rows are retrieved.

Below is an example of a filter

time_modified > current_date - interval '7 days'