Implementing Separate Data Dictionaries for Azure Functions APIs in API Management with Azure SQL

hampton123 1,165

I have different APIs (Azure Functions Node js) in API Management accessing different SQL tables in Azure SQL. I have an HTML page used in my Azure Storage account (SPA using B2C) for users to login and access my APIs, and depending on their subscription key provided they will see and have access to certain APIs. My company wants different data dictionaries for each individual API, and they only want them to be accessible via the API the user is accessing. I was wondering how this could be done. Thank you in advance, and please let me know if more information is required.

Pramod Valavala 20,641 Reputation points Microsoft Employee

2023-10-31T16:56:00.12+00:00

@Hunter B Could you elaborate on what you mean by data dictionaries and how they are used in your solution? Is it a single large data file? Do they need to be loaded for every request?
hampton123 1,165 Reputation points

2023-10-31T17:53:00.2166667+00:00

Hi Pramod Valavala, sure I can elaborate! Sorry if I wasn't too clear on my initial question.

By data dictionary I mean that I want to have an excel document that provides information about the structure and content of the SQL data the API is accessing. They would be implemented into my SPA whenever users log into the API - the users will provide their subscription key through the URL and this will provide them with all of the APIs associated with that subscription key. They still log in through B2C but the subscription key contains what APIs they have access to. Each API will have a different data dictionary associated with them, so I want to have individual data dictionaries for each API within my system.

Thinking about it now I'm not sure if they would need to be loaded for every request - there would be a clickable link or button that when pressed it would download the data dictionary to the user's system. The button/link associated with the API's data dictionary would only appear with what APIs appear on the SPA that are associated with the subscription key the users provide. I hope that makes sense, however please let me know if anything needs clarification.

Accepted answer

Pramod Valavala 20,641 Reputation points Microsoft Employee

2023-11-03T14:43:24.33+00:00

@Hunter B Thanks! That does help a bit.

If I understand correctly, your frontend needs to fetch this dictionary based on their subscription key to render the UI accordingly. Also, your backend would need it to construct SQL queries on the fly.

If that is indeed the case, I would simple suggest storing this dictionary in blob storage and expose an endpoint on your APIM that fetches it for the frontend, and your backend function app would simply fetch (and ideally cache in memory) when a request comes in with a particular subscription key.

Also, if possible, it would be better to use a different format like JSON for the data dictionary as they are better supported by most languages without additional libraries.
Please sign in to rate this answer.

1 person found this answer helpful.
hampton123 1,165 Reputation points

2023-11-03T17:50:25.5266667+00:00

Hi @Pramod Valavala thank you for your response. You have the idea down 100%, you're completely correct!

So just to make sure I understand what you're saying, essentially I should have each data dictionary in blob storage (preferably in JSON format) correct? Then through my frontend UI I'll have something such as a clickable link or button available when the API matching the subscription key appears. The button will submit a request to my Azure Function in APIM which will send a download request for the data dictionary inside of the Azure Storage account that corresponds with the subscription key?

Pramod Valavala 20,641 Reputation points Microsoft Employee

2023-11-03T18:26:24.5066667+00:00

@Hunter B Yes. The exact flow is completely up to you. 🙂

You could even simplify it further by storing the data dictionary's filename as the subscription key itself (assuming you are using B2C to authorize users) and add an endpoint in APIM to download directly from blob storage, without even needing an Azure Function for this part.

Here is an example policy snippet you can use in a separate APIM endpoint to download from blob storage directly.

hampton123 1,165 Reputation points

2023-11-03T19:23:50.9233333+00:00

Thank you Pramod Valavala
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Implementing Separate Data Dictionaries for Azure Functions APIs in API Management with Azure SQL

0 additional answers

Your answer