SQL

Modified on Thu, 17 Aug, 2023 at 5:38 PM

Description

Connect to a SQL database and import the data into your notebook directly.

Application

If your data lives in a SQL database a basic but lengthy approach to getting your data to Monolith would be:

Export and download the data from SQL database as .csv file.
Upload to Monolith via File Manager.
Import into a notebook via the Tabular importer.

The SQL importer removes the export, download, and upload steps in the process above. You can directly add a SQL importer in your notebook and pull the data over into it.

How to use

Prerequisites

Make sure the Monolith servers can connect to the database server. Very likely, a firewall rule needs to be set in place to enable this communication.
You need to find out which type of SQL database you have (MySQL, MSSQL, PostgreSQL). This needs to be taken into account in your config file.

Create a config file

The SQL importer needs to be configured to connect to the database.

Create a config file with file ending .sql-database as described below.
Upload the config file via the File Manager to Monolith.

MSSQL config file

[MyDatabase]
url=mssql+pymssql://{user}:{pass}@{hostname}:{port}/{db_name}

MySQL config file

[MyDatabase]
url=mysql://{user}:{pass}@{hostname}:{port}/{db_name}

PostgreSQL config file

[MyDatabase]
url=postgresql+psycopg2://{user}:{pass}@{hostname}:{port}/{db_name}

Config file elements

`{user}`	Username to access the database. Make sure to use a read-only user to access the database!
`{pass}`	The password to access the database. Regarding concerns of exposing the password on the Monolith platform: It is impossible to access and view the password of this file! The File Manager offers no possibility to view or download files. And within notebooks there is no manipulator which could load and reveal the content of this file. The SQL importer itself never shows the information within the config file.
`{hostname}`	The name/address of the server on which the database is hosted.
`{port}`	The port on which the database listens to requests.
`{db_name}`	The name of the database.

Setup SQL importer

Finally go to your notebook and create a SQL importer to import the data.

Use Add/Edit datasets to select the SQL config file.
You need to build the SQL Query which extracts the data you want.
Specify a Name under which the dataset is stored.
Click Apply to load the data.