Database URLs

What is a Database URL?

The Database URL is the connection string of a database.

By default in Zuar Runner, it is the local database (postgresql://db/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

DB2db2+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. Zuar Runner’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 Zuar Runner UI.

Zuar Runner 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!

Zuar Runner 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 Zuar Runner 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 Zuar Runner access to the source database.

To get the IP address of your Zuar Runner 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:

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