Access Data from Azure Data Lake Store using Polybase with Azure Data Warehouse

Question

I get a error when create external table

https://exoticbaryon.anset.org/2017/06/26/access-data-from-azure-data-lake-store-using-polybase-with-azure-data-warehouse/#comment-157

CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'xxxxx' 

CREATE DATABASE SCOPED CREDENTIAL ADLUser 
WITH IDENTITY =         xxxxx@/https://login.microsoftonline.com/xxxxx/oauth2/v2.0/token',
SECRET = xxxxx' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (TYPE = HADOOP,
  CREDENTIAL = ADLUser,
  LOCATION = N'adl://xxxxx.azuredatalakestore.net'
)


CREATE EXTERNAL FILE FORMAT TextFileFormat 
WITH ( 
   FORMAT_TYPE = DELIMITEDTEXT, 
   FORMAT_OPTIONS (FIELD_TERMINATOR =',',
                   STRING_DELIMITER = '"', 
                   USE_TYPE_DEFAULT = TRUE)
);


CREATE EXTERNAL TABLE [dbo].[xxxxx_external](
[EventMonth] [nvarchar](10) NULL,
[UserCount] [bigint] NULL,
[UserType] [nchar](8) NULL,
[StageType] [bigint] NULL,
[StageName] [nvarchar](9) NULL) 
WITH
(
LOCATION=N'/test/xxxxx.csv', 
DATA_SOURCE = AzureDataLakeStore , 
FILE_FORMAT = TextFileFormat 
) ;

CREATE TABLE [dbo].[xxxxx] 
WITH (DISTRIBUTION = HASH([EventMonth] ) ) 
AS SELECT * FROM 
[dbo].[xxxxx_external] ;

When run CREATE EXTERNAL TABLE Failed to execute query. Error: External file access failed due to internal error: 'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_IsDirExist. Java exception message:

HdfsBridge::isDirExist - Unexpected error encountered checking whether directory exists or not: MalformedURLException: no protocol: /https://login.microsoftonline.com/72f988bf-86f1-41af-91ab-2d7cd011db47/oauth2/v2.0/token'

May I confirm that you're using Azure Data Lake STORE, and not Azure Data Lake STORAGE? The naming is similar, and can be confusing. — Ron Dunn

Jagadish Chigurupati Jagadish Chigurupati · Accepted Answer · 2020-01-30T07:39:29

You have to modify you external Data Source to the similar format

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION                  = '<prefix>://<path>[:<port>]'
[,   CONNECTION_OPTIONS        = '<name_value_pairs>']
[,   CREDENTIAL                = <credential_name> ]
[,   PUSHDOWN                  = ON | OFF]
[,   TYPE                      = HADOOP | BLOB_STORAGE ]
[,   RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]'
)
[;]

You can find more info in the following link :https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15

As you are accessing Azure Data Lake you need to mention your prefix with 'wasbs' For first time try uploading a single file in your folder container and donot mention any .csv file name and load into external tables. Later you can mention your specific filename and test your code.

Access Data from Azure Data Lake Store using Polybase with Azure Data Warehouse

1 Answers