Cross-account AWS Glue Data Catalog access with Glue ETL

To process data in AWS Glue ETL, DataFrame or DynamicFrame is required. A DataFrame is similar to a table and supports functional-style (map/reduce/filter/etc.) along with SQL operations. The AWS Glue DynamicFrame is similar to DataFrame, except that each record is self-describing, so no schema is required initially. It computes a schema on-the-fly when required, and …

Implementing Glue ETL job with Job Bookmarks

AWS Glue is a fully managed ETL service to load large amounts of datasets from various sources for analytics and data processing with Apache Spark ETL jobs. In this post I will discuss the use of AWS Glue Job Bookmarks feature in the following architecture. AWS Glue Job Bookmarks help Glue maintain state information of …

Aurora MySQL – Export data to S3

Using SELECT INTO OUTFILE S3 you can query data from an Aurora MySQL DB cluster and save it directly into text files stored in S3 bucket. 1. Create an IAM policy for S3. { "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": [ "s3:DeleteObject", "s3:GetBucketLocation", "s3:GetObject", "s3:ListBucket", "s3:ListBucketMultipartUploads", "s3:PutObject" ], "Resource": [ "arn:aws:s3:::bucket-name", …

Reading Parquet files with AWS Lambda

I had a use case to read data (few columns) from parquet file stored in S3, and write to DynamoDB table, every time a file was uploaded. Thinking to use AWS Lambda, I was looking at options of how to read parquet files within lambda until I stumbled upon AWS Data Wrangler. From the document …

Scan Dynamodb table from AWS Glue in different account

In this blog post I will list down the steps required to setup the AWS Glue job to scan the dynamodb table in another account. In my setup, I scan the dynamodb table in Account A (us-west-2), perform glue transformations in Account B (us-east-1) and write it to S3 in Account B. Account A - …

AWS Glue – Querying Nested JSON with Relationalize Transform

AWS Glue has transform Relationalize that can convert nested JSON into columns that you can then write to S3 or import into relational databases. As an example - Initial Schema: >>> df.printSchema() root |-- Id: string (nullable = true) |-- LastUpdated: long (nullable = true) |-- LastUpdatedBy: string (nullable = true) |-- Properties: struct (nullable …

Redshift: Convert TEXT to Timestamp

How do you convert TEXT to timestamp in redshift? If the score column has data in given format, how can you display the timestamp. {"Choices":null, "timestamp":"1579650266955", "scaledScore":null} select cast(json_extract_path_text(score, 'timestamp') as timestamp) from schema.table limit 10; This sql will fail with -- ERROR: Invalid data DETAIL: ----------------------------------------------- error: Invalid data code: 8001 context: Invalid format …