Subscription requirements

To connect SQL with Plecto, you need to be subscribed to Plecto's Large or Enterprise plan and have admin access in your organization. Visit our Pricing page to learn more.

With our SQL integration, you can display anything that can be expressed by SQL. Plecto supports the following SQL server types:

  • MySQL
  • PostgreSQL
  • Microsoft SQL Server 2005 or later (including Azure SQL)
  • Oracle
  • Snowflake
  • BigQuery
  • Update frequency – Every 1 minute (or custom)
  • Supports custom fields? – Yes
  • Supports deleted registrations? – No

Our system will automatically increase the SQL update frequency of a data source, depending on time spent importing from your server. Examples:

  • If the update frequency is set to 1 minute, but the import takes between 30 - 120 seconds, Plecto will adjust the update frequency to 5 minutes.
  • If the update frequency is set to 1 or 5 minutes, but the import takes between 120 - 180 seconds, Plecto will adjust the update frequency to 15 minutes.
  • If the update frequency is set to 1, 5, or 15 minutes, but the import takes between 180 - 360 seconds, Plecto will adjust the update frequency to 1 hour.

Before you try to import data from your SQL server, please make sure to configure your firewall to only allow traffic from the following sources:

  • 54.76.22.100
  • 54.76.22.43
  • 52.210.249.227
  • 78.143.97.156

Before you connect, we recommend that you create a new username and password that are specifically intended for connecting your database to Plecto. This way, you will avoid sharing the same password across databases. It will also allow you to limit permission for the created user to only have read access.

  1. Go to Data management > Data sources > + New data source and choose your database.
  2. Fill out the credentials and click Continue.
Username field when connecting Azure

If you are using SQL server on Azure, you should add username@[hostname] in the username field. For example, if the hostname is plecto.database.windows.net, then you should add username@plecto.database.windows.net in the username field.

New SQL Data Source

An example of the configuration page for an SQL data source.

Once you connect Plecto to the database, you need to write a SELECT statement where you include the following standard fields:

  • member_id (required to create the user, if ID is not found)
  • member_name (required to map data to members in Plecto)
  • date (required date associated with this record (always in UTC!))
  • reference (required unique identifier; can be used to update registrations)
  • team_name (optional; we will upsert it in the Plecto data source)

Here's an example of an SQL statement:

SELECT
    sales_table.user_id as member_id,
    sales_table.user_name as member_name,
    sales_table.date as date,
    sales_table.id as reference,
    sales_table.sales_team as team_name 
FROM
    sales_table
WHERE
    sales_table.date > {{ last_update_time }}
    AND
    sales_table.date < {{ upper_date_limit }}
Stored procedures

Plecto can also use stored procedures instead of SELECT statements. These should be used with an EXEC statement. Please note that the stored procedures should still include the standard fields mentioned above.

Member ID source tells Plecto which system is the data supplier. The Member ID Source should be the same system name you see in the Data suppliers column under Organization > Employees. Both Member ID Source and Data suppliers refer to the same system.

If both places have different system names, Plecto will create new employees along with each data import, which might result in duplicate employee profiles. Therefore, we recommend using the same Member ID Source name as is the name of your data supplier.

With each import, Plecto will import and update all data, including the rows that have already been imported but have not changed since the last import run. Therefore, be mindful of how much data you actually need to import – the more optimal your setup, the faster Plecto will be able to process your data.

To speed up the updates and have a lower load on your servers, please limit your statement so that it only returns the latest changed data. You can use the variable {{ last_update_time }} to filter for items that did not get imported since the last run.

  • If you have a date field, you can add the following function to the clause, and the variable will be replaced with the latest date and time when the data source was updated:
    date field >= {{ last_update_time }} 
  • You can also add an upper date limit that will keep Plecto from importing data newer than the indicated limit. Use the following function:
    date field <= {{ upper_date_limit }}
  • You can also use a top 100 in the SELECT function. Remember to order DESC (descending) to select the newest records.
last_update_time and upper_date_limit

When you write an SQL statement, you can add a start date (last_update_time) and end date (upper_date_limit) that will make your database only import data from within that period.

You can run a test query to see how many rows there are during the indicated period.

SQL - Test Query Message.png

Plecto supports sending data over an SSH tunnel. SSH allows you to transport data over a secure and encrypted connection. Please contact your organization's technical staff if you want to connect your database to Plecto using an SSH tunnel but don't know how.

SQL - Using SSH Tunnel.png
RSA private key

Plecto uses an RSA private key (PEM format) to access your database. You should generate the key in the system you use, and it should be formatted in Plecto as follows:

-----BEGIN RSA PRIVATE KEY-----
paste key here
-----END RSA PRIVATE KEY-----

  • The query: Write an SQL statement that returns a number of rows, each corresponding to a registration in our system. Each row can have any number of columns that will be saved on the registration.
  • Historical data: Plecto can import historical data once. Then we keep the data up to date by importing every [x] minutes, but only the rows that have changed.
  • Import logs: You can see all the import logs, both automatic and manual, by clicking the View logs button in the top-right corner in the data source settings.
  • New data: With each import, Plecto will import all data. If you only want to see the new/updated data, you can limit your SQL statement (read below). By limiting the statement, Plecto will only import rows that have changed, for example, within the last 5 minutes.
  • Raw data: Plecto works best if you import raw data. That means one row should represent one item. Do not import already computed metrics. Plecto can calculate these metrics from the raw data using formulas.
Registration import limit

Plecto limits the number of registrations that can be imported per query based on the update frequency.

SQL Update Frequencies
Manual data import

Starting July 2022, our SQL integrations support manual data import.

  1. Open the data source settings and click Import historical data.
  2. Choose the start and end dates and click Start import.

Keep in mind that if you want to import lots of historical data, you should split data import into multiple sessions. If you import a lot of old data at once, the database might not be able to complete the import.

Start and end dates will be included in the statement

Once you choose the start and end dates for manual data import, they will be passed to the last_update_time and upper_date_limit in your statement's template.

Was this article helpful?

Please leave a comment to help us improve.