WHERE 1=0, Error message from Server

pritam_epam
Databricks Partner

‎09-11-2024 01:57 AM

Hi ,

I am getting this Error:

WHERE 1=0, Error message from Server: Configuration db table is not available. I am using PySpark and JDBC connection. Please help on this.

0 Kudos

szymon_dybczak
Esteemed Contributor III

‎09-11-2024 02:22 AM

Hi @pritam_epam ,

Could you share with us more details like code that you want to execute etc.?

0 Kudos

pritam_epam
Databricks Partner

‎09-11-2024 03:03 AM

@szymon_dybczak 

 

jdbc_url = f"jdbc:databricks://{os.environ[db_const.DATA_BRICKS_HOST]};" \
f"transportMode=http;" \
f"ssl=1;" \
f"httpPath={os.environ[db_const.DATA_BRICKS_HTTP_PATH]};" \
f"AuthMech=3;" \
f"UID=token;" \
f"PWD={os.environ[db_const.DATA_BRICKS_TOKEN]};" \
f"Catalog=scna_qa;" \
f"Schema=dsml"

conn = jdbc_url
print("JDBC connection url: ", conn)

# Error - configuration query not available
parameters = {}
df = pyspark.pandas.read_sql_query(sql=query, con=conn)
print("pyspark dataframe: ", df)
 
Exception coming:

com.databricks.client.support.exceptions.GeneralException: [Databricks][JDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: null, Query: SELECT * FROM (select * from scna_qa.dsml.geonode_sku_dategrp_kpi) SPARK_GEN_SUBQ_0 WHERE 1=0, Error message from Server: Configuration query is not available..

0 Kudos

szymon_dybczak
Esteemed Contributor III
In response to pritam_epam

‎09-11-2024 03:14 AM - edited ‎09-11-2024 03:23 AM

Below you're passing query variable to read_sql_query function but I don't see any place in your code where you defined this.

 

# Error - configuration query not available
parameters = {}
df = pyspark.pandas.read_sql_query(sql=query, con=conn)
print("pyspark dataframe: ", df)
 
 
And you don't need to use pandas, you can leverage pyspark to read from jdbc sources or you can also try Lakehouse Federation:
 

 

table = (spark.read
  .format("jdbc")
  .option("url", "<jdbc-url>")
  .option("dbtable", "<table-name>")
  .option("user", "<username>")
  .option("password", "<password>")
  .load()
)

Query databases using JDBC - Azure Databricks | Microsoft Learn

 

Query databases using JDBC - Azure Databricks | Microsoft Learn

 

0 Kudos

pritam_epam
Databricks Partner

‎09-11-2024 03:29 AM

This is the query I already defined - 

query = 'select * from scna_qa.dsml.geonode_sku_dategrp_kpi'
 
jdbc_url = f"jdbc:databricks://{os.environ[db_const.DATA_BRICKS_HOST]};" \
f"transportMode=http;" \
f"ssl=1;" \
f"httpPath={os.environ[db_const.DATA_BRICKS_HTTP_PATH]};" \
f"AuthMech=3;" \
f"UID=token;" \
f"PWD={os.environ[db_const.DATA_BRICKS_TOKEN]};" \
f"Catalog=scna_qa;" \
f"Schema=dsml"

conn = jdbc_url
print("JDBC connection url: ", conn)

# Error - configuration query not available
parameters = {}
df = pyspark.pandas.read_sql_query(sql=query, con=conn)
print("pyspark dataframe: ", df)
0 Kudos

pritam_epam
Databricks Partner

‎09-11-2024 03:47 AM

@szymon_dybczak  I also tried the another approach you provided,

 

jdbc_url = f"jdbc:databricks://{os.environ[db_const.DATA_BRICKS_HOST]};" \
f"transportMode=http;" \
f"ssl=1;" \
f"httpPath={os.environ[db_const.DATA_BRICKS_HTTP_PATH]};" \
f"AuthMech=3;" \
f"UID=token;" \
f"PWD={os.environ[db_const.DATA_BRICKS_TOKEN]};" \
f"Catalog=scna_qa;" \
f"Schema=dsml"
df = spark.read.format("jdbc") \
.option("url", jdbc_url) \
.option("dbtable", "scna_qa.dsml.geonode_sku_dategrp_kpi") \
.load()
print("pyspark dataframe: ", df)

Here scna_qa is Catalog and dsml is schema
 
Both the approach not working
Exception :
[Data bricks][JDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: null, Query: SELECT * F***, Error message from Server: Configuration Schema is not available..

 

0 Kudos

szymon_dybczak
Esteemed Contributor III
In response to pritam_epam

‎09-11-2024 04:49 AM - edited ‎09-11-2024 05:06 AM

Hi @pritam_epam ,

Try something similar to the following code and see if to works for you.

 

databricks_url = f'''jdbc:databricks://adb-774941343460743.3.azuredatabricks.net:443/default;
transportMode=http;
ssl=1;
httpPath=sql/protocolv1/o/your_org_id/your_cluster_id;
AuthMech=3;
UID=token;
PWD=your_accesss_token;
UseNativeQuery=0;
'''

df = spark.read.format("jdbc").option("url", databricks_url) \
.option("query", 'SELECT * FROM main.default.department') \
.load()

display(df)

 

In my case it worked without any issue:

szymon_dybczak_0-1726056322174.png

 

 

0 Kudos

pritam_epam
Databricks Partner

‎09-11-2024 05:21 AM

@szymon_dybczak ,

No it's again throwing same type of exceptions

[Databricks][JDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: null, Query: SELECT * FROM (SELECT * FROM scna_qa.dsml.geonode_sku_dategrp_kpi) SPARK_GEN_SUBQ_0 WHERE 1=0, Error message from Server: Configuration query is not available..

 

0 Kudos

pritam_epam
Databricks Partner

‎09-11-2024 05:24 AM

@szymon_dybczak 

can we do a meeting to understand what's the issue?

0 Kudos

pritam_epam
Databricks Partner

‎09-12-2024 02:02 AM

@szymon_dybczak 

Can you help us on this? Or could you provide a complete structure/steps how to connect with databricks using PySpark and JDBC step by step . Like initiate spark session then JDBC connection url then sql read all these in details.

Also what should be the JDBC version ? I am using databricks==0.2 and JDBC - DatabricksJDBC42.jar latest, what is the compatible JDBC driver?

0 Kudos