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.

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.

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 😄

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.

  • Select the Add a connector
  • Then search up the connector from the list and select the 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.

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

  • 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.
  • Now you can finally use this data from the data explorer of Toucan to build your beautiful Toucan charts 😄.
Complete example

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

DATA_PROVIDERS: [
    name: "open-data-paris"
    type: "HttpAPI"
    baseroute: 'https://opendata.paris.fr/api/'
    template:
        headers:
            requested-by: 'Toucantoco'
]
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:

{
  "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.

MySQL connector

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

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

  • Select the Add a connector
  • Then search up the connector from the list and select the MySQL 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 😄
  • 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.

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 !! 😄

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!

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

  • Select the option: Add a connector
  • Then, simply search up the required connector from the list and select the MongoDB connector.

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 😄

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

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! 😄

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)

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

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

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 😄

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

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! 😄

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)

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

  • Select the Add a connector
  • Then search up the connector from the list and select the AWS Redshift connector

Data provider configuration

Firstly, we have to fill in basic connection details, like the host (either choose between providing the host / hostname (more dynamic option)), port details, username & password, timeout.

Then click 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 raw 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, LOAD / LIVE, the QUERY that we would want to use, and a few other parameters.

Tip

You can pretty much query tables, views, cubes / buckets as well, right from the connector interface :)

The database I have chosen is pfmegrnargs and in this case, I am actually joining 2 tables and then querying the output of that joined table.

Also, I want to use LOAD 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 SQL syntax to query the desired results into Toucan charts. You can pretty much write simple select to complex queries like subqueries, joins, querying views etc

You have successfully connected to the AWS Redshift data source and 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! 😄

Google Sheets Connector

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

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

  • Select the Add a connector
  • Then search up the connector from the list and select the Google Sheets connector.

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.

After logging in, you shall be redirected back to Toucan, where you will have the required attributes auto-filled by Toucan 😄.

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 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:

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 😉

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!! 🔥