Basic Queries Tutorial
Auto-Group and Auto-Sum:
logQL automatically groups all string and date type fields. For all other number fields, it automatically sums. For instance the following query (from Quick Start section):
SELECT account, out
is equivalent to the following in SQL:
SELECT account, sum(out) FROM acc GROUP BY account
To disable Auto-Group and Auto-Sum, use the GREP command. Simply replace SELECT with GREP in your query. For instance:
GREP account, out
Is the SQL equivalent of:
SELECT account, out FROM acc
Parts of a logQL Query:
There are four parts to logQL query. Not all parts are always required.
- SELECT/GREP: Used to specify the required fields in the output.
- FROM: The data files to query on.
- USE: The Meta to use to query the data file.
- WHERE: The filter conditions.
- ORBER BY: Sort the output on one or more fields.
SELECT/GREP: This is used to select the fields from the data file. As mentioned in section titled ‘Auto-Group and Auto-Sum’ select results are automatically grouped and summed.
SELECT <comma separated list of fields>
SELECT account, in, out
Column Alias: An alias can be entered for the column with the following syntax:
SELECT "", "alias"
SELECT account "A/c", out "Credit"
Tip: To find all the distinct values in a give field, execute the query SELECT
Where: The where clause is used to filter the input data based on a certain condition
SELECT description, out WHERE account = "Food"
The following operators are supported:
|LIKE||Search for a pattern. Use ‘%’ for wild card.|
|NOTLIKE||Ensure pattern does not exist|
|IN||List of possible column values|
|NOTIN||List of values to exclude|
IMPORTANT: a field can only be compared to a value, logQL currently does not allow comparison of two fields.
The following binary operators are supported to club multiple conditions:
|and||Both conditions must evaluate to true|
|or||Either of the conditions must evaluate to true|
Inner queries: Where clause supports inner queries with the IN operator
SELECT <fields> WHERE <field> IN (<query>)
SELECT description, out WHERE account IN (select account WHERE out >50)
IMPORTANT: the inner query should return only one column.