Parsing error while using ALTER commands on Snowflake Objects #3479

arumugam-pounraj · 2022-06-22T08:08:18Z

Search before asking

I searched the issues and found no similar issues.

What Happened

Getting parsing error while trying to parse SQL statements which uses ALTER commands.

Tried parsing below ALTER statements with SQLFLUFF and got parsing error. The statements are from Snowflake docs

ALTER ACCOUNT SET TIMEZONE = 'UTC';
ALTER DATABASE DB_TEST RENAME TO DB_TEST_NEW;
ALTER RESOURCE MONITOR RM_TEST SET CREDIT_QUOTA=2000 TRIGGERS ON 80 PERCENT DO NOTIFY ON 100 PERCENT DO SUSPEND_IMMEDIATE;
ALTER STORAGE INTEGRATION TEST_MYINT SET ENABLED = TRUE;
ALTER NETWORK POLICY MYPOLICY1 SET ALLOWED_IP_LIST=('192.168.1.0/24', '192.168.255.100');
ALTER ROLE ROLE1 RENAME TO ROLE2;
ALTER SHARE SALES_S ADD ACCOUNTS = AB12345;
ALTER FILE FORMAT MY_FORMAT SET FIELD_DELIMITER = ',';
ALTER SEQUENCE SEQ_TEST SET INCREMENT = 2;
ALTER PIPE MYPIPE SET PIPE_EXECUTION_PAUSED = TRUE;
ALTER PROCEDURE IF EXISTS PROCEDURE1(float) RENAME TO PROCEDURE2;
ALTER MASKING POLICY EMAIL_MASK SET BODY ->
  CASE
    WHEN CURRENT_ROLE() IN ('ANALYST') THEN VAL
    ELSE SHA2(VAL, 512)
  END;

Error Output
L:  31 | P:   1 |  PRS | Line 31, Position 1: Found unparsable section: "ALTER ACCOUNT SET TIMEZONE = 'UTC'"
L:  32 | P:   1 |  PRS | Line 32, Position 1: Found unparsable section: 'ALTER DATABASE DB_TEST RENAME TO DB_TEST...'
L:  34 | P:   1 |  PRS | Line 34, Position 1: Found unparsable section: 'ALTER RESOURCE MONITOR RM_TEST SET CREDI...'
L:  35 | P:   1 |  PRS | Line 35, Position 1: Found unparsable section: 'ALTER STORAGE INTEGRATION TEST_MYINT SET...'
L:  36 | P:   1 |  PRS | Line 36, Position 1: Found unparsable section: 'ALTER NETWORK POLICY MYPOLICY1 SET ALLOW...'
L:  38 | P:   1 |  PRS | Line 38, Position 1: Found unparsable section: 'ALTER ROLE ROLE1 RENAME TO ROLE2'
L:  40 | P:   1 |  PRS | Line 40, Position 1: Found unparsable section: 'ALTER SHARE SALES_S ADD ACCOUNTS = AB123...'
L:  11 | P:   1 |  PRS | Line 11, Position 1: Found unparsable section: 'ALTER FILE FORMAT MY_FORMAT SET FIELD_DE...'
L:  41 | P:   1 |  PRS | Line 41, Position 1: Found unparsable section: 'ALTER SEQUENCE MYSEQ SET INCREMENT = 2'
L:  49 | P:   1 |  PRS | Line 49, Position 1: Found unparsable section: 'ALTER PIPE MYPIPE SET PIPE_EXECUTION_PAU...'
L:  50 | P:   1 |  PRS | Line 50, Position 1: Found unparsable section: 'ALTER PROCEDURE IF EXISTS PROCEDURE1(flo...'
L:  52 | P:   1 |  PRS | Line 52, Position 1: Found unparsable section: 'ALTER MASKING POLICY EMAIL_MASK SET BODY...'

Expected Behaviour

The Statements which are mentioned above are valid statements and executed fine in the Snowflake UI without any issues

Observed Behaviour

Could see the ALTER commands are failing for some of the Snowflake objects. For other objects like Schema, Table, View etc the ALTER commands were working fine

How to reproduce

Place all the ALTER statements mentioned above in a Alter_test.sql file
Execute below command
sqlfluff lint Alter_test.sql --dialect snowflake

Dialect

Snowflake

Version

0.13.2

Configuration

[sqlfluff]
dialect = snowflake

[sqlfluff:rules:L014]
extended_capitalisation_policy = consistent

Are you willing to work on and submit a PR to address the issue?

Yes I am willing to submit a PR!

Code of Conduct

I agree to follow this project's Code of Conduct

The text was updated successfully, but these errors were encountered:

tunetheweb · 2022-06-22T09:12:04Z

At present only some of the ALTER statements have been implemented in the Snowflake dialect.

Adding these is relatively easy (as detailed in the Contributing Dialect Changes wiki), but would need someone to go through them all to create the syntax. AlterWarehouseStatementSegment would be a good example to follow.

jared-rimmer · 2023-02-17T14:38:16Z

Hi, I'm attempting to have a go at this as my first contribution via adding ALTER ACCOUNT

I was wondering if I could get some help with how to implement all the different params that are available?

I've started off with this syntax:


    _account_params = OneOf(
        Sequence("ALLOW_ID_TOKEN", Ref("EqualsSegment"), Ref("BooleanLiteralGrammar")),
        Sequence("CLIENT_ENCRYPTION_KEY_SIZE", Ref("EqualsSegment"), Ref("IntegerSegment")),
        Sequence("ENFORCE_SESSION_POLICY", Ref("EqualsSegment"), Ref("BooleanLiteralGrammar")),
        Sequence("EXTERNAL_OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST", Ref("EqualsSegment"), Ref("BooleanLiteralGrammar")),
        Sequence("INITIAL_REPLICATION_SIZE_LIMIT_IN_TB", Ref("EqualsSegment"), Ref("IntegerSegment")),

    )

Then using it in this way within the match_grammar block:

        OneOf(
            _account_params,
           ...
)

Just wondering if this makes sense and /or is the most efficient way of implementing it.

Thanks!

WittierDinosaur · 2023-02-19T18:36:07Z

Hey @jared-rimmer , that seems liek a reasonable approach. The best bet is to open a PR, and one of the maintainers will add suggestions there

nevdelap · 2023-05-10T03:41:56Z

I was about to open a similar issue, but the three ALTER statements look like they could be the same thing, and maybe the CREATEs are somehow related? This may help you test this issue's fixes, or let me know if any of them should be their own separate issue. 🙏

ALTER WAREHOUSE CI_TRANSFORMING
SET COMMENT = 'Warehouse for dbt development transformations in CI'
    , AUTO_RESUME = TRUE
    , AUTO_SUSPEND=30;

CREATE RESOURCE MONITOR DEV_TRANSFORMING
WITH
    CREDIT_QUOTA = 3  -- 3 credits per day, half of what it used to be.
    FREQUENCY = DAILY
    START_TIMESTAMP = IMMEDIATELY
    NOTIFY_USERS = (NEVD)  -- email must be verified.
    TRIGGERS
        ON 85 PERCENT DO NOTIFY
        ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER RESOURCE MONITOR DEV_TRANSFORMING
SET NOTIFY_USERS = (NEVD);

CREATE RESOURCE MONITOR CI_TRANSFORMING
WITH
    CREDIT_QUOTA = 3  -- 3 credits per day, given the other half.
    FREQUENCY = DAILY
    START_TIMESTAMP = IMMEDIATELY
    NOTIFY_USERS = (NEVD)
    TRIGGERS
        ON 85 PERCENT DO NOTIFY
        ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER RESOURCE MONITOR CI_TRANSFORMING
SET NOTIFY_USERS = (NEVD);

results in...

> docker run -it --rm -v $PWD:/sql sqlfluff/sqlfluff:2.1.0 lint --dialect snowflake sql/repro.sql                                          (snowflake)
== [sql/repro.sql] FAIL
L:   3 | P:  68 |  PRS | Line 3, Position 68: Found unparsable section: '\n    ,
                       | AUTO_RESUME = TRUE\n    , AUTO_SUS...'
L:   8 | P:   5 |  PRS | Line 8, Position 5: Found unparsable section: '\n
                       | CREDIT_QUOTA = 3  -- 3 credits per ...'
L:  17 | P:   1 |  PRS | Line 17, Position 1: Found unparsable section: 'ALTER
                       | RESOURCE MONITOR DEV_TRANSFORMING\n...'
L:  21 | P:   5 |  PRS | Line 21, Position 5: Found unparsable section: '\n
                       | CREDIT_QUOTA = 3  -- 3 credits per ...'
L:  30 | P:   1 |  PRS | Line 30, Position 1: Found unparsable section: 'ALTER
                       | RESOURCE MONITOR CI_TRANSFORMING\nS...'
WARNING: Parsing errors found and dialect is set to 'snowflake'. Have you configured your dialect correctly?
All Finished 📜 🎉!
>

with an empty .sqlfluff.

jessanmen1 · 2023-05-23T09:38:31Z

I was about to open a similar issue, but the three ALTER statements look like they could be the same thing, and maybe the CREATEs are somehow related? This may help you test this issue's fixes, or let me know if any of them should be their own separate issue. 🙏

ALTER WAREHOUSE CI_TRANSFORMING
SET COMMENT = 'Warehouse for dbt development transformations in CI'
    , AUTO_RESUME = TRUE
    , AUTO_SUSPEND=30;

CREATE RESOURCE MONITOR DEV_TRANSFORMING
WITH
    CREDIT_QUOTA = 3  -- 3 credits per day, half of what it used to be.
    FREQUENCY = DAILY
    START_TIMESTAMP = IMMEDIATELY
    NOTIFY_USERS = (NEVD)  -- email must be verified.
    TRIGGERS
        ON 85 PERCENT DO NOTIFY
        ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER RESOURCE MONITOR DEV_TRANSFORMING
SET NOTIFY_USERS = (NEVD);

CREATE RESOURCE MONITOR CI_TRANSFORMING
WITH
    CREDIT_QUOTA = 3  -- 3 credits per day, given the other half.
    FREQUENCY = DAILY
    START_TIMESTAMP = IMMEDIATELY
    NOTIFY_USERS = (NEVD)
    TRIGGERS
        ON 85 PERCENT DO NOTIFY
        ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER RESOURCE MONITOR CI_TRANSFORMING
SET NOTIFY_USERS = (NEVD);

results in...

> docker run -it --rm -v $PWD:/sql sqlfluff/sqlfluff:2.1.0 lint --dialect snowflake sql/repro.sql                                          (snowflake)
== [sql/repro.sql] FAIL
L:   3 | P:  68 |  PRS | Line 3, Position 68: Found unparsable section: '\n    ,
                       | AUTO_RESUME = TRUE\n    , AUTO_SUS...'
L:   8 | P:   5 |  PRS | Line 8, Position 5: Found unparsable section: '\n
                       | CREDIT_QUOTA = 3  -- 3 credits per ...'
L:  17 | P:   1 |  PRS | Line 17, Position 1: Found unparsable section: 'ALTER
                       | RESOURCE MONITOR DEV_TRANSFORMING\n...'
L:  21 | P:   5 |  PRS | Line 21, Position 5: Found unparsable section: '\n
                       | CREDIT_QUOTA = 3  -- 3 credits per ...'
L:  30 | P:   1 |  PRS | Line 30, Position 1: Found unparsable section: 'ALTER
                       | RESOURCE MONITOR CI_TRANSFORMING\nS...'
WARNING: Parsing errors found and dialect is set to 'snowflake'. Have you configured your dialect correctly?
All Finished 📜 🎉!
>

with an empty .sqlfluff.

I'm also facing parse errors when creating a STORAGE INTEGRATION like this:

CREATE OR REPLACE STORAGE INTEGRATION whatever
TYPE = EXTERNAL_STAGE
ENABLED = TRUE
STORAGE_PROVIDER = 'S3'
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::we:role/whatever'
STORAGE_ALLOWED_LOCATIONS = ('s3://whatever/');

The parsing violation is:

==== parsing violations ====
L:   9 | P:  15 |  PRS | Line 9, Position 15: Found unparsable section: "\nSTORAGE_PROVIDER
                       | = 'S3'\nSTORAGE_AWS_ROL..."
WARNING: Parsing errors found and dialect is set to 'snowflake'. Have you configured your dialect correctly?

I understand that this is not support and I might try to contribute, is that right?

Orangemic · 2023-06-14T12:16:32Z

Hi, I am running into a similar issue whereby all ALTER WAREHOUSE queries are producing PRS errors. These are examples of queries I'm trying to run:

  ALTER WAREHOUSE WH_TEST SET AUTO_SUSPEND = 600;
  ALTER WAREHOUSE WH_TEST SET AUTO_RESUME = FALSE;
  ALTER WAREHOUSE WH_TEST SET MIN_CLUSTER_COUNT = 1;
  ALTER WAREHOUSE WH_TEST SET MAX_CLUSTER_COUNT = 1;
  ALTER WAREHOUSE WH_TEST SET SCALING_POLICY = 'ECONOMY';

Now I'm new here, so I want to know if someone is already working on this feature?
Or can I contribute by adding these statements somewhere? Thanks in advance!

Kylea650 · 2023-09-27T07:43:43Z

I'm also facing parse errors when creating a STORAGE INTEGRATION like this:

CREATE OR REPLACE STORAGE INTEGRATION whatever
TYPE = EXTERNAL_STAGE
ENABLED = TRUE
STORAGE_PROVIDER = 'S3'
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::we:role/whatever'
STORAGE_ALLOWED_LOCATIONS = ('s3://whatever/');

The parsing violation is:

==== parsing violations ====
L:   9 | P:  15 |  PRS | Line 9, Position 15: Found unparsable section: "\nSTORAGE_PROVIDER
                       | = 'S3'\nSTORAGE_AWS_ROL..."
WARNING: Parsing errors found and dialect is set to 'snowflake'. Have you configured your dialect correctly?

I understand that this is not support and I might try to contribute, is that right?

This should be solved be #5236. sqlfluff previously expected the storage_provider to be unquoted e.g. S3 instead of 'S3'.

moreaupascal56 · 2023-10-03T08:56:43Z

Hi, I am running into a similar issue whereby all ALTER WAREHOUSE queries are producing PRS errors. These are examples of queries I'm trying to run:
  ALTER WAREHOUSE WH_TEST SET AUTO_SUSPEND = 600;
  ALTER WAREHOUSE WH_TEST SET AUTO_RESUME = FALSE;
  ALTER WAREHOUSE WH_TEST SET MIN_CLUSTER_COUNT = 1;
  ALTER WAREHOUSE WH_TEST SET MAX_CLUSTER_COUNT = 1;
  ALTER WAREHOUSE WH_TEST SET SCALING_POLICY = 'ECONOMY';
Now I'm new here, so I want to know if someone is already working on this feature? Or can I contribute by adding these statements somewhere? Thanks in advance!

Hello, these Alter warehouse queries have been fixed by #5264

moreaupascal56 · 2023-10-03T09:12:49Z

Hello there, I tested (sqlfluff 2.3.2) all the queries that have been mentioned in this issue to be able to work more easily on this issue.
The queries that are still failing are the ones below:

-- warehouse
-- Here i think the issue are the commas
ALTER WAREHOUSE CI_TRANSFORMING
SET COMMENT = 'Warehouse for dbt development transformations in CI'
    , AUTO_RESUME = TRUE
    , AUTO_SUSPEND=30;


--Resource monitor
CREATE RESOURCE MONITOR DEV_TRANSFORMING
WITH
    CREDIT_QUOTA = 3  -- 3 credits per day, half of what it used to be.
    FREQUENCY = DAILY
    START_TIMESTAMP = IMMEDIATELY
    NOTIFY_USERS = (NEVD)  -- email must be verified.
    TRIGGERS
        ON 85 PERCENT DO NOTIFY
        ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER RESOURCE MONITOR DEV_TRANSFORMING
SET NOTIFY_USERS = (NEVD);

CREATE RESOURCE MONITOR CI_TRANSFORMING
WITH
    CREDIT_QUOTA = 3  -- 3 credits per day, given the other half.
    FREQUENCY = DAILY
    START_TIMESTAMP = IMMEDIATELY
    NOTIFY_USERS = (NEVD)
    TRIGGERS
        ON 85 PERCENT DO NOTIFY
        ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER RESOURCE MONITOR CI_TRANSFORMING
SET NOTIFY_USERS = (NEVD);

ALTER RESOURCE MONITOR RM_TEST SET CREDIT_QUOTA=2000 TRIGGERS ON 80 PERCENT DO NOTIFY ON 100 PERCENT DO SUSPEND_IMMEDIATE;



-- Account
ALTER ACCOUNT SET TIMEZONE = 'UTC';

-- Database
ALTER DATABASE DB_TEST RENAME TO DB_TEST_NEW;

-- Network policy
ALTER NETWORK POLICY MYPOLICY1 SET ALLOWED_IP_LIST=('192.168.1.0/24', '192.168.255.100');

-- sequence
ALTER SEQUENCE SEQ_TEST SET INCREMENT = 2;

--procedure
ALTER PROCEDURE IF EXISTS PROCEDURE1(float) RENAME TO PROCEDURE2;

-- MASKING POLICY
ALTER MASKING POLICY EMAIL_MASK SET BODY ->
  CASE
    WHEN CURRENT_ROLE() IN ('ANALYST') THEN VAL
    ELSE SHA2(VAL, 512)
  END;

I think we could open one PR per resource type, because it will be easier to develop and review. If anyone wants to contribute on one sub-part of this issue feel free to just comment that you are handling one resource type and let's do it :D
We can monitor the progress here:

PS: If anyone is motivated enough to create distinct issues per resource type that would be better but I think we can work with this issue like I said above. Btw it is easier to have issues related to one problem (so one resource type for example)

PS2: If anyone wants to contribute but is unsure, feel free to seek for help :)

jmks · 2023-10-05T00:23:49Z

I'll fix the ALTER ACCOUNT since that one gets me at $WORK 😄

WittierDinosaur · 2023-11-06T09:28:41Z

I think this issue is now complete. All of it parses on main. Thanks everyone for contributing to this anthology issue!

arumugam-pounraj added the bug Something isn't working label Jun 22, 2022

tunetheweb added postgres Issues relating to the Postgres dialect snowflake Issues related to the Snowflake dialect good first issue Good for newcomers and removed postgres Issues relating to the Postgres dialect labels Jun 22, 2022

moreaupascal56 mentioned this issue Sep 20, 2022

Snowflake: AlterShareStatementSegment and CreateDatabaseFromShareStatementSegment #3858

Merged

1 task

erevear mentioned this issue Oct 13, 2022

Snowflake: ALTER TABLE constraint actions #3959

Merged

1 task

This was referenced Feb 17, 2023

Add Snowflake dialect ALTER ROLE segment #4403

Merged

Snowflake: ALTER STORAGE INTEGRATION segment #4406

Merged

This was referenced Oct 6, 2023

Snowflake: Parse ALTER ACCOUNT statements #5283

Merged

Snowflake: Parse ALTER DATABASE statement #5284

Merged

moreaupascal56 mentioned this issue Oct 9, 2023

Snowflake: create AlterProcedureStatementSegment #5291

Merged

1 task

jmks mentioned this issue Oct 24, 2023

Snowflake: Support ALTER MASKING POLICY #5350

Merged

1 task

WittierDinosaur mentioned this issue Nov 5, 2023

Snowflake: Support AlterNetworkPolicy Statements #5377

Merged

1 task

WittierDinosaur closed this as completed Nov 6, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Parsing error while using ALTER commands on Snowflake Objects #3479

Parsing error while using ALTER commands on Snowflake Objects #3479

arumugam-pounraj commented Jun 22, 2022

tunetheweb commented Jun 22, 2022

jared-rimmer commented Feb 17, 2023

WittierDinosaur commented Feb 19, 2023

nevdelap commented May 10, 2023 •

edited

jessanmen1 commented May 23, 2023

Orangemic commented Jun 14, 2023

Kylea650 commented Sep 27, 2023

moreaupascal56 commented Oct 3, 2023 •

edited

moreaupascal56 commented Oct 3, 2023 •

edited

jmks commented Oct 5, 2023

WittierDinosaur commented Nov 6, 2023

Parsing error while using ALTER commands on Snowflake Objects #3479

Parsing error while using ALTER commands on Snowflake Objects #3479

Comments

arumugam-pounraj commented Jun 22, 2022

Search before asking

What Happened

Expected Behaviour

Observed Behaviour

How to reproduce

Dialect

Version

Configuration

Are you willing to work on and submit a PR to address the issue?

Code of Conduct

tunetheweb commented Jun 22, 2022

jared-rimmer commented Feb 17, 2023

WittierDinosaur commented Feb 19, 2023

nevdelap commented May 10, 2023 • edited

jessanmen1 commented May 23, 2023

Orangemic commented Jun 14, 2023

Kylea650 commented Sep 27, 2023

moreaupascal56 commented Oct 3, 2023 • edited

moreaupascal56 commented Oct 3, 2023 • edited

jmks commented Oct 5, 2023

WittierDinosaur commented Nov 6, 2023

nevdelap commented May 10, 2023 •

edited

moreaupascal56 commented Oct 3, 2023 •

edited

moreaupascal56 commented Oct 3, 2023 •

edited