You can use the query connector to create a relational database table from any SQL query against any relational database.

The source database and destination database can be Mitto's built in PostgreSQL database or any other remote database.

This is the preferred way to mirror data from other databases.

Caveats:

The database drivers must be installed (most drivers are installed by default on every Mitto instance - PostgreSQL, MSSQL, Redshift, etc).

Mitto needs network access to the source and destination databases. This typically means whitelisting IP addresses on potentially both sides (customer and Mitto).

Job Instructions:

Start creating the job:

Click "+ Add Job".

Select "Query".

Write the database query:

You will see a screen like this:

The Database URL is the connection string of the source database.

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

The structure of database connection strings is as follows:

<driver>://<username>:<password>@<server>:<port>/<database>

Query (database connector) - Database URLs

For example:

postgresql://admin:password123@localhost:5432/analytics

Type the query you want to create a table from in the SQL area.

It is a good practice to fully qualify your tables by including the schema of the table.

So for example if you want to mirror an entire table:

SELECT *
FROM <schema>.<table>;

Setup the resulting table:

Hit Next and Mitto will determine the structure of the resulting table from the query:

You can enable Upsert by checking the Upsert box.

Select the Primary key(s) and Last modified column for the table from the dropdown.

Specify the output:

Hit Next and specify the output destination for the query results.

Hit Save. 

Go run the job!