Celebal-Task/sqlzoo_chapter6.scala at master · tushargoyal02/Celebal-Task · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
// Databricks notebook source
//  File uploaded to /FileStore/tables/eteam.csv
// File uploaded to /FileStore/tables/goal.csv
//  File uploaded to /FileStore/tables/game.csv

import org.apache.spark.sql.functions._


// COMMAND ----------

val eteam_df = spark.read.format("csv").option("header","true").option("inferSchema","true").load("/FileStore/tables/eteam.csv")
eteam_df.show()

// COMMAND ----------

val goal_df = spark.read.format("csv").option("header","true").option("inferSchema","true").load("/FileStore/tables/goal.csv")
goal_df.show()

// COMMAND ----------

val game_df = spark.read.format("csv").option("header","true").option("inferSchema","true").load("/FileStore/tables/game.csv")
game_df.show()

// COMMAND ----------

// DBTITLE 1,Inner join between two tables
// THIS IS FOR PART 3
// APPLYING THE INNER JOIN BETWEEN TWO TABLES

game_df.select($"*").join(goal_df, $"id"===$"matchid", "inner").where($"teamid" === "GRE").show()

// COMMAND ----------

// THIS IS FOR PART 4

game_df.join(goal_df, $"id"===$"matchid").select($"team1",$"team2", $"player").where($"player".like("Robert%")).show()

// COMMAND ----------

// THIS IS FOR PART 5
goal_df.join(eteam_df, $"id"===$"teamid", "inner").filter($"gtime"  <= 18).select($"player",$"teamid",$"coach",$"gtime").show()

// COMMAND ----------

// THI IS FOR PART 6
//   FIRST JOINNING THE TWO DATAFRAME
game_df.join(eteam_df).where( game_df("team1") === eteam_df("id") )
//     FILTERING THE DATA
.where(eteam_df("coach") === "Fernando Santos" )
.select($"mdate",$"teamname").show()

// COMMAND ----------

 import sqlContext.implicits._

// COMMAND ----------

// THIS IS FOR PART 8
game_df.join(goal_df, $"matchid"===$"id")
.where( ($"team1" ==="GRE" || $"team2" ==="GRE") && $"teamid"!="GER" )
.select($"player").distinct().show()

// COMMAND ----------

// THIS IS FOR PART 10
game_df.join(goal_df, $"id"===$"matchid").groupBy($"stadium").agg(count("*")).show()

// COMMAND ----------

// THIS IS FOR PART 11
game_df.join(goal_df,$"id"===$"matchid").where($"team1"==="POL" || $"team2"==="POL")
.groupBy($"mdate",$"matchid").agg(count($"gtime")).show()