'Redshift: copy command Json data from s3

I have the following JSON data.

{  
   "recordid":"69",
   "recordTimestamp":1558087302591,
   "spaceId":"space-cd88557d",
   "spaceName":"Kirtipur",
   "partnerId":"Kirtipur",
   "eventType":"event-location-update",
   "eventlocationupdate":{  
      "event":{  
         "eventid":"event-qcTUrDAThkbPsXi438rRk",
         "userId":"",
         "tags":[  

         ],
         "mobile":"",
         "email":"",
         "gender":"OTHER",
         "firstName":"",
         "lastName":"",
         "postalCode":"",
         "optIns":[  

         ],
         "otherFields":[  

         ],
         "macAddress":"55:56:81🇧🇦a4:6d"
      },
      "location":{  
         "locationId":"location-bdfsfsf6a8d96",
         "name":"Kirtipur Office - wireless",
         "inferredLocationTypes":[  
            "NETWORK"
         ],
         "parent":{  
            "locationId":"location-c39ffc49",
            "name":"Kirtipur",
            "inferredLocationTypes":[  
               "vianet"
            ],
            "parent":{  
               "locationId":"location-8b47asdfdsf1c6a",
               "name":"Kirtipur",
               "inferredLocationTypes":[  
                  "ROOT"
               ]
            }
         }
      },
      "ssid":"",
      "rawUserId":"",
      "visitId":"visit-ca04ds5secb8d",
      "lastSeen":1558087081000,
      "deviceClassification":"",
      "mapId":"",
      "xPos":1.8595887,
      "yPos":3.5580606,
      "confidenceFactor":0.0,
      "latitude":0.0,
      "longitude":0.0
   }
}

I need to load this from the s3 bucket using the copy command. I have uploaded this file to my S3 bucket.

I have worked with copy command for csv files but have not worked with copy command on JSON files. I researched regarding json import via copy command but did not find solid helpful command examples.

I used the following code for my copy command.

COPY vianet_raw_data 
from 's3://vianet-test/vianet.json' 
with credentials as '' 
format as json 'auto';

This did not insert any data.

Can anyone please help me with the copy command for such JSON?

Thanks and Regards

amazon-redshift

Solution 1:^[1]

There are 2 scenarios (most probably 1st):

You want AWS's auto option to load from the s3 you provided in line 2. For that, you do:

COPY vianet_raw_data 
from 's3://vianet-test/vianet.json' 
with credentials as '' 
json 'auto';

Use custom json loading paths (i.e. you don't want all paths automatically)

COPY vianet_raw_data 
from 's3://vianet-test/vianet.json' 
with credentials as '' 
format as json 's3://vianet-test/vianet_PATHS.json';

Here, 's3://vianet-test/vianet_PATHS.json' contains all the specific JSON from the main location you want to look at.

Refer: https://docs.aws.amazon.com/redshift/latest/dg/r_COPY_command_examples.html#r_COPY_command_examples-copy-from-json

Solution 2:^[2]

One issue I notice is the formatting. It is nicely formatted the way you shared which is good to see for us, but when loading it into Redshift via COPY command I generally trim the JSON by removing all 'new line' and blank spaces.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	Vzzarr

'Redshift: copy command Json data from s3

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]