Building an Azure Functions App to return JSON results from Azure Synapse Analytics with Node.js

---

Dhyan Rathore

5–7 minutes

December 14, 2020

Azure, Azure Functions, Azure Synapse Analytics, JavaScript

---

Azure SQL Database and SQL Server 2016 (and later) supports an in-built feature for formatting query results as JSON. Then JSON support for Azure SQL data warehouse (currently Azure Synapse Analytics) was announced. Synapse Analytics supports querying and manipulating JSON data. However, there is no out-of-the-box support in Synapse to return SQL query results as JSON.

This article will create an HTTP triggered RESTful Azure Functions app with Node.js. This app will query Azure Synapse Analytics and convert and return the results as JSON.

Prerequisites

1. An active Microsoft Azure subscription
2. Azure Synapse Analytics data warehouse
3. Azure Functions App (Node.js)

Let’s reiterate how to get query results as JSON in SQL Database and Server. Add the FOR JSON clause to a SELECT statement to format query results as JSON, or export data from SQL Server as JSON. The structure of the JSON is defined explicitly. Let’s see it in action.

This serves well in SQL Database and Server but results in a syntax error in the Synapse Analytics. The straightforward way to get the JSON output is to convert the results to JSON explicitly.

Further reading on FOR JSON

Node.js is one of the most popular selections to create scalable, refined, high-performance REST API to be used by diverse clients. Azure Functions is another notable modern serverless computing service that developers use to create scalable solutions without worrying about the infrastructure setup.

Before jumping into the action, it’s essential to learn a few basics of developing a Node.js app in Azure Functions and Kudu’s role to install and manage the libraries. We covered all the important JavaScript concepts, Kudu, and step-by-step instructions for developing a Functions App in a previous article. We will be referring to these concepts and steps frequently. Take a quick glimpse before advancing.

Tedious

Tedious is a Node package used to interact with SQL Servers. Head over to your Function App’s Kudu, initialize the App and install the tedious package.

# npm init
# npm install tedious

We won’t get much into details of how to use Tedious as it is pretty simple. We have to follow the conventional pattern and a clever hack to get what we want:

• Connect to Synapse Analytics via the Connection object
• Execute command via the Request object
• Consume the resultset: convert to JSON

Tedious documentation

The Code

Head over to your function and paste the following snippet in the index.js. The snippet is self-explanatory and easily graspable. The link to the GitHub repo is given at the end of the article.

// Azure Function: Node.js code to read data from Azure Synapse Analytics with query parameter and return results as JSON
// Author: Dhyanendra Singh Rathore

// Import the tedious library
const Connection = require('tedious').Connection;
const Request = require('tedious').Request;
const TYPES = require('tedious').TYPES;

// Entry point of the function
module.exports = function(context, req) {

    // Define variables to store connection details and credentials
    // Connection details and credentials are fetched from Environment Variables during function execution
    const config = {
        server: process.env["SYNAPSE_SERVER_FQDN"],
        authentication: {
            type: 'default',
            options: {
                userName: process.env["SYNAPSE_USER"],
                password: process.env["SYNAPSE_USER_PASSWORD"],
            }
        },
        options: {
            encrypt: true,
            database: process.env["SYNAPSE_DATABASE"],
            port: 1433
        }
    };

    // Create Connection object
    const connection = new Connection(config);

    // Create array to store the query results
    let result = [];
    let rowData = {};

    // req.query.color will be passed as a Query variable in the URL
    const payload = [req.query.color];

    // Create query to execute against the database
    const queryText = "SELECT Color, COUNT(DISTINCT[ProductID]) as cnt FROM SalesLT.Product " + (payload[0] != undefined ? " WHERE Color IN ('" + payload[0] + "')" : "") + " GROUP BY Color ORDER BY cnt;";
    context.log(queryText);
    
    // Create Request object
    request = new Request(queryText, function(err) {
        if (err) {
            // Error in executing query
            context.log.error(err);
            context.res.status = 500;
            context.res.body = "Error executing the query";
        } else {
            context.res = {
                status: 200,
                isRaw: true,
                body: result,
                headers: {
                    'Content-Type': 'application/json'
                }
            }
        }
        context.done();
    });

    // Manipulate the results and create JSON
    request.on('row', function(columns) {
        rowData = {};
        columns.forEach(function(column) {
            // IMPORTANT: Change the conversion logic here to adjust JSON format
            rowData[column.metadata.colName] = column.value;
        });
        result.push(rowData);
    });

    connection.on('connect', function(err) {
        if (err) {
            // Error in connecting
            context.log.error(err);
            context.res.status = 500;
            context.res.body = "Error connecting to Azure Synapase";
            context.done();
        } else {
            // Connection succeeded
            connection.execSql(request);
        }
    });
}

Few important points worth mentioning here:

process.env

process.env["APPLICATION_SETTING_NAME"] is used to access the environment variables in our code. Environment variables make it possible to connect to resources and services without revealing connection secrets and credentials in plain text in our code. Environment variables can also refer to the secrets stored in an Azure Key Vault, thereby eliminating any accidental leakage.

req.query.color

Azure Functions accepts parameters passed either in the URL (aka query string) or the HTTP request body. These parameters are used to filter the results or perform business logic. We will use color to filter on the color of the products in our sample query. You can have as many parameters as you need.

context.done()

Tedious doesn’t support the async/await pattern, which is used by default by Azure Functions. If you try to use Tedious with the default Azure Function template, it won’t work.

// Default function
module.exports = async function(context, req) {
    /*
     * Logic here
     */
    responseMessage = {
        productId: 123,
        productName: "Her Delight"
    }
    context.res = {
        body: responseMessage
    };
}

Remove the async keyword from the function definition and then make sure the HTTP response is properly sent back by setting it in the context object provided by Azure Function runtime. Once the HTTP response is ready to be sent, call thecontext.done() to inform Azure Function that work is done.

// Function without async/await
module.exports = function(context, req) {
    /*
     * Logic here
     */
    responseMessage = {
        productId: 123,
        productName: "Her Delight"
    }
    context.res = {
        body: responseMessage
    };
    // Inform Azure Function that work is done
    context.done();
}

connection.execSql()

Tedious is loaded with several methods to execute your query against the SQL Server e.g. connection.callProcedure() to execute the stored procedures. It is recommended to go through the documentation and prefer the one which suits your needs the best.

Testing

Get the Functions URL and run it in a browser or a REST add-on. Alternatively, you can also test it in the Portal with the Test/Run option. Sample outputs with and without the color parameter are shown below.

That’s all, folks. We’ve created and tested a Node.js app to query and return JSON results from an Azure Synapse Analytics data warehouse. As promised, here’s the link to the GitHub repo for local development using VS Code. Have Fun!

Conclusion

We learned the shortcomings of Synapse Analytics to return results as JSON. We developed an Azure Functions App to query, convert, and return the query results as JSON.

Next Steps

It’s easy to make an Azure Functions app RESTful. Refer to our article for detailed how-to and step-by-step instructions with explanations

We talked about how credentials and secrets can be fetched from the Azure Key Vault as environment variables in functions. Read on to find out.