The Query section in Knowi forms the data engineering foundation in Knowi.
High-Level Features:
- Data exploration, query generation capabilities, and writing your own queries
- Execution of native Queries across NoSQL, SQL, REST APIs, and other sources
- Join multiple Data Sources on the fly without traditional ETL processing
- Optional transformations via UI or Cloud9QL on top of the returned result set
- Directly run queries in real-time in your database or save the query results (into our ElasticStore)
- Schedule query executions
- Drag & drop analytics on the results
- Reusable dataset, from which multiple visualizations can be created, as well as use that dataset as input into other queries
- Apply Machine Learning models on the Query
- Ask ad hoc questions to Dataset in plain English using Search-Based Analytics
- Create widget alerts & trigger notification alerts
Overview
Knowi supports native queries into NoSQL databases, SQL databases, and other sources. This allows you to skip traditional ETL processing, where you may need to send the data into a structured warehouse. Additionally, you can join queries on the fly. Queries can be either real-time against your database or, optionally, for long queries, store the results for faster processing or shield your database from frequent analytics workloads.
In Knowi, the results of any query (real-time/direct queries and non-direct queries) are considered a dataset. A dataset is a reusable component that can span multiple datasources/queries and abstracts the complexity of execution modalities, runtime parameters, and transformations. Further, datasets can be reused as inputs into other queries.
From the dataset, you can create multiple visualizations, each with a transformed view of the original data from the dataset.
Here's a high-level diagram of Knowi:
In this documentation, we will explore the following flow:
Adding the Datasource > Generating the Query > Defining Data Execution Strategy > Dataset Settings (Optional) > Preview Final Results > Create & Run Dataset > Run Visualization on Dataset > View Data on Dashboard
Let's get started.
Connectivity & Datasources
Adding the datasource and establishing network connectivity are the first steps in building visualizations with Knowi.
Please refer to the documentation on Connectivity and Datasources for more details.
Generating the Query
Depending on the type of Datasource you select, a list of collections/tables and field samples (or column names in the case of relational databases) will be automatically detected in the Data Explorer. With the help of these collections/tables, you can generate the query either by using the Visual Builder or by writing the query directly into the Query Editor.
Note: No data models to define or schema definitions are required upfront.
Note: For unstructured/semi-structured Datasources, Knowi uses native APIs for the database to infer fields supported by the database or fall back to sampling the data to determine fields.
Visual Builder
Visual Builder allows you to generate the Query in a no-code environment.
After selecting the Collections/Tables from the Data Explorer, you can simply choose the Metrics, Dimensions, and Filters to generate the query without involving any coding skills.
| FIELDS | PURPOSE |
| Metrics | Allows you to perform aggregations such as sum, count, avg., median, etc., along with Date manipulations |
| Dimensions | Allows you to group date-based fields based on date, week, etc. |
| Sort | Allows you to sort the results |
| Limit | Allows you to restrict the number of records to be fetched from the Datasource |
| Filters | Allows you to specify the filters |
Note: You can also directly drag and drop the fields from Data Explorer to Visual Builder to generate queries.
Collapse/Expand Data Explorer from Visual Query Builder
You can collapse or expand the data explorer section to adjust the available space.
If you are using a relational database like Amazon Redshift and have enabled Detect Schema (at the time of adding the Datasource), then selecting the metrics from the Data Explorer will have a dimming effect on the collection where the path needs to allow you to select multiple tables from the dropdown list.
Query Editor
The Query Editor is a versatile text editor designed for editing code and comes with several language modes and add-ons that implement more advanced editing functionalities.
You can simply switch to Query Editor mode via a toggle switch and write the Query directly in the editor.
You can also use Query Editor to inspect the Metrics, Dimensions, and Filters applied in the Visual Builder and make necessary edits.
Some of the additional functionalities in the Query Editor include key-maps as summarized in the table illustrated below:
|
Functionality |
Windows |
Mac |
|
Search |
Ctrl-F |
Cmd-F |
|
Find next |
Ctrl-G |
Cmd-G |
|
Find Previous |
Shift-Ctrl-G |
Shift-Cmd-G |
|
Replace |
Shift-Ctrl-F |
Cmd-Option-F |
|
Replace all |
Shift-Ctrl-R |
Shift-Cmd-Option-F |
|
Jump to line |
Alt-G |
Cmd-G |
Runtime Parameters
Queries can be templated with runtime parameters that can be passed dynamically (from embedded applications to filters).
Please refer to the documentation on Runtime Parameters for more details.
Cloud9QL
Cloud9QL is a SQL-like syntax that can be used to post-process/transform the return data, to complement native Queries. This is not a replacement for the underlying Query but offers powerful analytics functions on the results returned.
Please refer to this documentation on Cloud9QL for more details.