Is there anyway directly query the oracle db with where clause instead of filtering on complete dataframe in spark-sql 2.x

Question

I am using spark-sql.2.3.1 and I am connecting to oracleDB to fetch the dataframe

Code : >'    ora_df
                      .option("schema",schema)
                      .option("partitionColumn", partitionColumn)
                      .option("dbtable", query)
                      .load()
       > '

Issue : I would like to filter the table with date. So is there anyway to pass the query with dynamically build where clause?

@Alex ott sir can you tell if I can query using where clause directly on oracleDb instead of filtering the resultant dataframe? — BdEngineer, Nov 01 '18 at 19:41

score 0 · Accepted Answer · answered Nov 01 '18 at 22:24

0

Something like this is customary:

 val dataframe_mysql_4 = spark.read.jdbc(jdbcUrl, s"""(select DISTINCT type from family where type like '${val1}' ) f """, connectionProperties)

Set up you r connection string and then with variable setting of val1 you can add a where clause that uses this value. Note the f.

answered Nov 01 '18 at 22:24

thebluephantom

16,458
8
40
83

Started q : """( select * from MODEL_VALS WHERE DATA_DATE ='${colVal}' ) T """ Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-01741: illegal zero-length identifier at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) – BdEngineer Nov 02 '18 at 08:09
why did you put " f """ at the end of the query ? what does it mean here ? – BdEngineer Nov 02 '18 at 08:10
You could put e as well. But need an alias. – thebluephantom Nov 02 '18 at 08:17
if I have two queries alias should be different or same also fine ? In earlier query DATA_DATE ='${colVal}' ...this is not being parsed to respetive string value of "colVal" , what did i do wrong here ? – BdEngineer Nov 02 '18 at 09:25
Hard for me to comment on the date. Did you fill it (correctly)? ORACLE has certain approaches here. The 2 queries can use the same alias - I know it's a bit odd but that's just protocol. See https://stackoverflow.com/questions/25337722/ora-01741-illegal-zero-length-identifier – thebluephantom Nov 02 '18 at 09:34
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/182994/discussion-between-thebluephantom-and-user3252097). – thebluephantom Nov 02 '18 at 09:43

Is there anyway directly query the oracle db with where clause instead of filtering on complete dataframe in spark-sql 2.x

1 Answers1