Exam DP-203 topic 2 question 127 discussion

correct: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-specific-files

wrong! r.filepath() : returns full path ( /year=2022/month=10/day=01/file1.csv ) r.filepath(1) : returns year (2022) r.filepath(2) : returns month which we need.

Correct Answer My reason: from the script provided, particularly from here "From OPENROWSET ( BULK 'csv/system1/2022/*/*.csv', The search on the file path uses a wildcard that begins after the year 2022 (i.e. the asterisks) and the first parameter is the month after 2022. Hence, r.filepath(1) is correct.

ChatGPT: Given the typical folder structure in your case, which is csv/system1/{year}/{month}/{filename}.csv, you need to access specific segments of this path to filter data by month: r.filepath(0) would return the first segment after the initial folder, likely csv. r.filepath(1) would then return system1. r.filepath(2) refers to the next segment, which, according to your folder structure, represents the year (2022). So answer is r.filepath(2).

Correct answer

r.filepath(2) rerurn year (2022)

Correct. The term "first wildcard" refers to the asterisk that represents the month in the file path placeholder. For this specific example.

Answers are correct: As per next example, parameter (1) refers to year, and paremeter (2) to month. SELECT r.filepath() AS filepath ,r.filepath(1) AS [year] ,r.filepath(2) AS [month] ,COUNT_BIG(*) AS [rows] FROM OPENROWSET( BULK 'csv/taxi/yellow_tripdata_*-*.csv', DATA_SOURCE = 'SqlOnDemandDemo', FORMAT = 'CSV', PARSER_VERSION = '2.0', FIRSTROW = 2 ) WITH ( vendor_id INT ) AS [r] WHERE r.filepath(1) IN ('2017') AND r.filepath(2) IN ('10', '11', '12') GROUP BY r.filepath() ,r.filepath(1) ,r.filepath(2) ORDER BY filepath;

Second one is r.filepath(2) asper: When called without parameter, returns the full file path that a row originates from. When called with parameter, it returns part of path that matches the wildcard on position specified in the parameter. For example, parameter value 1 would return part of path that matches the first wildcard.