Query Snowflake Data from Node.js

The CData API Server, when paired with the Snowflake connector (or any of over 300 supported data sources), exposes Snowflake data as an OData web service that can be queried from Node.js using simple HTTP requests. This article walks you through setting up the CData API Server and demonstrates the process to retrieve JSON-formatted Snowflake data in Node.js.

Getting Started

---

Setting Up the API Server

If you haven't already, download and install the CData API Server. After installation, run the application, download the Snowflake connector from within the API Server, and configure your Snowflake data connection. Then, configure API Server to generate OData feeds for any tables you want to access in your single-page application (SPA).

Enable CORS

AngularJS requires servers to have CORS (Cross-Origin Resource Sharing) enabled. To enable CORS, go to the Server tab under the Settings section in the API Server and adjust the following settings:

• Click the checkbox to Enable cross-origin resource sharing (CORS).
• Either click the checkbox to Allow all domains without '*' or specify the domain(s) that are allowed to connect in Access-Control-Allow-Origin.
• Set Access-Control-Allow-Methods to "GET, PUT, POST, OPTIONS".
• Set Access-Control-Allow-Headers to "Content-Type, Authorization".
• Click Save to save the changes made.

Connect to Snowflake

To work with Snowflake data using AngularJS, we start by creating and configuring a Snowflake connection. Follow the steps below to configure the API Server to connect to Snowflake data:

1. First, navigate to the Connections page.
2. Click Add Connection and then search for and select the Snowflake connection.
3. Enter the necessary authentication properties to connect to Snowflake.
4. After configuring the connection, click Save & Test to confirm a successful connection.

Configure API Server Users

Next, create a user to access your Snowflake data through the API Server. You can add and configure users on the Users page. Follow the steps below to configure and create a user:

1. On the Users page, click Add User to open the Add User dialog.
2. Next, set the Role, Username, and Privileges properties and then click Add User.
3. An Authtoken is then generated for the user. You can find the Authtoken and other information for each user on the Users page:

Creating API Endpoints for Snowflake

Having created a user, you are ready to create API endpoints for the Snowflake tables:

1. First, navigate to the API page and then click Add Table .
2. Select the connection you wish to access and click Next.
3. With the connection selected, create endpoints by selecting each table and then clicking Confirm.

Gather the OData URL

Having configured a connection to Snowflake data, created a user, and added resources to the API Server, you now have an easily accessible REST API based on the OData protocol for those resources. From the API page in API Server, you can view and copy the API Endpoints for the API:

Just like with standard OData feeds, you can limit the fields returned by adding a $select parameter to the query. You can also include other standard OData URL parameters, such as $filter, $orderby, $skip, and $top. Refer to the help documentation for more details on supported OData queries.

Save the Authtoken

Select the user you created, then copy and save the authtoken for future use. You can also toggle on 'Token Expiration' and set the number of days based on your use case. Click Save to save the details.

Consume Snowflake OData feeds using Node.js

OData feeds provide a standardized way to access data and can be easily consumed in Node.js. Node.js scripts use the built-in http module to make requests to the CData API Server, which acts as an intermediary to expose Snowflake data as OData. The process involves constructing HTTP GET requests to the API Server's OData endpoints, handling the responses, and parsing the JSON data.

To get started, we'll create a new Node.js project and install the necessary packages to run it.

Create a Node.js Application

1. Initialize a Node.js project on your system (e.g., D: drive) using a Terminal or Command Prompt. Make sure to run it as an Administrator.

   cd D:\
   mkdir my-odata-app
   cd my-odata-app
   npm init -y
   

2. Now, install the required packages.

   npm install http
   

Fetching data by ID

In this section, we'll work on retrieving specific records from a Snowflake entity by filtering based on a unique identifier using the app_data.js script.

• Constructing the filtered request: The http.get() method is used to send a GET request to the CData API Server. The path option includes an OData $filter clause: /api.rsc/DEMO_DB_CRM_Account?$filter= + encodeURIComponent("Id eq '" + id + "'"). This filter tells the API Server to only return the record where the Id field matches the provided id value. The encodeURIComponent() function ensures that the filter expression is properly encoded for inclusion in the URL, handling any special characters. The auth option provides authentication credentials to the API Server.
• Processing the response: The script sets up event listeners on the response object (res). The data event listener accumulates the response body in the body variable. Once the entire response is received (the end event), the body is parsed as JSON using JSON.parse(), and the resulting JavaScript object (jsonData) is then logged to the console. Error handling is included to catch and log any errors during the HTTP request.

app_data.js

var http = require('http');

// Function to fetch data based on a given Id
function fetchDataById(id) {
    http.get({
        protocol: "http:",
        hostname: "localhost",
        port: 8080,
        path: "/api.rsc/DEMO_DB_CRM_Account?$filter=" + encodeURIComponent("Id eq '" + id + "'"), // Updated URL with filter
        auth: 'your_username:your_authtoken'  // Enter your username and authtoken/password
    }, function (res) {
        var body = '';

        res.on('data', function (chunk) {
            body += chunk;
        });

        res.on('end', function () {
            var jsonData = JSON.parse(body);
            console.log(jsonData);
        });
    }).on('error', function (e) {
        console.log("Error: ", e);
    });
}

// Example usage: Fetch data for Id '11'
fetchDataById('11');

Retrieve the specific records for an unique identifier from the Snowflake entity, using the following command in the Terminal or Command Prompt:

node app_data.js

Retrieving all data from a Snowflake entity

The app_table.js script focuses on fetching all records from a specified Snowflake entity.

• Making the unfiltered request: Similar to app_data.js, http.get() is used, but this time the path option simply specifies the entity's endpoint: /api.rsc/DEMO_DB_CRM_Account. By omitting the $filter parameter, the API Server returns all available data from that entity. Again, the auth option is used for authentication.
• Handling and Displaying the Data: The response is handled in the same way as in app_data.js: the script listens for data events to build the response body, parses the JSON response in the end event, and logs the parsed JSON data (jsonData) to the console. The raw body is also logged before parsing. Error handling is included here as well.

app_table.js

var http = require('http');

http.get({
    protocol: "http:",
    hostname: "localhost",
    port: 8080,
    path: "/api.rsc/DEMO_DB_CRM_Account", // Updated URL to fetch data from DEMO_DB_CRM_Account
    auth: 'your_username:your_password'  // Enter your username and password/authtoken
}, function (res) {
    var body = '';

    res.on('data', function (chunk) {
        body += chunk;
    });

    res.on('end', function () {
        console.log(body);
        var jsonData = JSON.parse(body);
        console.log(jsonData);
    });
}).on('error', function (e) {
    console.log("Error: ", e);
});

Retrieve all the records from the Snowflake entity, using the following command:

node app_table.js

Free Trial & More Information

Accessing Snowflake data from Node.js can be greatly simplified by leveraging an OData API. CData API Server exposes Snowflake data as an OData service, enabling you to perform "Read" operations using standard HTTP requests. This allows you to work with your Snowflake data in a more structured and standardized way.