Spark SQL DataFrame Lab Preparation Guide
This document summarizes the key Spark SQL topics (Column Operations, Basic Operations,
Basic Aggregations)
you need for the lab as per Nacho's email. It includes code examples, explanations, and likely
tasks.
------------------------------------------------------------
1. Spark Setup Example
------------------------------------------------------------
```python
from [Link] import SparkSession
from [Link] import *
spark = [Link]("SparkSQL_Lab").getOrCreate()
```
Creates the main Spark entry point for DataFrame and SQL operations.
------------------------------------------------------------
2. Creating a DataFrame
------------------------------------------------------------
```python
data = [("John", 25, "Ireland"), ("Mary", 30, "Spain"), ("Ali", 35, "France")]
columns = ["Name", "Age", "Country"]
df = [Link](data, columns)
[Link]()
```
Output:
```
+----+---+--------+
|Name|Age|Country |
+----+---+--------+
|John| 25|Ireland |
|Mary| 30|Spain |
|Ali | 35|France |
+----+---+--------+
```
■ Creates a small DataFrame for testing. Each tuple becomes a row.
------------------------------------------------------------
3. Column Operations
------------------------------------------------------------
Modify or add new columns.
### 3.1 Drop a Column
```python
df2 = [Link]("Country")
[Link]()
```
Removes unwanted columns.
### 3.2 Rename a Column
```python
df3 = [Link]("Name", "FullName")
```
Renames columns.
### 3.3 Add a New Column (Expression)
```python
df4 = [Link]("AgePlus5", col("Age") + 5)
[Link]()
```
Adds a calculated column.
### 3.4 Add Constant Column
```python
df5 = [Link]("City", lit("Cork"))
```
Adds a new column with same value for all rows.
### 3.5 Add Conditional Column
```python
df6 = [Link]("Category", when(col("Age") >= 30, "Senior").otherwise("Junior"))
[Link]()
```
Adds a column with conditional logic (like IF-ELSE).
### 3.6 Add Column using User Defined Function (UDF)
```python
from [Link] import StringType
def label(age): return "Adult" if age >= 18 else "Minor"
label_udf = udf(label, StringType())
df7 = [Link]("Label", label_udf(col("Age")))
```
Adds a custom Python function to process each row.
------------------------------------------------------------
4. Basic Operations
------------------------------------------------------------
Used for selecting, filtering, and sorting data.
### 4.1 Select Specific Columns
```python
df_select = [Link]("Name", "Age")
df_select.show()
```
Shows only chosen columns.
### 4.2 Filter Rows
```python
df_filter = [Link](col("Age") > 28)
df_filter.show()
```
Keeps only rows meeting a condition.
### 4.3 Drop Duplicates
```python
df_unique = [Link](["Name"])
```
Removes repeated entries based on given columns.
### 4.4 Sort / Order By
```python
df_sorted = [Link](col("Age").desc())
df_sorted.show()
```
Sorts data ascending or descending.
------------------------------------------------------------
5. Basic Aggregations
------------------------------------------------------------
Perform grouping and summary operations.
### 5.1 Count Rows
```python
count_rows = [Link]()
print(count_rows)
```
Returns total number of rows.
### 5.2 GroupBy and Aggregate
```python
df_group = [Link]("Country").agg(
avg("Age").alias("AvgAge"),
count("*").alias("NumPeople")
)
df_group.show()
```
Computes average age and number of people per country.
### 5.3 Order Aggregated Results
```python
df_ordered = df_group.orderBy(col("AvgAge").desc())
df_ordered.show()
```
Sorts results by a calculated column.
------------------------------------------------------------
6. Actions
------------------------------------------------------------
Actions trigger actual computation.
| Action | Description |
|---------|--------------|
| `show()` | Displays rows in tabular format |
| `count()` | Counts total rows |
| `collect()` | Returns data as list to Python (only for small datasets) |
------------------------------------------------------------
7. Full Example - Typical Lab Code
------------------------------------------------------------
```python
from [Link] import SparkSession
from [Link] import *
def my_main(spark, input_path):
df = [Link](input_path, header=True, inferSchema=True)
[Link]()
Column Operations
df = [Link]("delay", "delay_seconds")
df = [Link]("delay_minutes", col("delay_seconds") / 60)
df = [Link]("Status", when(col("delay_minutes") > 1, "Late").otherwise("On time"))
Basic Operations
df_filtered = [Link](col("bus_line") == 40)
df_unique = df_filtered.dropDuplicates(["vehicle_id"])
df_sorted = df_unique.orderBy(col("delay_minutes").desc())
Aggregations
df_summary = df_sorted.groupBy("bus_line").agg(
avg("delay_minutes").alias("avg_delay"),
count("*").alias("num_records")
)
Actions
df_summary.show()
if __name__ == '__main__':
spark = [Link]("Lab_SparkSQL").getOrCreate()
my_main(spark, "../datasets/[Link]")
```
■ Typical lab pattern:
1. Load dataset.
2. Add or modify columns.
3. Filter, deduplicate, sort.
4. Group and summarize.
5. Display results.
------------------------------------------------------------
8. What Professor Might Ask
------------------------------------------------------------
- Create or load a DataFrame using `[Link]()`.
- Drop unnecessary columns or rename them.
- Add new columns with computed or constant values.
- Filter by conditions (e.g., delay > 0 or bus_line == 40).
- Remove duplicates using `dropDuplicates()`.
- Group by a field and calculate `avg()`, `sum()`, or `count()`.
- Sort aggregated results using `orderBy()`.
- Show final output with `show()`.
------------------------------------------------------------
9. Key Concepts to Remember
------------------------------------------------------------
| Concept | Spark Function | Example |
|----------|----------------|----------|
| Create DF | `[Link]()` | create small DataFrame manually |
| Add Column | `withColumn()` | Add new column |
| Rename Column | `withColumnRenamed()` | Rename existing column |
| Drop Column | `drop()` | Remove unwanted column |
| Filter Rows | `filter()` | Keep rows meeting condition |
| Group + Aggregate | `groupBy().agg()` | Summarize grouped data |
| Sort | `orderBy()` | Sort results |
| Actions | `show()`, `count()`, `collect()` | Trigger computation |
------------------------------------------------------------
End of Document.