Ok Rajesh This is standalone.
In that case it ought to be at least 4 connections as one executor will use one worker. I am hesitant in here as you can see with (at least) as with Standalone mode you may end up with more executors on each worker. But try it and see whether numPartitions" -> "4" is good or you can change this to something higher. 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 *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 15 August 2016 at 12:19, Madabhattula Rajesh Kumar <mrajaf...@gmail.com> wrote: > Hi Mich, > > Thank you for detailed explanation. One more question > > In my cluster, I have one master and 4 workers. In this case, 4 > connections will be opened to Oracle ? > > Regards, > Rajesh > > On Mon, Aug 15, 2016 at 3:59 PM, Mich Talebzadeh < > mich.talebza...@gmail.com> wrote: > >> It happens that the number of parallel processes open from Spark to RDBMS >> is determined by the number of executors. >> >> I just tested this. >> >> With Yarn client using to executors I see two connections to RDBMS >> >> >> EXECUTIONS USERNAME SID SERIAL# USERS_EXECUTING SQL_TEXT >> ---------- ---------- ------- ------- --------------- >> -------------------------------------------------- >> 1 SCRATCHPAD 443 62565 1 SELECT >> "RANDOMISED","RANDOM_STRING","PADDING","CLU >> >> STERED","ID","SCATTERED","SMALL_VC" FROM (SELECT t >> o_char(ID) AS ID, >> to_char(CLUSTERED) AS CLUSTERED, >> >> to_char(SCATTERED) AS SCATTERED, to_char(RANDOMIS >> ED) AS RANDOMISED, >> RANDOM_STRING, SMALL_VC, PADDIN >> G FROM >> scratchpad.dummy) WHERE ID >= 23000001 AND >> ID < 24000001 >> 1 SCRATCHPAD 406 46793 1 SELECT >> "RANDOMISED","RANDOM_STRING","PADDING","CLU >> >> STERED","ID","SCATTERED","SMALL_VC" FROM (SELECT t >> o_char(ID) AS ID, >> to_char(CLUSTERED) AS CLUSTERED, >> >> to_char(SCATTERED) AS SCATTERED, to_char(RANDOMIS >> ED) AS RANDOMISED, >> RANDOM_STRING, SMALL_VC, PADDIN >> G FROM >> scratchpad.dummy) WHERE ID >= 24000001 AND >> ID < 25000001 >> >> So it sounds like (can someone else independently confirm this) that >> regardless of what one specifies in "numPartitions" one ends up one >> connection from one Spark executor to RDBMS. >> >> 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 >> >> >> *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 15 August 2016 at 09:12, Mich Talebzadeh <mich.talebza...@gmail.com> >> wrote: >> >>> Hi. >>> >>> This is a very good question >>> >>> I did some tests on this. >>> >>> If you are joining two tables then you are creating a result set based >>> on some conditions. In this case what I normally do is to specify an ID >>> column from either tables and will base my partitioning on that ID column. >>> This is pretty straight forward. So bring back your ID column and base you >>> lower and upper limit on that ID value >>> >>> "partitionColumn" -> "ID", >>> "lowerBound" -> "1", >>> "upperBound" -> "100000000", >>> "numPartitions" -> "100", >>> >>> >>> >>> Also I have noticed that regardless of the number of partitions you >>> specify at the RDBMS site, the number of parallel connections will be >>> limited and the result set will be partitioned accordingly. For example >>> with numberPartitions=100, I see only 8 connections in Oracle coming from >>> Spark connection. >>> >>> scala> val s = HiveContext.read.format("jdbc").options( >>> | Map("url" -> _ORACLEserver, >>> | "dbtable" -> "(SELECT to_char(ID) AS ID, to_char(CLUSTERED) AS >>> CLUSTERED, to_char(SCATTERED) AS SCATTERED, to_char(RANDOMISED) AS >>> RANDOMISED, RANDOM_STRING, SMALL_VC, PADDING FROM scratchpad.dummy)", >>> | "partitionColumn" -> "ID", >>> | "lowerBound" -> "1", >>> | "upperBound" -> "100000000", >>> | "numPartitions" -> "100", >>> | "user" -> _username, >>> | "password" -> _password)).load >>> s: org.apache.spark.sql.DataFrame = [ID: string, CLUSTERED: string ... 5 >>> more fields] >>> scala> s.toJavaRDD.partitions.size() >>> res1: Int = 100 >>> >>> This also seems to set the number of partitions. I still think that the >>> emphasis has to be on getting data from RDBMS as quickly as possible. The >>> partitioning does work. In below the login scratchpad has multiple >>> connections to Oracle and does the range selection OK >>> >>> 1 SCRATCHPAD 45 43048 1 SELECT >>> "SMALL_VC","CLUSTERED","PADDING","RANDOM_ST >>> >>> RING","ID","SCATTERED","RANDOMISED" FROM (SELECT t >>> o_char(ID) AS ID, >>> to_char(CLUSTERED) AS CLUSTERED, >>> >>> to_char(SCATTERED) AS SCATTERED, to_char(RANDOMIS >>> ED) AS >>> RANDOMISED, RANDOM_STRING, SMALL_VC, PADDIN >>> G FROM >>> scratchpad.dummy) >>> *WHERE ID >= 16000001 >>> AND ID < 17000001* >>> >>> 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 >>> >>> >>> *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 15 August 2016 at 08:18, ayan guha <guha.a...@gmail.com> wrote: >>> >>>> Hi >>>> >>>> I would suggest you to look at sqoop as well. Essentially, you can >>>> provide a splitBy/partitionBy column using which data will be distributed >>>> among your stated number of mappers >>>> >>>> On Mon, Aug 15, 2016 at 5:07 PM, Madabhattula Rajesh Kumar < >>>> mrajaf...@gmail.com> wrote: >>>> >>>>> Hi Mich, >>>>> >>>>> I have a below question. >>>>> >>>>> I want to join two tables and return the result based on the input >>>>> value. In this case, how we need to specify lower bound and upper bound >>>>> values ? >>>>> >>>>> select t1.id, t1.name, t2.course, t2.qualification from t1, t2 where >>>>> t1.transactionid=*11111* and t1.id = t2.id >>>>> >>>>> *11111 => dynamic input value.* >>>>> >>>>> Regards, >>>>> Rajesh >>>>> >>>>> On Mon, Aug 15, 2016 at 12:05 PM, Mich Talebzadeh < >>>>> mich.talebza...@gmail.com> wrote: >>>>> >>>>>> If you have your RDBMS table partitioned, then you need to consider >>>>>> how much data you want to extract in other words the result set returned >>>>>> by >>>>>> the JDBC call. >>>>>> >>>>>> If you want all the data, then the number of partitions specified in >>>>>> the JDBC call should be equal to the number of partitions in your RDBMS >>>>>> table. >>>>>> >>>>>> 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 >>>>>> >>>>>> >>>>>> *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 14 August 2016 at 21:44, Ashok Kumar <ashok34...@yahoo.com> wrote: >>>>>> >>>>>>> Thank you very much sir. >>>>>>> >>>>>>> I forgot to mention that two of these Oracle tables are range >>>>>>> partitioned. In that case what would be the optimum number of >>>>>>> partitions if >>>>>>> you can share? >>>>>>> >>>>>>> Warmest >>>>>>> >>>>>>> >>>>>>> On Sunday, 14 August 2016, 21:37, Mich Talebzadeh < >>>>>>> mich.talebza...@gmail.com> wrote: >>>>>>> >>>>>>> >>>>>>> If you have primary keys on these tables then you can parallelise >>>>>>> the process reading data. >>>>>>> >>>>>>> You have to be careful not to set the number of partitions too many. >>>>>>> Certainly there is a balance between the number of partitions supplied >>>>>>> to >>>>>>> JDBC and the load on the network and the source DB. >>>>>>> >>>>>>> Assuming that your underlying table has primary key ID, then this >>>>>>> will create 20 parallel processes to Oracle DB >>>>>>> >>>>>>> val d = HiveContext.read.format("jdbc").options( >>>>>>> Map("url" -> _ORACLEserver, >>>>>>> "dbtable" -> "(SELECT <COL1>, <COL2>, ....FROM <TABLE>)", >>>>>>> "partitionColumn" -> "ID", >>>>>>> "lowerBound" -> "1", >>>>>>> "upperBound" -> "maxID", >>>>>>> "numPartitions" -> "20", >>>>>>> "user" -> _username, >>>>>>> "password" -> _password)).load >>>>>>> >>>>>>> assuming your upper bound on ID is maxID >>>>>>> >>>>>>> >>>>>>> This will open multiple connections to RDBMS, each getting a subset >>>>>>> of data that you want. >>>>>>> >>>>>>> You need to test it to ensure that you get the numPartitions optimum >>>>>>> and you don't overload any component. >>>>>>> >>>>>>> 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 >>>>>>> >>>>>>> *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 14 August 2016 at 21:15, Ashok Kumar < >>>>>>> ashok34...@yahoo.com.invalid> wrote: >>>>>>> >>>>>>> Hi, >>>>>>> >>>>>>> There are 4 tables ranging from 10 million to 100 million rows but >>>>>>> they all have primary keys. >>>>>>> >>>>>>> The network is fine but our Oracle is RAC and we can only connect to >>>>>>> a designated Oracle node (where we have a DQ account only). >>>>>>> >>>>>>> We have a limited time window of few hours to get the required data >>>>>>> out. >>>>>>> >>>>>>> Thanks >>>>>>> >>>>>>> >>>>>>> On Sunday, 14 August 2016, 21:07, Mich Talebzadeh < >>>>>>> mich.talebza...@gmail.com> wrote: >>>>>>> >>>>>>> >>>>>>> How big are your tables and is there any issue with the network >>>>>>> between your Spark nodes and your Oracle DB that adds to issues? >>>>>>> >>>>>>> HTH >>>>>>> >>>>>>> Dr Mich Talebzadeh >>>>>>> >>>>>>> LinkedIn * https://www.linkedin.com/ profile/view?id= >>>>>>> AAEAAAAWh2gBxianrbJd6zP6AcPCCd OABUrV8Pw >>>>>>> <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 14 August 2016 at 20:50, Ashok Kumar < >>>>>>> ashok34...@yahoo.com.invalid> wrote: >>>>>>> >>>>>>> Hi Gurus, >>>>>>> >>>>>>> I have few large tables in rdbms (ours is Oracle). We want to access >>>>>>> these tables through Spark JDBC >>>>>>> >>>>>>> What is the quickest way of getting data into Spark Dataframe say >>>>>>> multiple connections from Spark >>>>>>> >>>>>>> thanking you >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>>> >>>> -- >>>> Best Regards, >>>> Ayan Guha >>>> >>> >>> >> >
