Databricks API JSON Result to Collection

naveed_raza · ‎08-12-25

Hi All,

Does anybody get a chance to work on Databricks API ?

Databricks API result comes in JSON and the JSON format is different compare to other API results as here in Databricks API we are getting columns in different array of object and actual data in different array of object.

What is the best way to convert this into proper data table (collection) with headers ?

Example:

{
"schema": {
   "columns": 
      [
        {
          "name": "headername",
          "type": "string"
        },
        {
          "name": "headername2",
          "type": "date"
        }
      ]
  },
 "result": 
   {
     "data_array":[
           [
             "row1header1value",
             "row1header2value"
           ],
           [
             "row2header1value",
             "row2header2value"
           ]
         ]
   }
}

#API, #databricks, #json

naveed_raza · ‎09-12-25

finally i have written the code to convert into table format,

here is sample Databricks API JSON output ( i have removed un wanted columns from JSON and kept only required one)

{
    "manifest": {
        "schema": {
            "columns":
            [{
                    "name": "headername",
                    "type_name": "string"
                }, {
                    "name": "headername2",
                    "type_name": "string"
                }
            ]
        }
    },
    "result": {
        "data_array": [
            [
                "row1header1value",
                "row1header2value"
            ],
            [
                "row2header1value",
                "row2header2value"
            ]
        ]
    }
}

here is my vb.net code, which i have written as a Global Code and using code staging calling this code by passing entire Databricks API JSON string as an input , and in output fetching Datatable and record count.

Note: make sure to add all necessary DLLs and namespace

Public Function ConvertDatabricksResultToDataTable(jsonStr As String) As DataTable

	Dim dt As New DataTable()

 ' Parse the JSON
	Dim jObj = JObject.Parse(jsonStr)

 ' Get Columns from manifest.schema.columns and adding to data table
	 Dim cols = jObj("manifest")("schema")("columns")

	 For Each col In cols
		 Dim colName As String = col("name").ToString()
		 Dim colType As String = col("type_name").ToString()

		' map Databricks type to VB.NET type
		 Select Case colType.ToUpper()
			Case "LONG", "BIGINT"
				dt.Columns.Add(colName, GetType(Long))
			Case "INTEGER", "INT"
				dt.Columns.Add(colName, GetType(Integer))
			Case "DECIMAL", "DOUBLE", "FLOAT"
				dt.Columns.Add(colName, GetType(Decimal))
			Case "DATE", "TIMESTAMP", "DATETIME"
				dt.Columns.Add(colName, GetType(Date))
			Case "BOOLEAN", "BOOL"
				dt.Columns.Add(colName, GetType(Boolean))
			Case Else
				dt.Columns.Add(colName, GetType(String))
		 End Select
	 Next

 ' Looping each record and addings rows from result.data_array
	Dim rows = jObj("result")("data_array")

	For Each r In rows
		Dim arr As New List(Of Object)

		For i = 0 To dt.Columns.Count - 1
			Dim value = r(i).ToString()

			' Convert data types per column
			Dim colType = dt.Columns(i).DataType

			If colType Is GetType(Long) Then
				arr.Add(Long.Parse(value))
			ElseIf colType Is GetType(Integer) Then
				arr.Add(Integer.Parse(value))
			ElseIf colType Is GetType(Decimal) Then
				arr.Add(Decimal.Parse(value))
			ElseIf colType Is GetType(Date) Then
				Dim d as Date
				If Date.TryParseExact(value,{"yyyy-MM-dd","yyyy-MM-ddTHH:mm:ss","yyyy-MM-ddTHH:mm:ssZ"},System.Globalization.CultureInfo.InvariantCulture,Globalization.DateTimeStyles.None,d) then
					arr.Add(d)
				else
					arr.Add(DBNull.Value)
				End if
			ElseIf colType Is GetType(Boolean) Then
				Select Case value.ToString().Trim().ToLower()
					Case "true", "1", "yes"
						arr.Add(True)
					Case "false", "0", "no"
						arr.Add(False)
					Case Else
						arr.Add(DBNull.Value)
				End Select
			Else
				arr.Add(value) ' fallback string
			End If
		Next

		dt.Rows.Add(arr.ToArray())
	Next

	Return dt

End Function

Please do let us know, if this solution is best to go or can we do it in any other better way. Hope this will help for others also.

Regards,

Mohammad Naveed

View answer in original post

naveed_raza · ‎08-12-25

i reviewed other options like OLEDB and ODBC,

Looks like using OLEDB we can't connect to Databricks, and using ODBC , we have to do some work around and for that looks like we have install Databricks odbc driver and do setup in bots to make this callable so that will get data in proper table format.

by seeing this , i have to go with Databricks API only and have to found a way to convert that JSON in proper table format

naveed_raza · ‎09-12-25

i thought of storing columns collection and data collection separately and doing loop for data , but thinking looping is not the good idea here.

i am writing VB.Net code to convert this into proper table format, will share the code here once its done

SayeedBinAbdullah · ‎09-12-25

Hi @naveed_raza

Have you tried Utility - JSON VBO->JSON to Collection

Best Regards,

Sayeed Bin Abdullah

naveed_raza · ‎09-12-25

Using JSON Utility , it convert in table, however one column we have nested collection and another column will have data collection.

One Table will have 2 columns , and each column will have nested collection, headers in one column, actual data in another

naveed_raza · ‎09-12-25

@SayeedBinAbdullah - this is how its showing , if we use JSON utility

schema column will have columns , and result column will have actual data row. i am done with coding, testing , will share the code shortly.

naveed_raza · ‎09-12-25

finally i have written the code to convert into table format,

here is sample Databricks API JSON output ( i have removed un wanted columns from JSON and kept only required one)

{
    "manifest": {
        "schema": {
            "columns":
            [{
                    "name": "headername",
                    "type_name": "string"
                }, {
                    "name": "headername2",
                    "type_name": "string"
                }
            ]
        }
    },
    "result": {
        "data_array": [
            [
                "row1header1value",
                "row1header2value"
            ],
            [
                "row2header1value",
                "row2header2value"
            ]
        ]
    }
}

here is my vb.net code, which i have written as a Global Code and using code staging calling this code by passing entire Databricks API JSON string as an input , and in output fetching Datatable and record count.

Note: make sure to add all necessary DLLs and namespace

Public Function ConvertDatabricksResultToDataTable(jsonStr As String) As DataTable

	Dim dt As New DataTable()

 ' Parse the JSON
	Dim jObj = JObject.Parse(jsonStr)

 ' Get Columns from manifest.schema.columns and adding to data table
	 Dim cols = jObj("manifest")("schema")("columns")

	 For Each col In cols
		 Dim colName As String = col("name").ToString()
		 Dim colType As String = col("type_name").ToString()

		' map Databricks type to VB.NET type
		 Select Case colType.ToUpper()
			Case "LONG", "BIGINT"
				dt.Columns.Add(colName, GetType(Long))
			Case "INTEGER", "INT"
				dt.Columns.Add(colName, GetType(Integer))
			Case "DECIMAL", "DOUBLE", "FLOAT"
				dt.Columns.Add(colName, GetType(Decimal))
			Case "DATE", "TIMESTAMP", "DATETIME"
				dt.Columns.Add(colName, GetType(Date))
			Case "BOOLEAN", "BOOL"
				dt.Columns.Add(colName, GetType(Boolean))
			Case Else
				dt.Columns.Add(colName, GetType(String))
		 End Select
	 Next

 ' Looping each record and addings rows from result.data_array
	Dim rows = jObj("result")("data_array")

	For Each r In rows
		Dim arr As New List(Of Object)

		For i = 0 To dt.Columns.Count - 1
			Dim value = r(i).ToString()

			' Convert data types per column
			Dim colType = dt.Columns(i).DataType

			If colType Is GetType(Long) Then
				arr.Add(Long.Parse(value))
			ElseIf colType Is GetType(Integer) Then
				arr.Add(Integer.Parse(value))
			ElseIf colType Is GetType(Decimal) Then
				arr.Add(Decimal.Parse(value))
			ElseIf colType Is GetType(Date) Then
				Dim d as Date
				If Date.TryParseExact(value,{"yyyy-MM-dd","yyyy-MM-ddTHH:mm:ss","yyyy-MM-ddTHH:mm:ssZ"},System.Globalization.CultureInfo.InvariantCulture,Globalization.DateTimeStyles.None,d) then
					arr.Add(d)
				else
					arr.Add(DBNull.Value)
				End if
			ElseIf colType Is GetType(Boolean) Then
				Select Case value.ToString().Trim().ToLower()
					Case "true", "1", "yes"
						arr.Add(True)
					Case "false", "0", "no"
						arr.Add(False)
					Case Else
						arr.Add(DBNull.Value)
				End Select
			Else
				arr.Add(value) ' fallback string
			End If
		Next

		dt.Rows.Add(arr.ToArray())
	Next

	Return dt

End Function

Please do let us know, if this solution is best to go or can we do it in any other better way. Hope this will help for others also.

Regards,

Mohammad Naveed

SayeedBinAbdullah · ‎10-12-25

Hi Naveed,

Can you please list down all the dll's required?

naveed_raza · ‎10-12-25

Hi Sayeed,

for me its hard to tell , as here i have created a reusable object (RPA Utility) which has all the actions which is not there in regular VBOs and i have added all possible DLLs to my object by default.

mostly NewtonJSON and other basic DLLs, here is my list of DLLs which i added.

simone.tomaCNH · Monday

Hi all,

If it helps, I created a reusable object that runs a query on Databricks (there are two actions, one for a personal token, the other for a token generated by Service Principal). Here is the code:

<type>Application</type>

<basetype>Application</basetype>

<datatype>unknown</datatype>

<diagnose>False</diagnose>

</element>

</appdef>

<view>

</view>

<name>Clean Up</name>

<view>

</view>

</subsheet>

<name>Authentication</name>

<view>

</view>

</subsheet>

<name>Execute Query</name>

<view>

</view>

</subsheet>

<name>Execute Query - Using Token</name>

<view>

</view>

</subsheet>

</stage>

</stage>

<reference>System.dll</reference>

<reference>System.Data.dll</reference>

<reference>System.Xml.dll</reference>

<reference>System.Drawing.dll</reference>

<reference>System.Net.Http.dll</reference>

<reference>EPPlus.dll</reference>

<reference>ClosedXML.dll</reference>

<reference>netstandard.dll</reference>

<reference>System.Core.dll</reference>

<reference>System.Net.Http.Json.dll</reference>

<reference>Newtonsoft.Json.dll</reference>

<reference>System.Windows.Forms.dll</reference>

<reference>System.Net.Http.dll</reference>

</references>

<import>System</import>

<import>System.Drawing</import>

<import>System.Data</import>

<import>System.Net.Http</import>

<import>System.Text</import>

<import>ClosedXML.Excel</import>

<import>OfficeOpenXml</import>

<import>System.Collections.Generic</import>

<import>System.Linq</import>

<import>System.IO</import>

<import>Newtonsoft.Json</import>

<import>Newtonsoft.Json.Linq</import>

<import>System.Windows.Forms</import>

<import>System.Net.Http.Headers</import>

</imports>

<language>csharp</language>

<code><![CDATA[public class DataTableToExcel

{

public void ExportToExcel(DataTable dataTable, string filePath, string sheet)

{

// Configura EPPlus per usare la licenza non commerciale

using (var package = new ExcelPackage())

{

var worksheet = package.Workbook.Worksheets.Add(sheet);

// Aggiungi le intestazioni delle colonne

for (int col = 0; col < dataTable.Columns.Count; col++)

{

worksheet.Cells[1, col + 1].Value = dataTable.Columns[col].ColumnName;

}

// Aggiungi i dati delle righe

worksheet.Cells["A2"].LoadFromDataTable(dataTable, false);

// Salva il file Excel

var fileInfo = new FileInfo(filePath);

package.SaveAs(fileInfo);

}

public class Column

{

public string name { get; set; }

}

public class Row

{

public List<string> values { get; set; }

}

public class JsonResponse

{

public List<Column> columns { get; set; }

public List<Row> rows { get; set; }

}

public static DataTable GenerateDataTableFromJson(string json)

{

JObject jsonObject = JObject.Parse(json);

JArray columns = (JArray)jsonObject["manifest"]["schema"]["columns"];

JArray dataArray = (JArray)jsonObject["result"]["data_array"];

DataTable dataTable = new DataTable();

foreach (var column in columns)

{

dataTable.Columns.Add(column["name"].ToString(), typeof(string));

}

foreach (var row in dataArray)

{

DataRow dataRow = dataTable.NewRow();

for (int i = 0; i < columns.Count; i++)

{

dataRow[i] = row[i].ToString();

}

dataTable.Rows.Add(dataRow);

}

return dataTable;

}

public static string EscapeForJson(string s)

{

if (s == null) return "";

return s.Replace("\\", "\\\\").Replace("\"", "\\\"");

}

]]></code>

</stage>

</stage>

</stage>

</stage>

</stage>

</inputs>

</stage>

</outputs>

</stage>

<narrative>Clean Up Page

This is an optional page where you might choose to perform some finalisation (or "cleanup") tasks as your business object is closed down.

The cleanup action will be called automatically immediately after closing your business object at the end of a business process.

You will not be able to call this action from a business process, nor will it be called at any other time than before the disposal of the business object.</narrative>

</stage>

<narrative>Initialise Page

This is an optional page where you might choose to perform some initialisation tasks after your business object is loaded.

The initialise action will be called automatically immediately after loading your business object.

You will not be able to call this action from a business process, nor will it be called at any other time than after the creation of the object.</narrative>

</stage>

</inputs>

</outputs>

<code><![CDATA[Success = false;

ErrorMessage = "";

JSONOutput = "";

HttpClient client = new HttpClient();

HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, baseUrl)

{

Content = new StringContent(

$"{{\"statement\":\"{sqlQuery}\", \"warehouse_id\":\"{warehouseId}\", \"timeout_seconds\":600}}",

Encoding.UTF8,

"application/json"

)

};

request.Headers.Add("Authorization", $"Bearer {accessToken}"); // Invia la richiesta

HttpResponseMessage response = client.SendAsync(request).Result;

if (response.IsSuccessStatusCode)

{

string responseBody = response.Content.ReadAsStringAsync().Result;

Success = true;

JSONOutput = responseBody;

}

else

{

string responseBody = response.Content.ReadAsStringAsync().Result;

ErrorMessage = responseBody;

}

]]></code>

</stage>

<narrative>https://westeurope.azuredatabricks.net/api/2.0/sql/statements</narrative>

</stage>

</stage>

</stage>

</stage>

<initialvalue>False</initialvalue>

</stage>

</stage>

</stage>

<datatype>collection</datatype>

</stage>

</inputs>

</outputs>

<code><![CDATA[Success = false;

ErrorMessage = "";

dataTable = null;

dataTable = new DataTable();

try

{

dataTable = GenerateDataTableFromJson(json);

Success = true;

}

catch (Exception ex)

{

Success = false;

ErrorMessage = ex.Message;

}

]]></code>

</stage>

</stage>

</stage>

</stage>

</stage>

<initialvalue>False</initialvalue>

</stage>

</inputs>

</outputs>

<code><![CDATA[Success = false;

ErrorMessage = "";

try

{

DataTableToExcel exporter = new DataTableToExcel();

exporter.ExportToExcel(DatatableResult, Filepath, SheetName);

Success = true;

}

catch (Exception ex)

{

Success = false;

ErrorMessage = ex.Message;

}]]></code>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</outputs>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

<datatype>password</datatype>

</initialvalueenc>

</stage>

</inputs>

</outputs>

<code><![CDATA[

string tokenUrl = $"https://login.microsoftonline.com/{TenantID}/oauth2/v2.0/token";

using (var httpClient = new HttpClient())

{

var form = new FormUrlEncodedContent(new[]

{

new KeyValuePair<string, string>("client_id", ClientId),

new KeyValuePair<string, string>("client_secret", ClientSecret),

new KeyValuePair<string, string>("grant_type", "client_credentials"),

// Scope corretto per Azure Databricks (AAD resource + /.default)

new KeyValuePair<string, string>("scope", "2ff814a6-3304-4ab8-85cb-cd0e6f879c1d/.default")

});

var resp = httpClient.PostAsync(tokenUrl, form).GetAwaiter().GetResult();

string body = resp.Content.ReadAsStringAsync().GetAwaiter().GetResult();

if (!resp.IsSuccessStatusCode)

{

throw new Exception($"Token request failed ({resp.StatusCode}): {body}");

}

var json = JObject.Parse(body);

string accessToken = json["access_token"]?.ToString();

if (string.IsNullOrEmpty(accessToken))

throw new Exception("access_token missing in token response");

AccessToken = accessToken;

}

]]></code>

</stage>

</stage>

</stage>

</inputs>

</stage>

</outputs>

</stage>

</stage>

</stage>

</stage>

<initialvalue>False</initialvalue>

</stage>

</stage>

</stage>

<datatype>collection</datatype>

</stage>

</inputs>

</outputs>

<code><![CDATA[Success = false;

ErrorMessage = "";

dataTable = null;

dataTable = new DataTable();

try

{

dataTable = GenerateDataTableFromJson(json);

Success = true;

}

catch (Exception ex)

{

Success = false;

ErrorMessage = ex.Message;

}

]]></code>

</stage>

</stage>

</stage>

</stage>

</stage>

<initialvalue>False</initialvalue>

</stage>

</inputs>

</outputs>

<code><![CDATA[Success = false;

ErrorMessage = "";

try

{

DataTableToExcel exporter = new DataTableToExcel();

exporter.ExportToExcel(DatatableResult, Filepath, SheetName);

Success = true;

}

catch (Exception ex)

{

Success = false;

ErrorMessage = ex.Message;

}]]></code>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

<onsuccess>0f46bfec-9fc3-4b45-9073-b2a2feebbbe3</onsuccess>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</stage>

</inputs>

</outputs>

<code><![CDATA[Success = false;

ErrorMessage = "";

JSONOutput = "";

try

{

using (var httpClient = new HttpClient())

{

httpClient.DefaultRequestHeaders.Add("Authorization", $"Bearer {accessToken}");

var payload = new

{

statement = sqlQuery,

warehouse_id = warehouseId,

wait_timeout = "30s"

};

var content = new StringContent(Newtonsoft.Json.JsonConvert.SerializeObject(payload), Encoding.UTF8, "application/json");

var resp = httpClient.PostAsync(databricksUrl, content).GetAwaiter().GetResult();

string body = resp.Content.ReadAsStringAsync().GetAwaiter().GetResult();

if (!resp.IsSuccessStatusCode)

{

throw new Exception($"Query failed ({resp.StatusCode}): {body}");

}

else

{

JSONOutput = body;

Success = true;

}

catch (Exception ex)

{

Success = false;

ErrorMessage = ex.ToString();

}

]]></code>

</stage>

<narrative>https://adb-<workspace-id>.<shard>.azuredatabricks.net/api/2.0/sql/statements</narrative>

</stage>

</outputs>

</stage>

</process>

Simone

SS&C Blue Prism Community

Databricks API JSON Result to Collection