Is there a way to get DAB running on top of Synapse Serverless SQL Pool

[kg (kg875)]

Hi,
Kind of an extension of this discussion: #1363

I wanted to understand if there is any workarounds for creating a GraphQL endpoint using DAB with a Synapse SQL Serverless Pool?

I've read in previous discussions that dedicated is not supported due to fact that DAB uses FOR JSON PATH, which serverless supports.

Unfortunately, my attempts so far have resulted in an error due to NO_BROWSETABLE not being supported:

Azure.DataApiBuilder.Service.Startup[0]
      Unable to complete runtime initialization. Refer to exception for error details.
      Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: 
Cannot obtain Schema for entity Products with underlying database object source: shop.products due to: NO_BROWSETABLE is not supported for SET.

Thanks!

[Sean Leonard (seantleonard)]

I opened an issue to track adding support for Synapse Serverless SQL Pool #1849

I don't have any work arounds at the moment but you are welcome to do some additional investigation to determine which schema resolving query is causing a failure in DAB due to some incompatibility:

I would suggest first setting a breakpoint and starting here:
https://github.com/Azure/data-api-builder/blob/7efa7f4823382e346205ab5e3617080ec9fb0bea/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs#L1227C3-L1234C33

            string tablePrefix = GetTablePrefix(conn.Database, schemaName);
            string queryPrefix = string.IsNullOrEmpty(tablePrefix) ? string.Empty : $"{tablePrefix}.";
            selectCommand.CommandText
                = $"SELECT * FROM {queryPrefix}{SqlQueryBuilder.QuoteIdentifier(tableName)}";
            adapterForTable.SelectCommand = selectCommand;


            DataTable[] dataTable = adapterForTable.FillSchema(EntitiesDataSet, SchemaType.Source, tableName);
            return dataTable[0];

[Aniruddh Munde (Aniruddh25)]

• rohkhann since he was getting a similar error

[Hari (HariprasadM-TomTom)]

Also getting the same error when trying to use "dab" over Synapse serverless pool. Tried with both external table and view.
info: Azure.DataApiBuilder.Core.Services.ISqlMetadataProvider[0] [Azure] REST path: /api/Azure fail: Azure.DataApiBuilder.Service.Startup[0] Unable to complete runtime initialization. Refer to exception for error details. Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Cannot obtain Schema for entity Azure with underlying database object source: dbo.azureinventorylive due to: NO_BROWSETABLE is not supported for SET.

[mahadevan-lakshminar]

Did this issue get any traction for enhancement?

We tried using "sqldw" as the database-type and the container booted up successfully but path "/api/temp2" gave a "503 - Service Unavailable" error.

[Aleksandar Stefanov (AleksandarStefanov-TomTom)]

@JerryNixon @yorek any plans to add support for Synapse Serverless?

[Jerry Nixon (JerryNixon)]

Hi everyone. There is currently no plan to support Synapse Serverless. Of course, since this is OSS, we are open to outside Pull Requests, but - if that is interesting to you - be sure and reach out to me before starting this work so we can plan for it appropriately. Meanwhile, these are the currently supported data types.

[Jason Dossett (jtdossett)]

Jerry Nixon (@JerryNixon), we are very interested in this. We would like to use DAB as our REST-on-top-of-Synapse strategy. I currently have a working POC that does two things:

• makes a change in SqlMetadataProvider.FillSchemaForTableAsync to effectively use cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly) instead of adapterForTable.FillSchema. This avoids the automatic SET NO_BROWSETABLE ON/OFF that ADO.NET wraps around the query.
• adds a convention-based requirement to include at least one key for DAB to use for each source entity. This avoids DAB trying to query for keys directly and not finding any and erroring. This could also potentially be addressed via another code change to make it Synapse-compatible, but this worked for our needs.

I am curious, because it's such a small change, if strategically something like this might be controlled via a flag on the mssql database type, maybe like '--serverless true' or '--avoid-no-browsetable' for example. I originally wondered about maybe a new database type (synssp?) but that would be a much bigger lift.

[himanshufreeman]

@jtdosset

Jerry Nixon (@JerryNixon), we are very interested in this. We would like to use DAB as our REST-on-top-of-Synapse strategy. I currently have a working POC that does two things:

• makes a change in SqlMetadataProvider.FillSchemaForTableAsync to effectively use cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly) instead of adapterForTable.FillSchema. This avoids the automatic SET NO_BROWSETABLE ON/OFF that ADO.NET wraps around the query.
• adds a convention-based requirement to include at least one key for DAB to use for each source entity. This avoids DAB trying to query for keys directly and not finding any and erroring. This could also potentially be addressed via another code change to make it Synapse-compatible, but this worked for our needs.

I am curious, because it's such a small change, if strategically something like this might be controlled via a flag on the mssql database type, maybe like '--serverless true' or '--avoid-no-browsetable' for example. I originally wondered about maybe a new database type (synssp?) but that would be a much bigger lift.

I was able to get around the NO_BROWSETABLE by using sqldw as provider instead of mssql. I ran into STRING_ESCAPE is not a recognized function in synapse dedicated SQL pool. Did you try with serverless pool or dedicated sql pool?

[Aleksandar Stefanov (tosokr)]

Jason Dossett (@jtdossett) can you share your POC? Thanks

[Jason Dossett (jtdossett)]

I've let my fork get out of sync, but here's the branch on my fork with the changes.