azureazure-data-factoryazure-databricksdatabricks-unity-catalog

writing data into Unity Catalog tables using ADF

I am looking to copy data from source RDBMS system into databricks Unity catalog. I am using "Azure Databricks Delta Lake" linked service and dataset. I got 100 tables that I want to copy across as they are in source. I stored the list in text file. Reading that file in ADF for using for each to copy all tables with columns and dump it into unity catalog. But all my copy activities fails with table/view not found error. I want ADF to create table if not exists. Is that possible. Does ADF supports Unity catalog?

enter image description here

enter image description here

Solution

  • I am looking to copy data from source RDBMS system into databricks Unity catalog. I am using "Azure Databricks Delta Lake" linked service and dataset. I got 100 tables that I want to copy across as they are in source. I stored the list in text file. Reading that file in ADF for using for each to copy all tables with columns and dump it into unity catalog. But all my copy activities fails with table/view not found error. I want ADF to create table if not exists. Is that possible. Does ADF supports Unity catalog?

    ADF does not support direct copy from RDBMS sources to Databricks Delta Lake. But to resolve this I tried performing the same way to work around.

    I have used stagging method. In your Copy Activity, go to the “Sink” tab (this is where you configure your Databricks Delta Lake destination). Scroll down to “Enable Staging” and set it to “Enabled”. Once you enable staging, it will ask you to configure: Staging Linked Service: Select your Azure Blob Storage or ADLS Gen2. Staging Path: Give a path in your storage account (ADF will use this to temporarily hold data). What happens now: ADF will first copy the data from your RDBMS into staging (ADLS or Blob Storage) in a format like Parquet. Then it will copy from staging into Databricks Delta Lake (which is supported).

    enter image description here

    enter image description here

    Lastly you can check the output.

    Also you can this documentation https://medium.com/@kyle.hale/creating-delta-lake-tables-in-databricks-unity-catalog-with-azure-data-factory-24e3ece861ea for other methods.