This walkthrough takes you from an empty Lake to a loaded table. Use it the first time you connect a database. The same steps apply to the other SQL databases on the same form.
What you need
The host, port, database, username and password of the database, or a connection string that holds them.
The Add/Modify Lake Objects permission to add a source and import tables, and Execute/Abort Lake Objects to run a load. Who can do what in the Lake explains what you see without them.
Connect the database
Open Lake in the sidebar and click Add datasource in the header of the Lake screen.
Pick your database, for example Postgres. To find it faster, type in Search sources... or choose Popular. Other source types are listed in Supported connectors.
On General, type a Name. The Code fills in from the name automatically; edit it if you want a different one. Optionally choose a Folder. Click Next.
On Connection, fill in Host, Port and Database, then enter Username and Password. Turn on SSL/TLS if the server requires it.
Optionally, click Test connection to check the details. Click Next.
On Datetime, keep Datetime values as Keep as written (wall clock) unless the source writes its times in another timezone. Click Add.
After you click Add, you see the message Connection added successfully. The source panel then opens for the new source.
Tip
You do not have to fill in the fields one by one. Click Paste connection string at the top of the Address section of Connection, paste the string and click Fill. The first question at the end of this page shows how.
The Port field is filled in with the default for your database type: 5432 for Postgres, 3306 for MySQL and MariaDB, 1433 for MSSQL and Azure SQL Database, 1521 for Oracle, 8123 for Clickhouse and 5439 for Amazon Redshift.
On the Datetime step, two settings decide how datetime values arrive:
Setting
What it does
Default
Datetime values
Keeps datetime values exactly as the source writes them. The second option converts them from the source timezone into platform time as they load.
Keep as written (wall clock)
Timezone
The timezone the source writes its datetime values in. Leave it empty if the source uses the same timezone as the platform.
Not set
Warning
For a Postgres source, the Database field cannot be changed after the source is created. Check the database name before you click Add.
Import a table
In the source panel, expand the database and the schema, and tick the table. Click Import. Its tooltip reads Import 1 table.
Wait for the message Import completed successfully. The table appears in the Lake tree under its source.
Set up the table
Double-click the table in the Lake tree, or right-click it and choose Edit.
On Table > General, set Sync Type and Workflow Name. Click Apply.
If the table has no primary key, open Table > Columns, tick PK on the column that identifies each row, and click Apply.
Setting
What it does
Default
Sync Type
Sets how each load writes the table into the Lake. See Choose a sync type.
Full Reload (Truncate Insert)
Synchronize
When on, the table load is included in its workflow.
On
Deploy and load the table
Click Deploy in the banner at the top of the table. The banner reads This table is ready to deploy. Deploying creates the table in the Lake with its current columns. You need at least one primary key column to deploy. If you have none, the banner reads "Primary key required. Define a primary key column before this table can be deployed." Go back to step 11.
Open the Settings tab and click Synchronize. In the Synchronize Table dialog, pick the date, then click Synchronize.
Watch the Sync Log until the Status shows Finished. The Processed Rows column shows how many rows the load handled. If the Rows card has not updated, click Refresh.
Your table is now loaded. To query it, open the Editor and follow Run your first query.
Things to know
Test connection is optional. Until you run it, the card shows Not tested.
After you save a password, the form does not show it again. It shows Stored password with a Replace button.
You cannot run Synchronize on a table until the table is deployed.
Yes. On Connection, click Paste connection string, paste a JDBC URL, a URI or host:port/database, and click Fill. The form fills in Host, Port and Database. A password in the string is never read, so enter Password yourself.
Why did the source panel not open after I clicked Add?
The panel waits while the source is still processing or installing, and opens when the source is ready. Sources that need a transformation step do not open the panel at all.
Which sync type should I start with?
Start with Full Reload (Truncate Insert), which is the default. Each load empties the Lake table and loads the whole source table again. Choose a sync type describes the others.
My first load does nothing. What did I miss?
Check that the table is deployed and that it belongs to a workflow. Synchronize is refused until you click Deploy. To set the workflow, open Table > General, choose Workflow Name and click Apply. A table with Synchronize off is not included in its workflow's loads. See Workflows in the Lake.
How do I know the load finished?
Open the Settings tab and read the Sync Log. The load is complete when its Status shows Finished. The Processed Rows column shows how many rows it handled.
Can I query the table right away?
Yes, once the Sync Log shows Finished. Open the Editor and query the table. Run your first query shows how.
Can I go back a step in the connection form?
Yes. Click Back, or click a step you have already visited in the stepper.