What do you mean "does not work" ? What's the error message ? BTW would it
be simpler that register the 3 data frames as temporary table and then use
the sql query you used before in hive and oracle ?
On Sun, Feb 14, 2016 at 9:28 AM, Mich Talebzadeh <m...@peridale.co.uk>
wrote:
> Hi,
>
>
>
> I have created DFs on three Oracle tables.
>
>
>
> The join in Hive and Oracle are pretty simple
>
>
>
> SELECT t.calendar_month_desc, c.channel_desc, SUM(s.amount_sold) AS
> TotalSales
>
> FROM sales s, times t, channels c
>
> WHERE s.time_id = t.time_id
>
> AND s.channel_id = c.channel_id
>
> GROUP BY t.calendar_month_desc, c.channel_desc
>
> ;
>
>
>
> I try to do this using Data Framess
>
>
>
>
>
> import org.apache.spark.sql.functions._
>
>
>
> val sqlContext = new org.apache.spark.sql.SQLContext(sc)
>
> //
>
> val s = sqlContext.load("jdbc",
>
> Map("url" -> "jdbc:oracle:thin:@rhes564:1521:mydb",
>
> "dbtable" -> "(sh.sales)",
>
> "user" -> "sh",
>
> "password" -> "xxxxx"))
>
> //
>
> val c = sqlContext.load("jdbc",
>
> Map("url" -> "jdbc:oracle:thin:@rhes564:1521:mydb",
>
> "dbtable" -> "(sh.channels)",
>
> "user" -> "sh",
>
> "password" -> "xxxxx"))
>
>
>
> val t = sqlContext.load("jdbc",
>
> Map("url" -> "jdbc:oracle:thin:@rhes564:1521:mydb",
>
> "dbtable" -> "(sh.times)",
>
> "user" -> "sh",
>
> "password" -> "xxxxx"))
>
> //
>
> val sc = s.join(c, s.col("CHANNEL_ID") === c.col("CHANNEL_ID"))
>
> val st = s.join(t, s.col("TIME_ID") === t.col("TIME_ID"))
>
>
>
> val rs = sc.join(st)
>
>
>
> rs.groupBy($"calendar_month_desc",$"channel_desc").agg(sum($"amount_sold"))
>
>
>
> The las result set (rs) does not work.
>
>
>
> Since data is imported then I assume that the columns for joins need to be
> defined in data frame for each table rather than importing the whole
> columns.
>
>
>
> Thanks,
>
>
>
>
>
> Mich Talebzadeh
>
>
>
> LinkedIn *
> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> NOTE: The information in this email is proprietary and confidential. This
> message is for the designated recipient only, if you are not the intended
> recipient, you should destroy it immediately. Any information in this
> message shall not be understood as given or endorsed by Peridale Technology
> Ltd, its subsidiaries or their employees, unless expressly so stated. It is
> the responsibility of the recipient to ensure that this email is virus
> free, therefore neither Peridale Technology Ltd, its subsidiaries nor their
> employees accept any responsibility.
>
>
>
>
>
--
Best Regards
Jeff Zhang
