Better parsing of SHOW PRIMARY KEYS in snowflake

[rooterkyberian]

Seems that support for SHOW commands is very basic - i.e. command is interpreted as text.
I need to support https://docs.snowflake.com/en/sql-reference/sql/show-primary-keys and to be more exact: to be able to exact Table Identifier from statements like this:

show primary keys /* ODBC:ForeignKeysMetadataSource */ in table "PROD"."SOMEAPP"."PRODUCTS"

For now I'm using this ugly patch that I based on similar functionality of mysql dialect:

import sqlglot
from sqlglot import TokenType
from sqlglot.dialects import Snowflake

def _show_parser(*args, **kwargs):
    def _parse(self):
        return self._parse_show_snowflake(*args, **kwargs)
    return _parse


class _SnowflakeDialect(Snowflake):
    """Snowflake dialect with basic support for `show primary keys in table ...` command."""

    class Tokenizer(Snowflake.Tokenizer):
        COMMANDS = Snowflake.Tokenizer.COMMANDS - {
            TokenType.SHOW,
        }

        KEYWORDS = {
            "PRIMARY KEYS": TokenType.PSEUDO_TYPE,  # fact I needed to add this was the biggest challenge to implement this - otherwise somehow it identified PRIMARY KEYS string as PRIMARY_KEY token and `s` text token (?!)
            **Snowflake.Tokenizer.KEYWORDS
        }

    class Parser(Snowflake.Parser):

        STATEMENT_PARSERS = {
            **Snowflake.Parser.STATEMENT_PARSERS,  # type: ignore
            TokenType.SHOW: lambda self: self._parse_show(),
        }
        SHOW_PARSERS = {
            **Snowflake.Parser.SHOW_PARSERS,  # type: ignore
            "PRIMARY KEYS": _show_parser("PRIMARY KEYS", target="IN TABLE"),
        }

        def _parse_show_snowflake(self, this, target=False, full=None, global_=None):
            if target:
                if isinstance(target, str):
                    self._match_text_seq(*target.split(" "))
                target_id = self._parse_table()
            else:
                target_id = None

            return self.expression(
                sqlglot.expressions.Show,
                this=this,
                target=target_id,
                full=full,
                log=log,
                **{"global": global_},
            )

Of course this is a hack, and probably breaks parsing of other SHOW commands.

P.S. ❤️ this library, I would hate having writing parsing of SQL-like things on my own

[barakalon]

Custom SHOW_PARSERS for Snowflake are the right approach.
PRs welcome :)

[tobymao]

closing this as not planned. but happy to accept a well tested + thought out PR. we can continue to the discussion of implementation here or in slack if you're interested