Snowflake db backend
We have written a Snowflake DB backend that we use internally in several projects and are considering releasing it for public use. My preference would be to incorporate it as a core backend so the nuances of Snowflake SQL are considered when changes are made to the core system. Is this something we should pursue? Thanks, Scott -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/3e5034a8-6c75-4f28-9830-2c87b671ae86n%40googlegroups.com.
Re: Snowflake db backend
Snowflake (at this point) isn't as fast as PostgreSQL for smaller transactions. PostgreSQL couldn't handle the performance we needed to drive our apps and we really didn't want to support a hybrid db app. We give up a little speed for the convenience of having everything in one system. At a recent Snowflake event, they stated that 30% of their users were using Snowflake for application backends. They are also working to improve their execution time on smaller transactions. Honestly, I don't think we'd release this as a third party package. We have had to code around a lot of core db specific code to get Snow SQL to work. If there was a hard API for DB backends my response would be different. It would only make sense for us if this backend were part of standard Django testing and support consideration. "Merging the backend straight to core would impose the requirement that all changes to Django core consider Snowflake" That's what has made the process harder than it should be to support a non-core db. It creates second class system support in favor of the core dbs that are code and tested against. Remove all of the db backends and make them all third party and you'd see a lot more db support. Scott On Friday, January 22, 2021 at 5:22:24 PM UTC-5 Adam Johnson wrote: > Yes we would favour a third party backend. Merging the backend straight to > core would impose the requirement that all changes to Django core consider > Snowflake, which is a fairly steep cost when there's only one organization > using it (at current). If you release as a third party package and show > some adoption, it could then be argued that it's worth merging. > > Additionally Scott - would love to see Disney appear on this page, perhaps > the "Platinum" section would feel comfortable: > https://www.djangoproject.com/fundraising/ ;) > > On Fri, 22 Jan 2021 at 21:57, Tom Forbes wrote: > >> I think this should definitely be released as a third party package, and >> if there is enough community interest it might be considered for inclusion. >> We could definitely update the docs to link to the package though. >> >> On a side note, is Snowflake fast enough for general purpose web apps? >> When we evaluated it the performance characteristics where similar to >> Redshift, optimised for large analytical aggregation rather than fast, >> smaller result sets typically seen with Django apps. >> >> Tom >> >> On 22 Jan 2021, at 21:49, Scott Fought wrote: >> >> >> >> We have written a Snowflake DB backend that we use internally in several >> projects and are considering releasing it for public use. My preference >> would be to incorporate it as a core backend so the nuances of Snowflake >> SQL are considered when changes are made to the core system. Is this >> something we should pursue? >> >> >> Thanks, >> >> >> Scott >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Django developers (Contributions to Django itself)" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to django-develop...@googlegroups.com. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/django-developers/3e5034a8-6c75-4f28-9830-2c87b671ae86n%40googlegroups.com >> >> <https://groups.google.com/d/msgid/django-developers/3e5034a8-6c75-4f28-9830-2c87b671ae86n%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Django developers (Contributions to Django itself)" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to django-develop...@googlegroups.com. >> > To view this discussion on the web visit >> https://groups.google.com/d/msgid/django-developers/08448193-19F7-4AA8-A112-ED6802DC7FBF%40tomforb.es >> >> <https://groups.google.com/d/msgid/django-developers/08448193-19F7-4AA8-A112-ED6802DC7FBF%40tomforb.es?utm_medium=email&utm_source=footer> >> . >> > > > -- > Adam > -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/cb99116e-65a8-458c-87a1-74458194d741n%40googlegroups.com.
Re: Snowflake db backend
In order to release the code, I need to provide a sustainment plan per our open source committee. Which is why I was reaching out. I can share some of the challenges conforming Snowflake to the SQL model Django uses. It sounds like we may have had similar experiences writing backends, but possibly we differ in completeness. I was able to write a working backend that met all of our application needs in about 3 weeks. Conforming to the unit tests is another story. We pass ~90% of the tests, but that last 10% will take a lot more effort to complete. For reference, we're using the snowflake-connector-python module, which implements PEP 249, v2.0. Snowflake does not support lastrowid. So, we grab the last ID inserted with a 'SELECT MAX(pk_name) FROM table_name'. This is obviously prone to failure. Assigning an ID during the INSERT would provide similar results on all backends. The feature flag `supports_transactions` really means `supports_nested_transactions`. Snowflake supports a single level of transaction, BEGIN + (ROLLBACK|COMMIT). Multiple BEGINS contribute to the current (only) transaction. Since I have to set this flag to False, no transactions are used, even ones that are supported and testing grinds to a crawl with all of the table truncations and repopulation. Since Django *normally* operates in auto-commit mode, this isn't a huge deal. Snowflake also doesn't support save points, but the feature flag seems to do what is expected when disabled. Snowflake does not support column references without a FROM or JOIN clause. I've only encountered this used in the unit tests. I've been able to work around most of the function differences by adding as_snowflake methods to the appropriate classes. There are a few cases like JSON_OBJECT that don't translate well, which work, but not entirely as expected. A search for 'connection.vendor == ' in the core code shows one example of where core backends are given privileged access to core Django inner workings that 3rd party backends don't. Please take these comments as observations, not complaints. I understand project evolution and the resistance to change something that doesn't seem broken. Maybe keep some of these thoughts in mind when a change is made to the core backends or the compiler and consider how other backends will need to implement that new feature. Scott On Saturday, January 23, 2021 at 10:36:38 AM UTC-5 f.apo...@gmail.com wrote: > On Saturday, January 23, 2021 at 12:41:35 AM UTC+1 t...@tomforb.es wrote: > >> > Honestly, I don't think we'd release this as a third party package. We >> have had to code around a lot of core db specific code to get Snow SQL to >> work >> >> This is good feedback that is valuable for us to hear. Even if you don’t >> release it as a fully maintained package just understanding the pain points >> you encountered while developing this is great feedback that can help us >> improve. >> >> Would you be willing to share specific code snippets here, or just >> general areas that you found challenging while developing this backend? >> > > +1, if it were possible to show the full code (even if you are not going > to maintain it in the longrun) would be very much helpful to improve. > > Like Tim I wrote one or two database backends in the past (informix and > the likes) and it was not to bad. That said, mine where relatively easy to > implement due to their closeness to existing database and (somehwat) > conformance with SQL standards. I'd love to see the code for snowflake and > maybe we can identify a few areas that would need improvement. > > Cheers, > Florian > -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/768b428d-8d7d-462a-b925-484a6e681b2bn%40googlegroups.com.
