Thanks, Mich, I have been using the JDBC source with MySQL & Postgres drivers in production for almost 4 years now. The error looked a bit weird and what I meant to ask was am I doing it right? As you mentioned, I will also check with the developers of the driver if they have anything to say about this. Thanks for looking into it :)
Regards, Farhan. On Fri, Apr 30, 2021 at 7:01 PM Mich Talebzadeh <mich.talebza...@gmail.com> wrote: > Hi Farhan, > > I have used it successfully and it works. The only thing that potentially > can cause this issue is the jdbc driver itself. Have you tried another > jdbc driver like progress direct etc. Most of these defects are related to > jdbc driver itself! > > HHT, > > Mich > > On Fri, 30 Apr 2021 at 13:49, Farhan Misarwala <farhan.misarw...@gmail.com> > wrote: > >> Hi Mich, >> >> I have tried this already. I am using the same methods you are using in >> my Java code. I see the same error, where 'dbtable' or 'query' gets added >> as a connection property in the JDBC connection string for the source db, >> which is AAS in my case. >> >> >> >> Thanks, >> >> Farhan. >> >> >> On Fri, Apr 30, 2021 at 3:07 PM Mich Talebzadeh < >> mich.talebza...@gmail.com> wrote: >> >>> Hi, >>> >>> If you are using Spark JDBC connection then you can do the following >>> generic JDBC from PySpark say. that tablename could be sql query as well >>> (select col1, col2 from <table>) >>> >>> ## load from database >>> def loadTableFromJDBC(spark, url, tableName, user, password, driver, >>> fetchsize): >>> try: >>> house_df = spark.read. \ >>> format("jdbc"). \ >>> option("url", url). \ >>> option("dbtable", tableName). \ >>> option("user", user). \ >>> option("password", password). \ >>> option("driver", driver). \ >>> option("fetchsize", fetchsize). \ >>> load() >>> return house_df >>> except Exception as e: >>> print(f"""{e}, quitting""") >>> sys.exit(1) >>> >>> ## write to database >>> def writeTableWithJDBC(dataFrame, url, tableName, user, password, >>> driver, mode): >>> try: >>> dataFrame. \ >>> write. \ >>> format("jdbc"). \ >>> option("url", url). \ >>> option("dbtable", tableName). \ >>> option("user", user). \ >>> option("password", password). \ >>> option("driver", driver). \ >>> mode(mode). \ >>> save() >>> except Exception as e: >>> print(f"""{e}, quitting""") >>> sys.exit(1) >>> >>> I don't know about AWS, but this will allow you to connect to Google >>> BigQuery. Check AWS documentation >>> >>> >>> def loadTableFromBQ(spark,dataset,tableName): >>> try: >>> df = spark.read. \ >>> format("bigquery"). \ >>> option("credentialsFile", >>> config['GCPVariables']['jsonKeyFile']). \ >>> option("dataset", dataset). \ >>> option("table", tableName). \ >>> load() >>> return df >>> except Exception as e: >>> print(f"""{e}, quitting""") >>> sys.exit(1) >>> >>> def writeTableToBQ(dataFrame,mode,dataset,tableName): >>> try: >>> dataFrame. \ >>> write. \ >>> format("bigquery"). \ >>> option("credentialsFile", >>> config['GCPVariables']['jsonKeyFile']). \ >>> mode(mode). \ >>> option("dataset", dataset). \ >>> option("table", tableName). \ >>> save() >>> except Exception as e: >>> print(f"""{e}, quitting""") >>> sys.exit(1) >>> >>> HTH >>> >>> >>> view my Linkedin profile >>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/> >>> >>> >>> >>> *Disclaimer:* Use it at your own risk. Any and all responsibility for >>> any loss, damage or destruction of data or any other property which may >>> arise from relying on this email's technical content is explicitly >>> disclaimed. The author will in no case be liable for any monetary damages >>> arising from such loss, damage or destruction. >>> >>> >>> >>> >>> On Fri, 30 Apr 2021 at 09:32, Farhan Misarwala < >>> farhan.misarw...@gmail.com> wrote: >>> >>>> Hi All, >>>> >>>> I am trying to read data from 'Azure Analysis Services' using CData's >>>> AAS JDBC driver >>>> <https://cdn.cdata.com/help/OAF/jdbc/pg_JDBCconnectcode.htm>. >>>> >>>> I am doing spark.read().jdbc(jdbcURL, aasQuery, connectionProperties) >>>> >>>> This errors out as the driver is throwing an exception saying there's >>>> no 'dbtable' as a connection property. This means spark is trying to set >>>> 'dbtable' as a connection property on the driver, instead of creating a >>>> connection and then executing the query I specify in the options 'dbtable' >>>> or 'query'. >>>> >>>> Usually, in my non-spark/vanilla java code, I use >>>> java.sql.DriverManager to create a connection and then execute a query >>>> successfully using execute() from java.sql.Statement. >>>> >>>> Does the JDBC data source not follow such a flow? if not then what >>>> could be the best solution in this case? please advice. It would be great >>>> if I can query AAS through our beloved Apache Spark ;) >>>> >>>> TIA, >>>> Farhan. >>>> >>>> >>>> >>>> >>>> >>>> --------------------------------------------------------------------- >>>> To unsubscribe e-mail: user-unsubscr...@spark.apache.org >>> >>> -- > > > > view my Linkedin profile > <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/> > > > > *Disclaimer:* Use it at your own risk. Any and all responsibility for any > loss, damage or destruction of data or any other property which may arise > from relying on this email's technical content is explicitly disclaimed. > The author will in no case be liable for any monetary damages arising from > such loss, damage or destruction. > > >