[duckdb] `CREATE SECRET`

[kfollesdal]

While using sqlmesh with duckdb, and are using the secret manager in duckdb to authenticate connection to azure storage account.

I discovered that sqlglot do not support CREATE SECRET ..., I get a WARING, but sqlmesh run fine. So this is not critical, so just nice to fix.

With sqlglot from main commit 647b98d

sqlglot.parse_one("""
    CREATE SECRET azure_secret (
        TYPE AZURE,
        PROVIDER CREDENTIAL_CHAIN,
        CHAIN 'cli',
        ACCOUNT_NAME 'mystorage'
    );
""",
read="duckdb")

gives

'CREATE SECRET azure_secret (
        TYPE AZURE,
        PROVIDER CREDENTIAL_CHAIN,
        CHAIN 'c' contains unsupported syntax. Falling back to parsing as a 'Command'.
Command(this=CREATE, expression=SECRET azure_secret (
           TYPE AZURE,
           PROVIDER CREDENTIAL_CHAIN,
           CHAIN 'cli',
           ACCOUNT_NAME 'mystorage'
       ))

Is this something that is easily fixed? Maybe point me to the right place in code with description on what must be done and I can try to make a PR.

[georgesittas]

You'd need to:

• Create a new SECRET token type, map "SECRET" to it and add that token to DB_CREATABLES in the base parser.
• Override or enhance _parse_create so that you can consume the (TYPE value [, KEY VALUE ...]) syntax. After parsing it, one idea is to represent this CSV of props using Properties, so each key-value pair would be represented as a Property, and then override create_sql for Snowflake so that if Create.kind is SECRET, somehow omit the = that would be generated for the Property nodes.

[georgesittas]

This is relatively low priority for the core team, but we'll be happy to accept a well-crafted PR for this. Feel free to reach out in Slack if you need further guidance.