redshift | hybrid automata , similar to SHIFT and Lambda-SHIFT | BPM library

Went with option 2. The Redshift Copy/Load process writes csv with manifest to S3 in any case so duplicating that is pointless.

Regarding the Questions:

1. N/A

2. Job Bookmarking does work. There is some gotchas though - ensure Connections both to RDS and Redshift are present in Glue Pyspark job, IAM self ref rules are in place and to identify a row that is unique [I chose the primary key of underlying table as an additional column in my materialized view] to use as the bookmark.

3. Using the primary key of core table may buy efficiencies in pruning materialized views during maintenance cycles. Just retrieve latest bookmark from cli using aws glue get-job-bookmark --job-name yourjobname and then just that in the where clause of the mv as where id >= idinbookmark

   conn = glueContext.extract_jdbc_conf("yourGlueCatalogdBConnection") connection_options_source = { "url": conn['url'] + "/yourdB", "dbtable": "table in dB", "user": conn['user'], "password": conn['password'], "jobBookmarkKeys":["unique identifier from source table"], "jobBookmarkKeysSortOrder":"asc"}

datasource0 = glueContext.create_dynamic_frame.from_options(connection_type="postgresql", connection_options=connection_options_source, transformation_ctx="datasource0")

That's all, folks

Consider changing WHERE flag1 = 'New' to WHERE date_diff ('day',fod,dt) <= 28

Try Query.union. Example: verbatim from the documentaion:

No.

As you have correctly understood, listagg is a function implemented by Redshift, rather than being inherited from Postgres/Paraccel, and it has been implemented only on the worker nodes.

The has function is from Postgres, and is implemented only on the leader-node.

The query planner will not permit a query using a leader-node only function to recruit worker nodes, so you cannot call listagg.

(BTW, if I remember correctly, that 'v' for reltype is also going to pick up materialized views.)

As an aside, you can in fact obtain the information you are looking for directly from the system tables, but this is a long and complex undertaking. I am a Redshift specialist and it took me two months for the first version, although I was working at the time.

There is a difference between '' and NULL -- and I should note that this is expected.

You can solve this in one of two ways:

How about creating a new custom operator? It should accept all the cli arguments and then you can pass them to code from existing script. Here is some rough draft of what I would do:

params argument is not "Templated", so it would only render strings. So move your param directly to SQL

To use Variable you could use DECLARE

I suspect that this probably won't work.

My reasoning is that I know that Amazon Redshift can load data from Amazon S3 even when the Redshift cluster is in a private subnet and there is no NAT server. Thus, Redshift has its "own connection" to S3 in the backplane, rather than going through the VPC.

Since the S3 Access Point exists only in a VPC, Redshift would not be able to use the Access Point.

(I look forward to being corrected if anyone knows better!)