synapse JDBC Connection Issue with Azure data bricks

damodara kuntrapaku 0 Reputation points
2023-04-19T02:47:53.7566667+00:00
Py4JJavaError                             Traceback (most recent call last)
<command-3970932412475338> in <module>
      1 # Load data from an Azure Synapse query.
----> 2 synapse_view_df = spark.read \
      3   .format("com.databricks.spark.sqldw") \
      4   .option("url", synapseConnectionString) \
      5   .option("tempDir", f"abfss://{ADLSContainerName}@{ADLSAccountName}.dfs.core.windows.net/{ADLSTempFolderName}") \

/databricks/spark/python/pyspark/sql/readwriter.py in load(self, path, format, schema, **options)
    162             return self._df(self._jreader.load(self._spark._sc._jvm.PythonUtils.toSeq(path)))
    163         else:
--> 164             return self._df(self._jreader.load())
    165 
    166     def json(self, path, schema=None, primitivesAsString=None, prefersDecimal=None,

/databricks/spark/python/lib/py4j-0.10.9.1-src.zip/py4j/java_gateway.py in __call__(self, *args)
   1302 
   1303         answer = self.gateway_client.send_command(command)
-> 1304         return_value = get_return_value(
   1305             answer, self.gateway_client, self.target_id, self.name)
   1306 

/databricks/spark/python/pyspark/sql/utils.py in deco(*a, **kw)
    115     def deco(*a, **kw):
    116         try:
--> 117             return f(*a, **kw)
    118         except py4j.protocol.Py4JJavaError as e:
    119             converted = convert_exception(e.java_exception)

/databricks/spark/python/lib/py4j-0.10.9.1-src.zip/py4j/protocol.py in get_return_value(answer, gateway_client, target_id, name)
    324             value = OUTPUT_CONVERTER[type](answer[2:], gateway_client)
    325             if answer[1] == REFERENCE_TYPE:
--> 326                 raise Py4JJavaError(
    327                     "An error occurred while calling {0}{1}{2}.\n".
    328                     format(target_id, ".", name), value)

Py4JJavaError: An error occurred while calling o2997.load.
: com.databricks.spark.sqldw.SqlDWSideException: Azure Synapse Analytics failed to execute the JDBC query produced by the connector.
Underlying SQLException(s):
  - com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'CONVERTED_DMT_GasNewEnergy_Vw_UtilisationOAC'. [ErrorCode = 208] [SQLState = S0002]
         
	at com.databricks.spark.sqldw.Utils$.wrapExceptions(Utils.scala:729)
	at com.databricks.spark.sqldw.SqlDWRelation.schema$lzycompute(SqlDWRelation.scala:68)
	at com.databricks.spark.sqldw.SqlDWRelation.schema(SqlDWRelation.scala:67)
	at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:496)
	at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:356)
	at org.apache.spark.sql.DataFrameReader.$anonfun$load$2(DataFrameReader.scala:323)
	at scala.Option.getOrElse(Option.scala:189)
	at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:323)
	at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:222)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
	at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:380)
	at py4j.Gateway.invoke(Gateway.java:295)
	at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
	at py4j.commands.CallCommand.execute(CallCommand.java:79)
	at py4j.GatewayConnection.run(GatewayConnection.java:251)
	at java.lang.Thread.run(Thread.java:750)
Caused by: java.sql.SQLException: Exception thrown in awaitResult: 
	at com.databricks.spark.sqldw.JDBCWrapper.executeInterruptibly(SqlDWJDBCWrapper.scala:137)
	at com.databricks.spark.sqldw.JDBCWrapper.resolveTable(SqlDWJDBCWrapper.scala:177)
	at com.databricks.spark.sqldw.SqlDWRelation.$anonfun$schema$3(SqlDWRelation.scala:70)
	at com.databricks.spark.sqldw.JDBCWrapper.withConnection(SqlDWJDBCWrapper.scala:335)
	at com.databricks.spark.sqldw.SqlDWRelation.$anonfun$schema$2(SqlDWRelation.scala:69)
	at scala.Option.getOrElse(Option.scala:189)
	at com.databricks.spark.sqldw.SqlDWRelation.$anonfun$schema$1(SqlDWRelation.scala:69)
	at com.databricks.spark.sqldw.Utils$.wrapExceptions(Utils.scala:692)
	... 19 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'CONVERTED_DMT_GasNewEnergy_Vw_UtilisationOAC'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:872)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:767)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQueryInternal(SQLServerStatement.java:699)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.buildExecuteMetaData(SQLServerPreparedStatement.java:1090)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getMetaData(SQLServerPreparedStatement.java:1066)
	at com.databricks.spark.sqldw.JDBCWrapper.$anonfun$resolveTable$1(SqlDWJDBCWrapper.scala:177)
	at com.databricks.spark.sqldw.JDBCWrapper.$anonfun$executeInterruptibly$3(SqlDWJDBCWrapper.scala:129)
	at scala.concurrent.Future$.$anonfun$apply$1(Future.scala:659)
	at scala.util.Success.$anonfun$map$1(Try.scala:255)
	at scala.util.Success.map(Try.scala:213)
	at scala.concurrent.Future.$anonfun$map$1(Future.scala:292)
	at scala.concurrent.impl.Promise.liftedTree1$1(Promise.scala:33)
	at scala.concurrent.impl.Promise.$anonfun$transform$1(Promise.scala:33)
	at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:64)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	... 1 more
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,443 questions
Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
1,955 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Pratik Somaiya 4,201 Reputation points
    2023-04-19T05:24:14.23+00:00

    Hello damodara kuntrapaku, I think issue is not with connection but finding the database object The error says:

    Py4JJavaError: An error occurred while calling o2997.load.
    : com.databricks.spark.sqldw.SqlDWSideException: Azure Synapse Analytics failed to execute the JDBC query produced by the connector.
    Underlying SQLException(s):
      - com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'CONVERTED_DMT_GasNewEnergy_Vw_UtilisationOAC'. [ErrorCode = 208] [SQLState = S0002]
       
    

    It is trying to find the database object: CONVERTED_DMT_GasNewEnergy_Vw_UtilisationOAC but cannot find it

    Is this object present in the database?