Azure Databricks迁移：SQL转PySpark及性能优化最佳实践咨询

Azure Databricks Usage Best Practices #

• Adopt Delta Lake as your storage layer: It provides ACID transactions, schema enforcement, and optimized data layout out of the box. Convert your existing tables to Delta format to leverage features like time travel and optimized writes.
• Organize your workspace for collaboration: Use folders to group related notebooks, libraries, and jobs. Assign appropriate permissions to teams, and use Databricks Repos to sync with Git for version control of your SQL scripts and notebooks.
• Use notebooks strategically: For ad-hoc analysis and prototyping, notebooks are ideal, but for production jobs, package reusable logic into libraries (like Python modules or JARs) and call them from notebooks or jobs to reduce redundancy.
• Optimize cluster usage: Choose the right cluster type (serverless vs. classic) based on your workload. For batch jobs, use job clusters that terminate after completion instead of persistent clusters to save costs. Enable auto-scaling for clusters handling variable workloads.
• Leverage Databricks SQL for querying: If you're primarily using SQL, use Databricks SQL endpoints for dedicated, optimized querying instead of general-purpose clusters. It's designed for BI workloads and offers better performance for interactive queries.

Should You Rewrite SQL to PySpark for Performance? #

In most cases, no—you don’t need to rewrite your working SQL code to PySpark for performance gains. Databricks’ Catalyst Optimizer optimizes SQL queries just as effectively as PySpark DataFrame operations, since both are translated into the same execution plan under the hood.

That said, consider rewriting specific parts to PySpark if:

• You have complex custom logic that’s hard to express efficiently in SQL (e.g., iterative machine learning workflows, complex row-level transformations that require custom UDFs with better performance when implemented using vectorized UDFs).
• You need fine-grained control over execution plans or want to use advanced APIs (like window functions with custom logic, or integration with Python libraries for specialized data processing).
• Your SQL code relies heavily on inefficient scalar UDFs—rewriting these as PySpark vectorized UDFs or replacing them with built-in functions can boost performance.

Stick with SQL for straightforward transformations: it’s more readable, easier to maintain, and your team is already familiar with it.

Post-Migration Performance Optimization Best Practices #

Delta Lake Optimization #

• Run OPTIMIZE on Delta tables: This compacts small files into larger ones, reducing the number of I/O operations. Pair it with ZORDER BY on columns frequently used in filters or joins to improve query speed.
• Use VACUUM to clean up old data: Remove unused data files and snapshots to reduce storage bloat and improve read performance. Set a retention period that aligns with your data governance policies.
• Enable auto-optimize and auto-compact: These features automatically optimize table layout as data is written, eliminating the need for manual OPTIMIZE runs for most workloads.

Cluster & Resource Tuning #

• Right-size your clusters: Match cluster node types to your workload. For CPU-heavy tasks, use compute-optimized instances; for memory-heavy tasks, use memory-optimized instances. Avoid over-provisioning resources.
• Use spot instances for non-critical jobs: Reduce costs by using spot VM instances for batch jobs that can tolerate interruptions.
• Leverage cluster caching: Cache frequently accessed tables or intermediate results using CACHE TABLE in SQL or cache() in PySpark. Be mindful of cache expiration to avoid stale data.

Query Optimization #

• Avoid SELECT *: Only select the columns you need to reduce data transfer and memory usage.
• Optimize joins and filters: Push filters down to the source where possible (Databricks does this automatically in many cases, but verify execution plans). Use broadcast joins for small tables to reduce shuffle overhead.
• Use CTEs wisely: While CTEs improve readability, overusing them can lead to repeated computations. Materialize large CTEs as temporary tables if they’re used multiple times.
• Partition tables strategically: Partition large tables by columns that are commonly used in WHERE clauses (e.g., date columns) to reduce the amount of data scanned during queries.

Monitoring & Iteration #

• Use the Databricks UI to analyze query plans: Check the execution plan to identify bottlenecks like data shuffles or full table scans.
• Track performance metrics: Monitor job run times, cluster utilization, and storage I/O to identify areas for improvement. Use Databricks’ built-in metrics or integrate with Azure Monitor.

内容的提问来源于stack exchange，提问作者Shikha