How to migrate Azure SQL tables to databricks dynamically?

 During Azure SQL to databricks migration, I was trying to find a way to migrate multiple tables within multiple databases as per defined(TableName,DBName) in a configuration table .


Configuration table structure like

DBname

SchemaName

SqlTableName

SqlServerName

DatabricksDBName

data like

DB1 dbo t1 serverUrl1 Cp_DB1

DB1 dbo t2 Serverurl2 Cp_DB1

DB2 dbo t1 serverurl3 Cp_DB2

DB3 dbo t2 serverUrl1 Cp_DB2


So I need to migrate these tables by picking data from corresponding Databases and server and need to load in Databricks bronze layer with expected databricks dbname


Solution


I created a adf pipeline to loop through with this table by lookup activity + foreach activity and passed servername, Tablename , database name, User,Password(from Key Vault) as parameters to databricks notebook inside pipeline.


ADF pipeline -> lookup activity > foreach activity> scale up corresponding db > notebook activity> scale down corresponding db.


Databricks generic notebook-



 % python 

dbutils.widgets.text(name = "DbName", defaultValue = "DB1")   

dbutils.widgets.text(name = "dbTable", defaultValue = "dbo.t1")

dbutils.widgets.text(name = "Server", defaultValue = "serverUrl1 ")

dbutils.widgets.text(name = "DatabricksDbName", defaultValue = "DB1")

dbutils.widgets.text(name = "User", defaultValue = "user1")

dbutils.widgets.text(name = "Password", defaultValue = "xxx") 

DatabricksTableName = dbutils.widgets.get('dbTable').split(".")[1]

url = "jdbc:sqlserver://" + dbutils.widgets.get('Server') + ";DatabaseName=" + dbutils.widgets.get('DbName') + ";"

dbutils.widgets.text(name = "DatabricksTableName", defaultValue = DatabricksTableName)

dbutils.widgets.text(name = "Url", defaultValue = url) 



create database

if not exists  ${DatabricksDbName}; 


drop table

if exists ${DatabricksDbName}.jdbc_InputTable;


CREATE  TABLE $ {DatabricksDbName

}.jdbc_InputTable


USING org.apache.spark.sql.jdbc

OPTIONS( url '${Url}',  dbtable '${dbTable}',  user '${User}',  password '${Password}');  


drop table

if exists ${DatabricksDbName}.${DatabricksTableName}; 


create table $ {DatabricksDbName

}.${ DatabricksTableName}

using delta as

select * from $ { DatabricksDbName

}.jdbc_InputTable; 


drop table

if exists ${DatabricksDbName}.jdbc_InputTable; 


#Databricks #Azure #AzureSQL #AzureKeyVault

Comments

Post a Comment

Popular posts from this blog

Performance optimization in Copy data activity in Azure

This article outlines the copy activity performance optimization features that you can leverage in Azure Data Factory and Synapse pipelines. Copy performance optimization features The service provides the following performance optimization features: Database transaction units (DTUs) Data Integration Units Self-hosted integration runtime scalability Parallel copy Staged copy Database transaction units (DTUs) A database transaction unit (DTU) represents a blended measure of CPU, memory, reads, and writes.  Service tiers in the DTU-based purchasing model are differentiated by a range of compute sizes with a  fixed amount of included storage, fixed retention period for backups, and fixed price. All service tiers in the DTU-based purchasing model  provide flexibility of changing compute sizes with minimal downtime; however, there is a switch over period where connectivity is lost to  the database for a short amount of time, which can be mitigated using retry logic. Single...
Read more

Azure blob Storage interview questions

 Azure Blob Storage is a cloud-based storage service that is often used to store and process large amounts of data.  it is important to be prepared to answer questions about this service. 1. What is Azure Blob Storage? Azure Blob Storage is a cloud-based storage service that is optimized for storing large amounts of unstructured data, such as video, audio,and images. Blob storage is also a good choice for storing data that needs to be accessed frequently, but doesn’t need to be updated often. 2. How does Azure Blob storage work? Azure Blob storage is a cloud-based storage service that allows you to save and retrieve data from anywhere in the world.  Blobs are stored in containers, which can be public or private, and can be accessed via a URL. Azure Blob storage is a great way to store large amounts of data that you need to be able to access quickly and easily. 3. What are the key components of Azure blob storage? The key components of Azure blob storage are containers and...
Read more

Why do we have two keys in storage account and need for rotate them ?

 On more than one occasion developers do not see the sense to have two keys for an Azure Storage account when they always use the first one  to its developments. The truth is that I have found that most of the time there is no key maintenance   to improve the security of the storage account.   The idea with this pair of keys is to always have a «backup key» when you regenerate one of them so that there is no loss of service at any time.  There might be case when you see your key1 has been compromised so you can rotate the key in applications , doing this your application will still work. The upgrade process in this case could be the following: The first key is used in your applications. When it’s time to regenerate the keys, change the first key for the second key in your applications settings that are using it. Regenerate the primary key and again change the secondary key with the new one generated. Regenerate the second key. This process can easily be aut...
Read more