Postgres
Overview
Postgres is an open source SQL database. It supports array, JSONB, JSON, and vector data types and has several supporting query functions and query syntax extensions. Qarbine uses the Postgres node.js driver for interacting with the database.
This tutorial covers the sample data which has JSON columns. It can be loaded by the Qarbine administrator as described in the Postgres configuration document within the Administration documentation area. The concepts discussed apply quite broadly.
Querying JSON Columns
In Qarbine, the query specification below retrieves all the rows from a table containing a JSON column.
SELECT * FROM jproducts
The results are shown below with the first row selected.
Qarbine can easily analyze this deeply nested data and format an analysis report. This interaction can also be embedded into applications for a seamless end user experience.
Notice the data column value is a real object. Conversions to JSON objects happen automatically. This enables much easier analytics than manually trying to deal with the simple string value.
Qarbine can be directed to further modify the answer set elements This is done using ‘pragmas” which are discussed in the general Data Source Designer documentation. The most common pragma likely to be used is “pullFieldsUp CSV_list_of_fields”.
This answer set can be simplified by using Qarbine pragmas as shown below.
#pragma pullFieldsUp data
SELECT * FROM jproducts
The results are shown below with the first row selected.
The fields that were previously within the data field object have been pulled up a level. This makes it much easier to reference values within template formulas.
Defining a Data Source
Overview
A Data Source is a Qarbine component responsible for retrieving data from somewhere. At a high level it has a name, a description and some arbitrary query string which when sent to the associated Qarbine Data Service endpoint returns some data. The overall execution flow for an analysis, including the optional prompt component, is shown below.
A single data source can be referenced by name from multiple Qarbine template components. This enables a single point of change when perhaps, an index is added, or some other query tweak is necessary. The alternative is to attempt to find all templates impacted by a schema or index change for example. This component reusability is especially beneficial when team members have varying roles and skills.
This data source can be found at “example/Postgres/Movies for director Steven Spielberg”.
The data source query specification below retrieves Steven Spielberg directed movies
SELECT *
FROM netflix_shows
WHERE type = 'Movie'
and director = 'Steven Spielberg'
Sample results are shown below.
The details of that element are shown below
Managing Answer Set Size
The default maximum number of rows starts off at 25 for a new data source. This is useful to evolve a query from a concept to one that you have verified returns the desired answer set. As noted, any native way of limiting an answer set size is the preferred approach. This setting is in the component dialog as shown below and also accessible by clicking the ‘Gear’ icon.
Once you are done drafting you can adjust this parameter. A “0” indicates there is no maximum. A number greater than 0 indicates to limit the final answer set size to that number of rows. This answer set truncation comes after any native query limit. So, if the answer set from the data endpoint is quite large, that content has to be returned to the Qarbine host. It then may truncate the number of rows. It is best to truncate at the query level (i.e., use a limit) to reduce the content sent from the data endpoint to the Qarbine host in the first place.
Adjusting Maximum Rows
Recall the default maximum rows at the component level is 25. When you are satisfied with your query you can change that setting by clicking.
Adjust the setting to “0” indicating no Qarbine answer set truncation.
Click
Prompt Integration
Overview
Qarbine prompts provide a way to obtain runtime values and variables for data source and template execution. To avoid hardcoding, prompts can use macro formulas to run queries which populate list widgets. Prompts are defined in a no code manner using the Prompt Designer. Shown below is the execution flow when there is a Prompt component.
Sample Prompt
The Prompt Designer supports a large variety of input widgets including entry fields, check boxes, radio button groups, sliders, and file input. Shown below is a prompt for a director’s name.
Clicking
propagates the director variable along to data sources and templates for their consumption.
This Prompt can be found at “example/Postgres/Prompt for top 10 director”. The prompt has the following elements.
The second prompt element has these main properties.
The list is populated by running a data source using the formula shown below.
That data source returns elements with a field named ‘director’ containing the name of the director. That value is shown in the list box and also used as the selection value.
Adjusting the Data Source to Use the Prompt
The data source defined above uses a hard coded director name. Load that data source and follow the steps below to use a prompted director name instead of a hard coded director name.
The following new data source can be found at “example/Postgres/Movies for prompted director”.
If you load the previously defined data source then immediately do a “Save as” action 
to create a new data source. Give it a name such as “Movies for prompted director”. You will use this data source in the following section describing template definition.
There are 2 main differences from the first data source:
- The query spec has a variable to a director name value.
- A prompt is referenced to obtain a director’s name.
The adjusted query specification is shown below.
SELECT *
FROM netflix_shows
WHERE type = 'Movie' and director = @director
Associated the prompt by first clicking 
to open the properties dialog.
Activate the Prompt tab.
Choose the drop down option shown below.
Since the prompt was just defined use the recents dialog button 
to choose the prompt reference. The desired result is shown below.
Save the data source updates by clicking
Defining an Analysis Template
Overview
A template defines how to process the data being retrieved from Data Source queries and other data expressions. It also defines formulas, formatting options, and other analysis and presentation options. The overall execution flow for an analysis, including the optional prompt component, is shown below
Using the Template Designer
This template can be found at “example/Postgres/Movies for prompted director”.
Review its properties by clicking on .
Notice it is associated with the data source defined above. Recall that data source is associated with the prompt described above as well.
The page layout properties are shown below.
The general layout of the template is shown below.
The “@director” cell in the report header shows the chosen director name from the prompt.
The first line of the body has its cells in bold. The other cells are standard labels and property formulas. The “#” prefix indicates a field of the current element.
Running the template presents the prompt as shown below.
A director is selected and the OK button clicked.
The director’s name propagates into the data source query specification. The answer set from running that query is processed based on the template and the results shown.
Next Steps
Accessing Your Database
To configure access to your database see the guides at
http://doc.qarbine.com/docs/category/data-service-configuration
Querying Your Database
For database specific interaction guides navigate to
http://doc.qarbine.com/docs/category/data-source-designer
References
A general reference for Postgres querying can be found at https://www.postgresql.org/docs/current/queries.html