How to connect a database to DataLens
Connect one of six supported engines, browse what is actually in it, preview before you commit, and load the tables you need as datasets.
What you will do
You will create a database connection, list the tables the credentials can see, preview rows from any of them, and load the ones you want as DataLens datasets.
You can load a whole table or write a SQL query and load its result, so a wide table does not have to arrive whole.
When this is useful
- You need to understand the structure of a database you have inherited and did not design.
- You want to profile production data for quality or PII without exporting extracts by hand.
- You are designing a model and need the real source schema rather than a diagram of it.
- You want to join operational data with a spreadsheet somebody sent you.
Before you start
- Connection details — Host, port, database name, username and password for most engines. BigQuery takes a Project ID, Dataset and Service Account JSON; Snowflake takes an Account, Warehouse, Database, username and password.
- Network access — The database must be reachable from where DataLens runs. A database behind a firewall or on a private network needs that access opened first.
- A read-only user, ideally — DataLens reads from your database. A user with read permission on the tables you care about is enough, and is the safer credential to use.
Steps
Open Data → Connections → Database
Select Data in the top navigation, then Connections, then the Database tab.
A row of engine choices: PostgreSQL, MySQL, SQL Server, Redshift, BigQuery and Snowflake.
Pick your engine
Select the engine you are connecting to. The form below changes to the fields that engine actually needs, and the default port fills in — 5432 for PostgreSQL, 3306 for MySQL, 1433 for SQL Server, 5439 for Redshift.
BigQuery and Snowflake have no port; they ask for the fields their own APIs use instead.
A form showing exactly the fields your chosen engine requires, and nothing else.
Fill in the connection and name it
Enter the host, port, database, username and password — or the equivalents for BigQuery and Snowflake.
Give the connection a name in Connection Name. This is what you will pick from later, so name it for the system rather than the server: "Orders production", not "db-3".
Test the connection
Select Test Connection. DataLens attempts a real connection with the credentials you entered and reports whether it succeeded.
Test first. A failure here is a credential, host or firewall problem, and it is far easier to diagnose on its own than in the middle of loading a table.
A success message, or a specific failure telling you what went wrong.
Browse the tables
Once connected, DataLens lists the tables visible to your credentials. Use the filter box to narrow a long list.
Select Preview on any table to see its columns and a sample of rows without loading it. This is how you explore an unfamiliar database — you can look through every table before importing anything.
A table list with a Preview action on each row.
Load a table, or a query
In Table mode, tick the tables you want and load them. Each becomes a DataLens dataset.
Switch to SQL mode instead if you want a subset — a join, a filtered slice, an aggregate. Write the query, and its result loads as a dataset.
SQL mode is the answer for a table too wide or too large to bring across whole.
The selected tables appear under Data → Datasets with row and column counts.
Save the connection for reuse
Select Save Connection. The connection is stored under My Connections so you can come back and load more tables later without re-entering credentials.
The connection listed under My Connections.
What happens next
Loaded tables behave exactly like uploaded files. Profile them in Govern, clean them in Prepare, or take them into Model to build a source model of the database you just connected.
If you connected the database to understand its structure, the Source Model tab in Model is the next stop — it shows tables and their relationships as a diagram.
Example
A solution architect joins a project with a PostgreSQL database nobody has documented. They create a connection, use Preview to walk through forty tables without importing anything, load the eight that matter, and build a source model from those. What used to be a week of reading DDL becomes an afternoon.
Tips
- Always use Test Connection before trying to browse. It separates a credential problem from a permissions problem.
- Use a read-only database user. DataLens only needs to read, and a read-only credential limits what a mistake can cost.
- Preview before loading. A table with a hundred columns is usually not the table you want whole — SQL mode will serve you better.
- Name connections after the system, not the server. You are picking from this list months later.
Limitations
- Six engines are supported: PostgreSQL, MySQL, SQL Server, Redshift, BigQuery and Snowflake. Others are not available on this tab.
- DataLens reads from your database. It does not write back to it, and loading a table does not keep it in sync — for continuous updates, use CDC Sync.
- The database must be reachable from where DataLens runs. Databases on private networks need network access arranged separately.
- Table listings show what your credentials can see. A table missing from the list is usually a permissions issue on the database, not a DataLens one.
Related how-to guides
Explore a source model
See the tables, columns and relationships of a system you did not design — as a diagram you can read.
Set up CDC sync
Keep a dataset current by capturing changes from a source table on a schedule, rather than reloading it whole.
Profile a dataset and find PII
See what is really in each column, and find the personal data before it ends up somewhere it should not.
Map source to target
Connect source columns to target columns, see what is unmapped, and export a source-to-target mapping.
Related questions
More of these on the DataLens FAQ page.
Which databases can DataLens connect to?
PostgreSQL, MySQL, SQL Server, Redshift, BigQuery and Snowflake. Each has its own connection form with the fields that engine requires.
Can DataLens help me understand a database structure I did not design?
Yes. After connecting you can list every table your credentials can see and preview columns and sample rows without importing anything. Loading the tables you care about then lets you build a source model showing tables and their relationships as a diagram.
Does DataLens write back to my database?
No. Database connections are read-only in effect: DataLens reads tables or query results and stores the result as a dataset. Nothing is written back to the source system.
Can I load only part of a large table?
Yes. Switch from Table mode to SQL mode and write a query — a filter, a join or an aggregate. The query result loads as the dataset instead of the whole table.
Try this in DataLens
DataLens is in private beta. Request access and work through this guide on your own data.
Request beta access