Thanks I will try with the options
On Feb 16, 2016 9:15 PM, "Mich Talebzadeh" <m...@peridale.co.uk> wrote:
> You can use JDBC to oracle to get that data from a given table. What
> Oracle stored procedure does anyway? How many tables are involved?
>
> JDBC is pretty neat. In example below I use JDBC to load two
> Dimension tables from Oracle in Spark shell and read the FACT table of 100
> million rows from Hive
>
> val HiveContext = new org.apache.spark.sql.hive.HiveContext(sc)
>
> println ("\nStarted at"); HiveContext.sql("SELECT
> FROM_unixtime(unix_timestamp(), 'dd/MM/yyyy HH:mm:ss.ss')
> ").collect.foreach(println)
>
> //
> var _ORACLEserver : String = "jdbc:oracle:thin:@rhes564:1521:mydb"
> var _username : String = "sh"
> var _password : String = "xx"
> //
>
> /Get the FACT table from Hive
> //
> var s = HiveContext.sql("SELECT AMOUNT_SOLD, TIME_ID, CHANNEL_ID FROM
> oraclehadoop.sales")
>
> //Get Oracle tables via JDBC
>
> val c = HiveContext.load("jdbc",
> Map("url" -> _ORACLEserver,
> "dbtable" -> "(SELECT to_char(CHANNEL_ID) AS CHANNEL_ID, CHANNEL_DESC FROM
> sh.channels)",
> "user" -> _username,
> "password" -> _password))
>
> val t = HiveContext.load("jdbc",
> Map("url" -> _ORACLEserver,
> "dbtable" -> "(SELECT TIME_ID AS TIME_ID, CALENDAR_MONTH_DESC FROM
> sh.times)",
> "user" -> _username,
> "password" -> _password))
>
> // Registar three data frames as temporary tables using
> registerTempTable() call
>
> s.registerTempTable("t_s")
> c.registerTempTable("t_c")
> t.registerTempTable("t_t")
> //
> var sqltext : String = ""
> sqltext = """
> SELECT rs.Month, rs.SalesChannel, round(TotalSales,2)
> FROM
> (
> SELECT t_t.CALENDAR_MONTH_DESC AS Month, t_c.CHANNEL_DESC AS SalesChannel,
> SUM(t_s.AMOUNT_SOLD) AS TotalSales
> FROM t_s, t_t, t_c
> WHERE t_s.TIME_ID = t_t.TIME_ID
> AND t_s.CHANNEL_ID = t_c.CHANNEL_ID
> GROUP BY t_t.CALENDAR_MONTH_DESC, t_c.CHANNEL_DESC
> ORDER by t_t.CALENDAR_MONTH_DESC, t_c.CHANNEL_DESC
> ) rs
> LIMIT 10
> """
> HiveContext.sql(sqltext).collect.foreach(println)
> println ("\nFinished at"); HiveContext.sql("SELECT
> FROM_unixtime(unix_timestamp(), 'dd/MM/yyyy HH:mm:ss.ss')
> ").collect.foreach(println)
>
> sys.exit()
>
>
>
> HTH
>
> --
>
> Dr Mich Talebzadeh
>
>
>
> LinkedIn
> 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 Cloud Technology
> Partners 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 Cloud Technology partners Ltd, its
> subsidiaries nor their employees accept any responsibility.
>
> On 16/02/2016 09:04, Gaurav Agarwal wrote:
>
> Hi
> Can I load the data into spark from oracle storedproc
>
> Thanks
>
>
>
>
>
>
>
