Home
%3CLINGO-SUB%20id%3D%22lingo-sub-795170%22%20slang%3D%22en-US%22%3EPolybase%20User%20Guide%20-%20Part%202%20%3A%20Unrestriced%20Storage%20the%20original%20Configuration%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795170%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20Polybase%20was%20made%20available%20for%20use%20in%20Azure%20Microsoft%20provided%202%20schema's%20which%20allowed%20you%20to%20connect%20to%20Blob%20Store%20via%20WASBS%20and%20Azure%20Data%20Lake%20via%20ADL.%20Authentication%20for%20Blob%20Store%20is%20Storage%20Account%20Key%20based%20and%20ADL%20makes%20use%20of%20Azure%20AD%20Application%20Service%20Principle%20both%20of%20which%20will%20only%20allow%20you%20to%20connect%20to%20storage%20which%20has%20no%20network%20restrictions%20in%20place%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Azure%20Storage%20Account%20V2%20we've%20introduced%20a%20new%20Schema%20called%20ABFSS%20which%20allows%20you%20to%20connect%20to%20both%20secured%20and%20unsecured%20storage.%20The%20following%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fstorage%2Fcommon%2Fstorage-account-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ELink%26nbsp%3B%3C%2FA%3Eprovides%20additional%20information%20on%20Azure%20Storage%20Accounts.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20implementation%20is%20ideal%20for%20connecting%20to%20external%20storage%20with%20the%20least%20amount%20of%20effort%20and%20configuration%20at%20DW%20level%20and%20one%20can%20be%20up%20and%20running%20in%20a%20few%20minutes%20ready%20to%20import%20files%20from%20your%20storage%20account.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Data-Warehouse-Support%2FPolybase-User-Guide-Part-1-Secured-Storage-Account%2Fba-p%2F740376%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3EPart%201%3C%2FA%3Ewe%20reviewed%20the%20Secured%20Storage%20Account%20configuration%20with%20Polybase.%3C%2FP%3E%0A%3CP%3EIn%20Part%203%20we%20will%20review%20Integration%20tools%20which%20make%20use%20of%20Polybase%20to%20load%20data%20(ADF%2FDatabricks)%3C%2FP%3E%0A%3CP%3EIn%20Part%204%20we%20will%20review%20how%20to%20modify%20your%20existing%20Polybase%20configuration%20without%20recreation%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ERequirements%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3ENo%20Firewall%20enabled%20at%20a%20Storage%20Account%20level%20and%20Allow%20All%20Networks%20is%20Enabled.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20making%20use%20of%20ADL%20schema%20the%20Storage%20account%20has%20to%20be%20Gen1%20Azure%20Data%20Lake.%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20on%20Azure%20Storage%20Account%20V2%20you%20have%20to%20make%20use%20of%20the%20ABFSS%20Schema.%3C%2FP%3E%0A%3CP%3ECurrently%20SAS%20tokens%20are%20unsupported%20with%20External%20Data%20Source%26nbsp%3BType%20%3D%20HADOOP%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EDatabase%20Security%20Consideration%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBefore%20we%20proceed%20further%2C%20we%20need%20to%20address%20the%20permissions%20a%20user%20requires%20to%20create%20all%20the%20required%20objects.%26nbsp%3BTo%20make%20this%20easier%20and%20more%20manageable%20I%20would%20suggest%20creating%20a%20role%20within%20your%20Data%20Warehouse%20and%20making%20users%20role%20members%20which%20would%20avoid%20having%20to%20grant%20and%20revoke%20permissions%20individually%20as%20users%20are%20added%20or%20removed.%26nbsp%3B%20The%20permissions%20which%20are%20required%20are%20elevated%20and%20therefore%20I%20would%20advise%20that%20only%20Database%20Administrators%2C%20Senior%20Project%20Members%20or%20knowledgeable%20users%20are%20granted%20these%20permissions.%3C%2FP%3E%0A%3CP%3EBelow%20are%20all%20the%20permissions%20you%20will%20require%20within%20the%20database%2C%20the%20script%20will%20create%20the%20role%2C%20assign%20the%20permissions%20to%20the%20role%20and%20add%20the%20role%20membership%20to%20the%20designated%20user%20account.%20Specify%20required%20Data%20Warehouse%20DB%20name%20and%20User%20account.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreate%20Role%20Polybase_Users%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGrant%20CREATE%20TABLE%20TO%20Polybase_Users%3C%2FP%3E%0A%3CP%3EGrant%20ALTER%20ANY%20SCHEMA%20TO%20Polybase_Users%3C%2FP%3E%0A%3CP%3EGrant%20ALTER%20ANY%20EXTERNAL%20DATA%20SOURCE%20TO%20Polybase_Users%3C%2FP%3E%0A%3CP%3EGrant%20ALTER%20ANY%20EXTERNAL%20FILE%20FORMAT%20TO%20Polybase_Users%3C%2FP%3E%0A%3CP%3EGrant%20CONTROL%20ON%20DATABASE%3A%3A%5B%5D%20TO%20Polybase_Users%3C%2FP%3E%0A%3CP%3Esp_addrolemember%20Polybase_Users%20%2C%20%3CYOURUSERNAME%3E%3C%2FYOURUSERNAME%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EStep%201%20-%20Obtain%20Storage%20Account%20Key%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThe%20Storage%20account%20Key%20is%20obtained%20from%20the%20Azure%20Portal%2C%20browse%20to%20the%20Target%20Storage%20Account%20resource%2C%20select%20the%20Access%20Key%20Blade%20and%20copy%20an%20Access%20Key%20from%20the%20window.%20%3CEM%3E(When%20you%20refresh%20or%20recycle%20the%20Keys%20subsequent%20Authentication%20will%20fail.%20In%20the%20event%20of%20you%20refreshing%20the%20keys%20update%20the%20Database%20Scoped%20Credential%20with%20the%20new%20Key%20Secret)%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126190i92608DC5278105E5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22storage.png%22%20title%3D%22storage.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EStep%202%3C%2FSTRONG%3E-%26nbsp%3B%3CSTRONG%3EDatabase%20Credential%20Configuration%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20next%20step%20is%20to%20configure%20the%20database%20credentials%20which%20will%20be%20used%20by%20your%20External%20Data%20Source%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20never%20created%20any%20external%20objects%20and%20this%20is%20the%20first%20time%20you%20are%20configuring%20Polybase%20you%20have%20to%20start%20out%20by%20creating%20a%20Master%20Key%20within%20your%20database.%20(%3CEM%3EAzure%20Data%20Warehouse%20does%20not%20require%20a%20password%20to%20be%20specified%20for%20the%20Master%20Key.%20If%20you%20make%20use%20of%20a%20password%2C%20take%20record%20of%20the%20password%3C%2FEM%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECREATE%20MASTER%20KEY%20%5BENCRYPTION%20BY%20PASSWORD%20%3D%20'somepassword'%5D%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20the%20key%20is%20created%20proceed%20with%20creating%20the%20credentials%20which%20will%20refer%20to%20the%20Storage%20Account%20Key%20which%20you%20obtained%20from%20the%20previous%20step.%20The%20Storage%20Account%20Key%20will%20be%20stored%20as%20the%20Secret%20for%20the%20Database%20Credential.%26nbsp%3B%20The%20Identity%20can%20be%20any%20text%20Except%20an%20OAUTH%202.0%20string%20when%20using%20WASBS%20Schema.%20OAUTH2.0%20is%20only%20supported%20with%20ADL%20Schema%20on%20Gen1%20and%20ABFSS%20Schema%20on%20Azure%20Storage%20Gen%202%20for%20Data%20Lake.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E2.a.%20Blob%20Store%20Credential%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECREATE%20DATABASE%20SCOPED%20CREDENTIAL%20%3CYOUR_CREDENTIAL_NAME%3E%3C%2FYOUR_CREDENTIAL_NAME%3E%3C%2FP%3E%0A%3CP%3EWITH%20IDENTITY%20%3D%20'%3CYOUR%20account%3D%22%22%20name%3D%22%22%3E'%2C%3C%2FYOUR%3E%3C%2FP%3E%0A%3CP%3ESECRET%20%3D%26nbsp%3B'%3CAZURE_STORAGE_ACCOUNT_KEY%3E'%3C%2FAZURE_STORAGE_ACCOUNT_KEY%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E2.c.%20Azure%20Data%20Lake%20Gen1%20and%20Gen%202%20Credentials%20(OAuth%202.0%20is%20now%20supported%20on%20Gen%202)%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3BCREATE%20DATABASE%20SCOPED%20CREDENTIAL%20%3CYOUR_CREDENTIAL_NAME%3E%3C%2FYOUR_CREDENTIAL_NAME%3E%3C%2FP%3E%0A%3CP%3EWITH%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIDENTITY%20%3D%20'%3CCLIENT_ID%3E%40%5C%3COAUTH_2.0_TOKEN_ENDPOINT%3E'%2C%26nbsp%3B%3C%2FOAUTH_2.0_TOKEN_ENDPOINT%3E%3C%2FCLIENT_ID%3E%3C%2FP%3E%0A%3CP%3ESECRET%20%3D%20'%3CKEY%3E'%3C%2FKEY%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3BAdditional%20information%20on%20how%20to%20create%20the%20OAuth%202.0%20Credentials%20can%20be%20found%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-lake-store%2Fdata-lake-store-service-to-service-authenticate-using-active-directory%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3EFor%20additional%20information%20review%20the%20documentation%20on%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fcreate-database-scoped-credential-transact-sql%3Fview%3Dazure-sqldw-latest%23c-creating-a-database-scoped-credential-for-polybase-connectivity-to-azure-data-lake-store%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EDatabase%20Scoped%20Credentials%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EStep%203%20-%20Create%20External%20Data%20Source%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWhen%20creating%20the%20External%20Data%20Source%2C%20obtain%20the%20Storage%20Account%20Name%20which%20you%20are%20connecting%20to%20as%20well%20as%20the%20Name%20of%20the%20Container%20OR%20the%20ADL%20Account%20Name%20when%20connecting%20to%20Data%20Lake%20Gen%201.%26nbsp%3B%20The%20following%20Schema%20and%20Paths%20should%20be%20used%20when%20connecting%20to%20storage%20as%20explained%20in%20our%20documentation%20on%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fcreate-external-data-source-transact-sql%3Fview%3Dazure-sqldw-latest%23arguments-2%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EExternal%20Data%20Sources%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20width%3D%22105%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2220%25%22%3E%3CP%3EData%20Source%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2220%25%22%3E%3CP%3ELocation%20Prefix%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2258%25%22%3E%3CP%3EPath%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2220%25%22%3E%3CP%3EAzure%20Blob%20Storage%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2220%25%22%3E%3CP%3Ewasb%5Bs%5D%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2258%25%22%3E%3CP%3E%3CCONTAINER%3E%40%3CSTORAGE_ACCOUNT%3E.blob.core.windows.net%3C%2FSTORAGE_ACCOUNT%3E%3C%2FCONTAINER%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2220%25%22%3E%3CP%3EData%20Lake%20Gen%201%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2220%25%22%3E%3CP%3Eadl%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2258%25%22%3E%3CP%3E%3CSTORAGE_ACCOUNT%3E.azuredatalake.net%3C%2FSTORAGE_ACCOUNT%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2220%25%22%3E%3CP%3EStorage%20Account%20V2%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2220%25%22%3E%3CP%3Eabfss%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2258%25%22%3E%3CP%3E%3CCONTAINER%3E%40%3CSTORAGE_ACCOUNT%3E.dfs.core.windows.net%3C%2FSTORAGE_ACCOUNT%3E%3C%2FCONTAINER%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E3.a.%20Blob%20Store%20Connection%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWhen%20connecting%20to%20a%20blob%20store%20you%20will%20have%20to%20create%20it%20as%20follows%3C%2FP%3E%0A%3CP%3E%26nbsp%3BCREATE%20EXTERNAL%20DATA%20SOURCE%20%3CMY_DATA_SOURCE_NAME%3E%3C%2FMY_DATA_SOURCE_NAME%3E%3C%2FP%3E%0A%3CP%3EWITH%20(%20LOCATION%20%3D%20'wasbs%3A%2F%2F%3CMY_CONTAINER%3E%40%3CSTORAGE_ACCOUNT%3E.core.windows.net%2F'%20%2C%20CREDENTIAL%20%3D%20mycredential%20%2C%3C%2FSTORAGE_ACCOUNT%3E%3C%2FMY_CONTAINER%3E%3C%2FP%3E%0A%3CP%3ETYPE%20%3D%20HADOOP%20)%20%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E3.b.%20Data%20Lake%20Gen%201%20Connection%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWhen%20connecting%20to%20ADLS%20Storage%20on%20Gen1%26nbsp%3Byou%20will%20have%20to%20create%20it%20as%20follows%3C%2FP%3E%0A%3CP%3ECREATE%20EXTERNAL%20DATA%20SOURCE%20%3CMY_DATA_SOURCE_NAME%3E%3C%2FMY_DATA_SOURCE_NAME%3E%3C%2FP%3E%0A%3CP%3EWITH%20(%20LOCATION%20%3D%20'adl%3A%2F%2Fstorageaccount.azuredatalakestore.net'%20%2C%3C%2FP%3E%0A%3CP%3ECREDENTIAL%20%3D%20%3CYOUR_CREDENTIAL_NAME%3E%2C%3C%2FYOUR_CREDENTIAL_NAME%3E%3C%2FP%3E%0A%3CP%3ETYPE%20%3D%20HADOOP%20)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E3.c.%20Storage%20Account%20V2%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWhen%20connecting%20to%20Storage%20on%20Gen2%20Storage%20Account%20you%20will%20have%20to%20create%20it%20as%20follows%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECREATE%20EXTERNAL%20DATA%20SOURCE%20%3CMY_DATA_SOURCE_NAME%3E%3C%2FMY_DATA_SOURCE_NAME%3E%3C%2FP%3E%0A%3CP%3EWITH%20(%20LOCATION%20%3D%20'abfss%3A%2F%2F%3CMY_CONTAINER%3E%40%3CSTORAGE_ACCOUNT%3E.dfs.core.windows.net'%3C%2FSTORAGE_ACCOUNT%3E%3C%2FMY_CONTAINER%3E%3C%2FP%3E%0A%3CP%3ECREDENTIAL%20%3D%20%3CYOUR_CREDENTIAL_NAME%3E%2C%3C%2FYOUR_CREDENTIAL_NAME%3E%3C%2FP%3E%0A%3CP%3ETYPE%20%3D%20HADOOP%20)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EStep%204%20%E2%80%93%20Create%20the%20External%20file%20format%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWhen%20connecting%20to%20files%20stored%20on%20an%20external%20location%20we%20need%20to%20specify%20what%20the%20file%20looks%20like%20in%20the%20terms%20of%20row%20%2F%20column%20delimiters%2C%20specific%20date%20time%20formats%20I%20would%20require%20to%20persist%20and%20what%20type%20of%20file%20the%20source%20file%20is.%26nbsp%3B%20Additional%20Info%20on%20this%20can%20be%20found%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fcreate-external-file-format-transact-sql%3Fview%3Dsql-server-2017%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20example%20I%20will%20create%20a%20file%20format%20that%20references%20a%20Delimited%20Text%20file%20which%20is%20comma%20delimited%20and%20has%20no%20table%20header%20in%20the%20file.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECREATE%20EXTERNAL%20FILE%20FORMAT%20NoHeader_CSV%3C%2FP%3E%0A%3CP%3EWITH%20(FORMAT_TYPE%20%3D%20DELIMITEDTEXT%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FORMAT_OPTIONS%20(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20FIELD_TERMINATOR%20%3D%20'%2C'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20STRING_DELIMITER%20%3D%20'%22'%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20USE_TYPE_DEFAULT%20%3D%20True)%3C%2FP%3E%0A%3CP%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EStep%205%20%E2%80%93%20Create%20External%20Table%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThe%20final%20step%20is%20to%20create%20the%20external%20table%20which%20will%20be%20consuming%20the%20credentials%20we%E2%80%99ve%20created%20and%20connecting%20to%20the%20External%20Data%20Source%20that%20has%20been%20provided.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CEM%3EWhen%20creating%20the%20above-mentioned%20objects%20in%20the%20Data%20Warehouse%20we%20are%20only%20validating%20the%20Syntax%20and%20if%20the%20syntax%20is%20valid%20the%20object%20will%20create.%20Therefore%2C%20you%20will%20only%20know%20that%20there%20is%20a%20problem%20once%20you%20create%20your%20External%20Table%20as%20only%20then%20will%20we%20authenticate%20using%20the%20information%20that%20has%20been%20provided.%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECREATE%20EXTERNAL%20TABLE%20%3CYOURTABLENAME%3E%3C%2FYOURTABLENAME%3E%3C%2FP%3E%0A%3CP%3E(%3C%2FP%3E%0A%3CP%3EColumn1%20varchar(50)%20Null%2C%3C%2FP%3E%0A%3CP%3EColumn2%20IDENTITY%2C%3C%2FP%3E%0A%3CP%3EColumn3%20datetime2%2C%3C%2FP%3E%0A%3CP%3E...%3C%2FP%3E%0A%3CP%3E)%3C%2FP%3E%0A%3CP%3EWITH%3C%2FP%3E%0A%3CP%3E(%3C%2FP%3E%0A%3CP%3ELOCATION%20%3D%20'%2Fpath%2Ffilename'%2C--Path%20will%20be%20the%20root%20of%20the%20specified%20container%3C%2FP%3E%0A%3CP%3EDATA_SOURCE%20%3D%20%3CMY_DATA_SOURCE_NAME%3E%20%2C%3C%2FMY_DATA_SOURCE_NAME%3E%3C%2FP%3E%0A%3CP%3EFILE_FORMAT%20%3D%20NoHeader_CSV%3C%2FP%3E%0A%3CP%3E)%20%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20followed%20the%20instructions%20and%20guidelines%20provided%20and%20specified%20a%20valid%20path%20and%20file%20name%2C%20you%20will%20be%20able%20to%20Create%20the%20External%20Table%20successfully%20and%20Select%20from%20the%20object%20without%20failure.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3CSTRONG%3ESolution%20Overview%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20intention%20of%20the%20solution%20is%20to%20allow%20you%20to%20make%20use%20of%20Polybase%20as%20quickly%20as%20possible%20and%20with%20the%20least%20amount%20of%20steps%20to%20connect%20to%20a%20Storage%20Account%20with%20no%20Firewall%20rules%20or%20Network%20restrictions%20in%20place.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EPart%203%20Integration%20tools%20which%20make%20use%20of%20Polybase%20to%20load%20data%20(ADF%2FDatabricks)%20to%20follow%20soon%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-795170%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20Part%202%20of%20this%20Polybase%20User%20Guide%20we%20focus%20on%20the%20original%20WASBS%20and%20ADL%20Schema's%20which%20were%20made%20available%20in%20Polybase%20to%20connect%20to%20Unrestricted%20Storage.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-795170%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EData%20Import%20and%20Export%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPolyBase%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

When Polybase was made available for use in Azure Microsoft provided 2 schema's which allowed you to connect to Blob Store via WASBS and Azure Data Lake via ADL. Authentication for Blob Store is Storage Account Key based and ADL makes use of Azure AD Application Service Principle both of which will only allow you to connect to storage which has no network restrictions in place 

 

In Azure Storage Account V2 we've introduced a new Schema called ABFSS which allows you to connect to both secured and unsecured storage. The following Link provides additional information on Azure Storage Accounts. 

 

The implementation is ideal for connecting to external storage with the least amount of effort and configuration at DW level and one can be up and running in a few minutes ready to import files from your storage account. 

 

In Part 1 we reviewed the Secured Storage Account configuration with Polybase.

In Part 3 we will review Integration tools which make use of Polybase to load data (ADF/Databricks)

In Part 4 we will review how to modify your existing Polybase configuration without recreation

 

 

Requirements

No Firewall enabled at a Storage Account level and Allow All Networks is Enabled. 

If you are making use of ADL schema the Storage account has to be Gen1 Azure Data Lake.

If you are on Azure Storage Account V2 you have to make use of the ABFSS Schema.

Currently SAS tokens are unsupported with External Data Source Type = HADOOP 

 

Database Security Consideration

 

Before we proceed further, we need to address the permissions a user requires to create all the required objects. To make this easier and more manageable I would suggest creating a role within your Data Warehouse and making users role members which would avoid having to grant and revoke permissions individually as users are added or removed.  The permissions which are required are elevated and therefore I would advise that only Database Administrators, Senior Project Members or knowledgeable users are granted these permissions.

Below are all the permissions you will require within the database, the script will create the role, assign the permissions to the role and add the role membership to the designated user account. Specify required Data Warehouse DB name and User account.

 

Create Role Polybase_Users 

Grant CREATE TABLE TO Polybase_Users

Grant ALTER ANY SCHEMA TO Polybase_Users

Grant ALTER ANY EXTERNAL DATA SOURCE TO Polybase_Users

Grant ALTER ANY EXTERNAL FILE FORMAT TO Polybase_Users

Grant CONTROL ON DATABASE::[] TO Polybase_Users

sp_addrolemember Polybase_Users , <yourUsername>

 

Step 1 - Obtain Storage Account Key 

The Storage account Key is obtained from the Azure Portal, browse to the Target Storage Account resource, select the Access Key Blade and copy an Access Key from the window. (When you refresh or recycle the Keys subsequent Authentication will fail. In the event of you refreshing the keys update the Database Scoped Credential with the new Key Secret) 

storage.png

 

Step 2Database Credential Configuration

 

The next step is to configure the database credentials which will be used by your External Data Source

If you have never created any external objects and this is the first time you are configuring Polybase you have to start out by creating a Master Key within your database. (Azure Data Warehouse does not require a password to be specified for the Master Key. If you make use of a password, take record of the password)

 

CREATE MASTER KEY [ENCRYPTION BY PASSWORD = 'somepassword'];

 

Once the key is created proceed with creating the credentials which will refer to the Storage Account Key which you obtained from the previous step. The Storage Account Key will be stored as the Secret for the Database Credential.  The Identity can be any text Except an OAUTH 2.0 string when using WASBS Schema. OAUTH2.0 is only supported with ADL Schema on Gen1 and ABFSS Schema on Azure Storage Gen 2 for Data Lake. 

 

2.a. Blob Store Credential 

 

CREATE DATABASE SCOPED CREDENTIAL <Your_Credential_Name>

WITH IDENTITY = '<Your Account Name>',

SECRET = '<azure_storage_account_key>'

 

2.c. Azure Data Lake Gen1 and Gen 2 Credentials (OAuth 2.0 is now supported on Gen 2)

 CREATE DATABASE SCOPED CREDENTIAL <Your_Credential_Name>

WITH 

IDENTITY = '<client_id>@\<OAuth_2.0_Token_EndPoint>', 

SECRET = '<key>'

 Additional information on how to create the OAuth 2.0 Credentials can be found here.

For additional information review the documentation on Database Scoped Credentials.

 

Step 3 - Create External Data Source

When creating the External Data Source, obtain the Storage Account Name which you are connecting to as well as the Name of the Container OR the ADL Account Name when connecting to Data Lake Gen 1.  The following Schema and Paths should be used when connecting to storage as explained in our documentation on External Data Sources.

 

Data Source

Location Prefix

Path

Azure Blob Storage

wasb[s]

<container>@<storage_account>.blob.core.windows.net

Data Lake Gen 1

adl

<storage_account>.azuredatalake.net

Storage Account V2

abfss

<container>@<storage_account>.dfs.core.windows.net

 

3.a. Blob Store Connection

When connecting to a blob store you will have to create it as follows

 CREATE EXTERNAL DATA SOURCE <My_Data_Source_Name>

WITH ( LOCATION = 'wasbs://<My_Container>@<Storage_Account>.core.windows.net/' , CREDENTIAL = mycredential ,

TYPE = HADOOP ) ;

 

3.b. Data Lake Gen 1 Connection

When connecting to ADLS Storage on Gen1 you will have to create it as follows

CREATE EXTERNAL DATA SOURCE <My_Data_Source_Name>

WITH ( LOCATION = 'adl://storageaccount.azuredatalakestore.net' ,

CREDENTIAL = <Your_Credential_Name>,

TYPE = HADOOP )

 

3.c. Storage Account V2

When connecting to Storage on Gen2 Storage Account you will have to create it as follows

 

CREATE EXTERNAL DATA SOURCE <My_Data_Source_Name>

WITH ( LOCATION = 'abfss://<My_Container>@<Storage_Account>.dfs.core.windows.net'

CREDENTIAL = <Your_Credential_Name>,

TYPE = HADOOP )

  

Step 4 – Create the External file format

When connecting to files stored on an external location we need to specify what the file looks like in the terms of row / column delimiters, specific date time formats I would require to persist and what type of file the source file is.  Additional Info on this can be found here

 

In the example I will create a file format that references a Delimited Text file which is comma delimited and has no table header in the file.

 

CREATE EXTERNAL FILE FORMAT NoHeader_CSV

WITH (FORMAT_TYPE = DELIMITEDTEXT,

      FORMAT_OPTIONS (

          FIELD_TERMINATOR = ',',

          STRING_DELIMITER = '"',

          USE_TYPE_DEFAULT = True)

)

 

 

Step 5 – Create External Table

 

The final step is to create the external table which will be consuming the credentials we’ve created and connecting to the External Data Source that has been provided.

 

When creating the above-mentioned objects in the Data Warehouse we are only validating the Syntax and if the syntax is valid the object will create. Therefore, you will only know that there is a problem once you create your External Table as only then will we authenticate using the information that has been provided.

 

CREATE EXTERNAL TABLE <YourTableName>

(

Column1 varchar(50) Null,

Column2 IDENTITY,

Column3 datetime2,

...

)

WITH

(

LOCATION = '/path/filename',--Path will be the root of the specified container

DATA_SOURCE = <My_Data_Source_Name> ,

FILE_FORMAT = NoHeader_CSV

) ;

 

If you followed the instructions and guidelines provided and specified a valid path and file name, you will be able to Create the External Table successfully and Select from the object without failure.  

 

 Solution Overview

 

The intention of the solution is to allow you to make use of Polybase as quickly as possible and with the least amount of steps to connect to a Storage Account with no Firewall rules or Network restrictions in place.

 

Part 3 Integration tools which make use of Polybase to load data (ADF/Databricks) to follow soon