break: Require SQLAlchemy 2.0 for SQL taps and targets

[edgarrmondragon]

Feature scope

Taps (catalog, state, stream maps, etc.)

Description

We should require new taps and targets use SQLAlchemy 2.0 to avoid having to migrate them later, and come up with a migration plan for existing ones.

Most of the compatibility work was done in #1357, but individual connectors may still be using deprecated APIs.

Ecosystem Support

• Athena: PyAthena supports SQLAlchemy 2.0 since v3.0.0.
• BigQuery: The SQLAlchemy Dialect for BigQuery supports SQLAlchemy 2.0 since v1.11.0.
• Clickhouse: ClickHouse SQLAlchemy supports SQLAlchemy 2.0 since 0.3.0.
• CockroachDB: The CockroachDB dialect for SQLAlchemy supports SQLAlchemy 2.0 since v2.0.0.
• duckdb: duckdb-engine supports SQLAlchemy 2.0 since v0.7.0-rc1.
• Redshift: sqlalchemy-redshift doesn't support it yet (support sqlalchemy 2.0 sqlalchemy-redshift/sqlalchemy-redshift#264).
• Snowflake: Snowflake SQLAlchemy supports SQLAlchemy 2.0 since v1.6.1.
• Starrocks: SQAlchemy 2.0+ is supported since 1.1.0 (StarRocks/starrocks@3b3a67f)

Related:

• SQLAlchemy 2.0 Deprecation warnings MeltanoLabs/target-postgres#87
• SNOW-739749: snowflake-sqlalchemy 1.4.4 requires sqlalchemy<2.0.0,>=1.4.0, but you have sqlalchemy 2.0.1 which is incompatible. snowflakedb/snowflake-sqlalchemy#380
• SNOW-1516075: set release date to July 8th snowflakedb/snowflake-sqlalchemy#514

[edgarrmondragon]

#1484 adds support for SQLAlchemy 2.* without introducing breaking changes

[edgarrmondragon]

Closed by #1916

[edgarrmondragon]

This isn't in main yet, so I'll reopen it. It's tracked by https://github.com/meltano/sdk/milestone/2 anyway.

[edgarrmondragon]

snowflakedb/snowflake-sqlalchemy#514 suggests that snowflake-sqlalchemy 1.6 will support SQLAlchemy 2.0.

[edgarrmondragon]

I added an Ecosystem Support checklist to the issue description. I think those are the major third-party SQLAlchemy dialects. Comment if you know one that's missing!

The only stale one is Redshift, which is used by TicketSwap/target-redshift. I don't wanna get them stuck in an older version of the Singer SDK just yet, so I'll punt this to sometime in 2024 Q1.