dataGriff

$localinstance = "grifftest"
SqlLocalDB.exe create "grifftest"  
SqlLocalDB.exe start "grifftest" 

## https://docs.microsoft.com/en-us/sql/tools/sqllocaldb-utility?view=sql-server-2017

$dacpacpath = "E:\Documents\Projects\Demo_DatabaseDevOps\AdventureWorks\AdventureWorks\bin\Debug\AdventureWorks.dacpac"
$profilepath = "E:\Documents\Projects\Demo_DatabaseDevOps\AdventureWorks\AdventureWorks\AdventureWorks.publish.xml"
$outputpath = "E:\Documents\Projects\Demo_DatabaseDevOps\AdventureWorks\AdventureWorks\"
$scriptpath = "$outputpath\script.sql"

dataGriff

 ###############################################################################################################
###############################################################################################################

function Update-SQLAgentJobProperty(
[string]$serverName,
[string]$jobName,
[string]$property,
[string]$propertyValue
){

dataGriff

/*
https://www.mssqltips.com/sqlservertip/4772/refactor-sql-server-scalar-udf-to-inline-tvf-to-improve-performance/
https://dba.stackexchange.com/questions/204545/creating-a-persisted-computed-column-with-a-function
*/

CREATE SCHEMA etl;
GO
CREATE SCHEMA staging;
GO
CREATE SCHEMA store;

dataGriff

USE master;
GO

CREATE DATABASE test;
GO 

USE test;
GO 

--######################################################################

dataGriff

USE [TEST]
GO

--######################################################################
PRINT '1.0 Create Functions to Simulate Constants';
GO
--######################################################################

CREATE
	OR ALTER

dataGriff

1. Download Powershell Module

Copy the Powershell module from here and saved somewhere on your PC as ShardManagement.psm1. e.g C:\Program Files)\WindowsPowerShell\Modules\ShardManagement\ShardManagement.psm1

2. Download Nuget and put in same Folder as Module

Download Nuget.exe and place in same folder as you put the Powershell module in part (1). e.g. C:\Program Files)\WindowsPowerShell\Modules\ShardManagement\Nuget.exe

3. You may need to change your Nuget to V2

dataGriff

function GetKeyValue(arg) {
  var z = arg;
  var obj = {
  }

  for(i = 0; i < z.length; i++)
  {

  a = z[1]
  key = a["Key"]

dataGriff

raw/{DataSource}/{EventHub}/avro/{Year}/{Month}/{Day}/{EventHub}_{Year}_{Month}_{Day}_{Hour}_{Minute}_{Second}_{PartitionId}_{Namespace}

dataGriff

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '5up3r53cr3t!';

/*
--didn't do it this way in the end, used managed service identity below instead!!!
--https://docs.microsoft.com/en-us/azure/sql-data-warehouse/load-data-from-azure-blob-storage-using-polybase -- to use managed id
drop DATABASE SCOPED CREDENTIAL ADL_User
CREATE DATABASE SCOPED CREDENTIAL ADL_User WITH 
IDENTITY = '15fd08dc-8e60-4eb0-9aec-141bddebfbe6@https://login.microsoftonline.com/cafe5856-f1cc-43b5-b041-4cfc98c266e7/oauth2/token', 
SECRET = 'x5MqGB]5mX54d/+NvozKVq_0O[K.K-Fg'; --generated under certificates and secrets
*/

dataGriff

spark.conf.set("fs.azure.account.auth.type", "OAuth")
spark.conf.set("fs.azure.account.oauth.provider.type", "org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider")
spark.conf.set("fs.azure.account.oauth2.client.id", "<registeredappid>")
spark.conf.set("fs.azure.account.oauth2.client.secret", "<passwordhere>")
spark.conf.set("fs.azure.account.oauth2.client.endpoint", "https://login.microsoftonline.com/<tenantid>/oauth2/token")
spark.conf.set("fs.azure.createRemoteFileSystemDuringInitialization", "true")
dbutils.fs.ls("abfss://<container>@accountname.dfs.core.windows.net/")
spark.conf.set("fs.azure.createRemoteFileSystemDuringInitialization", "false")