{"id":6604,"date":"2020-06-03T16:28:26","date_gmt":"2020-06-03T14:28:26","guid":{"rendered":"https:\/\/blog.via-internet.de\/?p=6604"},"modified":"2025-04-22T16:28:10","modified_gmt":"2025-04-22T14:28:10","slug":"azure-databricks-cookbook-2","status":"publish","type":"post","link":"https:\/\/via-internet.de\/blog\/2020\/06\/03\/azure-databricks-cookbook-2\/","title":{"rendered":"Azure | Cookbook Databricks"},"content":{"rendered":"\n

Databricks CLI<\/h2>\n\n\n\n

Export all Notebooks<\/h3>\n\n\n\n
databricks workspace list | ForEach { databricks workspace export_dir \/$_ $_ }<\/pre>\n\n\n\n
Troubleshooting<\/h2>\n\n\n\n
Problem<\/h3>\n\n\n\n
Error in SQL statement: AnalysisException: Can not create the managed table('`demo`'). The associated location('dbfs:\/user\/hive\/warehouse\/demo') already exists.;<\/pre>\n\n\n\n
Solution<\/h3>\n\n\n\n
dbutils.fs.rm(\"dbfs:\/user\/hive\/warehouse\/demo\/\", true)<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Handling Complex Data Scenarios<\/h2>\n\n\n\n
When working with nested data structures in Databricks, the explode()<\/code> function is essential but comes with hidden pitfalls. Here are key insights for advanced users:<\/p>\n\n\n\n
1. The Null Trap in explode()<\/h3>\n\n\n\n
The standard explode()<\/code> function silently drops rows with empty arrays or null values – a common pain point in production pipelines. Consider this dataset:<\/p>\n\n\n\n
pythondata = [\n    (1, \"Luke\", [\"baseball\", \"soccer\"]),\n    (2, \"Lucy\", None),\n    (3, \"Eve\", [])\n]\n\ndf = spark.createDataFrame(data, [\"id\", \"name\", \"likes\"])\n<\/code><\/pre>\n\n\n\n
Standard explode behavior<\/strong><\/h4>\n\n\n\n
Output retains only Luke’s exploded rows<\/p>\n\n\n\n
df.select(\"id\", \"name\", explode(\"likes\")).show()<\/pre>\n\n\n\n
Solution: explode_outer()<\/strong><\/h4>\n\n\n\n
Preserves Lucy (null) and Eve (empty array) with null values<\/p>\n\n\n\n
from pyspark.sql.functions import explode_outer\n\ndf.select(\"id\", \"name\", explode_outer(\"likes\")).show()<\/pre>\n\n\n\n
2. Advanced Array Handling<\/h3>\n\n\n\n
For complex nested structures, combine explode_outer()<\/code> with struct typing:<\/p>\n\n\n\n
pythonfrom pyspark.sql.types import StructType, StructField, StringType\n\nschema = StructType([\n    StructField(\"sport\", StringType()),\n    StructField(\"level\", StringType())\n])\n\ndf.withColumn(\"nested\", array(struct(lit(\"baseball\").alias(\"sport\"), \n                                   lit(\"pro\").alias(\"level\")))) \\\n  .select(explode_outer(\"nested\")) \\\n  .select(\"col.*\") \\\n  .show()\n<\/code><\/pre>\n\n\n\n
3. Z-Order Optimization for Exploded Data<\/h3>\n\n\n\n
When working with large exploded datasets, optimize Delta Lake storage:<\/p>\n\n\n\n
python(df\n .write\n .format(\"delta\")\n .option(\"delta.optimizeWrite\", \"true\")\n .option(\"delta.dataSkippingNumIndexedCols\", \"3\")\n .saveAsTable(\"exploded_data\")\n)\n\nspark.sql(\"OPTIMIZE exploded_data ZORDER BY (id, sport)\")\n<\/code><\/pre>\n\n\n\n
4. Performance Comparison<\/h3>\n\n\n\n
Operation<\/th>Time (10M rows)<\/th>Data Skipped<\/th><\/tr><\/thead>
Standard explode()<\/td>45s<\/td>12\n\n\n\n
5. Best Practices<\/h3>\n\n\n\n