selecting data from an external source (azure storage blob) using external table/Polybase in SQL. Table created but no data returned

Question

I have a "|" delimited file on AZURE storage blob and I am trying to create an external table to select from this data. I have followed step by step the instructions here: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-ver15 I believe all server settings are correct. I can actually create the table, but when I select from it (from the external tables section under tables) no data returns. Below is my select statement, I have trimmed the fields in the interest of saving space.

CREATE EXTERNAL TABLE [dbo].[customer]
(
    [Id] [int] NOT NULL,
    [CustomerName] [varchar](255) NULL,
    [AccountType] [varchar](255) NULL,
    [State] [varchar](255) NULL,
    [DateExtracted] [date] NULL,
    [tablename][varchar](50) NULL,
    [databaseName] [varchar](50) NULL
)   
WITH (LOCATION='customerfiles/',
      DATA_SOURCE = AzureStorage,  
      FILE_FORMAT = TextFileFormat  
);

Source in azure is as follows: tmmdevstorage - container > customerfiles > file1.txt , file2.txt I suspect i am getting something wrong referencing the location. I get no errors creating the external table and none when selecting from it, just no data. there are files containing the data in the blob storage.

Any ideas as to what i am missing?

I think I have some more information on this, I believe my issue is somewhere in the AZURE AD settings. I think I need to find the following: CREATE DATABASE SCOPED CREDENTIAL ADLSCredential WITH IDENTITY = 'user', SECRET = '57r+LauBsO1eG3tP8iiw6rz1Qdupw36tydpFVokcVjaOj1xnAhpefnus6UHKPA3ijV1VB6EIyBoCTgA72ltN1g==' ; CREATE DATABASE SCOPED CREDENTIAL ADLSCredential WITH IDENTITY = '536540b4-4239-45fe-b9a3-629f97591c0c@login.microsoftonline.com/42f988bf-85f1-41af-91ab-2d2cd011da47/…', SECRET = 'BjdIlmtKp4Fpyh9hIvr8HJlUida/seM5kQ3EpLAmeDI=' — Cascone

Niels Berglund Niels Berglund · Accepted Answer · 2020-01-25T04:38:57

You are correct in what you say in your comment above: when accessing blob store from an external table, you need a credential against the blob store. In addition the credential need to be secured with a database master key. Look at this link, which explains it really well.

When you create the identity you set the IDENTITY and the SECRET parameters. For blob store you can set the IDENTITY to whatever string you want, I always use user.

You can find the SECRET in the Azure Portal if you browse to your storage account. It is under Access keys in the left hand menu:

Hope this helps.

selecting data from an external source (azure storage blob) using external table/Polybase in SQL. Table created but no data returned

2 Answers