<!--

Welcome to Apache Airflow!  For a smooth issue process, try to answer the following questions.
Don't worry if they're not all applicable; just try to include what you can :-)

If you need to include code snippets or logs, please put them in fenced code
blocks.  If they're super-long, please use the details tag like
<details><summary>super-long log</summary> lots of stuff </details>

Please delete these comment blocks before submitting the issue.

-->

**Description**

A sql sensor which uses Standard SQL due to default one uses legacy sql


**Use case / motivation**

Currently (correct me if I am wrong!), the sql sensor only supports legacy sql. If I want to poke a BQ table, I do not think I can do that using standard sql right now.


**Are you willing to submit a PR?**

If community approves of this idea, sure!


airflow/sensors/sql.py
conn = BaseHook.get_connection(self.conn_id)
        allowed_conn_type = {
            'google_cloud_platform',
            'jdbc',
            'mssql',
            'mysql',
            'odbc',
            'oracle',
            'postgres',
            'presto',
            'snowflake',
            'sqlite',
            'trino',
            'vertica',
        }
        if conn.conn_type not in allowed_conn_type:
            raise AirflowException(
                f"Connection type ({conn.conn_type}) is not supported by SqlSensor. "
                + f"Supported connection types: {list(allowed_conn_type)}"
            )
        return conn.get_hook()
    def poke(self, context):