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?

Answer 1

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.