You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by Nicholas Chammas <ni...@gmail.com> on 2023/12/24 18:27:11 UTC
Re: Validate spark sql
This is a user-list question, not a dev-list question. Moving this conversation to the user list and BCC-ing the dev list.
Also, this statement
> We are not validating against table or column existence.
is not correct. When you call spark.sql(…), Spark will lookup the table references and fail with TABLE_OR_VIEW_NOT_FOUND if it cannot find them.
Also, when you run DDL via spark.sql(…), Spark will actually run it. So spark.sql(“drop table my_table”) will actually drop my_table. It’s not a validation-only operation.
This question of validating SQL is already discussed on Stack Overflow <https://stackoverflow.com/q/46973729/877069>. You may find some useful tips there.
Nick
> On Dec 24, 2023, at 4:52 AM, Mich Talebzadeh <mi...@gmail.com> wrote:
>
>
> Yes, you can validate the syntax of your PySpark SQL queries without connecting to an actual dataset or running the queries on a cluster.
> PySpark provides a method for syntax validation without executing the query. Something like below
> ____ __
> / __/__ ___ _____/ /__
> _\ \/ _ \/ _ `/ __/ '_/
> /__ / .__/\_,_/_/ /_/\_\ version 3.4.0
> /_/
>
> Using Python version 3.9.16 (main, Apr 24 2023 10:36:11)
> Spark context Web UI available at http://rhes75:4040 <http://rhes75:4040/>
> Spark context available as 'sc' (master = local[*], app id = local-1703410019374).
> SparkSession available as 'spark'.
> >>> from pyspark.sql import SparkSession
> >>> spark = SparkSession.builder.appName("validate").getOrCreate()
> 23/12/24 09:28:02 WARN SparkSession: Using an existing Spark session; only runtime SQL configurations will take effect.
> >>> sql = "SELECT * FROM <TABLE> WHERE <COLUMN> = some value"
> >>> try:
> ... spark.sql(sql)
> ... print("is working")
> ... except Exception as e:
> ... print(f"Syntax error: {e}")
> ...
> Syntax error:
> [PARSE_SYNTAX_ERROR] Syntax error at or near '<'.(line 1, pos 14)
>
> == SQL ==
> SELECT * FROM <TABLE> WHERE <COLUMN> = some value
> --------------^^^
>
> Here we only check for syntax errors and not the actual existence of query semantics. We are not validating against table or column existence.
>
> This method is useful when you want to catch obvious syntax errors before submitting your PySpark job to a cluster, especially when you don't have access to the actual data.
> In summary
> Theis method validates syntax but will not catch semantic errors
> If you need more comprehensive validation, consider using a testing framework and a small dataset.
> For complex queries, using a linter or code analysis tool can help identify potential issues.
> HTH
>
> Mich Talebzadeh,
> Dad | Technologist | Solutions Architect | Engineer
> London
> United Kingdom
>
> view my Linkedin profile <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>
> https://en.everybodywiki.com/Mich_Talebzadeh
>
>
> 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 Sun, 24 Dec 2023 at 07:57, ram manickam <ramsidmisc@gmail.com <ma...@gmail.com>> wrote:
>> Hello,
>> Is there a way to validate pyspark sql to validate only syntax errors?. I cannot connect do actual data set to perform this validation. Any help would be appreciated.
>>
>>
>> Thanks
>> Ram
Re: Validate spark sql
Posted by Bjørn Jørgensen <bj...@gmail.com>.
Mailing lists
For broad, opinion based, ask for external resources, debug issues, bugs,
contributing to the project, and scenarios, it is recommended you use the
user@spark.apache.org mailing list.
- user@spark.apache.org
<ht...@spark.apache.org> is for usage
questions, help, and announcements. (subscribe)
<user-subscribe@spark.apache.org?subject=(send%20this%20email%20to%20subscribe)>
(unsubscribe)
<user-unsubscribe@spark.apache.org?subject=(send%20this%20email%20to%20unsubscribe)>
(archives) <ht...@spark.apache.org>
- dev@spark.apache.org
<ht...@spark.apache.org> is for people
who want to contribute code to Spark. (subscribe)
<dev-subscribe@spark.apache.org?subject=(send%20this%20email%20to%20subscribe)>
(unsubscribe)
<dev-unsubscribe@spark.apache.org?subject=(send%20this%20email%20to%20unsubscribe)>
(archives) <ht...@spark.apache.org>
man. 25. des. 2023 kl. 04:58 skrev Mich Talebzadeh <
mich.talebzadeh@gmail.com>:
> Well not to put too finer point on it, in a public forum, one ought to
> respect the importance of open communication. Everyone has the right to ask
> questions, seek information, and engage in discussions without facing
> unnecessary patronization.
>
>
>
> Mich Talebzadeh,
> Dad | Technologist | Solutions Architect | Engineer
> London
> United Kingdom
>
>
> view my Linkedin profile
> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>
>
> https://en.everybodywiki.com/Mich_Talebzadeh
>
>
>
> *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 Sun, 24 Dec 2023 at 18:27, Nicholas Chammas <ni...@gmail.com>
> wrote:
>
>> This is a user-list question, not a dev-list question. Moving this
>> conversation to the user list and BCC-ing the dev list.
>>
>> Also, this statement
>>
>> > We are not validating against table or column existence.
>>
>> is not correct. When you call spark.sql(…), Spark will lookup the table
>> references and fail with TABLE_OR_VIEW_NOT_FOUND if it cannot find them.
>>
>> Also, when you run DDL via spark.sql(…), Spark will actually run it. So
>> spark.sql(“drop table my_table”) will actually drop my_table. It’s not a
>> validation-only operation.
>>
>> This question of validating SQL is already discussed on Stack Overflow
>> <https://stackoverflow.com/q/46973729/877069>. You may find some useful
>> tips there.
>>
>> Nick
>>
>>
>> On Dec 24, 2023, at 4:52 AM, Mich Talebzadeh <mi...@gmail.com>
>> wrote:
>>
>>
>> Yes, you can validate the syntax of your PySpark SQL queries without
>> connecting to an actual dataset or running the queries on a cluster.
>> PySpark provides a method for syntax validation without executing the
>> query. Something like below
>> ____ __
>> / __/__ ___ _____/ /__
>> _\ \/ _ \/ _ `/ __/ '_/
>> /__ / .__/\_,_/_/ /_/\_\ version 3.4.0
>> /_/
>>
>> Using Python version 3.9.16 (main, Apr 24 2023 10:36:11)
>> Spark context Web UI available at http://rhes75:4040
>> Spark context available as 'sc' (master = local[*], app id =
>> local-1703410019374).
>> SparkSession available as 'spark'.
>> >>> from pyspark.sql import SparkSession
>> >>> spark = SparkSession.builder.appName("validate").getOrCreate()
>> 23/12/24 09:28:02 WARN SparkSession: Using an existing Spark session;
>> only runtime SQL configurations will take effect.
>> >>> sql = "SELECT * FROM <TABLE> WHERE <COLUMN> = some value"
>> >>> try:
>> ... spark.sql(sql)
>> ... print("is working")
>> ... except Exception as e:
>> ... print(f"Syntax error: {e}")
>> ...
>> Syntax error:
>> [PARSE_SYNTAX_ERROR] Syntax error at or near '<'.(line 1, pos 14)
>>
>> == SQL ==
>> SELECT * FROM <TABLE> WHERE <COLUMN> = some value
>> --------------^^^
>>
>> Here we only check for syntax errors and not the actual existence of
>> query semantics. We are not validating against table or column existence.
>>
>> This method is useful when you want to catch obvious syntax errors before
>> submitting your PySpark job to a cluster, especially when you don't have
>> access to the actual data.
>>
>> In summary
>>
>> - Theis method validates syntax but will not catch semantic errors
>> - If you need more comprehensive validation, consider using a testing
>> framework and a small dataset.
>> - For complex queries, using a linter or code analysis tool can help
>> identify potential issues.
>>
>> HTH
>>
>>
>> Mich Talebzadeh,
>> Dad | Technologist | Solutions Architect | Engineer
>> London
>> United Kingdom
>>
>> view my Linkedin profile
>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>>
>>
>> https://en.everybodywiki.com/Mich_Talebzadeh
>>
>>
>> *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 Sun, 24 Dec 2023 at 07:57, ram manickam <ra...@gmail.com> wrote:
>>
>>> Hello,
>>> Is there a way to validate pyspark sql to validate only syntax errors?.
>>> I cannot connect do actual data set to perform this validation. Any
>>> help would be appreciated.
>>>
>>>
>>> Thanks
>>> Ram
>>>
>>
>>
--
Bjørn Jørgensen
Vestre Aspehaug 4, 6010 Ålesund
Norge
+47 480 94 297
Re: Validate spark sql
Posted by Mich Talebzadeh <mi...@gmail.com>.
Worth trying EXPLAIN
<https://spark.apache.org/docs/latest/sql-ref-syntax-qry-explain.html>statement
as suggested by @tianlangstudio
HTH
Mich Talebzadeh,
Dad | Technologist | Solutions Architect | Engineer
London
United Kingdom
view my Linkedin profile
<https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
https://en.everybodywiki.com/Mich_Talebzadeh
*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 Mon, 25 Dec 2023 at 11:15, tianlangstudio <ti...@aliyun.com>
wrote:
>
> What about EXPLAIN?
> https://spark.apache.org/docs/3.5.0/sql-ref-syntax-qry-explain.html#content
>
>
>
> <https://www.upwork.com/fl/huanqingzhu>
> <https://www.tianlang.tech/>Fusion Zhu <https://www.tianlang.tech/>
>
> ------------------------------------------------------------------
> 发件人：ram manickam <ra...@gmail.com>
> 发送时间：2023年12月25日(星期一) 12:58
> 收件人：Mich Talebzadeh<mi...@gmail.com>
> 抄 送：Nicholas Chammas<ni...@gmail.com>; user<
> user@spark.apache.org>
> 主 题：Re: Validate spark sql
>
> Thanks Mich, Nicholas. I tried looking over the stack overflow post and
> none of them
> Seems to cover the syntax validation. Do you know if it's even possible to
> do syntax validation in spark?
>
> Thanks
> Ram
>
> On Sun, Dec 24, 2023 at 12:49 PM Mich Talebzadeh <
> mich.talebzadeh@gmail.com> wrote:
> Well not to put too finer point on it, in a public forum, one ought to
> respect the importance of open communication. Everyone has the right to ask
> questions, seek information, and engage in discussions without facing
> unnecessary patronization.
>
>
>
> Mich Talebzadeh,
> Dad | Technologist | Solutions Architect | Engineer
> London
> United Kingdom
>
>
> view my Linkedin profile
> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>
>
> https://en.everybodywiki.com/Mich_Talebzadeh
>
>
>
> 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 Sun, 24 Dec 2023 at 18:27, Nicholas Chammas <ni...@gmail.com>
> wrote:
> This is a user-list question, not a dev-list question. Moving this
> conversation to the user list and BCC-ing the dev list.
>
> Also, this statement
>
> > We are not validating against table or column existence.
>
> is not correct. When you call spark.sql(…), Spark will lookup the table
> references and fail with TABLE_OR_VIEW_NOT_FOUND if it cannot find them.
>
> Also, when you run DDL via spark.sql(…), Spark will actually run it. So
> spark.sql(“drop table my_table”) will actually drop my_table. It’s not a
> validation-only operation.
>
> This question of validating SQL is already discussed on Stack Overflow
> <https://stackoverflow.com/q/46973729/877069>. You may find some useful
> tips there.
>
> Nick
>
>
> On Dec 24, 2023, at 4:52 AM, Mich Talebzadeh <mi...@gmail.com>
> wrote:
>
>
> Yes, you can validate the syntax of your PySpark SQL queries without
> connecting to an actual dataset or running the queries on a cluster.
> PySpark provides a method for syntax validation without executing the
> query. Something like below
> ____ __
> / __/__ ___ _____/ /__
> _\ \/ _ \/ _ `/ __/ '_/
> /__ / .__/\_,_/_/ /_/\_\ version 3.4.0
> /_/
> Using Python version 3.9.16 (main, Apr 24 2023 10:36:11)
> Spark context Web UI available at http://rhes75:4040
> Spark context available as 'sc' (master = local[*], app id =
> local-1703410019374).
> SparkSession available as 'spark'.
> >>> from pyspark.sql import SparkSession
> >>> spark = SparkSession.builder.appName("validate").getOrCreate()
> 23/12/24 09:28:02 WARN SparkSession: Using an existing Spark session; only
> runtime SQL configurations will take effect.
> >>> sql = "SELECT * FROM <TABLE> WHERE <COLUMN> = some value"
> >>> try:
> ... spark.sql(sql)
> ... print("is working")
> ... except Exception as e:
> ... print(f"Syntax error: {e}")
> ...
> Syntax error:
> [PARSE_SYNTAX_ERROR] Syntax error at or near '<'.(line 1, pos 14)
> == SQL ==
> SELECT * FROM <TABLE> WHERE <COLUMN> = some value
> --------------^^^
>
> Here we only check for syntax errors and not the actual existence of query
> semantics. We are not validating against table or column existence.
>
> This method is useful when you want to catch obvious syntax errors before
> submitting your PySpark job to a cluster, especially when you don't have
> access to the actual data.
>
> In summary
>
> - Theis method validates syntax but will not catch semantic errors
> - If you need more comprehensive validation, consider using a testing
> framework and a small dataset.
> - For complex queries, using a linter or code analysis tool can help
> identify potential issues.
>
> HTH
>
> Mich Talebzadeh,
> Dad | Technologist | Solutions Architect | Engineer
> London
> United Kingdom
>
> view my Linkedin profile
> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>
>
> https://en.everybodywiki.com/Mich_Talebzadeh
>
>
> 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 Sun, 24 Dec 2023 at 07:57, ram manickam <ra...@gmail.com> wrote:
> Hello,
> Is there a way to validate pyspark sql to validate only syntax errors?. I
> cannot connect do actual data set to perform this validation. Any
> help would be appreciated.
>
>
> Thanks
> Ram
>
>
回复：Validate spark sql
Posted by tianlangstudio <ti...@aliyun.com.INVALID>.
What about EXPLAIN?
https://spark.apache.org/docs/3.5.0/sql-ref-syntax-qry-explain.html#content <https://spark.apache.org/docs/3.5.0/sql-ref-syntax-qry-explain.html#content >
<https://www.upwork.com/fl/huanqingzhu >
<https://www.tianlang.tech/ >Fusion Zhu <https://www.tianlang.tech/ >
------------------------------------------------------------------
发件人：ram manickam <ra...@gmail.com>
发送时间：2023年12月25日(星期一) 12:58
收件人：Mich Talebzadeh<mi...@gmail.com>
抄 送：Nicholas Chammas<ni...@gmail.com>; user<us...@spark.apache.org>
主 题：Re: Validate spark sql
Thanks Mich, Nicholas. I tried looking over the stack overflow post and none of them
Seems to cover the syntax validation. Do you know if it's even possible to do syntax validation in spark?
Thanks
Ram
On Sun, Dec 24, 2023 at 12:49 PM Mich Talebzadeh <mich.talebzadeh@gmail.com <mailto:mich.talebzadeh@gmail.com >> wrote:
Well not to put too finer point on it, in a public forum, one ought to respect the importance of open communication. Everyone has the right to ask questions, seek information, and engage in discussions without facing unnecessary patronization.
Mich Talebzadeh,
Dad | Technologist | Solutions Architect | Engineer
London
United Kingdom
view my Linkedin profile <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/ >
https://en.everybodywiki.com/Mich_Talebzadeh <https://en.everybodywiki.com/Mich_Talebzadeh >
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 Sun, 24 Dec 2023 at 18:27, Nicholas Chammas <nicholas.chammas@gmail.com <mailto:nicholas.chammas@gmail.com >> wrote:
This is a user-list question, not a dev-list question. Moving this conversation to the user list and BCC-ing the dev list.
Also, this statement
> We are not validating against table or column existence.
is not correct. When you call spark.sql(…), Spark will lookup the table references and fail with TABLE_OR_VIEW_NOT_FOUND if it cannot find them.
Also, when you run DDL via spark.sql(…), Spark will actually run it. So spark.sql(“drop table my_table”) will actually drop my_table. It’s not a validation-only operation.
This question of validating SQL is already discussed on Stack Overflow <https://stackoverflow.com/q/46973729/877069 >. You may find some useful tips there.
Nick
On Dec 24, 2023, at 4:52 AM, Mich Talebzadeh <mich.talebzadeh@gmail.com <mailto:mich.talebzadeh@gmail.com >> wrote:
Yes, you can validate the syntax of your PySpark SQL queries without connecting to an actual dataset or running the queries on a cluster.
PySpark provides a method for syntax validation without executing the query. Something like below
____ __
/ __/__ ___ _____/ /__
_\ \/ _ \/ _ `/ __/ '_/
/__ / .__/\_,_/_/ /_/\_\ version 3.4.0
/_/
Using Python version 3.9.16 (main, Apr 24 2023 10:36:11)
Spark context Web UI available at http://rhes75:4040 <http://rhes75:4040/ >
Spark context available as 'sc' (master = local[*], app id = local-1703410019374).
SparkSession available as 'spark'.
>>> from pyspark.sql import SparkSession
>>> spark = SparkSession.builder.appName("validate").getOrCreate()
23/12/24 09:28:02 WARN SparkSession: Using an existing Spark session; only runtime SQL configurations will take effect.
>>> sql = "SELECT * FROM <TABLE> WHERE <COLUMN> = some value"
>>> try:
... spark.sql(sql)
... print("is working")
... except Exception as e:
... print(f"Syntax error: {e}")
...
Syntax error:
[PARSE_SYNTAX_ERROR] Syntax error at or near '<'.(line 1, pos 14)
== SQL ==
SELECT * FROM <TABLE> WHERE <COLUMN> = some value
--------------^^^
Here we only check for syntax errors and not the actual existence of query semantics. We are not validating against table or column existence.
This method is useful when you want to catch obvious syntax errors before submitting your PySpark job to a cluster, especially when you don't have access to the actual data.
In summary
*
Theis method validates syntax but will not catch semantic errors
*
If you need more comprehensive validation, consider using a testing framework and a small dataset.
*
For complex queries, using a linter or code analysis tool can help identify potential issues.
HTH
Mich Talebzadeh,
Dad | Technologist | Solutions Architect | Engineer
London
United Kingdom
view my Linkedin profile <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/ >
https://en.everybodywiki.com/Mich_Talebzadeh <https://en.everybodywiki.com/Mich_Talebzadeh >
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 Sun, 24 Dec 2023 at 07:57, ram manickam <ramsidmisc@gmail.com <mailto:ramsidmisc@gmail.com >> wrote:
Hello,
Is there a way to validate pyspark sql to validate only syntax errors?. I cannot connect do actual data set to perform this validation. Any help would be appreciated.
Thanks
Ram
Re: Validate spark sql
Posted by Gourav Sengupta <go...@gmail.com>.
Dear friend,
thanks a ton was looking for linting for SQL for a long time, looks like
https://sqlfluff.com/ is something that can be used :)
Thank you so much, and wish you all a wonderful new year.
Regards,
Gourav
On Tue, Dec 26, 2023 at 4:42 AM Bjørn Jørgensen <bj...@gmail.com>
wrote:
> You can try sqlfluff <https://sqlfluff.com/> it's a linter for SQL code
> and it seems to have support for sparksql
> <https://pypi.org/project/sqlfluff/>
>
>
> man. 25. des. 2023 kl. 17:13 skrev ram manickam <ra...@gmail.com>:
>
>> Thanks Mich, Nicholas. I tried looking over the stack overflow post and
>> none of them
>> Seems to cover the syntax validation. Do you know if it's even possible
>> to do syntax validation in spark?
>>
>> Thanks
>> Ram
>>
>> On Sun, Dec 24, 2023 at 12:49 PM Mich Talebzadeh <
>> mich.talebzadeh@gmail.com> wrote:
>>
>>> Well not to put too finer point on it, in a public forum, one ought to
>>> respect the importance of open communication. Everyone has the right to ask
>>> questions, seek information, and engage in discussions without facing
>>> unnecessary patronization.
>>>
>>>
>>>
>>> Mich Talebzadeh,
>>> Dad | Technologist | Solutions Architect | Engineer
>>> London
>>> United Kingdom
>>>
>>>
>>> view my Linkedin profile
>>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>>>
>>>
>>> https://en.everybodywiki.com/Mich_Talebzadeh
>>>
>>>
>>>
>>> *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 Sun, 24 Dec 2023 at 18:27, Nicholas Chammas <
>>> nicholas.chammas@gmail.com> wrote:
>>>
>>>> This is a user-list question, not a dev-list question. Moving this
>>>> conversation to the user list and BCC-ing the dev list.
>>>>
>>>> Also, this statement
>>>>
>>>> > We are not validating against table or column existence.
>>>>
>>>> is not correct. When you call spark.sql(…), Spark will lookup the table
>>>> references and fail with TABLE_OR_VIEW_NOT_FOUND if it cannot find them.
>>>>
>>>> Also, when you run DDL via spark.sql(…), Spark will actually run it. So
>>>> spark.sql(“drop table my_table”) will actually drop my_table. It’s not a
>>>> validation-only operation.
>>>>
>>>> This question of validating SQL is already discussed on Stack Overflow
>>>> <https://stackoverflow.com/q/46973729/877069>. You may find some
>>>> useful tips there.
>>>>
>>>> Nick
>>>>
>>>>
>>>> On Dec 24, 2023, at 4:52 AM, Mich Talebzadeh <mi...@gmail.com>
>>>> wrote:
>>>>
>>>>
>>>> Yes, you can validate the syntax of your PySpark SQL queries without
>>>> connecting to an actual dataset or running the queries on a cluster.
>>>> PySpark provides a method for syntax validation without executing the
>>>> query. Something like below
>>>> ____ __
>>>> / __/__ ___ _____/ /__
>>>> _\ \/ _ \/ _ `/ __/ '_/
>>>> /__ / .__/\_,_/_/ /_/\_\ version 3.4.0
>>>> /_/
>>>>
>>>> Using Python version 3.9.16 (main, Apr 24 2023 10:36:11)
>>>> Spark context Web UI available at http://rhes75:4040
>>>> Spark context available as 'sc' (master = local[*], app id =
>>>> local-1703410019374).
>>>> SparkSession available as 'spark'.
>>>> >>> from pyspark.sql import SparkSession
>>>> >>> spark = SparkSession.builder.appName("validate").getOrCreate()
>>>> 23/12/24 09:28:02 WARN SparkSession: Using an existing Spark session;
>>>> only runtime SQL configurations will take effect.
>>>> >>> sql = "SELECT * FROM <TABLE> WHERE <COLUMN> = some value"
>>>> >>> try:
>>>> ... spark.sql(sql)
>>>> ... print("is working")
>>>> ... except Exception as e:
>>>> ... print(f"Syntax error: {e}")
>>>> ...
>>>> Syntax error:
>>>> [PARSE_SYNTAX_ERROR] Syntax error at or near '<'.(line 1, pos 14)
>>>>
>>>> == SQL ==
>>>> SELECT * FROM <TABLE> WHERE <COLUMN> = some value
>>>> --------------^^^
>>>>
>>>> Here we only check for syntax errors and not the actual existence of
>>>> query semantics. We are not validating against table or column existence.
>>>>
>>>> This method is useful when you want to catch obvious syntax errors
>>>> before submitting your PySpark job to a cluster, especially when you don't
>>>> have access to the actual data.
>>>>
>>>> In summary
>>>>
>>>> - Theis method validates syntax but will not catch semantic errors
>>>> - If you need more comprehensive validation, consider using a
>>>> testing framework and a small dataset.
>>>> - For complex queries, using a linter or code analysis tool can
>>>> help identify potential issues.
>>>>
>>>> HTH
>>>>
>>>>
>>>> Mich Talebzadeh,
>>>> Dad | Technologist | Solutions Architect | Engineer
>>>> London
>>>> United Kingdom
>>>>
>>>> view my Linkedin profile
>>>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>>>>
>>>>
>>>> https://en.everybodywiki.com/Mich_Talebzadeh
>>>>
>>>>
>>>> *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 Sun, 24 Dec 2023 at 07:57, ram manickam <ra...@gmail.com>
>>>> wrote:
>>>>
>>>>> Hello,
>>>>> Is there a way to validate pyspark sql to validate only syntax
>>>>> errors?. I cannot connect do actual data set to perform this validation.
>>>>> Any help would be appreciated.
>>>>>
>>>>>
>>>>> Thanks
>>>>> Ram
>>>>>
>>>>
>>>>
>
> --
> Bjørn Jørgensen
> Vestre Aspehaug 4, 6010 Ålesund
> Norge
>
> +47 480 94 297
>
Re: Validate spark sql
Posted by Bjørn Jørgensen <bj...@gmail.com>.
You can try sqlfluff <https://sqlfluff.com/> it's a linter for SQL code and
it seems to have support for sparksql <https://pypi.org/project/sqlfluff/>
man. 25. des. 2023 kl. 17:13 skrev ram manickam <ra...@gmail.com>:
> Thanks Mich, Nicholas. I tried looking over the stack overflow post and
> none of them
> Seems to cover the syntax validation. Do you know if it's even possible to
> do syntax validation in spark?
>
> Thanks
> Ram
>
> On Sun, Dec 24, 2023 at 12:49 PM Mich Talebzadeh <
> mich.talebzadeh@gmail.com> wrote:
>
>> Well not to put too finer point on it, in a public forum, one ought to
>> respect the importance of open communication. Everyone has the right to ask
>> questions, seek information, and engage in discussions without facing
>> unnecessary patronization.
>>
>>
>>
>> Mich Talebzadeh,
>> Dad | Technologist | Solutions Architect | Engineer
>> London
>> United Kingdom
>>
>>
>> view my Linkedin profile
>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>>
>>
>> https://en.everybodywiki.com/Mich_Talebzadeh
>>
>>
>>
>> *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 Sun, 24 Dec 2023 at 18:27, Nicholas Chammas <
>> nicholas.chammas@gmail.com> wrote:
>>
>>> This is a user-list question, not a dev-list question. Moving this
>>> conversation to the user list and BCC-ing the dev list.
>>>
>>> Also, this statement
>>>
>>> > We are not validating against table or column existence.
>>>
>>> is not correct. When you call spark.sql(…), Spark will lookup the table
>>> references and fail with TABLE_OR_VIEW_NOT_FOUND if it cannot find them.
>>>
>>> Also, when you run DDL via spark.sql(…), Spark will actually run it. So
>>> spark.sql(“drop table my_table”) will actually drop my_table. It’s not a
>>> validation-only operation.
>>>
>>> This question of validating SQL is already discussed on Stack Overflow
>>> <https://stackoverflow.com/q/46973729/877069>. You may find some useful
>>> tips there.
>>>
>>> Nick
>>>
>>>
>>> On Dec 24, 2023, at 4:52 AM, Mich Talebzadeh <mi...@gmail.com>
>>> wrote:
>>>
>>>
>>> Yes, you can validate the syntax of your PySpark SQL queries without
>>> connecting to an actual dataset or running the queries on a cluster.
>>> PySpark provides a method for syntax validation without executing the
>>> query. Something like below
>>> ____ __
>>> / __/__ ___ _____/ /__
>>> _\ \/ _ \/ _ `/ __/ '_/
>>> /__ / .__/\_,_/_/ /_/\_\ version 3.4.0
>>> /_/
>>>
>>> Using Python version 3.9.16 (main, Apr 24 2023 10:36:11)
>>> Spark context Web UI available at http://rhes75:4040
>>> Spark context available as 'sc' (master = local[*], app id =
>>> local-1703410019374).
>>> SparkSession available as 'spark'.
>>> >>> from pyspark.sql import SparkSession
>>> >>> spark = SparkSession.builder.appName("validate").getOrCreate()
>>> 23/12/24 09:28:02 WARN SparkSession: Using an existing Spark session;
>>> only runtime SQL configurations will take effect.
>>> >>> sql = "SELECT * FROM <TABLE> WHERE <COLUMN> = some value"
>>> >>> try:
>>> ... spark.sql(sql)
>>> ... print("is working")
>>> ... except Exception as e:
>>> ... print(f"Syntax error: {e}")
>>> ...
>>> Syntax error:
>>> [PARSE_SYNTAX_ERROR] Syntax error at or near '<'.(line 1, pos 14)
>>>
>>> == SQL ==
>>> SELECT * FROM <TABLE> WHERE <COLUMN> = some value
>>> --------------^^^
>>>
>>> Here we only check for syntax errors and not the actual existence of
>>> query semantics. We are not validating against table or column existence.
>>>
>>> This method is useful when you want to catch obvious syntax errors
>>> before submitting your PySpark job to a cluster, especially when you don't
>>> have access to the actual data.
>>>
>>> In summary
>>>
>>> - Theis method validates syntax but will not catch semantic errors
>>> - If you need more comprehensive validation, consider using a
>>> testing framework and a small dataset.
>>> - For complex queries, using a linter or code analysis tool can help
>>> identify potential issues.
>>>
>>> HTH
>>>
>>>
>>> Mich Talebzadeh,
>>> Dad | Technologist | Solutions Architect | Engineer
>>> London
>>> United Kingdom
>>>
>>> view my Linkedin profile
>>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>>>
>>>
>>> https://en.everybodywiki.com/Mich_Talebzadeh
>>>
>>>
>>> *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 Sun, 24 Dec 2023 at 07:57, ram manickam <ra...@gmail.com> wrote:
>>>
>>>> Hello,
>>>> Is there a way to validate pyspark sql to validate only syntax errors?.
>>>> I cannot connect do actual data set to perform this validation. Any
>>>> help would be appreciated.
>>>>
>>>>
>>>> Thanks
>>>> Ram
>>>>
>>>
>>>
--
Bjørn Jørgensen
Vestre Aspehaug 4, 6010 Ålesund
Norge
+47 480 94 297
Re: Validate spark sql
Posted by ram manickam <ra...@gmail.com>.
Thanks Mich, Nicholas. I tried looking over the stack overflow post and
none of them
Seems to cover the syntax validation. Do you know if it's even possible to
do syntax validation in spark?
Thanks
Ram
On Sun, Dec 24, 2023 at 12:49 PM Mich Talebzadeh <mi...@gmail.com>
wrote:
> Well not to put too finer point on it, in a public forum, one ought to
> respect the importance of open communication. Everyone has the right to ask
> questions, seek information, and engage in discussions without facing
> unnecessary patronization.
>
>
>
> Mich Talebzadeh,
> Dad | Technologist | Solutions Architect | Engineer
> London
> United Kingdom
>
>
> view my Linkedin profile
> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>
>
> https://en.everybodywiki.com/Mich_Talebzadeh
>
>
>
> *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 Sun, 24 Dec 2023 at 18:27, Nicholas Chammas <ni...@gmail.com>
> wrote:
>
>> This is a user-list question, not a dev-list question. Moving this
>> conversation to the user list and BCC-ing the dev list.
>>
>> Also, this statement
>>
>> > We are not validating against table or column existence.
>>
>> is not correct. When you call spark.sql(…), Spark will lookup the table
>> references and fail with TABLE_OR_VIEW_NOT_FOUND if it cannot find them.
>>
>> Also, when you run DDL via spark.sql(…), Spark will actually run it. So
>> spark.sql(“drop table my_table”) will actually drop my_table. It’s not a
>> validation-only operation.
>>
>> This question of validating SQL is already discussed on Stack Overflow
>> <https://stackoverflow.com/q/46973729/877069>. You may find some useful
>> tips there.
>>
>> Nick
>>
>>
>> On Dec 24, 2023, at 4:52 AM, Mich Talebzadeh <mi...@gmail.com>
>> wrote:
>>
>>
>> Yes, you can validate the syntax of your PySpark SQL queries without
>> connecting to an actual dataset or running the queries on a cluster.
>> PySpark provides a method for syntax validation without executing the
>> query. Something like below
>> ____ __
>> / __/__ ___ _____/ /__
>> _\ \/ _ \/ _ `/ __/ '_/
>> /__ / .__/\_,_/_/ /_/\_\ version 3.4.0
>> /_/
>>
>> Using Python version 3.9.16 (main, Apr 24 2023 10:36:11)
>> Spark context Web UI available at http://rhes75:4040
>> Spark context available as 'sc' (master = local[*], app id =
>> local-1703410019374).
>> SparkSession available as 'spark'.
>> >>> from pyspark.sql import SparkSession
>> >>> spark = SparkSession.builder.appName("validate").getOrCreate()
>> 23/12/24 09:28:02 WARN SparkSession: Using an existing Spark session;
>> only runtime SQL configurations will take effect.
>> >>> sql = "SELECT * FROM <TABLE> WHERE <COLUMN> = some value"
>> >>> try:
>> ... spark.sql(sql)
>> ... print("is working")
>> ... except Exception as e:
>> ... print(f"Syntax error: {e}")
>> ...
>> Syntax error:
>> [PARSE_SYNTAX_ERROR] Syntax error at or near '<'.(line 1, pos 14)
>>
>> == SQL ==
>> SELECT * FROM <TABLE> WHERE <COLUMN> = some value
>> --------------^^^
>>
>> Here we only check for syntax errors and not the actual existence of
>> query semantics. We are not validating against table or column existence.
>>
>> This method is useful when you want to catch obvious syntax errors before
>> submitting your PySpark job to a cluster, especially when you don't have
>> access to the actual data.
>>
>> In summary
>>
>> - Theis method validates syntax but will not catch semantic errors
>> - If you need more comprehensive validation, consider using a testing
>> framework and a small dataset.
>> - For complex queries, using a linter or code analysis tool can help
>> identify potential issues.
>>
>> HTH
>>
>>
>> Mich Talebzadeh,
>> Dad | Technologist | Solutions Architect | Engineer
>> London
>> United Kingdom
>>
>> view my Linkedin profile
>> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>>
>>
>> https://en.everybodywiki.com/Mich_Talebzadeh
>>
>>
>> *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 Sun, 24 Dec 2023 at 07:57, ram manickam <ra...@gmail.com> wrote:
>>
>>> Hello,
>>> Is there a way to validate pyspark sql to validate only syntax errors?.
>>> I cannot connect do actual data set to perform this validation. Any
>>> help would be appreciated.
>>>
>>>
>>> Thanks
>>> Ram
>>>
>>
>>
Re: Validate spark sql
Posted by Mich Talebzadeh <mi...@gmail.com>.
Well not to put too finer point on it, in a public forum, one ought to
respect the importance of open communication. Everyone has the right to ask
questions, seek information, and engage in discussions without facing
unnecessary patronization.
Mich Talebzadeh,
Dad | Technologist | Solutions Architect | Engineer
London
United Kingdom
view my Linkedin profile
<https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
https://en.everybodywiki.com/Mich_Talebzadeh
*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 Sun, 24 Dec 2023 at 18:27, Nicholas Chammas <ni...@gmail.com>
wrote:
> This is a user-list question, not a dev-list question. Moving this
> conversation to the user list and BCC-ing the dev list.
>
> Also, this statement
>
> > We are not validating against table or column existence.
>
> is not correct. When you call spark.sql(…), Spark will lookup the table
> references and fail with TABLE_OR_VIEW_NOT_FOUND if it cannot find them.
>
> Also, when you run DDL via spark.sql(…), Spark will actually run it. So
> spark.sql(“drop table my_table”) will actually drop my_table. It’s not a
> validation-only operation.
>
> This question of validating SQL is already discussed on Stack Overflow
> <https://stackoverflow.com/q/46973729/877069>. You may find some useful
> tips there.
>
> Nick
>
>
> On Dec 24, 2023, at 4:52 AM, Mich Talebzadeh <mi...@gmail.com>
> wrote:
>
>
> Yes, you can validate the syntax of your PySpark SQL queries without
> connecting to an actual dataset or running the queries on a cluster.
> PySpark provides a method for syntax validation without executing the
> query. Something like below
> ____ __
> / __/__ ___ _____/ /__
> _\ \/ _ \/ _ `/ __/ '_/
> /__ / .__/\_,_/_/ /_/\_\ version 3.4.0
> /_/
>
> Using Python version 3.9.16 (main, Apr 24 2023 10:36:11)
> Spark context Web UI available at http://rhes75:4040
> Spark context available as 'sc' (master = local[*], app id =
> local-1703410019374).
> SparkSession available as 'spark'.
> >>> from pyspark.sql import SparkSession
> >>> spark = SparkSession.builder.appName("validate").getOrCreate()
> 23/12/24 09:28:02 WARN SparkSession: Using an existing Spark session; only
> runtime SQL configurations will take effect.
> >>> sql = "SELECT * FROM <TABLE> WHERE <COLUMN> = some value"
> >>> try:
> ... spark.sql(sql)
> ... print("is working")
> ... except Exception as e:
> ... print(f"Syntax error: {e}")
> ...
> Syntax error:
> [PARSE_SYNTAX_ERROR] Syntax error at or near '<'.(line 1, pos 14)
>
> == SQL ==
> SELECT * FROM <TABLE> WHERE <COLUMN> = some value
> --------------^^^
>
> Here we only check for syntax errors and not the actual existence of query
> semantics. We are not validating against table or column existence.
>
> This method is useful when you want to catch obvious syntax errors before
> submitting your PySpark job to a cluster, especially when you don't have
> access to the actual data.
>
> In summary
>
> - Theis method validates syntax but will not catch semantic errors
> - If you need more comprehensive validation, consider using a testing
> framework and a small dataset.
> - For complex queries, using a linter or code analysis tool can help
> identify potential issues.
>
> HTH
>
>
> Mich Talebzadeh,
> Dad | Technologist | Solutions Architect | Engineer
> London
> United Kingdom
>
> view my Linkedin profile
> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>
>
> https://en.everybodywiki.com/Mich_Talebzadeh
>
>
> *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 Sun, 24 Dec 2023 at 07:57, ram manickam <ra...@gmail.com> wrote:
>
>> Hello,
>> Is there a way to validate pyspark sql to validate only syntax errors?. I
>> cannot connect do actual data set to perform this validation. Any
>> help would be appreciated.
>>
>>
>> Thanks
>> Ram
>>
>
>