From a source to your first query

This tutorial takes you from a database you can reach to rows you can query in the Editor. Follow it the first time you bring data into DataMind OS. Each step links to the page with the full detail.

Before you start

You need:

• The connection details of your database: host, port, username, password and the database name. Ask your database administrator for them.
• These permissions. Your profile lists the permissions you have. If a menu item or button is missing, ask your administrator. See Who can do what in the Lake.

Connect a database

1. In the sidebar, click Lake, then click Add datasource.
2. In the source picker, type your database type in Search sources..., then select it.
3. On the General step, enter a Name. DataMind OS fills in Code from the name. The Code is used as a prefix when forming table names. You can change it, but it must contain only letters and numbers, and at least two of them. If you use folders, choose a Folder. Click Next.
4. On the Connection step, enter Host, Port, Username, Password and the database name. Turn on SSL/TLS if your database requires an encrypted connection. Some database types show extra fields. The connection guide explains them.
5. Click Test connection. If the test passes, the card shows "Connection test passed". If it fails, the card shows Connection test failed and the reason. Correct the details and test again.
6. Click Next. On the Datetime step, leave Timezone empty and keep Datetime values on "Keep as written (wall clock)", unless your source stores times in another timezone.
7. Click Add. The message "Connection added successfully" appears, the source shows in the Lake tree, and its source panel opens with the databases, schemas and tables of your source.

Adding a source does not import its tables. The next steps do that.

Import and deploy a table

8. In the source panel, tick the table you need, or click Check all, then click Import. The message "Import completed successfully." confirms it. If you closed the panel, click Import tables next to the source in the Lake tree to open it again.
9. In the Lake tree, double-click the imported table. A banner reads "This table is ready to deploy." Before you deploy, check that the key columns are marked PK in Columns. A table without a primary key cannot be deployed, and the banner reads "Primary key required. Define a primary key column before this table can be deployed." instead.
10. Click Deploy in the banner. The button reads Deploying while it runs. Until the table is deployed, its Settings tab shows Table Not Deployed. Once it is deployed, the banner disappears, and Settings and Preview become available.

Load the table

11. In the table, open Table > General and choose a Sync Type under Synchronization. For a first load, Full Reload (Truncate Insert) is the simplest choice: it needs no date column and replaces the table's data on every load. The incremental types need a date or time column. Choose a sync type explains them. If Apply shows at the bottom, click it to save your change.

12. Open the Settings tab and click Synchronize. Synchronize Table opens with the current date and time. Keep them and click Synchronize. The message "Synchronization started" confirms the load, and a new row appears in Sync Log.
13. Wait until the row's Status reads Finished. To update the log by hand, click the refresh button with the tooltip Refresh data. If Status reads Error, read Error Stack. If it reads Skipped, read Note. In the Storage card, click Refresh to see Rows filled.

Query it in the Editor

14. In the sidebar, click Editor.
15. Open the Database Explorer (Alt+1 toggles it). Check that the Role control at the top shows a role that may read your table. If the table is not listed yet, click Refresh Schema.
16. Expand the database whose name ends in _Lake, point at your table and click Insert into editor. The database name and the quoted table name appear in the console. Inserting the name is safer than typing it.
17. Type SELECT * FROM before the table name, and LIMIT 10 after it.
18. Press Ctrl+Enter. The statement at the cursor is highlighted. Press Enter, or Ctrl+Enter again, to run it. You can also select the statement and press Ctrl+Enter, which runs your selection straight away.
19. The rows appear in the Results tab of the results panel.

You now have data loaded into the Lake and a result from it in the Editor. The Editor shows only the databases and tables your role can read. See SQL dialect and data scope for what that means for your queries. To shape this table for reports, continue with Model your data in the Warehouse.

Load every table with the workflow

Adding a source also creates workflows named after the source. A table with a sync type such as Full Reload (Truncate Insert) runs in the one with (Standard) after the name. Workflow Name, under Synchronization on the table's General step, shows the table's workflow. Run the workflow when you want to load all its deployed tables in one go. You need Execute Lake Workflow Objects.

1. In the sidebar, click Workflows.
2. Hover over the workflow card and click the play button with the tooltip Run.
3. In Run Workflow, keep the date and time, and click Start.

Each table's Sync Log gets a new row. The play button is greyed out until at least one table of the workflow is deployed.

Questions, answered

Why does Test connection fail?

Check Host, Port, Username, Password and SSL/TLS, and make sure the database accepts connections from DataMind OS. The Connection test failed card and its toast show the message from the database. For more detail, see Connect a database.

I clicked Add, but no tables appear under the source. Why?

Adding a source does not import tables. Tick the tables you need in the source panel, which opens after Add, and click Import. If the panel is closed, click Import tables next to the source.

My source says "Setup incomplete". What do I do?

Click Continue on the source to finish its setup. Then import its tables.

Why does Deploy say "Selected tables are missing primary key"?

In the Deploy confirmation, the button reads "Selected tables are missing primary key" and stays disabled. Mark the key columns as PK in Columns, then deploy again.

I deployed the table, but it is empty. Why?

Deploying creates the table. It loads data only when you click Synchronize or when a workflow runs.

How do I know the load has finished?

Watch the Sync Log in the table's Settings tab, and click the refresh button to update it. When the newest row no longer shows a running status, the run has ended. Check Status and Error Stack to see whether it succeeded. For more on runs, see Run, stop and troubleshoot loads.

My query says the table does not exist. What should I check?

Check four things:

• The table is deployed. Only deployed tables exist for SQL.
• The Database Explorer is up to date. Click Refresh Schema, because the explorer keeps its list for a few minutes.
• The role in Role may read the table. The admin role always can. Another role needs to be in the table's Permission Groups, on its General step. See Configure a table.
• The name includes its database and quotes. Use Insert into editor instead of typing it.