Question: Check the schemas: [ ] gameclicks.printSchema ( ) root T- timestamp: string (nullable true) clickid: string (nullable true) userId: string (nullable = true) userSessionId: string

Check the schemas: [ ] gameclicks.printSchema ( ) root T- timestamp: string (nullable true) clickid: string (nullable true) userId: string (nullable = true) userSessionId: string (nullable = true) ishit: string (nullable true) teamId: string (nullable = true) |-- teamLevel: string (nullable = true) adclicks.printSchema () = E root T- timestamp: string (nullable true) txId: string (nullable true) userSessionId: string (nullable true) teamId: string (nullable = true) userId: string (nullable = true) |-- adid: string (nullable = true) | -- adCategory: string (nullable = true) Question 1: How many users in each team? Keywords: Dataframe API, SQL, group by, sort Use DataFrame API to group the users by teamID and count how many distinct users in each team. Sort the result in descending order. Indented block [ ] team_counts = # your code goes here (gla: 4 points) team_counts.show(). Now rewrite the above question using pure SQL: [ ] gameclicks.registerTemptable("gameclicks") query = # your code goes here (Q1b: 2 points) team_counts = spark.sql(query) team_counts.show() Questions 2: Now use the ad-clicks dataset to find the number of ad clicks in each hour. Keywords: group by, parse timestamp, plot timestamp_only adclicks.selectExpr(["to_timestamp (timestamp) as timestamp"]) click_count_by_hour = # your code goes here (Q2a: 4 points) click_count_by_hour.show(24) Check the schemas: [ ] gameclicks.printSchema ( ) root T- timestamp: string (nullable true) clickid: string (nullable true) userId: string (nullable = true) userSessionId: string (nullable = true) ishit: string (nullable true) teamId: string (nullable = true) |-- teamLevel: string (nullable = true) adclicks.printSchema () = E root T- timestamp: string (nullable true) txId: string (nullable true) userSessionId: string (nullable true) teamId: string (nullable = true) userId: string (nullable = true) |-- adid: string (nullable = true) | -- adCategory: string (nullable = true) Question 1: How many users in each team? Keywords: Dataframe API, SQL, group by, sort Use DataFrame API to group the users by teamID and count how many distinct users in each team. Sort the result in descending order. Indented block [ ] team_counts = # your code goes here (gla: 4 points) team_counts.show(). Now rewrite the above question using pure SQL: [ ] gameclicks.registerTemptable("gameclicks") query = # your code goes here (Q1b: 2 points) team_counts = spark.sql(query) team_counts.show() Questions 2: Now use the ad-clicks dataset to find the number of ad clicks in each hour. Keywords: group by, parse timestamp, plot timestamp_only adclicks.selectExpr(["to_timestamp (timestamp) as timestamp"]) click_count_by_hour = # your code goes here (Q2a: 4 points) click_count_by_hour.show(24)

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

Learn to use Apache Spark with Python Q1c to Q3d Load the datasets: = gameclicks spark.read.csv ("game-clicks.csv.gz", header='true') adclicks = spark.read.csv ("ad-clicks.csv.gz", header='true') +...

(6) PySpark and Spark MLlib (6 marks) (a) Assume the following Data Frame is defined in the PySpark shell. Write down your codes in PySpark shell to fulfil the following operations. 2 3 1 df_RD =...

Consider the following schema of a DataFrame (named retailDF), which is created in the PySpark: root |-- InvoiceNo: string (nullable = true) |-- StockCode: string (nullable = true) |-- Description:...

Schema for green_taxi_zone_df:\ root\ |-- lpep_pickup_datetime: string (nullable = true)\ |-- lpep_dropoff_datetime: string (nullable = true)\ |-- PULocationID: string (nullable = true)\ |--...

Questions Q1 Spark catalog has database(s), local and external tables, functions, etc. Show the current database you are connected in Spark. Q2 List all tables under the current database. Q3 Show all...

Using an Arduino, create a program in C. This program should be able to read values on the provided API. The API provides private grid tariffs. Create a program that reads the values of the API, and...

Consider the following schema of a DataFrame (named packages_DF) created in PySpark: root |-- date: date (nullable = true) |-- time: string (nullable = true) |-- size: integer (nullable = true) |--...

for my School Manager System assignment I stuck at Number 4 of this below table which is registration. I put my all codes plus my code for this table please help me to pass this Services...

What are chance and assignable causes of variability? What part do they play in the operation and interpretation of a Shewhart control chart? Discuss.

1. Normalization works through a series of stages called normal forms. Typically processed. a. 2 . 3 4 d. 5 stages are . 2. Some very specialized applications may require normalization beyond the a....

( 1 5 points ) Assume that you have just borrowed $ 1 0 , 0 0 0 via a 2 - year mortgage with a 1 2 % annual interest rate ( compounded monthly ) . Complete the following amortization schedule for...

On November 1, Aviation Training Corp. borrows $60,000 cash from Community Savings and Loan. Aviation Training signs a three-month, 7% note payable. Interest payable at maturity, Aviation's year-end...

=+j Integrate existing employment laws and regulations, ethical standards, CSR, and corporate governance into IHRM policies and practices.

=+4 Is the decision-making process transparent and open for scrutiny?

=+3 Is the decision green in terms of pollution and the carbon footprint?