Thanks Mich,
we have got it working using the example here under ;)
Mark
> On 11 Jul 2016, at 09:45, Mich Talebzadeh <mich.talebza...@gmail.com> wrote:
>
> Hi Mark,
>
> Hm. It should work. This is Spark 1.6.1 on Oracle 12c
>
>
> scala> val HiveContext = new org.apache.spark.sql.hive.HiveContext(sc)
> HiveContext: org.apache.spark.sql.hive.HiveContext =
> org.apache.spark.sql.hive.HiveContext@70f446c
>
> scala> var _ORACLEserver : String = "jdbc:oracle:thin:@rhes564:1521:mydb12"
> _ORACLEserver: String = jdbc:oracle:thin:@rhes564:1521:mydb12
>
> scala> var _username : String = "sh"
> _username: String = sh
>
> scala> var _password : String = "xxxx"
> _password: String = sh
>
> scala> 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))
> warning: there were 1 deprecation warning(s); re-run with -deprecation for
> details
> c: org.apache.spark.sql.DataFrame = [CHANNEL_ID: string, CHANNEL_DESC: string]
>
> scala> c.registerTempTable("t_c")
>
> scala> c.count
> res2: Long = 5
>
> scala> HiveContext.sql("select * from t_c").collect.foreach(println)
> [3,Direct Sales]
> [9,Tele Sales]
> [5,Catalog]
> [4,Internet]
> [2,Partners]
>
> HTH
>
>
> Dr Mich Talebzadeh
>
> LinkedIn
> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>
> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>
>
> http://talebzadehmich.wordpress.com <http://talebzadehmich.wordpress.com/>
>
> Disclaimer: Use it at your own risk. Any and all responsibility for any loss,
> damage or destruction of data or any other property which may arise from
> relying on this email's technical content is explicitly disclaimed. The
> author will in no case be liable for any monetary damages arising from such
> loss, damage or destruction.
>
>
> On 11 July 2016 at 08:25, Mark Vervuurt <m.a.vervu...@gmail.com
> <mailto:m.a.vervu...@gmail.com>> wrote:
> Hi Mich,
>
> sorry for bothering did you manage to solve your problem? We have a similar
> problem with Spark 1.5.2 using a JDBC connection with a DataFrame to an
> Oracle Database.
>
> Thanks,
> Mark
>
>> On 12 Feb 2016, at 11:45, Mich Talebzadeh <m...@peridale.co.uk
>> <mailto:m...@peridale.co.uk>> wrote:
>>
>> Hi,
>>
>> I use the following to connect to Oracle DB from Spark shell 1.5.2
>>
>> spark-shell --master spark://50.140.197.217:7077 <> --driver-class-path
>> /home/hduser/jars/ojdbc6.jar
>>
>> in Scala I do
>>
>> scala> val sqlContext = new org.apache.spark.sql.SQLContext(sc)
>> sqlContext: org.apache.spark.sql.SQLContext =
>> org.apache.spark.sql.SQLContext@f9d4387
>>
>> scala> val channels = sqlContext.read.format("jdbc").options(
>> | Map("url" -> "jdbc:oracle:thin:@rhes564:1521:mydb",
>> | "dbtable" -> "(select * from sh.channels where channel_id = 14)",
>> | "user" -> "sh",
>> | "password" -> "xxxxxxx")).load
>> channels: org.apache.spark.sql.DataFrame = [CHANNEL_ID: decimal(0,-127),
>> CHANNEL_DESC: string, CHANNEL_CLASS: string, CHANNEL_CLASS_ID:
>> decimal(0,-127), CHANNEL_TOTAL: string, CHANNEL_TOTAL_ID: decimal(0,-127)]
>>
>> scala> channels.count()
>>
>> But the latter command keeps hanging?
>>
>> Any ideas appreciated
>>
>> Thanks,
>>
>> Mich Talebzadeh
>>
>> LinkedIn
>> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
>>
>> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>
>>
>> http://talebzadehmich.wordpress.com <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.
>
> Met vriendelijke groet | Best regards,
> _______________________________________________________________________
>
> Ir. Mark Vervuurt
> Senior Big Data Scientist | Insights & Data
>
> Capgemini Nederland | Utrecht
> Tel.: +31 30 6890978 <tel:%2B31%2030%206890978> – Mob.: +31653670390
> <tel:%2B31653670390>
> www.capgemini.com <http://www.capgemini.com/>
> <apache-spark-certified-175x175.png><HWX_Badges_Cert_Color_Dev.jpeg><image001.gif>
> People matter, results count.
> __________________________________________________________________
>
> <image005.gif>
>
>
