Parsing "warehouse" and "schedule" in "create task" fails

[JoeHut]

The CREATE TASK statement in the snowflake dialect may have additional keywords, that are not supported in sqlfluff (see https://docs.snowflake.com/en/sql-reference/sql/create-task.html#syntax). Especially the WAREHOUSE and SCHEDULE is not supported.

Minimal example (as given on the official documentation):

CREATE TASK mytask_hour
  WAREHOUSE = mywh
  SCHEDULE = 'USING CRON 0 9-17 * * SUN America/Los_Angeles'
  TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
AS
INSERT INTO mytable(ts) VALUES(CURRENT_TIMESTAMP);

Output of sqlfluff parse --dialect snowflake test.sql

[L:  1, P:  1]      |file:
[L:  1, P:  1]      |    statement:
[L:  1, P:  1]      |        create_statement:
[L:  1, P:  1]      |            keyword:                                          'CREATE'
[L:  1, P:  7]      |            whitespace:                                       ' '
[L:  1, P:  8]      |            keyword:                                          'TASK'
[L:  1, P: 12]      |            whitespace:                                       ' '
[L:  1, P: 13]      |            object_reference:
[L:  1, P: 13]      |                identifier:                                   'mytask_hour'
[L:  1, P: 24]      |        unparsable:                                           !! Expected: 'Nothing...'
[L:  1, P: 24]      |            newline:                                          '\n'
[L:  2, P:  1]      |            whitespace:                                       '  '
[L:  2, P:  3]      |            raw:                                              'WAREHOUSE'
[L:  2, P: 12]      |            whitespace:                                       ' '
[L:  2, P: 13]      |            raw:                                              '='
[L:  2, P: 14]      |            whitespace:                                       ' '
[L:  2, P: 15]      |            raw:                                              'mywh'
[L:  2, P: 19]      |            newline:                                          '\n'
[L:  3, P:  1]      |            whitespace:                                       '  '
[L:  3, P:  3]      |            raw:                                              'SCHEDULE'
[L:  3, P: 11]      |            whitespace:                                       ' '
[L:  3, P: 12]      |            raw:                                              '='
[L:  3, P: 13]      |            whitespace:                                       ' '
[L:  3, P: 14]      |            raw:                                              "'USING CRON 0 9-17 * * SUN America/Los_Angeles'"
[L:  3, P: 61]      |            newline:                                          '\n'
[L:  4, P:  1]      |            whitespace:                                       '  '
[L:  4, P:  3]      |            raw:                                              'TIMESTAMP_INPUT_FORMAT'
[L:  4, P: 25]      |            whitespace:                                       ' '
[L:  4, P: 26]      |            raw:                                              '='
[L:  4, P: 27]      |            whitespace:                                       ' '
[L:  4, P: 28]      |            raw:                                              "'YYYY-MM-DD HH24'"
[L:  4, P: 45]      |            newline:                                          '\n'
[L:  5, P:  1]      |            raw:                                              'AS'
[L:  5, P:  3]      |            newline:                                          '\n'
[L:  6, P:  1]      |            raw:                                              'INSERT'
[L:  6, P:  7]      |            whitespace:                                       ' '
[L:  6, P:  8]      |            raw:                                              'INTO'
[L:  6, P: 12]      |            whitespace:                                       ' '
[L:  6, P: 13]      |            raw:                                              'mytable'
[L:  6, P: 20]      |            bracketed:
[L:  6, P: 20]      |                start_bracket:                                '('
[L:  6, P: 21]      |                raw:                                          'ts'
[L:  6, P: 23]      |                end_bracket:                                  ')'
[L:  6, P: 24]      |            whitespace:                                       ' '
[L:  6, P: 25]      |            raw:                                              'VALUES'
[L:  6, P: 31]      |            bracketed:
[L:  6, P: 31]      |                start_bracket:                                '('
[L:  6, P: 32]      |                raw:                                          'CURRENT_TIMESTAMP'
[L:  6, P: 49]      |                end_bracket:                                  ')'
[L:  6, P: 50]      |    statement_terminator:                                     ';'
[L:  6, P: 51]      |    newline:                                                  '\n'

==== parsing violations ====
L:   1 | P:  24 |  PRS | Line 1, Position 24: Found unparsable section: "\n WAREHOUSE =
                       | mywh\n SCHEDULE = 'USING ..."

Seems like there is already support for the keywords in the ALTER TASK statement. I will try and make a PR myself.

Expected Behaviour

Successful parsing and linting of the example

Observed Behaviour

WAREHOUSE and SCHEDULE are not parsable

Steps to Reproduce

Dialect

snowflake

Version

sqlfluff, version 0.6.9
Python 3.9.7

Configuration

Default Configuration

[tunetheweb]

This is a fairly standard dialect enhancement if you (or anyone else!) is looking to become a first time contributor!

This syntax is currently processed in the generic CreateStatementSegment which doesn't support these keywords:

sqlfluff/src/sqlfluff/dialects/dialect_snowflake.py

Lines 1358 to 1369 in 56c8a02

	@snowflake_dialect.segment()
	class CreateStatementSegment(BaseSegment):
	"""A snowflake `CREATE` statement.
	https://docs.snowflake.com/en/sql-reference/sql/create.html
	"""
	type = "create_statement"
	match_grammar = Sequence(
	"CREATE",
	Sequence("OR", "REPLACE", optional=True),

Probably worth adding a dedicated CreateTaskSegment (and then adding that to StatementSegment). And then adding a test case SQL to test/fixtures/dialects/snowflake/, running python test/generate_parse_fixture_yml.py to generate the YML equivalent, and that should be all that's required.

We're hoping to release a v0.7.0 later this week, or perhaps next, so would be good to get this in for that.

[JoeHut]

Nice. I'll try to make a PR later today.

[tunetheweb]

Great news! Feel free to reach out with any questions or if I can help at all.