Data Frame for basic transformations¶
Let us understand how to build the Data Frame to explore basic transformations. We wil be creating data frame using air traffic data.
Our air traffic data is in parquet file format.
We can use
spark.read.parquetto create data frame by passing appropriate path which contain air traffic data.We will build the Data Frame using 2008 January data. We will also preview schema as well as data using basic Data Frame functions to begin with.
Let us start spark context for this Notebook so that we can execute the code provided. You can sign up for our 10 node state of the art cluster/labs to learn Spark SQL using our unique integrated LMS.
from pyspark.sql import SparkSession
import getpass
username = getpass.getuser()
spark = SparkSession. \
builder. \
config('spark.ui.port', '0'). \
config("spark.sql.warehouse.dir", f"/user/{username}/warehouse"). \
enableHiveSupport(). \
appName(f'{username} | Python - Basic Transformations'). \
master('yarn'). \
getOrCreate()
If you are going to use CLIs, you can use Spark SQL using one of the 3 approaches.
Using Spark SQL
spark2-sql \
--master yarn \
--conf spark.ui.port=0 \
--conf spark.sql.warehouse.dir=/user/${USER}/warehouse
Using Scala
spark2-shell \
--master yarn \
--conf spark.ui.port=0 \
--conf spark.sql.warehouse.dir=/user/${USER}/warehouse
Using Pyspark
pyspark2 \
--master yarn \
--conf spark.ui.port=0 \
--conf spark.sql.warehouse.dir=/user/${USER}/warehouse
Tasks¶
Let us perform some tasks to understand filtering in detail. Solve all the problems by passing conditions using both SQL Style as well as API Style.
Read the data for the month of 2008 January. We will be using only 2008 January data for the demos.
%%sh
hdfs dfs -ls /public/airtraffic_all/airtraffic-part/flightmonth=200801
Found 1 items
-rw-r--r-- 2 hdfs hdfs 14654075 2021-03-01 14:16 /public/airtraffic_all/airtraffic-part/flightmonth=200801/part-00252-5cde1303-4ebf-4a12-8fad-f5d9f9c9124a.c000.snappy.parquet
airtraffic_path = "/public/airtraffic_all/airtraffic-part/flightmonth=200801"
airtraffic = spark. \
read. \
parquet(airtraffic_path)
airtraffic.printSchema()
root
|-- Year: integer (nullable = true)
|-- Month: integer (nullable = true)
|-- DayofMonth: integer (nullable = true)
|-- DayOfWeek: integer (nullable = true)
|-- DepTime: string (nullable = true)
|-- CRSDepTime: integer (nullable = true)
|-- ArrTime: string (nullable = true)
|-- CRSArrTime: integer (nullable = true)
|-- UniqueCarrier: string (nullable = true)
|-- FlightNum: integer (nullable = true)
|-- TailNum: string (nullable = true)
|-- ActualElapsedTime: string (nullable = true)
|-- CRSElapsedTime: integer (nullable = true)
|-- AirTime: string (nullable = true)
|-- ArrDelay: string (nullable = true)
|-- DepDelay: string (nullable = true)
|-- Origin: string (nullable = true)
|-- Dest: string (nullable = true)
|-- Distance: string (nullable = true)
|-- TaxiIn: string (nullable = true)
|-- TaxiOut: string (nullable = true)
|-- Cancelled: integer (nullable = true)
|-- CancellationCode: string (nullable = true)
|-- Diverted: integer (nullable = true)
|-- CarrierDelay: string (nullable = true)
|-- WeatherDelay: string (nullable = true)
|-- NASDelay: string (nullable = true)
|-- SecurityDelay: string (nullable = true)
|-- LateAircraftDelay: string (nullable = true)
|-- IsArrDelayed: string (nullable = true)
|-- IsDepDelayed: string (nullable = true)
airtraffic.select('Year', 'Month', 'DayOfMonth').distinct().show()
+----+-----+----------+
|Year|Month|DayOfMonth|
+----+-----+----------+
|2008| 1| 28|
|2008| 1| 25|
|2008| 1| 20|
|2008| 1| 11|
|2008| 1| 4|
|2008| 1| 5|
|2008| 1| 15|
|2008| 1| 3|
|2008| 1| 16|
|2008| 1| 9|
|2008| 1| 17|
|2008| 1| 19|
|2008| 1| 12|
|2008| 1| 6|
|2008| 1| 21|
|2008| 1| 18|
|2008| 1| 7|
|2008| 1| 1|
|2008| 1| 26|
|2008| 1| 24|
+----+-----+----------+
only showing top 20 rows
airtraffic.select('Year', 'Month', 'DayOfMonth').distinct().show(31)
+----+-----+----------+
|Year|Month|DayOfMonth|
+----+-----+----------+
|2008| 1| 28|
|2008| 1| 25|
|2008| 1| 20|
|2008| 1| 11|
|2008| 1| 4|
|2008| 1| 5|
|2008| 1| 15|
|2008| 1| 3|
|2008| 1| 16|
|2008| 1| 9|
|2008| 1| 17|
|2008| 1| 19|
|2008| 1| 12|
|2008| 1| 6|
|2008| 1| 21|
|2008| 1| 18|
|2008| 1| 7|
|2008| 1| 1|
|2008| 1| 26|
|2008| 1| 24|
|2008| 1| 23|
|2008| 1| 8|
|2008| 1| 2|
|2008| 1| 14|
|2008| 1| 10|
|2008| 1| 13|
|2008| 1| 27|
|2008| 1| 29|
|2008| 1| 30|
|2008| 1| 22|
|2008| 1| 31|
+----+-----+----------+
airtraffic.select('Year', 'Month', 'DayOfMonth').distinct().count()
31
airtraffic.count()
605659
airtraffic.show()
+----+-----+----------+---------+-------+----------+-------+----------+-------------+---------+-------+-----------------+--------------+-------+--------+--------+------+----+--------+------+-------+---------+----------------+--------+------------+------------+--------+-------------+-----------------+------------+------------+
|Year|Month|DayofMonth|DayOfWeek|DepTime|CRSDepTime|ArrTime|CRSArrTime|UniqueCarrier|FlightNum|TailNum|ActualElapsedTime|CRSElapsedTime|AirTime|ArrDelay|DepDelay|Origin|Dest|Distance|TaxiIn|TaxiOut|Cancelled|CancellationCode|Diverted|CarrierDelay|WeatherDelay|NASDelay|SecurityDelay|LateAircraftDelay|IsArrDelayed|IsDepDelayed|
+----+-----+----------+---------+-------+----------+-------+----------+-------------+---------+-------+-----------------+--------------+-------+--------+--------+------+----+--------+------+-------+---------+----------------+--------+------------+------------+--------+-------------+-----------------+------------+------------+
|2008| 1| 16| 3| 1725| 1735| 1959| 2021| OH| 5367| N716CA| 154| 166| 146| -22| -10| BGR| CVG| 906| 1| 7| 0| null| 0| NA| NA| NA| NA| NA| NO| NO|
|2008| 1| 17| 4| 1717| 1701| 1915| 1855| OH| 4977| N967CA| 118| 114| 101| 20| 16| SYR| CVG| 527| 2| 15| 0| null| 0| 16| 0| 4| 0| 0| YES| YES|
|2008| 1| 17| 4| 1220| 1225| 1440| 1504| OH| 5352| N709CA| 140| 159| 117| -24| -5| SAV| BOS| 901| 8| 15| 0| null| 0| NA| NA| NA| NA| NA| NO| NO|
|2008| 1| 17| 4| 1530| 1530| 1645| 1637| OH| 5426| N779CA| 75| 67| 45| 8| 0| CVG| GRR| 268| 5| 25| 0| null| 0| NA| NA| NA| NA| NA| YES| NO|
|2008| 1| 17| 4| 1203| 1205| 1429| 1429| OH| 5441| N809CA| 86| 84| 58| 0| -2| STL| CVG| 307| 3| 25| 0| null| 0| NA| NA| NA| NA| NA| NO| NO|
|2008| 1| 18| 5| 1150| 1150| 1457| 1524| OH| 5220| N436CA| 127| 154| 102| -27| 0| STL| JFK| 892| 4| 21| 0| null| 0| NA| NA| NA| NA| NA| NO| NO|
|2008| 1| 18| 5| 1215| 1009| 1540| 1251| OH| 5260| N446CA| 145| 102| 140| 169| 126| MCI| CVG| 539| 2| 3| 0| null| 0| 126| 0| 43| 0| 0| YES| YES|
|2008| 1| 19| 6| 835| 835| 1145| 1130| OH| 5276| N523CA| 130| 115| 83| 15| 0| TUL| CVG| 646| 4| 43| 0| null| 0| 0| 0| 15| 0| 0| YES| NO|
|2008| 1| 20| 7| 1925| 1935| 2148| 2124| OH| 5215| N729CA| 143| 109| 34| 24| -10| JFK| PHL| 94| 5| 104| 0| null| 0| 0| 0| 24| 0| 0| YES| NO|
|2008| 1| 20| 7| 825| 830| 1045| 1007| OH| 5324| N933CA| 140| 97| 92| 38| -5| RDU| CVG| 390| 1| 47| 0| null| 0| 0| 0| 38| 0| 0| YES| NO|
|2008| 1| 21| 1| 1638| 1640| 1741| 1754| OH| 4984| N719CA| 63| 74| 45| -13| -2| CVG| DTW| 229| 10| 8| 0| null| 0| NA| NA| NA| NA| NA| NO| NO|
|2008| 1| 21| 1| 1200| 1204| 1600| 1559| OH| 5056| N371CA| 180| 175| 147| 1| -4| MSY| LGA| 1183| 13| 20| 0| null| 0| NA| NA| NA| NA| NA| YES| NO|
|2008| 1| 21| 1| 1950| 1935| 2103| 2124| OH| 5215| N965CA| 73| 109| 29| -21| 15| JFK| PHL| 94| 8| 36| 0| null| 0| NA| NA| NA| NA| NA| NO| YES|
|2008| 1| 21| 1| 1950| 1830| 2225| 2007| OH| 5595| N641CA| 155| 97| 136| 138| 80| DCA| JFK| 213| 10| 9| 0| null| 0| 0| 80| 58| 0| 0| YES| YES|
|2008| 1| 21| 1| 700| 700| 955| 950| OH| 5610| N964CA| 115| 110| 87| 5| 0| HSV| DCA| 613| 8| 20| 0| null| 0| NA| NA| NA| NA| NA| YES| NO|
|2008| 1| 22| 2| 2020| 1910| 2223| 2125| OH| 5032| N538CA| 63| 75| 44| 58| 70| ORD| CVG| 264| 1| 18| 0| null| 0| 0| 58| 0| 0| 0| YES| YES|
|2008| 1| 22| 2| 1320| 1320| 1600| 1528| OH| 5331| N805CA| 160| 128| 102| 32| 0| CVG| JFK| 589| 12| 46| 0| null| 0| 0| 0| 32| 0| 0| YES| NO|
|2008| 1| 23| 3| 908| 908| 1216| 1149| OH| 5033| N963CA| 188| 161| 124| 27| 0| LGA| SAV| 722| 6| 58| 0| null| 0| 0| 0| 27| 0| 0| YES| NO|
|2008| 1| 23| 3| 1245| 1252| 1409| 1430| OH| 5050| N427CA| 84| 98| 73| -21| -7| CLT| CVG| 335| 1| 10| 0| null| 0| NA| NA| NA| NA| NA| NO| NO|
|2008| 1| 23| 3| 630| 635| 840| 831| OH| 5355| N926CA| 130| 116| 85| 9| -5| GSP| LGA| 610| 20| 25| 0| null| 0| NA| NA| NA| NA| NA| YES| NO|
+----+-----+----------+---------+-------+----------+-------+----------+-------------+---------+-------+-----------------+--------------+-------+--------+--------+------+----+--------+------+-------+---------+----------------+--------+------------+------------+--------+-------------+-----------------+------------+------------+
only showing top 20 rows