DataMosaicDocs · Connecting to data
SQL databases
PostgreSQL, MySQL, MariaDB, CockroachDB, Redshift, Aurora MySQL, Greenplum, ClickHouse and Vertica.
These nine drivers share one connection form. Microsoft SQL Server, Oracle, SQLite and Snowflake each have their own — see the pages beside this one.
| Driver | Default port |
|---|---|
| PostgreSQL | 5432 |
| MySQL | 3306 |
| MariaDB | 3306 |
| CockroachDB | 26257 |
| Amazon Redshift | 5439 |
| Amazon Aurora MySQL | 3306 |
| Greenplum | 5432 |
| ClickHouse | 8123 |
| Vertica | 5433 |
Several of these run on another product's driver — MariaDB on MySQL's, Redshift and Greenplum on PostgreSQL's — and the add menu says so on the entry.
#Connection
| Field | Default | Notes |
|---|---|---|
| Host | — | |
| Port | The driver's | 1–65535 |
| Database | — | |
| Table | — | Optional. See below |
| Username | — | |
| Password | — | Encrypted at rest, never sent back to a browser |
| Use TLS | On | |
| Trust server certificate | Off | Only shown when TLS is on |
The username should be a login that can only read. If you need somebody else to create one, The read-only login is a page you can send them: it carries the grants for each engine, and what Mosaic will and will not do with them.
#The Table field
Optional, and it does two things.
Name a table and Mosaic reads that one. If you also write no query of your own, it runs a generated one, and shows you exactly what it will run: "With no query of your own, this source runs: SELECT FROM SALES LIMIT 50000"*.
It must be a plain table name, optionally as schema.table. Anything else is refused: "A table name, optionally as schema.table. Nothing else."
#Query
The SELECT this source runs. Mosaic only ever reads.
That is enforced rather than promised: a statement that is not a read is refused as you type and the editor reverts, under the heading "This is not a read". The database is also asked to refuse writes on the session Mosaic opens, and a read-only login makes it certain — see Read-only access for all three layers and the login to create.
#Binds
Bind parameters, referenced in the query as :name for Oracle or @name for SQL Server. Each has a name, a type — string, number, date or boolean — and a value.
For values that should vary per run, prefer a parameter with {{name}}, which works in every driver and in every field.
#Test connection
Saves first if there are unsaved changes, then opens a session and runs the cheapest statement that proves it worked.
- Success: "Connected in 340 ms"
- With a collection where some members did not answer: "Connected in 340 ms — 2 of 20 hosts unreachable: db-milano, db-torino"
- Failure: the database's own error text
#Settings
See Timeouts and row limits. The two that matter are a 30 second timeout and a 50 000 row ceiling, both adjustable.
#Running across many machines
A source on any of these drivers can run the same query against a list of machines. See Running across many machines.