Creation date Minimum version
2017-10-15 1.2.2

Xcalar SQL Query Cheat Sheet

Xcalar Design is a simple and intuitive graphical user interface where any SQL Query operation can be applied to structured, semi-structured, and unstructured data. Xcalar empowers SQL programmers, data analysts, and line of business managers, to derive insights from data lakes by building and executing complex queries quickly and easily.

The following tables list the most common SQL Query operators and their equivalent Xcalar operations.

Where,

  • Column 1 contains the SQL Operator.
  • Column 2 contains the corresponding Xcalar Design function or action.
  • Column 3 contains the corresponding Xcalar Design Function bar command:
    • expression[1,2,3] is a Xcalar column, constant, aggregate, or nested function.
    • tab[1,2] is a Xcalar virtual table.
    • col[1,2] is a column in a Xcalar virtual table.

Table 1: Select Filter operators

SQL WHERE Clause Xcalar Design: Filter Function Xcalar Design: Function Bar
= eq filter(eq(expression1, expression2))
<> neq filter(neq(expression1, expression2))
> gt filter(gt(expression1, expression2))
>= ge filter(ge(expression1, expression2))
< lt filter(lt(expression1, expression2))
<= le filter(le(expression1, expression2))
AND and filter(and(expression1, expression2)
BETWEEN between filter(between(expression1, expression2, expression3)
CONTAINS contains filter(contains(expression1, expression2))
IS NULL exists filter(exists(expression))
ISBOOLEAN isBoolean filter(isBoolean(expression))
ISFLOAT isFloat filter(isFloat(expression))
ISINF isInf filter(isInf(expression))
ISINTEGER isInteger filter(isInteger(expression))
ISSTRING isString filter(isString(expression))
NOT not filter(not(expression))
OR or filter(or(expression1, expression2))
LIKE

like

(use * for wildcard character)

filter(like(expression1, expression2))
REGEX_LIKE (in some RDBMS) regex filter(regex(expression1, expression2))

Table 2: Aggregate Operators

SQL Function Xcalar Design: Aggregate Function Xcalar Design: Function Bar
AVG avg avg(expression)
COUNT count count(expression)
Not Available in SQL

listAgg

(Concatenates all strings of a column in order)

listAgg(expression)
MAX

Max

(Finds maximum value in a set)

max(expression)

maxInteger

(Finds maximum integer value in a set)

maxInteger(expression)

maxFloat

(Finds maximum float value in a set)

maxFloat(expression)

maxString

(Finds maximum string value in a set)

maxString(expression)

MIN

min

(Finds minimum value in a set)

min(expression)

minInteger

(Finds minimum integer value in a set)

minInteger(expression)

minFloat

(Finds minimum float value in a set)

minFloat(expression)

minString

(Finds minimum string value in a set)

minString(expression)
SUM sum sum(expression)
sumInteger sumInteger(expression)

Table 3: Sort Operators

SQL Clause Xcalar Design: Sort Action
ORDER BY col1 ASC A-Z
ORDER BY col1 DESC Z-A

ORDER BY col1 ASC, col2 DESC

(Multi-Column Sort)

  1. Select multiple columns. Right click and choose sort.
  2. Select the sort order for each column.
  3. Click sort.

Table 4: Join Operators

SQL Operator Join Type Xcalar Design Action

SELECT col1, col2, …

FROM tab1

INNER JOIN tab2

ON tab1.col1 = tab2.col1

 

Inner Join

INNER JOIN tab1,tab2

ON tab1.col1, tab2.col1

SELECT col1, col2, …

FROM tab1

LEFT JOIN tab2

ON tab1.col1 = tab2.col1

 

Left Outer Join

LEFT OUTER JOIN tab1,tab2

ON tab1.col1, tab2.col1

SELECT col1, col2, …

FROM tab1

RIGHT JOIN tab2

ON tab1.col1 = tab2.col1

Right Outer Join

RIGHT OUTER JOIN tab1,tab2

ON tab1.col1, tab2.col1

SELECT col1, col2, …

FROM tab1

FULL OUTER JOIN tab2

ON tab1.col1 = tab2.col1

Full Outer Join

FULL OUTER JOIN tab1,tab2

ON tab1.col1, tab2.col1

SELECT col1, col2, …

FROM tab1

INNER JOIN tab2

ON tab1.col1 <> tab2.col1

Inner Join with <> inequality
  1. FULL OUTER JOIN tab1, tab2
  2. Filter FNF values from tab1.col1
  3. Filter FNF values from tab2.col1

This selects all the rows that do not match on the join column.

SELECT col1, col2, …

FROM tab1

INNER JOIN tab2

ON tab1.col1 <= tab2.col1

Range Join

(Inner Join with <, >, <=, >=, BETWEEN inequality)

Currently not supported by Xcalar

Table 5: Set Operators

SQL Operator Xcalar Design: Action/Function Xcalar Design: Function Bar
UNION ALL
  1. Verify that you have the Union All extension installed in Xcalar Design.
  2. Right click on the column and select the extensions option.
  3. From the extensions list select Union All.
  4. Provide the table inputs to Union All.

 

-
COALESCE coalesce map(default:coalesce(expression))
CASE

if

 

map(if(expression1,expression2,expression3))
ifStr map(ifStr(expression1,expression2,expression3)

Table 6: Math, String, and Date Operators

SQL Operator Xcalar Design: Map Function Xcalar Design: Function Bar
ABS abs map(abs(expression))

absint

(Absolute value of integers)

map(absInt(expression))
+ add map(add(expression1, expression2))
- sub map(sub(expression1,expression2))
* mult map(mult(expression1,expression2))
/ div map(div(expression1,expression2))
& bitand map(bitand(expression1, expression2))
<< bitlshift map(bitlshift(expression1,expression2))
| bitor map(bitor(expression1,expression2))
>> bitrshift map(bitrshift(expression1,expression2))
^ bitxor map(bitxor(expression1,expression2))
CEILING ceil map(ceil(expression))
EXP exp map(exp(expression))
FLOATCOMPARE floatCompare map(floatCompare(expression1,expression2))
FLOOR floor map(floor(expression))
LOG log map(log(expression))
LOG10 log10 map(log10(expression))
LOG2 log2 map(log2(expression))
MODULO mod map(mod(expression1,expression2))
POWER pow map(pow(expression1, expression2))
ROUND round map(round(expression))
SQRT sqrt map(sqrt(expression))
|| concat map(concat(expression1,expression2))

DATEADD

(Add number of days, months, years)

dateAddDay map(dateAddDay(expression, Format,Day Interval))
dateAddMonth map(dateAddMonth(expression, Format,Month Interval))
dateAddYear map(dateAddYear(expression, Format,Year Interval))
dateAddInterval map(dateAddDay(expression, Format,Year/Month/Day Interval))
CAST (or) CONVERT convertDate map(convertDate(expression,Input- Format,Output-Format))
SYSUTCDATETIME convertFromUnixTS map(convertFromUnixTS(expression, Output-Format))
GETUTCDATE convertToUnixTS map(convertToUnixTS(O_CUSTKEY, Input-Format))
Not Available in SQL

ipAddrToInt

(converts an IPv4 address to an integer)

map(ipAddrToInt(ipAddr, numOctets))
Not Available in SQL

macAddrToInt

(converts a MAC address to an integer)

map(macAddrToInt(ipAddr,numOctets ))
Not Available in SQL

dhHash

(generates a hash key)

map(dhtHash(expression to compute hashkey))
RAND genRandom map(genRandom(expression1,expression2))
Not Available in SQL

genUnique

(generate a unique integer)

map(genUnique())
IF if map(if(condition,trueAction, falseAction))
ifStr map(ifStr(condition, trueAction, falseAction))
CONCAT concat map(concat(expression1,expression2))
IS NOT NULL not exists map(not(exists(expression))
Not Available in SQL

countChar

(counts occurrences of specified characters)

map(countChar(expression1,expression2))
CUT cut map(cut(string,fieldnumber,delimiter))
FIND find map(find(string,string to find,starting index,ending index))
INDEX (or) POSITION find map(find(string,string to find,starting index,ending index))
LEN len map(len(expression))
REPLACE replace map(replace(expression1,search string,replace string))
Not Available in SQL

rfind

(returns the index of the last character of search string)

map(rfind(string,string to find,starting index,ending index))
TRIM strip map(strip(expression))
SYSDATETIME now map(default:now())
SUBSTRING substring map(substring(string,starting index,ending index))
DATALENGTH wordCount map(wordCount(expression))
ACOS acos map(acos(expression))
ACOSH acosh map(acosh(expression))
ASIN asin map(asin(expression))
ASINH asinh map(asinh(expression))
ATAN atan map(atan(expression))
ATAN2 atan2 map(atan2(expression))
ATANH atanh map(atanh(expression))
COS cos map(cos(expression))
COSH cosh map(cosh(expression))
DEGREES degrees map(degrees(expression))
PI pi map(pi())
RADIANS radians map(radians(expression))
SIN sin map(sin(expression))
SINH sinh map(sinh(expression))
TAN tan map(tan(expression))
TANH tanh map(tanh(expression))
BOOLEAN bool map(bool(expression))
FLOAT float map(float(expression))
INT int map(int(expression,tobase))
STRING string map(string(expression))

Table 7: Correlated Subquery Operators

SQL Operator Xcalar Design: Actions Additional Details
EXISTS
  1. Group by on the Inner Table key column to remove duplicates.
  2. Inner Join between outer and inner table key column.
Implementing EXISTS and NOT EXISTS Correlated Subqueries in Xcalar
NOT EXISTS
  1. Group by on the Inner Table key column to remove duplicates.
  2. Left Outer Join between outer and inner table key column.
  3. Filter to include the FNF values from the inner table column.
Implementing EXISTS and NOT EXISTS Correlated Subqueries in Xcalar
IN
  1. Group by on the Inner Table key column to remove duplicates.
  2. Inner Join between outer and inner table key column.
-
NOT IN
  1. Group by on the Inner Table key column to remove duplicates.
  2. Left Outer Join between outer and inner table key column.
  3. Filter to include the FNF values from the inner table column.
-

Go to top