SQL Plotter: Bokeh
SQL Plotter: Bokeh is used to create a visualization from the results of an SQL Query.
Line Graph
The Line 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 Line 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:
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:
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 first twelve rows as values for the X axis from a column named months in a table named sales in your database and the line names and Y axis values for each line will come from the product_a, product_b, and product_c columns:
SELECT months, product_a, product_b, product_c
FROM sales
LIMIT 12;
Title
Type in a Title to display for your Line Graph.
Color Palette
Select one of the predefined color palettes for your Line Graph to use.
Follow this link for palette examples:
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 Type
Select the axis type that matches the nature of your data.
Follow this link for more details about the choices:
X Axis Label
Type in a label to display on the X axis of your Line Graph.
X Axis Labels Orientation
Select whether you want the labels along the X axis of your Line Graph to display horizontally or vertically.
Y Axis Values Sources
Type in a list of column names from your SQL query result that contains the values for your Y axis. Each column name should be enclosed in double quotes and separated by commas. The entire list should be enclosed by square brackets. For example:
["First Column Name", "Second Column Name"]
Each column name will result in a separate line displayed on your Line Graph.
Y Axis Label
Type in a label to display on the Y axis of your Line Graph.
Y Axis Values Range
Type in a list of two numerical values. The first value is the minimum allowed value for a line height and the second value is the maximum allowed value for a line height. Values that fall outside the bounds specified will not be displayed on your graph. The two values should be separated by a comma and enclosed in square brackets. For example:
Show Legend
Toggle to On to display a Legend with your Line 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 Line Graph to display.