# Welcome to Toucan

**Toucan is a customer-facing analytics platform that empowers companies to drive engagement with data storytelling**. With the best customizable end-user experience across any device, over 4 million Toucan stories are viewed each year. Toucan’s no-code, cloud-based platform cuts development costs and time to value with a fast, seamless implementation.\\

<figure><img src="/files/J9l31hO1cF6Lkm2g39vb" alt=""><figcaption></figcaption></figure>

**This documentation, meant as a user manual 📖, will help guide you in this journey.**

<table data-card-size="large" data-view="cards"><thead><tr><th></th><th></th><th></th><th data-type="files"></th><th data-hidden data-card-cover data-type="files"></th><th data-hidden data-card-target data-type="content-ref"></th></tr></thead><tbody><tr><td>Setting connections to data providers, doing light data prep, and building business metrics based on your data</td><td></td><td></td><td></td><td><a href="/files/Nfq5GtwAm5hfRQpMlPCh">/files/Nfq5GtwAm5hfRQpMlPCh</a></td><td><a href="https://github.com/ToucanToco/doc-v3/tree/main/broken-reference/README.md">https://github.com/ToucanToco/doc-v3/tree/main/broken-reference/README.md</a></td></tr><tr><td>Creating visualizations and broadcasting them on impactful layouts</td><td></td><td></td><td></td><td><a href="/files/uii43z2foS4m2NHZEcLb">/files/uii43z2foS4m2NHZEcLb</a></td><td><a href="https://github.com/ToucanToco/doc-v3/tree/main/broken-reference/README.md">https://github.com/ToucanToco/doc-v3/tree/main/broken-reference/README.md</a></td></tr><tr><td>Share insights and drive action plans</td><td></td><td></td><td></td><td><a href="/files/vgfK4rMarpmhgi3nqThS">/files/vgfK4rMarpmhgi3nqThS</a></td><td><a href="https://github.com/ToucanToco/doc-v3/tree/main/broken-reference/README.md">https://github.com/ToucanToco/doc-v3/tree/main/broken-reference/README.md</a></td></tr><tr><td>Deploy Toucan and configure user access</td><td></td><td></td><td></td><td><a href="/files/fHyfgV6Vtz5cJUIfd9Ct">/files/fHyfgV6Vtz5cJUIfd9Ct</a></td><td><a href="https://github.com/ToucanToco/doc-v3/tree/main/broken-reference/README.md">https://github.com/ToucanToco/doc-v3/tree/main/broken-reference/README.md</a></td></tr></tbody></table>


# Technical resources


# Toucan stack

## Introduction

This documentation aims to guide our partners through Toucan Toco architecture, introducing design choices and the security norms it respects.

Specific emphasized blocks like this one provide a high-level view in order to keep things simple and quick while the rest covers topics in depth.

**For business owners and administrative users**, this will show you an overview of our architecture and answer some of your questions about how Toucan works. You can forward it to your IT department if they need more information.

**For IT departments & architects**, this gives you a complete overview of the architecture of our apps, and will help you identify how Toucan can be integrated easily with your information system. It also describes the prerequisites needed to install our components as self-hosted software.

**For security experts**, this aims to transparently let you assess our security level and answer questions and address possible concerns you have regarding our authentication and authorization processes. It also shares the practices and processes we implement in our own information system to ensure that the data you transmit to us stays in good hands.

### Definitions

Toucan is a **web application** that allows you to display your data for your customers through data storytelling. Web applications are served through an HTTP server and an HTTP client, which is often the web browser.

#### The HTTP client / the Web Browser / the user

**Communication**

The HTTP client, often named the Web Browser, or simply the user in our diagrams, is **the end user**.

The end user is the one who interacts with the web application and is the one who sees the web page. The end user is the one who create values to the application, and therefore, our business logic is developped around the expected demands of the end user.

Technically speaking, the client doesn't simply fetch data from the server, but uses the HyperText Transfer Protocol (HTTP), which is a TCP protocol used to download HTML (HyperText Markup Language) files and many other files like CSS and JS files to display the web page.

To communicate securely with the server, the client uses TLS/SSL (Transport Layer Security), which is a additional protocol that is added on top of TCP and encrypts the communication between the client and the server.

|                      |
| -------------------- |
| Application (HTTP)   |
| TLS                  |
| Transport (TCP)      |
| Network (IP)         |
| Data Link (Ethernet) |
| Physical Layer       |

Any application data is encrypted *before* it is sent over the network. This is also known as "implicit" TLS because both client and server doesn't require to explicitly say that they want to use TLS.

The reasons why the client trust the server is thanks to the common source of authority that is used between the client and the server. This is often the [Certificate Authority](https://en.wikipedia.org/wiki/Certificate_authority) (CA) that is used to sign the server's TLS certificate. These sources of authority are installed directly in the user's operating system.

**Behavior**

Since the client is a web browser, it requires specific files to display a page:

* HTML files, which are the main content of the page like text and layouts.
* CSS files, which are the style of the page.
* JavaScript (JS) files, which are the "client-side" logic of the page, often used for animation and user interaction.

As you can see, after the files are downloaded and the page loaded, **no further data is sent to the server** and everything is happening on the client side. JS files may trigger further HTTP requests to the server, but only to fetch data, or trigger some actions on the server.

Since the files doesn't handle any business logic, these files collectively form the **front end**, which is responsible for everything the user sees and interacts with. In a way, these files are **safe to share to the public** (like embedding a dashboard in to a website) because they don't handle any sensitive data.

Our front end itself is evolving into a collection of independent, reusable components called Micro Front Ends (MFEs). Each MFE is designed to render a specific part of the application based on configuration files and Toucan data.

#### The HTTP server(s) / the back-end / Toucan

**Serving data**

The HTTP server(s), also known as the back-end, or simply Toucan in our diagrams, is the services that provides the *processed* and *formatted* data to the client.

These servers communicate with the database to fetch the data, which is then processed based on business rules to provide appropriate data to clients or other services.

![Basic application architecture](/files/EYlXbHUtje5Ln3kQ8ev4)

Toucan's value resides in the way we transform the data into a beautiful and presentable format for the end users.

**Micro services**

Similarly to the front-end, the back-end is splitted into multiple services. Each service is responsible for a specific part of the application and provides their own value to the whole application.

This often contrasts with the monolithic architecture, where the business logic is handled by a single service.

The choice of a microservice architecture is often done for several reasons:

* **Single Responsibility Principle**: Each service is responsible for a specific part of the application, making it easier to maintain and scale.
* **Maintainability**: Each service can be independently tested and deployed, making it easier to identify and fix issues.
* **Scalability**: Each service can be scaled independently, making it easier to handle high traffic loads.

In large teams, it is often recommended to use a microservice architecture, as it allows for better maintainability and scalability.

**Roles**

Our services are split into the following roles:

* The **"front-end"** service (or reverse proxy), which serve the front-end files.
* The **authorization** service, which store permissions rules and authorization policies.
* The **authentication** and **identity** service, which permit users to access the Toucan application.
* The **workspace** service, which handles various workspace management tasks like authorization and user session. It often communicates with the authorization service and authentication service.
* The **dataset** service, which store dataset configurations.
* The **layout** service, which store the configuration of the layouts of the dashboards.
* The **data execution** service, which executes the queries to fetch and transform the data from data sources based on the dataset configurations.
* The **databases** and **stores**, which store the user data.
* And various utility services like the notification service, etc.

All these services exchange between them with various standard protocol on top of HTTP, such as [REST](https://en.wikipedia.org/wiki/Representational_state_transfer) or [GraphQL](https://en.wikipedia.org/wiki/GraphQL) APIs.

## Technical Stack Details

This section provides a detailed insight into the architecture and components of Toucan, with a focus on the underlying technologies and services used to build the application.

We use the [C4 model](https://c4model.com/), a visual representation of Toucan software system.

### Detailed Workflow

#### System Context Diagram

The system context diagram explains the external communication between Toucan and its surrounding environment.

This is a way to expose all the interfaces (APIs) of each services of Toucan. Knowing these services do internal-external communication, they are all encrypted and secured with TLS.

![System Context Diagram](/files/dd5GHcSqlYiWZeGKcbTc)

This diagram provides an overview of Toucan software system and its surrounding environment showing the main components and how they interact each other.

In general, the user communicate with Toucan on two entrypoints:

* The reverse proxy, which serves the front-end files, but also handles the authentication of requests.
* The authentication service, which is use to initiate the authentication process.

#### Container Diagram

The container diagram provides a detailed view of the software system, illustrating its high-level components and their interactions.

![Container Diagram](/files/QQ2zNQfgdNTuUCpsKeb0)

For clarity, some components have been hidden from the diagram. Two of the most important are the cache and the flag manager. The cache is connected to nearly every service, and the flag manager, which controls the activation and deactivation of features, is also connected to most services.

This diagram shows that the embed doesn't communicate with the host website; instead, users communicate directly with the services. The external website simply inserts a script from Toucan used to trigger the client to actually load the dashboard from the Toucan services.

The diagram makes it easy to distinguish the different types of services and their roles, as discussed previously. You can clearly see which business components are closer to the presentation layer and which are closer to the data layer. For example, the data execution service is closer to the data layer because it is responsible to transform the customer's data, which is stored in external data sources, or our Cloud Object Storage.

To describe furthermore the diagram:

* **Black bold arrows** describe the main business flow of Toucan.
* **Black thin arrows** describe the communication between services to achieve a business rule.
* **Purple arrows** permits to distiguish authorization calls, which are simple calls to the authorization service.
* **Red arrows** shows the connections with the storage.

We make sure that:

* Red arrows (storage) are never exposed directly to the user.
* Black bold arrows (internal-external communication) are secured with TLS and are encrypted.
* Black thin arrows (internal-internal communication) are secured internally through private networks.
* Purple arrows (authorization) secures most of the business rules.

{% hint style="info" %}
**NOTE**: The diagram might not be 100% accurate, but it provides a good overview of the architecture.
{% endhint %}

### Open Source

We also contribute by publishing some of our work in the open source community.

Feel free to look at [our public repositories on GitHub](https://github.com/ToucanToco).


# Security


# Application Security

## Authentication

Toucan Toco allows users to authenticate in two different ways:

* By using their own list of accounts with usernames and passwords
* By leveraging your organization's SSO (Single Sign-On)

SSO can be configured using [SAML2](https://en.wikipedia.org/wiki/SAML_2.0) or [OpenID Connect (OIDC)](https://en.wikipedia.org/wiki/OpenID#OpenID_Connect_\(OIDC\)) authentication protocols.

### Local Accounts

Toucan has its own database of users. Basic administrative and viewer accounts are provided when the server is deployed.

#### Password Policy

We force users to respect the following password policy when creating or modifying their passwords:

* Password's length must be more than 8 characters.
* Password mustn't contain the user name.
* Password mustn't be present in a list of common passwords (e.g. `1233456789` or `quertyuiop`).

#### Password Storage

Toucan never stores the passwords in plain text, nor encrypted, nor encoded.

We follow the OWASP recommendations:

* The passwords are hashed and stored using the [Argon2](https://en.wikipedia.org/wiki/Argon2) hashing algorithmn winner of the 2015 [Password Hashing Competition](https://en.wikipedia.org/wiki/Password_Hashing_Competition), resiting both side-channel and GPU-based attacks.
* Each password gets a per-user random salt (stored alongside the hash and iteration count in the credential data).
* When the hashing algorithm (or any parameter) is updated, we force users to update their password.

### Anti-bruteforce Mechanism

We provide multiple layer of protection against brute-force attacks:

* **Brute-force protection log-based**: We use [Crowdsec](https://www.crowdsec.net/) to prevent SSH or HTTP [brute-force attacks](https://en.wikipedia.org/wiki/Brute-force_attack) on all our servers.
* [**Web Application Firewall**](https://en.wikipedia.org/wiki/Web_application_firewall): Using the same solution, by analysing incoming requests, we can also prevent attacks to ever reach our API endpoints.
* **IP Reputation system**: Using the same solution, we prevent known [botnets](https://en.wikipedia.org/wiki/Botnet) to attack our API endpoints, and challenge possible attackers to prove they are not a bot.
* **Applicative brute-force protection**: The authentication layer itself has brute force protection on its own using **rate limiting** and by **locking accounts** after too many failed attempts.

We have further mechanisms in our infrastructure to prevent [denial of service attacks](https://en.wikipedia.org/wiki/Denial-of-service_attack).

### Multi-factor Authentication

We provide these mechanisms:

* Time-based one-time password (TOTP)
* WebAuthn/Passkey
* Recovery codes

We can also provide SMS 2FA, but it's not recommended for production usage, and you'll need to contact the sales team.

### Single Sign On (SSO)

#### OpenID Connect

Toucan can act as an OIDC Client, allowing users to connect using your Identity Provider (IdP).

We use the Authorization Code Flow with PKCE (Proof Key for Code Exchange) to authenticate users.

We need the following configuration fields to be able to use your IdP:

* The Client ID: Also known as the Username, is used by our identity provider to fetch access tokens from your IdP.
* The Client Secret: Also known as the Password, is used by our identity provider to fetch access tokens from your IdP.
  * Other credentials method can be supported such as signed JWT assertion.
* The Discovery URL: A document containing information about your IdP, such as:
  * The issuer: The identifier of your IdP.
  * The authorization endpoint: The login page of your IdP.
  * The token endpoint: The endpoint to fetch access tokens from your IdP.
  * The user info endpoint: The endpoint to fetch user information from your IdP.
  * The JWKS endpoint: The endpoint to fetch public keys from your IdP, to validate JWT.
  * The supported scopes: The scopes supported by your IdP. Toucan will request `openid email`.
* The button text: The text to be displayed on the login button.

If the Discovery URL is not provided, you must provide the information manually.

We'll then provide the **Redirect URI** (also known as Callback URI) which must be whitelisted on your IdP.

#### SAML2

Toucan can act as a Service Provider (SP), allowing users to connect using your Identity Provider(IdP).

We use the SAML2.0 protocol to authenticate users with these settings:

* Redirect binding for the authentication request.
* Post binding for the authentication response.
* Required signed authentication response.

We need the following configuration fields to be able to use your IdP:

* The Metadata URL: A document containing information about your IdP, such as:
  * The IdP Entity ID: The identifier of your IdP.
  * The authentication endpoint for the "Redirect" binding: The login page of your IdP.
  * The public certificate used to verify the signed authentication response.
* The button text: The text to be displayed on the login button.

If the Metadata URL is not provided, you must provide the information manually.

We'll then provide the **SP Entity ID** and the **Assertion Consumer URL** (also known as Redirect/Callback URI) which must be whitelisted on your IdP.

#### Additional features

We also support the following features, upon request:

* [Back-channel logout](https://openid.net/specs/openid-connect-backchannel-1_0.html): Propagate the logout from the IdP to the clients.
* Post-login redirect URI: reach a specific page in Toucan after login.
* [Post-logout redirect URI (RP Initiated Logout)](https://openid.net/specs/openid-connect-rpinitiated-1_0.html#RPLogout): Propagate the logout from the client to the IdP.
* IdP Default Redirect: Hide the login page and redirect the user to the IdP automatically.
* Force re-authentication upon reaching the login page.
* SSO Permissions Provisioning.

### Session Management

Toucan uses a session token stored in a cookie to persist the session. The active session last 24h, and can be refreshed until one month.

Upon expiration, the user will be asked to log in again.

An account page is available to users to manage their session (they can see active sessions on which device they are logged in, and log out of them).

## Account administration and permissions management

### User Management

Admistrators has access to a panel allowing:

* Review of the accounts and their privileges
* Modification of accounts (privileges, password, etc.)
* Deletion of accounts

### Access Control

Permissions follow the RBAC (Role Based Access Control) model:

* **User-specific permissions**: Users can have permissions to resources.
* **Group-based permissions**: Groups can have permissions to resources. Users can be assigned to one or multiple groups.
* **Multiple level of permissions for each resource**: Permissions of a user for a small-app can be "none", "viewer", "validator", or "editor". A user can be "admin", and will have access to all the resources of the small-app. More details in [user management](/administration/managing-users/users).

Permissions allow users to configure:

* Access a small-app.
* Visiblity of stories and dashboards.
* Access to data through filters and template variables.

## Audit Logs and Monitoring

Every user's action on the Toucan platform is logged such as:

* Loading of new data.
* Processing of data.
* Releasing new versions of data to users.
* Successful and failed login attempts.
* Access token generation, Session creation.

Depending on the level of importance of the logs, the retention period can be between 30 days and 1 year. Anything related to authentication will be stored for 1 year.

## Data security

Every commnucation between services and clients are encrypted in transit using TLS, or by using an encrypted private network using packet encapsulation.

Every data stored in Keycloak is encrypted at-rest on a S3 using SSE-S3 (or its equivalent). Data stored in databases use block devices which are encrypted at-rest. The keys used for encryption are stored by our cloud providers.

Every database requires a credential, and every service uses its own credentials, which has different privileges.

## Additional documents

Upon request, we can provide additional documents to help you understand the security of the platform (including incident response, etc.).


# Source Code Quality

## Source Code Quality

We consider code quality to be very serious. We set our standards way above the "good enough" level to deliver a very high quality product.

### CI/CD

We have implemented a CI/CD([Continuous Integration](https://en.wikipedia.org/wiki/Continuous_integration) / [Continuous Delivery](https://en.wikipedia.org/wiki/Continuous_delivery)) pipeline based on [Jenkins](https://www.jenkins.io/) where version control, build, tests and deploy are mainly automated.

#### Pair Programming & Code Reviews

Each line of our code is often produced by not only one but two engineers, ensuring design decisions are always subject to debate and approval.

We reinforce this by protecting our master branches and ensuring every new code snippet is reviewed by at least one other member of the team in a Pull Request. These discussions are logged for documentation and new member training purposes.

During these code reviews, we're particularly attentive to some basic concerns that every developers should know such as [the top 10 security risks published by the OWASP](https://www.owasp.org/index.php/Top10#tab=Main).

#### Testing Policy

Unit and integration tests are systematically carried out on code that is produced.

Each push of new code is tested against our test portfolio in a new docker container.

Merging development branches into our main branch is not allowed if the tests fail.

Automated tests are performed each night.

#### Deploy

If automated tests are green, we deploy on nightly instances.

### Security Dependencies Policy

With each monthly release we update our dependencies in order to integrate their latest security patches.

We use [Github](https://docs.github.com/en/code-security/dependabot/dependabot-security-updates/configuring-dependabot-security-updates) and [pyup.io](https://pyup.io) to track and report vulnerabilities in our code dependencies.

Both services track public vulnerabilities listed on [MITRE's Common Vulnerabilities and Exposures (CVE) site](https://cve.mitre.org/).

When they receive notification of a newly-announced vulnerability, a security alert is sent to us with the details (which part is affected, how to correct it...).

For each alert we receive, a bug ticket is created in our backlog and is attributed a high priority.


# Global Security Practices

## Global Security Practices

### System User Management

User privileges, accounts and SSH keys are managed by our [Ansible playbooks](https://www.ansible.com/).

Adding a new user or removing an outgoing employee is fully automated.

### Global Password Management

We use a password manager to share all passwords, secrets and keys between related teams.

Passwords are **never** shared any another way.

Sharing is managed according to our groups and hierarchy policies set by the Toucan Toco administrators.

### Toucan's Hardware Hardening

All employees' mobile devices are enrolled in our Mobile Device Management system which imposes a set of rules like to have a lock screen, to encrypt the partition.

Toucan Toco administrators can also erase any mobile devices remotely.

Computer data partitions for all the team are completely encrypted.

### Office Access

Office access is only granted by building badges.

All building badges have a unique ID associated to each employee or visitor.

### Employee Departure

A procedure for employee departure is systematically applied when an employee leaves the company.

This procedure includes:

* retrieving the building badge
* disabling email, the password manager and SSO accounts
* removing data on laptops and mobile devices
* removing access to the infrastructure (if the employee is an admin)

This procedure is regularly updated and tested.

A large part of this procedure is fully automated by our [Ansible playbooks](https://www.ansible.com/).

### Office Network

To respect best practices, dedicated [VLANs](https://en.wikipedia.org/wiki/Virtual_LAN) have been configured to isolate the employees' network from the visitors' network.

All Wifi networks are protected with a dedicated [WPA2](https://en.wikipedia.org/wiki/Wi-Fi_Protected_Access) configuration.

### Audits

We regularly challenge and test what we do, create and manage.

For example, we test our backup restoration process every month.

We also audit our infrastructure and our application security every year by external resources.

All audits are made on our current master version which is available on [demo.toucantoco.com](https://demo.toucantoco.com).

Please note [demo.toucantoco.com](https://demo.toucantoco.com) is a real production instance with fake data, we apply the same security and monitoring policies to all our production instances.


# Security of Docker Images

We use containers in production and for our self-hosted package. This document explains how we handle the security of our containers. We implement the following measures to make sure that our deployments are secure.

## The native security of Docker images

### Build once, Run anywhere

We do not deploy our software directly on machines, but instead depend on container runtimes like [Docker](https://www.docker.com/) or [ContainerD](https://containerd.io/). These runtimes are responsible for the security of the containers, and are also responsible for the security of the underlying host.

These runtimes requires container images. A container image is a multi-layered Linux filesystem. To build a container image, we use an OS image containing all the dependencies needed to run a specific application, also known as "base image". We can add our own layers to the base image to build the complete container image, which is then pushed to a container registry.

![A machine running with containers.](/files/rcuV8hXpau5OouC4f87U)

Using this technology, we are able to make sure that the behavior of the containers is the same regardless of the underlying host.

### Host-Container security

Since we use container runtimes, the host is not directly affected by the vulnerabilities of the container images. The software is isolated from the host.

It's the responsibility of the container runtimes to deny unwanted privileges access to the host, and it is our responsibility to correctly configure the container runtimes to disable these privileges.

### Secure base images

Since we depend on base images, we make sure that they are secure.

**Frontend**

For the frontend, we build static assets and copy them on the `nginx:<version>-alpine-slim` container image, a stripped down version of nginx, drastically reducing the attack surface and the maintenance cost, which is itself built upon `alpine:3`.

The `alpine:3` is known for the very low attack surface and low vulnerability. Coupled with the stripped down version of nginx, there is rarely any vulnerability.

**Python projects**

We use the `ghcr.io/astral-sh/uv:python<py-version>-<debian-version>-slim` base image, which is itself built upon `python:<py-version>-<debian-version>-slim`, a stripped down version of python, which is itself built upon `debian:<version>-slim`, a stripped down version of debian.

The `debian:<version>-slim` is known for having a good track of their vulnerabilities. But, due to their release process, some vulnerabilities may show in reports. However, this is mostly false positives, or unexploitable vulnerabilities.

You can check the [Debian Security Tracker](https://security-tracker.debian.org/) for more information.

**NodeJS projects**

We use the `node:24-alpine` base image, which itself is built upon `alpine:3`.

**Rust projects**

We use the `debian:bookworm-slim` and `alpine:3` base images depending on the requirements of the project. Some dependencies requires C libraries which may or may not be available in `alpine:3`.

**Go projects**

We do not use any base image for Go projects, and run from scratch. Sometimes, we use `busybox` to add small debugging tools.

Go projects are statically compiled using the embedded C library inside the Go runtime. *CGO* is not enabled.

This offer the advantage of having no attack surface based on the base image.

### Summary

The containerization of our applications allow us to make sure the underlying host is not affected by the vulnerabilities of the container images.

To avoid a unmaintainable infrastructure, we use container images to offer the required dependencies, but also to easily update the base image when needed.

The chosen base images are known to be secure and maintained by a trusted source.

## Container Image Management

### Trusted container registries

We mainly use [Red Hat Quay Container Registry](https://quay.io/) to host our container images and OCI artifacts like Helm Charts. This registry is managed by the Toucan team, and is automatically scanned by Quay.

![Quay Scan Results](/files/XzcFbomXzd5h4yvz03wz)

We also depend on third-party container images, which are hosted on [Docker Hub](https://hub.docker.com/) or [GitHub Container Registry](https://docs.github.com/en/packages/working-with-a-github-packages-registry/working-with-the-container-registry). Docker Hub also scans their images, and we tend to avoid the GitHub Container Registry unless we know the image is trusted.

This way, the container images are always available and trusted.

### Continuous Container Image Scanning

While Quay scans the images automatically, we also use [Trivy](https://aquasecurity.github.io/trivy/) to scan the images on our side, without the need to wait for Quay.

The scan occurs daily and per-commit, and is run on the CI/CD infrastructure.

Depending on the release cycle and the security policy of the project, we might not be able to offer a fix for a vulnerability immediatly. Generally, the vulnerability reports usually concern third-party dependencies and it is recommended to read the CVE as false positives may be reported.

In any case, we implement strict security policies at runtime to avoid the exploitation of vulnerabilities.

## Deployment details

### Least privileges during deployment

Images are never pulled manually and uses Robots Accounts with read-only access to the registry, avoiding potential Supply Chain Attacks. We also use GitOps and a Zero-Trust infrastructure to deploy the services.

No untrusted users have access to the deployment server, and deployments are never done manually.

### Least privileges at runtime

While images are naturally built with rootless in mind, we also force the container runtime to remove privileges:

* User is always non-root and cannot escalate in privileges.
* Filesystems are read-only.
* Linux capabilities are dropped.

### Strict monitoring

We keep track of metrics and logs of applications to detect undesirable behavior. We use alerts to detect potential vulnerabilities or unexpected behavior.

### High available and self-healing

The services are replicated on multiple machines to avoid single points of failure. In case of DDoS, the unhealthy services are automatically restarted to their default state.

### Attack detection and network hardening

We use [CrowdSec](https://crowdsec.net/) to detect attacks and block them. Attackers with suspect behavior are automatically blocked.

Besides public services, internal services are never exposed to the public internet, and all communication is encrypted using TLS, or, if possible, using mTLS.

### Deployment rollouts and rollbacks

Finally, unhealthy services are **never** served, and the new versions of services are only served when all services are healthy.

This makes sure that the deployment of services is always safe, and broken releases are never deployed.

## Conclusion

Using secure container images with least privileges at runtime is the best way to make sure our infrastructure is secure. No privileges can be gain, no unexpected files can be written, and in case of incident, the infrastructure will automatically heal itself.

We keep track of vulnerabilities, and monitor constantly the health of the infrastructure to offer the best possible experience to our users.

The pipeline is secured from the beginning to the end, and we can be sure that the infrastructure is always secure.

![An extract of our CI/CD pipeline.](/files/NrJ0f6nhua40OrBpf24r)


# Getting Started : Embedded Analytics

## 📖 Introduction

**Welcome!** If you are here, it means you are ready to **dive into the world of embedded analytics** with [**Toucan**](https://www.toucantoco.com/en/). You want to integrate white-labeled analytics into your product to enrich your offering, impress your clients, and accelerate your roadmap. You are in the right place! 🙌

Whether you are a **new client ❤️ ready to get started** or a **prospect ✨ testing the Free Trial**, we will explore together how to navigate effectively to achieve your goals.

<figure><img src="/files/MNhOOmD2BgTRsezJGudj" alt=""><figcaption><p>Welcome on our Getting Started for Embedded Analytics</p></figcaption></figure>

## 📜 Preface

**Audience**

This guide is for product managers, data analysts, and developers integrating embedded analytics into their software with Toucan. It is for those who want to integrate white-labeled analytics efficiently.

{% hint style="info" %}

#### Important Note

This guide does not fully cover authentication management and embedding charts into your code, which are explained in the linked documentation. These aspects are crucial, but this guide focuses on the key steps to get started with embedded analytics. For more details, refer to the associated documentation.
{% endhint %}

## 🎯 Objective of this *Getting Started*

The goal of this guide is **simple**: to support you step by step **in getting started with embedded analytics.**

Before you begin, it is essential to understand **the major steps**. This guide will present these key steps so that you can ultimately **integrate personalized charts directly into your product or web portal**. 📺

*This is not a click-by-click tutorial* but rather a **general guide to help you get oriented**, giving you the necessary foundations to explore. You will find the associated documentation throughout and at the end of this guide.

You will be guided to see how to:

* Connect to data
* Create variables (to personalize the experience)
* Create customized datasets
* Create contextualized charts
* Embed these charts into your interface while managing secure authentication

Toucan can integrate analytics into your product at **different levels of granularity**, from simple and tailored widgets to full dashboards, while displaying the right data to the right person.

{% hint style="info" %}
You can find documentation about our embedded layouts

* [Tiles](https://docs-v3.toucantoco.com/visualizations-and-layouts/apps/home#title)
* [Stories](https://docs-v3.toucantoco.com/visualizations-and-layouts/apps/stories)
* [Dashboards](https://docs-v3.toucantoco.com/visualizations-and-layouts/apps/dashboard-builder)
* [Apps](https://docs-v3.toucantoco.com/visualizations-and-layouts/embedding/integration/embed-an-app)
  {% endhint %}

You just need to iterate gradually to add more analytics after that.

<figure><img src="/files/wMIM7kH8ENRPcHd9K3on" alt=""><figcaption><p>Example of Embedded Analytics (3 tiles on the left)</p></figcaption></figure>

## 🚂 Steps to follow when deploying embedded analytics

### 1) Connect to Data 🔗

To start, create a Toucan App, then [go to the **DataHub**](https://docs-v3.toucantoco.com/data-management/datasources-in-toucan) to create a connection to a data source, such as **PostgreSQL**. This step allows you to establish a solid foundation on which you can build your visualizations.

If you prefer, it is also possible to upload a [flat file](https://docs-v3.toucantoco.com/data-management/datasources-in-toucan/managing-files). This first step is crucial as it provides the necessary data for the following steps.

<figure><img src="/files/V7Bw9cIkTfjPtDcKxD7P" alt=""><figcaption><p>DataHub >> Datasource >> Add a Connector or a File</p></figcaption></figure>

### 2) Create Variables (to Personalize the Experience) 🧪

To provide a personalized experience, it is important [to create variables](https://docs-v3.toucantoco.com/data-management/managing-variables-in-toucan/variables-hub) that will help to adapt the content for each user.

First, go to the administration interface via the App Store to create the user attributes that will be used to personalize the experience (you need Admin rights for this). These attributes can be of different types and named as you wish, for example:

* **Client**: To identify data specific to each client.
* **Region**: To adapt the content based on location.
* **First Name**: To personalize the storytelling with the user's first name.

These variables should, of course, align with your Data Model and will likely correspond to columns.

<figure><img src="/files/GprEx0lkO04RMhHNXaG5" alt=""><figcaption><p>App Store >> Settings >> Users >> Properties</p></figcaption></figure>

Next, return to your Toucan App and go to the **Variable Management menu** (at the top right) of the application itself. Finally, assign default values to these variables to simulate the user experience.

For example:

* **Client**: Apple
* **Region**: California
* **First Name**: Chris

These values will allow you to view the data as if you were a real user carrying these user attribute values, thereby validating the personalization of visualizations in an app-building environment (Toucan studio).

<figure><img src="/files/KdbMUO94Oz2eSyq0Ovrb" alt=""><figcaption><p>App >> Settings >> Variable Management</p></figcaption></figure>

### 3) Create Customized Datasets 🚀

In Toucan, datasets can be saved as Stored (stored in Toucan - [see limitations in the documentation](https://docs-v3.toucantoco.com/data-management/datasets-in-toucan/stored-and-live-datasets)) or Live (leveraging the underlying data source).

{% hint style="info" %}
In embedded analytics, data is often processed in real time, and data is segregated by a “Client Identifier” (or "Product Identifier"), which would be a column in a “[One Big Table](https://medium.com/dbsql-sme-engineering/one-big-table-vs-dimensional-modeling-on-databricks-sql-755fc3ef5dfd)” data model. If the architectural model is different, we can work with you [to find the appropriate solution](https://docs-v3.toucantoco.com/data-management/using-advanced-data-concepts/data-personnalisation-with-user-attributes). We will assume this scenario for this getting started.
{% endhint %}

Data is the foundation of your project. With **YouPrep**, Toucan's data transformation tool, you can adjust data to meet your business needs: grouping data, creating conditional rules, performing joins, etc.

{% hint style="info" %}
Did you know? 👀 YouPrep can even [write the SQL language](https://docs-v3.toucantoco.com/data-management/datasets-in-toucan/preparing-data/youprep-tm-native-sql) for you for certain compatible connectors.
{% endhint %}

<figure><img src="/files/lgsIffh4JYV6feT5YL2E" alt=""><figcaption><p>DataHub >> Create a Dataset >> Play with Youprep!</p></figcaption></figure>

For example, you can apply a [**Filter Rows** step](https://docs-v3.toucantoco.com/data-management/datasets-in-toucan/preparing-data/filtering-with-youprep-tm) on the column that helps identify a client A or client B (e.g., "Client"). This filter ensures that only relevant data for each user is retained. Once the dataset is transformed and saved, it can be reused in your application, providing a personalized foundation for visualizations.

<figure><img src="/files/QWAs0pD62ZAQHXkvgrd2" alt=""><figcaption><p>Use a Filter Rows Step >> Filter our column on a Variable (with a default value already defined)</p></figcaption></figure>

By default, Toucan will use the value defined in the Variable Management menu.

In production, however, Toucan will use the user attribute value carried by the [Json Web Token](https://docs-v3.toucantoco.com/visualizations-and-layouts/embedding/authentication) (see step 5).

<figure><img src="/files/ebNiAi8zXIBQV4O15J1N" alt=""><figcaption><p>Don't forget to save your dataset (live mode here!)</p></figcaption></figure>

### 4) Create charts powered by storytelling 🎨

Now that your data is prepared, it is time to create contextualized charts. [Create a **story** in a Toucan chapter](https://docs-v3.toucantoco.com/visualizations-and-layouts/apps/stories/creating-a-story), consisting of one or two charts, with contextualization (narrative, tips, glossary), [accompanied by **KPIs**](https://docs-v3.toucantoco.com/visualizations-and-layouts/apps/stories/kpis) and filters.

To create these elements, you can follow the various documentation pages [to enrich your Data Story](https://docs-v3.toucantoco.com/visualizations-and-layouts/apps/stories/narrative)! If you want to move quickly, simply create a chart like a bar chart, leaderboard, or line chart. 😊

<figure><img src="/files/IIwSjpKTKWjd4yHwZ9Bv" alt=""><figcaption><p>Chapter >> Create a Story >> Create a chart from your personalized dataset</p></figcaption></figure>

Toucan can [apply filters](https://docs-v3.toucantoco.com/visualizations-and-layouts/apps/filters) to charts via the filter bar at the top, relying on a column from your dataset or another dataset with a column of the same name.

This allows your end users to filter and explore their data!

<figure><img src="/files/9qG9c2ydDxoK5qq3RLwi" alt=""><figcaption><p>On the top of your story >> Create a filter >> then apply the filter in the UI or in Youprep</p></figcaption></figure>

Finally, do not hesitate to complete the Narrative above the chart to give it meaning and make its definition self-explanatory. Variables tied to user attributes can even be directly integrated into the narrative to make the experience more engaging, such as including the user's first name in the text.

We call ‘templating’ syntaxes including variables to make you text (or data) dynamic depending on the context of use. You can refer to the [Templating documentation](https://docs-v3.toucantoco.com/visualizations-and-layouts/creating-visualizations/advanced-chart-configuration/templating-from-charts-dataset#example-in-a-narrative) for this or the reference to variables.

<figure><img src="/files/liaW5QQNQcfDd2tYaxFv" alt=""><figcaption><p>Nice Data Story isn't it?</p></figcaption></figure>

Don't forget to change your colors and logos in the [White Label](https://docs-v3.toucantoco.com/administration/instance-management/customizing-your-instance-whitelabel) and [Customization Menu](https://docs-v3.toucantoco.com/visualizations-and-layouts/apps/customizing-apps).

### 5) Embed Charts in Your Interface with Secure Authentication 👩🏻‍💻

{% hint style="danger" %}
You will probably need a developer to finalize this step if you are a customer. ([cf doc](https://docs-v3.toucantoco.com/visualizations-and-layouts/embedding/authentication))
{% endhint %}

To finalize, it is time to integrate your work directly into your software. Use a Json Web Token (JWT)-based authentication system to dynamically pass user attributes to Toucan. It is like the JWT is an ID card, and the user attributes are the characteristics of a person on the ID card: a number, height, eye color, etc.

Be sure to create [user tokens that carry user attributes](https://docs-v3.toucantoco.com/additional-ressources/tutorials/embed-a-story-with-user-attributes) with the same syntax as defined in the Variable Management section. For instance, if you have a "country" column to filter, it will be easier if your Variable is called "country".

<figure><img src="/files/sQ1N51IGv9lD45XQ1Kf6" alt=""><figcaption><p>For instance: Clement is connected on Breezy, so he can see its own figures compared to Charles!</p></figcaption></figure>

Once the authentication system is in place ([see the associated documentation](https://docs-v3.toucantoco.com/visualizations-and-layouts/embedding/authentication)), don't forget to publish your work in production (Publish Button), then generate an embed by clicking on the "..." in the interface.

<figure><img src="/files/Wnv4bmKiSnhOaOlDqoAJ" alt=""><figcaption><p>In Production Mode >> Click on the "..." next to a Story >> Embed Story</p></figcaption></figure>

Copy the **web component** into your frontend, place the token, and everything is ready! Each user will thus experience a personalized experience based on their data. Here the token was prepared for this demo.

<figure><img src="/files/zWAsAKWOw7Hj1tDt6tTg" alt=""><figcaption><p>Tada 🎉 An Awesome Data Story Embedded into your own frontend!</p></figcaption></figure>

Of course you can dig in the [Integration Documentation](https://docs-v3.toucantoco.com/visualizations-and-layouts/embedding/integration) to deep dive in what we can customize.

## 👩🏼‍🏫 Summary

We have explored together the steps to **get started with** **embedded analytics** using Toucan: connecting to data, creating variables and customized datasets, creating contextualized charts, and finally embedding them in your interface while managing secure authentication.

Once these skills are mastered, the Toucan solution will allow you **to add analytics to your product**, without any code, in just a few minutes, **providing a personalized and engaging experience**.

{% hint style="info" %}
There is much more to discover such as the use of our SDK (lien) to deeply integrate Toucan with your product or whitelabeling to blend Toucan viz within your design.
{% endhint %}

## 👋 Outro

The world of analytics is limitless: your clients probably expect standard indicators shared across your product! 👥 You could even address specific personas or offer a paid analytics module as part of your service offering. 💰 Your website could even feature public analytics or a barometer to raise awareness among your audience about certain metrics! 🌎 In short, data is just waiting for you to bring it to life.😊

{% hint style="info" %}
This **Webinar Replay** could help to understand the same Embedded Analytics path also 🎁 It's free!
{% endhint %}

{% embed url="<https://youtu.be/0zfu3JF3X08?si=xHEdojv2guY172CR>" %}

## 📚 Associated Documentation

* [Datasources in Toucan](https://docs-v3.toucantoco.com/data-management/datasources-in-toucan)
* [Dataset in Toucan (live and stored)](https://docs-v3.toucantoco.com/data-management/datasets-in-toucan)
* [Transform data with YouPrep](https://docs-v3.toucantoco.com/data-management/datasets-in-toucan/preparing-data/overview-of-youprep-)
* [Manage variables](https://docs-v3.toucantoco.com/data-management/managing-variables-in-toucan)
* [Tutorial on data variabilization](https://docs-v3.toucantoco.com/additional-ressources/tutorials/embed-a-story-with-user-attributes)
* [Variability based on your software's architecture](https://docs-v3.toucantoco.com/data-management/using-advanced-data-concepts/data-personnalisation-with-user-attributes)
* [Create a story](https://docs-v3.toucantoco.com/visualizations-and-layouts/apps/stories)
* [Filters](https://docs-v3.toucantoco.com/visualizations-and-layouts/apps/filters)
* [Authentication](https://docs-v3.toucantoco.com/visualizations-and-layouts/embedding/authentication)
* [Templating](https://docs-v3.toucantoco.com/visualizations-and-layouts/creating-visualizations/advanced-chart-configuration/templating-from-charts-dataset#example-in-a-narrative)
* [Easy Reference to Variables](https://docs-v3.toucantoco.com/data-management/managing-variables-in-toucan/easy-reference-to-variables)

If you have **any doubts or questions** after reading this Getting Started guide, feel free [to reach out to us](https://docs-v3.toucantoco.com/additional-ressources/support-for-app-builders)! 💌


# Advanced tutorials


# Embedding a story with user attributes

### Introduction <a href="#introduction" id="introduction"></a>

The process of embedding a story is simple — just copy the HTML script (either iFrame or web component) and use it to embed content wherever needed with the generated token. Additionally, you can use user attributes to ensure that individuals only view content aligned with their access permissions. Before jumping into the tutorial, it's vital to handle a few prerequisites for a smoother process. Follow these steps:

1. [Prepare Data Sources:](https://docs-v3.toucantoco.com/data-management/datasources-in-toucan)
2. [Create Your Story](https://docs-v3.toucantoco.com/visualizations-and-layouts/apps/stories/creating-a-story)

Now that your story is ready. Let's delve into how the attributes work behind the scenes for three users: Max, Amy, and Ben. Each of them holds specific permissions:

* Max: Access to France
* Amy: Access to Belgium
* Ben: Access to both France and Belgium

When users log in, the website generates a Toucan opaque token to authenticate them on embedded content. This token grants access rights to a JWT token containing the user's country as an attribute. Toucan dynamically organizes and presents data based on these values and the user's rights. It's important to note that the dynamic organization occurs after users set up filtering steps, ensuring data is customized to their rights and specified attributes, such as country values.

<figure><img src="/files/bOiI6ghNLTBzaNRsIr77" alt=""><figcaption></figcaption></figure>

Agenda

1. Generate a token with attributes
2. Filter your data based on attributes

### Generate a token with attributes <a href="#generate-a-token-with-attributes" id="generate-a-token-with-attributes"></a>

One of the most crucial steps in the embedding process is the creation of a token. This process includes proving your identity, establishing a secure system, managing your content from an admin center, generating secret keys, and creating secure token to authenticate your embedded content.

For detailed information, please refer to: [Authentication Documentation](https://docs-v3.toucantoco.com/visualizations-and-layouts/embedding/authentication)\
\
To break it down into a few steps:

1. **Generate Client Secret:**
   * [Get a secret key for authentication](https://docs-v3.toucantoco.com/visualizations-and-layouts/embedding/authentication#generate-your-client-secret)
2. **Cryptographic Keys:**
   * Choose between using a single RSA key pair or a JWKS endpoint
   * [If using RSA](https://docs-v3.toucantoco.com/visualizations-and-layouts/embedding/authentication#id-1.-generate-rsa-key-pair), generate a private and public key pair (2048-bit or more) and upload the public key to your admin space.
   * [If using JWKS](https://docs-v3.toucantoco.com/visualizations-and-layouts/embedding/authentication#id-2.-use-a-jwks-endpoint), set up an endpoint returning public keys for added security
3. **Create JWT Tokens with User Context:**
   * This involves creating a JWT token signed with your private key to authenticate your embeds.
   * The token contains information like user details, roles (USER or ADMIN), access rights, groups, attributes, and secrets.
   * Secrets are intended for safeguarding sensitive information, such as tokens used for authentication, enabling secure access to databases, APIs, and other systems.
   * Payload example:
     * `embed_context: {`\
       `"username": "YOUR_USER_EMAIL", // MANDATORY : user id`\
       `"roles": ["USER"], // MANDATORY`\
       `"privileges": { // MANDATORY : user access's right`\
       `"APP-ID": ["PRIVILEGE"],`\
       `},`\
       `"groups": ["USER_GROUP"], // user group`\
       `"attributes": { // everything else you want that can be used for custom permission, queries...`\
       `"ENTITY_ID": "ENTITY_ID"`\
       `},`\
       `"secrets": { // Secrets will not be sent to the front or displayed, allowing data such as credentials or tokens used for authentication to be sent.`\
       `"TOKEN": "ACCESS_TOKEN_FOR_DATAWAREHOUSE"`\
       `}`\
       `}`
   * In our case, we used the following payload,
     * `'embed_context': {`\
       `'username': "test@toucantoco.com",`\
       `'workspace_id': workspace_id,`\
       `'roles': ["USER"],`\
       `'privileges': {`\
       `small_app: ["toucan-tutorial"]`\
       `},`\
       `'attributes': {`\
       `"country" : "France"`\
       `}`\
       `}`
4. **Create Opaque Token:**

* This token does not contain sensitive information but links to the user context provided earlier.
* Use this under the HTML script that you get from Toucan

### Filter your data based on attributes <a href="#filter-your-data-based-on-attributes" id="filter-your-data-based-on-attributes"></a>

\
Go to the story you have already created and add a step to apply user attributes:

* Select the dataset.
* Click "Edit."
* Add a filter step where the country is equal to the following syntax:\
  `{{ (user or {}).get('attributes', {}).get('country', 'Belgium') }}`\
  Note: This code checks if there's information about the country in the give user attribute and if it's available, it provides that country; otherwise, it defaults to 'Belgium'. [More on advance syntax for variables.](https://docs-v3.toucantoco.com/data-management/using-advanced-data-concepts/advanced-syntax-for-variables#user-attributes)
* Save and publish the app.
* Next, go to production mode and copy the HTML script.

Test your HTML script on [CodePen](https://codepen.io/pen/). Insert the token, and remember to adjust the height by adding it to the user CSS section.

For example:

`body {`\
`height:100vh`\
`}`

Click on Select the dataset below to apply the user attribute country in the story below:

{% embed url="<https://demo.arcade.software/3NQfySkawLoBt1AKICFZ?embed>" %}

Congratulations! You have successfully embedded your story in a test environment.


# Dynamic filter with user attributes

To refine your dataset based on specific user attributes, incorporate these attributes directly in the SQL query. This can be particularly useful for personalizing data retrieval or implementing access controls.

**How to Implement:**

* First, switch to SQL code mode in your database tool or SQL editor.
* Next, locate the WHERE clause of your query. This is where you'll add the conditions based on user attributes.
* Insert the user attribute condition. For example, if you want to filter data for a specific country, you might add:

  ```sql
  Where country = '{{ (user or {}).get('attributes', {}).get('country', 'Belgium') }}'
  ```

Example Query:

```sql
SELECT * FROM "breezy"."deals_breezy"
Where country = '{{ (user or {}).get('attributes', {}).get('country', 'Belgium') }}'
```

In this example, we're filtering users based on the country attribute. This method leverages conditional logic to either use the `country` attribute or default to `'Belgium'` if the attribute doesn't exist.

{% @arcade/embed url="<https://app.arcade.software/share/Nibd6cFXviE4lY4n6XY8>" flowId="Nibd6cFXviE4lY4n6XY8" %}


# Dynamic Tables

To further customize your data retrieval based on user attributes, especially when working with dynamic tables in your SQL queries, you can dynamically adjust your query. This approach is ideal for personalizing and refining your dataset extraction from potentially different tables based on user settings or preferences.

**How to Implement:**

1. Switch the SQL editor to SQL code mode.
2. Identify the FROM clause in your query.
3. Integrate the user attribute condition by substituting the placeholder with your desired attribute.

Consider the example below for incorporating a table attribute directly into your query:

```sql
SELECT * 
FROM "breezy"."{{ (user or {}).get('attributes', {}).get('table', 'deals_breezy') }}"
```

The example demonstrates how to dynamically select appropriate table. This method leverages conditional logic to either use the `table` attribute or default to `deals_breezy` if the attribute doesn't exist.

{% @arcade/embed url="<https://app.arcade.software/share/raBTIcTNqt5jSBfUuiDt>" flowId="raBTIcTNqt5jSBfUuiDt" %}


# Dynamic Database

Expanding upon dynamic queries, it's also possible to dynamically select the database itself, guided by user attributes. This approach allows for a more personalized and efficient data handling strategy, tailored to the specific needs and contexts of individual users.

**How to Implement:**

1. Create a new dataset
2. Select "Several databases through variables" option
3. Integrate the user attribute condition by substituting the placeholder with your desired attribute.

Consider the example below for incorporating a database attribute directly into your query:

```python
{{ (user or {}).get('attributes', {}).get('database', 'rdb') }}
```

The example demonstrates how to dynamically select appropriate database. This method leverages conditional logic to either use the `database` attribute or default to `rdb` if the attribute doesn't exist.

{% @arcade/embed url="<https://app.arcade.software/share/ZMIBXRrNp6dau97dmtPG>" flowId="ZMIBXRrNp6dau97dmtPG" %}


# Dynamic Host

If you're looking to connect to a different host to personalize your databases, tables, or filter values, you can easily do so! Just use the same syntax that you used for setting up dynamic filters, tables, or databases. It's a straightforward process, and you'll have your custom setup running in no time.

**How to Implement:**

1. Create or select an existing connection
2. Edit the value with following syntax in mind:

   ```python
   { (user or {}).get('attributes', {}).get('value', 'default_value') }}
   ```

Consider the example below for incorporating a database attribute directly into your query

The example demonstrates how to dynamically select appropriate database.

```python
{{ (user or {}).get('attributes', {}).get('host', '111.111.11.11') }}
{{ (user or {}).get('attributes', {}).get('port', '11111') }}
{{ (user or {}).get('attributes', {}).get('user', 'enter_user_name') }}
```

This method leverages conditional logic to either use the `host, port or user` attribute or default to `'111.111.11.11','11111' and 'enter_user_name'` if the attribute doesn't exist.

<figure><img src="/files/zbuSdu0YBHUPzDp5H3Iv" alt=""><figcaption></figcaption></figure>


# Using the HTTP API connector in advanced use cases

## Overview

Hi dear App Builder,

This documentation showcases advanced use cases using the HTTP API Connector. It will expose three use cases to handle API at Scale:

* Handle Bearer token in Header - Static authentication
* Execute a first API Call for Authentication
* Handle Token in Token - Dynamic Authentication

## Handle Bearer Token in Header - Static Authentication

This use case is useful when you have an API that needs a Bearer Token (Token Access) in the header of your API call. In this situation, we suppose that the access to your data is static. It is assumed that only one “service account” is accessing a non-variable data item.

<figure><img src="/files/P74NoOyYihHuD127bmbq" alt=""><figcaption></figcaption></figure>

In order to set up this you have to create an HTTP API Connector and add Template > Header option as below at the connector level.

<figure><img src="/files/U2k1ufZL1p8MrW8EkZSv" alt=""><figcaption></figcaption></figure>

Then you have to use your API to create a Query from this Connector and call your application Endpoint. The Header with the Bearer Token will be systematically sent to your SaaS API.

<figure><img src="/files/wcvvKKpPNeA1paA1uuS0" alt=""><figcaption></figcaption></figure>

## Execute a first API Call for Authentication

The first use case is a classical one in the software industry. However, APIs often need to use a temporary access token, dynamically retrieved when a first authentication call is made. This access token is then sent during the application call, along with parameters, to retrieve data.

<figure><img src="/files/OzIXIhP5qj0GvU7DIQh3" alt=""><figcaption></figcaption></figure>

This solution is effective, and here again, the account service is static. Depending on the API, some parameters are requested, as in the example below with 4 parameters:

* *a grant\_type*
* *an account*
* *a username*
* *a password*

In this case, the call is encrypted (HTTPS) and sent as a POST. We can do even better in terms of security, as we’ll see in the 3rd scenario.

<figure><img src="/files/pP5nte0SARVdMLMcqFsw" alt=""><figcaption></figcaption></figure>

Again, you have to use your API to create a Query from this Connector and call your application Endpoint.

<figure><img src="/files/wcvvKKpPNeA1paA1uuS0" alt=""><figcaption></figcaption></figure>

## Handle Token in Token - Dynamic Authentication

Finally, the state of the art offers us a third situation, which works very well in an embedded analytics context. In this situation, a software editor (in SaaS for example), wishes to integrate visualizations and show data from Paul to Paul, and data from Mary to Mary, who are two users with different rights.

In this context, the ideal scheme is for the SaaS vendor to manage authentication, with an initial call to its Authentication API. The access token generated by this call can then be sent dynamically to Toucan, in a JWT.

When Toucan is used in an embedded analytics context, it can open this JWT to extract user attributes. These user attributes may contain token access sent by the SaaS embedding Toucan visualizations.

[Authentication — Toucan Doco documentation](/visualizations-and-layouts/embedding/authentication)

Finally, this user attribute containing the access token is sent in the header of an API call, as seen in the first scenario.

<figure><img src="/files/jivfNOCrZWHPcs1QnnDw" alt=""><figcaption></figcaption></figure>

The screenshot below shows the API connector that dynamically injects user attributes into a header. These attributes come from the JWT.

<figure><img src="/files/3TdGcftBgIRu4yDslrMA" alt=""><figcaption></figcaption></figure>

As always, you have to use your API to create a Query from this Connector and call your application Endpoint. Why not with some parameters if needed, here from our beloved Date Selector.

<figure><img src="/files/FLf1RcmemKWsI2x957n4" alt=""><figcaption></figcaption></figure>

Thanks for using Toucan and see you soon to share your experience with our Product!


# Using advanced syntax for SQL queries

### SQL Query Interpolation Syntax

This page provides an overview of SQL interpolation techniques, focusing on how to dynamically inject parameterized values into SQL queries. It explains the differences between the `<%= %>` and `{{ }}` syntaxes for injecting frontend and backend variables, and offers practical examples for various data types and scenarios

#### Variable Injection Sources

* Frontend variables (set in app configuration or by requesters/filters):
  * Use `<%= %>` syntax
* Backend variables (from instance config or user attributes):
  * Use `{{ }}` syntax

#### String or Integer Values from User/Instance Context

For string attributes:

```sql
WHERE INDUSTRY = '\'{{ string_attribute }}\''
```

For numeric attributes:

```sql
WHERE INDUSTRY = '{{ numeric_attribute }}'
```

#### Checkbox Requester (Array) of Strings

Use "IN" instead of "=" and join syntax:

```sql
WHERE CATEGORY IN ('<%= requestersManager.requester_check.join("','") %>')
```

#### List of String Values from User Attributes/Instance Context

```sql
WHERE beer_kind IN ('{{ "\',\'".join(user.attributes.beer_kind) }}')
```

#### Array or List of Integers

For checkbox filter:

```sql
WHERE CATEGORY IN ('<%= requestersManager.requester_check.join(",") %>')
```

For user attributes:

```sql
WHERE beer_kind IN ('{{ ",".join(user.attributes.beer_kind) }}')
```

#### String Template Filtering

Based on user/instance context:

```sql
WHERE beer_kind LIKE '%{{ user.attributes.beer_kind }}%'
```

Based on requester value:

```sql
WHERE beer_kind LIKE '%<%= requestersManager.beerkind %>%'
```

#### List of String Templates from Checkbox Values

```sql
WHERE beer_kind LIKE '%<%=requestersManager.beerkind.length > 1 ?requestersManager.beerkind.join("%' OR beer_kind LIKE '%"):requestersManager.beerkind[0] %>%'
```

#### List of String Templates from User/Instance Context

```sql
WHERE beer_kind LIKE '%{{ ("%\' OR beer_kind LIKE \'%").join(user.attributes.beer_kind) if user.attributes.beer_kind|length > 0 else user.attributes.beer_kind[0] }}%'
```

This structure organizes the information into clear sections with appropriate headers, code blocks for SQL snippets, and consistent formatting.


# Merging filters with our tool

{% hint style="info" %}
Available on-demand and enabled by default for migrated apps from v2
{% endhint %}

We design a tool to help you manage apps with numerous filters, especially migrated ones.

Most of the time, if you end up with too many filters, it's probable that some are duplicated. Check out our step-by-step tutorial that explain the benefits and how to use this new interface.

{% @arcade/embed url="<https://app.arcade.software/share/OthkOj2Sp3BTUubloVO4>" flowId="OthkOj2Sp3BTUubloVO4" %}


# Deep customization chart (CSS)

## Introduction

With the custom CSS feature, Toucan provides you with the tools to go beyond standard customization options. Whether you want to align the application's look with your brand identity or make specific layout adjustments, custom CSS offers complete flexibility. By applying your own CSS styles, you can fine-tune Toucan's elements to meet your needs for branding, accessibility, and design.

This documentation includes practical examples demonstrating how custom CSS can enhance the appearance of your Toucan application, divided into three main areas: a homepage, a chart and a dashboard.

Discover the power of customization with Toucan’s custom CSS feature !

Here’s a sneak peek at how easily you can enhance the look of your application:

<figure><img src="https://lh7-qw.googleusercontent.com/docsz/AD_4nXdw70FlmMIHl4YdSXzBnRS9003XvEG9Ehm5rAea3BhGyPlLVjRY4W853w-Sm7Wx8pOQ1JMb-xqrhOaj3Eii9H-s--XAXEBI1LeC8BuMbzAKXmGTU5qSI1fR97QidrqNec1cVq_Oew?key=JSNMgaamRsns2DBiowR75RYx" alt=""><figcaption></figcaption></figure>

With just a few CSS adjustments, you can transform standard elements into visually engaging, brand-aligned components. Take a look at the before-and-after examples below to see what’s possible!

## Examples of custom CSS

{% hint style="warning" %}
If you notice some CSS styles that aren’t usually part of your app, try refreshing the page. This will load your custom CSS as expected, ensuring that all your personalized styles are displayed correctly.
{% endhint %}

### Homepage

In this example, the homepage reflects a theme for eco-friendly or sustainable branding.

<figure><img src="https://lh7-qw.googleusercontent.com/docsz/AD_4nXd4q0Q50aoHZKdh92bedV1rWXFVGmZ2t-Dm1FBl08XuuzyalvrBBtWqTgj93CDnLlQqFRJbew-WjxZ2L1MfTtb_KzF3Wobpx08SQIKMdgIQPfwdiYvSjkuglB6ruBv-NkgdTt259Z0wK-j_KyQvx0qeXiU?key=JSNMgaamRsns2DBiowR75RYx" alt=""><figcaption></figcaption></figure>

To access the CSS code for this customized homepage:

{% content-ref url="/pages/QvQJFqMJgdiBSJgPK0ky" %}
[Homepage customization](/tutorials/advanced-tutorials/deep-customization-chart-css/homepage-customization)
{% endcontent-ref %}

### Chart

The following example demonstrates how to create a sleek and minimalist style for charts, emphasizing clarity and elegance. You can use default colors available in the customization theme section, change colors, and hide certain elements for a streamlined look.

<figure><img src="https://lh7-qw.googleusercontent.com/docsz/AD_4nXc_YDJeB0VmvV3g_HpU_kVe3L6SHMOOnYxeTCmxpADDNaiN7ysO_SRmuOh-6QTsuXQnsy1T8JxgRzFdIxk3ZtmE9bA5ua-q0UfWcwsUyiOKBzfMn2s7aNUEtMLgofeBGIbDvptrBDO3LuJ_OHi-OrW1peQ?key=JSNMgaamRsns2DBiowR75RYx" alt=""><figcaption></figcaption></figure>

To access the CSS code for this barchart customization:

{% content-ref url="/pages/kMxw0i1I3QNYIokt6baa" %}
[Chart customization](/tutorials/advanced-tutorials/deep-customization-chart-css/chart-customization)
{% endcontent-ref %}

### Dashboard

{% hint style="info" %}
To ensure your custom charts and tiles appear correctly in the dashboard, make sure to first customize them in the homepage and stories.
{% endhint %}

For the dashboard, here’s an example of a futuristic dark mode style, enhancing both visual impact and readability:

<figure><img src="https://lh7-qw.googleusercontent.com/docsz/AD_4nXfyvY5tPBtrrJuhGuYDhnukEKsuTnvYTuGeCcgpoM7wg7mScz-wUv4xjJIP0uj9akgVs2lJgHsf5Zw7Nm9l29F_mcd88kwiE5V-Xzo6wa0_pwQWmoObGAz303zLuaKXaEiLuJYuPK6uPVTsZOB30fdvcUnw?key=JSNMgaamRsns2DBiowR75RYx" alt=""><figcaption></figcaption></figure>

To access the CSS code for this customized dashboard:

{% content-ref url="/pages/NEsxGp4UTTio13H0XKsl" %}
[Dashboard customization](/tutorials/advanced-tutorials/deep-customization-chart-css/dashboard-customization)
{% endcontent-ref %}

## Find the elements to customize

To apply custom styles, start by identifying the correct element selectors using your browser’s inspect tool. This helps you preview changes in real-time, view associated classes and IDs, and test various options directly on the page. Once you’ve confirmed the desired styles, add the chosen classes to your custom CSS in the section: Customization > Theme > CSS Custom.

<figure><img src="https://lh7-qw.googleusercontent.com/docsz/AD_4nXdr20Td_DCS9geg3hn-mGsDjNEJkaXN_5-zu2L-apz2sei5hWt4ArPxZZsV04Mli952yx_e-YfShQ2zXoLeXcNiRfiMVuaSfkxNcHmiXv80ZPtpVVg7IRp9NQT2FCU21AYG606ZUQ?key=JSNMgaamRsns2DBiowR75RYx" alt=""><figcaption><p>Right click > inspect</p></figcaption></figure>

{% hint style="danger" %}
Class names may occasionally change to improve our product's quality. If you encounter this, locate the updated class name and add it after the original class used for your customization. This ensures your styles remain correctly applied to the updated elements.\
For example: .dDCHxK {}

Alternatively, to avoid relying on dynamically generated class names, you can use the child combinator in CSS to target specific elements.\
For example: .small-app-home\_\_content > div:nth-child(1) > div:nth-child(1) {}

This approach ensures your styles remain stable, even if class names are updated in the future.
{% endhint %}

## Simplify the modification of a specific chart with CSS

If you’d like to modify a **specific** chart in a story, you have two options:

<figure><img src="https://lh7-qw.googleusercontent.com/docsz/AD_4nXdcb0NSoIrc3yuFdjRyuyEuY1QvrnYBVN1XBLwMRFOeNJGKujyxPCigav9RMQzAZzEYyiDoNFOhiSsd6bt6e-0RntgcbnKG3lU_WJU8R62BKMsLRbX6PUdfPKy4jDV3KK8002B8?key=JSNMgaamRsns2DBiowR75RYx" alt=""><figcaption></figcaption></figure>

* You can either use the **Custom Class CSS** available in Chart editor > Display > Custom Class CSS. Then, in the custom CSS (customization section), you will need to add the class (like in the example, .bar-chart-example) and modify the styles of the elements you want to change using CSS.
* You can make adjustments directly in **the Code Editor** by manually adding the custom class name (like in the photo below) in the type area. Then it works like the Custom CSS Class.

<figure><img src="https://lh7-qw.googleusercontent.com/docsz/AD_4nXfLC5o4R0GXVqQ3sDpyMsTjLnkLV9WPBjiAo__nipo1fPL5m_bAVua379aSBk1CSfOlL5VfW941Z6q8vGavNKnqGh628xiAQpsBEgHyLA9Ty9OFmj5zvjGtUZdtav97NME3mTd0fQ?key=JSNMgaamRsns2DBiowR75RYx" alt=""><figcaption></figcaption></figure>

Both options allow you to customize the chart’s appearance to your needs.

## Bonus

You can add simple CSS animations to enhance your app’s visual experience:

<figure><img src="/files/UT18A5zWTMSIxg8LM2Xu" alt=""><figcaption></figcaption></figure>

This is the code for this simple card/tile animation:

```css
/* Tile CSS animation */

.home-tile__content {
 border-radius: 12px !important;
}

.smart-home-tile:hover {
 bottom: 5px;
 box-shadow: 0px 5px 0px #183C59;
 transition: 0.2s;
}
```

If you need inspiration for your header, here is an example:

<figure><img src="/files/wWaZahayyBrJh5lz0B9r" alt=""><figcaption></figcaption></figure>

You will find the code for this glassmorphism effect header below:

```css
/* Glassmorphism effect header */

.small-app-home__content > div:nth-child(1) > div:nth-child(1) {
  backdrop-filter: blur(8px) saturate(0.7) brightness(1.2) drop-shadow(2px 4px 6px black) !important;
  box-shadow: inset 0 0 0 200px rgba(255,255,255, 0.1), 2px 4px 6px rgba(0,0,0,0.1) !important;
  background-color: rgba(253, 255, 255, 0.1) !important;
}
```

Feel free to experiment with these examples to achieve the desired look and feel for your application ! By combining and adjusting styles, you can create a tailored visual experience that reflects your brand’s identity and meets your layout needs.


# Homepage customization

In this page, you will find the CSS code for the Homepage example:

```css
/* Homepage CSS */

/* Filter & title */

.small-app-home__content > div:nth-child(1) > div:nth-child(1) > div:nth-child(1) {
 color: white;
}

.small-app-home__content > div:nth-child(1) > div:nth-child(1) {
 background-color: #016938 !important;
}

/* Section title */

.category__name__content {
 color: #016938;
}

/* Section separator */

.category__name::after,
.category__name::before {
 background-color: #016938 !important;
 height: 3px !important;
 border-radius: 4px !important;
 opacity: 1 !important;
}

.category__name::after {
 margin-left: 10px;
}

.category__name::before {
 margin-right: 10px;
}

/* Tile */

.smart-home-tile {
 border-radius: 16px !important;
 box-shadow: 0px 5px 10px #016938;
}

[data-testid="filter-icon"] {
 bottom: 8px !important;
 right: 8px !important;
 border-radius: 8px;
}

.tc-value__value,
.tc-value__unit,
.tile-line__selected-value {
 color: #37AF45;
}

.tile-content__name {
 color: #016938;
}

/* Tile line chart */

.widget-line-spot-point {
 fill: #016938;
}

.tile-line__selected-date {
 background-color: rgba(0,0,0,0) !important;
 color: #37AF45;
}

.tile-line__line {
 stroke: #37AF45;
}

/* Tile leaderboard */

.horizontal-bar-chart__label {
 color: #37AF45;
 font-size: 14px !important;
}

.horizontal-bar__bar--complement,
.horizontal-bar__bar {
 rx: 4;
 ry: 4;
}

.horizontal-bar__bar-value {
 fill: #37AF45;
}

.horizontal-bar__bar--complement {
 fill: #F0FFF4;
}

```


# Chart customization

In this page, you will find the CSS code for the chart customization:

```css
/* Chart CSS */

/* Barchart */

.domain {
 stroke: none !important;
}

.tick line {
 display: none;
}

.axis.y .tick text {
 display: none !important;
}

.vertical-bar__bar-value {
 fill: #4B4B4B;
 font-size: 1.1rem;
}

.tc-story__charts {
 background-color: #FAFAFA;
}

```


# Dashboard customization

In this page, you will find the CSS code for the dashboard customization:

```css
/* Dashboard CSS */

/* Title */
.dashboard-header__title {
  color: #0ED9FE !important;
}

.dashboard-grid-item__title span {
  color: #624FE8;
}

/* Filter buttons */
.beMgwO {
  background-color: #2E2B4A !important;
  border: 1px solid #0ED9FE !important;
}

.hQEjLc {
  color: #0ED9FE !important;
}

.hSYCNm {
  color: #18A9E4;
}

.dnCQIL {
  color: #624FE8 !important;
}

.laFPgZ {
  color: #18A9E4;
}

.dqRdjb:nth-child(0) {
  color: #18A9E4;
}

.dqRdjb:nth-child(1) {
  color: #0154C4;
}

/* Banner & Filter */
.dashboard-header-filters-banner {
  border-bottom: none !important;
  padding: 16px 32px !important;
  background-color: #26233E !important;
  border-radius: 4px;
}

.dashboard-header__main-banner {
  border-bottom: none !important;
}

/* Container */
.embed-client-container,
.dashboard-layout__content,
.dashboard-header__main-banner {
  background-color: #2E2B4A !important;
}

/* Item grid */
.dashboard-grid-item {
  border: 2px solid #0ED9FE !important;
}

.dashboard-grid-item__header {
  border-color: #0ED9FE !important;
  background-color: #221F3A;
}
```


# Managing different environment

### A Comprehensive Tutorial

### Introduction

Managing multiple environments is a crucial practice for organizations that need to maintain separate spaces for development, testing, and production. This tutorial will guide you through the process of setting up and managing multiple environments in Toucan, helping you ensure good user experience, maintain data security, and establish a reliable workflow.

### Understanding Multi-Environment Management

#### Why Set Up Different Environments?

There are two key reasons to implement multi-environment management:

1. **Ensure a Good User Experience**
   * Validate changes before they're visible to end-users
   * Test new features without affecting the production environment
2. **Ensure Data Security**
   * Restrict app-builders' access to sensitive production data
   * Minimize impact of potential data breaches (e.g., if a hacker gains access to an app-builder login)

#### Types of Environments

1. **Development Environments (Test/Dev/Sandbox/Hotfix)**
   * Used by app-builders to draft and test solutions
   * Isolated from end-users
2. **Beta Environments**
   * Used by privileged end-users to test solutions before moving to production
   * Note: One environment can function as both a beta and pre-production environment
3. **Pre-Production Environments**
   * Used to create the next version of the production environment
   * Should be identical to the next version of the production app before validation
4. **Production Environments**
   * The final product accessible to all end-users
   * Should be stable and thoroughly tested

### Implementation Strategies

We'll explore three main implementation strategies for multi-environment management in Toucan. Choose the approach that best aligns with your organization's needs, infrastructure, and security requirements.

<figure><img src="/files/5WBfwHgbquluJdZXxV1d" alt=""><figcaption></figcaption></figure>

### Strategy 1: Duplicated Stories in a Single App

This strategy is ideal for teams that need a simple validation workflow without requiring data isolation.

#### Implementation Steps

1. Duplicate a story in Staging, and set its visibility to "Hidden in production"
2. Once the story is validated: a. Set the visibility of the copy to "Public" (or "Customized Visibility") b. Delete the original story c. Publish the changes

#### Pros

* Adds a layer of confirmation for design changes
* Relatively easy to set up and maintain
* Works with any data setup (both Load and Live Data, with variabilized queries or permissions)

#### Cons

* May lead to human error if there are frequent changes or multiple app-builders working on the same application
* Does not allow for production data isolation
* Does not work in an embed context

#### When to Choose This Strategy

Choose this approach when:

* You have a small team working on the application
* You don't need to isolate production data
* You want a lightweight approach to validate changes

### Strategy 2: Embedded Dashboards & Variabilized Queries

This strategy uses embedded dashboards and variabilized queries to manage environments, and is suitable for Live Data setups.

#### Implementation Steps

1. Create a new preproduction story in Staging with a variabilized query that points to the right database depending on user attributes
   * Set default values to display data even without attributes (allows app-builders to visualize their work in staging)
2. Publish the story
3. Add the story to a newly created Dashboard embedded in a pre-production environment
4. Once the new Dashboard is validated, replace the alias of the current embedded Dashboard in production with the alias of the new one
5. Delete the former Dashboard and the stories feeding it

#### Pros

* Provides a thorough validation process
* Relatively easy to maintain once set up
* Allows for limited updates
* Compatible with beta-users (if they have access to the Client Pre-production environment)
* Compatible with token-in-token authentication for production data isolation

#### Cons

* Not compatible with platform usage
* More maintenance intensive with single-item integration
* Not compatible with stored data

#### When to Choose This Strategy

Choose this approach when:

* You need a more rigorous validation process
* You're working with Live Data
* You need to test with beta users
* You need to isolate production data through token-in-token authentication

### Strategy 3: Using Toucan's Import/Export Script for App Migration

This strategy leverages Toucan's internal Import/Export script to transfer applications between environments, providing a robust solution for production data isolation scenarios.

#### Implementation Overview

1. Update the Preproduction App in staging mode
2. Once validated, publish the preproduction App
3. Ask your CSM to run Toucan's Import/Export script to migrate the application from preproduction to production
   * The process maintains all configurations and customizations during transfer
4. Publish the production App after migration

#### How to Access the Import/Export Script

Currently, the Import/Export script is an internal Toucan tool that requires assistance from your Customer Success Manager (CSM):

* Reach out to your dedicated CSM when you need to migrate an application
* Provide details about the source and destination environments
* Your CSM will coordinate the migration process with Toucan's technical team

**Note:** Toucan is considering making this tool directly available to users in the future, which would allow for self-service migrations between environments.

#### Pros

* Provides complete production data isolation
* Ensures exact replication of your validated application
* Eliminates manual file transfer errors
* Handles complex configurations automatically
* Maintains version history and audit trail

#### Cons

* Currently requires CSM involvement (not self-service)
* Migration scheduling depends on CSM availability

#### When to Choose This Strategy

Choose this approach when:

* It's critical to isolate app-builders from production data
* You need an exact replica of your preproduction environment in production
* You want to minimize the risk of manual transfer errors
* You can coordinate migration timing with your CSM

### Best Practices for Multi-Environment Management

#### Data Isolation

You can variabilize data access at multiple levels:

* Host level
* Database level
* Schema/Table level
* Column level

#### Naming Conventions

When using duplicated stories or dashboards:

* Establish thorough naming conventions to avoid confusion
* Consider adding environment identifiers (e.g., "DEV\_", "PREPROD\_", "PROD\_")
* Include version numbers when appropriate

#### User Access Control

* Use user rights to control access to different layouts
* Restrict access to staging/development environments to app-builders only
* Manage access to beta environments for selected testers

### Conclusion

Multi-environment management in Toucan helps ensure both a great user experience and proper data security. By selecting the right strategy for your needs, you can create a workflow that balances ease of use, security, and maintenance requirements.

Remember that the best approach depends on your specific requirements:

* For simple validation without data isolation concerns: Strategy 1 (Duplicated Stories)
* For Live Data with validation and limited data isolation: Strategy 2 (Embedded Dashboards & Variabilized Queries)
* For complete production data isolation: Strategy 3 (Import/Export Script)

Consult with your Toucan representative if you need help determining the best strategy for your specific use case.


# Overview of Data In Toucan

**Toucan offers lightweight ETL** (Extract, Transform, Load) capabilities to enhance your analytics experience:

* **Connectors**: Our [AnyConnect™](/data-management-in-datahub/datasources-in-toucan/managing-connectors) library provides native integration with the most advanced data warehouses available in the market.
* **Storage System**: Designed for the "last mile of data," our [storage system](/data-management-in-datahub/datasets-in-toucan/stored-and-live-datasets) ensures efficient visualization while minimizing data warehouse consumption.
* **No-Code Querying Interface and Engine**: [YouPrep™](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) is a user-friendly interface that allows non-technical users to easily prepare data and derive meaningful business metrics without relying on data analysts or engineers.
* **Cataloging Interface**: [DataHub™](/data-management-in-datahub/datasets-in-toucan/maintaining-data) serves as a centralized platform for data management, maintenance, and collaboration.

{% embed url="<https://youtu.be/UBEB-VR2ZN4>" %}
Discover our cataloging interface : the DataHub
{% endembed %}

Our goal is to cater to two types of user profiles:

1. **Non-technical users**: These users can leverage YouPrep™ to create metrics without depending on data analysts or engineers. The no-code experience simplifies the process of data preparation.
2. **Technical users**: For users managing more complex transformations and requiring the flexibility of a developer experience, Toucan provides advanced capabilities that allow for full customization.

<table data-view="cards"><thead><tr><th></th><th></th><th></th></tr></thead><tbody><tr><td></td><td><a data-mention href="/pages/sMZGhSfAtUjsMc0riUSL">/pages/sMZGhSfAtUjsMc0riUSL</a></td><td></td></tr><tr><td></td><td><a data-mention href="/pages/jDbBaYhVNgAopbTY2juG">/pages/jDbBaYhVNgAopbTY2juG</a></td><td></td></tr><tr><td></td><td><a data-mention href="/pages/EXJ7Zb5jCdrunApWljXR">/pages/EXJ7Zb5jCdrunApWljXR</a></td><td></td></tr></tbody></table>

{% hint style="info" %}
Toucan best works with a modern data stack and doesn't intend to replace ETL tools.
{% endhint %}


# New Data Execution System

This page explains how the new Data Execution system works

Toucan **Highly Available Data Execution System** (HADES) is the result of two years of R\&D to provide the most efficient data execution layer for an embedded analytics tool relying on the latest technologies available. HADES improves data execution efficiency.

### General

HADES is a multi-tenant service dedicated to executing data queries.

Its mission is to

* receive data pipelines,
* process data pipelines, and
* deliver the results efficiently.

<figure><img src="/files/0JM9ywSzcKAusfV9Aoci" alt=""><figcaption><p>HADES : simplified schema of HADES interactions with other services</p></figcaption></figure>

HADES is designed to address the limitations of our previous execution system.

It results in better rendering times for data-intensive dashboards (see [Better than ever](https://www.toucantoco.com/en/blog/multi-tenant-architecture) results in our blog post dedicated to HADES) and a smoother data preparation experience.

Our new Data Execution System enables more users to interact with their analytics dashboards simultaneously without experiencing slowdowns through improved handling of concurrent queries.

HADES ensures a faster experience for builders and viewers who have already accessed the same dashboard or underlying queries.

This system still maintains strict data isolation between customers with dedicated S3 buckets to each customer.

It also leverages on multi-tenant architecture benefits with a better peak management and high availability.

### Data pipeline processing

HADES natively implements [hybrid pipelines](https://www.toucantoco.com/en/blog/product-release-hybrid-pipelines), to seamlessly blend on-source and in-memory data pipelines processing. We determine the optimal execution engine for each step of a data transformation pipeline prioritizing [execution directly on the source database](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql#overview) when possible, and switching to in-memory processing for unsupported operations.

### How to test the new service

If you use a **supported connector** (see release note) want to test our new service, you can contact your CSM or our support.

#### **IP Allowlisting**

{% hint style="info" %}
Our new service is accessible to an unique IP (EU area) :\
`51.15.128.70`\
\
Whitelist your backend IP as well.
{% endhint %}


# Management of datasets stored under the new system

How the new Data Execution System handles stored datasets

### Overview <a href="#overview" id="overview"></a>

{% hint style="info" %}
Since the [v0.4.0](/additional-ressources/latest-releases/2025-releases#september-23-2025-v154) of our Data Execution System, we support refresh data jobs.
{% endhint %}

The *Refresh Data* feature in Toucan enables updating datasets stored in the Toucan data store. This document explains how this mechanism works with the new **Data Execution System (HADES)**, and outlines the main differences from the legacy execution engine.

The transition to a **multi-tenant job execution system** provides significant improvements in resource management efficiency, job stability, and overall performance.

***

### Changes Introduced by the New Data Execution System <a href="#changes-introduced-by-the-new-data-execution-syste" id="changes-introduced-by-the-new-data-execution-syste"></a>

Our new system restructures the way *refresh data jobs* are processed. Compared to the legacy model, it changes:

* How data processing jobs are managed
* The efficiency and reliability of those jobs
* The size and structure of stored data files

These changes collectively improve performance and scalability across multiple tenants.

***

### Legacy Data Execution System <a href="#legacy-data-execution-system" id="legacy-data-execution-system"></a>

In the previous system:

* Each **workspace** had a fixed amount of RAM for handling refresh jobs.
* These jobs were processed by a **dedicated backend worker** specific to the Toucan stack.
* Each backend worker managed all the refresh jobs for a single workspace.
* Jobs could be **interrupted arbitrarily** if the backend worker reached its memory limits.

This setup restricted concurrency and stability when multiple refresh operations competed for memory within the same workspace.

***

### How we handle refresh data jobs with our new Multi-Tenant Execution System <a href="#hades-new-multi-tenant-execution-system" id="hades-new-multi-tenant-execution-system"></a>

With our new multi-tenant execution system:

* Job processing is **multi-tenant** and shared across customers.
* Each refresh job runs in an **isolated Unix process**, ensuring fault isolation.
* Each **worker pod in Kubernetes** has a fixed maximum RAM limit.
* Memory is dynamically managed through a **custom allocator**, preventing any single job from consuming excessive memory.
* Previews are executed **synchronously** within HADES to ensure consistency between preview and execution runs.

This architecture ensures fair resource allocation: one customer’s job cannot “steal” memory from another.

<figure><img src="/files/IobjfxvSiPjCoIRyurDs" alt=""><figcaption><p>Data Execution Service - September 2025</p></figcaption></figure>

***

### Memory Allocation for Refresh Jobs <a href="#memory-allocation-for-refresh-jobs" id="memory-allocation-for-refresh-jobs"></a>

Every refresh job has a **defined memory limit**. For stored datasets, the workflow typically involves:

1. **Data loading:** fetching the dataset into memory from storage.
2. **In-memory processing:** applying the defined transformations.
3. **Result output:** writing the processed data back to the Toucan data store.

Memory allocation occurs progressively during processing rather than preallocating the full limit (e.g., no initial `malloc(4GiB)`). However, since most jobs load data early, memory saturation will typically occur near the beginning if limits are exceeded.

HADES differs from the legacy engine by **failing fast** when memory limits are reached, rather than later in processing.

### Factors affecting memory consumption

* **Column types:** more complex data types (e.g., nested or string-heavy fields) consume more memory.
* **Transformation complexity:** joins, aggregations, and sorts increase memory usage.
* **Column cardinality:** columns with many unique values increase computational load.
* **Early filtering:** applying filters earlier reduces downstream workload.
* **Column selection:** operating on fewer columns minimizes memory usage.

{% hint style="info" %}
Our service applies **lazy evaluation** and builds an **optimized execution plan** before allocating memory. This ensures efficient use of resources and predictable runtime behavior across concurrent jobs.
{% endhint %}

***

### Data Processing Workflow <a href="#data-processing-workflow" id="data-processing-workflow"></a>

Each refresh job follows the three standard data processing stages in Toucan:

### 1. Planning Extraction

During this step, our system plans everything needed to create this dataset.

### 2. Fetching Data

Data is retrieved from the source system (SQL query, remote file, or Toucan data store).\
Requirements:

* Source must be **available**
* Query must be **processable** within the source system’s constraints

Depending on pipeline type if **NativeSQL pipeline** all processing happens in the datasource before transfer to Toucan.

### 3. In-memory execution

The extracted data is loaded into memory, and transformations are applied in sequence.

* **Hybrid pipeline:** early steps run in the datasource, later ones in the Toucan engine.
* **Toucan-only pipeline:** all steps are processed in Toucan when incompatibilities exist (e.g., datasource not supporting SQL or hybrid chaining).

After transformations, the processed result is written to Toucan’s internal storage.

***

### Performance and Efficiency Results <a href="#performance-and-efficiency-results" id="performance-and-efficiency-results"></a>

Performance comparisons show **massive gains** with HADES:

| Scenario                   | Legacy System (Laputa) | HADES       | Performance Gain |
| -------------------------- | ---------------------- | ----------- | ---------------- |
| Simple Transformations     | Up to 6 minutes        | <10 seconds | 97% faster       |
| Complex Joins/Aggregations | 2–3 minutes            | <30 seconds | \~90% faster     |

***

### Data Storage Architecture <a href="#data-storage-architecture" id="data-storage-architecture"></a>

Under the new architecture:

* Customer data is stored in **dedicated S3 buckets**, one per workspace.
* The system is **multi-tenant**, processing jobs for all clients via a shared service layer.
* Processed data is **not persisted** in the execution layer — only in S3.

In the future, we plan to allow customers to **provide their own S3 buckets**, maintaining full control of their data while Toucan writes transformation outputs directly there.

***

### Data File Efficiency <a href="#disk-usage-and-data-file-efficiency" id="disk-usage-and-data-file-efficiency"></a>

The new storage layer uses **compressed, columnar formats** optimized for analytical workflows.\
Stored files occupy significantly less disk space compared to previous builds.

* Files downloaded by users remain **CSV-formatted**, while storage files use a more efficient binary format.
* Average disk usage per dataset has **decreased considerably**, resulting in faster reads and improved storage density.

***

### QA: refresh data under the new data execution system <a href="#maintenance-and-freshness-signals" id="maintenance-and-freshness-signals"></a>

### What is the *Refresh Data* feature in Toucan?

The *Refresh Data* feature updates datasets stored in the Toucan data store. It ensures your dashboards and metrics always reflect the most recent information from your sources.

### What has changed with the new Data Execution System (HADES)?

Toucan has migrated from a legacy, single-tenant system to **HADES**, a multi-tenant execution engine designed for improved job isolation, higher efficiency, and faster refresh times.

***

### Architecture and System Behavior <a href="#architecture-and-system-behavior" id="architecture-and-system-behavior"></a>

### How does the HADES system manage data refresh jobs?

Each refresh job runs as an isolated Unix process within a Kubernetes worker pod. Memory usage is tightly controlled by a **custom allocator**, which prevents one job from consuming another’s resources.

### What are the main differences between HADES and the legacy system?

| Aspect              | Legacy System            | HADES                            |
| ------------------- | ------------------------ | -------------------------------- |
| Job Scope           | One worker per workspace | Multi-tenant shared workers      |
| Memory Isolation    | Limited per workspace    | Full per-job isolation           |
| Failure Behavior    | Random job interruptions | Controlled memory-bound failures |
| Resource Efficiency | Fixed per workspace      | Dynamic balancing per job        |

### How does HADES prevent resource conflicts between tenants?

Each worker pod has a predefined memory limit. If a job tries to allocate more memory than allowed, it fails quickly, protecting other tenants from performance degradation.

***

### Memory Management and Performance <a href="#memory-management-and-performance" id="memory-management-and-performance"></a>

### How is memory allocated for refresh jobs?

Memory is allocated progressively during job execution. Large datasets trigger early allocations during data fetching, so failures occur sooner if limits are exceeded.

### What factors influence memory consumption?

* Data type complexity
* Transformation type (joins, aggregations, sorts)
* Column cardinality (number of unique values)
* Step ordering (early filtering is more efficient)
* Number of columns used in transformations

### Does Toucan use Polars for data processing?

Yes. Toucan uses **Polars**, which builds an optimized execution plan before allocating memory. This allows predictable, efficient use of resources across concurrent jobs.

### What happens if a job exceeds its memory limit?

Jobs exceeding their memory quota fail immediately rather than gradually degrading performance. This provides faster feedback and better resource stability.

***

### Data Processing Workflow <a href="#data-processing-workflow" id="data-processing-workflow"></a>

### What are the main stages of a refresh job?

Every refresh job follows three main steps:

1. **Data Extraction** – Fetching information from the datasource or files.
2. **Transformation** – Applying data processing steps (filtering, joins, aggregations).
3. **Loading** – Writing the processed output back to Toucan storage.

### What are the different pipeline types?

* **NativeSQL** – Entirely processed within the datasource.
* **Hybrid** – Early steps run in the datasource, later ones in Toucan.
* **Toucan-only** – Fully executed in Toucan (when datasource limitations exist).

***

### Data Storage and File Management <a href="#data-storage-and-file-management" id="data-storage-and-file-management"></a>

### How is customer data stored?

Each customer’s data is isolated in a **dedicated S3 bucket**. HADES uses a shared service architecture for job execution, but no customer data is persisted in the compute layer.

### Can customers use their own S3 buckets?

No but we plan to add it in a future update to allow customers to configure their own S3 storage so they retain full control over their data.

### How are files stored and downloaded?

* Files are stored in a compact, columnar format optimized for analytical queries.
* Downloads always return in **CSV format** for compatibility and ease of use.
* On average, file size in memory is significantly reduced compared to legacy formats


# Caching Architecture

This documentation outlines the caching strategy employed by the new Data Data Execution System . It details the infrastructure, storage mechanisms, and workflows used to ensure high performance, data security, and efficient resource management for live queries.

### 1. Infrastructure & Storage Overview

The caching layer leverages a combination of S3 object storage and in-memory data stores hosted on our infrastructure.

* **Storage Backend**: S3 Object Storage (S3-compatible)
* **Hosting Location**: France.
* **Orchestration & State**: Dragonfly (hosted on internal servers).
* **Data Format**: Apache Arrow IPC Streaming.
* **Duration**: 5 min (300 seconds)
* **Encryption**: Yes

### 2. Live Cache Workflow

The "Live Cache" handles the temporary storage of query results to reduce latency for frequent requests.

#### 2.1 Storage Mechanism

* Location: Query results are stored in an S3 bucket hosted in France.
* Encryption: All data in S3 is encrypted at rest using Server-Side Encryption with our on key (SSE-C).
* Retention: The default Time-To-Live (TTL) for cached live queries is 300 seconds (5 minutes).

#### 2.2 Data Serialization

To ensure high-performance data transfer, the Data Service API caches and retrieves queries using the Apache Arrow IPC Streaming format.

* Ref: [Arrow Columnar Format - IPC Streaming](https://arrow.apache.org/docs/format/Columnar.html#ipc-streaming-format)

#### 2.3 Retrieval Process

When a query is requested:

1. Lookup: The system checks if a cache is available for the query with Dragonfly
2. Hit: If the data is cached, the Arrow IPC file is streamed directly from S3 to the Frontend.
3. Miss: If the data is not cached, the query is executed, the results are written to S3 (streamed as IPC), and then streamed to the client.

> Note on Preview Data: Data used for previews flows is never cached and directly streamed to the client.

### 3. Technology Roles

To maintain distinct responsibilities within the architecture, different services handle specific aspects of the caching and execution lifecycle.

#### Dragonfly

Dragonfly acts as the primary interface for cache management.

* It is the memory of available caches for queries.
* It handles the high-performance caching operations.
* Distributed Locks: Manages locks to prevent race conditions during query execution.


# Datasources in Toucan

Datasources allow to configure remote and local data entry points to then create datasets from these sources, they act as the first entry point in Toucan. In Toucan, you can configure different types of data sources

* [SQL databases and datawarehouse](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector/database-and-data-warehouse-connectors) (PostgreSQL, MySQL, GBQ, etc)
* [REST APIs](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector/generic-connectors/setting-up-an-http-api-connector)
* [Remote files storages](/data-management-in-datahub/datasources-in-toucan/managing-remote-file-storages/setting-up-a-file-storage) (S3, FTPS, SFTP, etc)
* [Drag and drop flat files](/data-management-in-datahub/datasources-in-toucan/managing-files) (csv, excel, json, geojson)\
  \
  The interface is accessible in the first tab of the DataHub.

### Datasources types in Toucan

Toucan has three different types of data sources:

* **Connectors**: components allowing you to connect to your data ecosystem (database or service) to extract and use your data within your app.
* **File storages**: components allowing to connect to remote file storage and extract data from the files located in the remote storage (SFTP for now)
* **Files**: raw data you can import from your computer or a distant file server (like FTP or S3)

<figure><img src="/files/QbHMj8k7PvAHLl905nl0" alt="Capture shows the datasource UI in app/datahub"><figcaption></figcaption></figure>

### Manage datasources in Toucan

In this section, you will learn the best practices for datasources management in Toucan and how to:

* **Manage connectors**: add, set up, edit and delete connectors. [see our guide](/data-management-in-datahub/datasources-in-toucan/managing-connectors)
* **Manage file storages**: add, set up edit and delete remote file storages, [see our guide](/data-management-in-datahub/datasources-in-toucan/managing-remote-file-storages)
* **Manage files:** add, edit, and delete local and distant files, [see our guide](/data-management-in-datahub/datasources-in-toucan/managing-files)

### Create datasets from datasources

in this section, you will also learn how to create datasets from the different types of datasources.

* Create a dataset from a connector, [see our guide](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector)
* Create a dataset from a remote file, [see our guide](/data-management-in-datahub/datasources-in-toucan/managing-remote-file-storages)
* Create a dataset from a file, [see our guide](/data-management-in-datahub/datasources-in-toucan/managing-files)

<table data-view="cards"><thead><tr><th></th><th></th><th></th></tr></thead><tbody><tr><td></td><td><a data-mention href="/pages/g2opZ52wcVoDwmIwGqn8">/pages/g2opZ52wcVoDwmIwGqn8</a></td><td></td></tr><tr><td></td><td><a data-mention href="/pages/zbDehnm0IRXIdTMweIHI">/pages/zbDehnm0IRXIdTMweIHI</a></td><td></td></tr><tr><td><a href="/pages/Kt7Dpu2IiU83ILmrHDXN">Manage file storages</a></td><td></td><td></td></tr></tbody></table>


# Managing Connectors

Our library of connectors, AnyConnect™, offers a seamless integration to external data providers, enabling you to leverage data from your systems for Toucan visualizations. The library is specifically designed to connect to the most widely used and modern databases and data warehousing tools available in the market.

We provide two types of connectors:

1. **Generic Connectors**: These connectors allow you to connect to multiple data sources, but they require advanced configuration to establish the connection.
2. **Specific Connectors**: Built for individual data sources, these connectors feature a user-friendly interface that only requires the relevant information for authentication and establishing a connection to the specific data source

## Add a connector

A list of connectors will be displayed where you can choose a connector from the list of available connectors.

1. To add a connector, in the `Datasources` tab of `DataHub`, click on `Add a connector` button.

   <figure><img src="/files/6WLVBUiCRFEEIvRtJPRC" alt=""><figcaption></figcaption></figure>
2. A modal window will open. Select the connector you want to add among the list of connectors displayed
3. [Configure the connector and save it](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector). A modal will opened with the configuration form specific to the connector. form The connector will appear within the listing of configured connectors.
4. Click on `Test the connection` if the option is available or `Save` to add the connector

{% hint style="info" %}
**Note**

Some connectors need a specific installation. Please create a ticket to our support team if you want us to set up one of these connectors on your Toucan instance.
{% endhint %}

## Edit a connector configuration

To edit a connector configuration

1. Click on the "Settings" action within the actions menu of the connector you want to edit

![](/files/mF2sOoGohww6o7v5kerI)

1. Edit the configuration of the connector and save it

## Delete a connector

To delete a connector:

1. Click on the three dots button, a "Delete" option is displayed![](/files/mF2sOoGohww6o7v5kerI)
2. Confirm the deletion of the connector. If a dataset uses the connector you are trying to delete, you will have a warning message and be able to select child datasets that you would like to delete simultaneously.

## Test connection

Before saving your connector configuration, the test connection option allows you to verify that your configuration is correct by testing a simple call to check that your Toucan workspace is able to communicate with your data source.

This option is primarily available for SQL databases.

{% hint style="info" %}
We support test connection for the following connectors:\
\- AWS Athena\
\- AWS S3\
\- Databricks\
\- Google Big Query\
\- MongoDB\
\- MySQL\
\- PostGresSQL\
\- AWS Redshift\
\- Snowflake
{% endhint %}

for some connectors, before clicking on save button, a test connection button is available.

* After testing a connection, a modal will opened, the test connection is successful:

<figure><img src="/files/4mnkhoSoNyx7eUNG9MQs" alt="AWS Athena Connection Test"><figcaption><p>AWS Athena Connection Test</p></figcaption></figure>

<figure><img src="/files/faGKL8TfRNuWMdx1HBSL" alt="PostGreSQL Connection Test" width="375"><figcaption></figcaption></figure>

* if the connection is not successful, the modal will show where the problem might be:

### 🔒 Security

Security is a core priority in how we interface with your data systems:

* **Read-Only Access**: We operate in a non-intrusive way (only reading data). It does not have the ability to write, modify, or administer your data sources.
* **Least Privilege Principle**: We **strongly recommend** using a **read-only account** when configuring a connection. This minimizes potential risk and aligns with best practices for access control.
* **Encrypted Connections**: All data communications between Toucan and your source systems are encrypted. Connections with databases use secure protocols, and all interactions with the Toucan platform go through **HTTPS**.
* **Certificate Management**: By default, We support **trusted certificates**. If needed, it is also possible to configure a **custom certificate chain** for some connectors.
* **No Persistent Live Data Storage**: Data is not stored at rest unless explicitly configured in **stored mode**. In its default behavior, AnyConnect™ streams data only for immediate use in visualizations. See [Stored and Live Datasets](/data-management-in-datahub/datasets-in-toucan/stored-and-live-datasets) for more information
* Secrets management: all secret fields related to your connectors are stored in a Vault using [**Hashicorp Vault**](https://www.vaultproject.io/)

{% hint style="info" %}
Access to a workspace's secrets is handled as follows:

* The dataset service, to access a workspace's secrets, checks if it has a valid token for the given workspace, generating one if needed via Kubernetes authentication (Tokens are valid for one hour and are only stored in memory).
* A token only allows access to the secrets of a single workspace, thus ensuring segregation of access to a workspace's secrets
  {% endhint %}

<table data-view="cards"><thead><tr><th></th><th></th><th></th></tr></thead><tbody><tr><td></td><td><a data-mention href="https://github.com/ToucanToco/doc-v3/tree/main/data-management-in-datahub/datasources-in-toucan/managing-connectors/broken-reference/README.md">https://github.com/ToucanToco/doc-v3/tree/main/data-management-in-datahub/datasources-in-toucan/managing-connectors/broken-reference/README.md</a></td><td></td></tr><tr><td></td><td><a data-mention href="/pages/wscl586s7H7uOI8y3dP3">/pages/wscl586s7H7uOI8y3dP3</a></td><td></td></tr></tbody></table>


# Add a connector

{% hint style="warning" %}
Please note we provide a [daily auto-generated list of all our production IP](https://toucantoco.com/public-servers-list.html) if your IT team requires them to whitelist the data sources access.
{% endhint %}

{% hint style="info" %}
**Information**

Note that while configuring a connector, you can refer to variables to adapt your credentials depending on the user context, for example. Look at this [page](/data-management-in-datahub/using-advanced-data-concepts/advanced-syntax-for-variables) for more informations about variables.
{% endhint %}

## Available connectors and setup guide

## Generic Connectors

<table data-view="cards"><thead><tr><th></th><th></th><th></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td></td><td><a data-mention href="/pages/K5M1T8A4T3HXVqP66qSH">/pages/K5M1T8A4T3HXVqP66qSH</a></td><td></td><td><a href="/files/uSaLKwAlYuldIYcENFRk">/files/uSaLKwAlYuldIYcENFRk</a></td></tr><tr><td></td><td><a data-mention href="/pages/1BxG6168W2n4AfFAwtrS">/pages/1BxG6168W2n4AfFAwtrS</a></td><td></td><td><a href="/files/4RvJVIk53IxvvmajqXpF">/files/4RvJVIk53IxvvmajqXpF</a></td></tr></tbody></table>

## Database & Data warehouse Connectors

<table data-view="cards"><thead><tr><th></th><th></th><th></th><th data-hidden data-card-cover data-type="files"></th><th data-hidden data-card-target data-type="content-ref"></th></tr></thead><tbody><tr><td></td><td><a data-mention href="/pages/dRU90ac8YFaHG6bAIf7J">/pages/dRU90ac8YFaHG6bAIf7J</a></td><td></td><td><a href="/files/MFPZMoo023kiOriE1gIo">/files/MFPZMoo023kiOriE1gIo</a></td><td></td></tr><tr><td></td><td>Setting up a <strong>Google BigQuery connector</strong></td><td></td><td><a href="/files/nu7yvZZ1sG53xjXiZ6gn">/files/nu7yvZZ1sG53xjXiZ6gn</a></td><td><a href="/pages/o54uJZ9esuTDJYfK24YS">/pages/o54uJZ9esuTDJYfK24YS</a></td></tr><tr><td></td><td><a data-mention href="/pages/sQsoxEEFOxEIHub8zzFQ">/pages/sQsoxEEFOxEIHub8zzFQ</a></td><td></td><td><a href="/files/UHncvk0ZPTo5GnVorV3O">/files/UHncvk0ZPTo5GnVorV3O</a></td><td></td></tr><tr><td></td><td><a data-mention href="/pages/SOkVck3GOFdqEZwxCWFn">/pages/SOkVck3GOFdqEZwxCWFn</a></td><td></td><td><a href="/files/ybulcgpdyn7GJSw1NVoy">/files/ybulcgpdyn7GJSw1NVoy</a></td><td></td></tr><tr><td></td><td><a data-mention href="/pages/HrL8sBXqWIdzuhQamWpC">/pages/HrL8sBXqWIdzuhQamWpC</a></td><td></td><td><a href="/files/AME94bh1XiSycxKIkL9E">/files/AME94bh1XiSycxKIkL9E</a></td><td></td></tr><tr><td></td><td><a data-mention href="/pages/kLpZs3mpuLkskrvPNaq2">/pages/kLpZs3mpuLkskrvPNaq2</a></td><td></td><td><a href="/files/7Yh1XR7bDlImWdBT4P00">/files/7Yh1XR7bDlImWdBT4P00</a></td><td></td></tr><tr><td></td><td><a data-mention href="/pages/HgkpQlw00rCLU7JvBG9o">/pages/HgkpQlw00rCLU7JvBG9o</a></td><td></td><td><a href="/files/LAix0viuWNVLf64Q9e9W">/files/LAix0viuWNVLf64Q9e9W</a></td><td></td></tr><tr><td></td><td><a data-mention href="/pages/bhxoTytwWSObh8yJa5x4">/pages/bhxoTytwWSObh8yJa5x4</a></td><td></td><td><a href="/files/mbeSdzs8nTOOD7gMSzZ8">/files/mbeSdzs8nTOOD7gMSzZ8</a></td><td></td></tr><tr><td></td><td><a data-mention href="/pages/j9vdqZHTmo0vP61mNxcU">/pages/j9vdqZHTmo0vP61mNxcU</a></td><td></td><td><a href="/files/ltDQTKYB8CcOeWpq3EQ4">/files/ltDQTKYB8CcOeWpq3EQ4</a></td><td></td></tr><tr><td><a data-mention href="/pages/83sG8JmwsMJIJBSwDUbV">/pages/83sG8JmwsMJIJBSwDUbV</a></td><td></td><td></td><td><a href="/files/iUu474sodGsDj99Zt0fs">/files/iUu474sodGsDj99Zt0fs</a></td><td></td></tr><tr><td></td><td>Amazon Document DB</td><td></td><td><a href="/files/HRdK5eRcmyGcKQkaSYYx">/files/HRdK5eRcmyGcKQkaSYYx</a></td><td></td></tr><tr><td></td><td>Google Cloud MySQL</td><td></td><td><a href="/files/C9JtfQeenHp8MrEC7YlE">/files/C9JtfQeenHp8MrEC7YlE</a></td><td></td></tr><tr><td></td><td>Microsoft SQL Server</td><td></td><td><a href="/files/eCa9q9c0wft8BLpi6LF1">/files/eCa9q9c0wft8BLpi6LF1</a></td><td></td></tr></tbody></table>

## Online Services Connectors

<table data-view="cards"><thead><tr><th></th><th></th><th></th><th data-hidden data-card-cover data-type="files"></th></tr></thead><tbody><tr><td></td><td></td><td><a data-mention href="/pages/m0ovIkfziu2d132vmdhC">/pages/m0ovIkfziu2d132vmdhC</a></td><td><a href="/files/I5pRL0VpX82duexXaR78">/files/I5pRL0VpX82duexXaR78</a></td></tr><tr><td></td><td><a data-mention href="/pages/IRxbrfO4ZKGPUY0myS3x">/pages/IRxbrfO4ZKGPUY0myS3x</a></td><td></td><td><a href="/files/rVThNv29Anlg36MSmDWW">/files/rVThNv29Anlg36MSmDWW</a></td></tr><tr><td></td><td><a data-mention href="/pages/M11dyv8kbORiaVZfvfuO">/pages/M11dyv8kbORiaVZfvfuO</a></td><td></td><td><a href="/files/hyZMFe5MSpR8rSeVH74z">/files/hyZMFe5MSpR8rSeVH74z</a></td></tr><tr><td></td><td><a data-mention href="/pages/TjHM13daIKD2olvN01kF">/pages/TjHM13daIKD2olvN01kF</a></td><td></td><td><a href="/files/yLBL2YARcUTqw9DHgCR5">/files/yLBL2YARcUTqw9DHgCR5</a></td></tr><tr><td></td><td>OneDrive</td><td></td><td><a href="/files/WskthN5DPSkcjw78IK41">/files/WskthN5DPSkcjw78IK41</a></td></tr></tbody></table>


# Generic Connectors

How to configure REST API connectors and ODBC connectors in Toucan


# Setting up an HTTP API connector

## Connector features

You can use the Toucan HTTP API connector to connect to:

* a REST API
* a GraphQL API
* an ElasticSearch
* Any service that allows to access to HTTP API

With an HTTP API connection, you can fetch data from your API to fill your charts and dashboards.

{% hint style="info" %}
**Changelog**\
**November 2024**

* This connector supports pagination

**February 2025**

* Enhanced support of oAuth2.0 authentications
  {% endhint %}

This type of data source combines the features of Python’s [requests](http://docs.python-requests.org/) library to get data from any API with the filtering langage [jq](https://stedolan.github.io/jq/) for flexible transformations of the responses. Optionally, an [xpath](https://developer.mozilla.org/en-US/docs/Web/XPath) string can be provided to first parse the XML response and then the jq filter is be applied to get the data in tabular format.

## Configuring the connector

{% hint style="warning" %}
To configure this connector you will need to use the documentation of the API you need to connect to
{% endhint %}

### **Responsetype**

The type of response the connector has to expect from the queried API.

{% hint style="warning" %}
Make sure you use the correct `responsetype`, based on the queried API’s documentation. Currently JSON & XML are supported, the default being JSON.
{% endhint %}

### **Retrypolicy**

Defines how the connector should behave when the network is unreachable:

* `MAX ATTEMPTS`: number of attempts to do before aborting the connexion
* `MAX DELAY`: total time to wait before aborting the connexion
* `WAIT TIME`: time to wait between each attempt

### **Certificate**

If the connector must use a certificate to establish the connexion, you can provide the path to the certificate.

### **Auth**

The authentication method that the connector should use to query the data. `AUTHTYPE` Can be:

* `basic`: username password, you can provide them in
  * `positional arguments`: input your *username* and *password* in the right order
  * `named arguments`: input them this way *{“username”:”myusername”, “password”:”mypassword”}*
* `digest`: same as above
* `oAuth1`:
  * `positional arguments`: input *client\_id* (sometimes named *client\_key*) and *client\_secret*. Both are provided by the service you are trying to access
  * `named arguments`: input *{“client\_id”:your\_client\_id, “client\_secret”: your\_client\_secret}*.
* `oAuth2`: *(deprecated)*
  * `positional arguments`: enter one by one (in the right order), the URL to access to the authentication endpoint (e.g. <https://login.mywebsite.com/oauth2/token>), the “client\_ID” (sometimes named “client\_key”) and the “client\_secret”. These informations are provided by the service you are trying to access
  * `named arguments`: input *{“client\_id”:your\_client\_id, “client\_secret”: your\_client\_secret}*.
* `CustomTokenServer:` provides a flexible mechanism for authenticating API requests using a custom token server. the token you get is then sent in the the `Authorization` header prefixed with "`Bearer` `{{your_token}}"` . In the `named arguments` section you have to fill as a json dict the required elements to get your token:
  * `method`: The HTTP method to use when requesting the token (e.g., 'GET', 'POST').
  * `url`: The URL to get the token server.
  * `params` (optional): Query parameters to include in the token request.
  * `data` (optional): Form data to include in the token request body.
  * `headers` (optional): Additional headers to include in the token request.
  * `json` (optional): JSON payload to include in the token request body.
  * `token_header_name`: allows to override the default `Authorization` header.
  * `filter` (optional): A JQ-style filter to extract the token from the response. Defaults to "." (root of the JSON response).

### Authentication

We have added a dedicated section to manage OAuth 2.0 authentication for REST APIs. This authentication method enables users to authenticate with a third-party service (an OAuth 2.0 provider). Upon request from our backend, the provider issues a token with a specific scope. This token is then used in the `Authorization` header with the `Bearer` scheme to authenticate and access your data on the API. For more detailed information, please refer to the [OAuth2.0 standards](https://oauth.net/2/).

For now we only support the `Grant Type: Authorization Code`. This section outlines the fiels available for configuring this method

* `Configuration Type` (dropdown list) : `AuthorizationCodeOauth2` (only option available for now)
  * `Authentication URL` (mandatory): the URL used to initiate the OAuth2.0 authorization process. For example:`https://auth.api-acme.com/oauth/authorize`
  * `Token URL` (mandatory): The URL used to exchange the authorization code for an access token. For example: `https://auth.api-acme.com/oauth/token`
  * `Scope` (mandatory): The permissions requested from the OAuth2.0 provider. For example: `read write profile`
  * `Additional authentication params` (optional): a JSON object containing additional URL parameters to be included in the authentication request . For example:`{"add_param1": "value_2", "add_param2": "value_2"}`
  * `Client Id` (mandatory): The unique identifier for your application, provided by the OAuth2.0 service. For example: `client_abc123`
  * `Client Secret` (mandatory): The secret key associated with your client ID. For example: `secret_xyz789`

{% hint style="info" %}
**Redirect URL**

Make sure you authorize this URL in your OAuth2.0 provider, it is necessary to complete the OAuth2.0 process of exchanging information

[**https://api-{{my-workspace}}.toucantoco.guru/{{my-app}}/connectors/http/authentication/redirect**](https://api-{{myworkspace}}.toucantoco.com/%7B%7Bmy-app%7D%7D/connectors/http/authentication/redirect)
{% endhint %}

{% hint style="info" %}
**Additional authentication params**

**Some OAuth2.0 providers can ask for additional parameters in the request. By default we only send the following fields**

* client id (for example `client_abc123`)
* redirect\_uri (for example **`https://api-{{my-workspace}}.toucantoco.guru/{{my-app}}/connectors/http/authentication/redirect`**)
* response\_type (for example `code`)
* scope (for example `read write profile`)
* state (for example `xyz123securestate` which is a random string for CSRF protection)

Google as an [OAuth2.0 provider](https://developers.google.com/identity/protocols/oauth2/web-server#creatingclient) requires other parameters, to access to Google API that requires OAuth2.0 as a mean of authentication you will have to fill the Additional authentication params with the following json

<kbd>{</kbd>

<kbd>"prompt": "consent",</kbd>

<kbd>"access\_type": "offline",</kbd>

<kbd>"response\_type": "code"</kbd>

<kbd>}</kbd>
{% endhint %}

### **Template**

You can use this object to avoid repetition in data sources. The values of the three attributes will be used or overridden by all data sources using this connector.

* `json`: a JSON object of parameters to send in the **body** of every HTTP request made using the configured connector. *Example: { “offset”: 100, “limit”: 50 }*
* `headers`: a JSON object of parameters to send in the **header** of every HTTP request made using the configured connector. *Example: { “content-type”: “application/xml” }*
* `params`: a JSON object of parameters to send in the **query string** of every HTTP request made using the configured connector. *Example: { “offset”: 100, “limit”: 50}*
* `proxies`: JSON object expressing a mapping of protocol or host to corresponding proxy. *Example {“http”: “foo.bar:3128”, “<http://host.name”>: “foo.bar:4012”}*

## **Selecting data from the API**

**Endpoint URL**

* `url`: The API’s endpoint you want to query, it will be appended to the baseroute URL defined in the connector ⚠️ as it cannot be empty in the case when the API doesn’t have endpoint, you can split the baseroute url defined in the connector and put the last part in the datasource. Ex: <https://example.com/API> in connector and /v1 in datasource

#### **Endpoint parameters**

* #### `Method`: Defines the http method you want the datasource to perfom, GET, POST or PUT. Default is GET. You can find the method you need in the documentation of the API you want to query
* `headers`: a JSON object of parameters to send in the **header** of every HTTP request made using the configured connector. *Example: { “content-type”: “application/xml” }*. Overwrites the header’s parameter in Template
* `URL params`: a JSON object of parameters to send in the **query string** of every HTTP request made using the configured connector. *Example: { “offset”: 100, “limit”: 50}* Overwrites the params parameter in Template
* `Body`: a JSON object of parameters to send in the **body** of every HTTP request made using the configured connector. *Example: { “data”: “my\_parameters” }*.

**Advanced**

* `parameters`: A JSON object that will be used for variables interpolation in the query string. For testing purpose only. In production mode, it should be left blank as variable interpolation will be handled by the app requester.
* `json`: a JSON object of parameters to send in the **body** of every HTTP request made using the configured connector. *Example: { “offset”: 100, “limit”: 50 }* Overwrites the JSON parameter in Template
* `proxies`: JSON object expressing a mapping of protocol or host to corresponding proxy. *Example {“http”: “foo.bar:3128”, “<http://host.name”>: “foo.bar:4012”}* Overwrites the proxies parameter in Template
* `flatten column`: optional field where you can specify the name of a column that contains nested rows. the column names in the resulting DataFrame will be prefixed with the original column name. Specified more parameters using a `,` delimiter. If specified, the nested rows will be flattened into separate columns in the resulting data frame. *Example if you have a column orders: \[{"id": 3, "product": "Notebook", "price": 5.99}] results will be separated in orders\_id, orders\_product and orders\_price*
* `data`: Two options, Type1 for a simple string, Type2 for a JSON field. 💡 you can send XML data with Type1 option
* `xpath`: If the reply from the API contains XML data you can parse it with an xpath string. See documentation: [xpath](https://developer.mozilla.org/en-US/docs/Web/XPath) Example:

  ```
  <?xml version="1.0" encoding="UTF-8"?>
  <result>
  <bookstore>
      <book>
          <title>Harry Potter</title>
          <price>29.99</price>
      </book>
      <book>
          <title>Learning XML</title>
          <price>39.95</price>
      </book>
  </bookstore>
  </result>
  ```

In the connector we’ll have a response like this:

```
{"bookstore": {"book": [{"title":"Harry Potter", "price": "29.99"}, {"title": "Learning XML", "price":"39.95"}]}}
```

And we can then apply a:

* `filter`: String containing a jq filter applied to the data to get them in tabular format. See documentation: [jq](https://stedolan.github.io/jq/) Example:

  ```
  filter: ".bookstore.book[]"
  ```

Let’s take the JSON defined above

```
{"bookstore": {"book": [{"title":"Harry Potter", "price": "29.99"}, {"title": "Learning XML", "price":"39.95"}]}}
```

We apply the filter “.bookstore.book\[]” which means that it will extract the `book` list from the `bookstore` So we end up with a table of results looking like this:

| title        | price |
| ------------ | ----- |
| Harry Potter | 29.99 |
| Learning XML | 39.95 |

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.

### Pagination

This section presents the pagination support of Toucan. Pagination options allows to setup a configuration which will loop the results of a query until all results are retrieved.

{% hint style="warning" %}
Throttling and large datasets\
**Throttling**

We do not support *throttling* meaning that we do not have a speed limit feature when we request an API. This means we cannot control how quickly requests are sent. As a result, if too many requests are made too quickly, it might trigger an error message saying the system is overloaded.\
\
**Large datasets**\
Toucan execution preview calls are synchronous, which means that we only have 30 seconds to fetch and transform data. Depending in the query, it could be an issue if you are working on live data, prefer store datasets if it is the case.
{% endhint %}

#### Pagination configuration types

**Offset Limit (OffsetLimitPaginationConfig)**

This configuration type implements the offset/limit pagination pattern.

**Parameters**

* `offset_name`: (string) Parameter name for offset (default: `offset`)
* `limit_name`: (string) Parameter name for limit (default: "limit")
* `limit`: (int) **mandatory** Number of items per request
* `data_filter:` (string) **`mandatory`** offset pagination config field to determine which part of data must be used to compute the data length in the form of a JQ filter

Use case: APIs using offset/limit style pagination.

<details>

<summary>offset-limit example</summary>

Let's take the following configuration

* "offset\_name": "custom\_offset"
* "limit\_name": "custom\_limit"
* "limit": 50
* "data filter": ".items"

![](/files/7ifEqhkfRkp7v7SQIHLw)

We will perform the following calls:

* `https://my-api.com/data?custom_limit=50&custom_offset=0`
* `https://my-api.com/data?custom_limit=50&custom_offset=49`
* `https://my-api.com/data?custom_limit=50&custom_offset=99`
* `https://my-api.com/data?custom_limit=50&custom_offset=149`\
  ...

until there is no more page to access to.

</details>

**Page-based pagination (PageBasedPaginationConfig)**

This configuration implements page-based pagination

**Parameters**:

* `page_name`: (string) Parameter name for the page (default: `page`)
* `page`: (int) **mandatory** Current page number
* `per_page_name`: (string) Parameter name for items per page
* `per_page`: (int) Number of items per page
* `max_page_filter`: (string) JQ filter to extract maximum page number
* `can_raise_not_found`: (boolean) Whether 404 errors should be treated as end of pagination, must be set if no `max_page_filter` is available

**Use case**: Traditional APIs using page numbers where the information can be found in the response body.

<details>

<summary>page-based example</summary>

Let's take the following configuration

* "page\_name": "custom\_page"
* "page": 1
* "per\_page\_name": "custom\_per\_page"
* "per\_page": 100
* "max\_page\_filter": ".infos.last\_page"
* "Can Raise Not Found": False

![](/files/B8bQ6lQvxq2jiyPbB0cu)

We will perform the following calls:

* `https://my-api.com/data?custom_page=1&custom_per_page=100`
* `https://my-api.com/data?custom_page=2&custom_per_page=100`

Until we reach the last page indicated in `max_page_filter` and stop the data fetching.

For a configuration as below, where there is no per\_page parameter to set and no information related to the last page in the response body. The configuration will look like this:

* "page\_name": "page"
* "page": 1
* "per\_page\_name": ""
* "per\_page":
* "max\_page\_filter": ""
* Can Raise Not Found: True

![](/files/Q3wIhdgoz9WDsobGRI8o)

We will perform the following calls:

* `https://my-api.com/data?page=1`
* `https://my-api.com/data?page=2`

Until we reach a 404 when no page will return us then we will stop the data fetching.

</details>

**Cursor based pagination (CursorBasedPaginationConfig)**

This configuration implements cursor-based pagination

**Parameters**:

* `cursor_name`: (string) **mandatory** Parameter name for the cursor (default: `cursor`)
* `cursor_filter`: (string) **mandatory** JQ filter to extract next cursor

**Use case**: APIs using cursors/tokens for pagination.

<details>

<summary>cursor-based example</summary>

Let's take the following configuration

* "cursor\_name": "token"
* "cursor\_filter": ".metadata.next\_cursor"

![](/files/dnUpZfREZTp9vUkoz7C0)

We will perform the following call:

* `https://my-api.com/data`

`{`

`"data": [`

`... // API data`

`],`

`"metadata": {`

`"next_cursor": "abcde12345"`

`}`

`}`

* `https://my-api.com/data?token=abcde12345`

Until the next cursor is null

</details>

**Hyper Media Pagination (HyperMediaPaginationConfig)**

This configuration implements HATEOAS-style pagination using next links.

{% hint style="warning" %}
For this pagination type, all URLs need to have the same `base_url` configured. if the configured `base_url` is `https://my-api.com/data` then all next page urls must be at least `https://my-api.com/data/_whatever`
{% endhint %}

**Parameters**:

* `next_link_filter`: **mandatory (string)** JQ filter to extract next page URL
* `next_link`: **mandatory (string)** field which bears the next link URL

**Use case**: RESTful APIs following HATEOAS principles.

<details>

<summary>Hyper Media pagination example</summary>

Let's take the following configuration:

* "next\_link\_filter": ".metadata.next\_page"
* "next\_link": "next"

![](/files/OV1Cd9jiZ1P9nUMVSmK8)

We will perform the following call:

* `GET https://my-api.com/data`

// response example

`{`

`"data": [`

`... // API data`

`],`

`"metadata": {`

`"next_page": "https://my-api.com/data/next/page/2?auth_token=4321"`

`}`

`}`

* `GET https://my-api.com/data/next/page/2?auth_token=4321`

Until the next page URL is null

</details>

## Example of connection to Open Data Paris

### Setting up the connection to Open Data Paris

```
name: open-data-paris
baseroute: https://opendata.paris.fr/api/
```

### Selecting data from Open Data Paris

```
Dataset: books
Method: GET
URL: records/1.0/search/
Dataset: les-1000-titres-les-plus-reserves-dans-les-bibliotheques-de-pret
Facet: auteur
Filter: .records[].fields
```

The JSON response looks like this:

{% code overflow="wrap" %}

```json
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"       },       ...     ]   }
```

{% endcode %}

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

{% hint style="info" %}
**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 for simple tasks, but if you dig a little deeper, you’ll find a feature functional programming language hiding underneath.
{% endhint %}

{% hint style="warning" %}
**Performance**\
If the HTTP API connector is used in a live context, make sure that the API is performant enough and is able to retrieve data fast. In order to have suitable performance, make sure to retrieve a limited amount of data since its need additional transformation in order to unnest the data (in the case of json response).
{% endhint %}

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}


# Setting up an ODBC Connector

### Overview¶

You can use the generic ODBC connector for any of your data source compliant with ODBC.

{% hint style="warning" %}
The relevant **driver** must be installed and configured on your Toucan Toco workspace.\
\
see [Driver information](#driver-installation) for more information
{% endhint %}

#### Datasource configuration

* First, ask your Toucan Toco contact to install the driver on your instance

Go to the Datahub datasources tab

Mandatory parameters

* **Connection string**: a string of parameters used to establish a connection between an application and a database. It contains essential information needed to identify and access the data source.

Here are the key aspects of ODBC connection strings:

**Format and Structure**

Connection strings typically follow this format:

```
textkeyword1=value1;keyword2=value2;keyword3=value3
```

Each keyword-value pair is separated by a semicolon, and there's no space between the pairs.

**Common Parameters**

ODBC connection strings often include the following parameters:

* **DRIVER**: Specifies the ODBC driver to use
* **SERVER** or **ADDRESS**: The server name or IP address
* **DATABASE**: The name of the database to connect to
* **UID** (User ID) and **PWD** (Password): Authentication credentials
* **PORT**: The port number for the database server (if non-default)

**Example Connection Strings**

Here are some examples of ODBC connection strings:

1. For SQL Server:

   ```
   textDriver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
   ```
2. For a trusted connection:

   ```
   textDriver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes;
   ```
3. For a non-default port:

   ```
   textDriver={SQL Server};Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
   ```

This [website](https://www.connectionstrings.com) can serve as a reference for connection strings

**Query configuration**

To create a dataset, click on "Create a new dataset" on the connection of the connector you just set up

You will land on the query configuration part, to create :

Mandatory parameters

* Query: string type your SQL query in this field.

You can click on `Preview` to see your results and `Validate` to validate your query then on `Create` to create a dataset

### Driver installation

* Other driver installation scripts are available for:
  * [databricks](https://github.com/ToucanToco/toucan-connectors/blob/master/toucan_connectors/install_scripts/databricks.sh)
  * [microsoft sql](https://github.com/ToucanToco/toucan-connectors/blob/master/toucan_connectors/install_scripts/mssql.sh)
  * [oracle](https://github.com/ToucanToco/toucan-connectors/blob/master/toucan_connectors/install_scripts/oracle.sh)
* For syntax refer to:
  * [databricks](https://docs.databricks.com/integrations/bi/jdbc-odbc-bi.html)
  * [microsoft sql](https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Windows)
  * [oracle](https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-Oracle-from-RHEL-or-Centos)
* For reference, you can check [pyodbc](https://github.com/mkleehammer/pyodbc/wiki)


# Setting up a SOAP Connector

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

<figure><img src="/files/9xdIkJZPOzWOrX5ptX3Y" alt="Add a connector"><figcaption><p>Add a connector</p></figcaption></figure>

* Look up for SOAP in connector’s list

<figure><img src="/files/iwFuBh81cCO42vcl9uS6" alt="soap_logo"><figcaption><p>soap_logo</p></figcaption></figure>

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`

<figure><img src="/files/FAS96xzWDMSdu8aHXdiL" alt="soap_connector_form"><figcaption><p>soap_connector_form</p></figcaption></figure>

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)

<figure><img src="/files/1hHQzWfrDu68P8cZfdSu" alt="soap_ds"><figcaption><p>soap_ds</p></figcaption></figure>

Below is an example of Tooltip

<figure><img src="/files/suOgMYnlBRGGd09cJpLS" alt="tooltip_soap"><figcaption><p>tooltip_soap</p></figcaption></figure>

Your **SOAP** Connector is now configured 🚀


# Database and data warehouse Connectors

How to configure database and data warehouses in Toucan


# Add a PostgreSQL connection

How to configure a postgreSQL connection in Toucan.

## Connector Features

You can use the Toucan PostgreSQL connector to connect to a PostgreSQL cluster with a basic authentication and access `tables` or `views` with a SQL query or by [using our no-code form ](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode).

With a PostgreSQL connection, you can fetch data from your PostgreSQL database to fill your charts and dashboards.

{% hint style="info" %}
**Changelog**\
**December 23**\
\- this connector is [NativeSQL](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql) compatible\\

**April 24**\
\- This connector supports materialized views\
\
**November 2024**\
\- This connector supports [hybrid pipelines ](/data-management-in-datahub/datasets-in-toucan/preparing-data/hybrid-pipeline)\\

**July 25**\
\- This data connector is supported for connection and [NativeSQL](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql) by our [new Data Execution system](https://www.toucantoco.com/en/blog/multi-tenant-architecture)
{% endhint %}

## Configuring a PostgreSQL connection

Follow the steps described in [Add a connector](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector), choose `PostgreSQL` and fill in the connection information

<table><thead><tr><th>Field</th><th width="109.09765625">Format / Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td>Name (mandatory)</td><td>String</td><td>Use it to identify your connection</td><td><em>MyPostGreSQLConnection</em></td></tr><tr><td>Host (mandatory)</td><td>String</td><td>The domain name or IP address of your database server</td><td><em>"db.example.com"</em> or <em>"192.168.1.100"</em></td></tr><tr><td>Port (mandatory)</td><td>Integer</td><td>The listening port of your database server</td><td><em>3306</em></td></tr><tr><td>User (mandatory)</td><td>String</td><td>Your login username</td><td><em>myuser</em></td></tr><tr><td>Password (mandatory)</td><td>String</td><td>Your login password</td><td><em>secretpassword123</em></td></tr><tr><td>Default database (optional)</td><td>String</td><td>The default database to connect to</td><td>postgres</td></tr><tr><td>Charset (optional)</td><td>String</td><td>Character encoding for the connection</td><td>"<em>utf8mb4" (</em>default)</td></tr><tr><td>Connect Timeout (optional)</td><td>Integer</td><td>Connection timeout in seconds</td><td>30</td></tr><tr><td>Retry Policy (optional)</td><td>Boolean</td><td><p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max_delay: in seconds, above the connection is dropped</li><li>wait_time: time in seconds between each retry</li></ul></td><td></td></tr><tr><td>Slow Queries' Cache Expiration Time (optional)</td><td>Integer</td><td>Slow queries' cache expiration time in seconds</td><td></td></tr><tr><td>Include materialized views</td><td>Boolean</td><td>Show or hide materialized views when you are connected to your Postgres cluster</td><td>N/A</td></tr></tbody></table>

* Click on the `TEST CONNECTION` button then `SAVE` the connection

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

## Create a dataset from a PostgreSQL connection

{% hint style="info" %}
This data connector is supported in [simple and code/SQL mode](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode) with:

* our current Data Execution system
* new [Data Execution system](/data-management-in-datahub/new-data-execution-system)
  {% endhint %}

To create a dataset from PostgreSQL, refer to this [dedicated guide](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector) to:

* Select a `Database`
* Select a `Schema`
* Select `Table` or `Views`
* Keep the `columns` you need

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}

### How to troubleshoot a PostgreSQL connection

Generally ensure all mandatory fields (`Name`, `Host`, `Port`, `User`, `Password`) are filled.

#### Test connection modal

Use the [Managing Connectors](/data-management-in-datahub/datasources-in-toucan/managing-connectors#test-connection) modal to troubleshoot a misconfiguration

* If the modal displays a title Cannot establish connection with a warning icon in front of a line, check this specific connection step
* Inspect the modal error message for details—typically indicates network issues, authentication errors, or database unavailability.

#### Network access

If the modal displays a warning icon on `Host resolved` line and a displayed message:

`failed to lookup address information: Name or service not known`

`[Errno -2] Name or service not known`

* Verify that the Host (IP or DNS) is **reachable** from Toucan IP and is not blocked by firewalls.

{% hint style="info" %}
Check our IP for our [Current Data Execution System](https://toucantoco.com/public-servers-list.html) and [New Data Execution System](/data-management-in-datahub/new-data-execution-system#ip-allowlisting)
{% endhint %}

If the modal displays a warning icon on `Port opened` line and a displayed message:

`warning icon on port opened`\
`Connection refused (os error 111)`

* Check that the `Port` (default is 5432) matches the **PostgreSQL server’s configuration** and is **open**.

If encountering SSL-related errors, check server SSL configuration

#### Authentication

If the modal displays a warning icon on `Authenticated to PostgreSQL` line and the message displayed is:

`error returned from database: password authentication failed for user` or

`(psycopg.OperationalError) connection failed: connection to server at "{{your_server_ip}}", port 28561 failed: FATAL: password authentication failed for user "{{your_user}}" connection to server at "{{your_server_ip}}", port {{your_server_port}} failed: FATAL: password authentication failed for user "{{your_user}}"`

* Confirm `User` and `Password` are valid and have been granted connect permissions to the target database.

#### Other options

**Default database**

If the modal displays a warning icon on `Authenticated to PostgreSQL` line and the message displayed is:

`error returned from database: database "{{default_database}}" does not exist` or

`(psycopg.OperationalError) connection failed: connection to server at "{{your_server_ip}}", port {{your_server_port}} failed: FATAL: database "{{database}}" does not exist`

* Check the default database exists or that your user has acceess to it.

**Charset**

* If specified, make sure `Charset` is supported by the database instance.

**Connect timeout**

* Adjust `Connect Timeout` if timeouts occur, especially in remote or slow network situations.

**Advanced troubleshooting**

* Review logs on PostgreSQL server for more detailed error information.
* For persistent issues, attempt to connect using CLI with the same parameters to isolate issues.


# Add a Google Big Query connector

## Connector features

You can use the Toucan Google Big query connector to connect to Google Big Query with a service account authentication or a JWT token and access `tables` or `views` with a SQL query or by [using our no-code form ](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode).

With this connection, you can fetch data from your Google Big Query to fill your charts and dashboards.

{% hint style="info" %}
**Changelog**\
**December 23**\
\- This connector is [NativeSQL](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql) compatible\
\
**November 2024**\
\- This connector supports [hybrid pipelines ](/data-management-in-datahub/datasets-in-toucan/preparing-data/hybrid-pipeline)\\

**July 25**\
\- This data connector is supported for connection and [NativeSQL](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql) by our [new Data Execution system](https://www.toucantoco.com/en/blog/multi-tenant-architecture)
{% endhint %}

## Configuring a Google Big Query connection

{% hint style="info" %}
We offer two ways to connect to Google Big Query:

* with the information you can find in the service account json
* or by crafting a JWT token a JWT token and transmit this JWT token to Toucan, where it will be used to access your Google Big Query dataset.
  {% endhint %}

<details>

<summary>Prerequisites when using service_account to authenticate</summary>

You will need to have access to a [Google Cloud Platform](https://cloud.google.com/) account with a [project](https://cloud.google.com/storage/docs/projects) you would like to use in Toucan. Consult Google Cloud Platform documentation for how to [create and manage a project](https://cloud.google.com/resource-manager/docs/creating-managing-projects). This project should have a BigQuery dataset for Toucan to connect to.

**Google Cloud Platform: creating a service account and JSON file**

To enable Toucan to access your BigQuery dataset, you will first require a [service account](https://cloud.google.com/iam/docs/service-account-overview) JSON file. Service accounts are designed for non-human users, such as applications like Toucan, to [authenticate](https://en.wikipedia.org/wiki/Authentication) and [authorize](https://en.wikipedia.org/wiki/Authorization) their API requests.

Here's a step-by-step process for creating the service account JSON file, as outlined in Google's documentation for [setting up a service account](https://cloud.google.com/iam/docs/creating-managing-service-accounts) for your BigQuery dataset:

1. **Create a Service Account:**
   * Access your Google Cloud Platform project console.
   * In the main sidebar menu on the left, navigate to the **IAM & Admin** section.
   * Select "**Service account**" Existing service accounts, if any, will be listed.
   * At the top of the screen, click on "**+ CREATE SERVICE ACCOUNT.**"
2. **Fill Out Service Account Details:**
   * Provide a name for the service account.
   * Add a description (the service account ID will be generated once you provide a name).
   * Click the "**Create**" button to create the service account.
3. **Grant Access to the Service Account:**
   * To allow Toucan to view and run queries on your dataset, you need to assign roles to the service account.
   * Ensure that you assign the following roles to the service account:
     * BigQuery Data Viewer
     * BigQuery Metadata Viewer
     * BigQuery Job User (distinct from BigQuery User)
   * For more detailed information on roles in BigQuery, consult [Google Cloud Platform's documentation](https://cloud.google.com/bigquery/docs/access-control).
4. **Create a Key:**
   * Once you've assigned the necessary roles to the service account, click on the "**Create Key**" button.
   * Choose **JSON** as the **key type**.
   * The JSON file containing the credentials will be downloaded to your computer.

{% hint style="warning" %}
**The key can be downloaded only once**. If you delete it, you will need to create a new service account with identical roles to obtain another key.
{% endhint %}

</details>

<details>

<summary>Prerequisites when using JWT token to authenticate</summary>

```python
# the following code generate a JWT using using google.auth library
import time

# pip install google-api-python-client 
from google.auth  import crypt, jwt

def generate_jwt(
    sa_keyfile, // the path to service account json
    sa_email="account@project-id.iam.gserviceaccount.com",
    audience="your-service-name",
    expiry_length=3600,
):
    '''Generates a signed JSON Web Token using a Google API Service Account.''' 

    now = int(time.time())

    # build payload
    payload = {
        "iat": now,
        "exp": now + expiry_length,
        "iss": sa_email,
        "aud": audience,
        "sub": sa_email,
        "email": sa_email,
    }

    # sign with keyfile
    signer = crypt.RSASigner.from_service_account_file(sa_keyfile)
    jwt_token = jwt.encode(signer, payload)

    return jwt_token
```

</details>

Follow the steps described in [Add a connector](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector), choose `Google Big Query` and fill out the form with the following info:

<table><thead><tr><th>Field</th><th width="84.1015625">Format / Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td>Name (mandatory)</td><td>String</td><td>Use it to identify your connection</td><td><em>my-google_big_query_conneciion</em></td></tr><tr><td>GoogleCredentials</td><td>Boolean</td><td>Use this option if you want to authenticate with the information you will find in your service_account json file</td><td></td></tr><tr><td>Service Account</td><td>String</td><td>service_account value located in your service account json file</td><td>(see your file)</td></tr><tr><td>Project ID</td><td>String</td><td>project id value located in your service account json file</td><td>(see your file)</td></tr><tr><td>Private Key Id</td><td>String</td><td>Private Key Id value located in your service account json file</td><td>(see your file)</td></tr><tr><td>Private key</td><td>String</td><td>Private key value located in your service account json file</td><td>(see your file)</td></tr><tr><td>Client email</td><td>String</td><td>Client email value located in your service account json file</td><td>(see your file)</td></tr><tr><td>Client ID</td><td>String</td><td>Client ID value located in your service account json file</td><td>(see your file)</td></tr><tr><td>Authentication URI</td><td>String</td><td>Auth URI value located in your service account json file</td><td>(see your file)</td></tr><tr><td>Token URI</td><td>String</td><td>Token URI value located in your service account json file</td><td>(see your file)</td></tr><tr><td>Authentication provider X509 certificate URL</td><td>String</td><td>Auth Provider Cert URL value located in your service account json file</td><td>(see your file)</td></tr><tr><td>Client X509 certification URL</td><td>String</td><td>Client Cert UR value located in your service account json file</td><td>(see your file)</td></tr><tr><td>JWTCredentials</td><td>Boolean</td><td>Use this option if you want to authenticate by sending a JWT token</td><td></td></tr><tr><td>Project ID</td><td>String</td><td>Project ID corresponds to the id of your Google Big Query project</td><td>project_gbq_id</td></tr><tr><td>Json Web token (JWT) signed</td><td>String</td><td>corresponds to your JWT token you obtain by signing it with your service account json</td><td>token</td></tr><tr><td>Dialect</td><td>Boolean</td><td>Tick this case if you want to select a specific dialect used by your server between <code>legacy</code> and <code>standard</code> as query standard more information on this <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax">documentation</a>. By default we use standard standard</td><td></td></tr><tr><td>Oauth2 Scope</td><td>Array</td><td>OAuth 2.0 scopes define the level of access you need to request the Google APIs for more information see this <a href="https://developers.google.com/identity/protocols/oauth2/scopes?hl=fr">documentation</a></td><td>List of URLs</td></tr><tr><td>Retry Policy (optional)</td><td>Boolean</td><td><p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max_delay: in seconds, above the connection is dropped</li><li>wait_time: time in seconds between each retry</li></ul></td><td></td></tr><tr><td>Slow Queries' Cache Expiration Time (optional)</td><td>Integer</td><td>Slow queries' cache expiration time in seconds</td><td></td></tr></tbody></table>

* Click on the `TEST CONNECTION` button then `SAVE` the connection

  {% hint style="success" %} After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab {% endhint %}

{% hint style="info" %}
If you use Toucan by embedding it in other software. You could set up the two credentials sections (GoogleCredentials and JWTCredentials) and use variables (for example `{{user.secrets.JWT}}` and `{{user.secrets.project_id}}` to set up the JWTCredentials.

This way, you can design your application from a Google Big Query project linked to test data. And in production, you could pass your JWT and the project id linked to your user's production data into an embedContext. They will be interpolated by the values Toucan finds in your token.
{% endhint %}

## Create a dataset from a Google Big Query connection

{% hint style="info" %}
This data connector is supported in [simple and code/SQL mode](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode) for our current Data Execution system and new Data Execution systems
{% endhint %}

* Select a `DB_schema`
* Select a `Table`
* Only keep the columns you need

{% hint style="info" %}
For more info, see the dedicated section [Create a new dataset from a dataset](/data-management-in-datahub/datasets-in-toucan/managing-datasets/creating-datasets)
{% endhint %}

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}


# Add a MySQL connector

## Connector features

You can use the Toucan MySQL connector to connect to a mySQL cluster with a basic authentication and/or a chain certificate and access `tables` with a SQL query or by [using our no-code form ](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode).

With this connection, you can fetch data from your mySQL to fill your charts and dashboards.

{% hint style="info" %}
**Changelog**

**July 25**

* This data connector is supported for connection and [NativeSQL](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql) by our [new Data Execution system](https://www.toucantoco.com/en/blog/multi-tenant-architecture)
  {% endhint %}

## Configuring a MySQL connection

Follow the steps described in [Add a connector](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector), choose `MySQL` and fill out the form with the following info:

<table><thead><tr><th>Field</th><th width="137.91796875">Format / Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td>Name (mandatory)</td><td>String</td><td>Use it to identify your connection</td><td><em>MySQLConnection</em></td></tr><tr><td>Host (mandatory)</td><td>String</td><td>The domain name or IP address of your database server</td><td><em>"db.example.com"</em> or <em>"192.168.1.100"</em></td></tr><tr><td>Port (mandatory)</td><td>String</td><td>The listening port of your database server</td><td><em>3306</em></td></tr><tr><td>User (mandatory)</td><td>String</td><td>Your login user</td><td><em>myuser</em></td></tr><tr><td>Password (mandatory)</td><td>String</td><td>Your login password (this value will be stored as a secret)</td><td><em>secretpassword123</em></td></tr><tr><td>Charset (optional)</td><td>String</td><td>Character encoding for the connection</td><td><em>"utf8mb4"</em> (default value)</td></tr><tr><td>Charset Collation (optional)</td><td>String</td><td>The charset's collation for server connections</td><td><em>"utf8mb4_unicode_ci"</em></td></tr><tr><td>Connect Timeout (optional)</td><td>Integer</td><td>Connection timeout in seconds</td><td><em>30</em></td></tr><tr><td>SSL Mode (optional)</td><td>Enum</td><td><p>SSL Mode for MySQL server connection. If this option is disabled, the SSL Mode defaults to PREFERRED (use TLS if available)<br>Options: <code>VERIFY_IDENTITY</code>, <code>VERIFY_CA</code>, <code>REQUIRED</code><br></p><ul><li><code>REQUIRED</code>: Force TLS (without any identity verification and a CA cert),<br>Required fields: none</li><li><code>VERIFY_CA</code>: Force TLS and check server certificat against <code>SSL_CA</code> certificate<br>Required fields: <code>SSL_CA</code></li><li><code>VERIFY_IDENTITY</code>: Force TLS and check server certificate against <code>SSL_CA</code>, check hostname in the certificate (common name/dns names)<br>Required fields: <code>SSL_CA</code></li></ul></td><td><em>VERIFY_CA</em></td></tr><tr><td>SSL CA</td><td>String</td><td>CA certificate in PEM format, used for SSL Mode see SSL Mode for more information.</td><td><em>-----BEGIN CERTIFICATE-----</em><br><em>...</em><br><em>-----END CERTIFICATE-----</em></td></tr><tr><td>SSL Cert</td><td>String</td><td>X509 certificate in PEM format used for client authentication (mTLS)</td><td><em>-----BEGIN CERTIFICATE-----</em><br><em>...</em><br><em>-----END CERTIFICATE-----</em></td></tr><tr><td>SSL Key</td><td>String</td><td>Private key in PEM format used for client authentication (mTLS).</td><td><em>-----BEGIN PRIVATE KEY-----</em><br><em>...</em><br><em>-----END PRIVATE KEY-----</em></td></tr><tr><td>Retry Policy (optional)</td><td>Boolean</td><td><p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max_delay: in seconds, above the connection is dropped</li><li>wait_time: time in seconds between each retry</li></ul></td><td></td></tr><tr><td>Slow Queries' Cache Expiration Time (optional)</td><td>Integer</td><td>Slow queries' cache expiration time in seconds</td><td></td></tr></tbody></table>

* Click on the `TEST CONNECTION` button then `SAVE` the connection

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

## Create a dataset from a MySQL connection

{% hint style="info" %}
This data connector is supported in

* [code/SQL mode](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode) for our current Data Execution system
* both modes with our new Data Execution System
  {% endhint %}

To create a dataset from MySQL, click on the "create from icon", you will then be able to:

* Select the `Database`
* Select the `Schema`
* Select `Table` or `Views`
* Only keep columns you need

{% hint style="info" %}
For more info, see the dedicated section [Create a new dataset from a dataset](/data-management-in-datahub/datasets-in-toucan/managing-datasets/creating-datasets)
{% endhint %}

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}


# Add a Snowflake connector

## Connector features

You can use the Toucan Snowflake connector to connect to your Snowflake account with a key-pair authentication or basic authentication and access `tables` or `views` with a SQL query or by [using our no-code form ](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode).

With this connection, you can fetch data from your Snowflake to fill your charts and dashboards.

{% hint style="info" %}
**Changelog**

**December 23**

* This connector is [NativeSQL](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql) compatible<br>

**November 2024**

* This connector supports [hybrid pipelines](/data-management-in-datahub/datasets-in-toucan/preparing-data/hybrid-pipeline)

**September 25**

* This connector supports key-pair authentification following [Snowflake rules](https://www.snowflake.com/en/blog/blocking-single-factor-password-authentification)<br>

**October 25**

* This data connector is supported for connection and [NativeSQL](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode) by our new [Data Execution system](/data-management-in-datahub/new-data-execution-system)

**February 26**

* Modification of the account name format
  {% endhint %}

## Configuring a Snowflake connection

Follow the steps described in [Add a connector](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector), choose `Snowflake` and fill out the form with the following info:

<table><thead><tr><th>Field</th><th>Format / Type</th><th width="263.109375">Description</th><th>Example</th></tr></thead><tbody><tr><td>Name (mandatory)</td><td>String</td><td>Use it to identify your connection</td><td><em>MySnowflakeConnection</em></td></tr><tr><td>Account (mandatory)</td><td>String</td><td>in the <code>orgname-accountname</code> format. <a href="https://docs.snowflake.net/manuals/user-guide/python-connector-api.html#label-account-format-info">You can read more about it here</a></td><td>&#x3C;<em>account_identifier></em></td></tr><tr><td>Authentication method (mandatory)</td><td>Enum</td><td>List with <code>Snowflake (ID+Password)</code> (deprecated by Snowflake in November 2025) <code>Key pair</code> and <code>oAuth</code> (deprecated)</td><td><em>Key pair</em></td></tr><tr><td>User</td><td>String</td><td><br>- <code>Snowflake (ID+Password)</code><br>- <code>Key pair</code><br>The user with rights to access to the Snowflake database</td><td>my_snowflake_user</td></tr><tr><td>Password</td><td>String</td><td><p>- <code>Snowflake (ID+Password)</code><br>- <code>Key pair</code></p><p>Password associated to the user or to the private key if your key is protected by a password (will be stored as a secret)</p></td><td><em>mysecretpassword</em></td></tr><tr><td>Private Key</td><td>String</td><td><p>- <code>Key pair</code></p><p>the key generated to access to your Snowflake database the key in <strong>PEM-encoded PKCS#8 format</strong><br>(will be stored as a secret)</p></td><td><em>-----BEGIN ENCRYPTED PRIVATE KEY-----</em><br><em>MIIJpDBW[...]-----END ENCRYPTED PRIVATE KEY-----</em></td></tr><tr><td>Token Endpoint<br></td><td>String</td><td><p>- <code>oAuth</code></p><p>The token endpoint URL</p></td><td><em>https://&#x3C;your_snowflake_account>.snowflakecomputing.com/oauth/token-request</em></td></tr><tr><td>Token Endpoint Content Type</td><td>String</td><td><p>- <code>oAuth</code></p><p>The content type to use when requesting the token endpoint</p></td><td><em>application/x-www-form-urlencoded</em></td></tr><tr><td>Role (optional)</td><td>String</td><td>The user role that you want to connect with. See more details <a href="https://docs.snowflake.com/en/user-guide/admin-user-management.html#user-roles">here</a>.</td><td><em>USER</em></td></tr><tr><td>Default Warehouse (mandatory)</td><td>String</td><td>The default warehouse that shall be used for any data source</td><td><em>COMPUTE_WH</em></td></tr><tr><td>Retry Policy (optional)</td><td>Boolean</td><td><p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max_delay: in seconds, above the connection is dropped</li><li>wait_time: time in seconds between each retry</li></ul></td><td></td></tr><tr><td>Slow Queries' Cache Expiration Time (optional)</td><td>Integer</td><td>Slow queries' cache expiration time in seconds</td><td></td></tr></tbody></table>

* Click on the `TEST CONNECTION` button then `SAVE` the connection

  {% hint style="success" %} After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab {% endhint %}

## Create a dataset from a Snowflake connection

{% hint style="info" %}
This data connector is supported in [simple and code/SQL mode](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode) with:

* our current Data Execution system
* our new [Data Execution System](/data-management-in-datahub/new-data-execution-system#general)
  {% endhint %}

To create a dataset from Snowflake, click on the "create from" icon, you will then be able to:

* Select the `Database`
* Select the `Data warehouse`
* Select the `Schema`
* Select `Table` or `Views`
* Only keep the columns you need

{% hint style="info" %}
For more info, see the dedicated section [Create a new dataset from a dataset](/data-management-in-datahub/datasets-in-toucan/managing-datasets/creating-datasets)
{% endhint %}

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}

### How to troubleshoot a Snowflake connection

Generally ensure all mandatory fields (`Name`, `Account`, `User`, `Private Key`, `Password` if your private Key is protected by a password) are filled.

#### Test connection modal

Use the [Managing Connectors](/data-management-in-datahub/datasources-in-toucan/managing-connectors#test-connection) modal to troubleshoot a misconfiguration

The Snowflake test connection modal is composed fo two steps:

* `Connection to Snowflake` : check if Toucan is allowed to connect to Snowflake, on the network, the `account` listed is a Snowflake account, the `user` and `private key` are valid means of authentication.
* `Default warehouse exists` : checks if the Default Warehouse entered is valid / or accessible to your user.

#### Network access

Verify that your Snowflake account is **reachable** from Toucan IP and is not blocked by firewalls.

If you have made an error entering your account, you will have an

`internal server error`

{% hint style="info" %}
Check our IP for our [Current Data Execution System](https://toucantoco.com/public-servers-list.html) and [New Data Execution System](/data-management-in-datahub/new-data-execution-system#ip-allowlisting)
{% endhint %}

#### Authentication

If the modal displays a warning icon on `Connection to Snowflake` line and the message displayed is:

`Connection failed for the user '{{user_entered}}', please check your credentials`

* Confirm `User` , `Private Key`, `Password` are valid and have been granted connect permissions to the target warehouse.

#### Other options

**Default warehouse**

If the modal displays a warning icon on `Default warehouse exists` line and the message displayed is:

`The warehouse '{{YOUR_WAREHOUSE}}' does not exist.`

* Check the spelling of the warehouse that it exists or that your user has access to it.

**Advanced troubleshooting**

* Review logs on your Snowflake account for more detailed error information.
* For persistent issues, attempt to connect using CLI with the same parameters to isolate issues.


# Add a Microsoft SQL (MSSQL) Server connector

## Connector features

You can use the Toucan Microsoft SQL Server (MSSQL) connector to connect to your MSSQL account with a basic authentication and access `tables` with a SQL query.

With this connection, you can fetch data from your MSSQL to fill your charts and dashboards.

{% hint style="info" %}
**Changelog**

**April 25**\
\- This connector supports the option Trust server certificate

**October 25**\
\- This data connector is supported for connection and [NativeSQL](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode) by our new
{% endhint %}

## Configuring a Microsoft Server SQL connector (MSSQL ) connection

Follow the steps described in [Add a connector](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector), choose `MSSQL` and fill out the form with the following info:

| Field                                          | Format / Type | Description                                                                                                                                                                                                                                                                             | Example                                 |
| ---------------------------------------------- | ------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------- |
| Name (mandatory)                               | String        | Use it to identify your connection                                                                                                                                                                                                                                                      | *MyMsSQLConnection*                     |
| Host (mandatory)                               | String        | The domain name or IP address of your database server                                                                                                                                                                                                                                   | "*db.example.com*" or "*192.168.1.100*" |
| Port (optional)                                | Integer       | The listening port of your database server                                                                                                                                                                                                                                              | *1433*                                  |
| User (mandatory)                               | String        | Your login username                                                                                                                                                                                                                                                                     | *my\_user*                              |
| Password (mandatory)                           | String        | Your login password                                                                                                                                                                                                                                                                     | *my\_password*                          |
| Connect Timeout (optional)                     | Integer       | Connection timeout in seconds                                                                                                                                                                                                                                                           | *30*                                    |
| Trust server certificate (optional)            | Boolean       | Disable server certificate validation, can be used if the certificate used is untrusted. Connexion stays encrypted                                                                                                                                                                      |                                         |
| Retry Policy (optional)                        | Boolean       | <p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max\_delay: in seconds, above the connection is dropped</li><li>wait\_time: time in seconds between each retry</li></ul> |                                         |
| Slow Queries' Cache Expiration Time (optional) | Integer       | Slow queries' cache expiration time in seconds                                                                                                                                                                                                                                          |                                         |

Click on the `TEST CONNECTION` button then `SAVE` the connection

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

## Create a dataset from a MSSQL connection

To create a dataset from MSSQL, click on the "create a new dataset from this connector", you will then be able to:

* Select the `Database`
* Select the `Table`
* and write your SQL query in `Query`

The fields: `Validation rules`, `Parameters`,`Slow queries cache expiration time` are optional

{% hint style="info" %}
After selecting data from your connector you will be able to create a dataset thanks to YouPrep

For more info, see the dedicated section [Creating datasets](/data-management-in-datahub/datasets-in-toucan)
{% endhint %}

{% hint style="warning" %}
**Additional** **notes**

* We use for the the MSSQL connector uses the ODBC Driver 18 for SQL Server
* SQL Server will use the default database for the user
  {% endhint %}


# Add an Azure SQL connector

How to connect to an Azure SQL database.

## Connector features

You can use the Toucan Azure SQL connector to connect to your Azure SQL cluster with a basic authentication and access `tables` with a SQL query.

With this connection, you can fetch data from your Azure SQL to fill your charts and dashboards.

{% hint style="info" %}
**Changelog**

**April 25**\
\- This connector supports the option Trust server certificate

**October 25**\
\- This data connector is supported for connection and [NativeSQL](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode) by our new
{% endhint %}

## Configuring an Azure SQL connection

Follow the steps described in [Add a connector](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector), choose `AzureSQL` and fill out the form with the following info:

| Field                                          | Format / Type | Description                                                                                                                                                                                                                                                                             | Example                                 |
| ---------------------------------------------- | ------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------- |
| Name (mandatory)                               | String        | Use it to identify your connection                                                                                                                                                                                                                                                      | *MyMsSQLConnection*                     |
| Host (mandatory)                               | String        | The domain name or IP address of your database server                                                                                                                                                                                                                                   | "*db.example.com*" or "*192.168.1.100*" |
| Port (optional)                                | Integer       | The listening port of your database server                                                                                                                                                                                                                                              | *1433*                                  |
| User (mandatory)                               | String        | Your login username                                                                                                                                                                                                                                                                     | *my\_user*                              |
| Password (mandatory)                           | String        | Your login password                                                                                                                                                                                                                                                                     | *my\_password*                          |
| Connect Timeout (optional)                     | Integer       | Connection timeout in seconds                                                                                                                                                                                                                                                           | *30*                                    |
| Trust server certificate (optional)            | Boolean       | Disable server certificate validation, can be used if the certificate used is untrusted. Connexion stays encrypted                                                                                                                                                                      |                                         |
| Retry Policy (optionl)                         | Boolean       | <p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max\_delay: in seconds, above the connection is dropped</li><li>wait\_time: time in seconds between each retry</li></ul> |                                         |
| Slow Queries' Cache Expiration Time (optional) | Integer       | Slow queries' cache expiration time in seconds                                                                                                                                                                                                                                          |                                         |

Click on the `TEST CONNECTION` button then `SAVE` the connection

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

## Create a dataset from an Azure SQL connection

To create a dataset from AzureSQL, click on the "create from icon", you will then be able to:

* Select the `Database`
* Add a Query: you can query `tables`, `views`, and even stored procedures using the AZURE SQL.

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}


# Add an AWS Athena connector

How to connect to an AWS Athena account in Toucan

## Connector features

You can use the Toucan AWS Athena connector to connect to your AWS Athena account with a AWS Access key and AWS secret access key and access `tables` with a SQL query or by [using our no-code form ](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode).

With this connection, you can fetch data from your AWS Athena account to fill your charts and dashboards.

{% hint style="info" %}
**Changelog**

**December 23**\
\- this connector is [NativeSQL](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql) compatible\
\
**November 2024**\
\- This connector supports [hybrid pipelines ](/data-management-in-datahub/datasets-in-toucan/preparing-data/hybrid-pipeline)\\

**July 25**\
\- This data connector is supported for connection and [NativeSQL](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql) by our [new Data Execution system](https://www.toucantoco.com/en/blog/multi-tenant-architecture)
{% endhint %}

## Configuring an AWS Athena connection

Follow the steps described in [Add a connector](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector), choose `AWS Athena` and fill out the form with the following info:

<table><thead><tr><th width="171.7421875">Field</th><th width="140.01953125">Format / Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td>Name (mandatory)</td><td>String</td><td>Use it to identify your connection</td><td><em>MyAthenaConnection</em></td></tr><tr><td>S3 Output Bucket (mandatory)</td><td>String</td><td>the S3 bucket and prefix where results from your queries will be saved</td><td><em>s3://mybucket/athena-queries</em></td></tr><tr><td>AWS Access Key Id (mandatory)</td><td>String</td><td>the ID of the the AWS access key that will be used to connect to Athena.</td><td><em>AKIAIOSFODNN7EXAMPLE</em></td></tr><tr><td>AWS Secret Access Key (mandatory)</td><td>String</td><td>the AWS secret access key that will be used to connect to Athena. (will be stored as a secret)</td><td><em>wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY</em></td></tr><tr><td>Region Name (mandatory)</td><td>String</td><td>the name of the AWS region you need to query.</td><td><em>eu-west-3</em></td></tr><tr><td>Retry Policy (optional)</td><td>Boolean</td><td><p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max_delay: in seconds, above the connection is dropped</li><li>wait_time: time in seconds between each retry</li></ul></td><td></td></tr><tr><td>Slow Queries' Cache Expiration Time (optional)</td><td>Integer</td><td>Slow queries' cache expiration time in seconds</td><td></td></tr></tbody></table>

Click on the `TEST CONNECTION` button then `SAVE` the connection

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

{% hint style="info" %}
Depending on your rights, you will have the “Can list databases” checked or not, but note that this doesn’t prevent you to be able to use the connector itself.
{% endhint %}

## Create a dataset from an AWS Athena connection

{% hint style="info" %}
This data connector is supported in [simple and code/SQL mode](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode) for our current Data Execution system and new Data Execution systems
{% endhint %}

To create a dataset from AWS Athena, click on the "create from icon", you will then be able to:

* Select the `Database`
* Select a `table`
* Add a Query (optional): you can query using the SQL syntax accepted by Athena

{% hint style="info" %}
For more info, see the dedicated section [Create a new dataset from a dataset](/data-management-in-datahub/datasets-in-toucan/managing-datasets/creating-datasets)
{% endhint %}

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}


# Add a MongoDB connector

How to connect a MongoDB cluster  in Toucan

You can use the Toucan MongoDB connector to connect to your MongoDB cluster with a basic authentication and access `collections` with a aggregation pipeline

With this connection, you can fetch data from your MongoDB cluster to fill your charts and dashboards.

## Configuring a MongoDB connection

Follow the steps described in [Add a connector](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector), choose `MongoDB` and fill out the form with the following info:

<table><thead><tr><th>Field</th><th width="140.00390625">Format / Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td>Name (mandatory)</td><td>String</td><td>Use it to identify your connection</td><td><em>MyMongoDBConnection</em></td></tr><tr><td>Host (mandatory)</td><td>String</td><td>The domain name or IP address of your database server, for mongodb atlas you will use the full connection string</td><td><em>mongodb+srv://:@.mongodb.net/?retryWrites=true&#x26;w=majority (</em>Mongo Atlas<em>)</em></td></tr><tr><td>Username (mandatory)</td><td>String</td><td>your login username</td><td><em>my_mongo_user</em></td></tr><tr><td>Password (mandatory)</td><td>String</td><td>Your login password (will be stored as a secret)</td><td><em>my_mongo_password</em></td></tr><tr><td>SSL</td><td>Boolean</td><td>Flag to create the connection using SSL</td><td></td></tr><tr><td>Max_pool_size (optional)</td><td>Integer</td><td>Maximum number of connections in the connection pool</td><td><em>3</em> (default: 1)</td></tr><tr><td>Retry Policy (optional)</td><td>Boolean</td><td><p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max_delay: in seconds, above the connection is dropped</li><li>wait_time: time in seconds between each retry</li></ul></td><td></td></tr><tr><td>Slow Queries' Cache Expiration Time (optional)</td><td>Integer</td><td>Slow queries' cache expiration time in seconds</td><td></td></tr></tbody></table>

Click on the `TEST CONNECTION` button then `SAVE` the connection

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

## Create a dataset from a MongoDB connection

To create a dataset from MongoDB, click on the "create from icon", you will then be able to:

* `database`: The name of the database you want to query
* `collection`: The name of the collection you want to query
* Add a Query (optional): Use [MongoDB aggregation pipeline](https://www.mongodb.com/docs/manual/core/aggregation-pipeline/) to queries your collections , and return only the relevant results.
* After inputting your query,
* select "Preview" to review the results,
* and then click "Save" to create a dataset based on your chosen selection.

### Misc

#### Error Handling

The connector handles various error scenarios, including:

* Unknown database (`UnkwownMongoDatabase`)
* Unknown collection (`UnkwownMongoCollection`)
* Connection errors
* Authentication failures

{% hint style="info" %}
For more info, see the dedicated section [Create a new dataset from a dataset](/data-management-in-datahub/datasets-in-toucan/managing-datasets/creating-datasets)
{% endhint %}

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}


# Add an AWS Redshift connector

How to connect an AWS Redshift  in Toucan

## Connector features

You can use the Toucan AWS Redshift connector to connect to your AWS Redshift account with a db\_credentials or aws\_credentiamls and access `tables` or `views` with a SQL query or by [using our no-code form ](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode).

With this connection, you can fetch data from your Snowflake to fill your charts and dashboards.

{% hint style="info" %}
**Changelog**

**December 23**\
\- This connector is [NativeSQL](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql) compatible\
\
**November 2024**\
\- This connector supports [hybrid pipelines](/data-management-in-datahub/datasets-in-toucan/preparing-data/hybrid-pipeline)
{% endhint %}

## Configuring an AWS Redshift connection

Follow the steps described in [Add a connector](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector), choose `AWS Redshift` and fill out the form with the following info:

<table><thead><tr><th>Field</th><th width="123.2734375">Format / Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td>Name (mandatory)</td><td>String</td><td>Use it to identify your connection</td><td><em>MyRedshiftConnection</em></td></tr><tr><td>Host (mandatory)</td><td>String</td><td>The hostname of the Amazon Redshift cluster</td><td><em>example-cluster.1111.us-west-2.redshift.amazonaws.com</em></td></tr><tr><td>Port (mandatory)</td><td>Integer</td><td>The listening port of your Redshift Database</td><td><em>5439</em></td></tr><tr><td>Cluster identifier (mandatory)</td><td>String</td><td>The cluster identifier of the Amazon Redshift cluster</td><td><em>example-cluster</em></td></tr><tr><td>Default database (optional)</td><td>String</td><td>The name of the database instance to connect to</td><td><em>default_db</em></td></tr><tr><td>AuthenticationMethod</td><td>Enum</td><td>Authentication mechanism that will be used to connect to your Redshift datasource :<br>- <code>db_credentials</code><br>- <code>aws_credentials</code> (This approach allows users to use AWS credentials and limit the permissions the connected user has. The user should have the right permissions to access the redshift database. To find some examples of rights permissions, see <a href="https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-access-control-identity-based.html">this documentation</a> and <a href="https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-access-control-overview.html">this one</a>.)<br>- <code>aws_profile</code>( <strong>CAUTION</strong>: This authentication method can only work for now with the self-hosted mode. For more details about the profile: <a href="https://docs.aws.amazon.com/cli/latest/userguide/cli-configure-profiles.html">AWS CLI Profile</a>, you must fill the <strong>db_user</strong> and <strong>profile</strong> fields. To find some examples of rights permissions, see <a href="https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-access-control-identity-based.html">this documentation</a> and <a href="https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-access-control-overview.html">this one</a>.)<br><br></td><td></td></tr><tr><td>Username</td><td>String</td><td>Mandatory for <code>db_credentials</code><br>The username to use for authentication with the Amazon Redshift cluster</td><td><em>dbuser</em></td></tr><tr><td>Password</td><td>String</td><td>Mandatory for <code>db_credentials</code><br>The password that will be used to authenticate to the Redshift cluster</td><td><em>abcD1234</em></td></tr><tr><td>Db user</td><td>String</td><td>Mandatory for <code>aws_credentials</code><br>and <code>aws_profile</code><br>The user ID to use with Amazon Redshift</td><td><em>awsuser</em></td></tr><tr><td>Access Key Id</td><td>String</td><td>Mandatory for <code>aws_credentials</code><br>The access key id of your aws account.</td><td><em>AKIAIOSFODNN7EXAMPLE</em></td></tr><tr><td>Secret Access Key</td><td>String</td><td>Mandatory for <code>aws_credentials</code><br>Secret access key to access to your redshift</td><td><em>wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY</em></td></tr><tr><td>Session token</td><td>String</td><td>Optional for <code>aws_credentials</code></td><td><em>IQoJb3JpZ2luX2VjEOj//////////wEaCXVzLWVhc3QtMSJGMEQCIGN2...</em></td></tr><tr><td>Profile</td><td>String</td><td>Mandatory for <code>aws_profile</code><br>Your AWS profile</td><td><em>myawsprofile</em></td></tr><tr><td>Region</td><td>String</td><td>The region in which there is your aws account.</td><td><em>eu-west-3</em></td></tr><tr><td>Enable TCP keep-alive</td><td>Boolean</td><td>Disable TCP keep-alive by unticking this option. Disabling might be "<br>"required for long-running queries or if you are behind a firewall</td><td></td></tr><tr><td>Connection timeout</td><td>Integer</td><td>maximum length of time to wait for the server to respond. None by default</td><td><em>30</em> (default)</td></tr><tr><td>Retry Policy (optional)</td><td>Boolean</td><td><p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max_delay: in seconds, above the connection is dropped</li><li>wait_time: time in seconds between each retry</li></ul></td><td></td></tr><tr><td>Slow Queries' Cache Expiration Time (optional)</td><td>Integer</td><td>Slow queries' cache expiration time in seconds</td><td></td></tr></tbody></table>

Click on the `TEST CONNECTION` button then `SAVE` the connection

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

{% hint style="warning" %}
To have the graphical database exploration in your connector, **you must promote access to the default dev database to your user**. This database contains a table pg\_database listing all available databases and 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.
{% endhint %}

## Create a dataset from a Redshift connection

To create a dataset from Redshift, click on the "create from icon"; you will then be able to:

* Select the `Database`
* Select the `Schema`
* Select `Table` or `Views`
* Only keep the columns you need

{% hint style="info" %}
For more info, see the dedicated section [Create a new dataset from a dataset](/data-management-in-datahub/datasets-in-toucan/managing-datasets/creating-datasets)
{% endhint %}

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}


# Add a Databricks connector

How to connect a databricks cluster  in Toucan

{% hint style="warning" %}
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 datasets might not work properly in case of self stopped cluster
{% endhint %}

{% hint style="warning" %}
The relevant **driver** must be installed and configured on your Toucan Toco workspace
{% endhint %}

## Connector features

You can use the Toucan Databricks connector to connect to your Databricks account with a Personal Access token and access `tables` or `views` with a SQL query.

With this connection, you can fetch data from your Snowflake to fill your charts and dashboards.

## Configuring a Databricks connection in Toucan

{% hint style="info" %}
Retrieve ODBC connection information from Databricks as described [here](https://docs.databricks.com/integrations/bi/jdbc-odbc-bi.html)
{% endhint %}

Follow the steps described in [Add a connector](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector), choose `Databricks` and fill out the form with the following info:

<table><thead><tr><th width="172.0390625">Field</th><th width="145.6015625">Format / Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td>Name (mandatory)</td><td>String</td><td>Use it to identify your connection</td><td><em>MyDatabricksConnection</em></td></tr><tr><td>Host (mandatory)</td><td>String</td><td>hostname of databricks cluster can be found the cluster configuration</td><td><em>my-databricks-cluster.cloudprodiverdatabricks.net</em></td></tr><tr><td>Port (mandatory)</td><td>Integer</td><td>The listening port of your Databricks cluster</td><td><em>443</em> (default)</td></tr><tr><td>Http Path (mandatory)</td><td>String</td><td>Databricks compute resources URL, can be retrieved from Databricks UI cluster’s configuration in the ‘ODBC’ section</td><td><em>sql/protocol/v1/o/xxx/yyy</em></td></tr><tr><td>User (mandatory)</td><td>String</td><td><code>token</code>"if you use a personal access token PAT,<br><br>or username if you connect by username/password (deprecated since July 2024)</td><td><em>databricks_user</em></td></tr><tr><td>Password (mandatory)</td><td>String</td><td>Access token (generated from Databricks UI in user settings) (will be stored as a secret)</td><td><em>dapixxxxxx</em></td></tr><tr><td>ANSI</td><td>Boolean</td><td>Enforce compliance with the ANSI SQL standard for SQL operations and behaviors</td><td></td></tr><tr><td>On Demand</td><td>Boolean</td><td><strong>if your cluster is self-stopping, make sure to tick this option</strong>. With this option, the connector will try to start the cluster if it’s stopped before any query</td><td></td></tr><tr><td>Retry Policy (optional)</td><td>Boolean</td><td><p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max_delay: in seconds, above the connection is dropped</li><li>wait_time: time in seconds between each retry</li></ul></td><td></td></tr><tr><td>Slow Queries' Cache Expiration Time</td><td>Integer</td><td>Slow queries' cache expiration time</td><td></td></tr></tbody></table>

Click on the `TEST CONNECTION` button then `SAVE` the connection

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

{% hint style="warning" %}
If the cluster is stopped, the connection test might fail, but you can `SAVE` the configuration anyway
{% endhint %}

## Create a dataset from a Databricks connection

{% hint style="warning" %}
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 stored datasets.
{% endhint %}

{% hint style="info" %}
This data connector is only supported in [code/SQL mode](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode)
{% endhint %}

To create a dataset from Databricks, click on the "create from icon", you will then be able to:

* `QUERY`: the SQL query you want to run
* `PARAMETERS` (optional): dict, allows to parameterize the query.

{% hint style="info" %}
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.\*
{% endhint %}

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}


# Add a ElasticSearch connector

How to connect an elasticsearch cluster  in Toucan

## Connector features

You can use the Toucan Snowflake connector to connect to your Snowflake account with a key-pair authentication or basic authentication and access `tables` or `views` with a JSON query.

With this connection, you can fetch data from your Snowflake to fill your charts and dashboards.

{% hint style="info" %}
**Changelog**

**July 25**\
\- We have upgraded our client ElasticSearch and v9 is now enforced
{% endhint %}

## Configuring an ElasticSearch connection

Follow the steps described in [Add a connector](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector), choose `ElasticSearch` and fill out the form with the following info:

<table><thead><tr><th>Field</th><th width="137.6015625">Format / Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td>Name (mandatory)</td><td>String</td><td>Use it to identify your connection</td><td><em>my_elasticsearch</em></td></tr><tr><td>URL (mandatory)</td><td>String</td><td>URL of your ElasticSearch server <strong>mandatory</strong></td><td><em>https://elasticsearch-server.mydomain.com</em></td></tr><tr><td>Port (mandatory)</td><td>Integer</td><td>port number of your ElasticSearch server</td><td><em>9200</em></td></tr><tr><td>Scheme (mandatory)</td><td>String</td><td>connection scheme to use (e.g., "http" or"https").</td><td><em>https</em></td></tr><tr><td>Username (mandatory)</td><td>String</td><td>username to use for the authentication (if required)</td><td><em>my_login</em></td></tr><tr><td>Password (mandatory)</td><td>String</td><td>password for the authentication (if required) (will be stored as a secret)</td><td><em>abcD1234</em></td></tr><tr><td>Headers (optional)</td><td>Json dictionary</td><td><p>Allows to specify a dictionary of additional HTTP headers in the requests. It defaults to <code>None</code>, meaning that no additional headers are included by default.<br><br>this dictionnary allows to configure:</p><ul><li><strong>Authorization:</strong> If your Elasticsearch server requires specific authorization headers, you can include them here.</li><li><strong>Content-Type:</strong> You might include headers specifying the content type of the request, such as "application/json" if your queries are in JSON format.</li><li><strong>Custom Headers:</strong> Any other custom headers that your Elasticsearch server might expect for specific functionalities or integrations.</li></ul></td><td></td></tr><tr><td>ES version (mandatory)</td><td>String</td><td>Specify the ElasticSearch version you aimed</td><td>9 (default)</td></tr><tr><td>Retry Policy (optional)</td><td>Boolean</td><td><p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max_delay: in seconds, above the connection is dropped</li><li>wait_time: time in seconds between each retry</li></ul></td><td></td></tr><tr><td>Slow Queries' Cache Expiration Time (optional)</td><td>Integer</td><td>Slow queries' cache eexpiration time in seconds</td><td></td></tr></tbody></table>

Click on the `TEST CONNECTION` button then `SAVE` the connection

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

## **Create a dataset from an ElasticSearch connection**

fill out the required fields:

* `SearchMethod`: Select wether “search” or “msearch” (for multiple search)
* `Index`: Type the index of your ElasticSearch from which you want to extract data
* `Configuration type`: select “Type 1”
* `Body`: enter your query within a Json format (see an example below)

<figure><img src="/files/nmZIr9bH7q3TRk48CGIK" alt="Data source configuration"><figcaption><p><strong>Data source configuration</strong></p></figcaption></figure>


# Add a ClickHouse connector

## Connector features

{% hint style="info" %}
This data connector is compatible with [NativeSQL](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql) with our new data execution system
{% endhint %}

## Configuring a ClickHouse connection¶

You can use the Toucan ClickHouse connector to connect to your ClickHouse cluster with a basic authentication and access `tables` with a SQL query.

With this connection, you can fetch data from your ClickHouse to fill your charts and dashboards.

{% hint style="info" %}
**Changelog**

**September 25**\
\- This connector is supported by our new Data execution system
{% endhint %}

## Configuring a ClickHouse connection¶

Follow the steps described in [Add a connector](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector), choose `ClickHouse` and fill out the form with the following info:

<table><thead><tr><th>Field</th><th width="139.28515625">Format / Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td>Name (mandatory)</td><td>String</td><td>Use it to identify your connection</td><td><em>my_clickhouse_connector</em></td></tr><tr><td>Host (mandatory)</td><td>String</td><td>could be an IP address or an hostname (e.g localhost)</td><td>"<em>db.example.com</em>" or "<em>192.168.1.100</em>"</td></tr><tr><td>Port (mandatory)</td><td>Integer</td><td>an integer, by default ClickHouse runs on port 9000</td><td><em>9000</em> (default)</td></tr><tr><td>User (mandatory)</td><td>String</td><td>Your login user</td><td><em>db_user</em></td></tr><tr><td>Password (mandatory)</td><td>String</td><td>Your login password (this value will be stored as a secret)</td><td><em>abcD1234</em></td></tr><tr><td>SSL_connection (optional)</td><td>Boolean</td><td>Allow to enable to enable SSL wrapped TCP connection, to enable if your Toucan workspace and your ClickHouse cluster are not in the same network</td><td></td></tr><tr><td>Retry Policy (optional)</td><td>Boolean</td><td><p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max_delay: in seconds, above the connection is dropped</li><li>wait_time: time in seconds between each retry</li></ul></td><td></td></tr><tr><td>Slow Queries' Cache Expiration Time (optional)</td><td>Integer</td><td>Slow queries' cache expiration time in seconds</td><td></td></tr></tbody></table>

Click on the `SAVE` button to add the connection

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

## **Create a dataset from a ClickHouse connection**

{% hint style="info" %}
This data connector is only supported in [code/SQL mode](/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode)
{% endhint %}

Fill the mandatory fields:

* `Simple mode`
  * `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.
  * `columns`, a drop down list of available columns, we will select only the columns you checked
* Alternatively, in `SQL/code mode`
  * `Database`, a dropdown list of available databases
  * in the Query content section a SQL query field, where you can write your SQL query, if left blank a `select * from Table limit 50` will run

### How to troubleshoot a ClickHouse connection

If a warning icon appears after adding a ClickHouse connection, it indicates that Toucan cannot establish a stable connection to your ClickHouse cluster. In this case if you try to `create a new dataset from this connector`, you will not be able to pick a database from the dropdown (a `detailedHttpError` will be displayed when landing on the UI)

<figure><img src="/files/znYKV2LyB77ES8izmqLK" alt=""><figcaption></figcaption></figure>

**Mandatory fields**

Ensure all mandatory fields (`Name`, `Host`, `Port`, `User`, `Password`) are filled.

#### Network access

* Verify that the Host (IP or DNS) is **reachable** from Toucan IP and is not blocked by firewalls.

{% hint style="info" %}
Check our IP for our [Current Data Execution System](https://toucantoco.com/public-servers-list.html) and [New Data Execution System](/data-management-in-datahub/new-data-execution-system#ip-allowlisting)
{% endhint %}

* Check that the `Port` (default is 9000) matches the **ClickHouse cluster's configuration** and is **open**.

#### Authentication

* Confirm `User` and `Password` are valid and have been granted connect permissions to the target database.

#### Other options

**Connect timeout**

* Adjust `Connect Timeout` if timeouts occur, especially in remote or slow network situations.

**Advanced troubleshooting**

* Review logs on PostgreSQL server for more detailed error information.
* For persistent issues, attempt to connect using CLI with the same parameters to isolate issues.


# Online services connectors


# Setting up a Sharepoint connector

<details>

<summary>Credentials setting in Azure</summary>

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

<figure><img src="/files/j5o8DG8GRSvwxZQGb20g" alt="sharepoint_1"><figcaption><p>sharepoint_1</p></figcaption></figure>

* Give a name to your app (for instance : Toucan) & define who can have access to this app. Click then on Register

<figure><img src="/files/43uuTOsTZlRlDyDRD55w" alt="sharepoint_2"><figcaption><p>sharepoint_2</p></figcaption></figure>

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:

<figure><img src="/files/o9s9IkSj8hvfqTCQFqq3" alt="sharepoint_3"><figcaption><p>sharepoint_3</p></figcaption></figure>

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

<figure><img src="/files/GJMnqBPfT8bpozrDv88W" alt="sharepoint_4"><figcaption><p>sharepoint_4</p></figcaption></figure>

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

<figure><img src="/files/5fTgzUrHa2j0sGYlEKnd" alt="sharepoint_5"><figcaption><p>sharepoint_5</p></figcaption></figure>

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.

<figure><img src="/files/AJKTAXKwsLZ2yuSNFSdz" alt="sharepoint_6"><figcaption><p>sharepoint_6</p></figcaption></figure>

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

<figure><img src="/files/BQzL7rGiAaCF8xabQ9BQ" alt="sharepoint_7"><figcaption><p>sharepoint_7</p></figcaption></figure>

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.

<figure><img src="/files/ZCdlEDRd71YtbDMh5pI2" alt="sharepoint_8"><figcaption><p>sharepoint_8</p></figcaption></figure>

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

<figure><img src="/files/nLAIhPXlqE1qEaoYSCmu" alt="sharepoint_9"><figcaption><p>sharepoint_9</p></figcaption></figure>

#### Sharepoint connector setting in Toucan:

</details>

## Configuring a Sharepoint connection¶

| Field                                          | Format / Type | Description                                                                                                                                                                                                                                                                             | Example |
| ---------------------------------------------- | ------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------- |
| Name (mandatory)                               | String        | Use it to identify your connection                                                                                                                                                                                                                                                      |         |
| Client ID (mandatory)                          | String        | corresponds to “Application (client) ID” in Azure                                                                                                                                                                                                                                       |         |
| Client Secret (mandatory)                      | String        | corresponds to the secret displayed after having added a new secret in Azure                                                                                                                                                                                                            |         |
| Scope (mandatory)                              | String        | The scope determines what type of access the app is granted when the user is signed in it can be set as “offline\_access Sites.Read.All”                                                                                                                                                |         |
| Tenant (mandatory)                             | String        | The tenant determines what part of your organisation you want to signed in corresponds to “Directory (tenant) ID” in Azure                                                                                                                                                              |         |
| Retry Policy (optional)                        | Boolean       | <p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max\_delay: in seconds, above the connection is dropped</li><li>wait\_time: time in seconds between each retry</li></ul> |         |
| Slow Queries' Cache Expiration Time (optional) | Integer       | Slow queries' cache expiration time in seconds                                                                                                                                                                                                                                          |         |

## Create a dataset from a Sharepoint connection

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.

## Misc

### Error handling

When you get an error while configuring the connection, 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`.

<figure><img src="/files/booCT6O0XloRUSjzBeT5" alt="sharepoint_11"><figcaption><p>sharepoint_11</p></figcaption></figure>

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

<figure><img src="/files/UDCJu1SJemxNDLlDDuxQ" alt="sharepoint_12"><figcaption><p>sharepoint_12</p></figcaption></figure>


# Setting up a Google Sheets connector

### Google Sheets Connector

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

### Add a Google sheets connection

* As soon as you click on the connector icon, you will be redirected by google to sign in to your account, in order to access to your google sheet. If not click on the button `GRANT AUTHORIZATION` to display the window
* After logging in, you shall be redirected back to Toucan, where you will have the required attributes automatically filled by Toucan.
* fill the fields necessary

| Field                                          | Format / Type | Description                                                                                                                                                                                                                                                                             | Example                    |
| ---------------------------------------------- | ------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------- |
| Name (mandatory)                               | String        | Use it to identify your connection                                                                                                                                                                                                                                                      | *MyGoogleSheetsConnection* |
| Retry Policy (optional)                        | Boolean       | <p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max\_delay: in seconds, above the connection is dropped</li><li>wait\_time: time in seconds between each retry</li></ul> |                            |
| Slow Queries' Cache Expiration Time (optional) | Integer       | Slow queries' cache expiration time in seconds                                                                                                                                                                                                                                          |                            |

* Now, click on the `SAVE` button

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

### Create a dataset from a Google sheets connection

{% hint style="warning" %}
This connector does not support Excel files (.xls, .xlsx, etc.) saved on Google Drive.
{% endhint %}

* In the Edit datasource section
  * `ID OF THE SPREADSHEET` (**mandatory**): string, Fill this field with the spreadsheet id which can be found in the url to access your google spreadsheet after the`https://docs.google.com/spreadsheets/d/` (For example if your URL is `https://docs.google.com/spreadsheets/d/a98db973kwl8xp1lz94kjf0bma5pez8c` then the id is `a98db973kwl8xp1lz94kjf0bma5pez8c)`

<details>

<summary>Find a spreadsheet ID of a google sheets</summary>

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:

<figure><img src="/files/9BaqCuofufVPhYfdYOBA" alt=""><figcaption><p>ID of a spreadsheet in Google sheets</p></figcaption></figure>

</details>

* `SHEET TITLE` (optional): string, title of the sheet you want to get, once you have fill your spreadsheet id, we will get all the sheet that exist and they will be displayed in a dropdown
* `HEADER ROW` (optional): int, default to 0. Row of the header of the spreadsheet
* `Dates as float` (optional): boolean, render date as float or string from the sheet true by default

Once you have fill all the necessary fields, you can click on

* `PREVIEW` to see the data Toucan will get from your Google sheets
* `VALIDATE`, your data will be created and you will redirected to the YouPrep interface

{% hint style="info" %}
For more info, see the dedicated section [Create a new dataset from a dataset](/data-management-in-datahub/datasets-in-toucan/managing-datasets/creating-datasets)
{% endhint %}

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}


# Setting up an AWS S3 connector

## Configuring the AWS S3 connector in Toucan

The AWS S3 connector lets you access files hosted in an AWS S3 bucket. We use AWS STS (Security token Service) to authenticate to the S3 bucket via the [Assume Role function](https://docs.aws.amazon.com/STS/latest/APIReference/API_AssumeRole.html).

<table><thead><tr><th>Field</th><th width="89.9453125">Format / Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td>Name (mandatory)</td><td>String</td><td>Use it to identify your connection</td><td><em>MyS3Connection</em></td></tr><tr><td>Bucket Name (mandatory)</td><td>String</td><td>the S3 bucket name you want to query data from</td><td><em>bucket_s3_name</em></td></tr><tr><td>Prefix (Optional)</td><td>String</td><td>a prefix for your object like a path folder</td><td><em>marketing/</em></td></tr><tr><td>Role ARN (mandatory)</td><td>String</td><td>AWS Amazon Ressources Names (ARN), identifier that provides access to AWS ressources, configured with policies. Will be given to you by Toucan support</td><td></td></tr><tr><td>ExternalId (mandatory)</td><td>String</td><td>already set, represents an ID used in AWS policy configuration</td><td></td></tr><tr><td>Retry Policy (optional)</td><td>Boolean</td><td><p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max_delay: in seconds, above the connection is dropped</li><li>wait_time: time in seconds between each retry</li></ul></td><td></td></tr><tr><td>Slow Queries' Cache Expiration Time (optional)</td><td>Integer</td><td>Slow queries' cache expiration time in seconds</td><td></td></tr></tbody></table>

Click on the `TEST CONNECTION` button then `SAVE` the connection

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

## Selecting data from AWS S3

To create a dataset from AWS S3, click on the "create from icon", you will then be able to:

* Select a file hosted in your S3 bucket

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}


# Setting up a Salesforce connector

### Salesforce Connector¶

{% hint style="info" %}
Warning

This connector is deprecated. It is recommended to use the **HTTP API** instead.
{% endhint %}

<details>

<summary>Prerequisites - Connected App creation¶</summary>

Login to your Salesforce application.

On the top right of the screen click on setup

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

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

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

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](https://docs.toucantoco.com/concepteur/power-apps-with-data/02-connectors.html#set-up-oauth2-credentials-for-your-platform).

</details>

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

{% hint style="info" %}
Start the configuration once the **credentials** are configured, add your connector:
{% endhint %}

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

<details>

<summary>Pop-up to authenticate</summary>

<figure><img src="/files/IQU9t0gJ3Btr63NzJimC" alt="authorize the connected app"><figcaption><p>authorize the connected app</p></figcaption></figure>

</details>

* then click on the `SAVE` button

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

## Click on "Create a new dataset from this datasource "

Mandatory parameter(s)

* `Query`, here an SOQL query is expected to extract the data from the API. [here](https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_examples.htm) for some examples.

## Misc

#### Authentication

Salesforce authentication phase can be quite challenging, this [stackoverflow thread](https://stackoverflow.com/questions/12794302/salesforce-authentication-failing) is really helpful for troubleshooting.


# Setting up a Hubspot connector

### Configuring a Hubspot connection

{% hint style="info" %}
Warning

This connector is deprecated. It is recommented to use the **HubSpot connector with Private Apps** instead.
{% endhint %}

<details>

<summary>Create hubspot application</summary>

This connector is used to retrieve data from the HubSpot API: [HubSpot API Documentation](https://developers.hubspot.com/docs/api/overview)

The email events part relies for now on Hubspot’s legacy API: [HubSpot legacy API Documentation](https://legacydocs.hubspot.com/docs/overview)

**Create a HubSpot application¶**

First, you need to create a HubSpot application. To do so, you will need a registered [HubSpot developer account](https://developers.hubspot.com/):

* Head over to `Manage apps`.

<figure><img src="/files/u98HeRkKDgaDeJVKlHww" alt="Hubspot Developer Console"><figcaption><p>Hubpot Manage App Menu</p></figcaption></figure>

* Click on `Create app`.

<figure><img src="/files/9w83nkG3eEusPIvkEI5d" alt="HubSpot_create_app"><figcaption><p>HubSpot_create_app</p></figcaption></figure>

* Once the App Info is filled, click on the `Auth` part, you’ll see the following:

<figure><img src="/files/PqR3PAEGGnU8ujbUrzyC" alt="HubSpot_auth_app_layout"><figcaption><p>HubSpot_auth_app_layout</p></figcaption></figure>

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

    <div align="center"><figure><img src="/files/iYP9gPzQaeSFPm6nQAmi" alt="HubSpot_app_add_redirect_uri"><figcaption><p>HubSpot_app_add_redirect_uri</p></figcaption></figure></div>
* Scroll down to the `Add a required scope` part:

<figure><img src="/files/0RjnIwVVvmIvjls9UUEt" alt="HubSpot_app_add_scopes"><figcaption><p>HubSpot_app_add_scopes</p></figcaption></figure>

* 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](https://docs.toucantoco.com/concepteur/power-apps-with-data/02-connectors.html#set-up-oauth2-credentials-for-your-platform) for more information.

</details>

{% hint style="info" %}
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.
{% endhint %}

<details>

<summary>Authentication to Hubspot</summary>

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

<figure><img src="/files/qQxemsxQim5FSZqUdQIA" alt="HubSpot_create_connector_step_4"><figcaption><p>HubSpot_create_connector_step_4</p></figcaption></figure>

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:

<figure><img src="/files/j3bSPrRM9ILe8aPFYCka" alt="HubSpot_create_connector_step_5"><figcaption><p>HubSpot_create_connector_step_5</p></figcaption></figure>

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.

</details>

| Field                                          | Format / Type | Description                                                                                                                                                                                                                                                                             | Example               |
| ---------------------------------------------- | ------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------- |
| Name (mandatory)                               | String        | Use it to identify your connection                                                                                                                                                                                                                                                      | *MyHubspotConnection* |
| Auth Flow Id (mandatory)                       | String        | Automatically filled by Hubspot                                                                                                                                                                                                                                                         |                       |
| Retry Policy (optional)                        | Boolean       | <p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max\_delay: in seconds, above the connection is dropped</li><li>wait\_time: time in seconds between each retry</li></ul> |                       |
| Slow Queries' Cache Expiration Time (optional) | Integer       | Slow queries cache expiration time in seconds                                                                                                                                                                                                                                           |                       |

#### Create a dataset from a Hubspot connection

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](https://developers.hubspot.com/docs/api/crm/properties)
  * 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`

### Configuring a HubSpot (with a private application) connection ¶

This connector is used to retrieve data from the HubSpot API: [HubSpot API Documentation](https://developers.hubspot.com/docs/api/overview)

<details>

<summary>Create a private application</summary>

To create one, follow these steps:

1. Go to the **Settings** page of your HubSpot account (click on the small gear in the top right corner of the page).
2. Head over to the **Account Setup/Private Apps** section.
3. Click on the **Create a private app** button.

<figure><img src="/files/GOHq28lahrmgi0EddRJ0" alt="HubSpot_private_app_private_apps_menu"><figcaption><p>HubSpot_private_app_private_apps_menu</p></figcaption></figure>

1. Fill out the basic information form.

<figure><img src="/files/c0NZXrSFikyUpVeEOBdt" alt="HubSpot_private_app_basic_info"><figcaption><p>HubSpot_private_app_basic_info</p></figcaption></figure>

1. Click on the **Scopes** tab. Toucan needs a read access on all CRM objects you want to query. The following objects are supported:
   * `companies`
   * `contacts`
   * `deals`
   * `owners`
   * `quotes`
2. Once you’ve ticked all the scopes you need, click on the “Create app” button.

<figure><img src="/files/1a1V0PBCwQrx3Qh1wk1o" alt="HubSpot_private_app_scopes"><figcaption><p>HubSpot_private_app_scopes</p></figcaption></figure>

1. You will now need to copy your app’s access token: Click on the **View access token** button, and then on **Copy** in the pop-up.

<figure><img src="/files/vUJ3a3f06XiKWOKpR0FS" alt="HubSpot_private_app_view_access_token"><figcaption><p>HubSpot_private_app_view_access_token</p></figcaption></figure>

</details>

Now that your HubSpot private application is created, you can create a connection

| Field        | Format / Type | Description                                                                          | Example                      |
| ------------ | ------------- | ------------------------------------------------------------------------------------ | ---------------------------- |
| Name         | String        | Use it to identify your connection                                                   | *MyPrivateHubspotConnection* |
| Access token | String        | Access token field previously seen in the configuration (will be stored as a secret) |                              |

## Create a dataset from a Hubspot datasource¶

The `HubspotDataset` parameter is needed to specify the kind of data you want to query. Pick the desired dataset (note that your private app needs to have the right scopes for that), and click on **Validate** (or **Run preview** if you want a preview of your data first).


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

<figure><img src="/files/VX9RbDBHCdkr8VjGjlEn" alt="GS_Redirection"><figcaption><p>GS_Redirection</p></figcaption></figure>

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 app store, click on the “Admin area” button, and then click on the “Credentials” button:

<figure><img src="/files/3FYn5RiVnzkpfamJzetX" alt="credentials-button"><figcaption><p>credentials-button</p></figcaption></figure>

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:

<figure><img src="/files/dZMxgARVGcTRzcyqk2FJ" alt="credentials-platform-interface"><figcaption><p>credentials-platform-interface</p></figcaption></figure>

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

<figure><img src="/files/NqezQhugnfAH2ds2ygR8" alt="creds-example"><figcaption><p>creds-example</p></figcaption></figure>

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


# Create a dataset from a connector

## Create a dataset from a connector

You already configured a connector, that you want to use in order to retrieve your data. For that:

1. Click on the dataset creation button on from your configured connector

   <figure><img src="/files/ngdefGw6421qlKvb44u0" alt=""><figcaption></figcaption></figure>
2. Make the appropriate configuration in order to retrieve the data. Refer to your connector [documentation](/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector) in order to setup the configuration
3. Validate the configuration
4. Prepare your data thanks to our no-code transformation tool YouPrep™ (know more about YouPrep [here](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm))

   <figure><img src="/files/UVGpIh8ykwThBQyGK7Sm" alt=""><figcaption></figcaption></figure>
5. Save your new dataset by clicking on the button "Create" (at the bottom).
6. Give a name to the dataset (the name shouldn't be already used by another dataset), and select the storage type between storing it in Toucan, or having it as LIVE data.
7. Click on "Save" to save your dataset. If you store the dataset in Toucan, you also canto "Save and refresh" the dataset in order to make it available to use

{% hint style="info" %}
When making the configuration in order to retrieve the appropriate data, note that you can refer to variables (more on variables in this [page](/data-management-in-datahub/using-advanced-data-concepts/advanced-syntax-for-variables)) instead of giving a fixed value.
{% endhint %}


# Native SQL: SQL / Code mode and simple mode

Only for connectors which support nativeSQL

When working with a datasource supporting [NativeSQL](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql), you have two modes for writing your queries.

After connecting to your database, when you click on "create a dataset from datasource", you land on a new page where, on the right, you have three modals: *"Configuration", "*&#x51;*uery*"*,* and "R*eview*" which will lead you to the creation of a dataset.

## Query configuration mode

{% hint style="info" %}
If your workspace is fueled by our New Data Execution System, you have access to a fully redesigned form, refer to [this part](#new-query-configuration-form)
{% endhint %}

### Simple mode

In **Simple mode,** (which is the default mode for all connectors which support NativeSQL), you can query your database without the hassle of writing a SQL query, and look at your database model constantly to avoid errors.

After choosing the database you want to query and clicking on "*Validate configuration",* you switch to the "Q*uery"* tab

<figure><img src="/files/PwDmXSj219VDq1z4qmEn" alt=""><figcaption><p>Edit datasource - configuration</p></figcaption></figure>

you switch to the "Q*uery"* tab where you can select the table to query

<figure><img src="/files/XqTAQgknAuUz745bez5G" alt=""><figcaption><p>Edit datasource - Query - Table selection</p></figcaption></figure>

You can navigate through the database schema to choose the table you want to query.

<figure><img src="/files/QGyvQiGebUVRFcZQ1uZe" alt=""><figcaption><p>Edit datasource - Query - Table selection</p></figcaption></figure>

Then you can choose the columns you want to keep in your dataset by clicking/un-clicking on the tick box.

You can choose to select all columns by clicking on "*Select all*" or unselect all columns by clicking on "*Clear All*".

You can click on the "*Preview*" button to display on the right the columns of your selection.

<figure><img src="/files/97ZODMoMeP28HQ7bwSAn" alt=""><figcaption><p>Edit datasource - Query - Table selection -> Columns selection</p></figcaption></figure>

Once you are satisfied with your query, you can click on the "*Apply Data Selection*" button. You are redirected to the "*Review"* tab, where you can review the query you will send to your database.

On the right of each field, you have an *Edit* icon for each field which will redirect you to the designated section when clicked.

<figure><img src="/files/ydXdtSlhsz43XwjlWLcZ" alt=""><figcaption><p>Edit datasource - Review</p></figcaption></figure>

By clicking on the "*Preview*" button, you can view the data.

By clicking on "*Save query"* you will be redirected to the dataset creation where you will be able to apply data transformation.

<figure><img src="/files/K7uGLD0P3KVk1feqIkyv" alt=""><figcaption><p>New dataset from source</p></figcaption></figure>

### Code/SQL mode

In the configure datasource interface, once you have chosen the database in the configuration tab, in the query mode, at the bottom right, there's an icon (burger menu). You can click on the message "*Switch to code mode*" that is displayed.

<figure><img src="/files/Ylig9lOgufDvYwJ6Kd1X" alt=""><figcaption><p>PostgreSQL - Edit datasource - Query -Columns selection - switch to code mode</p></figcaption></figure>

You can switch to code mode since table selection and after: And the current SQL request will be displayed. You can insert a variable using the '`/`' key or by clicking on `insert a variable` at the top right of the input box. Learn more about variables in the [dedicated section](/data-management-in-datahub/managing-variables-in-toucan).

<figure><img src="/files/MbHs4GRVZOcPErAADETs" alt=""><figcaption><p>Edit datasource - Query - Columns selection - code mode</p></figcaption></figure>

This field is a playground SQL where you can write the query you desire to get data from your database. You can erase the current query to write your own.

<figure><img src="/files/XsD4yJ4Efwf1MogN1xuB" alt=""><figcaption><p>Edit datasource - Query -Columns selection - code mode</p></figcaption></figure>

## New query configuration form

{% hint style="info" %}
If the new [Data Execution System](/data-management-in-datahub/new-data-execution-system) is activated on your workspace and your connector is nativeSQL compatible, A new query configuration form has been designed.

Check the [latest release note](/additional-ressources/latest-releases) to see which data connectors are supported and nativeSQL compatibles.
{% endhint %}

### Simple mode

In *Simple Mode*, users can build queries without writing SQL manually (the query sent is in the following form `SELECT [selected columns] FROM [schema].[selected table] LIMIT 400`). This mode provides a guided interface to select databases, tables, and columns.

* **Workflow**:
  1. Select a **Database** from a list (or use the default variable if dynamic selection is enabled).
  2. Browse and choose a **Table**; all tables available in the selected database schemas are displayed.
  3. Select or deselect individual **Columns** that should appear in the query output.
  4. Execute the query against the selected database through the `Preview` button
  5. Or / then click on `Next` button to pass to the YouPrep

<figure><img src="/files/OHr3JNLImOXZEi7m4456" alt=""><figcaption></figcaption></figure>

**Selection through a Fixed Database**

* Users explicitly choose a database from a dropdown list.
* Once chosen, the relevant tables from that database are listed for selection.
* The query will always target this fixed database.

<figure><img src="/files/Tl3x9yWbX47yyLNHULdO" alt=""><figcaption></figcaption></figure>

**Selection through a Dynamic Database**

* A **variable** is used to define the target database.
* A default value for the variable is provided, so Simple Mode can still list the available tables and columns.
* The actual query execution adapts based on the variable's runtime value.

<figure><img src="/files/e5T3qwH3GlUFys9sxIFY" alt=""><figcaption></figcaption></figure>

### Code/SQL Mode

**Workflow**:

1. Select a **Database** from a dropdown list (`Fixed database`) or via a variable(`Dynamic database`).
2. Use the **SQL editor input box**, where you can enter multiline queries.
3. Execute the query against the selected database through a preview button
4. Then click on `Preview` button to have a preview of your query, the query is sent with `LIMIT 400` for performance purposes
5. Then/or click on `Next` button to pass to the YouPrep

**Fixed Database**

* The user chooses a database explicitly from a fixed list.
* SQL queries written in the editor will always target this database.

<figure><img src="/files/vY09FyUOHvA5YLk9BnZV" alt=""><figcaption></figcaption></figure>

**Dynamic Database**

* The database is chosen through a **variable**.
* The editor displays the tables and autocompletion based on the variable’s default database, but queries execute against whichever database is resolved at runtime.

<figure><img src="/files/lC1Ega7dAnkalgTzx0zj" alt=""><figcaption><p>Code/SQL mode: with dynamic database</p></figcaption></figure>

***

#### Explore Database

The **Database Explorer** provides navigation and exploration tools for users to better understand their database structure and build queries more efficiently.

* Users can browse multiple schemas and tables within the selected database.
* The explorer helps in quickly referencing table names, column structures, and relationships.
* This feature is especially useful in Code/SQL Mode to assist in building complex queries manually.

<figure><img src="/files/KB3Ed6qOio9VUuR2A1AM" alt=""><figcaption><p>code SQL mode with one table selected</p></figcaption></figure>

<figure><img src="/files/fCXurstsNn53WWMyq13a" alt=""><figcaption><p>code SQL mode with multiple tables selected</p></figcaption></figure>


# Managing file storages

{% hint style="info" %}
This feature is under feature flag, if you want to benefit from it, contact your CSM or our support.
{% endhint %}

Toucan allows you to easily connect from remote sources such as SFTP into your workspace by connecting a file storage system via the DataHub section. Once the connection is set up, you can browse folders, select a file (csv, parquet, excel, json, geojson), and create a dataset from it, while also managing and updating your storage connections as needed.

Datasets created from files located in remote file storage can be **:**

* **Live:** Data is read directly from the remote file storage system.
* **Stored:** Data is downloaded and saved in the Toucan data storage system.

Datasets originating from remote file storages can be handled in either of the above ways.

### Best Practices

* **Use Clear Naming**: Name your file storage connections descriptively to easily identify them later.
* **Credentials are kept secure:** private keys, passwords are kept in Toucan vault.
* **Regularly Review Connections**: Remove unused file storage connections to keep your workspace organized.


# Add a file storage

### Configure a file storage connection

To begin, navigate to the **DataHub** section and select **Data Sources**. Below the connector options, you’ll find the **File Storage** section.

### Adding a New File Storage

1. **Click “Add a File Storage”**\
   This opens a modal window where you can choose the type of file storage system you want to connect.
2. **Select Storage Type among:**
   * [SFTP](/data-management-in-datahub/datasources-in-toucan/managing-remote-file-storages/setting-up-a-file-storage/sftp)
3. **Configure Connection**\
   After selecting your file storage, you’ll be redirected to a new modal. Enter the required connection details following the appropriating documentation
4. **Test Connection (optional)**\
   Click on **Test connection** to test the connection with the file storage system you want to connect
5. **Save**\
   Click on **Save** to create a new file storage connection. A new file storage object will now appear in your list.

Click on the cross or **Cancel** button to close the modal.

### Managing File Storage

Once your file storage is set up, you have several actions available:

* **Edit**: update the connection details if your credentials or server information change.
* **Delete**: remove the file storage connection if it’s no longer needed.
* **Import a new file from this storage**: to create a dataset (stored or live) from a file located in the storage system, you have just plugged.


# SFTP

### Requirements

{% hint style="warning" %}
Ensure that your SFTP is opened (port and IP) on your Toucan's workspace.
{% endhint %}

### Configuring a SFTP remote file storage in Toucan

Fill in the connection information after opening the modal of the connector:

### Connection options

File Storage type

* Format: dropdown list among the options available in [Add a file storage](/data-management-in-datahub/datasources-in-toucan/managing-remote-file-storages/setting-up-a-file-storage)
* Description: the file storage type you are going to configure
* Example: `SFTP`

**Name** (mandatory)

* Format: String
* Description: A name to identify your connection
* Example: `my_SFTP`

**User** (mandatory)

* Format: String
* Description: Your SFTP login user
* Example: `myuser`

**Host** (mandatory)

* Format: String
* Description: The domain name or IP address of your SFTP server
* Example: `sftp.example.com` or `192.168.1.100`

**Port** (optional, default: 22)

* Format: Integer
* Description: The listening port of your SFTP server
* Example: `22`

**Private Key** (mandatory)

* Format: Secret
* Description: The private SSH key used for authentication
* Example:

```
text-----BEGIN OPENSSH PRIVATE KEY-----
...
-----END OPENSSH PRIVATE KEY-----
```

Prefix (optional)

* Format: String
* Description: the default path to access in your remote file storage

**After Configuration**

Click on Test the connection to test the connection with your SFTP or **SAVE** to save the connection. After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasources" tab.

### Troubleshooting

* **Connection Issues**: Double-check your SFTP credentials and network access.
* **Need Help**: Reach out to Toucan support or consult the in-app help center for further assistance.


# Edit, delete a file storage


# Import a file from a storage

### Navigate in a remote file storage connection and create a dataset

To create a dataset from a Remote file storage connection, click on "Import a new file from this storage". You will then be able to:

### **Select a File or Folder**

* You can specify the path to a single file or a folder on the SFTP server.
* If a folder is selected, the connector will list all entries within the folder.

<figure><img src="/files/rllRVJhjUXjshEqOT0qu" alt=""><figcaption><p>Remote file storage navigation</p></figcaption></figure>

### Create a new dataset from a file

After adding a file from a file storage system by clicking on the `+` icon at the right of the file named, you'll be redirected to an interface where you can configure how Toucan will interpret the file.

{% hint style="info" %}
the file type will be pre-filled if Toucan guess it from the file extension, otherwise it must be specified manually
{% endhint %}

<figure><img src="/files/KT80wnE85KLuoCoaOCRe" alt=""><figcaption><p>Create a dataset from a file located in a file storage</p></figcaption></figure>

in this interface, you can:

* view the file path (which cannot be modified)
* configure the file type (csv, parquet, excel, json, geojson).

{% hint style="warning" %}
Please note that if the file type you choose does not match the system file type, you will receive a Toucan error.
{% endhint %}

Once the configuration is complete, you can click on `Preview` or `Validate` to create a dataset from this file.

## Create a dataset from a file located in a remote file storage

The dataset created from this file can be **stored** (downloaded in Toucan) or live (read directly from the file storage system).


# Managing Files

Toucan allows you to easily import files, whether they are located on your **local computer** or on a **remote file server** (S3/FTP).

Toucan supports various file types, including **Excel, CSV, XML, Parquet, JSON, and GEOJSON**. This ensures that you can seamlessly work with different file formats and leverage their data within the Toucan platform

{% hint style="info" %}
**Basemaps**

Basemaps files (used in order to build map charts) should be uploaded with geojson extension.
{% endhint %}

{% hint style="warning" %}
**Warning**

For optimal performance and storage efficiency, it is not recommended to import large files directly into Toucan.\
If your file is large in size, we highly recommend pre-processing the data within the file before uploading it. Alternatively, you can connect directly to a data warehousing tool for seamless integration and efficient data management.
{% endhint %}


# Adding, editing and deleting local files

In this section, we will cover how to:

* Add a file from your computer
* Edit a file
* Delete a file

## Adding a file

Follow the different steps to upload your local file:

1. Drag and drop your file within the green box, or click on the "Add a file" button and select your file within your computer

   <figure><img src="/files/sZy1TBsXyQJNwXyL7Ejj" alt="" width="375"><figcaption></figcaption></figure>
2. Define the settings of your file (consult this [page](/data-management-in-datahub/datasources-in-toucan/managing-files/using-advanced-file-settings) for more information about some advanced configurations) to extract data from the file. Note that for files containing several sheets as an Excel file, you can select the sheet that you would like to import as dataset.
3. Click "Save" (on the bottom) and confirm the saving. Your file is now uploaded and should be listed in the files section. Dataset(s) corresponding to your file should also be created, and are accessible in the "Datasets" tab.

{% hint style="info" %}
**Dataset name**

Within the file configuration interface, make sure that the name given to the dataset that will be associated to the file or selected tab (for Excel case) is not used by another dataset.
{% endhint %}

## Editing a file Settings

Follow the different steps for that:

1. Go to "Datasources" tab
2. Click on the "Settings" action within the actions menu of the file you want to edit

   <figure><img src="/files/3aKf2Zpjk1COU1OVUMNX" alt=""><figcaption></figcaption></figure>
3. Reconfigure the settings (refer to this [page](/data-management-in-datahub/datasources-in-toucan/managing-files/using-advanced-file-settings) for more information about some advanced configurations) and save your file.

## Deleting a file

In this section, we will see how to delete a file that is not needed anymore for your App.

Follow the different steps for that:

1. Go to the "Datasources" tab
2. Click on the "Delete" action within the actions menu of the file you want to delete

   <figure><img src="/files/3aKf2Zpjk1COU1OVUMNX" alt=""><figcaption></figcaption></figure>
3. Confirm the deletion of the file. If the file you are trying to delete is used by a dataset, you will have a warning message and you will be able to select child datasets that you want to delete.


# Advanced file settings

## Overview

We all want to drop a file in Toucan (excel, CSV…) and directly use it in our charts! Most of the time, the file appears perfectly fine on Toucan.

However, let me show you some very useful tricks and directives that will help you if your datasource doesn’t appear properly in the tool.

Sometimes, even when you applied all the requirements for a good flat file as expected by Toucan, you might need to customize the data interpretation done by Toucan to transform a datasource into a dataset.

## Column type

*The first tip to remember for both Excel and CSV files* is that in Toucan, numbers appear in blue, and the strings are in black. When loading a file, some numbers might not be recognized as numbers, for instance.

<figure><img src="/files/WrdP9UNfnanpyVE3XOXU" alt=""><figcaption></figcaption></figure>

It is never too late! You can easily pick your datasource in the data explorer and previsualize it.

You have 2 different ways to do it:

* With the visual interface (in the "CONVERT VALUES" field): you can add your dtype parameter, please write it in a JSON format. For example: `{ "labels": "str" }`
* With the code mode interface: you can use the function dtype, followed by the name of the column and by the type. For example: `dtype: labels : 'str'`

## Dates

For some file types, like Excel or Google Sheets, Toucan should understand that some columns contain dates. But for others, like CSVs, there is no way to find out automatically.

No worries, it’s super easy to tell Toucan which column to parse as a date: just tick the box “parse columns as dates” and indicate the names of dates columns.

![](/files/Bf2qLp78hpi8fCSSurAB)

## Decimals

*For CSV files:* another quite tricky situation is when your file is formatted with decimal separators like the comma (`,`) instead of the international standard (the dot `.`).

But again, no problem for Toucan; you have two different ways to indicate it:

* With the visual interface: you can add your decimal parameter, in the "DECIMAL" field. Just write the desired character.
* With the code mode interface: you can use the function decimal, followed by the character. For example: `decimal: ","`

### Separators

*For CSV files:* Last trick here concerns the format of the file. As it’s very common in CSV files.

We all know that sometimes when we open our CSV file, all the data is not separated into different columns, and you need to apply your separator directly in Excel. This also happens in Toucan. No worries, I got a solution 🙏

You have two different ways to do it:

* With the visual interface: you can add your separator parameter, in the "SEP" field. Just write the desired character.
* With the code mode interface: you can use the function sep, followed by the character. For example: `sep: ","`

💡 Note that these options come from the panda's library we use to interpret the files. If you want to check all the options available, you can find them under the following links:

* for CSV: <https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html>
* for excel: <https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html>

Let’s go!


# Adding and combining remote files in Toucan

Toucan ca read files over a network connection for a large variety of protocols.

## Adding a remote file in Toucan

In order to add a remote file in Toucan, use the code mode of file settings.

Follow the following steps to access it:

* Upload a csv empty file (or any small random csv file) within Toucan
* Switch to code mode within the configuration interface

<img src="/files/ugUnjA4LU0bJ8O3ZHyEB" alt="" data-size="original">

* Replace the fields of the code block depending on the distant file server and configuration associated. Refer to the sections below the fields to fill.
* Save File settings. A new file should appear in the listing of files (in datasources). A dataset will be also automatically created.

Example with a CSV file on Dropbox.

```
domain: 'my_remote_data'
type: 'csv'
file: 'https://www.dropbox.com/s/9yu9ekfjk8kmjlm/fake_data.csv?dl=1'
```

We support:

* ftp (as well as sftp or ftps),
* http (and https),
* S3 and
* a long list of other schemes (‘mms’, ‘hdl’, ‘telnet’, ‘rsync’, ‘gopher’, ‘prospero’, ‘shttp’, ‘ws’, ‘https’, ‘http’, ‘sftp’, ‘rtsp’, ‘nfs’, ‘rtspu’, ‘svn’, ‘git’, ‘sip’, ‘snews’, ‘tel’, ‘nntp’, ‘wais’, ‘svn+ssh’, ‘ftp’, ‘ftps’, ‘file’, ‘sips’, ‘git+ssh’, ‘imap’, ‘wss’).

### FTP Server

* Mandatory: access to a FTP server and to Toucan staging mode on your workspace
* Open [Filezilla](https://filezilla-project.org/) or any FTP client
* Copy the URL corresponding to the location of the file on the FTP server it should look like this:

```
ftp://user:password@example.com/pub/file.txt
```

"ftp" is the protocol used, "user" and "password" are the login credentials "example.com" is the domain of the server, and "/pub/file.txt" is the full path to the file on the server.

{% hint style="info" %}
**Important**

💡Contact us via [help@toucantoco.com](mailto:help%40toucantoco.com) or your Delivery contact to set up a hidden password in the URL
{% endhint %}

* Paste this url in the file field and modify the other configuration fields configuration as explained above in [#adding-a-remote-file-in-toucan](#adding-a-remote-file-in-toucan "mention")
* A new file should appear in the listing of files (in datasources). A dataset will be also automatically created.

```
domain: 'db_test'
type: 'csv'
file: 'ftps://<login>:<password>@ftps.toucantoco.com:990/my_db.csv'
separator: ";"
```

#### Toucan Toco FTP Server

You can send data to Toucan Toco FTP Server with the following credentials:

* Host: ftps.toucantoco.com
* Port: 990 (for the connection) and range 64000-64321 (for data transfert)
* Protocol: FTPS (if you use FileZilla it’s `implicit FTP over TLS`)
* Mode: Passive Mode
* User : Given by the Toucan Toco Team
* Password: Given by the Toucan Toco Team

### S3 Bucket

The access key and secret key for your data files hosted on S3 buckets can be configured this way:

```
s3://<access key>:<secret key>@mybucket/filename'
```

For example:

```
domain: 'my_data'
type: 'csv'
file: 's3://<access key>:<secret key>@mybucket/my_data.csv'
separator: ";"
```

{% hint style="info" %}
Note

If your access key or secret key contains special characters such as “/”, “@” or “:” you have to encode them. URL encoding converts special characters into a format that can be transmitted over the Internet. You will find more infos about this topic [here](https://www.w3schools.com/tags/ref_urlencode.asp) (as well as an automatic encoder).
{% endhint %}

Toucan Toco can provide a S3 bucket with a dedicated AWS IAM user related to your instance.

Thus you will be able to configure your datasources block with a special configuration as following:

```
domain: 'my_data'
type: 'csv'
file: "{{ secrets.extra.s3.s3_uri_auth_encoded }}/my_data.csv"
separator: ";"
```

{% hint style="info" %}
**Note**

If you are using a custom domain name for your S3 bucket using minio per example. Here is the syntax you should use
{% endhint %}

```
domain: 'my_data'
type: 'csv'
file: 's3://<access key>:<secret key>@mybucket/my_data.csv'
separator: ";"
fetcher_kwargs:
    client_kwargs:
        endpoint_url: "https://endpoint.mydomain.com:9000"
```

## Combining a remote file in Toucan

In the [previous page](/data-management-in-datahub/datasources-in-toucan/managing-files/using-advanced-file-settings/adding-and-combining-remote-files-in-toucan), we saw how to add remote files in Toucan. Read the previous page first, before going further with this one.

In this page, we will discover how to combine several remote files into one file.

You can load multiple files - uploaded on our server or on a FTP/S3 server - in a unique file with the option `match: true`. The dataset that will be created from the file will contain a column `__filename__` corresponding to the origin file of the row.

**Tutorial**

Your corporation has now a new file of data each month : data-product-corporation-201801.csv, data-product-corporation-201802.csv … You want them to be loaded in a single domain called `data-product-corpo`

* Find the regular expression (regex) that matches your files with [regex101.com](https://regex101.com/#python).

`data-product-corporation-\d{6}\.csv`

* Don’t forget to use ‘^’ and ‘$’ to be more restrictive.

`^data-product-corporation-\d{6}\.csv$`

* Add a backslach to escape backslaches.

`^data-product-corporation-\\d{6}\\.csv$`

* Copy your regular expression in the "file" option of your datasource block
* Add the option `match: true`

```
domain: 'data-product-corpo'
file: '^data-product-corporation-\\d{6}\\.csv$'
skip_rows: 0
separator: ','
encoding: 'utf-8'
type: 'csv'
match: true
```

Example of content for FTP (with authentication):

```
domain: 'db_test'
type: 'csv'
file: 'ftps://<login>:<password>@ftps.toucantoco.com:990/^data-product-corporation-\\d{6}\\.csv$'
separator: ";"
```

Example of content for S3 (with authentication):

```
domain: 'my_data'
type: 'csv'
file: 's3://<access key>:<secret key>@mybucket/^data-product-corporation-\\d{6}\\.csv$'
separator: ";"
fetcher_kwargs:
    client_kwargs:
        endpoint_url: "https://endpoint.mydomain.com:9000"
```


# Datasets in Toucan

A dataset is a collection of data. At Toucan they appear as tables of data. Datasets are results of queries to which we give a name. They appear in our DataHub, in the “Datasets” tab.

Datasets can be:

* [**Stored or Live**](/data-management-in-datahub/datasets-in-toucan/stored-and-live-datasets)
* **Reusable across all the apps or specific to a story**
  * **Reusable**: By default all datasets created by Toucan can be reused by any other datasets or visualization in Toucan. If you edit it, it will be impacted all the components using it. That's the best way to have an easy-to-maintain app
  * **Specific to a story**: if your dataset depends on the value of a story filter, it won't be used outside of this story, as two stories with different filters won't be able to share the same filter value
* **Created from a datasource or created from another dataset**
  * **Created from a datasource**: a dataset created from a data source will provide a specific data selection experience depending on the source
    * *For instance, if you are creating a dataset from your Redshift connector, you will be able to choose a database, a schema, and then the table with the specific column you would like to use*
  * **Created from another dataset**: a dataset created from another dataset is called a child dataset; if the dataset changes, all its child-datasets will be changed accordingly

In this section, you will learn how to:

* **Manage datasets**: create, edit, duplicate, delete, refresh, and publish datasets
* **Prepare data**: use our no-code querying tool, YouPrep, to transform data and create metrics that matter
* **Maintain data**: organize, set permission and validation rules, see dependencies

<table data-view="cards"><thead><tr><th></th><th></th><th></th><th data-hidden data-card-target data-type="content-ref"></th></tr></thead><tbody><tr><td></td><td><a data-mention href="/pages/lIssW65VbJQcAawr2oVD">/pages/lIssW65VbJQcAawr2oVD</a></td><td></td><td><a href="/pages/lIssW65VbJQcAawr2oVD">/pages/lIssW65VbJQcAawr2oVD</a></td></tr><tr><td></td><td><a data-mention href="/pages/RF0mwEEL0piQm3MTD5XG">/pages/RF0mwEEL0piQm3MTD5XG</a></td><td></td><td></td></tr><tr><td></td><td><a data-mention href="/pages/VAjJQ1BG78wofLJMKO0G">/pages/VAjJQ1BG78wofLJMKO0G</a></td><td></td><td></td></tr></tbody></table>


# Stored and Live Datasets

In this page we will dive in the difference between Stored and Live Datasets and their benefits.

There are two ways to handle dataset storage in Toucan:

* **Stored dataset**: The dataset is stored in the Toucan data store.
* **Live dataset**: The dataset is queried by Toucan every time it is needed to fuel a tile or story.

## Stored Datasets

A **stored dataset** is similar to a [materialized view](https://en.wikipedia.org/wiki/Materialized_view). It stores the pre-computed result of a query (in this case, a **YouPrep pipeline**). The resulting data is saved in the **Toucan data store**.

When using a stored dataset, you rely entirely on Toucan for both storage and computation. The computation can be triggered:

* **manually**, after building the dataset or on demand
* **automatically**, at a scheduled time through an automation

### How a stored dataset is refreshed

A stored dataset represents a snapshot of your data at a given moment. To update it, you must perform a *refresh*. See [refresh datasets](/data-management-in-datahub/datasets-in-toucan/managing-datasets/stored-datasets/refreshing-and-publishing-datasets) for more information

A **refresh** is a Toucan feature that recomputes the dataset by:

* fetching its datasource (e.g., a flat file or a SQL query to a database)
* transforming the data according to the steps defined in the YouPrep pipeline
* storing the updated result in the Toucan data store

A refresh only affects **staging** data. To apply these updates to **production**, you must publish the app.\
Once the refresh is complete, the previous dataset result is replaced with the updated data.

{% hint style="warning" %}
A stored dataset is already computed and then cannot include any variable
{% endhint %}

## Live Datasets

A **Live Dataset** is a dataset whose result is computed on-the-fly each time it is needed **the result is** not kept as a pre-computed result in the Toucan data store. It is computed either directly at the [datasource level](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql), in Toucan’s in-memory engine (RAM), or through a [hybrid computation](/data-management-in-datahub/datasets-in-toucan/preparing-data/hybrid-pipeline) combining both. Starting with a live dataset ensures that the displayed data is always up to date, reflecting the latest state of the source.

### From which datasets can a live dataset be built?

Live Datasets can be built on top of:

* Other live datasets: if all datasets in the lineage are live datasets, it means that Toucan will not store data at any point of the data preparation process - so no data replication outside your own systems -, and the data displayed to users will be as fresh as it exists in the external datasource

<figure><img src="/files/hzUwHWO7GeJ8XAY251Kv" alt="Data lineage with only live datasets in Toucan"><figcaption><p>When all datasets in Toucan are live, data is as fresh as the source</p></figcaption></figure>

* Stored datasets: in this case the data will be as fresh at its parent dataset in Toucan. Using a live dataset on top of a stored dataset is useful if you want to use [user context related variables](#user-content-fn-1)[^1] in the dataset

<figure><img src="/files/RshQauADSbOIiiet7Dl8" alt="Data lineage with a stored parent datasets"><figcaption><p>When a live dataset is built on top of a stored dataset it is as fresh as the parent dataset</p></figcaption></figure>

## Stored datasets vs. Live Datasets benefits

|                                        Stored datasets                                        |                                                              Live datasets                                                             |
| :-------------------------------------------------------------------------------------------: | :------------------------------------------------------------------------------------------------------------------------------------: |
|  Useful if you don't have any data warehousing solution in your data stack to build analytics | <p>If all the parent datasets are also live:<br>- No data replication outside of your system<br>- Data as fresh as the data source</p> |
| Already computed: can be faster than live datasets (depending on the data source performance) |                                                Can use variables in the computation step                                               |

[^1]: Variables related to a user context depends on an individual specific user: a user interacting with the app with specific attributes (roles, permissions) or selecting filter values on its browser


# Transform data with YouPrep

In this section, we will:

* Discover our integrated no-code data preparation tool YouPre&#x70;**™**
* Learn more about some specifities of its functioning as
  * NativeSQL
  * Learn how to deal with variables with YouPre&#x70;**™**


# Overview of YouPrep™

## What is YouPrep™?

**YouPrep™** is **Toucan's no-code data preparation module**. This visual interface enables non-technical business users to easily transform their data.

**YouPrep™ enables data transformation through** via a UI displaying a configuration form with different options depending on the data transformation step&#x73;**.**

Toucan has implemented different data transformation steps (filtering, computation, aggregation, text and date operations, reshaping, and combining several datasets)

## YouPrep™ interface

YouPrep™ interface breaks down into two panels:

* **The data transformation pipeline** (combinaison of all transformation steps) on the left shows the ordered series of transformation steps applied to your data.
  * When you need to configure a given step, the left panel switches to the step edition form
* **the data table preview** on the right shows th result of the Data transformation applied to a subset of data
* The right panel also includes a **widgets menu** above the data table. The widgets menu gathers the different data transformations steps available from the columns headers

<figure><img src="/files/kEgHBD2z6BXfCvJFjE5R" alt="Capture screen that represents the UI of YouPrep with the different elements: search bar, data transformation pipeline, data table preview, hybrid pipeline indication, widgets menu" width="563"><figcaption><p>YouPrep UI with the different section</p></figcaption></figure>

## Use YouPrep™ steps

There is different ways to use YouPrep™ steps:

* By using the **widgets menu**:

<figure><img src="/files/XnHlkVEgnZTA6Zt4y52P" alt="" width="563"><figcaption><p>YouPrep widgets menu</p></figcaption></figure>

* Or through the **search bar**:

<figure><img src="/files/C8gvI8PCZgB1Uj17V5aT" alt="Capture that shows the search bar of Youprep" width="291"><figcaption><p>YouPrep search bar</p></figcaption></figure>

* Some steps are only available by clicking on the three dot menu at the top right of each column header

<figure><img src="/files/aSRG1X9fQtxEtAIF2SJU" alt="Captures that shows the steps that can be used by clicking on the three dot menu available on each column header" width="244"><figcaption><p>YouPrep column header menu</p></figcaption></figure>

### YouPrep™ data transformation pipeline

When applying a data transformation step:

* the result of the transformation appears in the data table
* **the step** is added at the bottom of the data transformation pipeline.
  * you can edit any step configuration by clicking on the <i class="fa-pencil">:pencil:</i> button
  * you can delete any step of a given pipeline by clicking on <i class="fa-circle">:circle:</i> icon located on the left, if a step is deleted the remain steps will be unchanged.
  * It is also possible to drag and drop a step in the pipeline to execute it earlier or later in the pipeline.

If you click on any step of the pipeline, the data table will update to show you the result of the transformations until this step. Following steps get greyed to show that they are temporarily disabled and not executed. It can be very helpful when debugging.

### Subset of data

In order to avoid to apply the data transformation on too many results and slow down your experience.\
\
The computation preview of your data transformation pipeline is applied on a **limited number of rows** (10 000 by default).\
\
You can adjust this number of rows for preview computation with any value. It can be configured by clicking on the <i class="fa-eye-dropper">:eye-dropper:</i> icon.

<figure><img src="/files/oavtL8C3s3urLAzuVbUp" alt="" width="563"><figcaption><p>subset of data</p></figcaption></figure>

{% hint style="info" %}
Note that your preview and experience could be slowed down if you choose a computation preview too high.
{% endhint %}

#### Performance optimization

Toucan data transformation pipeline is processed **sequentially**. The **ordering of steps matter**, so we recommend to minimize the number of rows, by applying filtering steps at the beginning, in order to allow the following steps to be processed faster.

#### Step insertion

You can insert a step in the middle of a data transformation pipeline. The newly added step in the pipeline is placed immediately after.

**So if you want to insert a step in a data transformation pipeline:**

* select the step after which you need to insert your new step,
* Configure your data transformation step
* Your step is applied after the selected step

## Hybrid Pipeline in YouPrep

YouPrep through [Hybrid pipeline](/data-management-in-datahub/datasets-in-toucan/preparing-data/hybrid-pipeline) allows to mix different data transformation engines in one data transformation pipeline

* [NativeSQL](/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql), which is a Toucan feature that allow to configure steps with YouPrep that will be translated in SQL and transformation at the database level
* Toucan in-memory engine where the data transformation steps are applied in-memory in Toucan backend.

{% hint style="info" %}
**More detail on steps documentation**

You can consult [weaverbird](https://weaverbird.toucantoco.dev/docs/general-principles/) documentation or the following pages of this section, if you need further information about YouPrep!
{% endhint %}


# Column header

### Overview

When you are working on a dataset to create a dashboard, you might want to apply an operation on a specific column on the dataset you are working on. **You can access the following steps to your dataset through the hamburger menu on the data table when working with a specific column**:

* [Rename column](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/column-header/rename-column)
* [Duplicate column](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/column-header/duplicate-column)
* [Delete columns](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/filter/delete-columns)
* Other operations
  * [Filter rows](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/filter/filter-rows)
  * [Top N rows](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/filter/top-n-rows)
  * [Fill null values](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/column-header/fill-null-values)
  * [Replace values](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/column-header/replace-values)
  * [Sort values](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/column-header/sort-values)
  * [Trim spaces](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/text/trim-spaces)
  * [Get unique groups/values](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/aggregate/get-unique-groups-values)
  * [Compute statistics](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/compute/compute-statistics)

Through the column type you can access to:

* [Convert](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/column-header/convert-columns-data-types)


# Rename column

The Replace column step enables you to change the name of a specified column in

### Step parameters

* `columns to rename...` **array(column, name)\***: The column(s) to rename
  * `column` **column(string)\***: Name of the column to modify
  * `new column name` **string\***: Name of the new column after modification

### Example

**Input**

<figure><img src="/files/pZCazL3x5PYu5eJSbbMC" alt=""><figcaption><p>Column header - rename column input</p></figcaption></figure>

**Configuration**

```json
{
    "to_rename": [
        ["Group", "New_Group"],
        ["Label", "New_Label"]
    ]
}
```

**Output**

<figure><img src="/files/bkaRAB51IRQ1niKlTvHH" alt=""><figcaption><p>Column header - rename column output</p></figcaption></figure>


# Duplicate column

The Duplicate column step enables you to duplicate a specific column. The duplicated column will be added to the end of the dataset.

### Step parameters

* `Duplicate column...` **column(string)\***: the column to duplicate
* `new column name` **string\***: Set a name for the duplicated column.

### Example

**Input**

<figure><img src="/files/G0b4omOHnutU2ynyiZQb" alt=""><figcaption><p>Column header - duplicate column input</p></figcaption></figure>

**Configuration**

```json
{
    "column": "Value",
    "new_column_name": "Value_duplicate"
}
```

**Output**

<figure><img src="/files/LEXIPTAjFEZZOO6PNjjK" alt=""><figcaption><p>Column header - duplicate column output</p></figcaption></figure>

{% hint style="warning" %}
an error will be raised if you try to set a name that is already used for another column.
{% endhint %}


# Fill null values

The fill null values step allows to fill null values in specified columns with a value of your choice.

### Step parameters

* `Replace null values in..` **column(array)\***: the columns where the null values will be filled with another value
* `With` **value\***: the value that will replace null values

### Example

**Input**

<figure><img src="/files/ceymbEfViNE4qyyNG5sV" alt=""><figcaption><p>Column header - fill null values input</p></figcaption></figure>

**Configuration**

```json
{
    "columns": ["Value", "KPI"],
    "value": "NaN"
}
```

**Output**

<figure><img src="/files/nJhHJtuCHQg4LDWY26sJ" alt=""><figcaption><p>Column header - fill null values output</p></figcaption></figure>


# Replace values

The replace vaules step allows to to replace values in a column

### Step parameters

* `Search in column...` **column(string)\***: the column in which to search for values to replace
* `Values to replace` **array(value, new value)\***: in this section of the form you can specify one or more values to replace, with the corresponding new value that you expect.

### Example

**Input**

<figure><img src="/files/8NcoVav34EB7MOQjjFzG" alt=""><figcaption><p>Column header - replace values input</p></figcaption></figure>

**Configuration**

```json
{
    "search_column": "Country",
    "to_replace": [
        ["FR", "France"]
        ["UK", "Great Britain"]
    ]
}
```

**Output**

<figure><img src="/files/5fl6QqPSBk8xefpzFI1t" alt=""><figcaption><p>Column header - replace values output</p></figcaption></figure>


# Sort values

The sort values step allows to sort values based on one or several columns.

### Step parameters

* `Column...`: the column where values will be sorted
* `Order`: whether you want the values in this column to be sorted in ascending (`asc`) or descending order (`desc`).

### Example

**Input**

<figure><img src="/files/SvjdJ9iIqrJsGGU2B3Yz" alt=""><figcaption><p>Column header - sort values input</p></figcaption></figure>

**Configuration**

```json
{
    "columns": [
        {
            "column": "Value",
            "order": "desc"
        },
    ]
}
```

**Output**

<figure><img src="/files/GXV82zvtXFRvxPraBEo1" alt=""><figcaption><p>Column header - sort values output</p></figcaption></figure>

{% hint style="info" %}
You can add columns to apply a combined sort, by clicking on “Add column”. The order of columns matters, i.e. first the first column will be sorted, then and without changing the order of the first column, the second column will be sorted etc. Please see the example below for illustration.

You can specify if you want to rank rows based on one or several columns.
{% endhint %}


# Convert columns data types

The Convert columns data types allows to cast column data types.

### Step parameters

* `Convert columns` **column(array)\***: the columns to convert
* `To data type` **type(string)**: the data type to convert into, either `integer`, `float`, `text`, `date` or `boolean`

### Example

**Input**

<figure><img src="/files/5NSelszFKKIszK810IUD" alt=""><figcaption><p>Column header - convert input</p></figcaption></figure>

**Configuration**

```json
{
  "columns": ["id", "boolean_column"]
  "data_type": "text"
}
```

**Output**

<figure><img src="/files/xykpD52FA1uSzCEHesJm" alt=""><figcaption><p>Column header - convert output</p></figcaption></figure>

{% hint style="info" %}
In a effort to harmonize as much as possible the conversion behaviors, for some cases, in NativeSql our implementation casting works otherwise than the CAST AS method.

Precisely, when casting float to integer, the default behavior rounds the result, other languages truncate it. That’s why the use of `TRUNCATE` was implemented when converting float to int. The same implementation was done when converting strings to int (for date represented as string). As for the conversion of date to int, we handled it by assuming the dataset’s timestamp is in `TIMESTAMP_NTZ` format.
{% endhint %}


# Add

### Overview

When you are working on a dataset to create a dashboard, you might want to add columns the dataset you are working on. YouPrep allows to add data through some steps. **You can apply the following steps to your dataset**:

* [Add text column](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/add/add-text-column)
* [Add formula column](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/add/add-formula-column)
* [Add conditional column](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/add/add-conditional-column)


# Add text column

The Add Text column step allows you to create a new column in your dataset with a string to fill

### Step parameters

1. `New column`**(string)\*** : Specify the name for the new column that will contain the result of your formula.
2. `Enter a text` **(string)\***: Specify the text that will fill all the rows of the new column you are going to create

### Example

**Input**

<figure><img src="/files/qkmAFfxntHkD5hztl44K" alt=""><figcaption><p>Add - Add a text column input</p></figcaption></figure>

**Configuration**

```json
{
    "new_column": "new_one",
    "text": "This is a text"
}
```

**Output**

<figure><img src="/files/bzgvcvJ6CigHCnVsBCy6" alt=""><figcaption><p>Add - Add a text column output</p></figcaption></figure>

{% hint style="warning" %}

* If you try to add a column that already exists you will overwrite it.
* If you try to add an int in the "Enter a text" input it will interpreted as a string
  {% endhint %}


# Add formula column

The Formula step allows you to create a new column in your dataset by applying a custom formula to existing columns.

### Step parameters

1. `New Column` **(string)\***: Specify the name for the new column that will contain the result of your formula.
2. `Formula`**\***: Enter the formula you want to apply. This can be a mathematical expression, a string manipulation, or a combination of functions. You can reference existing columns in your formula.

### Example

**Input**

<figure><img src="/files/NiWPzk25tZE0PL2bCMnX" alt=""><figcaption><p>Add - Add formula column input</p></figcaption></figure>

**Configuration**

```json
{
    "new_column": "monthly_salary",
    "formula": "salary/12"
}
```

**Output**

<figure><img src="/files/99aux75Qrlg5mPbo3rzN" alt=""><figcaption><p>Add - Add formula column output</p></figcaption></figure>

{% hint style="warning" %}
A column can be referenced by its name without quotes unless they include whitespaces, in such a case you need to use brackets ‘\[]’ (e.g. `[myColumn]`).

Any characters string escaped with quotes (simple or double) will be considered as a string.

The supported operators are : addition (`+`), substraction (`-`), multiplication (`*`), division (`/`), modulo (`%`).
{% endhint %}

{% hint style="info" %}
**Supported operators**

The following operators are supported by the formula step (note that a value can be a column name or a literal, such as `42` or `foo`).

* `+`: Does an addition of two numeric values. **See the `concatenate` step to append strings**
* `-`: Does an substraction of two numeric values. **See the `replace` step to remove a part of a string**
* `*`: Multiplies two numeric values.
* `/`: Divides a numeric value by another. Divisions by zero will return `null`.
* `%`: Returns the rest of an integer division. Divisions by zero will return `null`.
  {% endhint %}


# Add conditional column

The Add conditional column allows you to create a new column based on If...Then...Else schema with possibilities to add nested conditions in order to create conditional logic for data manipulation.

The condition is expressed in the `if` parameter with a condition object, which is the same object expected by the `condition` parameter of the [filter step](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/filter/filter-rows)). Conditions can be grouped and nested with logical operators `and` and `or`.

The `then` parameter only supports a string, that will be interpreted as a formula (cf. [formula step](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/add/add-formula-column)). If you want it to be interpreted strictly as a string and not a formula, you must escape the string with quotes (e.g. ‘“this is a text”’).

`if...then...else` blocks can be nested as the `else` parameter supports either a string that will be interpreted as a formula (cf. [formula step](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/add/add-formula-column)), or a nested if `if...then...else` object.

### Step parameters

1. `New Column` **(string)**: Enter a name for the new column that will be created based on your conditions and formulas.
2. `If Condition`: Define the condition(s) that will be evaluated.
3. `Then Formula`: Specify the formula to be applied when the condition is true.
4. `Else Formula`: Specify the formula to be applied when the condition is false.

### Example

**Input**

<figure><img src="/files/COJaluNgt0am0VIVxT5w" alt=""><figcaption><p>Add - Add conditional column input</p></figcaption></figure>

**Configuration**

```json
{
    "new_column": "Category",
    "if": {
            "and": [
                {
                        "column": "Price",
                        "operator": "ge",
                        "value": 50
                },
                {
                        "column": "Stock",
                        "operator": "gt",
                        "value": 10
                },
            ]
        },
    "then" "Premium",
    "else": "Standard"    
}
```

Where `gt`: greater than and `ge`: greater than or equal to

**Output**

<figure><img src="/files/eSrg8WqamKVd4Hx53xRP" alt=""><figcaption><p>Add - Add a conditional column output</p></figcaption></figure>

{% hint style="info" %}
**Defining Conditions**

You can create three types of conditions:

1. **Simple Condition**:
   * Column Name: Enter the name of the column you want to filter on.
   * Operator: Choose from operators like equals, not equals, greater than, less than, etc.
   * Value: Enter the value to compare against.
2. **AND Condition**: Combine multiple conditions that must all be true.
3. **OR Condition**: Combine multiple conditions where at least one must be true.
   {% endhint %}

{% hint style="info" %}
**Operators**

The following operators are available for conditions:

* eq (equals)
* ne (doesn't equal to)
* gt (is greater than)
* ge (is greater than or equal to)
* lt (is less than)
* le (is less than or equal to)
* in (is one of)
* nin (is not one of)
* matches (matches pattern)
* notmatches (doesn't match pattern)
* isnull (is null)
* notnull (is not null)
* from (starting in/on)
* until (ending in/on)
  {% endhint %}

{% hint style="info" %}

#### Formulas

For the "Then" and "Else" parts, you can enter formulas that will be applied based on the condition results. These can be simple values or complex expressions. See the [formula step](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/add/add-formula-column) for more information.
{% endhint %}

{% hint style="info" %}

#### Nested Conditions

You can create nested conditions by using another If...Then...Else structure in the "Else" part of your condition.
{% endhint %}


# Filter

### Overview

When you are working on a dataset to create a dashboard, you might want to filter your data to display only a part of them. YouPrep allows to apply filter that will help you to filter your dataset on some conditions. **You can apply the following operations to your dataset**:

* [Delete columns](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/filter/delete-columns)
* [Keep columns](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/filter/keep-columns)
* [Filter Rows](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/filter/filter-rows)
* [Top N Rows](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/filter/top-n-rows)
* [ArgMax](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/filter/argmax)
* [ArgMin](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/filter/argmin)


# Delete columns

The Delete column step allows you to delete column in the dataset you are working on.

### Step parameters

* `Delete columns`**(array)\***: specify one or several column(s) to delete

### Example

**Input**

<figure><img src="/files/tNEDoyfuko1Iz435jApa" alt=""><figcaption><p>Filter - Delete columns input</p></figcaption></figure>

**Configuration**

```json
{
    "columns" : ["departement", "hire_date", "email"]
}
```

**Output**

<figure><img src="/files/bCox44YFu6eaUA7xGDbW" alt=""><figcaption><p>Filter - Delete columns output</p></figcaption></figure>


# Keep columns

The Keep columns step allows you to choose a column or multiple columns, meaning that it will delete every other columns. Useful when you have a lot of columns and that you only need to use a few of them.

### Step parameters

* `Keep columns` **(array)\***: specify one or several column(s) to keep

### Example

**Input**

<figure><img src="/files/tNEDoyfuko1Iz435jApa" alt=""><figcaption><p>Filter - Keep columns input</p></figcaption></figure>

**Configuration**

```json
{
    "columns" : ["id", "name", "country", "salary"]
}
```

**Output**

<figure><img src="/files/rfTw6uEtsA9KocMn5bIG" alt=""><figcaption><p>Filter - Keep columns output</p></figcaption></figure>


# Filter rows

The Filter step allows you to selectively include or exclude rows from your dataset based on specified conditions. This step is part of a data processing pipeline and can be used to modify data coming from datasets represented in rows and columns.

### Step parameters

**Condition**: This is where you define your filtering criteria. You can create three types of conditions:

* **Simple condition\***:
  * `Column`**(string)\***: Enter the name of the column you want to filter on.
  * `Operator`**\[operators]\***: Choose an operator to filter your data. defaults to `eq`
  * `Value`**\***: Enter the value to compare against (not required for `isnull` and `notnull` operators).
* **ADD CONDITION** : Combine multiple simple conditions that you can bind by either an "AND" or "OR" logical operator
* **ADD GROUP ()**: Add a group of simple conditions that you can bind by either an "AND" or "OR" logical operator. Note that you cannot nest a group of conditions in another group.

### Example

**Input**

<figure><img src="/files/tNEDoyfuko1Iz435jApa" alt=""><figcaption><p>Filter rows - input</p></figcaption></figure>

**Configuration**

```json
{
    "condition": {
        "OR": [
            {
                "column": "department",
                "value": "IT",
                "operator": "eq"
            },
            {
                "column": "country",
                "value": "Canada",
                "operator": "eq"
            }
        ]
}
```

{% hint style="info" %}
Rows meeting any of these conditions will appear in the filter's output
{% endhint %}

**Output**

<figure><img src="/files/GaHUwqNAmSTnVyjkbN0C" alt=""><figcaption><p>Filter - filter rows - output</p></figcaption></figure>

{% hint style="info" %}
**\[Operators]**

The following operators are available for conditions:

* `eq` (equals)
* `ne` (doesn't equal to)
* `gt` (is greater than)
* `ge` (is greater than or equal to)
* `lt` (is less than)
* `le` (is less than or equal to)
* `in` (is one of)
* `nin` (is not one of)
* `matches` (matches pattern)
* `notmatches` (doesn't match pattern)
* `isnull` (is null)
* `notnull` (is not null)
* `from` (starting in/on)
* `until` (ending in/on)

`matches` and `notmatches` operators are used to test value against a regular expression.
{% endhint %}

{% hint style="info" %}
**Values**

`value` can be an arbitrary value depending on the selected operator (e.g a list when used with the `in` operator, or `null` when used with the `isnull` operator).

Value can be:

* a variable or
* a fixed value among
  * `date`,
  * `string`,
  * `int`,
  * `float`,
  * `array`

For date only `starting in/on`, `ending in/on`, `is null` ,`is not null` are available
{% endhint %}


# Top N rows

The Top N rows step allows you get the top N rows of your data based on value column to be ranked. The top can be performed by group if specified

### Step parameters

* `Get top...`**\*(int)**: specify the number of top rows to retain
* `Sort column...`**\*(string)**: the ranking will be based on this column (so its values must be sortable)
* `Sort order...`**\*\["asc", "desc"]**: whether the you want the above column to e sorted on ascending (`asc`) or descending (`desc`) order
* `Group by...` **(array)**: if you want to return a top by group, you can select one or several columns that will be used to constitute unique groups

### Example

**Input**

<figure><img src="/files/tNEDoyfuko1Iz435jApa" alt=""><figcaption><p>Filter - Top N rows input</p></figcaption></figure>

**Configuration**

```json
{
    "limit": 5,
    "rank_on": "salary",
    "sort": "desc"
    "group_by": []
}
```

**Output**

<figure><img src="/files/uWz77m6M9RX2CMz4bqDu" alt=""><figcaption><p>Filter - Top N rows output</p></figcaption></figure>


# ArgMax

The ArgMax step allows you to get row(s) matching the maximum value in a given column. You can optionally specify to apply the step by group, i.e. get max row(s) by group.

### Step parameters

* `Search max value in`**\* (string)**: the column the maximum value will be searched in
* `Group by` **(array)**: array of one or several columns that will be used to constitute unique groups. Then the step will return max row(s) for every group)

### Example

**Input**

<figure><img src="/files/tNEDoyfuko1Iz435jApa" alt=""><figcaption><p>Filter - Argmax input</p></figcaption></figure>

**Configuration**

```json
{
    "column": "age"
    "group_by": []
}
```

**Output**

<figure><img src="/files/8cnSBdgxiFPbR5iGt2is" alt=""><figcaption><p>Filter - Argmax output</p></figcaption></figure>


# ArgMin

The ArgMin step allows you to get row(s) matching the minimum value in a given column. You can optionally specify to apply the step by group, i.e. get min row(s) by group.

### Step parameters

* `Search min value in`**\* (string)**: the column the minimum value will be searched in
* `Group by` **(array)**: array of one or several columns that will be used to constitute unique groups. Then the step will return min row(s) for every group)

### Example

**Input**

<figure><img src="/files/tNEDoyfuko1Iz435jApa" alt=""><figcaption><p>Filter - Argmin input</p></figcaption></figure>

**Configuration**

```json
{
    "column": "age"
    "group_by": []
}
```

**Output**

<figure><img src="/files/yNwXKRYBgVuixGDVQXH4" alt=""><figcaption><p>Filter - Argmin output</p></figcaption></figure>


# Aggregate

When you are building your dataset to create a dashboard, you might not have the values at the visualization level you need: if you have your data aggregated at a city level, you might want to visualize them at a country level. You will then need to aggregate them. YouPrep allows to aggregate your dataset. **You can apply the following operations to your dataset**:

* [Group By](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/aggregate/group-by)
* [Add totals rows](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/aggregate/add-total-rows)
* [Hierarchical roll-up](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/aggregate/hierarchical-rollup)
* [Get unique Groups/value](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/aggregate/get-unique-groups-values)


# Group by

The Group by step allows you to group your data by one or more columns and perform calculations on other columns. This step is useful for summarizing data and creating reports.

### Step parameters

2. `Group rows by` **column(array)\***: Select one or more columns that will be used to constitute unique groups. For example, you might group by "product" or "category".
3. `And aggregate...` **array(aggregation)\***: Define one or more aggregations to perform on your grouped data. For each aggregation, you need to specify:
   * `Columns`: **column(array)\***: the columns to be aggregated (you can apply the same aggregation function to several columns at once)
   * `Function` **(string)\***: the aggregation function to be applied (`sum`, `avg`, `count`, `min`, or `max`)
4. `Keep Original Granularity` **(boolean)**: whether to keep the original granularity, in that case computed aggregations will be added in new columns. If unchecked, the output will only contain the grouped and aggregated data
5. `Count null values like regular values` (boolean): Select whether to include `null` values in the count. If checked, `null` values will be counted as regular entries.

### Example

**Input**

<figure><img src="/files/DMOMm2LD956Zb22FPURT" alt=""><figcaption><p>Aggregate - group by input</p></figcaption></figure>

**Configuration**

```json
{
    "on": []
    "aggregations": [
        {
            "columns": [],
            "aggfunction": ""
        },
        {
            "columns": [],
            "aggfunction": ""
        }
    ]
    "keep_original_granularity": false 
}
```

**Output**

<figure><img src="/files/E22F7efzIRsZPX9qXifJ" alt=""><figcaption><p>aggregate - group by output</p></figcaption></figure>

{% hint style="info" %}
If an aggregation function is applied once in a column, the output column will replace the aggregated column with the same name.

If it's applied twice or more on the same column, the aggregated columns will be named `column_name-aggfunction`

For example, if you compute an aggregation on a sales column for sum and average, you will have a column named `sales-sum` and another one titled `sales-avg`
{% endhint %}


# Add total rows

The Add total rows step allows you to compute “Total” columns and append those rows to your current dataset

### Step parameters

* `Columns to compute total rows in`: Specify a dimension columns on the left side, and the corresponding total rows label on the right side. You can add several couples to compute total rows in several columns.
  * `Columns to aggregate`: in this section of the form you can specify one or more columns to aggregate, with the corresponding aggregation function to be applied .You can add columns to aggregate by clicking on the button `Add aggregation`.
    * `Columns`: the columns to be aggregated (you can apply the same aggregation function to several columns at once)
    * `Function` the aggregation function to be applied (`sum`, `average`, `count`, `count`` ``distinct`, `min`, `max`, `first` or `last`)
  * `(Optional) Group by`: Optional, if you need to apply the total rows computation by group of rows

### Example

**Input**

<figure><img src="/files/rH7ObCj2QdYNDjSFr0ep" alt=""><figcaption><p>Aggregate - add total rows input</p></figcaption></figure>

**Configuration**

```json
{
    "totals_dimensions": [
        {
            "total_col": "country",
            "total_rows_label": "all countries"
        }
    ]
    "aggregations": [
        {
            "columns": ["value1"],
            "aggfunction": "sum"
        }
    ],
    "group_by": []
}
```

**Output**

<figure><img src="/files/9s6zkXMaGbH7BrbDEh3x" alt=""><figcaption><p>Aggregate - add total rows output</p></figcaption></figure>


# Hierarchical rollup

The Hierarchical rollup step allows you to compute aggregated data at every level of a hierarchy, specified as a series of columns from top to bottom level. The output data structure stacks the data of every level of the hierarchy, specifying for every row the label, level and parent in dedicated columns.

Aggregated rows can be computed with using either `sum`, `average`, `count`, `count distinct`, `min`, `max`, `first` or `last`.

### Step parameters

* `Hierarchal columns (from top to bottom level)`: here you must specify the list of columns that have a hierarchical link, in hierarchical order from top to bottom level.
* `(Optional) Columns to aggregate`: Here you can specify one or more columns to aggregate, with the corresponding aggregation function to be applied. You can add a column to aggregate by clicking on the button `Add aggregation`.
  * `Columns`: the columns to be aggregated (you can apply the same aggregation function to several columns at once)
  * `Function` the aggregation function to be applied (`sum`, `average`, `count`, `count distinct`, `min`, `max`, `first` or `last`).
* `(Optional) Group rollup by`: Optional, if you need to apply the rollup computation by groups of rows, you may specify here columns used to constitute groups.
* `(Optional) Label column name to be created`: Optional, if you want to give a custom name to the output labels column (`label` by default).
* `(Optional) Level column name to be created`: Optional, if you want to give a custom name to the output levels column (`level` by default).
* `(Optional) Parent column name to be created`: Optional, if you want to give a custom name to the output parents column (`parent` by default).

### Example

**Input**

<figure><img src="/files/S5nkjyQNr5f3GLEG8Psz" alt=""><figcaption><p>Aggregate - hierarchical rollup input</p></figcaption></figure>

**Configuration**

```json
{
    "hierarchy": ["continent", "country", "city"],
    "aggregations": [
        {
            "aggfunction": "sum",
            "columns": ["value", "value_bis"]
        }        
    ],
    "group_by": [],
    "label_col": "label",
    "level_col": "level",
    "parent_label_col": "parent"
}
```

**Result**

<figure><img src="/files/zRM21iCmIsCioqOn2E7S" alt=""><figcaption><p>Aggregate - hierarchical rollup output</p></figcaption></figure>


# Get unique groups/values

The Get unique groups/value step allows you to get the values from a column or unique groups of values from a combination of several columns.

### Step parameters

* `Get unique groups/values in columns` **column(array)\***: Select one or several columns that will be combined to constitute unique groups of values

### Example

**Input**

<figure><img src="/files/NRTixcYgxOj4S7dpM7QJ" alt=""><figcaption><p>Aggregate - get unique groups / values input</p></figcaption></figure>

**Configuration**

```json
{
    "columns": ["category", "product"]
}
```

**Output**

<figure><img src="/files/fjZpjnA4urSoF2O80eLs" alt=""><figcaption><p>Aggregate - get unique groups / values output</p></figcaption></figure>


# Aggregating data

Aggregate in YouPrep™

### Overview

<figure><img src="/files/YFJwmyV0SkE6wXNhlvZV" alt="aggregate widget" width="203"><figcaption><p>aggregate widget</p></figcaption></figure>

### Examples

{% hint style="warning" %}
Warning

Before you start!

My dataset is containing my turnover per Quarter for each year.
{% endhint %}

<figure><img src="/files/kZAplb6lnLVQsvUzJXpJ" alt="dataset"><figcaption><p>dataset</p></figcaption></figure>

#### Ex1: Group by

I want to display the evolution of my turnover per year. I need to group all my values per year using the Aggregate step : Group By operation:

* Group by allows you to perform different types of operation on your KPIs : you can sum them up or get their average for example !

Your YouPrep™ step should look like this:

<figure><img src="/files/Q7r0EqlEULqzViTBvvy5" alt="YouPrep™ step"><figcaption><p>YouPrep™ step</p></figcaption></figure>

Here you go ;)

<figure><img src="/files/KB58w8WRH4DektmldflW" alt="YouPrep step"><figcaption><p>YouPrep step</p></figcaption></figure>

#### Ex2: Hierarchical rollup

My data might only contains data at the lowest level. The hierarchical rollup operation will allow you to aggregate data at each hierarchical level and stack the aggregated data of every level, based on hierarchical columns (e.g. city < subcountry < counrtry ). This step is really helpful to give your dataset a hierarchical structure that Toucan could interpret properly for its charts (ex leaderboard drill, waterfall drill… or even hierarchical view selector).

> 📝 Perfect operation to set a hierarchy with only the children value.

Before you start! You will often have to display various levels of aggregations on your screens. For ewample, my dataset contains the main cities worldwide. We can find different level: names of the city, subcountry and country. Thanks to the hierarchical rollup operation, we are going to create a hierarchy between these three columns.

```
![keep column](image14.png)

I would like to have the number of main cities by subcountries and countries. By adding a hierarchical rollup step, the output data structure stacks the data of every level of the hierarchy, specifying for every row the label, level and parent in dedicated columns.
Your YouPrep step should look like this:
![rollup operation](image10.png)

Now I can add a filter to be able to have the ranking of the main city at the three levels:
  - Ranking of the countries containing the most “main cities”
  - Ranking of the sub countries containing the most “main cities”
  - Ranking of the main cities

![rollup operation2](image11.png)
```

Ex3: Get unique

> 📝 You can use this step to get the unique values from a column or unique groups of values from a combination of several columns.

<figure><img src="/files/96445Nhi09fcpJHqYtGn" alt="get unique1" width="375"><figcaption><p>get unique1</p></figcaption></figure>

Get unique groups/values in columns: you can select one or several columns that will be combined to constitute unique groups of values.

<figure><img src="/files/peXnl3WzonUhDu7cfqDh" alt="get unique2"><figcaption><p>get unique2</p></figcaption></figure>

Tadaaaa 🎉

<figure><img src="/files/TLMmHotaHmHm1kioDJmd" alt="get unique3"><figcaption><p>get unique3</p></figcaption></figure>

Do not hesitate to read the [weaverbird](https://weaverbird.toucantoco.dev/docs/general-principles/) documentation if you need any further informations on YouPrep!


# Compute

When you are building your dataset to create a dashboard, you might want to make various data transformations and calculations to enrich your dataset, derive insights, and prepare it for effective visualization. You can apply the following operations to your dataset:

* [Add formula column](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/add/add-formula-column)
* [Compute evolution](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/compute/compute-evolution)
* [Cumulated sum](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/compute/cumulated-sum)
* [Percentage of total](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/compute/percentage-of-total)
* [Rank](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/compute/rank)
* [Moving average](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/compute/moving-average)
* [Compute statistics](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/compute/compute-statistics)
* [Absolute value](/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm/compute/absolute-value)\\


# Compute evolution

This Compute evolution steps allows you to calculate the change in values over time, both in absolute terms and as a percentage. It's particularly useful for tracking trends and growth rates in your data.

### Step parameters

* `Date column` **column(string)\***: the date column (must be of type `date`) that will be used as a reference for the computation
* `Value column` **column(string)\***: the value column that you want to compute the evolution of
* `Compute evolution vs` **string\["last year", "last month", "last week", "last day"]\***: whether you want to compute evolution versus last year, last month, last week or last day
* `Compute evolution in` **string\["absolute value", "percentage"]column(string)\***: Choose `absolute value` for absolute difference or `percentage` for percentage change.
* `Group by`(optional) **column(array)**: Use this option if you need to perform the evolution computation by group of rows. You should do so to make sure every date is unique inside each and every group. See examples 2 and 3 for a concrete illustration.
* `New column name` **string (optional)**: Use this option if you want to give a custom name to the output column. By default, it will be your original column name suffixed with either `_EVOL_ABS` or `_EVOL_PCT` depending on the kind of evolution that you chose.

### Example

**Input**

<figure><img src="/files/Ujii4djLvgF8vEr7UOlV" alt=""><figcaption><p>Compute - Compute evolution input</p></figcaption></figure>

**Configuration**

```json
{
    "date_col": "date",
    "value_col": "sales",
    "evolution_type": "last year",
    "evolution_format": "abs", 
    "index_columns": ["region", "product"]
    "new_column_name": ""
}
```

**Output**

<figure><img src="/files/RESIESKOJOSGu1AqGZNY" alt=""><figcaption><p>Compute - Compute evolution output</p></figcaption></figure>


# Cumulated sum

The cumulated sum step enables you to calculate running totals based on a reference column, typically dates. It's valuable for understanding accumulation over time and visualizing progressive totals.

### Step parameters

* `Columns to cumulate` **tuples({column; string})\***: the value columns you want to compute the cumulated sum of, and for each one the name of the result column (by default it will be your original column name suffixed by `_CUMSUM`).
* `Reference column to sort (usually dates)`**column(string)**: the column that will be used to order rows in ascending order. Usually you will use a date column here (to compute a year-to-date result for exemple).
* `Group By` **(string)** **(optional)**: if you need to apply the cumulated sum computation by group of rows, you may specify here the columns to be used to constitute groups.

### Example

**Input**

<figure><img src="/files/UGKYXEJoQznoVKz1qANw" alt=""><figcaption><p>Compute - Cumulated sum input</p></figcaption></figure>

**Configuration**

```json
{
    "cumul_columns": [
        {"sales": "cumulative_sales"},
        {"quantity": "cumulative_quantity"}
    ],
    "ref_column": "sales",
    "group_by": []
}
```

**Output**

<figure><img src="/files/PkjTIMLu745a1sh9Cn7m" alt=""><figcaption><p>Compute - Cumulated sum output</p></figcaption></figure>


# Percentage of total

The Percentage of total step helps you calculate the relative proportion of each value within a total, optionally within specified groups. It's essential for understanding the composition of your data and relative contributions.

### Step parameters

* `Value Column` **column (string)\***: The column that will be used for the computation.
* `Group By` **column (string)\* (optional)**: if you want the computation to be segmented by group, you can select one or several columns that will be used to constitute unique groups.
* `New column name` **string (optional)**: if you want to give a custom name to the column of results to be created. By default, it will be your original column name suffixed by `_PCT`.

### Example

**Input**

<figure><img src="/files/LONNaY2RB5QlhmKl6Qoq" alt=""><figcaption><p>Compute - percentage of total input</p></figcaption></figure>

**Configuration**

```json
{
    "column": "sales",
    "group_by": [],
    "new_column_name": "sales_percentage"
}
```

**Output**

<figure><img src="/files/VlbR0kKg8KIJthxkurBB" alt=""><figcaption><p>Compute - percentage of total output</p></figcaption></figure>


# Rank

The Rank step allows you to order your data based on specific values, with options for different ranking methods. This is useful for identifying top performers, prioritizing items, or creating ordered lists within your dashboard.

### Steps parameters

* `Value column to rank` **column(string)\***: the value column that will be ordered to determine rank
* `Sort order` **dropdown\["asc", "desc"]\***: how to order the value column to determine ranking. Either `asc`(ending) or `desc`(ending), `desc` by default
* `Ranking method` **dropdown\["standard", "dense"]\***: either `standard` or `dense`, as explained above
* `Group ranking by` **column(array) (optional)**: if you need to apply the ranking computation by group of rows, you may specify here the columns to be used to constitute groups (see example 2 below)
* `New column name`**(string) (optional)**: if you want to give a custom name to the output column to be created (by default it will be your original column name suffixed by `_RANK`).

### Example

**Input**

<figure><img src="/files/HewL4JrDZKDbUPoKYdTY" alt=""><figcaption><p>Compute - Rank input</p></figcaption></figure>

**Configuration**

```json
{
    "value_col": "sales",
    "order": "standard",
    "method": "asc",
    "group_by": [],
    "new_column_name": "sales_rank_asc"
}
```

**Output**

<figure><img src="/files/c5rdmOoHPBoZuAiBsU2B" alt=""><figcaption><p>Compute - Rank output</p></figcaption></figure>

{% hint style="info" %}
There are 2 ranking methods available, that you will understand easily through those examples:

* `standard`: input = \[10, 20, 20, 20, 25, 25, 30] => ranking = \[1, 2, 2, 2, 5, 5, 6]
* `dense`: input = \[10, 20, 20, 20, 25, 25, 30] => ranking = \[1, 2, 2, 2, 3, 3, 4]

(The `dense` method is basically the same as the `standard` method, but rank always increases by 1 at most).
{% endhint %}


# Moving average

This Moving average step smooths out short-term fluctuations in your data by calculating averages over a specified window of time or number of rows. It's particularly helpful for identifying trends in time-series data.

### Step parameters

* `Value column` **column(string)**: the value column used as the basis for the moving average computation
* `Reference column to sort (usually dates)` **column(string)**: the column used to sort rows
* `Moving window (in number of rows)` **int\***: the number of rows included in the moving window
* `Group by` **(optional)**: if you want perform the computation by group of rows (see example 2 below)
* `New column name` **(optional)**: if you want to specify a custom column name (by default, it will be your original value column name suffixed with `_MOVING_AVG`)

### Example

**Input**

<figure><img src="/files/cYFRoSwip2RNwY5eJGpp" alt=""><figcaption><p>Compute - Moving average input</p></figcaption></figure>

**Configuration**

```json
{
    "value_col": "sales",
    "column_to_sort": "date",
    "moving_window": 3,
    "group_by": [],
    "new_column_name": "sales_moving_avg"
}
```

**Output**

<figure><img src="/files/LyIZmjHGsZ2VFPJ3vOqO" alt=""><figcaption><p>Compute - Moving average output</p></figcaption></figure>


# Compute statistics

The Compute statistics step provides key statistical measures for your numeric data, such as median and quintiles. It's crucial for understanding the distribution of your data and identifying important thresholds or benchmarks. These operations collectively enable you to transform raw data into meaningful insights, prepare your dataset for various types of visualizations, and create a more comprehensive and informative dashboard.

### Step parameters

* `Column` **column(string)\***: Select the column to compute statistics on.
* `Group By Columns` **string(array)** **(optional)**: Select columns to group the data by before calculating statistics.
* `Basic statistics` checkbox\["count", "average", "min", "max", "median"]: Choose a basic statistic to compute.
* Advanced statistics: checkbox\["standard deviation", "variance", "first quartile", "last quartile", "first decile", "last decile", "first centile", "last centile"]: Choose an advanced statistic to compute
* `Custom Quantiles` checkbox: Specify custom quantiles to calculate, including label, nth value, and order.

### Example

**Input**

<figure><img src="/files/wpZFlkb4MMPjiSGG2UFc" alt=""><figcaption><p>Compute - Compute statistic input</p></figcaption></figure>

**Configuration**

```json
{
    "column": "sales",
    "group_by": [],
    "basic_statistics": ["count", "max", "min", "average"],
    "adv_statistics": ["standard_deviation"]
    "cust_quant": []
}
```

**Output**

<figure><img src="/files/MjTWzQe7kNn2avlZnO7a" alt=""><figcaption><p>Compute- Compute statistic output</p></figcaption></figure>




---

[Next Page](/llms-full.txt/1)