I am Using numPartitions, lowerBound, upperBound in Spark Dataframe to fetch large tables from oracle to hive but unable to ingest complete data

Multi tool use
Multi tool use


I am Using numPartitions, lowerBound, upperBound in Spark Dataframe to fetch large tables from oracle to hive but unable to ingest complete data



I am Using numPartitions, lowerBound, upperBound in Spark Dataframe to fetch large tables from oracle.but it gives me data equivalent to only one partition.
Suppose number of partitions are 10 and total records are 100 then only 10 records (total records/numPartitions) i am able to ingest in Hive.
Below is my code snippet


val hiveContext = SparkApp.getHiveContext("AppName")
val jdbcUsername = "MYUSERNAME"
val jdbcPassword = "MYPASSWORD"
val jdbcDatabase ="DBNAME"
val jdbcUrl = "jdbc:oracle:thin:@//hostname:1522/servicename"
val lowerBound = 1
val totalRecords = 100
val partitions = 10
val orclTableName = "MYTEST_TABLE"

val columnName = "rownum"
val connectionProperties = new Properties()
connectionProperties.put("user", s"${jdbcUsername}")
connectionProperties.put("password", s"${jdbcPassword}")
connectionProperties.put("driver","oracle.jdbc.driver.OracleDriver")

val orclTableDF = hiveContext.read.jdbc(url=jdbcUrl,table=orclTableName,columnName = columnName,lowerBound=lowerBound, upperBound= totalRecords , numPartitions=partitions, connectionProperties=connectionProperties)



orclTableDF.write.saveAsTable("MYTEST_NEW_TABLE")



Could you please let me know what i am missing.




1 Answer
1



Without partitionColumn parameter read won’t be parallelized. Please provide the column name for the partition key. Make sure with that key your data is evenly partitioned if not you may get data skew issue. If your data is not evenly partitioned then using rownum function evenly distribute your data by using mod operator.





In hiveContext.read.jdbc(), columnName property is same as the partitionColumn parameter for hiveContext.read.format("jdbc").option() . I have used hiveContext.read.format("jdbc").option() as well with the same parameter which you mentioned but i am facing same problem.
– Akhil Kakkar
Jul 3 at 8:06






below is the code snippet for this which i used, val orclTableDF = hiveContext.read.format("jdbc").option("url", "jdbc:oracle:thin:@//MyHost:1522/servicename").option("dbtable", "orclTableName").option("user", "MYUSERNAME").option("password", "MYPASSWORD").option("partitionColumn", "rownum").option("lowerBound", "1").option("upperBound", "100").option("numPartitions", "10").option("driver", "oracle.jdbc.driver.OracleDriver").load()
– Akhil Kakkar
Jul 3 at 8:10






Is the rownum column already exists in your dB which I doubt because in oracle to generate a sequence number you can use that. So in your dbtable parameter use the below query instead of table name “select rownum as rownseq,col1,col2 from schemaname.tablename” which will parallelise your query as per row num. if your total rows are 10 then spark will create 10 parallel sessions
– Chandan
Jul 3 at 9:20





Did it work for you
– Chandan
Jul 3 at 17:35






By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

u7qJIbBkWdVOs6V3eKH86PZRkXzPM P,sHZDYRBc2dUSqKdTx,e4ky17kSe6,g,teY9z0pmw1OwH h
-
6vaN,GvJO,jS,6R8tfYw6al7fQVH7X48XzuFDmc7OjHy wAgX7eecoZNm7HWJ d1xWU8ESvrBcBiASmvrues9le29gY8 eFq 9

Popular posts from this blog

PHP contact form sending but not receiving emails

PHP contact form sending but not receiving emails I have created a contact form using PHP to send the data. The form seems to send fine as there are no errors and success message appears, however I am not receving the emails into the designated inbox. I am using PHPMailer as I originally tried just using the php 'mail' command which I now understand is a bit hit and miss. I cannot see why I would not be receving the emails so I would be very grateful for any help that could be given. I'm quite new to PHP so please be patient with me :) <?php use PHPMailerPHPMailerPHPMailer; use PHPMailerPHPMailerException; $msg = ""; if (isset($_POST['submit'])) { require 'phpmailer/src/Exception.php'; require 'phpmailer/src/PHPMailer.php'; require 'phpmailer/src/SMTP.php'; function sendemail ($to, $from, $body) { $mail = new PHPMailer(true); $mail->setFrom($from); $mail->addAddress($to); $ma...
Read more

Do graphics cards have individual ID by which single devices can be distinguished?

Do graphics cards have individual ID by which single devices can be distinguished? I have several graphics cards of the same manufacturer and same model and I want to distinguish them not only by the pcie socket they reside in but by some individual value I can read from the hardware. Is there such value and if so what would it be called and how could I access it? Right now I'm using modern nvidia cards (10xx series) and nvidia inspector.but I'd prefer if there was any value that was also applicable to amd. I have programming and scripting knowledge. I am running Windows 10 1 Answer 1 This is a powershell example to pull information from video cards. The DeviceID is supposed to be unique but only if the manufacturer supports Windows Display Driver Model (WDDM). (I copied this directly from docs.microsoft.com) $strComputer = "." $colItems = get-wmiobject -class "Win32_VideoC...
Read more

Create weekly swift ios local notifications

Create weekly swift ios local notifications I can create daily notifications but not weekly ones using swift 3 ios 10 User Notifications framework. Daily works fine but when I add in the .weekday parameter it doesn't send me a notification. The code is: for i in 1 ... 7 { let center = UNUserNotificationCenter.current(); let content = UNMutableNotificationContent(); content.title = "Title"; content.body = "content"; content.sound = UNNotificationSound.default(); var dateComponents = DateComponents(); dateComponents.weekday = i; // hours is 00 to 11 in string format if (daynight == "am") { dateComponents.hour = Int(hours); } else { dateComponents.hour = Int(hours)! + 12; } // mins is 00 to 59 in string format dateCom...
Read more