If i.startswith( "struct]*>", "longstring",i) Namestr= "Name" else: # This is when we deal with json copied straight from AWS Console WebUI network traffic (aka being lazy) Namestr= "name" if j.get( "TableList") != None: # This is when we bother to run `aws glue get-tables`Ĭol=j Note that i wrote it quickly to parse the data for my need so you may have to tweak some regex, etc to fit yours. Of course, to get files automatically ingested into Redshift the moment they are dropped into the bucket, I highly recommend checking out the blog post above by AWS.īelow is the script to parse glue.json. JSON 's3://bucketname_where_you_host/jsonpath.json' GZIP
#Redshift json object manual#
Below is an example of what a manual COPY command look like COPY SchemaName.TableName from 's3://bucketname/objectid' This script runs over it and generates CREATE TABLE command for AWS Redshift and a working jsonpath.json file that you can use to load data using COPY command straight from S3 to your Redshift. This file contains column information for your data. After glue runs, you can run aws glue get-tables -database-name > glue.json to get the tablelist and their data structure.
Glue is very good at identifying the data structure you deal with. It was clear that the job can be done a lot quicker to write a parser for glue result to create table. This JSONPath file has to be perfectly aligned with the column in CREATE TABLE command for redshift. This is, of course, not including the fact that I need to generate a JSONPath file to tell Redshift which field/column relates to which nested json object. However, when i looked at the glue schema, turn out it is ALOT more than i thought (it worked out to be around 300 columns in the end after you expand every nested json). It is not overly complicated but you have to be comfortable with deploying cloudformation stack, managing lambda function & its dynamodb to queue job and using the utility tool writen in Nodejs to create configurations for our lambda function.Īfter getting all that setup, my next task is to create Redshift table… To quickly try sending data to the cluster, i manually went through some files, picked out some common fields, wrote CREATE TABLE command by hand and it worked great.
#Redshift json object code#
Turn out, I did not have to write a single piece of code for that, AWS has already done it for me in this blog post. I knew a lambda trigger on S3 objectcreate event would be perfect for the job and it can simply send a COPY command over to Redshift to let it migrate data to our cluster. This tool would crawl over your data and identify tables and schema that would fit the given data! How neat!įast forward, I wanted to attempt flatten the JSON and chuck it into a redshift cluster. These JSON come in with different attributes, tags, type, values and it was difficult to work out the schema without spending a great deal of time going through them manually or running script to pick up the logs, run some logic across to identify new fields etc…ĪWS Glue come to rescue! I first used Athena to play around with the data and a colleague of mine found Redshift spectrum, which, during setup would run you through setting AWS Glue.
Recently I had to deal with large amount of unsorted JSON input log. There can be up to 7 or 10 chunks of options in each row of the "Analysis" column from the table.Sometimes the path to copy large amount of unsorted data from S3 to Redshift is a bit annoying. Below is an example of the data held in the "Analysis" column in the table. The new columns "Currency", "Price", "Days" and "SpecialDays" are all created from this transform, but they have no data in them. The columns "Id", "LineItemId", "ItemHash" and "Request Date" are all columns from the table. , CAST(json_extract_path_text("Analysis", 'FulfillmentOptions', 'SpecialDays', TRUE ) AS Text) AS SpecialDays , CAST(json_extract_path_text("Analysis", 'FulfillmentOptions', 'Days', TRUE ) AS Text) AS Days , CAST(json_extract_path_text("Analysis", 'FulfillmentOptions', 'Price', TRUE ) AS Text) AS Price , CAST(json_extract_path_text("Analysis", 'FulfillmentOptions', 'Currency', TRUE ) AS Text) AS Currency The code I'm using is: SELECT "Id", "LineItemId", "ItemHash" I'm attempting to parse out a json column with multiple nodes of data in the same chunk of json from a table in a relational database.
0 kommentar(er)
