sqlfluff: Error due to declare statement with bigquery dialect

Question

My main issue here is that I've got an error with this code when I do a an sql lint, however I don't know how to specify to do an except for this rule in my .sqlfluff file when this is a variable from a declare statement as you can see above. Any idea ?

DECLARE N INT64 DEFAULT 3;
DECLARE periode DATE DEFAULT CURRENT_DATE() - 1;

WITH article AS (
    SELECT
        ref.offer,
        events.page_full_name AS article_name,
        COUNT(events.visit_id) AS nb_visit,
        RANK() OVER (
            PARTITION BY ref.offer
            ORDER BY COUNT(events.visit_id) DESC
        ) AS article_ranking
    FROM `events` AS events
    LEFT JOIN `reference` AS ref USING (site_id)
    WHERE
        events.event_day = periode
        AND (
            (
                LOWER(events.content) LIKE "%article%"
                AND ref.channel IN (
                    "Régions", "élèves", "La 1ère", "info"
                )
            )
            OR (
                events.page_chapter2 LIKE "%article%" AND ref.channel = "La 1ère"
            )
        )
    GROUP BY 1, 2
)

SELECT
    offer,
    article_name,
    nb_visit
FROM article
WHERE article.article_ranking <= N

This is my .sqlfluff code

[sqlfluff]

dialect = bigquery

exclude_rules = ST07

enter image description here

I couldn't find any solution to my problem.

@nbk I use variable to easily modify the period of time and ranking so I can reuse it later so I need it. — aegn, Jul 07 '23 at 16:27

score 0 · Answer 1 · answered Jul 07 '23 at 17:20

That ios not hpow sqlfluff works see https://docs.sqlfluff.com/en/stable/configuration.html

[sqlfluff]

dialect = bigquery

exclude_rules = ST07
period = '2023-07-06'
N = 3

and the templatwe would be

WITH article AS (
    SELECT
        ref.offer,
        events.page_full_name AS article_name,
        COUNT(events.visit_id) AS nb_visit,
        RANK() OVER (
            PARTITION BY ref.offer
            ORDER BY COUNT(events.visit_id) DESC
        ) AS article_ranking
    FROM `events` AS events
    LEFT JOIN `reference` AS ref USING (site_id)
    WHERE
        events.event_day = {{ periode }}
        AND (
            (
                LOWER(events.content) LIKE "%article%"
                AND ref.channel IN (
                    "Régions", "élèves", "La 1ère", "info"
                )
            )
            OR (
                events.page_chapter2 LIKE "%article%" AND ref.channel = "La 1ère"
            )
        )
    GROUP BY 1, 2
)

SELECT
    offer,
    article_name,
    nb_visit
FROM article
WHERE article.article_ranking <= {{ N }}

When you follow the link you can see more examples, how to use variables

sqlfluff: Error due to declare statement with bigquery dialect

1 Answers1