<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 <center> Introduction to Spark In-memmory Computing via Python PySpark </center>
Step1:  Airlines Data
Spark SQL
- Spark module for structured data processing
- provide more information about the structure of both the data and the computation being performed for additional optimization
- execute SQL queries written using either a basic SQL syntax or HiveQL
DataFrame
- distributed collection of data organized into named columns
- conceptually equivalent to a table in a relational database or a data frame in R/Python, but with richer optimizations under the hood
- can be constructed from a wide array of sources such as
Step2:  You can interact with a DataFrame via SQLContext using SQL statements by registerting the DataFrame as a table
Step3:  How many unique airlines are there?
Step4:  Calculate how many flights completed by each carrier over time
Step5:  How do you display full carrier names?
Step6:  What is the averaged departure delay time for each airline?
<ASSISTANT_TASK:>
Python Code:
import sys
import os
sys.path.insert(0, '/usr/hdp/2.6.0.3-8/spark2/python')
sys.path.insert(0, '/usr/hdp/2.6.0.3-8/spark2/python/lib/py4j-0.10.4-src.zip')
os.environ['SPARK_HOME'] = '/usr/hdp/2.6.0.3-8/spark2/'
os.environ['SPARK_CONF_DIR'] = '/etc/hadoop/synced_conf/spark2/'
os.environ['PYSPARK_PYTHON'] = '/software/anaconda3/4.2.0/bin/python'
import pyspark
conf = pyspark.SparkConf()
conf.setMaster("yarn")
conf.set("spark.driver.memory","4g")
conf.set("spark.executor.memory","60g")
conf.set("spark.num.executors","3")
conf.set("spark.executor.cores","12")
sc = pyspark.SparkContext(conf=conf)
Explanation: <center> Introduction to Spark In-memmory Computing via Python PySpark </center>
End of explanation
sqlContext = pyspark.SQLContext(sc)
sqlContext
airlines = sqlContext.read.format("com.databricks.spark.csv")\
    .option("header", "true")\
    .option("inferschema", "true")\
    .load("/repository/airlines/data/")\
    .cache()
%%time
airlines.count()
%%time
airlines.count()
airlines.printSchema()
Explanation: Airlines Data
Spark SQL
- Spark module for structured data processing
- provide more information about the structure of both the data and the computation being performed for additional optimization
- execute SQL queries written using either a basic SQL syntax or HiveQL
DataFrame
- distributed collection of data organized into named columns
- conceptually equivalent to a table in a relational database or a data frame in R/Python, but with richer optimizations under the hood
- can be constructed from a wide array of sources such as: structured data files, tables in Hive, external databases, or existing RDDs.
End of explanation
airlines.registerTempTable("airlines")
Explanation: You can interact with a DataFrame via SQLContext using SQL statements by registerting the DataFrame as a table
End of explanation
uniqueAirline = sqlContext.sql("SELECT DISTINCT UniqueCarrier \
                                FROM airlines")
uniqueAirline.show()
Explanation: How many unique airlines are there?
End of explanation
%%time
carrierFlightCount = sqlContext.sql("SELECT UniqueCarrier, COUNT(UniqueCarrier) AS FlightCount \
                                    FROM airlines GROUP BY UniqueCarrier")
carrierFlightCount.show()
Explanation: Calculate how many flights completed by each carrier over time
End of explanation
carriers = sqlContext.read.format("com.databricks.spark.csv")\
    .option("header", "true")\
    .option("inferschema", "true")\
    .load("/repository/airlines/metadata/carriers.csv")\
    .cache()
carriers.registerTempTable("carriers")
carriers.printSchema()
%%time
carrierFlightCountFullName = sqlContext.sql("SELECT c.Description, a.UniqueCarrier, COUNT(a.UniqueCarrier) AS FlightCount \
                                    FROM airlines AS a \
                                    INNER JOIN carriers AS c \
                                    ON c.Code = a.UniqueCarrier \
                                    GROUP BY a.UniqueCarrier, c.Description \
                                    ORDER BY a.UniqueCarrier")
carrierFlightCountFullName.show()
Explanation: How do you display full carrier names?
End of explanation
%%time
avgDepartureDelay = sqlContext.sql("SELECT FIRST(c.Description), FIRST(a.UniqueCarrier), AVG(a.DepDelay) AS AvgDepDelay \
                                    FROM airlines AS a \
                                    INNER JOIN carriers AS c \
                                    ON c.Code = a.UniqueCarrier \
                                    GROUP BY a.UniqueCarrier \
                                    ORDER BY a.UniqueCarrier")
avgDepartureDelay.show()
airlines.unpersist()
sc.stop()
Explanation: What is the averaged departure delay time for each airline?
End of explanation
<END_TASK>