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.

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!

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.

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
}