38 ADF & Databricks Use Case - 2

Azure Data Factory and Postgres Integration Use Case

Overview of the Use Case

  • Purpose: To build a solution utilizing Azure Data Factory (ADF) as a middleware to extract data from PostgreSQL and process it for reporting and analytics.

  • Core Idea: Implement an ETL (Extract, Transform, Load) job that loads data into a storage volume.

  • Utilizes Azure Data Factory as an ETL component to facilitate data extraction and syncing.

Data Flow Explanation

  • Data Extraction:

    • Source: PostgreSQL database.

    • Destination: A volume (specifically, a Parquet file in Azure Data Lake Storage).

  • The ADF pipeline is set to extract data from PostgreSQL and load it into the designated volume.

ADF Pipeline Setup

  1. Creating Connections:

    • Necessary to establish linked services between ADF and external services required for data integration.

    • Initial connection made with the PostgreSQL database.

      • Specifications required for connection:

      • Server Name

      • Port

      • Database Name

      • Username

      • Password

  2. Loading and Accessing Data:

    • Once data is loaded into ADF (Azure Data Factory), the next step is to save it into Databricks catalog volume.

    • Important to understand where and how files are organized in ADLS (Azure Data Lake Storage).

    • Files are stored in the following hierarchy:

      • Inside the sample volume container

      • Located within the Kubix meta store under the sample data directory.

  3. Handling File Types and Formats:

    • Data extracted and saved as Parquet files.

    • Decided against using direct methods to pull data into Databricks instead of through ADF for reasons of connectivity, robustness, and configuration flexibility.

Dataset Creation in ADF

  1. Creating the Dataset:

    • Dataset linked to PostgreSQL defined to extract data.

    • Capable of utilizing SQL queries to filter data (dynamic or static).

    • Example: Pass dynamic table names or conditions as parameters.

  2. Dynamic Filenames:

    • Parameters can be defined to make file names dynamic based on operational needs.

    • Create a parameter in the pipeline (e.g., file_name).

    • Allows specifying file names at runtime to avoid hardcoding.

Executing Pipelines in ADF

  • A copy activity will be structured to move data from PostgreSQL to ADLS container:

    • Source: PostgreSQL table.

    • Destination: ADLS container, configuring it to allow dynamic updates, such as date-based filtering for orders.

Running Notebooks within ADF Pipelines

  • A specific ADF task designed to run a Databricks notebook after the data copy operation.

  • Provides seamless loading into Spark dataframes, creating temporary views, and executing necessary transformations through Python code.

  • Parameters need to be forwarded correctly to ensure the notebook executes with the expected inputs.

Databricks Integration

  1. Creating Databricks Linked Service:

    • From Azure Data Factory, establish a new linked service towards Databricks.

    • Differentiate between job clusters (temporary) and all-purpose clusters (persistent across multiple sessions).

    • Specify cluster ID and access tokens required for authentication.

  2. Accessing Clusters and Tokens:

    • Generate an access token within Databricks.

    • Use the cluster ID to designate which cluster to execute the notebook.

Best Practices and Dynamic Features

  • Emphasize using dynamic queries to handle varying data structures and requirements:

    • Flexibility in specifying table names and paths as parameters to accommodate changes in data structure.

    • Employ provisioning of temp views in Spark dynamically based on the parameters.

    • Maintain a config file (such as JSON) to define source and target relationships to simplify integration with new tables.

Handling New Tables in the Future

  • Steps to create notebooks dynamically:

    • Utilize parameters to dictate table names dynamically, avoiding the need to hardcode into notebooks.

    • Consider a centralized approach to manage table relationships in JSON format.

Benefits of Using ADF

  • ADF's broad connectivity and flexibility to pull data from hybrid sources (on-premises and cloud).

  • Support for self-hosted integration runtimes allows connectivity to on-prem databases, useful for enterprise scenarios.

  • Allows for orchestrating complex workflows involving data processing and reporting across various systems efficiently.