Data Engineering 101 SQL and PySpark 1727161935

Data

Engineering 101
SQL and
PySpark

Shwetank Singh
GritSetGrow - GSGLearn.com
Data Engineering 101: SQL and PySpark

SELECT ALL COLUMNS

SQL
SELECT * FROM table;
PYSPARK
df.select("*")
Shwetank Singh
SELECT SPECIFIC COLUMNS

SQL
SELECT col1, col2 FROM table;
PYSPARK
df.select("col1", "col2")
Shwetank Singh
FILTERING ROWS (WHERE CLAUSE)

SQL
SELECT * FROM table WHERE condition;
PYSPARK
df.filter("condition")
Shwetank Singh
ORDERING ROWS
SQL
SELECT * FROM table ORDER BY col1;
PYSPARK
df.orderBy("col1")
Shwetank Singh
ORDERING ROWS DESCENDING

SQL
SELECT * FROM table ORDER BY col1 DESC;
PYSPARK
df.orderBy(df.col1.desc())
Shwetank Singh
LIMITING ROWS
SQL
SELECT * FROM table LIMIT 10;
PYSPARK
df.limit(10)
Shwetank Singh
SELECTING DISTINCT VALUES

SQL
SELECT DISTINCT col1 FROM table;
PYSPARK
df.select("col1").distinct()
Shwetank Singh
GROUP BY AND AGGREGATE

(COUNT)
SQL
SELECT col1, COUNT(*) FROM table GROUP

BY col1;
PYSPARK
df.groupBy("col1").count()
Shwetank Singh
GROUP BY AND AGGREGATE (AVG)

SQL
SELECT col1, AVG(col2) FROM table GROUP

BY col1;
PYSPARK
df.groupBy("col1").avg("col2")
Shwetank Singh
INNER JOIN
SQL
SELECT *
FROM table1
INNER JOIN table2
ON table1.id = table2.id;
PYSPARK
df1.join(df2, df1.id == df2.id, "inner")
Shwetank Singh
LEFT JOIN
SQL
SELECT * FROM table1

LEFT JOIN table2 ON table1.id = table2.id;
PYSPARK
df1.join(df2, df1.id == df2.id, "left")
Shwetank Singh
RIGHT JOIN
SQL
RIGHT JOIN table2
PYSPARK
df1.join(df2, df1.id == df2.id, "right")
Shwetank Singh
FULL OUTER JOIN

SQL
FULL JOIN table2
PYSPARK
df1.join(df2, df1.id == df2.id, "outer")
Shwetank Singh
SUBQUERIES
SQL
SELECT * FROM (SELECT col1, col2 FROM
table) sub_table;
PYSPARK
sub_df = df.select("col1", "col2")
Shwetank Singh
CASE STATEMENTS
SQL
SELECT col1,
CASE WHEN condition
THEN result ELSE result2 END
FROM table;
PYSPARK
df.select("col1", when(condition,
result).otherwise(result2).alias("new_col"))
Shwetank Singh
WINDOW FUNCTIONS (ROW NUMBER)

SQL
SELECT col1,
ROW_NUMBER() OVER (ORDER BY col2) AS row_num
FROM table;
PYSPARK
df.withColumn("row_num",
row_number().over(Window.orderBy("col2")))
Shwetank Singh
WINDOW FUNCTIONS (AGGREGATIONS)

SQL
SELECT col1,
SUM(col2) OVER (PARTITION BY col3) AS sum_col2
FROM table;
PYSPARK
df.withColumn("sum_col2", sum("col2") \
.over(Window.partitionBy("col3")))
Shwetank Singh
LAG FUNCTION
SQL
SELECT col1,
LAG(col2, 1) OVER (ORDER BY col3)
AS lag_col2 FROM table;
PYSPARK
df.withColumn("lag_col2", lag("col2", 1) \
.over(Window.orderBy("col3")))
Shwetank Singh
LEAD FUNCTION
SQL
SELECT col1,
LEAD(col2, 1) OVER (ORDER BY col3) AS lead_col2
FROM table;
PYSPARK
df.withColumn("lead_col2", lead("col2", 1) \
Shwetank Singh
HANDLING NULLS (IS NULL)

SQL
SELECT * FROM table WHERE col1 IS NULL;
PYSPARK
df.filter(df.col1.isNull())
Shwetank Singh
HANDLING NULLS (IS NOT NULL)

SQL
SELECT * FROM table WHERE col1
IS NOT NULL;
PYSPARK
df.filter(df.col1.isNotNull())
Shwetank Singh
UNION OF TWO TABLES

SQL
UNION
SELECT * FROM table2;
PYSPARK
df1.union(df2)
Shwetank Singh
INTERSECT OF TWO TABLES

SQL
INTERSECT
PYSPARK
df1.intersect(df2)
Shwetank Singh
EXCEPT (DIFFERENCE) OF TWO TABLES

SQL
EXCEPT
PYSPARK
df1.subtract(df2)
Shwetank Singh
CREATING TEMPORARY VIEW

SQL
CREATE TEMP VIEW temp_table
AS SELECT * FROM table;
PYSPARK
df.createOrReplaceTempView("temp_table")
Shwetank Singh
USING SQL QUERIES ON DATAFRAMES

SQL
SELECT * FROM temp_table;
PYSPARK
spark.sql("SELECT * FROM temp_table")
Shwetank Singh
PIVOTING DATA
SQL
SELECT * FROM
(SELECT col1, col2 FROM table)
PIVOT (SUM(col2) FOR col1 IN ('value1', 'value2'));
PYSPARK
df.groupBy()\
.pivot("col1", ['value1', 'value2']).sum("col2")
Shwetank Singh
UPDATING ROWS
SQL
UPDATE table SET col1 = value WHERE
condition;
PYSPARK
df = df.withColumn("col1", when(condition,
value).otherwise(df.col1))
Shwetank Singh
DELETING ROWS
SQL
DELETE FROM table WHERE condition;
PYSPARK
df = df.filter(~condition)
Shwetank Singh
HANDLING DUPLICATES
SQL
SELECT col1, COUNT(*) FROM table
GROUP BY col1 HAVING COUNT(*) > 1;
PYSPARK
df.groupBy("col1").count().filter("count > 1")
Shwetank Singh
CALCULATING PERCENTAGE
SQL
SELECT col1, (col2 / col3) * 100 AS percentage
FROM table;
PYSPARK
df.withColumn("percentage", (df.col2 /
df.col3) * 100)
Shwetank Singh
STRING FUNCTIONS (CONCATENATION)

SQL
SELECT CONCAT(col1, col2) AS new_col
FROM table;
PYSPARK
df.select(concat("col1", "col2") \
.alias("new_col"))
Shwetank Singh
DATE FUNCTIONS (CURRENT DATE)

SQL
SELECT CURRENT_DATE AS today;
PYSPARK
df.select(current_date().alias("today"))
Shwetank Singh
EXTRACTING YEAR FROM DATE

SQL
SELECT EXTRACT(YEAR FROM date_col) AS year
FROM table;
PYSPARK
df.select(year("date_col").alias("year"))
Shwetank Singh
CONDITIONAL AGGREGATION
SQL
SELECT SUM(CASE WHEN condition THEN col1 ELSE 0 END)
FROM table;
PYSPARK
df.select(sum(when(condition, df.col1) \
.otherwise(0)))
Shwetank Singh
RENAMING COLUMNS
SQL
SELECT col1 AS new_col1 FROM table;
PYSPARK
df.withColumnRenamed("col1", "new_col1")
Shwetank Singh
DROPPING COLUMNS
SQL
ALTER TABLE table DROP COLUMN col1;
PYSPARK
df.drop("col1")
Shwetank Singh
ADDING NEW COLUMNS

SQL
ALTER TABLE table
ADD COLUMN new_col data_type;
PYSPARK
df.withColumn("new_col", expression)
Shwetank Singh
REPLACING VALUES
SQL
UPDATE table SET col1 = new_value

WHERE condition;
PYSPARK
df.withColumn("col1", when(condition,
new_value).otherwise(df.col1))
Shwetank Singh
USING UDFS (USER-DEFINED FUNCTIONS)

SQL
CREATE FUNCTION my_udf AS ...;
SELECT my_udf(col1) FROM table;
PYSPARK
df.select(my_udf("col1"))
Shwetank Singh
EXPLODING ARRAYS
SQL
SELECT col1, EXPLODE(array_col) AS exploded_col
FROM table;
PYSPARK
df.select("col1", explode("array_col") \
.alias("exploded_col"))
Shwetank Singh
FLATTENING NESTED DATA

SQL
SELECT col1, nested_col.* FROM table;
PYSPARK
df.select("col1", "nested_col.*")
Shwetank Singh
CASTING DATA TYPES

SQL
SELECT CAST(col1 AS data_type)
FROM table;
PYSPARK
df.select(df.col1.cast("data_type"))
Shwetank Singh
HANDLING JSON DATA

SQL
SELECT JSON_VALUE(json_col, '$.key')
FROM table;
PYSPARK
df.select(get_json_object("json_col", "$.key"))
Shwetank Singh
GROUPING SETS
SQL
SELECT col1, col2, SUM(col3)
FROM table
GROUP BY GROUPING SETS ((col1), (col2));
PYSPARK
df.groupBy("col1",
"col2").agg(sum("col3"))
Shwetank Singh
ROLLUP
SQL
FROM table
GROUP BY ROLLUP(col1, col2);
PYSPARK
df.rollup("col1", "col2") \
.agg(sum("col3"))
Shwetank Singh
CUBE
SQL
FROM table GROUP BY CUBE(col1, col2);
PYSPARK
df.cube("col1", "col2") \
.agg(sum("col3"))
Shwetank Singh
RANK FUNCTION
SQL
SELECT col1,
RANK() OVER (ORDER BY col2) AS rank
FROM table;
PYSPARK
df.withColumn("rank", rank() \
Shwetank Singh
DENSE RANK FUNCTION

SQL
SELECT col1,
DENSE_RANK() OVER (ORDER BY col2) AS dense_rank
FROM table;
PYSPARK
df.withColumn("dense_rank",
dense_rank().over(Window.orderBy("col2")))
Shwetank Singh
CUMULATIVE SUM (RUNNING TOTAL)

SQL
SELECT col1,
SUM(col2) OVER (ORDER BY col1) AS running_total
FROM table;
PYSPARK
df.withColumn("running_total", sum("col2")\
.over(Window.orderBy("col1") \
.rowsBetween(Window.unboundedPreceding,
Window.currentRow)))
Shwetank Singh
HANDLING DATES (DATE DIFFERENCE)

SQL
SELECT DATEDIFF(date1, date2) FROM table;
PYSPARK
df.select(datediff("date1", "date2"))
Shwetank Singh
STRING FUNCTIONS (SUBSTRING)

SQL
SELECT SUBSTRING(col1, start, length)
FROM table;
PYSPARK
df.select(substring("col1", start, length))
Shwetank Singh
UPPER AND LOWER CASE CONVERSION

SQL
SELECT UPPER(col1), LOWER(col2)
FROM table;
PYSPARK
df.select(upper("col1"), lower("col2"))
Shwetank Singh
FILTER WITH IN CLAUSE

SQL
SELECT * FROM table
WHERE col1 IN (value1, value2);
PYSPARK
df.filter(df.col1.isin(value1, value2))
Shwetank Singh
FILTER WITH BETWEEN CLAUSE

SQL
SELECT * FROM table
WHERE col1 BETWEEN value1 AND value2;
PYSPARK
df.filter(df.col1.between(value1, value2))
Shwetank Singh
ORDER BY MULTIPLE COLUMNS

SQL
SELECT * FROM table
ORDER BY col1, col2 DESC;
PYSPARK
df.orderBy("col1", df.col2.desc())
Shwetank Singh

Data Engineering 101 SQL and PySpark 1727161935

Uploaded by

Copyright:

Available Formats

Data Engineering 101 SQL and PySpark 1727161935

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Data Engineering 101 SQL and PySpark 1727161935

Uploaded by

Copyright:

Available Formats

Data

SELECT ALL COLUMNS

SELECT * FROM table;

SELECT SPECIFIC COLUMNS

SELECT col1, col2 FROM table;

FILTERING ROWS (WHERE CLAUSE)

SELECT * FROM table WHERE condition;

SELECT * FROM table ORDER BY col1;

ORDERING ROWS DESCENDING

SELECT * FROM table ORDER BY col1 DESC;

SELECT * FROM table LIMIT 10;

SELECTING DISTINCT VALUES

SELECT DISTINCT col1 FROM table;

GROUP BY AND AGGREGATE

SELECT col1, COUNT(*) FROM table GROUP

GROUP BY AND AGGREGATE (AVG)

SELECT col1, AVG(col2) FROM table GROUP

df1.join(df2, df1.id == df2.id, "inner")

SELECT * FROM table1

df1.join(df2, df1.id == df2.id, "left")

df1.join(df2, df1.id == df2.id, "right")

FULL OUTER JOIN

df1.join(df2, df1.id == df2.id, "outer")

sub_df = df.select("col1", "col2")

WINDOW FUNCTIONS (ROW NUMBER)

WINDOW FUNCTIONS (AGGREGATIONS)

HANDLING NULLS (IS NULL)

SELECT * FROM table WHERE col1 IS NULL;

HANDLING NULLS (IS NOT NULL)

UNION OF TWO TABLES

INTERSECT OF TWO TABLES

EXCEPT (DIFFERENCE) OF TWO TABLES

CREATING TEMPORARY VIEW

USING SQL QUERIES ON DATAFRAMES

SELECT * FROM temp_table;

spark.sql("SELECT * FROM temp_table")

DELETE FROM table WHERE condition;

df.groupBy("col1").count().filter("count > 1")

STRING FUNCTIONS (CONCATENATION)

DATE FUNCTIONS (CURRENT DATE)

SELECT CURRENT_DATE AS today;

EXTRACTING YEAR FROM DATE

SELECT col1 AS new_col1 FROM table;

ALTER TABLE table DROP COLUMN col1;

ADDING NEW COLUMNS

UPDATE table SET col1 = new_value

USING UDFS (USER-DEFINED FUNCTIONS)

FLATTENING NESTED DATA

SELECT col1, nested_col.* FROM table;

CASTING DATA TYPES

HANDLING JSON DATA

DENSE RANK FUNCTION

CUMULATIVE SUM (RUNNING TOTAL)

HANDLING DATES (DATE DIFFERENCE)

SELECT DATEDIFF(date1, date2) FROM table;

STRING FUNCTIONS (SUBSTRING)

df.select(substring("col1", start, length))

UPPER AND LOWER CASE CONVERSION

FILTER WITH IN CLAUSE

FILTER WITH BETWEEN CLAUSE