SQL queries for filtering Table
In the previous exercise, you have run a simple SQL query on a DataFrame. There are more sophisticated queries you can construct to obtain the result that you want and use it for downstream analysis such as data visualization and Machine Learning. In this exercise, we will use the temporary table people
that you have created previously, filter out the rows where the "sex" is male and female and create two DataFrames.
Please note the "solution" is case sensitive for the SQL commands (For example, it only accepts FROM
and not from
). The "solution" only accepts "==" and not "=".
Remember, you already have a SparkSession spark
and a temporary table people
available in your workspace.
This exercise is part of the course
Big Data Fundamentals with PySpark
Exercise instructions
- Filter the
people
table to select all rows where sex is female intopeople_female_df
DataFrame. - Filter the
people
table to select all rows where sex is male intopeople_male_df
DataFrame. - Count the number of rows in both
people_female
andpeople_male
DataFrames.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Filter the people table to select female sex
people_female_df = spark.____('SELECT * FROM ____ WHERE sex=="____"')
# Filter the people table DataFrame to select male sex
people_male_df = spark.____('SELECT * ____ people ____ ____=="____"')
# Count the number of rows in both people_df_female and people_male_df DataFrames
print("There are {} rows in the people_female_df and {} rows in the people_male_df DataFrames".format(people_female_df.____(), people_male_df.____()))