title: How to Export Results of a SQL Query from Databricks to Azure Data Lake Store
body: <p>I am trying to export the results from a spark.sql query in Databricks to a folder in Azure Data Lake Store - ADLS</p>
  
  <p>The tables that I'm querying are also in ADLS.</p>
  
  <p>I have accessed the files in ADLS from Databricks with the following commnad:</p>
  
  <pre><code>base = spark.read.csv("adl://carlslake.azuredatalakestore.net/landing/",inferSchema=True,header=True)
  base.createOrReplaceTempView('basetable')
  </code></pre>
  
  <p>I am querying the table with the following command:</p>
  
  <pre><code>try:
    dataframe = spark.sql("select * from basetable where LOAD_ID = 1199")
  except:
    print("Exception occurred 1166")
  else:
    print("Table Load_id 1166")
  </code></pre>
  
  <p>I am then attempting to export the results to the folder in Azure using the following:</p>
  
  <pre><code>try:
   dataframe.coalesce(1).write.option("header","true").mode("overwrite").csv("adl://carlslake.azuredatalakestore.net/jfolder2/outputfiles/")
    rename_file("adl://carlslake.azuredatalakestore.net/jfolder2/outputfiles", "adl://carlslake.azuredatalakestore.net/landing/RAW", "csv", "Delta_LoyaltyAccount_merged")
  except:
    print("Exception Occurred 1166")
  else:
    print("Delta File Created")
  </code></pre>
  
  <p>There are two weird issues here:</p>
  
  <ol>
  <li><p>I have specified to query on load_id = 1199, and although there isn't a load_id = 1199 the query is still successful.</p></li>
  <li><p>I would would like the second "try" statement to fail if the first "try" failed, but the second try statement runs regards of the first "try" statement.</p></li>
  </ol>
  
  <p>Can someone let me know where I'm going wrong?</p>
  
  <p>The table can be viewed here 
  <a href="https://drive.google.com/file/d/1nRRP6vBA7uKSyAIX0v6LLA8tcNG7g6GS/view?usp=sharing" rel="nofollow noreferrer">thetable</a></p>