Send SQL data to datasets (Windows)

SQL-Dataset is a command line app that quickly and easily integrates your Microsoft SQL Server, MySQL, Postgres and SQLite databases with Geckoboard. Rather than having to work with client libraries and write code to connect to and query your database, with SQL-Dataset all you need to do is fill out a simple config file and run the program from the command line.

Running SQL-Datasets on a MacOS or Linux machine or server?

If you plan to run SQL-Datasets on a MacOS or Linux machine or server, follow the steps given in this macOS and Linux set-up guide.

Downloading the SQL-Dataset app

To get started with SQL-Dataset, download the latest version of the app for your specific OS and system type.

Note

You may be be presented with the following warning from your browser:Not commonly downloaded and may harm your computer.This is due to the fact that the binary isn't signed, but it's fine. If you click View downloads, this should open up your download folder. Keep this open for later.

After that, follow the steps below:

Create a config file

SQL-Datasets works by reading all of the information it needs from a YAML file.

Tip

To help get you started, we've prepared an example.yml config.

Try to save the config file in the same place as the downloaded executable, it will make running the program easier.Not commonly downloaded and may harm your computer.

Example config file

Here's what an example config file looks like:

geckoboard_api_key: your_api_key
database:
 driver: mysql
 host: xxxx
 port: xxxx
 username: xxxx
 password: xxxx
 name: xxxx
 tls_config:
  ca_file: xxxx
  key_file: xxxx
  cert_file: xxxx
  ssl_mode: xxxx
refresh_time_sec: 60
datasets:
 - name: dataset.name
   update_type: replace
   sql: >
    SELECT 1, 0.34, source
    FROM table
   fields:
    - type: number
      name: Signups
    - type: percentage
      name: Conversion rate
    - type: string
      name: Source

If you wish, you can provide any of geckoboard_api_key, host, port, username, password and (database) name as environment variables with the syntax "{{ YOUR_CUSTOM_ENV }}". Make sure to keep the quotes in there! Here's an example:

geckoboard_api_key: "{{ GB_API_KEY }}"

geckoboard_api_key

This is where your Geckoboard API key goes. You can find yours by following these steps.

database

Enter the type of database you're connecting to in the driver field. SQL-Dataset supports:

  • mssql
  • mysql
  • postgres
  • sqlite

Get in touch

If you'd like to see support for another type of database, let our Customer Success team know or, if you're technically inclined, make the change and submit a pull request!

Only three parameters are required:

  • driver
  • username
  • name

The other attributes, such as host and port, will default to their driver-specific values unless overridden.

SSL

If your database requires a CA cert or a x509 key/cert pair, you can supply this in tls_config under the database key.

tls_config:
 ca_file: /path/to/file.pem
 key_file: /path/to/file.key
 cert_file: /path/to/cert.crt
 ssl_mode: (optional)

The possible values for ssl_mode depend on the database you're using:

MSSQL
disable, false, true – try disable option if you experience connection issues.
MySQL
true, skip-verify.
Postgres
disable, require, verify-ca, verify-full.
SQLite
Not applicable.

Important note

We strongly recommend that the user account you use with SQL-Dataset has the lowest level of permission necessary.

For example, one which is only permitted to perform SELECT statements on the tables you're going to be using. Like any SQL program, SQL-Dataset will run any query you give it, which includes destructive operations such as overwriting existing data, removing records, and dropping tables.

We accept no responsibility for any adverse changes to your database due to accidentally running such a query.

refresh_time_sec

Once started, SQL-Dataset can run your queries periodically and push the results to Geckoboard. Use this field to specify the time, in seconds, between refreshes.

If you do not wish for SQL-Dataset to run on a schedule, omit this option from your config.

datasets

Here's where the magic happens - specify the SQL queries you want to run, and the Datasets you want to push their results into.

name
The name of your Dataset.
sql
Your SQL query.
fields
The schema of the Dataset into which the results of your SQL query will be parsed.
update_type
Either replace, which overwrites the contents of the Dataset with new data on each update, or append, which merges the latest update with your existing data.
unique_by
An optional array of one or more field names whose values will be unique across all your records. When using the append update method, the fields in unique_by will be used to determine whether new data should update any existing records.

fields

A Dataset can hold up to 10 fields. The fields you declare should map directly to the columns that result from your SELECT query, in the same order.

SQL-Dataset supports all of the field types supported by the Datasets API:

  • date
  • datetime
  • number
  • percentage
  • string
  • money

Here's an example:

sql: SELECT date, orders, refunds FROM sales
fields:
 - name: Date
   type: date
 - name: Orders
   type: number
 - name: Refunds
   type: number

The money field type requires a currency_code to be provided:

fields
 - name: MRR
   type: money
   currency_code: USD

Numeric field types can support null values. For a field to support this, pass the optional key:

fields:
 - name: A field which might be NULL
   type: number
   optional: true

The Datasets API requires both a name and a key for each field, but SQL-Dataset will infer a key for you. Sometimes, however, the inferred key might not be permitted by the API. If you encounter such a case, you can supply a specific key value for that field.

fields:
 - name: Your awesome field
   key: some_unique_key
   type: number

Run the program

To run the program on your Windows machine or server, follow these steps:

    1. Search for Command within Cortana. It should return Command Prompt as a matching application.Searching for command within Cortana in Windows
    2. Click on the Command Prompt app. A new terminal window will open.New Command Prompt terminal window
    3. Return to the Downloads folder window, drag the sql-dataset .exe file into the Command Prompt window. The window should update with the full path of the program.Command Prompt with full path of sql-dataset file
    4. In the Command Prompt window type -config. Make sure you include the spaces before and after -config to separate it from the executable path.
    5. Drag the config file into the Command Prompt window, as you did with the sql-dataset .exe file.Command Prompt with config file path included
    6. Click on the Command Prompt window and press the Return key (Enter) on your keyboard. If all is successful, it will begin to run.Command Prompt with program running

Note

If you have the refresh_time_sec key in your config it will re-run the same query after that time. If you only want a one time send of the data or have it scheduled, just remove the key.

Was this article helpful?

Awesome! 👍  Thanks so much for your feedback!