Repository: sqlfluff/sqlfluff
Pull request: Spark3: Add Direct File Query
<!--Thanks for adding this feature!-->

<!--Please give the Pull Request a meaningful title for the release notes-->

### Brief summary of the change made
<!--Please include `fixes #XXXX` to automatically close any corresponding issue when the pull request is merged. Alternatively if not fully closed you can say `makes progress on #XXXX`.-->
- Adds support to directly query files of supported types.
- Fixes #602 

### Are there any other side effects of this change that we should be aware of?
- Reorganized some grammar for Files to isolate File keywords that support this clause.
- Removed "XML" File format. No test cases and requires additional work before it will parse correctly.


- Linting fails due to special characters in identifiers (L057). These special keywords are required so would be an exception to L057. I've not yet worked on rules so would appreciate any guidance on where to get started on fixing this particular bug.

```bash
> sqlfluff lint --dialect spark3 test/fixtures/dialects/spark3/select_from_file.sql
== [test/fixtures/dialects/spark3/select_from_file.sql] FAIL
L:   6 | P:  14 | L057 | Do not use special characters in identifiers.
L:  13 | P:  14 | L057 | Do not use special characters in identifiers.
L:  20 | P:  10 | L057 | Do not use special characters in identifiers.
L:  27 | P:  11 | L057 | Do not use special characters in identifiers.
L:  34 | P:  11 | L057 | Do not use special characters in identifiers.
L:  41 | P:  11 | L057 | Do not use special characters in identifiers.
L:  48 | P:  11 | L057 | Do not use special characters in identifiers.
L:  55 | P:  17 | L057 | Do not use special characters in identifiers.
L:  62 | P:  17 | L057 | Do not use special characters in identifiers.
L:  69 | P:  10 | L057 | Do not use special characters in identifiers.
L:  76 | P:  12 | L057 | Do not use special characters in identifiers.
All Finished 📜 🎉!
```

```bash
[L: 76, P:  6]      |                from_expression:
[L: 76, P:  6]      |                    [META] indent:
[L: 76, P:  6]      |                    from_expression_element:
[L: 76, P:  6]      |                        table_expression:
[L: 76, P:  6]      |                            file_reference:
[L: 76, P:  6]      |                                keyword:                      'DELTA'
[L: 76, P: 11]      |                                dot:                          '.'
[L: 76, P: 12]      |                                identifier:                   '`/mnt/datalake/table`'
[L: 76, P: 33]      |                    [META] dedent:
[L: 76, P: 33]      |    statement_terminator:                                     ';'
[L: 76, P: 34]      |    newline:                                                  '\n'
```

### Pull Request checklist
- [X] Please confirm you have completed any of the necessary steps below.

- Included test cases to demonstrate any code changes, which may be one or more of the following:
  - `.yml` rule test cases in `test/fixtures/rules/std_rule_cases`.
  - `.sql`/`.yml` parser test cases in `test/fixtures/dialects` (note YML files can be auto generated with `tox -e generate-fixture-yml`).
  - Full autofix test cases in `test/fixtures/linter/autofix`.
  - Other.
- Added appropriate documentation for the change.
- Created GitHub issues for any relevant followup/future enhancements if appropriate.


Problem statement:
Issue with Databricks absolute path tables
We're using DBT with Databricks and tried to implement sqlfluff for linting. However, we're seeing that the following syntax is unsupported:

```sql
SELECT *
FROM delta.`/mnt/datalake/table`
```

Is this something that we want to support in sqlfluff? If so, I can try to come up with a PR.

Hints from issue discussion:
hi @Fokko, glad to see you here!

sqlfluff has a modular approach to dialects, you should be able to see all the dialects here: https://github.com/sqlfluff/sqlfluff/tree/master/src/sqlfluff/core/dialects

I'm thinking you would need to implement a Spark SQL dialect (which would be awesome to have)

Related #417
This is not specific to Databricks but the Delta Lake OSS project & Spark DataSourceV2 classes.

The Delta dialect should probably be an extension of Spark SQL and a valid file format in DBT and HQL.

https://docs.delta.io/latest/delta-batch.html#read-a-table

https://github.com/delta-io/connectors/#create-a-hive-table
https://docs.getdbt.com/reference/resource-configs/spark-configs#configuring-tables
@tunetheweb - Can you change the label from `new dialect` to `spark3`?

Spark docs have been updated recently. This should be supported in Spark dialect then Databricks (which is SaaS Spark) could inherit it.

https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-file.html
> @tunetheweb - Can you change the label from `new dialect` to `spark3`?

Done!

Commit message: switch QuotedIdentifierSegment to QuotedLiteralSegment inside FileReferenceSegment

ran sqlfluff fix on tests and updated yml again

Patch to review:
diff --git a/src/sqlfluff/dialects/dialect_spark3.py b/src/sqlfluff/dialects/dialect_spark3.py
index 87287cdc986..2f9a8cf8551 100644
--- a/src/sqlfluff/dialects/dialect_spark3.py
+++ b/src/sqlfluff/dialects/dialect_spark3.py
@@ -207,7 +207,12 @@
 )
 
 spark3_dialect.add(
-    # Add Hive Segments TODO : Is there a way to retrieve this w/o redefining?
+    BinaryfileKeywordSegment=StringParser(
+        "BINARYFILE",
+        KeywordSegment,
+        name="binary_file",
+        type="file_format",
+    ),
     JsonfileKeywordSegment=StringParser(
         "JSONFILE",
         KeywordSegment,
@@ -246,7 +251,6 @@
         "BracketedPropertyListGrammar"
     ),
     CommentGrammar=hive_dialect.get_grammar("CommentGrammar"),
-    FileFormatGrammar=hive_dialect.get_grammar("FileFormatGrammar"),
     LocationGrammar=hive_dialect.get_grammar("LocationGrammar"),
     PropertyGrammar=hive_dialect.get_grammar("PropertyGrammar"),
     SerdePropertiesGrammar=hive_dialect.get_grammar("SerdePropertiesGrammar"),
@@ -270,7 +274,11 @@
     DatabasePropertiesGrammar=Sequence(
         "DBPROPERTIES", Ref("BracketedPropertyListGrammar")
     ),
-    DataSourceFormatGrammar=OneOf(
+    DataSourcesV2FileTypeGrammar=OneOf(
+        # https://github.com/apache/spark/tree/master/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2 # noqa: E501
+        # Separated here because these allow for additional
+        # commands such as Select From File
+        # https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-file.html
         # Spark Core Data Sources
         # https://spark.apache.org/docs/latest/sql-data-sources.html
         "AVRO",
@@ -278,10 +286,31 @@
         "JSON",
         "PARQUET",
         "ORC",
-        "JDBC",
-        # Community Contributed Data Sources
+        # Separated here because these allow for additional commands
+        # Similar to DataSourcesV2
         "DELTA",  # https://github.com/delta-io/delta
-        "XML",  # https://github.com/databricks/spark-xml
+        "CSV",
+        "TEXT",
+        "BINARYFILE",
+    ),
+    FileFormatGrammar=OneOf(
+        Ref("DataSourcesV2FileTypeGrammar"),
+        "SEQUENCEFILE",
+        "TEXTFILE",
+        "RCFILE",
+        "JSONFILE",
+        Sequence(
+            "INPUTFORMAT",
+            Ref("QuotedLiteralSegment"),
+            "OUTPUTFORMAT",
+            Ref("QuotedLiteralSegment"),
+        ),
+    ),
+    DataSourceFormatGrammar=OneOf(
+        Ref("FileFormatGrammar"),
+        # NB: JDBC is part of DataSourceV2 but not included
+        # there since there are no no significant syntax changes
+        "JDBC",
     ),
     # Adding Hint related segments so they are not treated as generic comments
     # https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-hints.html
@@ -1601,3 +1630,38 @@ class ValuesClauseSegment(BaseSegment):
         Ref("DelimitedValues"),
         Ref("AliasExpressionSegment", optional=True),
     )
+
+
+@spark3_dialect.segment(replace=True)
+class TableExpressionSegment(BaseSegment):
+    """The main table expression e.g. within a FROM clause.
+
+    Enhance to allow for additional clauses allowed in Spark.
+    """
+
+    type = "table_expression"
+    match_grammar = OneOf(
+        Ref("ValuesClauseSegment"),
+        Ref("BareFunctionSegment"),
+        Ref("FunctionSegment"),
+        Ref("FileReferenceSegment"),
+        Ref("TableReferenceSegment"),
+        # Nested Selects
+        Bracketed(Ref("SelectableGrammar")),
+    )
+
+
+@spark3_dialect.segment()
+class FileReferenceSegment(BaseSegment):
+    """A reference to a file for direct query.
+
+    https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-file.html
+    """
+
+    type = "file_reference"
+
+    match_grammar = Sequence(
+        Ref("DataSourcesV2FileTypeGrammar"),
+        Ref("DotSegment"),
+        Ref("QuotedLiteralSegment"),
+    )
diff --git a/test/fixtures/dialects/spark3/select_from_file.sql b/test/fixtures/dialects/spark3/select_from_file.sql
new file mode 100644
index 00000000000..7c9b57153a1
--- /dev/null
+++ b/test/fixtures/dialects/spark3/select_from_file.sql
@@ -0,0 +1,76 @@
+-- PARQUET file
+SELECT
+    a,
+    b,
+    c
+FROM parquet.`examples/src/main/resources/users.parquet`;
+
+-- Directory of Parquet Files
+SELECT
+    a,
+    b,
+    c
+FROM parquet.`examples/src/main/resources/users`;
+
+-- ORC file
+SELECT
+    a,
+    b,
+    c
+FROM orc.`examples/src/main/resources/users.orc`;
+
+-- JSON file
+SELECT
+    a,
+    b,
+    c
+FROM json.`examples/src/main/resources/people.json`;
+
+-- Directory of JSON files
+SELECT
+    a,
+    b,
+    c
+FROM json.`examples/src/main/resources/people`;
+
+-- Text File
+SELECT
+    a,
+    b,
+    c
+FROM text.`examples/src/main/resources/people.txt`;
+
+-- JSON treated as Text File
+SELECT
+    a,
+    b,
+    c
+FROM text.`examples/src/main/resources/people.json`;
+
+-- BinaryFile
+SELECT
+    a,
+    b,
+    c
+FROM binaryfile.`/events/events-kafka.json`;
+
+-- Directory of BinaryFiles
+SELECT
+    a,
+    b,
+    c
+FROM binaryfile.`/events/events-kafka`;
+
+-- CSV File
+SELECT
+    a,
+    b,
+    c
+FROM csv.`/sales/sales.csv`;
+
+-- Delta File; test for Issue #602
+SELECT
+    a,
+    b,
+    c
+FROM delta.`/mnt/datalake/table`;
diff --git a/test/fixtures/dialects/spark3/select_from_file.yml b/test/fixtures/dialects/spark3/select_from_file.yml
new file mode 100644
index 00000000000..9b19fcdc04b
--- /dev/null
+++ b/test/fixtures/dialects/spark3/select_from_file.yml
@@ -0,0 +1,282 @@
+# YML test files are auto-generated from SQL files and should not be edited by
+# hand. To help enforce this, the "hash" field in the file must match a hash
+# computed by SQLFluff when running the tests. Please run
+# `python test/generate_parse_fixture_yml.py`  to generate them after adding or
+# altering SQL files.
+_hash: 72432be0024d6d35c89fc8f63024ab78271ac1187b19925a0fae2de3c90eae26
+file:
+- base:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            identifier: a
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: b
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: c
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+              - identifier: parquet
+              - dot: .
+              - identifier: '`examples/src/main/resources/users.parquet`'
+- statement_terminator: ;
+- base:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            identifier: a
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: b
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: c
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+              - identifier: parquet
+              - dot: .
+              - identifier: '`examples/src/main/resources/users`'
+- statement_terminator: ;
+- base:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            identifier: a
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: b
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: c
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+              - identifier: orc
+              - dot: .
+              - identifier: '`examples/src/main/resources/users.orc`'
+- statement_terminator: ;
+- base:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            identifier: a
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: b
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: c
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+              - identifier: json
+              - dot: .
+              - identifier: '`examples/src/main/resources/people.json`'
+- statement_terminator: ;
+- base:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            identifier: a
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: b
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: c
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+              - identifier: json
+              - dot: .
+              - identifier: '`examples/src/main/resources/people`'
+- statement_terminator: ;
+- base:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            identifier: a
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: b
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: c
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+              - identifier: text
+              - dot: .
+              - identifier: '`examples/src/main/resources/people.txt`'
+- statement_terminator: ;
+- base:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            identifier: a
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: b
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: c
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+              - identifier: text
+              - dot: .
+              - identifier: '`examples/src/main/resources/people.json`'
+- statement_terminator: ;
+- base:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            identifier: a
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: b
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: c
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+              - identifier: binaryfile
+              - dot: .
+              - identifier: '`/events/events-kafka.json`'
+- statement_terminator: ;
+- base:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            identifier: a
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: b
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: c
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+              - identifier: binaryfile
+              - dot: .
+              - identifier: '`/events/events-kafka`'
+- statement_terminator: ;
+- base:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            identifier: a
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: b
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: c
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+              - identifier: csv
+              - dot: .
+              - identifier: '`/sales/sales.csv`'
+- statement_terminator: ;
+- base:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            identifier: a
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: b
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: c
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+              - identifier: delta
+              - dot: .
+              - identifier: '`/mnt/datalake/table`'
+- statement_terminator: ;


Code review comments:
diff_hunk: @@ -270,18 +274,43 @@
     DatabasePropertiesGrammar=Sequence(
         "DBPROPERTIES", Ref("BracketedPropertyListGrammar")
     ),
-    DataSourceFormatGrammar=OneOf(
+    DataSourcesV2FileTypeGrammar=OneOf(
+        # https://github.com/apache/spark/tree/master/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/v2 # noqa: E501
+        # Separated here because these allow for additional
+        # commands such as Select From File
+        # https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-file.html
         # Spark Core Data Sources
         # https://spark.apache.org/docs/latest/sql-data-sources.html
         "AVRO",
         "CSV",
         "JSON",
         "PARQUET",
         "ORC",
-        "JDBC",
-        # Community Contributed Data Sources
+        # Separated here because these allow for additional commands
+        # Similar to DataSourcesV2
         "DELTA",  # https://github.com/delta-io/delta
-        "XML",  # https://github.com/databricks/spark-xml
+        "CSV",
+        "TEXT",
+        "BINARYFILE",
+    ),
+    FileFormatGrammar=OneOf(
+        Ref("DataSourcesV2FileTypeGrammar"),
+        "SEQUENCEFILE",
+        "TEXTFILE",
+        "RCFILE",
+        "JSONFILE",
+        Sequence(
+            "INPUTFORMAT",
+            Ref("QuotedLiteralSegment"),
+            "OUTPUTFORMAT",
+            Ref("QuotedLiteralSegment"),
+        ),
+    ),
+    DataSourceFormatGrammar=OneOf(
+        Ref("FileFormatGrammar"),
+        # NB: JDBC is part of DataSourceV2 but not included
+        # there since there are no no significant syntax changes
path: src/sqlfluff/dialects/dialect_spark3.py
text: @user1:
```suggestion
        # there since there are no significant syntax changes
```