SQL

An overview of the SQL-integration and how to set it up.

Using our SQL integration it is possible to display:

  • Anything SQL expressible.

Useful facts about SQL: 

Update Frequency:
Every 1 minute (or custom)

Supports Custom Fields?
Yes

Supports deleted registrations?
No

How to import data from your SQL server into Plecto:

Step 1: Plecto supports the following SQL server types: 

  • MySQL
  • PostgreSQL
  • Microsoft SQL Server 2005 and later
  • Azure 
  • Oracle

 Step 2: Please configure your firewall to only allow traffic from these sources: 

  • 54.76.22.100
  • 54.76.22.43
  • 52.210.249.227
  • 78.143.97.156

 Step 3: Add an SQL data source and connect it to your database: 

In general, we recommend creating a username and password specifically for Plecto to be able to connect to the database. They should be read only.

Please note: If you are using Azure then you should to add @hostname to the username.

E.g. if the hostname is "plecto.database.windows.net" then you should add "@plecto" to the username.


Step 4: Write a SELECT statement where you should 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 DB)

 

Step 5: 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 }};

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.

Important information about the import

  • 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 which 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 has changed.
  • New data: Plecto only imports rows that have changed in the last 5 minutes (for example). This means you have to write your SQL query in such a way that it only returns the latest 5 minutes of changes. Please see below
  • 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.

How to limit your SQL statement:

Plecto will, in each import, import all data – also data that has already been imported. Therefore, it is important to limit your statement so you do 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 it only returns the latest changed data. You can use the variable {{ last update time }} to filter out items that were not imported since the latest run.

If you have a date field, it can be done in the clause by using the following function:

date field >= {{ last update time }} 

{{ last update time }} will be replaced with the last date and time the datasource was updated.

You can also use a top 100 in the SELECT function, and remember to order DESC (descending) to select the newest records. 

Update frequencies 

Our system will automatically increase the SQL update frequency of a data source depending on the reply time 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 minute, but the import takes between 120 - 180 seconds, we will adjust the update frequency to 15 minutes. 
  • If the update frequency is set to 15 minute, but the import takes between 180 - 360 seconds, we will adjust the update frequency every 1 hour. 

Registrations import limit

Based on the update frequency, Plecto limits the number of registrations that can be imported per query:

Update frequency Max number of registrations
1 min 1.000
5 min 4.000
15 min 10.000
30 min 16.000
1 hour 20.000
2 hours 30.000
4 hours 40.000
8 hours 60.000
1 day 100.000