Support for running T-SQL query

[meavk]

RTI kusto supports T-SQL along with KQL. However, the tool kusto_query currently supports only KQL.

Azure Data Explorer identifies that it's a T-SQL query when there is an empty comment -- in the beginning. This syntax can be used to identify that It's T-SQL query. Following syntax can be used to set the query_language to sql.

        # Set language to SQL if query starts with "--"
        # https://learn.microsoft.com/en-us/azure/data-explorer/t-sql#query-with-t-sql
        lines = query.lstrip().splitlines()
        if lines and lines[0].strip() == "--":
            # https://learn.microsoft.com/en-us/kusto/api/rest/t-sql?view=microsoft-fabric#request-structure
            crp.set_option("query_language", "sql")

Also, some modification will be needed in the doc string.

def kusto_query(query: str, cluster_uri: str, database: Optional[str] = None) -> List[Dict[str, Any]]:
    """
    Executes a KQL or T-SQL query on the specified database. 
    The type of query is auto-detected based on the first line of the query. In a T-SQL query, the first line must be '--'.
    If no database is provided, it will use the default database.

    :param query: The KQL/T-SQL query to execute. 
    :param cluster_uri: The URI of the Kusto cluster.
    :param database: Optional database name. If not provided, uses the default database.
    :return: The result of the query execution as a list of dictionaries (json).
    """
    return _execute(query, cluster_uri, database=database)

Note: I have tested this locally, can raise a PR.

[danield137]

There is an existing PR that allows passing in client request properties (#50).
It needed some testing before merging, and I'm working on a testing harness to make it safe to introduce changes.

Regardless, I don't think it makes sense for us to change the description of the method.
If anything, we might change it to kql_query instead to tighten the functionality.

Whether we add a new tool for SQL directly is an open question. I don't think it's on anyone's priority list, especially since we are trying to keep the tool count to a minimum at this point, until we feel like it is clear why adding more tools will help.

Having said the above, agents should be very capable at translating SQL to KQL. Perhaps you can handle it upstream?

[meavk]

@danield137 I think that (#50 ) should be sufficient to indicate that the query is T-SQL.

Few more details on our scenario. The MCP server is used along with GitHub Copilot by the developers to convert Postgres SQL to T-SQL and optimize it. GitHub Copilot invokes the kusto_query tool to validate the changes.

[danield137]

Not quite sure why not to translate to KQL if you're already doing that?

[meavk]

1. We're making use of the explain command to convert T-SQL to KQL. That requires query to be running in RTI.
2. The dev team is not familiar with the original data model and relationships. They're relying on another team from customer side for reviewing the scripts for correctness. They're not familiar with KQL. For them, it's easier if the scripts are in SQL.

As a company, customer is used to SQL. T-SQL support in RTI is something that is helping this customer in transitioning to Fabric.

[meavk]

I have a PR here - #70
This incorporates above suggestion to have a separate tool for T-SQL query.

[danield137]

merged with #50