Foxtrot Query Language

FQL is a subset of SQL for querying events and running supported agrregations on foxtrot. FQL takes advantages of the dynamic Hazelcast based caching and other optimizations in ES and HBase, but provides a dead simple and familiar interface to talk with foxtrot.

How to use it

There are two basic ways of using it:

• Use the GUI - Go to the foxtrot console and click the "FQL" tab on the top right. Or directly go to the /fql path.

  Example: http://localhost:17000/fql
  

• From the command line - Hit the API /foxtrot/v1/fql using curl:

  curl -XPOST http://localhost:17000/foxtrot/v1/fql -d "show tables"
  

Reference

In the following:

• <item> refers to user input.
• [item] refers to optional section.

Show tables

show tables

See table columns

desc <table>

Example:

desc mobile-app

See events

select <fieldlist>|* from <table> [where <conditions>] [order by <field> [desc]] [limit <limit> [offset <offset>]] 

Example:

• Get the latest 10 events:

  select * from mobile-app
  

• Get only the event type field of latest 20 events:

  select eventType from mobile-app limit 20 
  

• Get the previous 20 after the above (Events are returned newest first):

  select eventType from mobile-app limit 20 offset 10
  

• Get only the event type field of oldest 20 events:

  select eventType from mobile-app order by _timestamp limit 20
  

Selecting time range for query

Time can be easily selected by using the last() function in the where clause. The variants are:

• last(duration) : Sets a window that starts with (current time - duration) to current time

• last(duration, currentTime) : Sets a window that starts with (currentTime - duration) to currentTime

  select eventType from test where last('1m')
  

Operators supported in where clause

The following operators are supported:

• equals (=), and not equals (<>) operator
• in operator
• between operator for numbers
• less than (<), greater than (>), less than equals (<=), greater than equals (>=) operator for numbers
• and operator for combining clauses (NOTE: or is not supported)

Temporal fields

Foxtrot operates on timestamps which are long numbers. To mark a field as a temporal field to set caching properly, use temporal(<fieldname>) in where clause.

Examples:

• Select oldest 100 eventType and account ID for first 20 app open and close events for 6th October 8:00 AM IST to 2:00PM,

select eventType, accountId from mobile-app where eventType in ('APP_LOAD', 'APP_CLOSE') and temporal(_timestamp) between 1412562600000 and 1412584200000 order by _timestamp limit 100

Group events by type

select * from <table> [where <clauses>] group by <field-list>

Example:

• Group app loads by OS and version for app open and close events for 6th October 8:00 AM IST to 2:00PM:

select * from mobile-app  where eventType in ('APP_LOAD', 'APP_CLOSE') and temporal(_timestamp) between 1412562600000 and 1412584200000 group by os, osVersion

Count events

• Count all events in a table:

  select count(*) from table [ where <clause>]
  

• Count all events where particular field is present:

  select count(field_name) from table where [ where <clause>]
  

• Count event of last 1 day:

  select count(*) from table where last('1d') and [ where <clause>]
  

• Count number of distinct values of a column:

  select count(distinct field_name) from table [ where <clause>]
  

See event trends with time

select trend(\<field-name\>) from table [where <clause>]

trend() function variants

• trend(<fieldname>) - Trend on this field name for the selected time period or last 24 hrs if it's missing, and with period set to 'minutes'

  trend(eventType)
  

• trend(<fieldname>, <period>) - Trend on this field name for the selected time period or one day if it's missing, with specified period

  trend(eventType, 'hours')
  

• trend(<fieldname>, <period>, <timestamp field>) - Trend on this field name for the selected time period or one day if it's missing, with specified period and using the specified field ad the timestamp field.

  trend(eventType, 'hours', clientTimestamp)
  

Example:

select trend(eventType, 'hours', clientTimestamp) from mobile-app  where eventType in ('APP_LOAD', 'APP_CLOSE')

Get statistics on numeric fields

select stats(\<field-name\>) from table [where <clause>]

Example:

• Get max, sum etc as well as percentiles of load time during the selected period for app loads:

  select stats(opDuration) from mobile-app  where eventType = 'APP_LOAD' and temporal(_timestamp) between 1412562600000 and 1412584200000
  

Get trend of statistics on numeric fields

select statstrend(\<field-name\>) from table [where <clause>]

statstrend() function variants

• statstrend(<fieldname>) - Trend on stats for this field for the selected time period or last 24 hrs if it's missing, and with period set to 'minutes'

  statstrend(opDuration)
  

• statstrend(<fieldname>, <period>) - Trend on stats for this field for the selected time period or one day if it's missing, with specified period

  statstrend(opDuration, 'hours')
  

Example:

select statstrend(opDuration, 'hours') from mobile-app  where eventType = 'APP_LOAD' and temporal(_timestamp) between 1412562600000 and 1412584200000

FAQ

• Why don't we have "distinct"? The group by functionality serves the same purpose. Ignore the counts, and you have distinct.
• Why don't we have max(), min() etc? The stats() and statstrend() functions serve the same purpose and give much more information.
• Do we support subquery? No.
• Do we support joins? Foxtrot queries run on a single table, so having join makes no sense.
• Query returning error?_ If your table has '-' or any special character try enclosing table name with in ` character Eg : select count(*) from `my-table`