Workflow parameter in sql not working

StevenW · ‎10-08-2024

I'm using the following input parameters when running from a workflow:

wid_UnityCatalogName = dbutils.jobs.taskValues.get(taskKey="NB_XXX_Workflow_Parameters", key="p_UnityCatalogName", default="xx_lakehouse_dev")

dbutils.widgets.text("UnityCatalogName", wid_UnityCatalogName)

wid_UnityCatalogBronzeJDESchema = dbutils.jobs.taskValues.get(taskKey="NB_XXX_Workflow_Parameters", key="p_UnityCatalogBronzeXXXSchema", default="bronze_xxx")

dbutils.widgets.text("UnityCatalogBronzeJDESchema", wid_UnityCatalogBronzeJDESchema)

And then I run various SQL statements that use these parameters successfully.

On step 16 it fails..

create or replace temp view f0010_view as

select trim(b.ccco) as ccco

,trim(b.ccname) as ccname

,trim(b.cccrcd) as cccrcd

, cast(b.CCDFYJ as Int) as CCDFYJ

--,${UnityCatalogName}.${UnityCatalogBronzeJDESchema}.f_Convert_JDEJulian_ToDate(cast(b.CCDFYJ as Int)) as FISCAL_YEAR_START_DT

,${UnityCatalogName}.${UnityCatalogBronzeJDESchema}.f_Convert_JDEJulian_ToDate(cast(b.CCDFYJ as Int)) as FISCAL_YEAR_START_DT

,b.CCPNC as CCPNC

ERROR:

[PARSE_SYNTAX_ERROR] Syntax error at or near '.'.(line 7, pos 😎 == SQL == create or replace temp view f0010_view as select trim(b.ccco) as ccco ,trim(b.ccname) as ccname ,trim(b.cccrcd) as cccrcd , cast(b.CCDFYJ as Int) as CCDFYJ --,..f_Convert_XXXJulian_ToDate(cast(b.CCDFYJ as Int)) as FISCAL_YEAR_START_DT ,..f_Convert_JDEJulian_ToDate(cast(b.CCDFYJ as Int)) as FISCAL_YEAR_START_DT --------^^^ ,b.CCPNC as CCPNC ,b.CCCALD as CCCALD ,NOP.DESCRIPTION as NORMAL_NO_OF_PERIODS_DESC

The ",..f_Convert_XXXJulian_ToDate" is supposed to "xx_lakehouse_dev.bronze_xxx.f_Convert_XXXJulian_ToDate"

Steps 12, 13 & 14 all executed successfully with these same parameters.

Any idea what this madness is?

Thanks,

filipniziol · ‎10-08-2024

Hi @StevenW ,

I see that you are using python noteboook and then the view is created in SQL.

1. If you are using %sql magic command then to use parameters you need to reference them like $parameter or :parameter (depending on the runtime).

2. If you are using spark.sql to run a query kept in string, then you need to remove $ and use standard string interpolation:

query = f"""
CREATE OR REPLACE TEMP VIEW f0010_view AS
SELECT
    TRIM(b.ccco) AS ccco,
    TRIM(b.ccname) AS ccname,
    TRIM(b.cccrcd) AS cccrcd,
    CAST(b.CCDFYJ AS INT) AS CCDFYJ,
    {wid_UnityCatalogName}.{wid_UnityCatalogBronzeJDESchema}.f_Convert_JDEJulian_ToDate(CAST(b.CCDFYJ AS INT)) AS FISCAL_YEAR_START_DT,
    b.CCPNC AS CCPNC
FROM some_table AS b
"""

StevenW · ‎10-08-2024

Hi @filipnizio

Thanks .. yes, I'm using the %sql command. I used ${xx_lakehouse_dev} and $xx_lakehouse_dev .. for most of the sql in the same notebook, it works fine. Then "suddenly" it doesn't ..?