Issues with SQLFluff Command Execution and Path Specification

[markus-sb1]

Environment:

• SQLFluff version: 2.2.1 (Installed in virtual env at /workspace/project/dbt/env/bin/sqlfluff)
• SQLFluff plugin version: 2.3.1

User Preferences (JSON):

{
  "gitpod.openInStable.neverPrompt": true,
  "workbench.colorTheme": "Visual Studio Dark",
  "redhat.telemetry.enabled": false,
  "diffEditor.ignoreTrimWhitespace": false,
  "sqlfluff.executablePath": "/workspace/project/dbt/env/bin/sqlfluff",
  "sqlfluff.config": "/workspace/project/.sqlfluff",
  "sqlfluff.linter.run": "onSave",
  "sqlfluff.experimental.format.executeInTerminal": true,
  "editor.formatOnSave": false
}

Issue Description:
When I attempt to format a document using the "Format Document" right-click option, I receive an error related to missing environment variables (SNOWFLAKE_ACCOUNT). The exact command executed and output received is as follows:

Executed Command:

/workspace/project/dbt/env/bin/sqlfluff fix --force --config /workspace/project/.sqlfluff dbt/models/stage/examples/example.sql

Output:

==== finding fixable violations ====
FORCE MODE: Attempting fixes...
=== [dbt templater] Sorting Nodes...
Traceback (most recent call last):
... dbt.exceptions.EnvVarMissingError: Parsing Error. Env var required but not provided: 'SNOWFLAKE_ACCOUNT' 

Strangely, when I manually change my working directory to /workspace/project/ and run the exact same command in the terminal, the file is linted and fixed successfully.

Questions:

1. What directory is the command being run from when using the "Format Document" option?
2. How can I adjust the settings so that paths are correctly recognized and the command executes successfully from the SQLFluff plugin?
3. Additionally, I would like to be able to format documents while my working directory is /workspace/project/dbt. What adjustments are needed to achieve this?

Any guidance or suggestions would be greatly appreciated.

[RobertOstermann]

1. The command is being run from the workspace folder if the working directory is not set.
2. You can use the sqlfluff.env.environmentVariables setting to add environment variables when the command is run.
3. You can use the sqlfluff.workingDirectory setting to set the working directory the commands are run from, though usually I do not recommend setting this variable. Usually people can just set the sqlfluff.config using the ${workspaceFolder} variable.

[markus-sb1]

Thanks for the quick reply, but I still can't get the plugin to work. Same error as alwas: missing env vars. I don't understand why a linter needs env vars to lint. I thought this was fixed in v2.1.1: https://stackoverflow.com/questions/76330626/missing-env-variable-specified-in-profiles-yaml-running-sqlfluff-on-pre-commit

What do you recommend I change specifically in my user preferences or sqlfluff.config?

[RobertOstermann]

Do you get that same error when running sqlfluff from the command line? If so, you should create an issue in the main sqlfluff repo. I only handle issues with the extension itself and am not an expert on sqlfluff.

[markus-sb1]

No, the generated sqlfluff command runs fine when running from the terminal. The plugin starts executing the generated command and exits immediately with the traceback I provided above.

[RobertOstermann]

Ok, I have not seen this issue before and am not sure how to solve it. If you check the output channel it should tell you the exact command being run by the extension. The extension should be running in the terminal and use those env vars as well, but you might have to manually set whatever you need in the settings.json. You could check to see what env vars you have set on the command line when it is working and copy the relevant ones over.

[markus-sb1]

    "sqlfluff.executablePath": "/workspace/project/dbt/env/bin/sqlfluff",
    "sqlfluff.config": "/workspace/project/.sqlfluff",
    "sqlfluff.linter.run": "onSave",
    "sqlfluff.experimental.format.executeInTerminal": true,
    "editor.formatOnSave": false,
    "sqlfluff.environmentVariables": [
        {
            "key": "SNOWFLAKE_ACCOUNT",
            "value": "acc_name"
        },
        {
            "key": "SNOWFLAKE_USER",
            "value": "[email protected]"
        },
        {
            "key": "SNOWFLAKE_PRIVATE_KEY_PATH",
            "value": "/path/"
        },
    ],
}

Thank you for your help. This settings.json fixed my problem, but I still find it weird that these env variables have to be specified.

[markus-sb1]

Update
I have identified the difference between the behavior of sqlfluff when run manually via the terminal and when executed through the extension. It seems to be related to the environment variable recognition linked to the profiles.yml file.

When I run sqlfluff manually using the command line, it successfully identifies the environment variables defined in my profiles.yml file. This file is specified in my .sqlfluff configuration as follows:

[sqlfluff:templater:dbt]
project_dir = ./dbt
profiles_dir = ./dbt
profile = dbt_snowflake
target = prod

However, when I attempt to achieve the same via the extension, it either fails to find the reference to these environment variables or possibly does not look for them at all.

1. Is there a known issue with the extension not handling environment variables as specified in .sqlfluff and defined in profiles.yml?
2. I want to avoid having to define the env variables in the User Settings as there are multiple developers on this project and it would be nice to use a reference instead. What steps can be taken to ensure the extension recognizes the environment variables defined in profiles.yml?

[RobertOstermann]

1. I have not heard of this issue before, but am not surprised it is an issue. I have not tested this before and do not know how the extension handles environment variables inside the sqlfluff configuration.
2. You could specify a .env file then add the "sqlfluff.env.customDotEnvFiles" setting to use that .env file.

I will probably not be updating the extension to handle environment variables in the profiles.yml, I do not work for sqlfluff and this is not a feature I would use. I will review and merge any PRs adding this functionality if you would like to contribute or a member of the sqlfluff team could add this functionality.