What is a Database URL?

The Database URL is the connection string of a database.

By default in Mitto it is the local database (postgresql://localhost/analytics).

The structure of database connection strings is as follows:

{driver}://{username}:{password}@{server}:{port}/{database}?{parameters}

Common database drivers

DBMS Driver
Amazon Redshift redshift+psycopg2
Google BigQuery bigquery
IBM DB2 db2+ibm_db
MariaDB mysql+pymysql
MySQL mysql+pymysql
PostgreSQL postgresql
SQL Server mssql+pyodbc
Snowflake snowflake

Parameters

DBMS Parameter
SQL Server ?driver=ODBC+Driver+17+for+SQL+Server
Snowflake ?warehouse={warehouse}

Encrypted Credentials

In most database url strings, you are required to pass a username and password through the string. Mitto's Credential manager is a way to encrypt and store credentials to that they don't need to be explicitly included in the database url string.

To use saved credentials:

Navigate to the Credential manager via the Mitto UI.

Mitto Credential Manager image

Select Add Credentials and from the dropdown choose Basic Credential type. Give the credential an easy to use name like "mysql" or "psql". Fill in the username and password input with the database credentials. SAVE!

Mitto Basic Credential

Next, create a Query job from the Add Job menu. On the Connect screen, deselect Use default database URI which should expose and input for Database URL. Input the Database URL string associated with the type of database you are connecting to and use the Credential Name created in the previous step instead including them in the string. For example, if you are trying to query a PostgreSQL Database, the string would look like postgresql://psql@<hostname of database>/<database name>

Check the Use credentials box and it should expose a dropdown selector. This is where you will selected the saved credential from the previous step.

Lastly, write your desired query in the query input window.

Connect to database

NOTE: In order for Mitto to access most databases, it will likely need to have access via it's IP address or some other security mechanism implemented by your organization. Contact IT to determine the best way to allow Mitto access to the source database.

To get the IP address of your Mitto instance, use your command line and run nslookup <mitto hostname> . Based on the example above you would run nslookup zuar.net

Once you've completed the Wizard for the Query job. The input section of the JSON configuration will look something like: 

  input: {
    use: query.io#QueryInput
    query: SELECT * FROM analytics.demo.global_superstore_orders_2016;
    dbo: postgresql://psql@zuar.net/analytics
    stream_results: 50000
    credentials: psql
  }