Data Access API - Downloading content of Dataset in csv/json format

[vikash4]

Hi,

Few month back, we've ingested 200K record via batch ingestion. Records were in JSON format. Now we wanted to download the records in json or csv format. We are using following Data access api to download the dataset content... 

• Get the batches associated with Dataset - https://platform.adobe.io/data/foundation/catalog/batches?dataSet=<datase-id>
  "metrics": {
             "partitionCount": 382,           
              "inputFileCount": 2,            
              "outputRecordCount": 200000,
              "outputFileCount": 382,
              "inputRecordCount": 200000
          }
  Response : Return one batch id & metrics param return 382 as output file count 
• Get the files associated with the batch-id return in first step - https://platform.adobe.io/data/foundation/export/batches/<batch-id>/files?start=0&limit=100 . Since total output file is 382, I've executed this api four times with different "start" param values(0,100,200,300). Response : dataSetFileId, dataSetViewId & link to access the contents of dataset file. Example link : https://platform.adobe.io:443/data/foundation/export/files/<dataSetFileId> 
  Q1 : How can I get file details for all 382 files with one request? I mean, I don't want to reset "start" param each time. Is there any way to achieve this?
• Q2 : Do I need to call "/export/files" api individually for all 382 files to get the file path and access the content? Access the file path  : https://platform.adobe.io:443/data/foundation/export/files/<dataSetFileId> Access the content : https://platform.adobe.io:443/data/foundation/export/files/<dataSetFileId>/?path=<FilePath>.parquet
• Q3 : When I change the extension of path param value from .parquet to .csv, it return 404 response https://platform.adobe.io:443/data/foundation/export/files/<dataSetFileId>/?path=<FilePath>.csv 

{

    "type": "DTAC-4040",

    "status": 404,

    "title": "Not Found",

    "detail": "The file not found in ADLS",

    "additionalDetails": {

        "requestId": null

    }

• This api https://platform.adobe.io:443/data/foundation/export/files/<dataSetFileId>/?path=<FilePath>.parquet shows unreadable content in POSTMAN in .parquet format. How we can download these content in csv/json format in separate file as a response.

Thanks.

[_Manoj_Kumar_]

Hello @vikash4 

According to this document. We can get maximum of 100 files in a single API request and there is no other way mentioned.

Following this thread, to understand if there is a better way to export the data.

[vikramrai]

What type of data can be accessed ? is it possible to download or view customer or PII data?

[Anil_Umachigi]

Also to add, data will only be available in Parquet format and not really JSON/CSV if you go this route. 

To help troubleshoot, could you help us with the intent? why are you trying to download the data from AEP instead of getting it directly from the source ( where you uploaded it from initially)  

[vikash4]

Hi @Anil_Umachigi,

Thanks for your reply. This is an event data that is ingested 4 months back and I believe dataset have some expiry(ttl) on that which cause removal of the dataset from datalake. I did not check expiry for this dataset. I wanted to share the copy of dataset with other teams to do some analysis and I don't have this copy in my system or at the source. So wondering if we can download this in csv or json format for sharing?

Following document mention that we can access and download batch files and I was following the same...

https://experienceleague.adobe.com/docs/experience-platform/data-access/api.html?lang=en

If there is any another way, could you please guide?

Thanks.

[vikash4 · Accepted solution]

Got one workaround to download the dataset content in csv or json format. Not sure if this is standard way but serve my purpose.

I've used Python SDK(DatasetReader), Pandas library in JupyterLab notebook to read & export the content in csv & json format. This was very quick.

from platform_sdk.dataset_reader import DatasetReader
import pandas as pd

dataset_reader = DatasetReader(get_platform_sdk_client_context(), dataset_id="<datasetid>")
df = dataset_reader.limit(200000).read()
df.to_csv("eventData.csv",sep=',',encoding='utf-8',index=False,header=True)
df.to_json("eventData.json",orient='records',lines=True)

For 200K records, it takes 1.5GB memory for read and export operation.

If you've large dataset and limited memory, try using "offset()" with "limit()" to avoid delay or crash.

df = dataset_reader.limit(50000).offset(1).read()

Thanks.

[vikramrai]

I have a question regarding - What kind of data is retrievable ? is it possible to download or view customer or PII data and any other information you can provide on type of data and an example will be helpful 

[makzare1463]

Create new database, download or export any dataset into CSV format. And upload to new database 

[ChetanyaJain]

Hi @vikash4 ,

You can export the datasets using the new SFTP connector (beta) - https://experienceleague.adobe.com/docs/experience-platform/destinations/catalog/cloud-storage/sftp.... in csv/json format easily.

https://experienceleague.adobe.com/docs/experience-platform/assets/select-datatype-datasets.png?lang... 

Another reference: https://experienceleague.adobe.com/docs/experience-platform/destinations/ui/activate/export-datasets... 

Regards,

Chetanya Jain