Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 201 of 212

189 C H A P T E R 9 | Introducing Azure SQL Data Warehouse Settings blade. Click the Copy button to the right of one of the keys to store an access key on the clipboard. Next, use the AzCopy utility to upload your text file. To do this, open a command-prompt window and go to the AzCopy installation directory like this: cd /d "%ProgramFiles(x86)%\Microsoft SDKs\Azure\AzCopy" Then, after replacing the placeholder tokens with the blob service endpoint and storage account key, run the following AzCopy command: .\AzCopy.exe /Source:C:\Temp\ /Dest:/datacontainer/dimsalesterritory/ /DestKey: /Pattern:DimSalesTerritory.txt Now open SSDT, connect to your SQL Data Warehouse in SQL Server Object Explorer, and then right- click the database and click New Query. Next, you must configure authentication as shown in Example 9-6 to create a master key and add a database-scoped credential to your SQL Data Warehouse that PolyBase uses to access the data in blob storage. In the CREATE DATABASE SCOPED CREDENTIAL statement, you can use any string value for IDENTITY because it is not used during authentication, but replace the placeholder token for SECRET with the same storage account key that you used to copy data into blob storage. Example 9-6: Configuring authentication for access to blob storage -- Create a master key if one does not already exist CREATE MASTER KEY; -- Create a database-scoped credential CREATE DATABASE SCOPED CREDENTIAL AzureDWDataStorageCredential WITH IDENTITY = 'user', SECRET = ''; Then you need to create the following external objects in SQL Data Warehouse, as shown in Example 9-7: External data source Identifies where to find your blob storage. The LOCATION argument requires the name of the blob container and storage account into which you uploaded your data. The CREDENTIAL argument uses the database-scoped credential that you added to the SQL Data Warehouse earlier. For more information about creating an external data source and about using PolyBase to access a Hadoop source, see https://msdn.microsoft.com/en- us/library/dn935022.aspx. External file format Describes the structure of your data. The FORMAT_TYPE argument takes one of the following values: DELIMITEDTEXT, RCFILE, ORC, or PARQUET. For more information about creating an external file format and the various ways to use define formats, see https://msdn.microsoft.com/en-us/library/dn935026.aspx. External table Provides the table definition and location of data. Here you must include the column names and data types, provide the directory relative to the blob storage container that holds your data, reference the external data source, and specify the file format. For more information about creating an external table, see https://msdn.microsoft.com/en- us/library/dn935021.aspx. Example 9-7: Creating external objects

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016