Load data from one database to another database in ADLA

Currently in U-SQL, there is no support for backup and restore of databases. So, if you are having a production database and want to restore the data from production to development, you dont have easy mechanism to do it.

Below powershell script will be helpful to copy all data from production database to development database, provided both are having same schema.

Make sure that you are doing deployment in both development and production database and they are in same schema for tables.

The below powershell script generates the TRUNCATE TABLE & INSERT INTO script for all the production database tables. This genrerated script can be run as a job in ADLA development account. Replace the highlighted values with your details accordingly.  

Connect-AzureRmAccount -SubscriptionName "YOUR_SUBSCRIPTIONNAME"

$usqlScriptForTablesBackup = ""

$tables = @()

$databaseName = "PRODUCTIONDB"

$tables = Get-AdlCatalogItem -Account "PRODUCTION_ADLA_Account" -ItemType Table -Path $databaseName |  ForEach-Object { [pscustomobject]@{schema=$_.SchemaName; table=$_.Name} }

foreach($table in $tables)

{

$usqlScriptForTablesBackup += "TRUNCATE TABLE {0}.{1}.{2}; INSERT INTO {0}.{1}.{2} SELECT * FROM PRODUCTION_ADLA_Account.{0}.{1}.{2};`r`n" -f "PRODUCTIONDB",$table.schema, $table.table

}

$databaseName = "DEVELOPMENTDB"

$tables = Get-AdlCatalogItem -Account "PRODUCTION_ADLA_Account" -ItemType Table -Path $databaseName |  ForEach-Object { [pscustomobject]@{schema=$_.SchemaName; table=$_.Name} }

foreach($table in $tables)

{

$usqlScriptForTablesBackup += "TRUNCATE TABLE {0}.{1}.{2}; INSERT INTO {0}.{1}.{2} SELECT * FROM PRODUCTION_ADLA_Account.{0}.{1}.{2};`r`n" -f "sources",$table.schema, $table.table

}

$usqlScriptForTablesBackup | Out-File -FilePath "D:\backupscript.usql"

git creating a branch and setting upstream branch

If you are working on a new task and you want to create a user branch and push it to remote, you can do so, using below methods.

Assuming you are in develop branch

git checkout develop #Recommended to always point your branch to same commit as develop
git pull                       #Recommened to get the latest from remote
git branch users/venkat/performanceFix #Creating new branch for your needs
git checkout users/venkat/performanceFix #Checking out branch locally
git push origin users/venkat/performanceFix  #Pushing the branch to remote
git push --set-upstream origin users/venkat/germanyThresholdChanges #Setting upstream branch for  current branch as the newly created branch


or

git checkout -b users/venkat/performanceFix Origin/venkat/performanceFix #single step if the remote branch already exists

or

If the remote branch already exists and you want to set the remote branch as current branch upstream.

git branch users/venkat/performanceFix
git checkout users/venkat/performanceFix
git branch --set-upstream-to origin/users/venkat/performanceFix

Finding Deleted file content in git

I was searching for a deleted file content and wanted to see which commit, the file was deleted. Finally, was able to resolve the same using the below commands.

git log -p -- DeletedFilePath  #Here, p stands for patch mode   or

git rev-list -n 1 HEAD -- DeletedFilePath # Here, we are printing commits in reverse chronological way

After I found the commit, I went to VSO to search for the commit. We can also find the commit content using below git command.

git show

Generating ALTER TABLE Script for MASKING COLUMNS from existing database

If we are having a database with masking columns(Dynamic Data Masking) and not sure about how to get all the masking columns information as a ALTER TABLE script,  we can use below script for the same.

SELECT 'ALTER TABLE ' + SCHEMA_NAME(t.schema_id) + '.' + object_name(c.object_id) + ' ALTER COLUMN ' + c.name + ' ' + UPPER(tp.name) +

                CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')

                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'

                     WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')

                       THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'

                     WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')

                       THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'

                     WHEN tp.name = 'decimal'

                       THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'

                    ELSE ''

                END + ' MASKED WITH (FUNCTION='''+ c.masking_function COLLATE SQL_Latin1_General_CP1_CI_AS + ''')'+ CASE c.is_nullable WHEN 1 THEN ' NULL;' ELSE ' NOT NULL;' END

FROM sys.masked_columns as c

JOIN sys.tables AS t

ON c.object_id = t.object_id

JOIN sys.types as tp

on c.user_type_id = tp.user_type_id