Mitto can export SQL queries to delimited (e.g. comma, tab, pipe, etc) flat files. You can use any single character delimiter.

Here's an example SQL database table we will export to CSV.

Sample data for toCSV

Create a Generic Job

On the bottom left of the screen click on the +Add Job button. From the wizard select Generic and type in a title. Edit the following JSON for your needs:

{
    "input": {
        "dbo": "postgresql://localhost/analytics",
        "query": [
            "SELECT * from excel.tennis_products;"
        ],
        "use": "query.io#QueryInput"
    },
    "output": {
        "delimiter": ",",
        "path": "/var/mitto/data/output_{year}_{month}_{day}.csv",
        "use": "call:mitto.iov2#tocsv"
    },
    "steps": [
        {
            "transforms": [
                {
                    "rename_columns": false,
                    "use": "mitto.iov2.transform#ExtraColumnsTransform",
                    "include_empty_columns": true
                }
            ],
            "use": "mitto.iov2.steps#Input"
        },
        {
            "transforms": [
                {
                    "use": "mitto.iov2.transform#FlattenTransform"
                }
            ],
            "use": "mitto.iov2.steps#Output"
        }
    ]
}

Keys

input

Contains the database and query: SELECT * from excel.tennis_products;. Any functioning query will work. Mitto will use the column names for headers.

output

Contains the output path, and the delimiter to use. /var/mitto/data/ is the public directory. Files in this directory will be visible in the Files page (in the menu on the left of your Mitto UI).

In this example we're using , as a delimiter. For tabs use: \t

    Path Variables

    It is possible to include date and time variables in your output path/filenames. In        our example /var/mitto/data/output_{year}_{month}_{day}.csv, the strings        inside curly brackets {} are variables; in this case the current year, month and            day.

    Available Variables (Originally written on 26/05/2020 at 7:30AM):

  • year - The current year (2020)
  • month - The current month (05)
  • day - The current day (26)
  • hour - The current hour (07)
  • start_time - Date and time the job started (05-26-2020T07:30:30)
  • last_year - 2019
  • last_month - 04
  • last_day - 25
  • last_hour - 06
  • next_year - 2021
  • next_month - 06
  • next_day - 27
  • next_hour - 08

steps

Any transform steps for processing the data. The steps included are default steps which will include empty columns.

Resulting Flat File

output from toCSV job

The flat file is created on Mitto's file system. You can download the file manually or use another job (e.g. email, command line using rclone, etc) to send the file elsewhere automatically.

Anatomy of a Mitto IO job
This blog post is part 1 of a multi part blog series on understanding and customizing Mitto IO jobs. Mitto [https://www.zuar.com/products/mitto] comes with many different plugins [https://www.zuar.com/help/mitto/plugins/…