Databricks Reading Only metadata from JDBC Source but not Data

Question

Tried different ways

sql

  CREATE TABLE oracle_table
  USING org.apache.spark.sql.jdbc
  OPTIONS (
    dbtable 'persons',
    driver 'oracle.jdbc.driver.OracleDriver',
    user '<user>',
    password '<pass>',
    url 'jdbc:oracle:thin://@<host>:1521/orcl')

above code returned OK

select * from oracle_table

throwed java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

python

jdbcUrl = "jdbc:oracle:thin:@<host>:1521/orcl"
properties = {
    "user": "<user>",
    "password": "<password>",
    "driver": "oracle.jdbc.driver.OracleDriver" 
}

pushdown_query = "( SELECT * FROM persons ) emp_alias"
df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=properties)
df.printSchema()

returned

 |-- PERSON_ID: decimal(38,10) (nullable = true)
 |-- FIRST_NAME: string (nullable = true)
 |-- LAST_NAME: string (nullable = true)

But for below code

df.show()

throwed java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

help me!

Does the CREATE TABLE run straight away? It's probably "lazy loading", meaning that it doesn't run until you actually select from it. So most likely the issue is actually in your create table, and your databricks actually can't connect to <host>:1521. Are you certain your databricks install has network connectivity to the Oracle server? — Nick.Mc
– Nick.Mc, Commented Jul 21, 2020 at 12:50
Initally i have faced connectivity error,i have updated the azure inbound and outbound rules ,then it is able to load metadata from table DESCRIBE persons is also giving all columns and datatypes, if there is connectivity issue is shoudn't load the metadata also right!, thats what confusing me — ChandraPrakash
– ChandraPrakash, Commented Jul 21, 2020 at 13:41
@ChandraPrakash You may checkout similar issue discussed here: stackoverflow.com/questions/52542816/… — CHEEKATLAPRADEEP
– CHEEKATLAPRADEEP, Commented Jul 22, 2020 at 4:45
The %sh telnet trick is pretty handy I'll have to remember that one — Nick.Mc
– Nick.Mc, Commented Jul 22, 2020 at 11:58
i figured out the issue firewall is enabled for only master node of databricks and workers are in private ip .Thats why im able to get metadata but not data — ChandraPrakash
– ChandraPrakash, Commented Jul 29, 2020 at 15:40

CHEEKATLAPRADEEP · Accepted Answer · 2020-08-07 04:46:31Z

1

Sharing the answer as per the comment by the original poster:

I figured out the issue firewall is enabled for only master node of databricks and workers are in private ip. That's why I'm able to get metadata but not data.

answered Aug 7, 2020 at 4:46

CHEEKATLAPRADEEP

12.8k1 gold badge22 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Databricks Reading Only metadata from JDBC Source but not Data

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related