topic Re: WHERE 1=0, Error message from Server in Data Engineering

WHERE 1=0, Error message from Server

pritam_epam — Wed, 11 Sep 2024 08:57:55 GMT

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.

Re: WHERE 1=0, Error message from Server

szymon_dybczak — Wed, 11 Sep 2024 09:22:54 GMT

Hi @pritam_epam ,

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

Re: WHERE 1=0, Error message from Server

pritam_epam — Wed, 11 Sep 2024 10:03:29 GMT

@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..

Re: WHERE 1=0, Error message from Server

szymon_dybczak — Wed, 11 Sep 2024 10:23:55 GMT

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

Re: WHERE 1=0, Error message from Server

pritam_epam — Wed, 11 Sep 2024 10:29:16 GMT

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)

Re: WHERE 1=0, Error message from Server

pritam_epam — Wed, 11 Sep 2024 10:47:01 GMT

@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..

Re: WHERE 1=0, Error message from Server

szymon_dybczak — Wed, 11 Sep 2024 12:06:51 GMT

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:

Re: WHERE 1=0, Error message from Server

pritam_epam — Wed, 11 Sep 2024 12:21:57 GMT

@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..

Re: WHERE 1=0, Error message from Server

pritam_epam — Wed, 11 Sep 2024 12:24:44 GMT

@szymon_dybczak

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

Re: WHERE 1=0, Error message from Server

pritam_epam — Thu, 12 Sep 2024 09:02:38 GMT

@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?