Repository: sqlfluff/sqlfluff

Issue:
Edit/add new JSON schema to work with github actions
relevant slack conversation: https://sqlfluff.slack.com/archives/C01V7UR2ARG/p1625599754011800

We're moving off of Jenkins onto Github Actions, and so I was starting to add our SQLFluff into github actions following the super helpful guides [here](https://github.com/sqlfluff/sqlfluff-github-actions/tree/main/menu_of_workflows)

I was able to get it run, but I noticed we were not getting any annotations on the PR itself. So to see the errors you have to go into the checks and find each line and file where the error occurs. Before, we had this set up in our jenkins job, so that when a SQLFluff error is found, it posts it on the PR on the file/line from the SQLFluff output. 

We are going to end up building something custom with our devops team for now, but it would be a great enhancement to update the JSON schema in SQLFluff and/or add an additional format output that matches the github actions annotations schema so more people could do this in the future without having to build something custom. (found [here](https://docs.github.com/en/rest/reference/checks#annotations-items) and [here](https://github.com/yuzutech/annotations-action) )

The current JSON output for SQLFluff looks like this 
```
[
  {
    "filepath": "dbt/models/admin/fact_dbt_runs.sql",
    "violations": [
      {
        "line_no": 7,
        "line_pos": 1,
        "code": "L010",
        "description": "Inconsistent capitalisation of keywords."
      }
    ]
  }
]
```

 

Discussion:

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 9f04ece0f..8bc59ef57 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -9,6 +9,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 
 ### Added
 
+- Linting output now supports GitHub Actions [#1190](https://github.com/sqlfluff/sqlfluff/issues/1190)
 - Support for QUALIFY syntax specific to teradata dialect [#1184](https://github.com/sqlfluff/sqlfluff/issues/1184)
 - Support for TRUNCATE statement [#1194](https://github.com/sqlfluff/sqlfluff/pull/1194)
 - Support for prepared statement syntaxes specific to mysql dialect [#1147](https://github.com/sqlfluff/sqlfluff/issues/1147)
diff --git a/docs/source/production.rst b/docs/source/production.rst
index 111345f99..f041e3daf 100644
--- a/docs/source/production.rst
+++ b/docs/source/production.rst
@@ -131,6 +131,17 @@ This is equivalent to running ``pip install sqlfluff[dbt]``.
 Note that you can pass the same arguments available
 through the CLI using ``args:``.
 
+Using `GitHub Actions`_ to Annotate PRs
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+When `sqlfluff lint` is run with the `--format github-annotation` option, it
+produces output compatible with this [action](https://github.com/yuzutech/annotations-action).
+You can use this to annotate the SQL in `GitHub pull requests`.
+
+For more information and examples on using SQLFluff in GitHub Actions, see the
+[`sqlfluff-github-actions` repository](https://github.com/sqlfluff/sqlfluff-github-actions).
+
 .. _`pre-commit`: https://pre-commit.com/
 .. _`git hook`: https://git-scm.com/book/en/v2/Customizing-Git-Git-Hooks
 .. _`dbt templater`: `dbt-project-configuration`
+.. _`GitHub Actions`: https://github.com/features/actions
+.. _`GitHub pull requests`: https://docs.github.com/en/github/collaborating-with-pull-requests/proposing-changes-to-your-work-with-pull-requests/about-pull-requests
diff --git a/src/sqlfluff/cli/commands.py b/src/sqlfluff/cli/commands.py
index d1920beb8..153011e8b 100644
--- a/src/sqlfluff/cli/commands.py
+++ b/src/sqlfluff/cli/commands.py
@@ -272,9 +272,17 @@ def dialects(**kwargs):
     "--format",
     "format",
     default="human",
-    type=click.Choice(["human", "json", "yaml"], case_sensitive=False),
+    type=click.Choice(
+        ["human", "json", "yaml", "github-annotation"], case_sensitive=False
+    ),
     help="What format to return the lint result in (default=human).",
 )
+@click.option(
+    "--annotation-level",
+    default="notice",
+    type=click.Choice(["notice", "warning", "failure"], case_sensitive=False),
+    help="When format is set to github-annotation, default annotation level (default=notice).",
+)
 @click.option(
     "--nofail",
     is_flag=True,
@@ -300,6 +308,7 @@ def lint(
     paths,
     processes,
     format,
+    annotation_level,
     nofail,
     disregard_sqlfluffignores,
     logger=None,
@@ -325,7 +334,7 @@ def lint(
 
     """
     c = get_config(**kwargs)
-    non_human_output = format in ("json", "yaml")
+    non_human_output = format != "human"
     lnt, formatter = get_linter_and_formatter(c, silent=non_human_output)
     verbose = c.get("verbose")
 
@@ -365,6 +374,27 @@ def lint(
         click.echo(json.dumps(result.as_records()))
     elif format == "yaml":
         click.echo(yaml.dump(result.as_records()))
+    elif format == "github-annotation":
+        github_result = []
+        for record in result.as_records():
+            filepath = record["filepath"]
+            for violation in record["violations"]:
+                # NOTE: The output format is designed for this GitHub action:
+                # https://github.com/yuzutech/annotations-action
+                # It is similar, but not identical, to the native GitHub format:
+                # https://docs.github.com/en/rest/reference/checks#annotations-items
+                github_result.append(
+                    {
+                        "file": filepath,
+                        "line": violation["line_no"],
+                        "start_column": violation["line_pos"],
+                        "end_column": violation["line_pos"],
+                        "title": "SQLFluff",
+                        "message": f"{violation['code']}: {violation['description']}",
+                        "annotation_level": annotation_level,
+                    }
+                )
+        click.echo(json.dumps(github_result))
 
     if bench:
         click.echo("==== overall timings ====")


Test patch:
diff --git a/test/cli/commands_test.py b/test/cli/commands_test.py
index ac6f1a002..4ed1bfb4b 100644
--- a/test/cli/commands_test.py
+++ b/test/cli/commands_test.py
@@ -497,7 +497,7 @@ def test__cli__command_fail_nice_not_found(command):
     assert "could not be accessed" in result.output
 
 
-@pytest.mark.parametrize("serialize", ["yaml", "json"])
+@pytest.mark.parametrize("serialize", ["yaml", "json", "github-annotation"])
 def test__cli__command_lint_serialize_multiple_files(serialize):
     """Check the general format of JSON output for multiple files."""
     fpath = "test/fixtures/linter/indentation_errors.sql"
@@ -510,11 +510,97 @@ def test__cli__command_lint_serialize_multiple_files(serialize):
 
     if serialize == "json":
         result = json.loads(result.output)
+        assert len(result) == 2
     elif serialize == "yaml":
         result = yaml.safe_load(result.output)
+        assert len(result) == 2
+    elif serialize == "github-annotation":
+        result = json.loads(result.output)
+        filepaths = {r["file"] for r in result}
+        assert len(filepaths) == 1
     else:
         raise Exception
-    assert len(result) == 2
+
+
+def test__cli__command_lint_serialize_github_annotation():
+    """Test format of github-annotation output."""
+    fpath = "test/fixtures/linter/identifier_capitalisation.sql"
+    result = invoke_assert_code(
+        args=[
+            lint,
+            (fpath, "--format", "github-annotation", "--annotation-level", "warning"),
+        ],
+        ret_code=65,
+    )
+    result = json.loads(result.output)
+    assert result == [
+        {
+            "annotation_level": "warning",
+            "file": "test/fixtures/linter/identifier_capitalisation.sql",
+            "line": 1,
+            "message": "L036: Select targets should be on a new line unless there is "
+            "only one select target.",
+            "start_column": 1,
+            "end_column": 1,
+            "title": "SQLFluff",
+        },
+        {
+            "annotation_level": "warning",
+            "file": "test/fixtures/linter/identifier_capitalisation.sql",
+            "line": 2,
+            "message": "L027: Unqualified reference 'foo' found in select with more than "
+            "one referenced table/view.",
+            "start_column": 5,
+            "end_column": 5,
+            "title": "SQLFluff",
+        },
+        {
+            "annotation_level": "warning",
+            "file": "test/fixtures/linter/identifier_capitalisation.sql",
+            "line": 3,
+            "message": "L012: Implicit aliasing of column not allowed. Use explicit `AS` "
+            "clause.",
+            "start_column": 5,
+            "end_column": 5,
+            "title": "SQLFluff",
+        },
+        {
+            "annotation_level": "warning",
+            "file": "test/fixtures/linter/identifier_capitalisation.sql",
+            "line": 3,
+            "message": "L014: Inconsistent capitalisation of unquoted identifiers.",
+            "start_column": 5,
+            "end_column": 5,
+            "title": "SQLFluff",
+        },
+        {
+            "annotation_level": "warning",
+            "file": "test/fixtures/linter/identifier_capitalisation.sql",
+            "line": 4,
+            "message": "L010: Inconsistent capitalisation of keywords.",
+            "start_column": 1,
+            "end_column": 1,
+            "title": "SQLFluff",
+        },
+        {
+            "annotation_level": "warning",
+            "file": "test/fixtures/linter/identifier_capitalisation.sql",
+            "line": 4,
+            "message": "L014: Inconsistent capitalisation of unquoted identifiers.",
+            "start_column": 12,
+            "end_column": 12,
+            "title": "SQLFluff",
+        },
+        {
+            "annotation_level": "warning",
+            "file": "test/fixtures/linter/identifier_capitalisation.sql",
+            "line": 4,
+            "message": "L014: Inconsistent capitalisation of unquoted identifiers.",
+            "start_column": 18,
+            "end_column": 18,
+            "title": "SQLFluff",
+        },
+    ]
 
 
 def test___main___help():