Query Azure Data Lake Storage Data through ODBC in Node.js

Node.js is a JavaScript runtime environment that allows you to run JavaScript code outside of a browser. With the CData ODBC Driver for Azure Data Lake Storage, you can access live Azure Data Lake Storage data from Node.js apps and scripts. In this article, we walk through installing node-odbc and the required tools to create a simple Node.js app with access to live Azure Data Lake Storage data.

With built-in optimized data processing, the CData ODBC Driver offers unmatched performance for interacting with live Azure Data Lake Storage data in Node.js. When you issue complex SQL queries from Node.js to Azure Data Lake Storage, the driver pushes supported SQL operations, like filters and aggregations, directly to Azure Data Lake Storage and utilizes the embedded SQL engine to process unsupported operations client-side (often SQL functions and JOIN operations).

Connecting to Azure Data Lake Storage Data

If you have not already done so, provide values for the required connection properties in the data source name (DSN). You can use the built-in Microsoft ODBC Data Source Administrator to configure the DSN. This is also the last step of the driver installation. See the "Getting Started" chapter in the help documentation for a guide to using the Microsoft ODBC Data Source Administrator to create and configure a DSN.

Authenticating to a Gen 1 DataLakeStore Account

Gen 1 uses OAuth 2.0 in Azure AD for authentication.

For this, an Active Directory web application is required. You can create one as follows:

1. Sign in to your Azure Account through the .
2. Select "Azure Active Directory".
3. Select "App registrations".
4. Select "New application registration".
5. Provide a name and URL for the application. Select Web app for the type of application you want to create.
6. Select "Required permissions" and change the required permissions for this app. At a minimum, "Azure Data Lake" and "Windows Azure Service Management API" are required.
7. Select "Key" and generate a new key. Add a description, a duration, and take note of the generated key. You won't be able to see it again.

To authenticate against a Gen 1 DataLakeStore account, the following properties are required:

• Schema: Set this to ADLSGen1.
• Account: Set this to the name of the account.
• OAuthClientId: Set this to the application Id of the app you created.
• OAuthClientSecret: Set this to the key generated for the app you created.
• TenantId: Set this to the tenant Id. See the property for more information on how to acquire this.
• Directory: Set this to the path which will be used to store the replicated file. If not specified, the root directory will be used.

Authenticating to a Gen 2 DataLakeStore Account

To authenticate against a Gen 2 DataLakeStore account, the following properties are required:

• Schema: Set this to ADLSGen2.
• Account: Set this to the name of the account.
• FileSystem: Set this to the file system which will be used for this account.
• AccessKey: Set this to the access key which will be used to authenticate the calls to the API. See the property for more information on how to acquire this.
• Directory: Set this to the path which will be used to store the replicated file. If not specified, the root directory will be used.

Building node-odbc

In order to connect to Azure Data Lake Storage through the CData ODBC Driver, you need to build node-odbc manually (after installing the required tools).

Installing the Required Tools

The following commands install the tools required to build node-odbc (note the -g parameter, which installs the tools globally).

npm i -g windows-build-tools
npm i -g node-gyp

Building node-odbc

After installing the required tools, create a directory for the Node.js app and install odbc (which builds the binary for us to use in our Node.js script).

mkdir nodeodbc
cd nodeodbc
npm i -g node

Querying Azure Data Lake Storage from Node.js

With the ODBC Driver installed, a DSN Configured, and node-odbc built, we are ready to query live Azure Data Lake Storage data from a Node.js app. The sample code below connects to a specific DSN and queries the Resources table.

myscript.js

const odbc = require('odbc');

async function queryADLS() {
    const connection = await odbc.connect(`DSN=CData ADLS Source`);
    const data = await connection.query('SELECT FullPath, Permission FROM Resources');
    console.log(data);
}

queryADLS();

Once you write the app, use node to execute the script:

node myscript.js

Download a free, 30-day trial of the CData ODBC Driver for Azure Data Lake Storage and start working with your live Azure Data Lake Storage data in Node.js. Reach out to our Support Team if you have any questions.