18. Databricks: SQL vs. Python for Data Manipulation & Delta Lake Features

Notebook Language and Execution

  • Default Notebook Language: When creating a notebook in Databricks, a default language (e.g., Python, SQL, R, Scala) is specified. This is the language the notebook assumes you are writing in.

  • Magic Commands for Cell-Specific Language: You can override the default language for individual cells using magic commands (e.g., %python, %sql, %r, %scala). This allows for polyglot notebooks where different languages can be used within the same notebook.

    • Example: If the default is SQL, %python can be used to execute Python code in a specific cell.

Creating Tables in Databricks

  • Traditional Approach (PySpark/DataFrame): Historically, data was often collected from files into a PySpark DataFrame, and then the DataFrame was written into a table.

  • SQL-Centric Approach: Databricks also fully supports creating and managing tables using standard SQL DDL (Data Definition Language) statements, similar to any traditional database.

    • Azure Databricks SQL Reference: Comprehensive documentation is available for all SQL statements (DDL, DML, data retrieval) supported in Databricks.

    • CREATE DATABASE: Used to create a new database (schema) to organize tables, e.g., CREATE DATABASE cubics;.

    • CREATE TABLE Statement: Allows direct table creation using SQL.

      • Syntax: CREATE TABLE <database_name>.<table_name> ( <column_name_1> <data_type_1>, <column_name_2> <data_type_2>, ... ) USING Delta;

      • Example: CREATE TABLE cubics.emp (emp_id INT, emp_name STRING, address STRING, salary INT) USING Delta; This creates a table in Delta format.

Types of Tables: Managed vs. External

  • Managed Table: When a CREATE TABLE statement is executed without specifying an external location, a managed table is created.

    • Storage: Databricks manages both the table's metadata and its underlying data files. The data files are typically stored in an internal location (e.g., /user/hive/warehouse/<database_name>.db/<table_name>/) that the user does not directly control.

    • Lifecycle: If the managed table is dropped, both the table's metadata and all its underlying data files are deleted.

  • External Table: (Mentioned as a contrast, with more details to come later).

    • Storage: The user specifies an external storage location for the data files. Databricks only manages the table's metadata.

    • Lifecycle: If an external table is dropped, only its metadata is removed from Databricks, while the underlying data files remain in the specified external storage location.

Data Manipulation Language (DML) with Delta Lake

  • Delta Lake Prerequisite: UPDATE, DELETE, and MERGE operations are exclusively available for Delta tables. These operations are generally not directly supported on other file formats like Parquet without significant workarounds.

  • INSERT INTO Statement: Used to add new rows of data to a table.

    • Standard SQL: Works identically to INSERT INTO in other SQL databases.

    • File Creation on Insert: Each INSERT operation, even for a single record, typically results in the creation of a new data file in the table's storage location.

  • UPDATE Statement: Used to modify existing data in a table.

    • Standard SQL: Follows standard SQL syntax: UPDATE <table_name> SET <column>=<value> WHERE <condition>;

    • Delta Mechanism: When a record is updated:

      1. The original data file containing the old version of the record is marked for deletion but is not immediately removed from storage.

      2. A new data file is created with the updated version of the record.

      3. This means more files might exist in storage than active records, as old, unused files are retained until a VACUUM operation.

  • DELETE Statement: Used to remove rows from a table.

    • Standard SQL: Follows standard SQL syntax: DELETE FROM <table_name> WHERE <condition>;

    • Delta Mechanism: When records are deleted:

      1. The data files containing the deleted records are marked for deletion but are not immediately removed.

      2. No new data files are typically added.

The Delta Transaction Log and File Management

  • Delta Log (_delta_log directory):

    • A critical component of Delta Lake, located in a subdirectory (e.g., _delta_log) within the table's storage path.

    • Consists of a series of JSON files (e.g., 00000000000000000000.json, 00000000000000000001.json, etc.) that record every transaction (CREATE, INSERT, UPDATE, DELETE, MERGE, etc.) performed on the Delta table.

    • Each JSON file contains detailed metadata about the operation, including files added, files removed, transaction IDs, and operation metrics.

  • Rebuilding Table State: When a query (e.g., SELECT * FROM table) is executed:

    • Delta Lake reconstructs the current state of the table by reading the initial state (e.g., from 0.json) and then sequentially applying all subsequent changes recorded in the JSON log files until the latest transaction is processed.

    • Example: If there are 55 log files (0-4.json), the table state is built by applying changes from 1.json, then 2.json, then 3.json, and finally 4.json to the initial state defined in 0.json.

  • Checkpoint Files: To optimize the rebuilding process for tables with a large number of transactions (many log files), Delta Lake automatically creates checkpoint files periodically (e.g., after every 1010 transactions).

    • Purpose: A checkpoint file summarizes the state of the table up to a certain point, allowing faster reconstruction of the table's current state by starting from the most recent checkpoint instead of processing all individual JSON log files from the very beginning.

    • Persistence: Checkpoint files are never dropped by cleanup operations; they are essential for state reconstruction.

  • VACUUM Operation:

    • Purpose: VACUUM is a maintenance command used to permanently remove data files from a Delta table's storage location that are no longer referenced by the table's transaction log (i.e., files marked for deletion during UPDATE/DELETE operations).

    • Retention Period: It typically requires a retention period (default 77 days) to ensure that files necessary for