2
votes

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'

1
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

1 Answers

0
votes

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.