Improve SQLAlchemy dialect support for S3 Tables and Iceberg CTAS

[laughingman7743]

Summary

Athena added CTAS support for S3 Tables in 2025-08 and continues to expand Iceberg integration. PyAthena's SQLAlchemy dialect (pyathena/sqlalchemy/compiler.py) currently has basic Iceberg DDL handling triggered by setting `table_type = 'iceberg'` in dialect options (see compiler.py:668-682), but it does not directly model S3 Tables.

S3 Tables are Iceberg-backed and live in a dedicated bucket type with their own catalog. The CTAS form looks roughly like:

```sql
CREATE TABLE "s3tablescatalog/bucket"."ns"."tbl"
WITH (table_type = 'ICEBERG', ...)
AS SELECT ...
```

Proposed change

Investigation issue. Tasks to scope:

1. Confirm what the dialect currently emits for an Iceberg table targeted at an S3 Tables catalog name (does the three-part identifier catalog.namespace.table round-trip correctly?).
2. Add tests under tests/pyathena/sqlalchemy/ exercising:
   • CREATE TABLE on a s3tablescatalog/<bucket> catalog
   • CTAS variant
   • Partition transforms (day, bucket, truncate) already covered by AthenaPartitionTransform
3. Document supported dialect options for S3 Tables in pyathena/sqlalchemy/compiler.py docstrings.

References

• Using Athena to query the data in your tables — S3 Tables
• Querying Iceberg tables — Athena User Guide
• Athena Release Notes (2025-08)