ARRAY column not supported on HybridTable despite Snowflake documentation claiming otherwise

[benvdh-incentro]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using?

   Python 3.12.8 (main, Dec 4 2024, 08:54:12) [GCC 11.4.0]

2. What operating system and processor architecture are you using?

   Linux-6.8.0-51-generic-x86_64-with-glibc2.35

3. What are the component versions in the environment (pip freeze)?

   alembic==1.14.0
   asn1crypto==1.5.1
   certifi==2024.12.14
   cffi==1.17.1
   charset-normalizer==3.4.1
   cryptography==44.0.0
   dependency-injector==4.45.0
   et_xmlfile==2.0.0
   filelock==3.16.1
   greenlet==3.1.1
   idna==3.10
   iniconfig==2.0.0
   Mako==1.3.8
   MarkupSafe==3.0.2
   numpy==2.2.1
   openpyxl==3.1.5
   packaging==24.2
   pandas==2.2.3
   pandas-stubs==2.2.3.241126
   platformdirs==4.3.6
   pluggy==1.5.0
   pycparser==2.22
   PyJWT==2.10.1
   pyOpenSSL==24.3.0
   pytest==8.3.4
   python-dateutil==2.9.0.post0
   python-dotenv==1.0.1
   pytz==2024.2
   requests==2.32.3
   ruff==0.8.6
   six==1.17.0
   snowflake-connector-python==3.12.4
   snowflake-sqlalchemy==1.7.2
   sortedcontainers==2.4.0
   SQLAlchemy==2.0.37
   tomlkit==0.13.2
   types-pytz==2024.2.0.20241221
   typing_extensions==4.12.2
   tzdata==2024.2
   urllib3==2.3.0

4. What did you do?

   I tried to add a structured data type column to a hybrid table (in my case an array column), but snowflake-sqlalchemy throws an error stating this is only supported on Iceberg Tables. However, the Snowflake documentation states the following (see subsection: Data types not supported in indexes):

   Hybrid table columns support these data types as long as the columns are not indexed.

   from typing import List
   
   from snowflake.sqlalchemy import HybridTable
   from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped
   from snowflake.sqlalchemy.custom_types import ARRAY
   
   class MyBase(DeclarativeBase):
       pass
   
   class myTable(MyBase):
       __table_name__ = "my_table"
       
       @classmethod
       def __table_cls__(cls, name, metadata, *args, **kw):
           return HybridTable(name, metadata, *args, **kw)
   
       my_array_field: Mapped[List[str]] = mapped_column(
           ARRAY,
           nullable=False
       )
   
   MyBase.metadata.create_all(engine)

5. What did you expect to see?

   Since I ran the above code (except the last line) with alembic, I expected alembic to generate a migration script that would add the array column
   to the table named my_table. In turn I expect the migration script runs successfully.

   What should have happened and what happened instead?
   While atempting to generate the alembic migration, the following error is thrown instead:

   snowflake.sqlalchemy.exc.StructuredTypeNotSupportedInTableColumnsError: Column 'aggregation_fields' is of a structured type, which is only supported on Iceberg tables. The table 'aggregation_method' is of type 'HybridTable', not Iceberg.

   I suspect this happens because HybridTable inherits from CustomTableBase which by default sets _support_structured_types = False, and HybridTable does not override this class variable, whereas IcebergTable does perform the following override _support_structured_types = True.

   I Just tested the above hypothesis, and can confirm everything works flawlessly when _support_structured_types = True is added to the HybridTable class. Both the migration gets generated successfully, and the migration runs successfully...

6. Can you set logging to DEBUG and collect the logs?

[sfc-gh-dszmolka]

thanks for raising this - looks like its fixed with #572; reopening to be able to properly track the release

[sfc-gh-dszmolka]

released with snowflake-sqlalchemy 1.7.3