BI Connector (deprecated)

The BI Connector is a PostgreSQL-compatible interface to data in Tinybird. You can connect many of your favorite tools to Tinybird, such as Tableau, Apache SuperSet, or Grafana.

All Data Sources and published Pipes created in Tinybird are available as standard PostgreSQL tables. You can query using standard PostgreSQL syntax and use any tool that implements the PostgreSQL protocol.

Tinybird uses TLS 1.2 and higher for BI Connector connections.

Use the ClickHouse® interface to connect BI tools to Tinybird. It is available by default and includes dedicated setup guides.

Compatibility

The BI Connector has been successfully tested on the following solutions:

  • DBeaver
  • PowerBI
  • Tableau
  • Grafana
  • Metabase
  • Superset
  • Klipfolio
  • Anodot

Connect to Tinybird

Connect and access your database as you would with regular PostgreSQL. Use your command-line tool, any graphical interface such as DBeaver, or any other SQL client such as your favorite BI tool.

For example:

psql -U <user> --host bi.us-east.tinybird.co --port 5432 -d <database>

After connecting to your database, list all available views in the public schema to see all available Data Sources and API Endpoints. If you're using the PostgreSQL command line, type \dv to get the list of views.

If you list all the tables, a table per Data Source appears. The name of the table is the identifier of the Data Source. Don't use those IDs. Instead, find your tables with their user-friendly names in the views space.

Best practices

1. Avoid queries on exposed Pipes

Both your Pipes and Data Sources are exposed in the BI Connector as tables. Query against Data Sources tables only, and avoid using any Pipes tables. In general, queries on Data Sources tables are much faster and easier to optimize.

2. Use Materialized Views

With Tinybird you can build Pipes that materialize the result as a Materialized View. Materialized Views appear in the BI Connector as tables.

Instead of querying raw data via the BI Connector, build transformations, filters, aggregations, or joins in standard Tinybird Pipes to create a Materialized View. Use the BI Connector to read the pre-prepared Materialized View.

For example, if your dashboard shows events aggregated by day, create a Materialized View with pre-aggregated data by day, and have your BI chart read this.

3. Use dedicated Materialized Views for different charts

Build Materialized Views specifically for each chart or widget to avoid additional transformation operations.

4. Use efficient sorting keys

Data stored in Tinybird is sorted using a sorting key. Use the appropriate sorting keys for your data according to the needs of your dashboard to efficiently find relevant data.

For example, if your dashboard filters by time, include time in the table's sorting key.

5. Avoid column mappings

Avoid using column mappings, as filtering by a mapped column can prevent the index (sorting key) from being used.

6. Avoid JOINs

Avoid JOINs over the BI Connector. Perform JOINs inside a Pipe, materialize the result as a Materialized View, and query the already-JOINed data.

Limits

The following limits apply:

  • Tinybird query timeout is 10 seconds.
  • PostgreSQL statement timeout is 5 minutes.
  • Resources are limited to 1 thread per query.
  • Concurrent queries are limited to 100 per cluster.
  • Queries over the BI Connector are only allocated 1 CPU core per query. This only applies to the BI Connector, not to standard Tinybird Pipes.
  • Single queries mustn't return more than 1 GB of data at a time.
  • The total amount of data returned by all queries executed within a 5-minute period must not exceed 6 GB. This is to prevent overloading the system with too much data in a short period of time.

Some settings can be adjusted on request. Contact Tinybird support to discuss if your use case can be supported.

Limitations

The following limitations apply:

  • You can't use parameters in your endpoints. However, you can still filter your data using SQL WHERE clauses.
  • Some widgets of BI tools create SQL queries that aren't compatible with the BI connector.
  • Some JOIN operations run in Postgres and not Tinybird, which means they might run much, much more slowly. Avoid JOINs over the BI Connector.
  • Sometimes, PostgreSQL might rewrite the query so that filters might end up not using sorting keys. Sorting keys allow you to run faster queries, and therefore those resulting queries might be slower and more expensive since they would require full table scans.
  • Querying large datasets can result in a timeout. Large generally means many millions of rows, but this is also affected by how many columns and how large the values are.

Next steps

Updated