Working on a project where I have to bring data from ADLS to Synapse. I am planning to use Polybase so that I can create external tables based on CSV files from ADLS and avoid physical data movement to save time and money.
On ADLS, I may have multiple files with the same name but with a date suffix like Sales_01_Oct_2021.CSV, Sales_02_Oct_2021.CSV etc. These files are having one-day data in them. I want to create an external table called External.Sales and this table should have data from all the files starting with Sales. Is this possible..?
-- Create a temp table to hold the imported data
CREATE EXTERNAL TABLE dbo.FIPSLOOKUP_EXT (
UID INT NOT NULL,
iso2 VARCHAR(2) NULL,
iso3 VARCHAR(3) NULL,
code3 INT NULL,
FIPS INT NULL,
Admin2 VARCHAR(255) NULL,
provincestate VARCHAR(255) NULL,
countryregion VARCHAR(255) NULL,
latitude DECIMAL(12,9) NULL,
longitude DECIMAL(12,9) NULL,
combined_key VARCHAR(255) NULL,
population INT NULL