Spark_SQL

December 5, 2021

1 Programme Spark SQL

- Importer les librairies
[ ]: from pyspark import SparkContext
from [Link] import SQLContext

- Créer le spark context ”sc” sur la machine local ”local” avec 2 THreads avec une application
nommée SQLExercice
[ ]: sc = SparkContext("local[2]","SQLExercice")

- Créer le SQL spark context ”sqlContext”

[ ]: sqlContext = SQLContext(sc)

[ ]: - Charger le fichier supermarket_sales.csv dans le csv_data

[ ]: data_file = "/supermarket_sales.csv"
csv_data= [Link](data_file)
csv_data.take(5)
#type(csv_data)

Afficher l’entete du fichier csv

[7]: header = csv_data.first()
header

[7]: 'Invoice ID,Branch,City,Customer type,Gender,Product line,Unit

price,Quantity,Tax 5%,Total,Date,Time,Payment,cogs,gross margin percentage,gross
income,Rating'

- Charger le fichier dans le dataframe df.

[8]: df=[Link](data_file, header=True)#display data from the dataframe
#[Link]()
#[Link]()
[Link]()

1
[8]: 1000

- Afficher le schéma du dataframe df

[9]: [Link]()

root
|-- Invoice ID: string (nullable = true)
|-- Branch: string (nullable = true)
|-- City: string (nullable = true)
|-- Customer type: string (nullable = true)
|-- Gender: string (nullable = true)
|-- Product line: string (nullable = true)
|-- Unit price: string (nullable = true)
|-- Quantity: string (nullable = true)
|-- Tax 5%: string (nullable = true)
|-- Total: string (nullable = true)
|-- Date: string (nullable = true)
|-- Time: string (nullable = true)
|-- Payment: string (nullable = true)
|-- cogs: string (nullable = true)
|-- gross margin percentage: string (nullable = true)
|-- gross income: string (nullable = true)
|-- Rating: string (nullable = true)

- Afficher le contenu de la colonne ”City”

[10]: [Link]("City").show()

+---------+
| City|
+---------+
| Yangon|
|Naypyitaw|
| Yangon|
| Yangon|
| Yangon|
|Naypyitaw|
| Yangon|
|Naypyitaw|
| Yangon|
| Mandalay|
| Mandalay|
| Mandalay|
| Yangon|
| Yangon|
| Yangon|
| Mandalay|

2
 | Yangon|
| Yangon|
| Yangon|
| Mandalay|
+---------+
only showing top 20 rows

[ ]: - Afficher le contenu des colonnes "Invoice ID","City","Customer type"

[11]: [Link]("Invoice ID","City","Customer type").show()

+-----------+---------+-------------+
| Invoice ID| City|Customer type|
+-----------+---------+-------------+
|750-67-8428| Yangon| Member|
|226-31-3081|Naypyitaw| Normal|
|631-41-3108| Yangon| Normal|
|123-19-1176| Yangon| Member|
|373-73-7910| Yangon| Normal|
|699-14-3026|Naypyitaw| Normal|
|355-53-5943| Yangon| Member|
|315-22-5665|Naypyitaw| Normal|
|665-32-9167| Yangon| Member|
|692-92-5582| Mandalay| Member|
|351-62-0822| Mandalay| Member|
|529-56-3974| Mandalay| Member|
|365-64-0515| Yangon| Normal|
|252-56-2699| Yangon| Normal|
|829-34-3910| Yangon| Normal|
|299-46-1805| Mandalay| Member|
|656-95-9349| Yangon| Member|
|765-26-6951| Yangon| Normal|
|329-62-1586| Yangon| Normal|
|319-50-3348| Mandalay| Normal|
+-----------+---------+-------------+
only showing top 20 rows

- Afficher le contenu de la colonne ”Customer type” dont la valeur est ”Normal”

[14]: [Link](df["Customer type"]=="Normal").show()

+-----------+------+---------+-------------+------+--------------------+--------
--+--------+-------+--------+---------+-----+-----------+------+----------------
-------+------------+------+
| Invoice ID|Branch| City|Customer type|Gender| Product line|Unit
price|Quantity| Tax 5%| Total| Date| Time| Payment| cogs|gross margin

3
percentage|gross income|Rating|
+-----------+------+---------+-------------+------+--------------------+--------
--+--------+-------+--------+---------+-----+-----------+------+----------------
-------+------------+------+
|226-31-3081| C|Naypyitaw| Normal|Female|Electronic access…|
15.28| 5| 3.82| 80.22| 3/8/2019|10:29| Cash| 76.4|
4.761904762| 3.82| 9.6|
|631-41-3108| A| Yangon| Normal| Male| Home and lifestyle|
46.33| 7|16.2155|340.5255| 3/3/2019|13:23|Credit card|324.31|
4.761904762| 16.2155| 7.4|
|373-73-7910| A| Yangon| Normal| Male| Sports and travel|
86.31| 7|30.2085|634.3785| 2/8/2019|10:37| Ewallet|604.17|
4.761904762| 30.2085| 5.3|
|699-14-3026| C|Naypyitaw| Normal| Male|Electronic access…|
85.39| 7|29.8865|627.6165|3/25/2019|18:30| Ewallet|597.73|
4.761904762| 29.8865| 4.1|
|315-22-5665| C|Naypyitaw| Normal|Female| Home and lifestyle|
73.56| 10| 36.78| 772.38|2/24/2019|11:38| Ewallet| 735.6|
4.761904762| 36.78| 8|
|365-64-0515| A| Yangon| Normal|Female|Electronic access…|
46.95| 5|11.7375|246.4875|2/12/2019|10:25| Ewallet|234.75|
4.761904762| 11.7375| 7.1|
|252-56-2699| A| Yangon| Normal| Male| Food and beverages|
43.19| 10| 21.595| 453.495| 2/7/2019|16:48| Ewallet| 431.9|
4.761904762| 21.595| 8.2|
|829-34-3910| A| Yangon| Normal|Female| Health and beauty|
71.38| 10| 35.69| 749.49|3/29/2019|19:21| Cash| 713.8|
4.761904762| 35.69| 5.7|
|765-26-6951| A| Yangon| Normal| Male| Sports and travel|
72.61| 6| 21.783| 457.443| 1/1/2019|10:39|Credit card|435.66|
4.761904762| 21.783| 6.9|
|329-62-1586| A| Yangon| Normal| Male| Food and beverages|
54.67| 3| 8.2005|172.2105|1/21/2019|18:00|Credit card|164.01|
4.761904762| 8.2005| 8.6|
|319-50-3348| B| Mandalay| Normal|Female| Home and lifestyle|
40.3| 2| 4.03| 84.63|3/11/2019|15:30| Ewallet| 80.6|
4.761904762| 4.03| 4.4|
|371-85-5789| B| Mandalay| Normal| Male| Health and beauty|
87.98| 3| 13.197| 277.137| 3/5/2019|10:40| Ewallet|263.94|
4.761904762| 13.197| 5.1|
|273-16-6619| B| Mandalay| Normal| Male| Home and lifestyle|
33.2| 2| 3.32| 69.72|3/15/2019|12:20|Credit card| 66.4|
4.761904762| 3.32| 4.4|
|636-48-8204| A| Yangon| Normal| Male|Electronic access…|
34.56| 5| 8.64| 181.44|2/17/2019|11:15| Ewallet| 172.8|
4.761904762| 8.64| 9.9|
|649-29-6775| B| Mandalay| Normal| Male| Fashion accessories|
33.52| 1| 1.676| 35.196| 2/8/2019|15:31| Cash| 33.52|

4
 4.761904762| 1.676| 6.7|
|189-17-4241| A| Yangon| Normal|Female| Fashion accessories|
87.67| 2| 8.767| 184.107|3/10/2019|12:17|Credit card|175.34|
4.761904762| 8.767| 7.7|
|145-94-9061| B| Mandalay| Normal|Female| Food and beverages|
88.36| 5| 22.09| 463.89|1/25/2019|19:48| Cash| 441.8|
4.761904762| 22.09| 9.6|
|848-62-7243| A| Yangon| Normal| Male| Health and beauty|
24.89| 9|11.2005|235.2105|3/15/2019|15:36| Cash|224.01|
4.761904762| 11.2005| 7.4|
|871-79-8483| B| Mandalay| Normal| Male| Fashion accessories|
94.13| 5|23.5325|494.1825|2/25/2019|19:39|Credit card|470.65|
4.761904762| 23.5325| 4.8|
|640-49-2076| B| Mandalay| Normal| Male| Sports and travel|
83.78| 8| 33.512| 703.752|1/10/2019|14:49| Cash|670.24|
4.761904762| 33.512| 5.1|
+-----------+------+---------+-------------+------+--------------------+--------
--+--------+-------+--------+---------+-----+-----------+------+----------------
-------+------------+------+
only showing top 20 rows

- Importer la librairie IntegerType

[16]: from [Link] import IntegerType

- Transformer le type string de la colonne ”Total” en une colonne de type Integer

[18]: df = [Link]("Total",df["Total"].cast(IntegerType()))

[19]: [Link]()

root
|-- Invoice ID: string (nullable = true)
|-- Branch: string (nullable = true)
|-- City: string (nullable = true)
|-- Customer type: string (nullable = true)
|-- Gender: string (nullable = true)
|-- Product line: string (nullable = true)
|-- Unit price: string (nullable = true)
|-- Quantity: string (nullable = true)
|-- Tax 5%: string (nullable = true)
|-- Total: integer (nullable = true)
|-- Date: string (nullable = true)
|-- Time: string (nullable = true)
|-- Payment: string (nullable = true)
|-- cogs: string (nullable = true)
|-- gross margin percentage: string (nullable = true)
|-- gross income: string (nullable = true)

5
 |-- Rating: string (nullable = true)

- Grouper par ”Branch” et faire la somme

[21]: #[Link]("BRanch").show()
[Link]("Branch").sum("Total").show()

+------+----------+
|Branch|sum(Total)|
+------+----------+
| B| 106032|
| C| 110408|
| A| 106029|
+------+----------+

- Importer les librairies : sum, avg, max, min, mean, count

[23]: from [Link] import sum, avg, max, min, mean, count

- Grouper par ”Branch” et faire la somme, la moyenne, le min et le max

[24]: [Link]("Branch").agg(sum("Total").alias("total")\
,avg("Total").alias("total_avg")\
,min("Total").alias("total_min")\
,max("Total").alias("total_avg")).show()

+------+------+-----------------+---------+---------+
|Branch| total| total_avg|total_min|total_avg|
+------+------+-----------------+---------+---------+
| B|106032|319.3734939759036| 18| 1022|
| C|110408| 336.609756097561| 10| 1042|
| A|106029| 311.85| 12| 1039|
+------+------+-----------------+---------+---------+

- Afficher la somme du total des branches ”A”

[25]: [Link]("Branch").sum("Total").where(df["Branch"]=="A").show()

+------+----------+
|Branch|sum(Total)|
+------+----------+
| A| 106029|
+------+----------+

- Grouper par ”Branch” et faire la somme, la moyenne, le min et le max et faire un affichage par
ordre croissant

6
[26]: [Link]("Branch").agg(sum("Total").alias("total")\
,avg("Total").alias("total_avg")\
,min("Total").alias("total_min")\
,max("Total").alias("total_avg"))\
.orderBy("total").show()

+------+------+-----------------+---------+---------+
|Branch| total| total_avg|total_min|total_avg|
+------+------+-----------------+---------+---------+
| A|106029| 311.85| 12| 1039|
| B|106032|319.3734939759036| 18| 1022|
| C|110408| 336.609756097561| 10| 1042|
+------+------+-----------------+---------+---------+