如何创建Jsonpath文件在redshift中加载数据

Question

下面是我对Json的一个示例记录：

{
  "viewerId": "Ext-04835139",
  "sid5": "269410578:2995631181:2211755370:3307088398:33879957",
  "firstHbTimems": 1.506283958371E12,
  "ipAddress": "74.58.57.31",
  "streamUrl": "https://dc3-ll-livedazn-dznlivejp.hs.llnwd.net/live/channel/1007/all/stream.m3u8?event_id=61824040049&h=c912885e2a69ffa7ea84f45dc18c004d",
  "asset": "[nlq9biy7trxl1cjceg70rogvd] Saints @ Panthers",
  "os": "IOS",
  "osVersion": "10.3.3",
  "deviceModel": "iPhone",
  "geoInfo": {
    "city": 63666,
    "state": 3851,
    "isp": 120,
    "longitudeTimes1K": -73562,
    "country": 37,
    "dma": 0,
    "asn": 5769,
    "latitudeTimes1K": 45502,
    "publicIP": 1245329695
  },
  "totalPlayingTime": 4.097,
  "totalBufferingTime": 0.0,
  "VST": 1.411,
  "avgBitrate": 202.0,
  "playStateSwitch": [
    "{'seqNum': 0, 'eventNum': 0, 'sessionTimeMs': 7, 'startPlayState': 'eUnknown', 'endPlayState': 'eBuffering'}",
    "{'seqNum': 1, 'eventNum': 5, 'sessionTimeMs': 1411, 'startPlayState': 'eBuffering', 'endPlayState': 'ePlaying'}"
  ],
  "bitrateSwitch": [

  ],
  "errorEvent": [

  ],
  "tags": {
    "LSsportName": "Football",
    "c3.device.model": "iPhone+6+Plus",
    "LSvideoType": "LIVE",
    "c3.device.ua": "DAZN%2F5560+CFNetwork%2F811.5.4+Darwin%2F16.7.0",
    "LSfixtureId": "5trxst8tv7slixckvawmtf949",
    "genre": "Sport",
    "LScompetitionName": "NFL+Game+Pass",
    "show": "NFL+Game+Pass",
    "c3.cmp.0._type": "DEVATLAS",
    "c3.protocol.type": "cws",
    "LSsportId": "9ita1e50vxttzd1xll3iyaulu",
    "stageId": "8hm0ew6b8m7907ty8vy8tu4tl",
    "LSvenueId": "na",
    "syndicator": "None",
    "applicationVersion": "2.0.8",
    "deviceConnectionType": "wifi",
    "c3.client.marketingName": "iPhone+6+Plus",
    "playerVersion": "1.2.6.0",
    "c3.cmp.0._id": "da",
    "drmType": "AES128",
    "c3.sh": "dc3-ll-livedazn-dznlivejp.hs.llnwd.net",
    "c3.pt.ver": "10.3.3",
    "applicationType": "ios",
    "c3.viewer.id": "Ext-04835139",
    "LSinterfaceLanguage": "en",
    "c3.pt.os": "IOS",
    "playerVendor": "Open+Source",
    "c3.client.brand": "Apple",
    "c3.cws.sf": "7",
    "c3.cmp.0._ver": "1",
    "c3.client.hwType": "Mobile+Phone",
    "c3.pt.os.ver": "10.3.3",
    "isAd": "false",
    "c3.device.cver.bld": "2.124.0.33357",
    "stageName": "Regular+Season",
    "c3.client.osName": "iOS",
    "contentType": "Live",
    "c3.device.cver": "2.124.0",
    "LScompetitionId": "wy3kluvb4efae1of0d8146c1",
    "expireDate": "na",
    "c3.client.model": "iPhone+6+Plus",
    "c3.client.manufacturer": "Apple",
    "LSproductionValue": "na",
    "pubDate": "2017-09-23",
    "c3.cluster.name": "production",
    "accountType": "FreeTrial",
    "c3.adaptor.type": "eCws1_7",
    "c3.device.brand": "iPhone",
    "c3.pt.br": "Non-Browser+Apps",
    "contentId": "nlq9biy7trxl1cjceg70rogvd",
    "streamingProtocol": "FairPlay",
    "LSvenueName": "na",
    "c3.device.type": "Mobile",
    "c3.protocol.level": "2.4",
    "c3.player.name": "AVPlayer",
    "contentName": "Saints+%40+Panthers",
    "c3.device.manufacturer": "Apple",
    "c3.framework": "AVFoundation",
    "c3.pt": "iOS",
    "c3.device.ver": "6+Plus",
    "c3.video.isLive": "T",
    "c3.cmp.0._cfg_ver": "1504808821",
    "c3.cws.clv": "2.124.0.33357",
    "LScountryCode": "America%2FEl_Salvador"
  },
  "playername": "AVPlayer",
  "isLive": "T",
  "playerVersion": "1.2.6.0"
}

如何创建jsonpath文件将其加载到redshift？

谢谢

Answer 1

您的json中有一个嵌套数组-所以jsonpath不会为您展开它。

关于如何继续，您有几个选择：

1. 你可以在更高的级别加载你的数据(例如playStateSwitch而不是seqNum )--然后尝试使用redshift来处理这些数据。这可能很棘手，因为您无法在redshift中分解数组中的json数据。
2. 您可以使用aws glue / python / pyspark或其他一些可以处理这些嵌套数组的etl工具对数据进行预处理。

Answer 2

这完全取决于最终目标，这在上面的描述中是不清楚的。我将按以下顺序处理该解决方案

定义需要加载到Redshift中的字段和数组值。如果需要复制所有记录，那么下一个检查就是如何处理多个数组记录。

如果作为JSON源的一部分缺少数组或键/值，那么JSONPath将不会按原样工作-因此，最好在将数据集复制到RS之前更新JSON以添加缺少的数组。可以使用Linux命令或外部工具(如JP或refer additional reference )完成JSON更新

如果嵌套数组中的所有值都是必需的，那么另一种解决方法是使用外部表an example

否则，可以使用此格式开发JSONPATH文件

{
    "jsonpaths": [
       "$.viewerId", ///root level fields
        ...
       "$geoInfo.city", /// object hierarchy 
        ...
       "$playStateSwitch[0].seqNum" ///define the required array number
        ...
    ]
 }

霍普，这有帮助。