SQLAlchemy source for Lumen AI

[amaloney]

Include BigQuery as a source for Lumen AI.

[ahuang11]

Ideally, we would simply have a SQLAlchemySource so we don't have to re-invent the wheel of wrapping all the SQL dialects.

[philippjfr]

Can you expand on how you see SQLAlchemySource working in a way that avoids implementing different dialetcts? I can see having a source that you give a sqlalchemy Session however there's two major problems with that:

• It wouldn't be serializable
• Arbitrary SQL execution will require .execute(text(sql_expr)) which afaik does not have a translation layer for different dialects

[ahuang11]

Arbitrary SQL execution will require .execute(text(sql_expr))

Yes, I was thinking this route.

which afaik does not have a translation layer for different dialects

I don't think this matters since the LLM is the translation layer, which is why we include this in the prompt: - Use only {{ dialect }} SQL syntax. If translation/transpiling layer is important, we can use sqlglot https://github.com/tobymao/sqlglot/tree/main?tab=readme-ov-file#examples

The reason I suggested SQLAlchemy is because you can use URLs for credentials:

url = (
    "snowflake://<user_login_name>:<password>"
    "@<account_identifier>/<database_name>"
    "?warehouse=<warehouse_name>"
)

Or a pydantic/parameterized model for common keywords, like here:

ConnectionComponents(
    driver=AsyncDriver.POSTGRESQL_ASYNCPG,
    username="prefect",
    password="prefect_password",
    database="postgres"
)

https://github.com/PrefectHQ/prefect/blob/main/src/integrations/prefect-sqlalchemy/prefect_sqlalchemy/credentials.py#L88