SQL Plotter: Bokeh – Bar Graph

SQL Plotter: Bokeh

SQL Plotter: Bokeh is used to create a visualization from the results of an SQL Query.

Bar Graph

The Bar Graph action will create a visualization from the results of an SQL Query. Whenever your Task runs, it will execute your SQL Query against the database specified in your Target Database Connection. A multi-column result table with values for the x and y axes of your graph will be built. That table of values will be stored along with the other parameters that you have specified locally on your Nominode in a JSON Visualization Blob. Any JSON Visualization Blob existing from a previous run of your task will be overwritten. Your Nominode can translate the JSON Visualization Blob into a Bar Graph that can be displayed in a Widget on Dashboard.

This link contains more information about adding a Widget to a Dashboard on your Nominode:

https://support.nomnomdata.com/portal/kb/articles/managing-dashboards-on-a-nominode#Adding_a_Widget_to_a_Dashboard

This Task requires you to provide a remote database Connection, an SQL Query string to run against the database, and several parameters that describe how you want your graph to look.

Alias

All Tasks contain this field. Set an Alias name that is meaningful and memorable as it will appear in the list of Tasks on your Nominode.

Target Database Connection

SQL Database access information is stored in a Generic:Database Connection on your Nominode.

This link contains more information about creating a Connection on a Nominode:

https://support.nomnomdata.com/portal/kb/articles/managing-connections-on-a-nominode#Creating_a_Connection

The Generic:Database Connection has seven fields.

  • Set the Alias field to something meaningful like “YourDatabase on YourSQLServer”.
  • Leave the Engine Type field set to the default value of “mysql”.
  • Set the Hostname field to the DNS name of the server running the software hosting your database.
  • Set the Database Name field to the name of your database.
  • Set the Username field to the name of the SQL user that has the desired access to your database.
  • Set the Password field to the SQL user’s password.
  • Set the Port field to the port number that your database server is listening on. MySQL servers listen on port 3306 by default.

Once the Connection is created, you can select its Alias from the drop down list for the Target Database Connection field on this Task.

SQL Query

Type in an SQL query that will generate the values to display on x and y axes of your graph.  For example, this SQL query will pull the names of the bars from the first ten unique values in a column named yourcolumn in a table named yourtable in your database and the height of each bar will be the number of times each unique value appears in yourcolumn:

SELECT yourcolumn as xvalues, COUNT(*) as yvalues
FROM yourtable
GROUP BY 1
LIMIT 10;

Title

Type in a Title to display for your Bar Graph.

Color Palette

Select one of the predefined color palettes for your Bar Graph to use.

Follow this link for palette examples:

https://docs.bokeh.org/en/latest/docs/reference/palettes.html

X Axis Values Source

Type in the name of the column in your SQL query result that contains the values for your X axis.

X Axis Label

Type in a label to display on the X axis of your Bar Graph.

X Axis Labels Orientation

Select whether you want the labels along the X axis of your Bar Graph to display horizontally or vertically.

Y Axis Values Source

Type in the name of the column in your SQL query result that contains the values for your Y axis.

Y Axis Label

Type in a label to display on the Y axis of your Bar Graph.

Y Axis Values Range

Type in a list of two numerical values. The first value is the minimum allowed value for a bar height and the second value is the maximum allowed value for a bar height. Values that fall outside the bounds specified will not be included on your graph. The two values should be separated by a comma and enclosed in square brackets. For example:

[0,100]

Show Legend

Toggle to On to display a Legend with your Bar Graph. Toggle to Off, otherwise.

Legend Title

Type in the title to display in your Legend.

Legend Label Names

Map the column titles in your SQL query result to the names that you want to display in your Legend. Each mapping should be in the form of the column title in double quotes followed by a colon and then the desired label name for that column in double quotes. Each of those pairs should be separated by a comma and the entire set should be enclosed by curly braces. For example:

{“First Column Name”:”First Label Name”,”Second Column Name”:”Second Label Name”}

Legend Labels Orientation

Select whether you want the labels in your Legend to display horizontally or vertically.

Legend Location

Select from nine different locations where you want the legend of your Bar Graph to display.

Byte sized updates delivered to your inbox
  • This field is for validation purposes and should be left unchanged.