Data Connectors

Use the Studio to harness the power of our awesome and easy to use connectors.

Overview

One thing all of us Tokars and Toucan as a firm is proud of has to be our wide range of connectivity to data sources. We offer more than 50+ connectors to various platforms ranging from classical Databases, ERP systems, and even to data sources that have no ODBC connectivity via an Http API connector (if there is an API offering).

We don’t want you to put extra effort into extracting the data out of whatever you data source might be. Let us get going then 😄.

Note

Even if Toucan doesn’t offer connectivity to your data source, rest assured, as we are completely capable of building a custom connector given that your data source has API connectivity or an open-source API Client based on your preference.

Terminology:

There are 2 different entities when it comes to configuring connectors in Toucan: Data Providers, and Data sources. Let’s see what they mean:

Data Sources

This is your raw data. They might be simple flat files or even Remote files over an FTPS. The data you receive that will form the basis of your small-app.

See data sources as “untouched” data. Data sources come directly from the organization, you haven’t done anything to them yet to fit your needs in the small-app.

Think of it as the original version of your data. Well, the source 😄

Data Providers

Using our connectors, you can query multiple external data providers in Toucan Toco apps.

Data providers are server-side, which means they require a connection from Toucan Toco’s server to the data source.

Supported connectors

We offer more than 50+ connectors to various platforms. You can consult more in detail some of our connectors in the following sections.

List of all suported connectors
  • Adobe Analytics
  • Aircall
  • Amazon Athena
  • Amazon Document DB
  • Anaplan
  • Clickhouse
  • Databricks
  • Dataiku
  • Denodo
  • Elasticsearch
  • Facebook Ads
  • Facebook Insights
  • Github Connector
  • Google Adwords
  • Google Analytics
  • Google Big Query
  • Google Cloud MySQL
  • Google My Business
  • Google Sheets
  • Hive
  • Http API
  • Hubspot
  • Indexima
  • Lightspeed
  • LinkedinAds
  • MicroStrategy
  • Microsoft Azure SQL
  • Microsoft SQL Server
  • MongoDB
  • MySQL
  • Net Explorer
  • OData
  • Odbc
  • OneDrive
  • Oracle SQL
  • PostgreSQL
  • ROK
  • Redshift
  • Revinate
  • SAP HANA
  • Salesforce Service Cloud
  • SharePoint
  • Snowflake
  • Soap
  • Toucan Toco
  • Trello
  • Wootric

Http API connector

An HTTP API connector is a very useful and powerful connector that can help you to connect to get data from any HTTP REST API’s.

This type of data source combines the features of Python’s requests library to get data from any API with the filtering language jq for Flexible and desired transformations of the responses.

How to use

First, we need to navigate to the DATA section in the bottom panel of the Toucan studio and then select the CONNECTORS tab to get directed to Toucan connector interface 😄

eval_rst   .. figure:: img/Connectors_TAB.jpg     :class: initial-width     :alt: connectors tab

The User-friendly connector interface greets you with Toucan’s trademark Guided Framework. You just need to fill in the right parameters to get going.

eval_rst   .. figure:: img/Screenshot_070320_103840_PM.jpg     :class: initial-width     :alt: add connector button

  • Select the Add a connector
  • Then search up the connector from the list and select the Http API connector

eval_rst   .. figure:: img/HTTP_API_Connector.jpg     :class: initial-width     :alt: http api connector

Data provider configuration

  • Fill out the basic details like the name, base route URL that you would be using to pull the data from, authentication, etc…
  • Click on TEST CONNECTION and after success click SAVE to finalize the connection.

eval_rst   .. figure:: img/HTTP_API_Test_connection.jpg     :class: initial-width     :alt: test connection connector

For this demo, I would like to access an open API but feel free to test it out with other kinds as well.

  • After successfully, configuring the data provider you can see in your connectors interface that your newly configured data provider is available.

eval_rst   .. figure:: img/HTTP_API_Saved_Data.jpg     :class: initial-width     :alt: http api saved data

  • Now click on the + symbol that says ADD A QUERY to actually query the data provider and extract data into Toucan! (Data source)

Data source configuration

  • Now, similar to the data provider, we have to fill in the basic details like domain name or simply the name you want to provide the table that would store the data in Toucan, data connection - `LIVE’/’LOAD’, end point url, params, HTTP method (GET / POST/ PUT etc).

Then finally SAVE the data source to use it.

eval_rst   .. figure:: img/HTTP_API_Data_Source.jpg     :class: initial-width     :alt: http api data source

  • You have successfully created the Data source, Congratulations!! You can now `view’ it in the connectors interface with the corresponding Domain name that you defined.

eval_rst   .. figure:: img/HTTP_API_Final_Data.jpg     :class: initial-width     :alt: http api final

  • Now you can finally use this data from the data explorer of Toucan to build your beautiful Toucan charts 😄.

eval_rst   .. figure:: img/HTTP_API_Data.jpg     :class: initial-width     :alt: http api data

Complete example

The complete spec of an HttpAPI entry in DATA_SOURCES is as follows:

coffee       DATA_PROVIDERS: [           name: "open-data-paris"           type: "HttpAPI"           baseroute: 'https://opendata.paris.fr/api/'           template:               headers:                   requested-by: 'Toucantoco'       ]

coffee       DATA_SOURCES: [         domain: "books"         type: "HttpAPI"         name: "open-data-paris"         method: "GET"         url: "records/1.0/search/"         params:           dataset: 'les-1000-titres-les-plus-reserves-dans-les-bibliotheques-de-pret'           facet: 'auteur'         filter: ".records[].fields"       ]

The JSON response looks like this:

json   {     "nhits": 1000,     "parameters": { ... },     "records": [       {         "datasetid": "les-1000-titres-les-plus-reserves-dans-les-bibliotheques-de-pret",         "recordid": "4b950c1ac5459379633d74ed2ef7f1c7f5cc3a10",         "fields": {           "nombre_de_reservations": 1094,           "url_de_la_fiche_de_l_oeuvre": "https://bibliotheques.paris.fr/Default/doc/SYRACUSE/1009613",           "url_de_la_fiche_de_l_auteur": "https://bibliotheques.paris.fr/Default/doc/SYRACUSE/1009613",           "support": "indéterminé",           "auteur": "Enders, Giulia",           "titre": "Le charme discret de l'intestin [Texte imprimé] : tout sur un organe mal aimé"         },         "record_timestamp": "2017-01-26T11:17:33+00:00"       },       {         "datasetid":"les-1000-titres-les-plus-reserves-dans-les-bibliotheques-de-pret",         "recordid":"3df76bd20ab5dc902d0c8e5219dbefe9319c5eef",         "fields":{           "nombre_de_reservations":746,           "url_de_la_fiche_de_l_oeuvre":"https://bibliotheques.paris.fr/Default/doc/SYRACUSE/1016593",           "url_de_la_fiche_de_l_auteur":"https://bibliotheques.paris.fr/Default/doc/SYRACUSE/1016593",           "support":"Bande dessinée pour adulte",           "auteur":"Sattouf, Riad",           "titre":"L'Arabe du futur [Texte imprimé]. 2. Une jeunesse au Moyen-Orient, 1984-1985"         },         "record_timestamp":"2017-01-26T11:17:33+00:00"       },       ...     ]   }

We apply the filter .records[].fields which means that for every entry in the records property it will extract all the properties of the fields object. So we end up with a table of results looking like this (I’m skipping columns in this example but you see the point):

nombre_de_reservations auteur skipped columns…
1094 Enders, Giulia
746 Sattouf, Riad

Note: the reason to have a filter option is to allow you to take any API response and transform it into something that fits into a column based data frame. jq is designed to be concise and easy to use for simple tasks, but if you dig a little deeper you’ll find a featureful functional programming language hiding underneath.

PostgreSQL connector

Wonderful ! This connector is used to extract data from a PostgreSQL database !

How to use

Navigate to the connector interface from the DATA section in Toucan studio.

my sql connector
  • Select the Add a connector
my sql connector button
  • Then search up the connector from the list and select the PostrgesSQL connector
PostgresSQL add connector

Data provider configuration

  • We have to fill in the basic connection details like the connection details like host/ server name, username, password to connect to the database, port, we can also set timeout.
  • Then we can finally hit the TEST CONNECTION and SAVE the connection 😄
PostgresSQL data provider
  • After testing connection, you should have something like this :
PostgresSQL connected
  • After successfully, configuring the data provider you can see in your connectors interface that your newly configured data provider is available.
  • Now click on the + symbol that says ADD A QUERY to actually query the data provider and extract data into Toucan! (Data source)

Data Source configuration

Now that we have configured the data provider, its time to set the data source or simply the table that we are going to use to populate the charts in Toucan.

We need to fill in the details regarding the database and obviously the QUERY that we want to use.

  • domain: str, required
  • database: str, required

Note

Whenever you are querying a database, you would need to consider how complex is your query, for choosing between LOAD / LIVE modes of Toucan. Because if your query has some extra complexity to it, like joining tables/views or aggregating over many conditions, it would be recommended to ‘LOAD’ that data into Toucan, rather than LIVE querying because you don’t want your user to wait for more than 2 - 3 sec for the screen to load.

So depending on your DB’s performance / ability to respond back to a request within a quick time, you are free to choose between LOAD / LIVE.

The database I have chosen is Sample_data. Now we can hit the VALIDATE CONFIGURATION.

PostgresSQL validate

Now select one table from the list by following the tree structure. Here, I have selected DATES_WITH_NULLS table.

PostgresSQL Select table

Finally, select the columns you are interested in. You can also select all of them with the “Select all” button at the bottom left.

PostgresSQL Select columns

Here, let’s select DATE and VALUE column.

At any time, you can switch to SQL code mode. Here, my query is immediately translated into code mode.

PostgresSQL Code mode

At the end, you need to refresh this connection :

PostgresSQL Refresh

You have successfully connected to the PostgreSQL database and you can have a look at it in the Data Explorer tab and can use the dataset for your awesome Toucan visualizations. 😄

MySQL connector

Yep, you guessed it! This connector is used to extract data from a MySQL database with ODBC connectivity.

How to use

Navigate to the connector interface from the DATA section in Toucan studio.

my sql connector
  • Select the Add a connector
my sql connector button
  • Then search up the connector from the list and select the MySQL connector
my sql add connector

Data provider configuration

  • We have to fill in the basic connection details like the connection details like host/ server name, username, password to connect to the database, port, we can also set timeout.
  • Then we can finally hit the TEST CONNECTION and SAVE the connection 😄
my sql data provider
  • After successfully, configuring the data provider you can see in your connectors interface that your newly configured data provider is available.
  • Now click on the + symbol that says ADD A QUERY to actually query the data provider and extract data into Toucan! (Data source)

Data Source configuration

Now that we have configured the data provider, its time to set the data source or simply the table that we are going to use to populate the charts in Toucan.

We need to fill in the details regarding the database and obviously the QUERY that we want to use.

Tip

You can pretty much query tables, views and even stored procedures as well, in the connector interface’s ‘QUERY’ tab. :)

Either query or table are required, both at the same time are not supported.

  • domain: str, required
  • name: str, required
  • database: str, required
  • query: str (not empty), required if table is not provided.
  • table: str (not empty), required if query is not provided, will read the whole table.
  • follow_relations: bool, default to false. Merges data from foreign key relations.
  • parameters dict, optional. Allow to parameterize the query.

Note

Whenever you are querying a database, you would need to consider how complex is your query, for choosing between LOAD / LIVE modes of Toucan. Because if your query has some extra complexity to it, like joining tables/views or aggregating over many conditions, it would be recommended to ‘LOAD’ that data into Toucan, rather than LIVE querying because you don’t want your user to wait for more than 2 - 3 sec for the screen to load.

So depending on your DB’s performance / ability to respond back to a request within a quick time, you are free to choose between LOAD / LIVE.

The database I have chosen is Rfam and the table I wanna access is called family, Also I want to query this data LIVE in Toucan, I am going to fill in those details in the respective sections provided and also write the query and then hit SAVE to finalize the data source.

my sql data source

You have successfully connected to the MySQL database and you can have a look at it in the Data Explorer tab and can use it in the pristine Toucan visualizations. Way to Go Cowboy / Cowgirl !! 😄

my sql data explorer

MongoDB Connector

This connector is used to retrieve data from a MongoDB database, which is also a NoSQL database. This is a clear example that shows Toucan’s vast spectrum of connectivity, allowing our users to connect to RDBMS’s as well as NoSQL DB’s like MongoDB with ease!

How to use

Navigate to the connector interface from the DATA section in Toucan studio.

connectors tab mongodb
  • Select the option Add a connector
mongodb add connector button
  • Then, simply search up the required connector from the list and select the MongoDB connector.
mongodb add

Data provider configuration

As we discussed earlier in the other connectors, we have to fill in basic connection details, like the host (server name /server IP address), port details, username & password, etc.

Then click TEST CONNECTION and finally SAVE it 😄

mongodb modal

After successfully, configuring the data provider you can see in your connectors interface that your newly configured data provider is available.

Now click on the + symbol that says ADD A QUERY to actually query the data provider and extract data into Toucan! (Data source)

mongodb modal add

Data source configuration

Now that we have configured the data provider, its time to set the data source or in simple terms the table that we are going to query and use to populate the charts in Toucan.

We need to fill in the details regarding the database, collection name, the QUERY that we would want to use.

Supports simple, multiples and aggregation queries as described in our documentation

  • domain: str, required
  • name: str, required
  • database: str, required
  • collection: str, required
  • query: str (translated to a query {domain: <value>}), dict or list, required

The database I have chosen is video and the collection I want to access is called movieDetails, Also I want to ‘LOAD’ this data into Toucan, I am going to fill in those details in the respective sections provided.

Note

Whenever you are querying a database, you would need to consider how complex is your query, for choosing between LOAD / LIVE modes of Toucan. Because if your query has some extra complexity to it, like joining tables/views or aggregating over many conditions, it would be recommended to ‘LOAD’ that data into Toucan, rather than LIVE querying because you don’t want your user to wait for more than 2 - 3 sec for the screen to load.

So depending on your DB’s performance / ability to respond back to a request within a quick time, you are free to choose between LOAD / LIVE.

Now we can write the query in 2 types of configuration or methods, Type 1 allows you to write the mongo query in terms of a JSON, which we are gonna use now.

I am specifying in the query, that I would like to filter the results based on columns rated and year, I am still able to use the mongo operators as you can see and after analyzing the DataSet Preview on the right, hit SAVE to finalize the data source.

mongodb query

You have successfully connected to the Mongo database and you can have a look at it in the Data Explorer tab and can use the dataset for your awesome Toucan visualizations. Let’s get it! 😄

mongodb data explorer

Anaplan connector

This connector is used to connect to an Anaplan source.

How to use

Navigate to the connector interface from the DATA section in Toucan studio.

connectors tab
  • Select the option Add a connector
"add a connector" button
  • Then search up the connector from the list and select the Anaplan connector.
Anaplan connector button

Data provider configuration

Fill in your connection details here:

  • NAME: Anything you like (pick something that will allow you to recognize that connector).
  • USERNAME: Your Anaplan user name (usually that’s your e-mail address).
  • PASSWORD: Your Anaplan password.
Anaplan connector add query

Once you’re done, hit the TEST CONNECTION button. If everything is successful, you can close the connection modal and save your connector by hitting SAVE.

Data source configuration

Let’s configure your first query.

Start by clicking on the ADD A QUERY button in your connector:

Anaplan connector add query

Then, fill out the required fields. Start by picking a workspace, and Toucan will suggest available models. Once you’ve picked a model, Toucan will suggest a view. Pick the desired view, and you’re done!

Anaplan connector add query

Azure SQL DB connector

This connector is used to connect to an Azure SQL database.

Note

Toucan also offers an On-Demand Managed Service mode of deployment other than the SaaS & On-premise variants, where essentially, Toucan deploys itself inside the cloud where your DB is staged (in this case, Azure)

How to use

Navigate to the connector interface from the DATA section in Toucan studio.

azure connector
  • Select the option Add a connector
azure connector button
  • Then search up the connector from the list and select the Azure SQL connector.
azure connector select

Data provider configuration As we discussed earlier in the other connectors, we have to fill in basic connection details, like the host (server name /server IP address), port details, username & password, etc.

Then click TEST CONNECTION and finally SAVE it 😄

azure connector data provider

After successfully, configuring the data provider you can see in your connectors interface that your newly configured data provider is available.

Now click on the + symbol that says ADD A QUERY to actually query the data provider and extract data into Toucan! (Data source)

azure connector add query

Data source configuration

Now that we have configured the data provider, its time to set the data source or in simple terms the table that we are going to query and use to populate the charts in toucan.

We need to fill in the details regarding the database, collection name, the QUERY that we would want to use.

Tip

You can pretty much query tables, views and even stored procedures as well, in the connector interface’s ‘QUERY’ tab. :)

The database I have chosen is testconnector and in this case, I am actually querying a view called SalesLT.vGetALLCategories.

Also, I want to use LIVE query this data into Toucan, I am going to fill in those details in the respective sections provided.

Note

Whenever you are querying a database, you would need to consider how complex is your query, for choosing between LOAD / LIVE modes of Toucan. Because if your query has some extra complexity to it, like joining tables/views or aggregating over many conditions, it would be recommended to ‘LOAD’ that data into Toucan, rather than LIVE querying because you don’t want your user to wait for more than 2 - 3 sec for the screen to load.

So depending on your DB’s performance / ability to respond back to a request within a quick time, you are free to choose between LOAD / LIVE.

Now we can normally use the AZURE SQL syntax to query the desired results into toucan charts. You can pretty much write simple select to complex queries like subqueries, joins, querying even views, stored procedures etc

azure connector data source

You have successfully connected to the Azure SQL database and you can have a look at it in the Data Explorer tab and can use the dataset for your intuitive toucan visualizations. Let’s goo! 😄

azure connector data explorer

AWS Athena connector

This connector is used to connect to an AWS Athena Data source.

Note

Toucan also offers an On-Demand Managed Service mode of deployment, other than the SaaS & On-premise variants, where essentially, Toucan deploys itself inside the cloud where your DB is staged (in this case, AWS cloud)

How to use

Navigate to the connector interface from the DATA section in Toucan studio.

aws connector
  • Select the option Add a connector
aws connector button
  • Then search up the connector from the list and select the Athena connector
aws athena connector button

Data provider configuration

Here you can fill connection parameters:

  • NAME : the name of your connector.
  • S3 OUTPUT BUCKET : the S3 bucket and prefix where results from your queries will be saved (Ex s3://mybucket/athena-queries).
  • AWS ACCESS KEY ID : the ID of the the AWS access key that will be used to connect to Athena.
  • AWS SECRET ACCESS KEY : the AWS secret access key that will be used to connect to Athena.
  • REGION NAME : the name of the AWS region you need to query.
aws connector form

After entering those informations, you can test the connection with AWS, to make sure your inputs are correct and working.

test connection button

If all settings are valid, you’re going to have a success message like this :

successful connection modal

Note

Depending on your rights, you will have the “Can list databases” checked or not, but note that this doesn’t prevent you to be hable to use the connector itself.

After saving your connector, you can now click on the + symbol that says ADD A QUERY to actually query the data provider and extract data into Toucan! (Data source)

add query button

Data source configuration

Now that we have configured the data provider, its time to set the data source or in simple terms, the raw table that we are going to query.

We need to fill in the details regarding the database, the domain, LOAD / LIVE, the database, the QUERY that we would want to use (or the table input), and a few other parameters.

query addition preview

The database chosen in the screenshot is mydatabase and in this case, the query is written in the QUERY field. Note that it’s possible to just specify in the TABLE field the table you want to run queries on.

Note

Whenever you are querying a database, you need to consider how complex your query is, to choose between the LOAD and LIVE modes of Toucan. If your query is complex, for example if it aggregates over many conditions, or if it does joins with other tables or views, we recommend using LOAD mode. For an optimal user experience, LIVE queries should take less than 3 seconds.

We can now use standard SQL syntax to query the desired results into Toucan charts. You can write any query considered valid Athena SQL .

Note

The datasource’s configuration flow is the same when you add it directly from a tile or a story.

Congratulation, at this stage, you have successfully connected to the AWS Athena data source. You can have a look at it in the Data Explorer tab and can use the dataset for your intuitive toucan visualizations. Who’s ready for some mind-blowing viz? 🚀

AWS Redshift DB connector

This connector is used to connect to an AWS Redshift Data source.

Note

Toucan also offers an On-Demand Managed Service mode of deployment, other than the SaaS & On-premise variants, where essentially, Toucan deploys itself inside the cloud where your DB is staged (in this case, AWS cloud)

How to use

Navigate to the connector interface from the DATA section in Toucan studio.

aws connector
  • Select the option Add a connector
aws connector button
  • Then search up the connector from the list and select the Redshift connector
aws connector button

Data provider configuration

Here you can fill connection parameters:

  • host
  • port
  • cluster_identifier
  • connect_timeout (Optional)

After this, you can choose an authentication method to connect to AWS Redshift:

  • Method db_credentials: You have to fill username and password used to etablished the connection.
  • Method aws_credentials: You have to fill the access_key_id, secret_access_key, region and db_user fields. The session_token is optional. This approach allows users the option of using AWS credentials and limiting the permissions the connected user has. The user should have the right permissions to access to the redshift database. To find some examples about rights permissions, see this documentation and this one.
  • Method aws_profile: CAUTION: This authentication method can only work for now with the self-hosted mode. For more details about profile: AWS CLI Profile You have to fill the db_user and profile fields. To find some examples about rights permissions, see this documentation and this one. Then click TEST CONNECTION and finally SAVE it.
aws data provider

After successfully, configuring the data provider you can see in your connectors interface that your newly configured data provider is available.

Tip

In order to have the graphical database exploration in your connector, you need to promote an access to the default dev database to your user. This database contains a table pg_database listing all available databases as well as the pg_table_def listing all available tables in the cluster's databases. Without this access to the dev database, you will face a warning error when testing the connection of your data provider.

Now click on the + symbol that says ADD A QUERY to actually query the data provider and extract data into Toucan! (Data source)

aws add data

Alternatively, you can directly configure a query by clicking on the + ADD a chart (or + Add tile) link in a story (or in homepage).

Story add a chart

In the query section, click on the live connections link and select the Redshift connector you configured.

Redshift live connection

It will open the Edit datasource menu.

Data source configuration

Note

Whenever you are querying a database, you would need to consider how complex is your query, for choosing between LOAD / LIVE modes of Toucan. Because if your query has some extra complexity to it, like joining tables/views or aggregating over many conditions, it would be recommended to LOAD that data into Toucan, rather than LIVE querying because you don't want your user to wait for more than 2 - 3 sec for the screen to load. So depending on your DB's performance / ability to respond back to a request within a quick time, you are free to choose between LOAD / LIVE. If you configure the query from a tile or story, the LOAD option won't be available.

Once in the datasource configuration menu, you’ll be able to select the database to query from the dropdown list (provided the configured user has the required rights on dev database).

Redshift datasource configuration

Once the database selected, you can click on VALIDATE CONFIGURATION and move forward to the QUERY section. In this section you will be able to search & select tables in the previously selected database. Tables are listed underneath available schemas they belong to in the database.

Redshift table selection

Note

In this menu, you also have the possibility to click on the SWITCH TO CODE MODE button. It’ll display an SQL query interface where you’ll be able to directly write an SQL query to execute in the selected database.

Once the table selected, you can click on the SELECT TABLE button. The right panel will display a slice of the selected table and you’ll be able to select the desired columns on the left panel.

Redshift columns selection

In this section, you can search & select a column or directly SELECT ALL columns. Once done, you can click on APPLY DATA SELECTION to move forward to the REVIEW section. The right panel will be refreshed based on your column selection. The left panel, will display the selected schema, table & columns.

Redshift review section

Finally, if you’re satisfied with the query’s configuration you can click on SAVE QUERY and it will be available for further data preparation or for chart’s configuration.

Redshift query configured

Note

By default, column names are returned lowercased by Redshift. You can change this behaviour as described here.

Congratulation, at this stage, you have successfully connected to the AWS Redshift data source. Who’s ready for some mind-blowing viz? 🚀

Google Sheets Connector

This connector is used to retrieve data from a Google spreadsheet.

Note that you will need to have Google OAuth2 credentials set up for your platform. For more information, please see the section Set up OAuth2 credentials for your platform.

How to use

Navigate to the connector interface from the DATA section in Toucan studio.

google sheet connector
  • Select the option Add a connector
google sheet button
  • Then search up the connector from the list and select the Google Sheets connector.
google sheet modal

Data provider configuration As soon as you click on the connector icon, you will be redirected by google to sign in to your account, in order to connect to your google sheet.

google sheet redirection

After logging in, you shall be redirected back to Toucan, where you will have the required attributes automatically filled by Toucan 😄. You just need to give a name to your connector.

Now, You just need to TEST CONNECTION and finally SAVE it 😄

google sheet data provider

After successfully, configuring the data provider you can see in your connectors interface that your newly configured data provider is available.

Now click on the + symbol that says ADD A QUERY to actually query the data provider and extract data into Toucan! (Data source)

google sheet add query

Data source configuration Now that we have configured the data provider, its time to set the data source or in simple terms, the table that we are going to query and use to populate the charts in Toucan.

We need to fill in the details regarding the spreadsheet like ID of the sheet, Sheet / Tab name, headers etc.

  • domain: str, required
  • name: str, required. Should match the data provider name
  • spreadsheet_id: str, required. Id of the spreadsheet which can be found inside the url: https://docs.google.com/spreadsheets/d//edit?pref=2&pli=1#gid=0,
  • sheet: str. By default, the extractor returns the first sheet.
  • header_row: int, default to 0. Row of the header of the spreadsheet

You can have a look at this below image to have an understanding of ‘where to find’ the required parameters for filling the data source config:

google sheet attributes

I chose to load this data into toucan and then save the configuration to finalize it and be able to use it to populate the Toucan charts 😉

google sheet final data source

You have successfully connected to your Google spreadsheet and you can have a look at it in the Data Explorer tab and can use the dataset for your awesome Toucan visualizations. Someone’s on a ROLL!! 🔥

google sheet data explorer

Don’t hesitate to contact us if you have some questions about connectors.

Sharepoint Connector

This connector is dedicated to extract files from Microsoft Sharepoint. The configuration will be divided in 2 parts :

  • Credentials setting in Azure : this part might probably done by your IT department.
  • Sharepoint connector setting in Toucan.
Credentials setting in Azure

In this part, we will connect to Azure in order to authorize the Toucan App.

  • Connect to Azure with an admin account : Azure Active Directory admin center.
  • Click on Azure Active Directory, then on App registrations and then on New registration
sharepoint connector 1
  • Give a name to your app (for instance : Toucan) & define who can have access to this app. Click then on Register
sharepoint connector 2

Your app has been now declared on Azure. In the Overview screen you can have access to several informations :

  • Application (client) ID -> it will correspond to the “Client ID” in Toucan.
  • Directory (tenant) ID -> it will correspond to the “Tenant ID” in Toucan.

Now we will create a Client secret. To do so, go on the Certificated & secrets section, and click on New client secret:

sharepoint connector 3

Give a name (field Description) to the client secret and set the validity period:

sharepoint connector 4

The secret will appear just one time, so copy it now. You won’t be able to copy it again after !

sharepoint connector 5

Now that we created the client secret, we will define the “redirect URIs”. To do this, go on the Authentication section, then click on Add a platform, and select Web in the window on the right.

sharepoint connector 6

Set the URI following your configuration (sub-domain, application name & connector_name): https://api-{sub_domain}.toucantoco.com/{application_name}/oauth/redirect?connector_name={connector_name}

sharepoint connector 7

Note that the connector_name will be the one that we will specify after in Toucan as “name”. Once the URI has been defined, click on “Configure”.

  • Go on the API permissions section, then click on Add a permission. Click on Microsoft Graph, then on Application permissions.
sharepoint connector 8

Add these 2 permissions : Files.Read.All & User.Read

sharepoint connector 9

The setting in Azure is now finished. You can create a Sharepoint connector in Toucan.

Sharepoint connector setting in Toucan

In this part, we will configure the Sharepoint connector in Toucan. You will need the informations of the first part (ask your IT department if they made the configuration) : connector_name, application (client) ID, directory (tenant) ID:

  • In the “Staging” mode, go on the connector interface (in datastore), and add a new “Sharepoint” connector.
  • Configure the Sharepoint connector regarding the informations you have in Azure.
sharepoint connector 10

Fields description :

NAME : it should be equal to the “connector_name” set while configuring the redirect URI in Azure. CLIENT ID : corresponds to “Application (client) ID” in Azure CLIENT SECRET : corresponds to the secret displayed after having added a new secret in Azure SCOPE : it can be set as “offline_access Sites.Read.All” TENANT : corresponds to “Directory (tenant) ID” in Azure

  • Click then on Test Connection. If you get an error, it might be a misleading information (fix in progress), try to move to the next step anyway. Click on Close, and then click on Save.
sharepoint connector 11

You might be redirected to Microsoft in order to authorize access. Accept the authorization:

sharepoint connector 12

Now, your Sharepoint connector has been correctly configured. You can import a file by adding a new query.

sharepoint connector 13

Set the different fields regarding the informations of your Sharepoint and the file you want to load (you can see the example bellow in which we are loading the sheet “data” of the file “Data_Shark_Attack.xlsx” located in Documents/test/Test_Sharepoint of our Sharepoint).

sharepoint connector 14

Fields description :

DOMAIN : domain name of the dataset. SITE URL : URL of the sharepoint. DOCUMENT LIBRARY : library. FILE : file path. SHEET : sheet name in the file.

Your Sharepoint Connector is now configured 🚀

ElasticSearch connector

This connector is used to connect to an ElasticSearch source.

How to use

Navigate to the connector interface from the DATA section in Toucan studio.

connectors tab
  • Select the option Add a connector
"add a connector" button
  • Then search up the connector from the list and select the ElasticSearch connector.

Data provider configuration

Fill in your connection details here (think about adding a host to get all necessary fields):

  • NAME: A meaningful name that will allow you to recognize the configured connector.
  • URL: The URL of your ElasticSearch server
  • PORT: The port number of the server
  • SCHEME: The scheme to use
  • USERNAME: The username to use for the authentication
  • PASSWORD: The password for the authentication

Once you’re done, hit the SAVE button.

Data source configuration

Let’s configure your first query.

Start by clicking on the ADD A QUERY button in your connector:

Then, fill out the required fields:

  • SearchMethod: you can select wether “search” or “msearch” (for multiple search)
  • Index: type the index of your ElasticSearch inwhich you want to extract data
  • Configuration type: select the “Type 1”
  • Body: enter your query within a Json format (see an example below)
how to add a query to a ElasticSearch connector

Databricks connector

This connector is used to retrieve data from a Databricks cluster Databricks Documentation.

What is it?

Databricks provides an SQL API to read & extract data from clusters hosted on the main cloud providers. This connector provides an interface to execute SQL queries against this API.

⚠️ This connector support ‘on-demand’ clusters i.e.: self stopping clusters. Make sure to tick the ON DEMAND parameter on the connector’s configuration form to handle queries on a stopped cluster.

⚠️ Live queries (queries created directly from a tile or a story) might not work properly in case of self stopped cluster

How to configure a Databricks connector and a query

Retrieve ODBC connection information from Databricks as described here

How to connect & configure a query

Navigate to the connector interface from the DATA section in Toucan studio.

connector
  • Click on the Add a connector button
Add a connector button
  • Then search up the connector from the list and select the Databricks connector
databricks add connector

Data provider configuration

Here, fill the connection details:

  • NAME: anything you like
  • HOST: usually in the format my-databricks-cluster.cloudprodiverdatabricks.net, you can retrieve it from your cluster’s configuration
  • PORT: default is 443
  • HTTPPATH: sql/protocol/v1/o/xxx/yyy, you can retrieve it from Databricks UI cluster’s configuration in the ‘ODBC’ section
  • PWD: your access token (generated from Databricks UI in user settings), usually in this format dapixxxxxx
  • ON DEMAND: if your cluster is self-stopping, make sure to tick this option. With this option, the connector will try to start the cluster if it’s stopped before any query

Then we can finally hit the TEST CONNECTION button. ⚠️ if the cluster is stopped, the connection test might fail, but you can SAVE the configuration anyway

my databricks data provider
  • After successfully configuring the data provider, you can see in your connector interface that your newly configured data provider is available.
  • Now click on the + symbol that says ADD A QUERY to actually query the data provider and extract data into Toucan!

Data Source configuration

⚠️ Please note that in case of a shutdown cluster, the query preview & live queries might be broken as of current state of the implementation. In such situations, the connector tries to start the cluster and wait for the cluster to be started. If you plan to use the connector in an ‘on-demand’ fashion (i.e.: with self-stopping clusters) use it only with ‘Load Data’ queries.

Now that you have configured the data provider, its time to set the data source. Two parameters are mandatory:

  • DOMAIN: str, required. Defines the name of the query
  • QUERY: str, required. The SQL query you want to run
  • PARAMETERS: dict, optional. Allows to parameterize the query.

You have successfully connected to the Databricks cluster. You can have a look at it in the Data Explorer tab and can use it in any Toucan’s chart 🚀.

Note

We specifically designed this connector to handle DATA REFRESH from an on-demand clusters. During this process, the connector will try to start the cluster and wait for it to be ready before running queries.*

Linkedin Ads Connector

This connector is used to retrieve data from the Linkedin Ads API: Linkedin Documentation

What is it?

Linkedin Ads has a marketing api offering that provides data for reporting about various resources like campaigns, creatives, user profile data etc.

How to connect ?

You need to have the credentials of the linkedin ads account, when you shall be prompted to generate the access token later. We can utilise the rest api connector available in toucan to get the data, all we need to are 3 things in general:

1) Create an app on linkedin dev platform. Get the approval from linkedin for accessing the marketing api. You will be asked to fill in a request access from that will usually be approved within 3 - 5 business days.

app creation

Access to LinkedIn’s marketing APIs requires a developer application that has been granted access to the Marketing Developer Platform(MDP). If you don’t have an app yet, you can create one through the create app form. Otherwise, you can see the list of your apps through the Developer Portal.

Note: We are using the 3 legged Flow for authorization and not the 2 - legged one as we cannot get access to the marketing api in the latter method.

2) Once you get the approval, configure the redirect uri with the URI provided in the credentials manager, copy the client_id et client_secret and paste them in the credentials manager interface. The redirect URI should be like this: https://api-yourinstance.toucantoco.com/oauth/redirect?type=LinkedinAds

Linkedinads app credentials

Okay, great work in configuring the credentials ! Now, you need to configure the data provider . Linkedin ads has a CAMPAIGN Manager where all the campaigns, creatives , analytics information are stored. This is from where we are fetching all the data, we need to know atleast the basic required parameters like Creative / Campaign ID. (Campaign tab has all the campaigns listed along with the info like campaign id and similarly all the creatives are in the Ads tab)

Linkedinads app Campaign Manager

Navigate to the connector interface from the DATA section in Toucan studio.

Connectors Tab
  • Select the option Add a connector
Add a connector button
  • Then search up the connector from the list and select the LinkedinAds connector.
LinkedinAds modal

Data provider configuration As soon as you click on the connector icon, you will be redirected to Linkedin to sign in to your account.

After logging in, you shall be redirected back to Toucan, where you will have the required attributes automatically filled by Toucan 😄. You just need to give a name to your connector.

LinkedinAds Connector form

There is an optional Template option to provide parameters at the connector level instead of in the data source. Now, You just need to TEST CONNECTION and finally SAVE it 😄

After successfully, configuring the data provider you can see in your connectors interface that your newly configured data provider is available. Now click on the + symbol that says ADD A QUERY to actually query the data provider and extract data into Toucan! (Data source)

Data source configuration Now that we have configured the data provider, its time to set the data source or in simple terms, the table that we are going to query and use to populate the charts in Toucan.

We need to fill in the details regarding the query to send to the API.

  • finder_method: str, required, default to “analytics”
  • start date: str, required. Format dd/mm/yyyy.
  • end date: str, optional. Format dd/mm/yyyy. Defaults to today
  • TimeGranularity: str. By “ALL”, available: “DAILY”, “MONTHLY”, “YEARLY”
  • Flatten Column: str, optional. Allows to unest nested column from the API’s response
  • Parameters: dict, some parameters are required, see below for an example

You can have a look at this below image to have an understanding of ‘where to find’ the required parameters for filling the data source config:

Linkedin Ads attributes

Configuration Example:

Linkedin Ads Example

Now you can start to play with you Linkedin Ads Data 🔥

HubSpot connector

This connector is used to retrieve data from the HubSpot API: HubSpot API Documentation

The email events part relies for now on Hubspot’s legacy API: HubSpot legacy API Documentation

Create a HubSpot application

First, you need to create a HubSpot application. To do so, you will need a registered HubSpot developer account:

  • Head over to Manage apps.

    Hubspot Manage apps button
  • Click on Create app.

    Hubspot Create app button
  • Once the App Info is filled, click on the Auth part, you’ll see the following:

    Hubspot app Auth configuration screen
  • Scroll down to the “Redirect URL” part and fill up the redirect URI.

    • The redirect URI should be like the following pattern:

      • https://api-{your-instance-name}.toucantoco.com/oauth/redirect?type=Hubspot

        Hubspot app Redirect URI configuration screen
  • Scroll down to the Add a required scope part:

    Hubspot app Scopes configuration screen
  • Then add any combination of the following scopes :

    • contacts
    • content
    • forms
    • business-intelligence
    • e-commerce
  • Note that the connector will not work on certain types of data if a scope listed above is not added to the required scopes of your HubSpot application.

  • Do not forget to click on the Save button at the bottom of the page when all your modifications are finished.

How to connect

Once your HubSpot application is created, you will need to set-up the client_id and client_secret credentials of your previously created application.

Look at Set up OAuth2 credentials for your platform for more information.

Data provider configuration

Once the credentials are set-up, you will need to configure your data provider. All of the pulled data is taken from your HubSpot application, whether it’s about email campaigns, companies, deals, etc.

Go to the connectors interface from the DATA section in the Toucan Studio:

Hubspot connector creation step 1

Click on the ADD A CONNECTOR button:

Hubspot connector creation step 2

In the search bar, search for the hubspot connector by typing hubspot, then click on the connector:

Hubspot connector creation step 3

A pop-up window will open and look like this, click on the account that you wish to link to your connector:

Hubspot connector creation step 4

On the next window, all the scopes that are required by the application are displayed; a confirmation is required to link your connector to your hubspot account, just click on Connect app to do so:

Hubspot connector creation step 5

You will be redirected to your instance with a new popup with two fields: name and Auth Flow ID. Auth Flow ID should be pre-filled and name empty.

Fill the name part and save your new connector:

Hubspot connector creation step 6

Data source configuration

Once the data provider is configured, it is possible to add one (or more!) data source that will provide data for your apps.

The HubspotDataset parameter is needed to define the kind of data you want to query in the following list:

  • contacts (default value)
  • companies
  • deals
  • products
  • web-analytics
  • email-events

The HubspotObjectType and parameters parameters are needed for the web-analytics dataset:

  • parameters is a dict-like object that will contain filters that you want to use to filter the resulting data
    • The key must follow this schema: objectProperty.{property}, where property is a value defined in HubSpot’s documentation
    • The value can be anything
  • HubspotObjectType has only one value for now (contact) but this may evolve in the future

Once you are all set, just hit SAVE and voilà!

Hubspot connector data source creation

Clickhouse connector

This connector can be used to connect to a Clickhouse Database. clickhouse documentation

How to connect ?

This connector is quite straight-forward to configure.

Data provider configuration

Only few fields need to be filled in the Data Provider configuration:

  • Name
  • Host it could be an IP address or an hostname (e.g localhost)
  • Port an integer, by default clickhouse runs on port 9000
  • User
  • Password
  • ssl_connection a checkbox to enable SSL wrapped TCP connection
Clickhouse Data Provider Example

Data Source configuration

The Clickhouse Data Source configuration form is like the other SQL data source, available fields are:

  • Database, a dropdown list of available databases
  • Table, a drop down list of available tables. The list will be populated only when a database is selected.
  • Query, a string field where you can write your SQL query, if left blank a select * from Table limit 50 will run
  • Domain, the data source’s name.

The configurations below will help you to fill the form:

Clickhouse Data Source Example 1
Clickhouse Data Source Example 2

And that’s all, your Clickhouse Connector is ready to be used ! 🚀

SOAP Connector

This Connector is a generic connector to query SOAP APIs. It requires an URL to the Service definition file (WSDL file) to load the available services and can handle various authentication protocol, provided that credentials can be passed through a header.

Data Provider’s configuration

Add Connector
  • Look up for SOAP in connector’s list
SOAP LOGO

You can now configure the Data Provider :

  • Fill the name field with a relevant name
  • Headers is an optional dict with authentication information e.g {"Authorization": "Bearer 1234567"}
  • Wsdl Endpoint is a mandatory URL pointing to the SOAP service definition file (WSDL File) e.g: https://example.com/services/service1?wsdl
SOAP Connector Form

Finally, you can configure the Data Source

  • Fill the domain field with a relevant name
  • Select a Method from the dropdown list. It will be auto filled if the connection to the WSDL file is effective
  • Give the required parameters in the Service Parameters field as a dict. To help you, the tooltip (? above the field) is populated with the services definitions.
  • Optionally, fill the Flatten Column field with a list of column names where the data is nested (i.e the column is a dict)
SOAP DS Form

Below is an example of Tooltip

SOAP DS Tooltip

Your SOAP Connector is now configured 🚀

Salesforce Connector

This connector is dedicated to extract CRM data from the salesforce REST API. First step is to create a connected app in Salesforce.

Connected App creation

Login to your Salesforce application.

On the top right of the screen click on setup

Salesforce Setup

Then, on the left bar click on Apps > App Manager

Salesforce Setup 2

and create a new connected app by clicking on New Connected App.

Salesforce Setup 3

You will then be redirected to the app’s creation screen. You can get a configuration example in the image below :

Salesforce Setup 4

Finally, you will be redirected to the Connected App configuration screen where you’ll find the client id & client secret for your app.

Use them to configure the oAuth credentials in Toucan’s credentials manager.

 Data Provider’s configuration

Once the credentials are configured, add your connector:

Navigate to the connector interface from the DATA section in Toucan studio.

Connector's Tab
  • Click on the Add a connector button
  • Then search up the connector from the list and select the Salesforce connector.
Salesforce Setup 5

A pop-up window will open to authorize the connected app to access your data:

Salesforce Setup 6

This authentication phase can be quite challenging, this stackoverflow thread is really helpful for troubleshooting.

After the authorization process, fill the Data Provider’s configuration form. The Data Provider’s configuration form will pop-up, you need to fill only 2 parameters:

  • Name, for example “Saleforce”
  • Instance Url, you can retrieve this information from Salesforce in settings, company information and then search for “instance”.
Salesforce Setup 8

 Data Source configuration

There are 2 fields to fill,

  • Domain, for example “Accounts”
  • Query, here an SOQL query is expected to extract the data from the API. Click here for examples.

And you’re all set ! Your Salesforce Connector is ready to rumble !

Facebook Ads connector

This connector is used to retrieve data from the Facebook API: Facebook Developers Documentation

Prerequisites

In order to be able to link your facebook app to our connectors you will need the following :

  • A Facebook account, this account needs to have a developer access
  • An application with reading rights on Ads following the app creation process below.

Create a Facebook application

With your Facebook account:

  • Head over to your Facebook developper account and click on My Apps:

    Facebook Ads my apps button
  • In your apps manager, click on Create App:

    Facebook Ads Create App button
  • On the following modal, click on Manage Business Integrations then on Continue:

    Facebook Ads Manage business integrations
  • Fill the following form then click on Create App:

    Facebook Ads Manage business integrations
  • You should then be redirected to the following screen, you will need to add the Facebook Login product in order to be able to pull data from your application through OAuth:

    Facebook Ads Add Oauth
  • Head over to the Settings screen, under the Facebook Login product:

    Facebook Ads Facebook Login Settings
  • Fill the Valid OAuth Redirect URIs field with your redirect URI then hit the Save Changes button

  • The redirect URI should be like the following pattern:

    • https://api-{your-instance-name}.toucantoco.com/oauth/redirect?type=FacebookAds

      Facebook Ads Facebook Login Settings

How to configure the credentials on Toucan Toco

Once your Facebook application is created, you will need to register the client_id and the client_secret credentials of your previously created application.

Retrieve your client_id and client_secret

  • Head over to the Basic screen under the Settings category:

    Facebook Ads App Settings
  • Your client_id is the App ID and your client_secret is the App Secret, you’ll need to click on the Show button and enter your account’s password to get the client_secret:

    Facebook Ads crdentials

Once you have your credentials, look at the Set up OAuth2 credentials for your platform for more information.

Data provider configuration

Once your application is created and your credentials are set-up, you will need to configure your data provider.

  • Head over to the connectors interface from the DATA section in the Toucan Studio:

    Facebook Ads add connector step 1
  • Click on ADD A CONNECTOR and search the Facebook Ads connector in the list, then select it:

    Facebook Ads add connector step 2
  • A pop up window will appear and will ask your confirmation to link your Facebook app to your Toucan Toco instance:

    Facebook Ads add connector step 3
  • Then you’ll see the following modal, name your data provider then hit the save button:

    Facebook Ads add connector step 3

Data source configuration

Once the data provider is configured, it is possible to add one (or more!) data source that will provide data for your Toucan app.

The FacebookAdsDataKind parameter is needed to define the kind of data you want to query, as of today the following data is retrievable:

You will need to specify your account_id in the parameters dict of the data source.

You can find the account_id in your Ads Manager:

Facebook Ads add account id

By default, Facebook’s API returns only the ID of the objects, other fields are retrievable when specified in the data fields field; these fields must be comma separated.

Once everything is all set, just hit SAVE and voilà !

Facebook Ads final screen

Set up OAuth2 credentials for your platform

A lot of modern APIs rely on the OAuth2 protocol for authorization, as an industry-standard. It allows a client to easily authenticate and authorize access to target resources. You may not be familiar with the term, but you actually run through the OAuth2 protocol when you login to some of your apps and authorize access to your data via this kind of popup:

google sheet redirection

In Toucan Toco, as an administrator you can easily setup OAuth2 credentials for your platform. Once setup, any App Builder on your platform will be able to create a connection to Google Sheets in just a couple of clicks!

Let’s see how this works!

From the apps store, click on the “Admin area” button, and then click on the “Connectors” button:

credentials button

You will land on a interface listing connectors for which you can add credentials. When no credentials have been filled for a given connector, the status will appear as “Not configured”, in red. To set new OAuth2 credentials, click on the configure button:

credentials listing

Now you can enter the client ID and client secret of your OAuth2 app (that you will get by contacting your data provider, or sometimes by following an online procedure like in the Google console):

credentials config examples

You’re all set!! Now your OAuth2 connectors are ready to be used in all the apps of your platform, with a super easy setup for any app builder!

Below is the list of current Toucan connectors that rely on the OAuth2 protocol for authorization:

  • Aircall
  • Google Sheets
  • Github
  • Salesforce
  • LinkedinAds
  • Google Adwords