SNOW-638838: Cluster by in ORM classes and queries

[nareto]

This is neither a bug nor a feature request - I would have started a "discussion" but it's not enabled on this repo.

1. What version of Python are you using?

python --version --version
Python 3.10.5 (main, Jun  6 2022, 18:49:26) [GCC 12.1.0]

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

python -c 'import platform; print(platform.platform())'
Linux-5.18.9-arch1-1-x86_64-with-glibc2.35

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

asn1crypto==1.5.1
asttokens==2.0.5
attrs==21.4.0
backcall==0.2.0
black==22.6.0
certifi==2022.6.15
cffi==1.15.0
charset-normalizer==2.0.12
click==8.1.3
cryptography==36.0.2
debugpy==1.6.0
decorator==5.1.1
entrypoints==0.4
executing==0.8.3
greenlet==1.1.2
idna==3.3
iniconfig==1.1.1
ipdb==0.13.9
ipykernel==6.15.0
ipython==8.4.0
jedi==0.18.1
jupyter-client==7.3.4
jupyter-core==4.10.0
matplotlib-inline==0.1.3
mypy-extensions==0.4.3
nest-asyncio==1.5.5
oscrypto==1.3.0
packaging==21.3
parso==0.8.3
pathspec==0.9.0
pexpect==4.8.0
pickleshare==0.7.5
platformdirs==2.5.2
pluggy==1.0.0
prompt-toolkit==3.0.29
psutil==5.9.1
ptyprocess==0.7.0
pure-eval==0.2.2
py==1.11.0
pycparser==2.21
pycryptodomex==3.14.1
pydantic==1.9.1
pydantic-sqlalchemy==0.0.9
Pygments==2.12.0
PyJWT==2.4.0
pyOpenSSL==22.0.0
pyparsing==3.0.9
pytest==7.1.2
python-dateutil==2.8.2
pytz==2022.1
pyzmq==23.2.0
requests==2.28.0
six==1.16.0
snowflake-connector-python==2.7.8
snowflake-sqlalchemy==1.3.4
SQLAlchemy==1.4.37
stack-data==0.3.0
toml==0.10.2
tomli==2.0.1
tornado==6.1
traitlets==5.3.0
typing_extensions==4.2.0
urllib3==1.26.9
wcwidth==0.2.5

I have a table in Snowflake created like this:

create or replace TABLE WH.SNAME.TNAME cluster by (date_trunc('hour',TIMESTAMP))(
	TIMESTAMP TIMESTAMP_NTZ(9) NOT NULL,
	QUANTITY NUMBER(38,8),
	primary key (TIMESTAMP)
);

I would like to:

1. represent this properly in sqlalchemy-ORM (with cluster_by)
2. run queries with where date_trunc('hour',TIMESTAMP) > ...

what I have for 1:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import DateTime,  Float

Base = declarative_base()


class TableORM(Base):
    __tablename__ = "TNAME"
    __table_args__ = {"schema": "SNAME"}

    TIMESTAMP = Column(DateTime)
    QUANTITY = Column(Float)

    __mapper_args__ = {
        "primary_key": [TIMESTAMP]
    }

But I'm not sure how to add the "cluster by" information in the above class.

For 2 I have:

query = db_session.query(TableORM).filter(TableORM.TIMESTAMP > ...)

but this obviously is not what I want - I cluster by date_trunc('hour', TIMESTAMP) and thus I would like to use exactly this in ORM queries. How can I achieve this?

[sfc-gh-mkeller]

recreate jira

[github-actions[bot]]

To clean up and re-prioritize bugs and feature requests we are closing all issues older than 6 months as of Apr 1, 2023. If there are any issues or feature requests that you would like us to address, please re-create them. For urgent issues, opening a support case with this link Snowflake Community is the fastest way to get a response