Is it possible to modify query of Apache Spark JDBC connector?

user_2433653 0
Viewed 228

I'm using PySpark to read data from an Oracle DB table. The way I do that is:

 profiles = spark.read.jdbc(
            url=db_url,
            table='(select /*+ SCN_ASCENDING */ '
                  'data, sys_hashval from profiles)',
            properties=connection_details,
            predicates=predicates_list)

Spark translates this into a query of this form:

SELECT "ID","DATA"
FROM (
  select /*+ FULL(profiles) SCN_ASCENDING */ id, data, sys_hashval
  from profiles
)
WHERE sys_hashval >= 10840662 and sys_hashval < 11012736

Which is causing problems because the hint that is added by me to the inner sub query is not in the outer query which is added by Spark. Is there anyway to modify the outer query? Or Spark doesn't give the ability to control that?

Thanks

pyspark apache-spark oracle sql python
2 Answers

I'm not sure how to modify this query from the PySpark, but there are a few ways to modify the query from the database side. For example, the package DBMS_ADVANCED_REWRITE lets you swap out one query for another.

begin
    sys.dbms_advanced_rewrite.declare_rewrite_equivalence
    (
        name             => 'spark_rewrite_1',
        source_stmt      => q'[SELECT "ID","DATA"
FROM (
  select /*+ FULL(profiles) SCN_ASCENDING */ id, data, sys_hashval
  from profiles
)
WHERE sys_hashval >= 10840662 and sys_hashval < 11012736]',
        destination_stmt => q'[SELECT /*+ SCN_ASCENDING */ "ID","DATA"
FROM (
  select /*+ FULL(profiles) */ id, data, sys_hashval
  from profiles
)
WHERE sys_hashval >= 10840662 and sys_hashval < 11012736 and 1=1]',
        validate         => false,
        rewrite_mode     => 'TEXT_MATCH'
    );
end;
/

That package can be difficult to use because you have to get the exact text of the queries. If the literals change you'll need a separate re-write, so hopefully you can control those numbers. And the final query must have a difference other than hints, which is why I added 1=1. You may also need privileges on the package, and may need a DBA to run this command: grant execute on dbms_advanced_rewrite to your_username;

If the above code doesn't work, queries can also be forced to change hints through SQL Profiles, SQL translation framework, or possibly a hint that references the outer query query block name.

user_4091720

Oracle looks for the ID column in the subquery. That's most probably the error.

I'm not familiar with Spark, but we can try adding the primary key of profiles in your subquery, like so:

select /*+ FULL(profiles) SCN_ASCENDING */ data, sys_hashval, pk_name "ID"
from profiles

Just change pk_name with the primary key of your profiles table.

user_53997190
Related