Glue | Simple prototyping - HTML partials served up with AJAX

IIUC, if all unique id's can be sorted into contiguous blocks.

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

The services you mention (mostly) live outside of VPCs.

VPCs are used for services that use virtual computers, such as Amazon EC2 computers and Amazon RDS databases.

By using services that don't involve specific 'computers' (such as Amazon S3, Athena, QuickSight) you can take advantage of much lower costs, paying only what you use. These services do not mimic traditional servers and therefore don't need VPCs. All the networking complexity is hidden and you can concentrate on using the service instead of running a network.

Yes, VPCs add extra security, but that's only because resources on a VPC need securing due to potential security holes. The services you mention are all secured via IAM and do not expose themselves outside the published APIs.

The "Max concurrent job runs per account" limit is a soft limit (https://docs.aws.amazon.com/general/latest/gr/glue.html). Maybe log a service request with AWS and ask for an increase in the limit. The second thing is I am not sure how you have implemented your sleep action in the code, maybe instead of doing just a sleep catch the exception each time you make the call, if there is an exception, sleep with an exponential backoff in seconds and try again when sleep time is finished and repeat until your get a positive response OR when you reach your own set limit to stop. This way your processing will not stop until you give up, but just slow down when throtteling kicks in.

The data is dynamically retrieved from an API call. You can retrieve direct from that url and simplify the json returned to get a dataframe:

tl;dr somewhere deep in the guts of the cli package (called to generate the pretty-printed output about column types), the code is generating a random string to use as a label.

A major clue is that

Both spark DataFrame and glue DynamicFrame infer the schema when reading data from json, but evidently, they do it differently: sparks treats all numerical values as bigint, while glue is trying to be clever, and (I guess) looks at the actual range of values on the fly.

Some more info about DynamicFrame schema inference can be found here.

If you are going to write parquet in the end anyway, and want the schema stable and consistent, I'd say your easiest way around this is to just revert your change and go back to spark DataFrame. You can also use apply_mapping to change the types explicitly after reading the data, but it seems like defeating the purpose of having the dynamic frame in the first place.

Yes. PyODBC works with Synapse. It's not perfect but I use it.

https://docs.microsoft.com/en-us/azure/azure-sql/database/connect-query-python

Note that installing it can be a bit tricky. You need the Python package, but also the ODBC driver and the apt package unixodbc-dev.

Here is the part of my dockerfile that does it on Ubuntu 18.04

If your partitions are "predictable", for example date based, you could completely bypass the crawlers and use partition projection. See the docs:

https://docs.aws.amazon.com/athena/latest/ug/partition-projection.html