PLAY WITH EINSTEIN – SAQL OPERATORS

Hi All, It has been a long time since I did not post anything about Einstein. Your wait is over and here I came with another set of the tutorial in which we will learn what are the basic SAQL Operators and basic introduction about them. In the previous tutorial, we learned how we can create the dataset using CSV file and some basic of SAQL. In this tutorial, we will learn how we can use SAQL for putting some dynamic filters into the dashboard.

SAQL uses following operators to perform calculations & comparisons:

• Arithmetic Operators
• Comparison Operators
• String Operators
• Logical Operators
• Case
• Null Operators

Arithmetic Operators: We have addition, subtraction, multiplication, division & modulo to perform arithmetic operations.

Comparison Operators: It is used to compare the values. Following are the operators:

1. Equals (==)
2. Not Equals (!=)
3. Less Than (<)
4. Less or Equal (<=)
5. Greater Than (>)
6. Greater or Equal (>=)
7. Matches (matches): Ex: q = filter q by ‘Stage’ matches “Closed Won”;
8. In (in): Ex: q= filter a by country in [“IN”, “US”, “UK”];
9. Not in (not in): Ex: q= filter a by country not in [“IN”, “US”, “UK”];

String Operators: Use + sign to concatenate strings.

Ex: q = foreach q generate Id as Id, Year + “-” + Month + “-” + Day as

CreatedDate;

Logical Operators: AND, OR, & NOT are used to perform logical operations.

Null Operators: It is used to test whether the value is null or not.

1. is null
2. is not null

Ex: q = load “dataset name”;

q = foreach q generate Name as Name, Year as Year;

q = filter q by Year is not null

Ex: q = load “dataset name”;

q = foreach q generate (case when Name is null then “hari” else Name end) as Name;

Case: It is like the switch statement. We use it within the foreach statement to create logic between conditions.

It has two syntax forms: searched case expression and simple case expression. The case and end keywords begin and close the expression. The when and then keywords define a conditional statement. A case expression can contain one or more conditional statement.

Searched Case Expression:

Syntax: case

     when ***search_condition*** then result_expr    

     [ when search_condition2 then result_expr2 … ]    

     [ else default_expr ]

end

Example: q = load “data”;

q = foreach q generate temperature, (case when temperature < 0 then “Low” when temperature > 0 and temperature < 50 then “Medium” else “High” end) as temperature;

Simple Case Expression:

Syntax: case primary_expr    

     when test_expr then result_expr    

     [ when test_expr2 then result_expr2 … ]    

     [ else default_expr ]

End

Example: q = load “data”;

       q = foreach q generate xInt, (case xInt % 3 when 0 then “3n” when 1 then “3n+1” else “3n+2” end) as modThree;

SAQL Group, Co-group & Union

Group: Adds one or more columns to a group. If data is grouped by a value that’s null in a row, that whole row is removed from the result.

Syntax: q= group q by field;

q= group q by (field1, field2, …);

Ex: q = load “dataset name”;

q = group q by (year, month);

q = foreach q generate year as year, month as month;

Co-group: Co-grouping means that two input streams, called left and right are grouped independently and arranged side by side. Only data that exists in both groups appears in the results.

Syntax: q= cogroup q by expression, q by expression, …;

Ex: a = load “data1”;

b = load “data2”;

a = cogroup a by year, b by year;

1. You can cogroup more than 2 datasets
2. You can’t have the same stream on both sides of a cogroup operation. To perform a cogroup operation on 1 dataset, load the dataset twice so you have 2 streams.

Union: Combines multiple result sets into one result set.

Syntax: result= union a, b, c, …;

Thanks for reading 🙂 Sharing is caring :). Video will be out soon.

If you have any query/question please feel free to come up in the comment section with or you can tweet me @cloudyamit.