Data Lake Integration — Part2
Azure Data engineering Part 5
Create ADLS Gen 2 storage account (Hierarchical namespace enabled storage). Add Container to the above storage account. Assign Storage blob data contributor role to the Synapse Identity.
Create the storage account named stgstoresales
Add a container stgstoresales-container
Add Synpase managed identity to storage blob reader role
Upload Supermarket_sales file to stgstoresales-container
Get Raj Rathi’s stories in your inbox
Join Medium for free to get updates from this writer.
Before we start the next step- create a master key: The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='AAAAAAAA'Reading data directly from Azure storage
Use managed identity feature in synapse so no need to store keys of storage account in Synapse
Steps to access data
1. Create scoped credential
2. Create external data sources
3. Create format file
4. Create schema
5. Create an external table (mount table)
6. Read data
A database scoped credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server.
An external data source is a connection to an external database.
File format defines the structure of data in an external source
Schema is a grouping of related table
Moving data from Azure storage to internal tables in SQL dedicated pool
Create schema
Create internal table
Copy data to internal table
Next section, we will write the stored procedure to copy data from the external table to the internal table and schedule it.
