SQL Server Polybase with multiple files

Question

I want to use PolyBase to read a directory of csv or xlsx files with similar schemas but different file names. File names has pattern such 'subjectXYZ_yyyy-mm-dd'. The files are added daily and I don't want to create an External Table per file. How I should set ODBC DSN and/or PolyBase DataSource/External Tables parameters for this?

Answer 1

Polybase / External tables support either single file names or folders in the LOCATION argument, but the files must be the same structure. A simple example using CETAS (but the principle is the same):

CREATE EXTERNAL TABLE ext.lineitem_1995
WITH (
    LOCATION = 'enriched/tpch/tpch10/lineitem_partitioned/1995',
    DATA_SOURCE = [MyDataSource],
    FILE_FORMAT = [ParquetFF]
) AS
SELECT *
FROM dbo.lineitem
WHERE YEAR(l_shipdate) = 1995;