[GH-ISSUE #75] Escape reserved words in templates for BigQuery #45

New issue

Closed

opened 2026-03-23 20:28:42 +00:00 by mirror · 1 comment

mirror commented

2026-03-23 20:28:42 +00:00

Owner

Originally created by @lbertelo01 on GitHub (Jun 13, 2024).
Original GitHub issue: https://github.com/RADar-AZDelta/Rabbit-in-a-Blender/issues/75

The note_nlp table uses the reserverd word 'offset' which needs to be escaped in the template(s).

During an etl test for note_nlp, this template failed:
https://github.com/RADar-AZDelta/Rabbit-in-a-Blender/blob/main/src/riab/etl/bigquery/templates/etl/%7Bomop_table%7D_merge.sql.jinja

This is the query that fails:

Originally created by @lbertelo01 on GitHub (Jun 13, 2024). Original GitHub issue: https://github.com/RADar-AZDelta/Rabbit-in-a-Blender/issues/75 The note_nlp table uses the reserverd word 'offset' which needs to be escaped in the template(s). During an etl test for note_nlp, this template failed: https://github.com/RADar-AZDelta/Rabbit-in-a-Blender/blob/main/src/riab/etl/bigquery/templates/etl/%7Bomop_table%7D_merge.sql.jinja This is the query that fails: <img width="837" alt="image" src="https://github.com/RADar-AZDelta/Rabbit-in-a-Blender/assets/98480071/4963f4d6-f95a-4f7c-8a33-d48427fdb0a5">

mirror closed this issue

2026-03-23 20:28:42 +00:00

mirror commented

2026-03-23 20:28:42 +00:00

Author

Owner

@FannyDHondt commented on GitHub (Jul 4, 2024):

Note_nlp werkt nog niet. Ik krijg volgende error

Name "offset" not found inside t at [13:15]

Query:

CREATE OR REPLACE TABLE `azd-dev-data-fdhondt01.omop.note_nlp`
LIKE `azd-dev-data-fdhondt01.omop.note_nlp`AS (
    WITH cte_uploaded_tables AS (
        SELECT *
        FROM `azd-dev-data-fdhondt01.work.note_nlp__upload__davinci_prostaat`
    ), cte_keys_swapped AS (
        SELECT
            swap_pk.y as `note_nlp_id`,
            IFNULL(swap_fk0.y, 0) as `note_id`,
            IFNULL(swap_ci0.conceptId, 0) as `section_concept_id`,
            t.`snippet`,
            t.`"offset"`,
            t.`lexical_variant`,
            IFNULL(swap_ci1.conceptId, 0) as `note_nlp_concept_id`,
            IFNULL(swap_ci2.conceptId, 0) as `note_nlp_source_concept_id`,
            t.`nlp_system`,
            t.`nlp_date`,
            t.`nlp_datetime`,
            t.`term_exists`,
            t.`term_temporal`,
            t.`term_modifiers`
        FROM cte_uploaded_tables t
        LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__section_concept_id_usagi` swap_ci0 on swap_ci0.sourceCode = t.`section_concept_id`
                and swap_ci0.mappingStatus = 'APPROVED'
        LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__note_nlp_concept_id_usagi` swap_ci1 on swap_ci1.sourceCode = t.`note_nlp_concept_id`
                and swap_ci1.mappingStatus = 'APPROVED'
        LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__note_nlp_source_concept_id_usagi` swap_ci2 on swap_ci2.sourceCode = t.`note_nlp_source_concept_id`
                and swap_ci2.mappingStatus = 'APPROVED'
        INNER JOIN `azd-dev-data-fdhondt01.work.note_nlp_id_swap` swap_pk on swap_pk.x = t.note_nlp_id
            and IFNULL(swap_pk.`section_concept_id`, 0) = IFNULL(swap_ci0.conceptId, 0)
            and IFNULL(swap_pk.`note_nlp_concept_id`, 0) = IFNULL(swap_ci1.conceptId, 0)
            and IFNULL(swap_pk.`note_nlp_source_concept_id`, 0) = IFNULL(swap_ci2.conceptId, 0)
        LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_id_swap` swap_fk0 on swap_fk0.x = t.`note_id` 
    ), cte_duplicates AS (
        SELECT *
            , ROW_NUMBER() OVER(
                PARTITION BY
                note_nlp_id
                , `section_concept_id`
                , `note_nlp_concept_id`
                , `note_nlp_source_concept_id`
            ) AS rn
        FROM cte_keys_swapped
    )
    SELECT * EXCEPT(rn)
    FROM cte_duplicates
    WHERE rn = 1
)

Ik heb de query eens gerund in BigQuery en het werkt als t."offset", veranderd wordt in t.offset,

@FannyDHondt commented on GitHub (Jul 4, 2024): Note_nlp werkt nog niet. Ik krijg volgende error ``` Name "offset" not found inside t at [13:15] Query: CREATE OR REPLACE TABLE `azd-dev-data-fdhondt01.omop.note_nlp` LIKE `azd-dev-data-fdhondt01.omop.note_nlp`AS ( WITH cte_uploaded_tables AS ( SELECT * FROM `azd-dev-data-fdhondt01.work.note_nlp__upload__davinci_prostaat` ), cte_keys_swapped AS ( SELECT swap_pk.y as `note_nlp_id`, IFNULL(swap_fk0.y, 0) as `note_id`, IFNULL(swap_ci0.conceptId, 0) as `section_concept_id`, t.`snippet`, t.`"offset"`, t.`lexical_variant`, IFNULL(swap_ci1.conceptId, 0) as `note_nlp_concept_id`, IFNULL(swap_ci2.conceptId, 0) as `note_nlp_source_concept_id`, t.`nlp_system`, t.`nlp_date`, t.`nlp_datetime`, t.`term_exists`, t.`term_temporal`, t.`term_modifiers` FROM cte_uploaded_tables t LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__section_concept_id_usagi` swap_ci0 on swap_ci0.sourceCode = t.`section_concept_id` and swap_ci0.mappingStatus = 'APPROVED' LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__note_nlp_concept_id_usagi` swap_ci1 on swap_ci1.sourceCode = t.`note_nlp_concept_id` and swap_ci1.mappingStatus = 'APPROVED' LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_nlp__note_nlp_source_concept_id_usagi` swap_ci2 on swap_ci2.sourceCode = t.`note_nlp_source_concept_id` and swap_ci2.mappingStatus = 'APPROVED' INNER JOIN `azd-dev-data-fdhondt01.work.note_nlp_id_swap` swap_pk on swap_pk.x = t.note_nlp_id and IFNULL(swap_pk.`section_concept_id`, 0) = IFNULL(swap_ci0.conceptId, 0) and IFNULL(swap_pk.`note_nlp_concept_id`, 0) = IFNULL(swap_ci1.conceptId, 0) and IFNULL(swap_pk.`note_nlp_source_concept_id`, 0) = IFNULL(swap_ci2.conceptId, 0) LEFT OUTER JOIN `azd-dev-data-fdhondt01.work.note_id_swap` swap_fk0 on swap_fk0.x = t.`note_id` ), cte_duplicates AS ( SELECT * , ROW_NUMBER() OVER( PARTITION BY note_nlp_id , `section_concept_id` , `note_nlp_concept_id` , `note_nlp_source_concept_id` ) AS rn FROM cte_keys_swapped ) SELECT * EXCEPT(rn) FROM cte_duplicates WHERE rn = 1 ) ``` Ik heb de query eens gerund in BigQuery en het werkt als `t.`"offset"`,` veranderd wordt in `t.`offset`,`