([Link]
com)
main (Python)
Import notebook
Section 1
df_dilevery = [Link](header=True).csv('/FileStore/tables/fooddelivery/food_delivery.csv')
rdd = df_dilevery.rdd
distinct_customers = [Link](lambda row: row['CustomerName']).distinct().count()
print("Total distinct count", distinct_customers)
df_dilevery: [Link] = [DeliveryID: string, OrderID: string ... 8 more fields]
Total distinct count 100
from collections import Counter
df_sales = [Link]('/FileStore/tables/fooddelivery/food_delivery.csv')
rdd_dilevery = df_dilevery.rdd
rdd_payments = rdd_dilevery.map(lambda x: (x['CustomerName'], x['PaymentMethod']))
rdd_grouped = rdd_payments.groupByKey().map(lambda x: Counter(x).most_common(1)[0][0])
rdd_grouped.collect()
df_sales: [Link] = [_c0: string, _c1: string ... 8 more fields]
'Olivia Smith',
'William Moore',
'Emily Wilson',
'Sarah Jones',
'Emma Davis',
'Sarah Doe',
'Daniel Wilson',
'Emma Wilson',
'Jane Brown',
'Emily Taylor',
'Daniel Moore',
'Michael Jones',
'Jane Moore',
'Sarah Smith',
'David Taylor',
'Emma Johnson',
'Emily Moore',
'Michael Taylor',
'Jane Davis',
'Jane Taylor',
'David Davis']
df_dilevery.createOrReplaceTempView("food_delivery")
[Link]("""
SELECT AVG(delivery_count) as avg_delivery_count
FROM (
SELECT DriverID, COUNT(*) as delivery_count
FROM food_delivery
GROUP BY DriverID)
""").show()
+------------------+
|avg_delivery_count|
+------------------+
| 100.0|
+------------------+
[Link]("""
SELECT * FROM food_delivery
WHERE DeliveryID is NULL OR OrderID is NULL OR CustomerName is NULL OR DeliveryDate is NULL
OR DriverID is NULL OR RestaurantID is NULL OR DeliveryStatus is NULL OR PaymentMethod is NULL OR
DeliveryAddress is NULL OR CustomerPhone is NULL
""").show()
+----------+-------+------------+------------+--------+------------+--------------+-------------+--------------
-+-------------+
|DeliveryID|OrderID|CustomerName|DeliveryDate|DriverID|RestaurantID|DeliveryStatus|PaymentMethod|DeliveryAddres
s|CustomerPhone|
+----------+-------+------------+------------+--------+------------+--------------+-------------+--------------
-+-------------+
+----------+-------+------------+------------+--------+------------+--------------+-------------+--------------
-+-------------+
Section 2
df_sales = [Link](header=True).csv('/FileStore/tables/fooddelivery/food_sales.csv')
df_dilevery.[Link]("delta").mode("overwrite").save('/delta/foodDelivery')
df_sales.[Link]("delta").mode("overwrite").save('/delta/foodSales')
df_dilevery.createOrReplaceTempView("food_delivery_delta_2")
df_sales.createOrReplaceTempView("food_sales_delta_2")
df_sales: [Link]
OrderID: string
MenuItem: string
Quantity: string
PricePerItem: string
TotalPrice: string
Discount: string
Tax: string
OrderDate: string
[Link]("""
SELECT [Link], AVG([Link]) AS avg_order_value
FROM food_delivery_delta_2 d
JOIN food_sales_delta_2 s
ON [Link] = [Link]
GROUP BY [Link];""")
DataFrame[PaymentMethod: string, avg_order_value: double]
%sql
VACUUM delta.`/delta/foodSales` RETAIN 168 HOURS
_sqldf: [Link] = [path: string]
Table
This result is stored as _sqldf and can be used in other Python and SQL cells.
Section 3
df_stream = ([Link]("header", True)
.option("inferSchema", True)
.schema(df_sales.schema)
.csv("/FileStore/tables/fooddelivery/streaming/"))
df_stream.[Link]("delta").outputMode("append").option("checkpointLocation",
"/delta/foodSales_streaming_checkpoint").table("foodSales_streaming")
8de0aa05-cc92-4415-a982-ceae424856f3 Last updated: 68 days ago
df_stream: [Link] = [OrderID: string, MenuItem: string ... 6 more fields]
<[Link] at 0x7fa43c591550>
%sql
SELECT MenuItem, SUM(Quantity) as totalQuantity FROM foodSales_streaming GROUP BY MenuItem
_sqldf: [Link] = [MenuItem: string, totalQuantity: double]
Table
This result is stored as _sqldf and can be used in other Python and SQL cells.
%sql
SELECT MenuItem, SUM(Quantity) as totalQuantity FROM foodSales_streaming GROUP BY MenuItem
_sqldf: [Link] = [MenuItem: string, totalQuantity: double]
Table
This result is stored as _sqldf and can be used in other Python and SQL cells.
_sqldf: [Link] = [path: string, metrics: struct]
Table
This result is stored as _sqldf and can be used in other Python and SQL cells.