External datasets
A live PostgreSQL table, read in place.
An external dataset is a table in a PostgreSQL database that Beetl queries where it sits, with nothing copied. Each query reads the live table, and Beetl pushes filters and column selection down to PostgreSQL where it can. Beetl only reads from the database and never writes to it.
A TenantAdmin adds the database once as an external source, and then any user can register its tables as datasets. PostgreSQL is the only supported database.
Before you start
- The database must be reachable from the public internet on the host and port you enter. Databases on a private or internal network address cannot be used. If yours is not reachable, export from it into a File Set instead.
- Beetl connects over TLS and verifies the server certificate against the host name. The server needs a valid certificate for that host name, issued by a publicly trusted certificate authority.
- Create a PostgreSQL role for Beetl and grant it
SELECTon only the tables you want to register. Beetl lists only the tables that role can read, and no query can reach beyond its grants.
Add the database
Only a TenantAdmin can add a source. Other users who open the form are asked to contact one.
On Home, right-click the canvas, choose New connection, and pick Postgres under Data sources.
Enter a connection name, the host, the port (5432 by default) and the database, then the username and password of the role you created.
Choose Add. Beetl tests read access before saving. If the test fails, nothing is saved and the form shows the reason.
The source appears as a node on Home and in the Data sources list on the Connections page
(/web/connections). There a TenantAdmin can manage it:
| Action | What it does |
|---|---|
| Test | Checks read access again with the stored credentials. |
| Replace credentials | Saves a new username and password, after testing them. |
| Remove | Removes the source from Beetl. This is refused while any dataset still uses it, and the error names those datasets. Delete them first. |
A source's host, port and database cannot be edited. For a different server, add a new source.
Register a table
Any user can register a table from a source.
On Home, click the source's node, or right-click it and choose Register table. You can also choose Register data set on the canvas and pick the source there.
Find the table. Tables are grouped by schema, and you can search them. The list shows the tables, views and materialized views that the source's role can read, outside PostgreSQL's system schemas.
Check the name. Beetl suggests the table name in lowercase. You write this name in SQL, and it follows the usual naming rules.
Choose Register data set. Beetl reads the table's columns and adds the dataset to Home.
Each table can be registered once per source.
Using an external dataset
You can query an external dataset in Query, use it as a pipeline source, and read it in dashboards. It is read-only everywhere:
- It cannot be a pipeline destination, and it never triggers a pipeline run.
- In a query result built from it, drill-down to the records behind a cell is not available.
Every query runs against your database, so heavy or frequent queries add load there. A query stops with a byte-limit error once it has pulled 64 MiB from external tables; pipeline runs read their full input. For a large table, filter in the query so fewer rows leave PostgreSQL, or copy what you need into a dataset with a pipeline and query that copy.
Renaming and deleting
Any user can delete an external dataset. Deleting removes it from Beetl and never touches the table in your database. To change its name, contact Beetl; see Finding, renaming and deleting.
Related
Last updated on