topic Databricks Rest api swagger definition not handling bigint or integer in Data Engineering

Databricks Rest api swagger definition not handling bigint or integer

DanielW — Thu, 15 May 2025 12:48:30 GMT

I want to test create a custom connector in a Power App that connects to table in Databricks.

The issue is if I have any columns like int or bigint.

No matter what I define in the response in my swagger definition See below), it is not correct type in the Power App so cannot be displayed. string columns are displayed ok.

testint: type: number testint: type: integer format: int64 testint: type: string

Re: Databricks Rest api swagger definition not handling bigint or integer

lingareddy_Alva — Thu, 15 May 2025 13:43:28 GMT

Hi @DanielW

This is a common issue when connecting Power Apps to Databricks due to how data types are handled between the systems.
Here's how to resolve it:

When Databricks returns numeric types (int, bigint) through an API, they're being serialized as strings in the JSON response,
but your Power Apps connector expects them as numeric types according to your Swagger definition.

Solution
You need to adjust your Swagger definition to properly handle the type conversion. Try these approaches:
Option 1: Update your Swagger definition to expect strings

testint:
type: string
x-ms-powerApps-mapping:
powerAppsType: number

Option 2: Modify your Databricks API response
If you're writing a custom API endpoint in Databricks, ensure you're explicitly casting the numeric values:

# Example in Python
def get_data():
df = spark.sql("SELECT * FROM your_table")
# Convert numeric columns to actual numbers in the JSON
return df.toJSON().map(lambda x: json.loads(x)).collect()

Option 3: Create a transformation policy
In your custom connector, add a response transformation policy:
-- Go to your custom connector definition
-- Add a policy under "Response transformation"
-- Use a policy like:
in javascript&colon;
#set($body = $response.body)
#foreach($item in $body.items)
#set($item.testint = $number.parseInt($item.testint))
#set($item.bigint = $number.parseInt($item.bigint))
#end

Best Practice Implementation
For the most reliable approach:
1. In your Swagger definition, define numeric columns as strings:

in yaml:

testint:
type: string
description: "Integer value (returned as string)"

2. In your Power App, use the Value() function to convert the string to a number when needed:
Value(YourDataSource.testint)

This approach handles the type mismatch by acknowledging that the values come as strings but can be
processed as numbers within Power Apps.

Re: Databricks Rest api swagger definition not handling bigint or integer

DanielW — Thu, 15 May 2025 13:53:07 GMT

@lingareddy_Alva Thanks very much for your reply , I will test out the options for the integer and bigint columns. For a number column I made mistake defining the column in yaml .

testnumber: format: number

Although the power app complains this is untyped , it displays it perfectly. Indeed I added a number to value and this was correctly displayed. So is this an option for my integer and big int columns

Re: Databricks Rest api swagger definition not handling bigint or integer

lingareddy_Alva — Thu, 15 May 2025 14:26:20 GMT

@DanielW

You're welcome! Yes, that's absolutely an option for your integer and bigint columns. Using format: number without explicitly specifying a type can work well for handling numeric values from Databricks in Power Apps.

Here's how you could define those columns in your Swagger:

In yaml file

testint:
format: number
description: "Integer column from Databricks"

bigintcolumn:
format: number
description: "BigInt column from Databricks"

If you're still getting any issues with very large numbers (particularly with bigint columns that
might exceed JavaScript's number precision), you could also try:

bigintcolumn:
type: string
format: number
x-ms-powerApps-display-format: number

This hybrid approach tells Power Apps to treat the value as a number for display purposes while still accepting the string representation from the API.
I'm glad to hear the format: number approach is already working for your number columns! This should work equally well for your integer and bigint columns as long as they're within the standard numeric range that JavaScript/Power
Apps can handle (approximately ±9,007,199,254,740,991).

Re: Databricks Rest api swagger definition not handling bigint or integer

DanielW — Thu, 15 May 2025 15:12:06 GMT

Hi @lingareddy_Alva

Unfortunately the swagger defintions alone won't solve my issues

testint:
type: string
format: number
x-ms-powerApps-mapping:powerAppsType: number

Power apps error: JSON parsing error , expected a number and got a string

testint:
format: number
x-ms-powerApps-mapping:powerAppsType: number

Power apps error: JSON parsing error , expected a number and got a string

Hi @lingareddy_Alva

Unfortunately the swagger defintions alone won't solve my issues

testint:
type: string
format: number
x-ms-powerApps-mapping:powerAppsType: number

Power apps error: JSON parsing error , expected a number and got a string

testint:
format: number

Power apps error: JSON parsing error , expected a number and got a string

So only testnumber works with just the format definition in my YAML.

Re: Databricks Rest api swagger definition not handling bigint or integer

lingareddy_Alva — Thu, 15 May 2025 17:42:21 GMT

@DanielW

Thank you for the update. I understand the Swagger definitions alone aren't resolving your type conversion issues.
This sounds like a deeper problem with how the data is being processed between Databricks and Power Apps.

Option 1: Transform the data in your API endpoint
Option 2: Use a custom connector policy script
Option 3: Create a middleware API
Option 4: Power Automate flow as middleware

Re: Databricks Rest api swagger definition not handling bigint or integer

DanielW — Fri, 16 May 2025 07:49:36 GMT

Hi @lingareddy_Alva ,

Thanks for your help yesterday and advice on the options I should look at and their order of impact.

Taking Option 2, I think the code you reference will end up in the Code section and not the Policy , as MS seemed to have limited what I can do under Policy details

Re: Databricks Rest api swagger definition not handling bigint or integer

DanielW — Fri, 16 May 2025 11:51:55 GMT

@lingareddy_Alva

Quick update. I have populated a collection in the Power App with all column values without the policy or changes to code snippet. I had to close and re-open the power app for the amended custom connector to take effect.

Thanks once again for your help.

test_bigintcolumn1: format: number description: Integer column from Databricks test_bigintcolumn2: format: number description: Integer column from Databricks

Re: Databricks Rest api swagger definition not handling bigint or integer

lingareddy_Alva — Fri, 16 May 2025 12:40:20 GMT

You are welcome !! @DanielW

Re: Databricks Rest api swagger definition not handling bigint or integer

lingareddy_Alva — Fri, 16 May 2025 12:41:13 GMT

Awesome @DanielW

Re: Databricks Rest api swagger definition not handling bigint or integer

DanielW — Fri, 16 May 2025 15:36:17 GMT

Hi probably one to pick up next week. but I attempted to parametise me SQL statement and it was painful!

parameters: - name: body in: body required: true schema: type: object properties: warehouse_id: type: string description: The ID of the warehouse to retrieve. default: <warehouse_id> x-ms-visibility: important statement: type: string description: SQL statement to execute. default: > SELECT * FROM cycle_shops WHERE shop_id = :shop_id x-ms-visibility: important shop_id: type: string description: The ID of the shop I to filter by. wait_timeout: type: string description: Timeout for the SQL execution. default: 5s

Re: Databricks Rest api swagger definition not handling bigint or integer

DanielW — Mon, 19 May 2025 08:53:42 GMT

Actually, when you recheck the docs , there is the answer

parameters: - name: body in: body required: true schema: type: object properties: warehouse_id: type: string description: The ID of the shop to retrieve. default: <warehouse_id> x-ms-visibility: important statement: type: string description: SQL statement to execute. default: > SELECT * FROM cycle_shops WHERE shop_id = :shop_id x-ms-visibility: important parameters: type: array description: List of SQL parameters. items: type: object properties: name: type: string description: The name of the SQL parameter. example: shop_id value: type: string description: The value for the SQL parameter. example: '10'

Re: Databricks Rest api swagger definition not handling bigint or integer

DanielW — Fri, 23 May 2025 09:26:32 GMT

Hi @lingareddy_Alva

This might warrant another post to keep the conversation focussed, but I found a couple of things with the custom connector that make it a bit cumbersome to use.

1) I don't seem to be able to have two post operations under /statments so I can't have SetShopDetails . I am looking at use a HTTP PUT but did see some errors when I tired this week

paths: /statements: post: summary: GetShopDetails operationId: GetShopDetails

2) You can't see define a generic response in the YAML so keet the custom connector very flexible. The idea would be the Power App would have figure out how to handle the response.

responses: '200': description: Successfully retrieved shop details. schema: type: object properties: results: type: array items: type: object

SO you have define the reponse array for each SELECT request. Indeed with 1) we are looking at having to create a connector per call to a table. Not a great dev experience.