CODEWITHSUNDEEP
azure-data-lakeu-sql
John
John

Reputation: 3

USQL Extract subset of files

I have a USQL question. I have a daily job that is outputting files to a directory in the following format

/MyOutput/{YYYY}/{MM}/{DD}/file.csv

I have a second job now that I want to run that will use the most recent 30 files produced by the first job. I can't figure out how to best do this though.

I know I can do wildcards in the extractor but I prefer not to extract all files and then use a select/where to remove the ones I don't want as extracting all files could get really costly if I'm keeping years worth of these files.

So is there a nice way in usql to say extract only the most recent x files? Or what options do I have here?

Thanks, John

Upvotes: 0

Views: 123

Answers (1)

Dave Bending
Dave Bending

Reputation: 171

If you use a date pattern it will do what you want.

@rows = 
    EXTRACT 
      ...,
      date DateTime
    FROM /MyOutput/{date:YYYY}/{date:MM}/{date:dd}/file.csv;

SELECT * FROM @rows WHERE date > '2018-5-3'

Will read only the files matching the date range - it won't read all of them in first.

Upvotes: 1

Related Questions