Automate Databricks Integration Tasks from PowerShell

The CData ADO.NET Provider for Databricks is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Databricks.

Getting Started

---

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Databricks; this tutorial shows how to use the Provider to create, retrieve, update, and delete Databricks data.

Once you have acquired the necessary connection properties, accessing Databricks data in PowerShell can be enabled in three steps.

To connect to a Databricks cluster, set the properties as described below.

Note: The needed values can be found in your Databricks instance by navigating to Clusters, and selecting the desired cluster, and selecting the JDBC/ODBC tab under Advanced Options.

• Server: Set to the Server Hostname of your Databricks cluster.
• HTTPPath: Set to the HTTP Path of your Databricks cluster.
• Token: Set to your personal access token (this value can be obtained by navigating to the User Settings page of your Databricks instance and selecting the Access Tokens tab).

1. Load the provider's assembly:

   
   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Databricks\lib\System.Data.CData.Databricks.dll")
       

2. Connect to Databricks:

    
   $conn= New-Object System.Data.CData.Databricks.DatabricksConnection("Server=127.0.0.1;Port=443;TransportMode=HTTP;HTTPPath=MyHTTPPath;UseSSL=True;User=MyUser;Password=MyPassword;")
   $conn.Open()
   

3. Instantiate the DatabricksDataAdapter, execute an SQL query, and output the results:

   
   $sql="SELECT City, CompanyName from Customers"
   
   $da= New-Object System.Data.CData.Databricks.DatabricksDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.city $_.companyname
   }
     

Update Databricks Data

$cmd =  New-Object System.Data.CData.Databricks.DatabricksCommand("UPDATE Customers SET Country='US' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Databricks.DatabricksParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Databricks Data

$cmd =  New-Object System.Data.CData.Databricks.DatabricksCommand("INSERT INTO Customers (Country) VALUES (@myCountry)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Databricks.DatabricksParameter("@myCountry","US")))
$cmd.ExecuteNonQuery()

Delete Databricks Data

$cmd =  New-Object System.Data.CData.Databricks.DatabricksCommand("DELETE FROM Customers WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Databricks.DatabricksParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()