Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,338 questions
Polybase folder or wildcard query returns no data on SQL 2019
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(105.60000000000001, 31%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESQ%3C/text%3E%3C/svg%3E)
Stefan Quinn
1
Reputation point
According to point 5 of the documentation here https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-azure-blob-storage?view=sql-server-ver15&WT.mc_id=Portal-Microsoft_Azure_Support
it suggests that querying folders works. Naturally all of the files would need to have the same structure.
We have Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64) Standard Edition (64-bit) on Windows Server 2022 Datacenter 10.0 <X64> (Build 20348: ) (Hypervisor)
on an Azure VM and are querying files on an Azure StorageV2 (general purpose v2). We can successfully create and fetch data from a single file but not from a folder using a wildcard as proposed by the Microsoft d365 team.
When we attempt to query a folder with a wildcard i.e. "/folderName/*.csv" or "/folderName/" no data is returned but querying a single file works as expected.
External data source: TYPE = HADOOP, LOCATION = 'abfss://....
Single file works: LOCATION = 'Tables/test1.csv'
Wildcard doesn't work: LOCATION ='Tables/*.csv'
Any ideas would be appreciated. Apparently this method works with Synapse and SQL 2022...
cheers
UPDATE
I've managed to get the folder query to work but still stuck on the wildcard search.
I opened a ticket with Microsoft and they have said that the External Data Source abfss location can be problematic and that it should not have a folder name in it so you should use:
container@yourlake@wrqr .core.windows.net and NOT container@yourlake@wrqr .core.windows.net/folderName
{count} votes
-
Erland Sommarskog 100.9K Reputation points MVP2022-10-01T09:17:20.32+00:00 It could be that this is a new feature in SQL 2022, but I don't know.
But some notes on your post. Point 5 in the link you have, does not seem to use wildcard. In fact, I don't could not find anything about wildcards on that page.
Also, your post is victim to a small formatting accident. The posting editor is not an WYSIWYG editor, but uses Markdown, a light markup language. If you enclose a piece of text in asterisk, the text will appear with italics, and the asterisks will be gone. This makes it a little difficult to read your post. If you want to retain the asterisks, select the text and press Ctrl-K, which will apply backticks, and the text will come out
*shaded*as in this example.
Sign in to comment