NAV Navbar
  • Download the app
  • Make it executable
  • Create a config file
  • Run the script
  • Get support

    Send SQL data to datasets

    SQL-Dataset is a command line app that quickly and easily integrates your 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.

    Download the app

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

    Make it executable

    To make the app executable on macOS and Linux, you'll need to open a terminal and run chmod u+x [path/to/file], where [path/to/file] is the path to your downloaded app. Windows users can skip to creating a config file.

    Here's what an example [path/to/file] looks like:

    /Users/macosuser/Downloads/v0.2.3/sql-dataset-darwin-10.10-amd64

    Create a config file

    SQL-Datasets works by reading all of the information it needs from a YAML file. To help get you started, we've prepared an example.yml config.

    If you prefer, 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.

    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:

    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:

    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.

    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

    Now 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

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

    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-Datasets supports all of the field types supported by the Datasets API:

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

    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.

    Run the script

    Make sure that the SQL-Dataset app and your config file are in the same folder, then from the command line navigate to that folder and run (don't forget the period . required at the beginning of the command):

    .[your folder path]/sql-dataset -config config.yml

    Where config.yml is the name of your config file. Once you see confirmation that everything ran successfully, head over to Geckoboard and start using your new dataset to build widgets.

    When SQL query returns no data

    The SQL-Datasets app has a built-in feature whereby if your SQL query within the config file returns no rows of data, the following will occur:

    For Append calls (i.e. POST): No new data will be pushed. The logic here being there's nothing to append to the dataset. The corresponding widget(s) powered by that dataset will continue to show the previous data.

    For Replace calls (i.e. PUT): This will wipe clean all the existing data in the dataset and will leave behind an empty dataset. The dataset itself and its schema will be preserved though. The corresponding widget(s) will display a No data to visulize message.