substitution invocator for a variable in PyCharm sql

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

substitution invocator for a variable in PyCharm sql

Mich Talebzadeh
In Spark/Scala you can use 's' substitution invocator for a variable in sql call, for example

    var sqltext =
      s"""
        INSERT INTO TABLE ${broadcastStagingConfig.broadcastTable} PARTITION (broadcastId = ${broadcastStagingConfig.broadcastValue},brand)
        SELECT
          ocis_mrg_pty_id AS partyId
        , target_mobile_no AS phoneNumber
        , brand
        FROM ${tag}
       WHERE
          length(target_mobile_no) = ${broadcastStagingConfig.mobileNoLength}
       AND
          substring(target_mobile_no,1,1) = ${broadcastStagingConfig.ukMobileNoStart}
        """
    spark.sql(sqltext)

However, in PySpark the same fails

rows = spark.sql(s"""SELECT COUNT(1) FROM ${fullyQualifiedTableName}""").collect()[0][0]
                                                                               ^
SyntaxError: invalid syntax

What is the correct substitute invocation in PyCharm if any?

Thanks,



LinkedIn  https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw

 



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.

 

Reply | Threaded
Open this post in threaded view
|

Re: substitution invocator for a variable in PyCharm sql

Russell Spitzer
The feature you are looking for is called "String Interpolation" and is available in python 3.6. It uses a different syntax than scala's
https://www.programiz.com/python-programming/string-interpolation

On Mon, Dec 7, 2020 at 7:05 AM Mich Talebzadeh <[hidden email]> wrote:
In Spark/Scala you can use 's' substitution invocator for a variable in sql call, for example

    var sqltext =
      s"""
        INSERT INTO TABLE ${broadcastStagingConfig.broadcastTable} PARTITION (broadcastId = ${broadcastStagingConfig.broadcastValue},brand)
        SELECT
          ocis_mrg_pty_id AS partyId
        , target_mobile_no AS phoneNumber
        , brand
        FROM ${tag}
       WHERE
          length(target_mobile_no) = ${broadcastStagingConfig.mobileNoLength}
       AND
          substring(target_mobile_no,1,1) = ${broadcastStagingConfig.ukMobileNoStart}
        """
    spark.sql(sqltext)

However, in PySpark the same fails

rows = spark.sql(s"""SELECT COUNT(1) FROM ${fullyQualifiedTableName}""").collect()[0][0]
                                                                               ^
SyntaxError: invalid syntax

What is the correct substitute invocation in PyCharm if any?

Thanks,



LinkedIn  https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw

 



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.

 

Reply | Threaded
Open this post in threaded view
|

Re: substitution invocator for a variable in PyCharm sql

Mich Talebzadeh
Thanks Russell f-string interpolation helped. Replace Scala 's' with Python 'f'!

Mich


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, 7 Dec 2020 at 14:13, Russell Spitzer <[hidden email]> wrote:
The feature you are looking for is called "String Interpolation" and is available in python 3.6. It uses a different syntax than scala's
https://www.programiz.com/python-programming/string-interpolation

On Mon, Dec 7, 2020 at 7:05 AM Mich Talebzadeh <[hidden email]> wrote:
In Spark/Scala you can use 's' substitution invocator for a variable in sql call, for example

    var sqltext =
      s"""
        INSERT INTO TABLE ${broadcastStagingConfig.broadcastTable} PARTITION (broadcastId = ${broadcastStagingConfig.broadcastValue},brand)
        SELECT
          ocis_mrg_pty_id AS partyId
        , target_mobile_no AS phoneNumber
        , brand
        FROM ${tag}
       WHERE
          length(target_mobile_no) = ${broadcastStagingConfig.mobileNoLength}
       AND
          substring(target_mobile_no,1,1) = ${broadcastStagingConfig.ukMobileNoStart}
        """
    spark.sql(sqltext)

However, in PySpark the same fails

rows = spark.sql(s"""SELECT COUNT(1) FROM ${fullyQualifiedTableName}""").collect()[0][0]
                                                                               ^
SyntaxError: invalid syntax

What is the correct substitute invocation in PyCharm if any?

Thanks,



LinkedIn  https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw

 



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.