Forum Discussion

tmarshall's avatar
tmarshall
Advisor
9 years ago

Bulk Data API - Too Many Fields Error

Hello Lithium Developers and NaokiT,   More questions about the Bulk Data API and how to best use it so it will not break our mapping structure.   If we use the curl script and specify all the av...
  • NaokiT's avatar
    NaokiT
    9 years ago

    tmarshall

     

    1)  While we do not often add columns or change the order, it will happen on occasion.  We disclose these changes in the Lithium release notes, but I also try to post the on the Bulk Data API TKB a few weeks before it happens.  We strongly recommend that you not count on field order to map your data, but instead do the mapping by header name.  The header names have never changed.  So ingest the full file - preferably as a json file by adding "Accept: application/json".  Then parse it on your side for the fields that you need.  Or if you're using some sort of BI tool there should be some function like HLookup in Excel.

     

    2) That's correct you are capped at 20, but inclusive only and not exclusive.  We can expand the number of fields if that helps, but the exclusive feature will probably not come till later this year.

     

    3) If you have to do the mappings across 3 exports than document.id is the common key you can join across all data.  But document.id will only be in files from March 4, 2016

NaokiT's avatar
NaokiT
Lithium Alumni (Retired)
9 years ago

tmarshall

 

1)  While we do not often add columns or change the order, it will happen on occasion.  We disclose these changes in the Lithium release notes, but I also try to post the on the Bulk Data API TKB a few weeks before it happens.  We strongly recommend that you not count on field order to map your data, but instead do the mapping by header name.  The header names have never changed.  So ingest the full file - preferably as a json file by adding "Accept: application/json".  Then parse it on your side for the fields that you need.  Or if you're using some sort of BI tool there should be some function like HLookup in Excel.

 

2) That's correct you are capped at 20, but inclusive only and not exclusive.  We can expand the number of fields if that helps, but the exclusive feature will probably not come till later this year.

 

3) If you have to do the mappings across 3 exports than document.id is the common key you can join across all data.  But document.id will only be in files from March 4, 2016

  • tmarshall's avatar
    tmarshall
    Advisor
    9 years ago

    Thanks NaokiT. I'll work with our BI guy to understand if we can map to name as this sounds like the best way to have the script to continue to work if/when items are added on the Lithium side.

     

    Can you please clarify where to put the JSON text in the following script and what that looks like? I've put it in a few places and have not been successful.

     

    curl https://api.lithium.com/lsi-data/v1/data/export/community/mycompany.prod?fromDate=201603150000&toDate=201603160000  -H "client-id: cooluniquekeyforustogetdata=" -u "secondcooluniquekey:" >> C:\Users\tmarshall\bulk_test\prod_20160315-test1.txt
    • NaokiT's avatar
      NaokiT
      Lithium Alumni (Retired)
      9 years ago

      tmarshall  Great to hear.  I think that's a better way forward.  

       

      I think you need to add -H again to add to the header.  Try this

       

      curl https://api.lithium.com/lsi-data/v1/data/export/community/mycompany.prod?fromDate=201603150000&toDate=201603160000 -H "client-id: cooluniquekeyforustogetdata=" -H "Accept: application/json" -u "secondcooluniquekey:" >> C:\Users\tmarshall\bulk_test\prod_20160315-test1.txt

       

      Out of curiosity what BI tool are you planning to use?  I'm guessing they have their own JSON connector which should make it easier to use.   

      • tmarshall's avatar
        tmarshall
        Advisor
        9 years ago

        Hey NaokiT,

         

        We are using Informatica, and checked with our resident BI/Informatica developer and he is not aware of an HLOOKUP type option or JSON connector. This does not sound possible to me, but I'm not an informatica expert ;) I have asked him to try and pursue a support ticket to see what options we could use. I tried the updated script and it works great so will have him include it with his ticket. On a side note, it looks like the information is loaded by the hour and even if I specify a minute of time it loads the same sized file.

         

        I've reached out to Lithium support to understand if we can increase the number of fields we can call, so you might be getting a ping from Steve about this possibility. We are behind on this project and need to get something to the business and naming the fields we know works. We appreciate your help, and will continue to research if we can do a lookup as I agree it does sound like the best option, but as a backup hope you can work with us.

         

        Thanks,

        Tim