SQL

An overview of SQL's data types and useful facts. 

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: Create a username and password for Plecto to get read only access to the database: 

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

 

Please note: If you use Azure, please make sure 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: Writing your SELECT statement 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 isn't found)
  • date (required date associated with this record (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 on 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 }};

 

Important: 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 form the raw data using formulas.

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. 

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 the datasource was updated.

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

 

Update frequency: 

Our system will automatically increase your SQL update frequency depending on the reply time from your server. 

If the update frequency is set to 1 minute, but the import takes between 30 - 120 seconds, we 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 imported per call: