Created
May 20, 2020 17:49
-
-
Save ZMon3y/9328a919bc0b1581c4513b0768c52866 to your computer and use it in GitHub Desktop.
bash Script to Create Azure Synapse Analytics Workspace & SQL Pool
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
IF OBJECT_ID('ods.myTable', 'U') IS NOT NULL | |
DROP TABLE ods.myTable | |
CREATE TABLE ods.myTable | |
( | |
[foo] int NOT NULL | |
) | |
WITH | |
( | |
DISTRIBUTION = ROUND_ROBIN, | |
CLUSTERED COLUMNSTORE INDEX | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'ods')) | |
BEGIN | |
EXEC ('CREATE SCHEMA [ods] AUTHORIZATION [dbo]') | |
END |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
#### DEFINE VARIABLES #### | |
echo "DEFINING VARIABLES" | |
randomNumber=$((1 + RANDOM % 1000)) | |
resourceGroupName="synapsePOC" | |
location="eastus2" | |
synapseStorageAccount="synapsestorageaccount$randomNumber" # synapseStorageAccount### | |
synapseFileSystem="synapsefilesystem$randomNumber" # synapseFileSystem### This is basically a container | |
synapseWorkspaceName="synapseworkspace$randomNumber" | |
synapseAdminName="sqladminuser" | |
synapseAdminPW="y0urg00dP@ssW0rd" | |
synapseSQLPoolName="sqlpool$randomNumber" # 15 char max | |
synapsePerformanceLevel="DW100c" | |
ipaddress=`curl -s checkip.dyndns.org | sed -e 's/.*Current IP Address: //' -e 's/<.*$//'` | |
createScripts="create_schemas.sql,create_tables.sql" | |
#### ENABLE SYNAPSE EXTENSION #### | |
echo "ENABLING SYNAPSE EXTENSION" | |
az extension add --name synapse | |
#### DELETE RG IF EXISTS #### | |
echo "DELETING RESOURCE GROUP $resourceGroupName IF IT EXISTS" | |
if [ `az group exists --name $resourceGroupName` = "true" ] | |
then | |
echo "IT EXISTED....DELETING" | |
az group delete --name $resourceGroupName | |
else | |
echo "IT DIDN'T EXIST...CONTINUING" | |
fi | |
#### CREATE RG #### | |
echo "CREATING RESOURCE GROUP $resourceGroupName" | |
az group create --name $resourceGroupName --location $location | |
#### CREATE STORAGE ACCOUNT #### --https://docs.microsoft.com/en-us/cli/azure/storage/account?view=azure-cli-latest#az-storage-account-create | |
echo "CREATING STORAGE ACCOUNT FOR SYNAPSE: $synapseStorageAccount" | |
az storage account create --name $synapseStorageAccount \ | |
--resource-group $resourceGroupName \ | |
--access-tier Hot \ | |
--enable-hierarchical-namespace true \ | |
--kind StorageV2 \ | |
--location $location \ | |
--sku Standard_RAGRS | |
#### CREATE FILE SYSTEM / CONTAINER #### | |
echo "CREATING FILE SYSTEM (CONTAINER): $synapseFileSystem" | |
connectString=`az storage account show-connection-string -g $resourceGroupName -n $synapseStorageAccount -o tsv` | |
az storage container create --name $synapseFileSystem \ | |
--account-name $synapseStorageAccount \ | |
--connection-string $connectString | |
#### CREATE SYNAPSE WORKSPACE #### | |
echo "CREATING SYNAPSE WORKSPACE" | |
az synapse workspace create --name $synapseWorkspaceName \ | |
--resource-group $resourceGroupName \ | |
--storage-account $synapseStorageAccount \ | |
--file-system $synapseFileSystem \ | |
--sql-admin-login-user $synapseAdminName \ | |
--sql-admin-login-password $synapseAdminPW \ | |
--location $location | |
#### CREATE SYNAPSE SQL POOL #### | |
echo "CREATING SYNAPSE SQL POOL" | |
az synapse sql pool create --resource-group $resourceGroupName \ | |
--workspace-name $synapseWorkspaceName \ | |
--name $synapseSQLPoolName \ | |
--performance-level $synapsePerformanceLevel | |
# Allow web access to workspace workaround | |
identity=`az synapse workspace show --name $synapseWorkspaceName --resource-group $resourceGroupName --query "identity.principalId" -o tsv` | |
az role assignment create --role "Storage Blob Data Contributor" --assignee-object-id $identity --scope `az storage account show -g $resourceGroupName -n $synapseStorageAccount --query "id" -o tsv` | |
az synapse workspace firewall-rule create -g $resourceGroupName --workspace-name $synapseWorkspaceName --name allowAll --start-ip-address 0.0.0.0 --end-ip-address 255.255.255.255 | |
#### MODIFY FIREWALL #### | |
echo "MODIFYING FIREWALL TO ALLOW ACCESS FROM THIS ENVIRONMENT ($ipaddress)" | |
az synapse workspace firewall-rule create --resource-group $resourceGroupName \ | |
--workspace-name $synapseWorkspaceName \ | |
--name azureCLI \ | |
--start-ip-address $ipaddress \ | |
--end-ip-address $ipaddress | |
#### CONNECT TO DB & CREATE SCHEMA & TABLES #### --https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15 | |
echo "CONNECTING TO NEW SYNAPSE DB & CREATING SCHEMA AND TABLES" | |
sqlcmd -S $synapseWorkspaceName.sql.azuresynapse.net -U $synapseAdminName \ | |
-P $synapseAdminPW \ | |
-I \ | |
-d $synapseSQLPoolName \ | |
-i $createScripts | |
#### PAUSE SQL POOL #### | |
az synapse sql pool pause --name $synapseSQLPoolName --resource-group $resourceGroupName --workspace-name $synapseWorkspaceName |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment