Can anyone help me with this error

Question

I am trying to fetch some data from azure data lake to azure datawarehouse, but I am unable to do it I have followed the documentation link

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-load-from-azure-data-lake-store

But I am getting this error when I am trying to create an external table, I have created another web/api app but still was not able to access thE application here is the error which I am facing

EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_IsDirExist. Java exception message:
GETFILESTATUS failed with error 0x83090aa2 (Forbidden. ACL verification failed. Either the resource does not exist or the user is not authorized to perform the requested operation.). [0ec4b8e0-b16d-470e-9c98-37818176a188][2017-08-14T02:30:58.9795172-07:00]: Error [GETFILESTATUS failed with error 0x83090aa2 (Forbidden. ACL verification failed. Either the resource does not exist or the user is not authorized to perform the requested operation.). [0ec4b8e0-b16d-470e-9c98-37818176a188][2017-08-14T02:30:58.9795172-07:00]] occurred while accessing external file.'

Here is the script which I am trying to get it to work with

CREATE DATABASE SCOPED CREDENTIAL ADLCredential2
WITH
    IDENTITY = '2ec11315-5a30-4bea-9428-e511bf3fa8a1@https://login.microsoftonline.com/24708086-c2ce-4b77-8d61-7e6fe8303971/oauth2/token',
    SECRET = '3Htr2au0b0wvmb3bwzv1FekK88YQYZCUrJy7OB3NzYs='
;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore11
WITH (
    TYPE = HADOOP,
    LOCATION = 'adl://test.azuredatalakestore.net/',
    CREDENTIAL = ADLCredential2
);

CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH
(   FORMAT_TYPE = DELIMITEDTEXT
,    FORMAT_OPTIONS    (   FIELD_TERMINATOR = '|'
                    ,    DATE_FORMAT         = 'yyyy-MM-dd HH:mm:ss.fff'
                    ,    USE_TYPE_DEFAULT = FALSE
                    )
);

CREATE EXTERNAL TABLE [extccsm].[external_medication] 
(
   person_id              varchar(4000),
   encounter_id           varchar(4000),
   fin                    varchar(4000),
   mrn                    varchar(4000),
   icd_code               varchar(4000),
   icd_description        varchar(300),
   priority               integer,
   optional1              varchar(4000),
   optional2              varchar(4000),
   optional3              varchar(4000),
   load_identifier        varchar(4000),
   upload_time            datetime2,
   xx_person_id           varchar(4000),--Person ID is the ID that we will use to represent the person through out the process uniquely, This requires initial analysis to determine how to set it
   xx_encounter_id        varchar(4000),--Encounter ID is the ID that will represent the encounter uniquely through out the process, This requires initial analysis to determine hos to set it based on client data
   mod_optional1      varchar(4000),
   mod_optional2      varchar(4000),
   mod_optional3      varchar(4000),
   mod_optional4      varchar(4000),
   mod_optional5      varchar(4000),
   mod_loadidentifier     datetime2 
)
WITH 
(
    LOCATION='\testfiles\procedure_azure.txt000\',
    DATA_SOURCE = AzureDataLakeStore11,   --DATA SOURCE THE BLOB STORAGE
    FILE_FORMAT = TextFileFormat,   --TYPE OF FILE FORMAT
    REJECT_TYPE = percentage,
    REJECT_VALUE = 1,
    REJECT_SAMPLE_VALUE = 0
);

Please tell me whats wrong here?

No, I am not loggin with active directory, but my datawarehouse sql server has a active directory admin — dead mah
@user1408786 I have configured active driectory applications as it was required in the doumentation. — dead mah
Have you done the credential bit to create the secret? It's subtly linked to in the document near the bit that says "you must first create an Azure Active Directory Application,": docs.microsoft.com/en-us/azure/data-lake-store/… — wBob

wBob wBob · Accepted Answer · 2017-08-14T23:17:53

I can reproduce this but it's hard to narrow down exactly. I think it's to do with permissions. From the Azure portal:

Data Lake Store > yourDataLakeAccount > your folder > Access

From there, make sure your AD Application has Read, Write and Execute permission on the relevant files / folders. Start with one file initially. I can reproduce the error by assigning / unassigning the Execute permissions but need to repeat the steps to confirm. I'll retrace my steps but for now concentrate your search here. In my example below, my Azure Active Directory Application is called adwAndPolybase; you can see I've given it Read, Write and Execute. I also experimented with the Advanced and 'Apply to children' options:

Can anyone help me with this error

1 Answers