Subscription

To connect an SQL integration 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

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

Now you can add an SQL data source in Plecto and connect it to your database. We recommend you create a new username and password that are specifically used 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.

New SQL Data Source

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

Hostname

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

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

  • member_name (required to map data to members in Plecto)
  • member_id (required to create the user, if ID is not found)
  • 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)

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.

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
Member ID Source

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.

  • Update frequency: Every 1 minute (or custom)
  • Supports Custom Fields? Yes
  • Supports deleted registrations? No

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.

  • 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.

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, it is necessary to limit your statement so that Plecto does not import millions of rows of data at every import.

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. 

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.

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

SQL Update Frequencies

Was this article helpful?

Please leave a comment to help us improve.