Query 4 levels and get bulk csv from workbench

Question

I'm hoping this is an easy one but I'm having no luck in developer console or workbench.

I have an object called Order_Line_Item__c that is related to Order__c which is related to Opportunity which is then related to Account. I'm trying to run the following:

SELECT Id, Order__r.Opportunity__r.AccountId, Document_Category__c, Quantity__c
FROM Order_Line_Item__c 
WHERE Order__r.Opportunity__r.StageName = 'Closed Won'

In developer console this returns [object Object] with a column header of Order_r.Opportunity_r instead of the AccountId. I run this exact same query in workbench and it returns my first 200 records great. But I try to return bulk csv and it fails saying:

Failed: InvalidBatch : Failed to process query: FUNCTIONALITY_NOT_ENABLED: Foreign Key Relationships not supported in Bulk Query

My end goal here is to group by AccountId and sum the Quantity__c for each unique Account. This will give me the total products purchased by Account. I can do multiple small queries, open in excel, run VLOOKUPs, and get my totals that way. But is there an easier way to do all of this? Any pointers or documentation would be appreciated.

Thanks in advance!

Answer 1

Both developer console and workbench will fail with this sort of query. You'll need to use a more full featured ETL tool, like Jitterbit, Pentaho, or Talend (which all have free versions).