Impala Migration: Impala to Databricks SQL¶

A detailed guide for migrating Apache Impala workloads to Databricks SQL, including SQL dialect conversion, Kudu-to-Delta migration, performance tuning, and worked examples.

Overview¶

Impala is Cloudera's interactive SQL engine, designed for low-latency analytical queries on HDFS and Kudu data. It serves BI dashboards, ad-hoc analysis, and scheduled reporting workloads. On Azure, these workloads migrate to Databricks SQL Warehouses, which provide comparable or better interactive query performance through the Photon engine, adaptive query execution, and result caching.

The good news: Impala SQL is very close to Spark SQL / ANSI SQL. The migration is primarily a dialect conversion exercise with a few structural changes around metadata management and storage format.

SQL dialect comparison¶

Syntax that works identically¶

The following Impala SQL constructs work without modification on Databricks SQL:

• SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
• JOIN (INNER, LEFT, RIGHT, FULL OUTER, CROSS)
• UNION, UNION ALL, INTERSECT, EXCEPT
• CASE WHEN ... THEN ... ELSE ... END
• WITH (Common Table Expressions)
• WINDOW functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, etc.)
• LATERAL VIEW EXPLODE
• IN, EXISTS, subqueries
• CAST, COALESCE, NULLIF, NVL
• Most string functions: CONCAT, SUBSTR, TRIM, UPPER, LOWER, LENGTH, REGEXP_REPLACE
• Most date functions: YEAR, MONTH, DAY, DATE_ADD, DATE_SUB, DATEDIFF
• Most aggregate functions: COUNT, SUM, AVG, MIN, MAX, COUNT(DISTINCT ...)

Syntax that requires conversion¶

Kudu to Delta Lake migration¶

Kudu is Impala's mutable storage engine, designed for fast inserts, updates, and deletes on analytical data. Delta Lake is the direct replacement.

Feature comparison¶

Kudu table migration script¶

# Step 1: Read from Kudu (on CDH cluster, export to Parquet on HDFS)
# Run this on the CDH cluster
spark.read \
    .format("org.apache.kudu.spark.kudu") \
    .option("kudu.master", "kudu-master:7051") \
    .option("kudu.table", "my_database.my_table") \
    .load() \
    .write.format("parquet") \
    .mode("overwrite") \
    .save("hdfs:///export/kudu/my_database/my_table")

# Step 2: Transfer Parquet files to ADLS Gen2 (azcopy or ADF)
# azcopy copy "hdfs-export-path" "abfss://bronze@storage.dfs.core.windows.net/kudu/my_table"

# Step 3: Create Delta table on Databricks
spark.read.parquet("abfss://bronze@storage.dfs.core.windows.net/kudu/my_table") \
    .write.format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .partitionBy("date_col") \
    .saveAsTable("silver.my_database.my_table")

# Step 4: Enable auto-optimization
spark.sql("""
    ALTER TABLE silver.my_database.my_table
    SET TBLPROPERTIES (
        'delta.autoOptimize.optimizeWrite' = 'true',
        'delta.autoOptimize.autoCompact' = 'true'
    )
""")

# Step 5: Apply Z-ordering for high-cardinality query columns
spark.sql("""
    OPTIMIZE silver.my_database.my_table
    ZORDER BY (customer_id, order_date)
""")

Impala partitioning to Delta partitioning¶

Decision framework¶

Example: convert Impala partitioned table¶

-- Impala (before)
CREATE TABLE sales.orders (
    order_id BIGINT,
    customer_id BIGINT,
    amount DECIMAL(18,2),
    status STRING
)
PARTITIONED BY (order_date DATE)
STORED AS PARQUET;

-- Databricks SQL (after)
CREATE TABLE silver.sales.orders (
    order_id BIGINT,
    customer_id BIGINT,
    amount DECIMAL(18,2),
    status STRING,
    order_date DATE
)
USING DELTA
PARTITIONED BY (order_date);

-- Apply Z-ordering for customer_id queries
OPTIMIZE silver.sales.orders ZORDER BY (customer_id);

Performance tuning comparison¶

Impala performance knobs vs Databricks equivalents¶

Common performance issues after migration¶

Worked example: full query migration¶

Original Impala query¶

-- Impala: Daily revenue report with customer segmentation
COMPUTE INCREMENTAL STATS sales.orders;
COMPUTE INCREMENTAL STATS sales.customers;

SELECT
    o.order_date,
    c.segment,
    COUNT(*) AS order_count,
    SUM(o.amount) AS total_revenue,
    NDV(o.customer_id) AS unique_customers,
    APPX_MEDIAN(o.amount) AS median_order_value,
    GROUP_CONCAT(DISTINCT o.status, ', ') AS statuses_seen
FROM sales.orders o
JOIN sales.customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-12-31'
  AND o.status != 'cancelled'
GROUP BY o.order_date, c.segment
HAVING SUM(o.amount) > 1000
ORDER BY o.order_date, total_revenue DESC;

Migrated Databricks SQL query¶

-- Databricks SQL: Daily revenue report with customer segmentation
-- Statistics are collected automatically on Delta tables, but you can force:
ANALYZE TABLE silver.sales.orders COMPUTE STATISTICS FOR ALL COLUMNS;
ANALYZE TABLE silver.sales.customers COMPUTE STATISTICS FOR ALL COLUMNS;

SELECT
    o.order_date,
    c.segment,
    COUNT(*) AS order_count,
    SUM(o.amount) AS total_revenue,
    APPROX_COUNT_DISTINCT(o.customer_id) AS unique_customers,
    PERCENTILE_APPROX(o.amount, 0.5) AS median_order_value,
    CONCAT_WS(', ', COLLECT_SET(o.status)) AS statuses_seen
FROM silver.sales.orders o
JOIN silver.sales.customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2025-01-01' AND '2025-12-31'
  AND o.status != 'cancelled'
GROUP BY o.order_date, c.segment
HAVING SUM(o.amount) > 1000
ORDER BY o.order_date, total_revenue DESC;

Key changes annotated¶

Impala metadata caching vs Direct Lake / Databricks SQL caching¶

Impala relies on metadata caching in the Impala Catalog daemon (catalogd) to avoid repeated HMS lookups. On Databricks SQL, two caching mechanisms replace this:

Databricks SQL result caching¶

• Automatically caches query results for identical queries
• Cache invalidated when underlying Delta table changes
• No configuration required; enabled by default on SQL Warehouses

Databricks SQL disk caching¶

• Caches remote ADLS Gen2 data on local SSDs of warehouse nodes
• Reduces repeated reads from storage
• Enabled by default on SQL Warehouse worker nodes

Fabric Direct Lake (alternative target)¶

For organizations also using Microsoft Fabric, Direct Lake mode provides:

• Power BI reads Delta tables directly from OneLake -- no data import
• Sub-second query response for dashboards
• No separate caching layer to manage

Migration checklist for Impala workloads¶

• Inventory all Impala queries (scheduled reports, BI connections, ad-hoc)
• Export Kudu table schemas and data to Parquet
• Transfer Parquet data to ADLS Gen2
• Create Delta tables with appropriate partitioning
• Convert Impala SQL to Databricks SQL (use dialect table above)
• Replace COMPUTE STATS with ANALYZE TABLE
• Replace Impala-specific functions (NDV, APPX_MEDIAN, GROUP_CONCAT)
• Update JDBC/ODBC connection strings for BI tools
• Run OPTIMIZE ... ZORDER BY on frequently queried columns
• Enable auto-optimization on all Delta tables
• Benchmark query latency: Impala vs Databricks SQL
• Validate row counts and checksums between source and target
• Update monitoring dashboards to use Azure Monitor
• Train BI users on Databricks SQL Editor (minimal change from Impala shell)

Next steps¶

1. Walk through the Impala to Databricks Tutorial for a hands-on migration exercise
2. Review the Benchmarks for Impala vs Databricks SQL performance data
3. See the Complete Feature Mapping for the full component comparison

Last updated: 2026-04-30 Maintainers: CSA-in-a-Box core team