Azure SQL Data Warehouse

Setting up a SQL Data Warehouse

Create a resource in the Azure portal
Select SQL Data Warehouse in the Marketplace
Under the Basics tab, include the following information:
- Subscription
- Resource group
- Data warehouse name
- Server
Under the Select Performance Level field, select Gen2DW100c
Review + Create

Adding a Client IP Address

After the SQL Data Warehouse instance is provisioned, open it by selecting Go To Resource
At the top of the Overview pane, select the Server Name link to go to the associated SQL Server instance
Select Firewalls and Virtual Networks
Add Client IP

Initializing Connections in Azure Data Studio

Select your data warehouse from the resource group in the Azure portal
Copy the server name for the data warehouse
Add a new connection in Azure Data Studio
Enter the following information:
- Connection type
- Server (taken from the server name from step 2)
- Authentication type (e.g. SQL Login)
- User name
- Password
- Database
Connect

Installing Azure CLI for macOS

Install Azure CLI

$ brew update && brew install azure-cli

Log in to Azure CLI in Azure CLI

$ az login

Run an bash script in Azure CLI

$ bash ./path_to_file/test.sh

Adding a User to Azure SQL Data Warehouse

Create the following queries for the database within either Azure SQL Data Warehouse or Azure Data Studio
Create a new server login (so the user can access the server)

CREATE LOGIN example_user_login WITH PASSWORD = 'Str0ng_password';

Create a new database login (so the user can access the database)

CREATE USER example_user_name FOR LOGIN example_user_login;

Allow user to read data from Azure SQL Data Warehouse

EXEC sp_addrolemember 'db_datareader', 'example_user_name';

Creating a Hash Table in Azure Data Studio

(
	[EmployeeID] int NOT NULL,
	[EmployeeName] varchar(30) NOT NULL,
	[DOB] date NOT NULL,
	[Address] varchar(50) NOT NULL,
	[BloodGroup] nvarchar(2) NOT NULL
)
WITH
(
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = HASH([EmployeeID])
);

Creating a Round-Robin Distributed Table in Azure Data Studio

(
	[EmployeeID] int NOT NULL,
	[EmployeeName] varchar(30) NOT NULL,
	[DOB] date NOT NULL,
	[Address] varchar(50) NOT NULL,
	[BloodGroup] nvarchar(2) NOT NULL
)
WITH
(
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = ROUND_ROBIN
);

Creating a Replicated Distributed Table in Azure Data Studio

(
	[EmployeeID] int NOT NULL,
	[EmployeeName] varchar(30) NOT NULL,
	[DOB] date NOT NULL,
	[Address] varchar(50) NOT NULL,
	[BloodGroup] nvarchar(2) NOT NULL
)
WITH
(
	CLUSTERED COLUMNSTORE INDEX,
	DISTRIBUTION = REPLICATE
);

References

Previous

Next

Data Warehousing in Azure

Azure Data Factory