4 ms·

To say that Redshift doesn't support Arrays vs BigQuery's UDF support is a little confusing. Redshift has built-in functions for array operations and Python UDF

To say that Redshift doesn't support Arrays vs BigQuery's UDF support is a little confusing. Redshift has built-in functions for array operations and Python UDF support. https://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_ARRAY_ELEMENT_TEXT.html https://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_...

georgewfraser 8y ago
The fundamental problem is that Redshift doesn't have an UNNEST/FLATTEN operator. So even if you write a UDF that does something with an array, it's extremely awkward to work with the results in SQL.
- pcarolan 8y ago
  That is a big downside and I hope Redshift does add this to their product. Unnesting + flattening is difficult (but not impossible) to do in Redshift. I'd argue that python + built-in JSON support makes it an orange not a red though in that category, but thank you for the clarification and thank you for this awesome analysis!
  - cosmie 8y ago
    One downside to using Redshift's Python UDFs is that they don't parallelize; instead, they process on a single node. So while technically possible, it's only really feasible to use them for purposes like JSON flattening as part of your ETL load, rather than on the fly. And since they only allow scalar outputs, you either have to run a bunch (one per flattened value) or use a hacky solution where you leverage some nested CTEs to try to output all of the flattened records as a single value with a custom delimiter, then leverage built in functions to further split that scalar into multiple rows. I've heavily used Python UDFs in Redshift, and they're fantastic to fall back on when you're backed into a corner. But they're such a second-class citizen within Redshift[1] that I wouldn't consider it a viable alternative to the lack of unnesting and flattening support. [1] I've used Python UDFs extensively since they came out, but haven't evaluated their performance characteristics in about 6 months. Please let me know if my analysis is out of date.

georgewfraser 8y ago

The fundamental problem is that Redshift doesn't have an UNNEST/FLATTEN operator. So even if you write a UDF that does something with an array, it's extremely awkward to work with the results in SQL.

pcarolan 8y ago
That is a big downside and I hope Redshift does add this to their product. Unnesting + flattening is difficult (but not impossible) to do in Redshift. I'd argue that python + built-in JSON support makes it an orange not a red though in that category, but thank you for the clarification and thank you for this awesome analysis!
- cosmie 8y ago
  One downside to using Redshift's Python UDFs is that they don't parallelize; instead, they process on a single node. So while technically possible, it's only really feasible to use them for purposes like JSON flattening as part of your ETL load, rather than on the fly. And since they only allow scalar outputs, you either have to run a bunch (one per flattened value) or use a hacky solution where you leverage some nested CTEs to try to output all of the flattened records as a single value with a custom delimiter, then leverage built in functions to further split that scalar into multiple rows. I've heavily used Python UDFs in Redshift, and they're fantastic to fall back on when you're backed into a corner. But they're such a second-class citizen within Redshift[1] that I wouldn't consider it a viable alternative to the lack of unnesting and flattening support. [1] I've used Python UDFs extensively since they came out, but haven't evaluated their performance characteristics in about 6 months. Please let me know if my analysis is out of date.