amazon-redshift
Return elements of Redshift JSON array on separate rows
Thanks to this inspired blog post, I’ve been able to craft a solution. This is: Create a look-up table to effectively ‘iterate’ over the elements of each array. The number of rows in this table has be equal to or greater than the maximum number of elements of arrays. Let’s say this is 4 (it … Read more
Using psycopg2 with Lambda to Update Redshift (Python)
In order for this to work you need to build psycopg2 with statically linked libpq.so library. Check out this repo https://github.com/jkehler/awslambda-psycopg2. It has already build psycopg2 package and instructions how to build it yourself. Back to your questions: What is causing this problem? psycopg2 needs to be build an compiled with statically linked libraries for … Read more
Using sql function generate_series() in redshift
Amazon Redshift seems to be based on PostgreSQL 8.0.2. The timestamp arguments to generate_series() were added in 8.4. Something like this, which sidesteps that problem, might work in Redshift. SELECT current_date + (n || ‘ days’)::interval from generate_series (1, 30) n It works in PostgreSQL 8.3, which is the earliest version I can test. It’s … Read more
Redshift. Convert comma delimited values into rows
A slight improvement over the existing answer is to use a second “numbers” table that enumerates all of the possible list lengths and then use a cross join to make the query more compact. Redshift does not have a straightforward method for creating a numbers table that I am aware of, but we can use … Read more