Databricks Rest api swagger definition not handling bigint or integer

[DanielW]

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

[lingareddy_Alva]

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:
#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.

[DanielW]

@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

[lingareddy_Alva · Accepted solution]

@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).

[DanielW]

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.

[lingareddy_Alva]

@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

[DanielW]

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

[lingareddy_Alva]

You are welcome !! @DanielW 

[DanielW]

@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

[lingareddy_Alva]

Awesome @DanielW 

[DanielW]

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

[DanielW]

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' 

[DanielW]

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.