Query service - Access system-generated values

[filouterrible]

Hi, 

I'm trying to query a dataset using the query service. I would like to query only the latest batch data received. 

We are using a fully custom schema (_tenant...).

I've tried accessing the system-generated such as createdByBatchID, but it seems that it is not accessible in the dataset.

ex.

SELECT createdByBatchID  

FROM <dataset>

Do I have to activate it some ways or is it automatically available following a batch ingestion?

Any leads on how I could query based on latest batch would be great.

Thanks 

[brekrut]

The batch_id generated from the system is a text generated value, I would not recommend using this to look for a batch which has been ingested.  I would recommend using the rank function and partition by fields to look at data which has been ingested over times within the datalake.

rank() OVER(PARTITION BY <field1> order by <field>)

[filouterrible]

@brekrut in order to rank something I have to have a field to rank it over. My question is how can I access such value. Either something as Batch_id or CreatedDate that would allow me to distinguish one batch from another. I do not have access to such field at the moment using the query service.

Thanks 

[brekrut · Accepted solution]

When ingesting data into a dataSet which is not timebase I would recommend placing a timestamp field upon the schema.  This will allow you to rank over agains the dataSet.  

[brekrut]

_acp_batchid will allow you to identify a specific batch which has been ingested in to the AEP datalake, but this is a string value and it is not sequential.

[maggod]

Have you tried this?

select _ACP_BATCHID .... 

We are filling the field

_repo.modifyDate

during ingestion process to have a timestamp available.

[NimashaJain]

Did you find the suggested solutions helpful? It would be great if you can  mark the answer as correct for posterity. If you have found out solution yourself, share it with wider audience in the community.