Now that we have DingoQL setup and connected to our database we can start querying database. DingoQL makes analytics queries extremely easy to run. Before we start we will first familize ourselves with API Explorer in DingoQL.
API explorer is an IDE like environment provided in DingoQL's admin panel. Once we log into DingoQL's admin panel we can go to API explorer by selecting "API Explorer" from top navigation.
This API explorer is a Graphiql playground. Graphiql is a popular GraphQL IDE. Here we have integrated Graphiql IDE so that we can conveniently create develop DingoQL queries.
In left panel of API explorer we can write our queries. To test this out, we can copy and paste the following query in left side panel and click the red colored play / execute button to run this query.
{
q {
sampledb {
public {
orders(metric: { operator: SUM, field: freight }) {
jsonData
}
}
}
}
}
We will see JSON response in right panel. In a later tutorial we will learn, how this json response will be consumed by your application to display different types of Graphs.
API explorer also has a bottom panel which shows the response in a tabular manner.
The bottom panel of API explorer also contains a "Schema and Security" tab where we can view fields available in table we are querying.
Now that we have an understanding of DingoQL's API explorer we will learn how we can write analytics queries.
We will go through few examples of queries on a table orders
which contains the following columns.
Column name | Data type |
---|---|
freight | Float |
shipCity | String |
orderId | Integer |
customerId | Char |
employeeId | Integer |
orderDate | Timestamp |
requiredDate | Timestamp |
shippedDate | Timestamp |
shipVia | Integer |
shipName | Integer |
shipAddress | String |
shipCity | String |
shipRegion | String |
shipPostalCode | String |
shipCountry | String |
All analytics queries in DingoQL starts at root q
.
Then we have to pick a datasource, which for this case is sampledb
. Then we pick a database's schema. For this example schema is public
.
In the last step we will pick a table we want to query. For this example we will select orders
table.
Here orders
is also graphql query name. Like any graphql query we will also have to specify response fields we want from this query. So we specify response fields jsonData
and dataTypes
and we get following query.
{
q {
sampledb {
public {
orders {
jsonData,
dataTypes
}
}
}
}
}
But this analytics query is not valid because we have to specify how to query orders
table. For this example, we can pick two fields shippedDate
and frieght
. This will give us a two-dimensional dataset - shipName vs freight.
{
q {
sampledb {
public {
orders(fields: [ shipName, freight ]) {
jsonData,
dataTypes
}
}
}
}
}
Let's say we want to find sum of freight
in all orders. We can do that by setting input field metric
as follows.
{
q {
sampledb {
public {
orders(metric: { operator: SUM, field: freight }) {
jsonData,
dataTypes
}
}
}
}
}
Here we are specifying that we want to execute a SUM
query on frieght
field.
Next we will add another dimension to this query by setting one more field to this query as follows.
{
q {
sampledb {
public {
orders(metric: { operator: SUM, field: freight }, fields: [ shipCity ]) {
jsonData,
dataTypes
}
}
}
}
}
This gives a dataset of sum of frieght
per shipCity
.
Plotting a metric against time is probably the most common use case in analytics. For example we might want to plot average freight by day. This query can be made by querying for field shippedDate_DAY
as follows.
{
q {
sampledb {
public {
orders(metric: { operator: AVG, field: freight },
fields: [ shippedDate_DAY ]) {
jsonData,
dataTypes
}
}
}
}
}
We can change the unit of time-axis by changing the postfix of this query to _MONTH
or _YEAR
etc.
{
q {
sampledb {
public {
orders(metric: { operator: AVG, field: freight },
fields: [ shippedDate_MONTH ]) {
jsonData,
dataTypes
}
}
}
}
}
We can also add more fields. For example we might want average freight for each day by ship city of order.
{
q {
sampledb {
public {
orders(
metric: { operator: AVG, field: freight },
fields: [ shippedDate_DAY, shipCity ]) {
jsonData,
dataTypes
}
}
}
}
}
Filters are also extremely simple with DingoQL. Filters can be defined by setting filters
field. Let's say we want sum of all frieghts from city "Madrid". We can do that with following query.
{
q {
sampledb {
public {
orders(metric: { operator: SUM, field: freight },
filters: {
shipCity_EQ: "Madrid"
}) {
jsonData,
dataTypes
}
}
}
}
}
Here we are setting field shipCity_EQ
in filters object. shipCity
is field name and _EQ
is comparison operator equal to.
Let's say we want sum of all frieght from 'Madrid' which are greater than 50. We can do that by setting field freight_GT
in filters input. Here postfix _GT
stands for greater than operator.
{
q {
sampledb {
public {
orders(metric: { operator: SUM, field: freight },
filters: {
shipCity_EQ: "Madrid",
freight_GT: 50
}) {
jsonData,
dataTypes
}
}
}
}
}
I hope this tutorial gives a basic idea about how to write analytics queries in DingoQL.
With DingoQL we can write more complex analytics queries. To know about all the querying capabilities in DingoQL please refer to documentation.
Coming Soon: Server integration