Skip to main content

 

Interana Docs

BAQL syntax and usage

Interana provides a flexible query language to use within an external query API call. This article describes how to format BAQL syntax. It includes a table comparing and contrasting some types of queries with analogies in SQL, as well as some example BAQL queries.

BAQL syntax

Use BAQL inside a call to the Interana external query API.

A complete API call containing BAQL is formatted as follows:

{"bql": "select count(*) from foreverMusic"}

A sample curl request with BAQL looks like this:

curl 'https://11.2.34.141/v1/query' \
    -H 'Content-Type: application/json' \
    -H 'Authorization: Token san+aslnasw50293sjlfhgnoOvWW/sQH09y0' \
    -d '{"bql": "select count(*) from foreverMusic"}' --verbose
> POST /v1/query HTTP/2
> Host: 11.2.34.141
> User-Agent: curl/7.54.0
> Accept: */*
> Content-type: application/json
> Authorization: Token san+aslnasw50293sjlfhgnoOvWW/sQH09y0
> Content-Length: 32

For more information on using the external query API, see Use the Interana external query API.

This article focuses on the syntax within the double quotes.

BAQL building blocks

Like other database query languages, BAQL statements consist of a sequence of clauses, which can in turn consist of expressions. BAQL clauses must be assembled in a specific order to form a valid statement.

To build a BAQL query, start by choosing aggregations and the target of the aggregations (that is, the things you want to aggregate over).

Every BAQL query must contain the required clauses, as follows:

  • aggregation
  • table
  • time range (if none is supplied, the query defaults to the time range "beginning of time to now")

Other clauses are optional, for example:

  • Filter on aggregation
  • Group by clause

For example, consider the following statement:

select count (* where page = "Error") from my_table group by ROUND(length,10) between 6 months ago and now

This BAQL statement consists of several clauses and an expression. The clauses in the example are the following:

  • select count (* where page="Error")
  • from my_table
  • group by artist
  • between 6 months ago and now

The expressions in the example are the following:

  • ROUND(length,10)
  • page = "Error"

More examples of valid expressions include the following:

  • ROUND(length,10) + 1
  • page like ".*error.*"
  • length - another_length
  • length / 100 * 100

BAQL is case sensitive.

BAQL equivalents of Interana UI queries

The following table shows common Interana queries and their equivalents in BAQL.

Interana sentence-model UI

BAQL statement

Concept

Show count of events

select count (*) from my_table

Aggregate

Show count of events
Filtered to events with page that matches Error

select count (* where page=”Error”) from my_table

 

Filter

Show count of events Filtered to events with page that matches Error
Split by artist

select count (* where page=”Error”) from my_table group by artist

Split by

Show count of events Filtered to events with page that matches Error ​​​​Split by artist
6 months ago until now

select count (* where page=”Error”) from my_table group by artist between 6 months ago and now

Relative time

Show count of events Filtered to events with page that matches Error
Split by ROUND(length,10) 6 months ago until now

select count (* where page="Error") from my_table group by ROUND(length,10) between 6 months ago and now

Math

Show count of events Filtered to all events
split by username
Limit 5
Ordered by cnt ascending
Starting 7 days ago Ending now

select count(*) as cnt from nightly1_usage group by username limit 5 order by cnt between 7 days ago and now Ordered by, refer to measure name (cnt)

Some queries allowed in BAQL do not work in the Interana UI, and vice versa. In particular, many of the restrictions imposed by the UI around query start and end times are enforced because the UI wants to be able to guarantee that the end time is later than the start time no matter when the query is run, in case you pin the query to a board, for example. In that context, the UI does not allow queries whose validity depends on the time of day. The BAQL API doesn't concern itself with that; it accepts or rejects the query based on whether the time range is valid at the moment you run the query.

Time

Relative time

The following query defines a relative time window:

select count(* where action="hate") from fashion between beginning_of_time and now
Trailing time windows

The following query counts the number of events for each 2 day window every day between 2019-01-01 and 2019-01-15 UTC:

select count(*) from my_table for every day over 2 days between 2019-01-01 and 2019-01-15

In the current version of Interana, a trailing window must be a multiple of the resolution. For example, a trailing window cannot be 7 days if the resolution is 3 days. This also means that a trailing window cannot be smaller than the resolution.

Specify timezone

By default, BAQL uses UTC rather than the timezone that your Interana cluster uses in UI-based queries. To specify a timezone, append timezone <timezone> to the end of your query. For example:

select count(*) from my_table between 7 days ago and now timezone US/Pacific

To correctly format your timezone, see the tz timezone database and the related Wikipedia article. Note that Interana supports only timezones with one-hour alignments. For example, it does not support America/St. Johns (+3:30) or Asia/Calcutta (+5:30).

Custom event properties

An event property can be raw or custom (also called manual). A custom event property can be created using one of three methods: label, filter, or calculate. The syntax for filtering your query on a custom event property varies depending on the method used to create the property, as follows:

Event property method Filter syntax
Calculate where <clc_event_prop_name> = "<value>"
Label where <lbl_event_prop_name> = "<value>"
Filter where <flt_event_prop_name> 
where not <flt_event_prop_name>

For example, to filter on a label event property, use the following:

select count(* where action="hate") from fashion

But to filter on a filter event property called authenticated (where each value is either "true" or "false"), use the following syntax:

select count(* where authenticated) from fashion

Actor property examples

The following query returns the number of users who had an event between 2019-01-08 and 2019-01-15 but not in the previous week (2019-01-01 to 2019-01-18):

with prev_week_activity as Actor<user>(
  count(*) over 1 week offset -1 week
)
select count_unique(user where prev_week_activity = 0) from my_table between 2019-01-08 and 2019-01-15

The following query defines two actor properties, user_age and years_voting, the former as an aggregation and the latter as a formula:

with user_age as Actor<user>(
  max(age) between beginning_of_time and now
),
years_voting as Actor<user>(
  user_age - 18
)
select ...

Escaping special characters

Access fields with spaces, periods, hyphens or other special characters by using backquotes. For example:

select count(*) from my_table group by `hello.world` between 7 days ago and now

Quoted strings are currently not supported in as statements. 

  • Was this article helpful?