Spark SQL- operators Cheatsheet- Explanation with Usecases

Dec 28, 2024

•

•

4 min read

Spark SQL Operators Cheatsheet

1. Arithmetic Operators

Operator	Syntax	Description	Example
`+`	`a + b`	Adds two values	`SELECT 5 + 3;`
`-`	`a - b`	Subtracts one value from another	`SELECT 5 - 3;`
`*`	`a * b`	Multiplies two values	`SELECT 5 * 3;`
`/`	`a / b`	Divides one value by another	`SELECT 6 / 2;`
`%`	`a % b`	Returns the remainder of division	`SELECT 5 % 2;`

2. Comparison Operators

Operator	Syntax	Description	Example
`=`	`a = b`	Equal to	`SELECT * FROM table WHERE col = 10;`
`!=` or `<>`	`a != b` or `a <> b`	Not equal to	`SELECT * FROM table WHERE col != 10;`
`>`	`a > b`	Greater than	`SELECT * FROM table WHERE col > 10;`
`<`	`a < b`	Less than	`SELECT * FROM table WHERE col < 10;`
`>=`	`a >= b`	Greater than or equal to	`SELECT * FROM table WHERE col >= 10;`
`<=`	`a <= b`	Less than or equal to	`SELECT * FROM table WHERE col <= 10;`

3. Logical Operators

Operator	Syntax	Description	Example
`AND`	`a AND b`	Returns true if both conditions are true	`SELECT * FROM table WHERE a > 10 AND b < 20;`
`OR`	`a OR b`	Returns true if any condition is true	`SELECT * FROM table WHERE a > 10 OR b < 20;`
`NOT`	`NOT a`	Reverses the boolean value of a condition	`SELECT * FROM table WHERE NOT a > 10;`

4. String Operators

Operator	Syntax	Description	Example
`		`	`a		b`	Concatenates two strings	`SELECT ‘Hello’		‘ World’;`
`LIKE`	`a LIKE pattern`	Matches a string to a pattern	`SELECT * FROM table WHERE name LIKE 'A%';`
`RLIKE`	`a RLIKE pattern`	Matches a string to a regex pattern	`SELECT * FROM table WHERE name RLIKE '^A';`
`ILIKE`	`a ILIKE pattern`	Case-insensitive `LIKE` pattern matching	`SELECT * FROM table WHERE name ILIKE 'a%';`

5. Null Handling Operators

Operator	Syntax	Description	Example
`IS NULL`	`a IS NULL`	Checks if a value is NULL	`SELECT * FROM table WHERE col IS NULL;`
`IS NOT NULL`	`a IS NOT NULL`	Checks if a value is NOT NULL	`SELECT * FROM table WHERE col IS NOT NULL;`
`NULLIF`	`NULLIF(a, b)`	Returns NULL if a equals b, otherwise a	`SELECT NULLIF(10, 10);`
`COALESCE`	`COALESCE(a, b, ...)`	Returns the first non-NULL value	`SELECT COALESCE(NULL, 'default');`

6. Set Operators

Operator	Syntax	Description	Example
`UNION`	`query1 UNION query2`	Combines results of two queries (removes duplicates)	`SELECT col FROM table1 UNION SELECT col FROM table2;`
`UNION ALL`	`query1 UNION ALL query2`	Combines results of two queries (includes duplicates)	`SELECT col FROM table1 UNION ALL SELECT col FROM table2;`
`INTERSECT`	`query1 INTERSECT query2`	Returns common rows from both queries	`SELECT col FROM table1 INTERSECT SELECT col FROM table2;`
`EXCEPT`	`query1 EXCEPT query2`	Returns rows from the first query not in the second	`SELECT col FROM table1 EXCEPT SELECT col FROM table2;`

7. Aggregate Functions (Operators)

Function	Syntax	Description	Example
`COUNT`	`COUNT(*)` or `COUNT(col)`	Counts rows or non-NULL values	`SELECT COUNT(*) FROM table;`
`SUM`	`SUM(col)`	Sums up numeric values	`SELECT SUM(sales) FROM table;`
`AVG`	`AVG(col)`	Calculates the average	`SELECT AVG(price) FROM table;`
`MIN`	`MIN(col)`	Finds the minimum value	`SELECT MIN(price) FROM table;`
`MAX`	`MAX(col)`	Finds the maximum value	`SELECT MAX(price) FROM table;`
`GROUP BY`	`GROUP BY col`	Groups rows based on a column value	`SELECT category, SUM(sales) FROM table GROUP BY category;`

8. Window Functions

Function	Syntax	Description	Example
`ROW_NUMBER`	`ROW_NUMBER() OVER (PARTITION BY col ORDER BY col2)`	Assigns a unique number to each row within a partition	`SELECT ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales) AS rank FROM table;`
`RANK`	`RANK() OVER (PARTITION BY col ORDER BY col2)`	Assigns rank to rows with gaps for duplicates	`SELECT RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank FROM table;`
`DENSE_RANK`	`DENSE_RANK() OVER (PARTITION BY col ORDER BY col2)`	Assigns rank to rows without gaps	`SELECT DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank FROM table;`
`NTILE`	`NTILE(n) OVER (PARTITION BY col ORDER BY col2)`	Divides rows into n buckets	`SELECT NTILE(4) OVER (ORDER BY sales) AS quartile FROM table;`
`LEAD`	`LEAD(col, offset, default) OVER (PARTITION BY col ORDER BY col2)`	Accesses the value of the next row	`SELECT LEAD(sales, 1, 0) OVER (ORDER BY date) FROM table;`
`LAG`	`LAG(col, offset, default) OVER (PARTITION BY col ORDER BY col2)`	Accesses the value of the previous row	`SELECT LAG(sales, 1, 0) OVER (ORDER BY date) FROM table;`

9. Miscellaneous Operators

Operator	Syntax	Description	Example
`CAST`	`CAST(expr AS type)`	Converts a value to a specified type	`SELECT CAST(price AS STRING) FROM table;`
`CASE`	`CASE WHEN condition THEN result ELSE result END`	Conditional logic	`SELECT CASE WHEN sales > 100 THEN 'High' ELSE 'Low' END AS category FROM table;`
`DISTINCT`	`DISTINCT col`	Returns unique values	`SELECT DISTINCT category FROM table;`

This cheatsheet provides a comprehensive overview of commonly used Spark SQL operators and functions with their syntax, descriptions, and examples. Use it as a reference to efficiently write and optimize Spark SQL queries!

Share this Post, Make us Famous:-

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts