Split JSON file into

Question

I am trying to ingest this data into Snowflake. The file is too big. The suggestion of

STRIP_OUTER_ARRAY = FALSE

...is not any use here. I've done a lot of searching. DQ seems to be a good option and I have it installed on my Windows 10 machine but cannot figure out which arguments I need to use and the documentation is beyond me at the moment.

I thought of working with the file as text in python but have not got that working either.

I have not worked with JSON much. Seems like there must be a simple solution that I am missing. All help appreciated.

Source file:

https://www.kaggle.com/yamaerenay/spotify-dataset-19212020-160k-tracks?select=dict_artists.json

This is a dictionary of IDs and the IDs to which they are linked (up to 20, empty if none). The descriptions are in a different file.

Sample Data:

{'0DheY5irMjBUeLybbCUEZ2': [],
 '0DlhY15l3wsrnlfGio2bjU': [],
 '0DmRESX2JknGPQyO15yxg7': [],
 '0DmhnbHjm1qw6NCYPeZNgJ': [],
 '0Dn11fWM7vHQ3rinvWEl4E': [],
 '0DotfDlYMGqkbzfBhcA5r6': [],
 '0DqP3bOCiC48L8SM9gK4W8': [],
 '0Drs3maQb99iRglyTuxizI': [],
 '0DsPeAi1gxPPnYjgpiEGSR': [],
 '0DtvnTxgZ9K5YaPS5jdlQW': [],
 '0Du4t8UK1lBD8TnOIa3c17': [],
 '0DuJ5k2ERoAizEdgUzJGTc': [],
 '0DujX6vG6qOz2wVBCQg5Vi': [],
 '0DvBSYDq5ikdlKgp94Yamq': [],
 '0DvvojCMIqsOT1Btiwvq1h': ['3Y9UedETQztUmRuB2pYaGR',
  '6ng2L9Pwj7NeXm0vJW8LLr',
  '0QAlsftQZIVyNXDtK7PEt2',
  '32qUUy6h1wKd5jN4vRSF20',
  '7kc7HZFnOwUWd8dL8jypPf',
  '07L8VAxYnAlJwnNEWdTIHI',
  '3A6TC7BhsBm8LktswCdm0X',
  '5FeE1flsA6Vuzi4uCMtpEC',
  '7oJlnzQBZE2SAS1JrQHI3F',
  '4LVKk2qM6bwMYHT3fg5sCN',
  '5wbq8xuXzD2EONQZDhZVoQ',
  '7ofTH0nFjkbKuRTpazOzzV',
  '7e9esbJQ7IVwkxpqjSEFL9',
  '4xirLzVl1nnPPBgUFvFbFh',
  '5N6NpGkfs4tFA4HLjNA7xh',
  '2qxp4iVVSlrFAImfZmPmsP',
  '4hyy9Yw1ZxHW2WndWc92h8',
  '3fF0uc3b2sQ5Gc3R09oWej',
  '2VXQKnUs5dpfG6j7AtrH0b',
  '7vHKCt7yOiBg1Hci7izDLh'],
 '0DxcE03jyjg3wQVHUQKx8C': [],

user_132438 · Answer

That's a hard json to ingest into different rows, but we can do a quick transformation with jq:

jq --compact-output '. | to_entries| .[]'

It will transform an input like yours:

Into one easy to ingest by Snowflake:

https://jqplay.org/s/EHzqi55IK2

user_14740792 · Answer

Felipe gave me most of the answer, i.e. run this line in JQ:

jq --compact-output '. | to_entries| .[]' dict_artists.json > dict_artists2.json

Something was up with the formatting in the output of this file; Felipe's screenshot showed unexpected characters while I have since noticed in a text editor that the file was encoded as something other than just UTF8 (a slightly different flavour). I saved as UTF8 with a different file name and have successfully ingested the file.

Split JSON file into <16MB for ingestion to Snowflake

2 Answers