Connecting Denodo to Azure Cosmos DB

You can translate the document:

Goal

Azure Cosmos DB is Microsoft’s globally distributed multi-model database service. You can use Azure Cosmos DB to quickly create and query key/value databases, document databases, and graph databases, all of which benefit from the global distribution and horizontal scale capabilities at the core of Azure Cosmos DB.

This document explains how to connect Denodo to an Azure Cosmos DB SQL API account and how to use the Cosmos DB REST API in Linux or Kubernetes environments where the ODBC driver is not supported.

Connect Denodo to an Azure Cosmos DB SQL API account

Prerequisites

The recommended way to connect Denodo with Azure Cosmos DB SQL API account is using Denodo's ODBC connectivity capabilities

  1. Download and install the Microsoft Azure Cosmos DB ODBC Driver. Starting from  Denodo 8, the Denodo platform can be installed only on a 64-bit platform, so download the “Microsoft Azure Cosmos DB ODBC 64-bit.msi” driver. The ODBC driver can be downloaded from https://docs.microsoft.com/en-gb/azure/cosmos-db/odbc-driver.
  2. Create a new 64 bit DSN using the installed Microsoft Azure Cosmos DB ODBC driver. We need to use the data in the “Settings -> Keys” section of the Azure Portal.
  1. Specify host with this format: https://XXXXX.documents.azure.com:443/ The CosmosDB account appears in the URI.
  2. For the Access Key we must use the CosmosDB account PRIMARY KEY. We should use the Read-write keys.

Note: For versions prior to Denodo 8 If there are any issues while using a 64 bit ODBC driver, we recommend to follow the below steps:.

  1. For 64-bit Windows, download the “Microsoft Azure Cosmos DB ODBC 32x64-bit.msi for 32-bit on 64-bit Windows” driver.
  2. For 32-bit Windows, download the “Microsoft Azure Cosmos DB ODBC 32-bit.msi for 32-bit Windows” driver.
  3. Create a new 32 bit DSN using the installed Microsoft Azure Cosmos DB ODBC driver. Use the data in the “Settings -> Keys” section of the Azure Portal.
  1. Specify host with this format: https://XXXXX.documents.azure.com:443/ The CosmosDB account appears as URI.
  2. For the Access Key we must use the CosmosDB account PRIMARY KEY. We should use the Read-write keys.
  1. Install a 32 bit Denodo platform. If the current Denodo installation is for 64 bits, the recommended setup is using an additional Denodo 32 bit installation as a proxy. Note that this might require additional Denodo licenses. In the latter case it would be necessary to:
  1. Import the Cosmos DB desired tables into a 32 bit Denodo platform.
  2. Import the 32 bit Denodo platform as a new data source into the 64 bit Denodo platform. The Virtual DataPort adapter will be used.
  3. Import the desired tables from the 32 bit Denodo platform into the 64 bit Denodo platform.
  1. Make sure there is not a file called <DENODO_HOME>/lib/contrib/rt17.jar in the Denodo installation. If it exists, it must be deleted.

Connectivity

  1. From the Denodo Platform Control Center, append the following property to the JVM options of the Virtual DataPort Server: -XX:CompileCommand=exclude,sun/jdbc/odbc/*.*
  2. From the design studio, create a new ODBC data source by navigating to “File > New > Data source > ODBC”. Select the previously created CosmosDB DSN from the DSN drop down.
  3. Click Test connection to test the datasource connection and click save button to save the datasource.


  1. There is an additional step after the ODBC datasource is created. Edit the VQL of the the data source to set "cosmosdb" as DATABASENAME.  To make this change, copy the VQL from the VQL tab and modify the DATABASENAME to ‘cosmosdb’ . The modified VQL should be something like this:


CREATE OR REPLACE DATASOURCE ODBC ds_cosmosdb

    DSN = 'Cosmo DB'

    USERNAME = ''

    USERPASSWORD = ''

    CLASSPATH = 'jdbc-odbc'

    DATABASENAME = 'cosmosdb'

    DATABASEVERSION = '2021'

    FETCHSIZE = 1000

    VALIDATIONQUERY = 'Select 1'

    INITIALSIZE = 4

    MAXIDLE = -1

    MINIDLE = 0

    MAXACTIVE = 20

    EXHAUSTEDACTION = 1

    TESTONBORROW = true

    TESTONRETURN = false

    TESTWHILEIDLE = false

    TIMEBETWEENEVICTION = -1

    NUMTESTPEREVICTION = 3

    MINEVICTABLETIME = 1800000

    POOLPREPAREDSTATEMENTS = false

    MAXOPENPREPAREDSTATEMENTS = -1;

Execute the modified VQL in the VQL shell and refresh the design studio by navigating to File > Refresh.

  1. Disable the Datasource Connection Pool. For this we set the “Maximum number of active connections” to 0. This is recommended because the Azure Cosmos DB ODBC driver caches metadata about the existing tables at the connection level. This means that the tables added or modified after a connection has been opened, will not be visible to the clients using the connection. Disabling the connection pool ensures Denodo does not reuse connections so these problems do not appear.
  2. Now we can start creating base views over this CosmosDB data source.

Connect Cosmos DB REST API in Linux environments

The Azure Cosmos DB REST API allows applications to interact directly with Cosmos DB resources such as databases, containers, and documents using standard HTTP requests. This approach is especially useful in Linux or Kubernetes environments where ODBC or SDK drivers may not be available.

A typical Cosmos DB REST API request uses specific HTTP headers for authorization:

GET https://{account}.documents.azure.com/dbs/{db-id}/colls/{coll-id}/docs

x-ms-date: <UTC date>

authorization: <auth signature>

x-ms-version: <version>

To integrate Cosmos DB REST API into Denodo, create a JSON data source in the Virtual DataPort (VDP) Server.

For this, create a new JSON data source by navigating to “File > New > Data source > JSON”

Specify the Data Route as HTTP Client and click on Configure. Under the Configuration tab specify the HTTP Method and the Base URL as sample mentioned below:

https:// XXXXX.documents.azure.com:443/dbs

The next step is to configure the necessary HTTP headers for the base URL to work successfully. It is mandatory to include the following required headers

Name

Value

x-ms-date

@date

authorization 

@auth

x-ms-version 

2018-12-31

Here,

@auth → represents the authorization signature generated dynamically.

@date → represents the current UTC date/time of the request.

Save the JSON Data Source and create a base view that interpolates the required header values using the output from the stored procedure.

Next, create a Java Stored Procedure that dynamically generates the required header values authorization and x-ms-date.

The stored procedure accepts the following input parameters: masterKey, verb (representing the HTTP method), resourceType, and resourceId.

It returns two output values: authorization, which contains the generated authentication signature, and x-ms-date, which provides the current UTC timestamp for the request.

Sample Java Code

 import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Base64;
import java.util.Date;
import java.util.Locale;
import java.util.TimeZone;

 import javax.crypto.Mac;
import javax.crypto.spec.SecretKeySpec;

 import com.denodo.vdb.engine.storedprocedure.AbstractStoredProcedure;
import com.denodo.vdb.engine.storedprocedure.StoredProcedureException;
import com.denodo.vdb.engine.storedprocedure.StoredProcedureParameter;
import com.denodo.vdb.engine.storedprocedure.Transaction;

import static com.denodo.vdb.engine.storedprocedure.AbstractStoredProcedure.LOG_ERROR;

/**
* Denodo stored procedure to generate CosmosDB Authorization header and x-ms-date dynamically.
*/
@SuppressWarnings("serial")
public class Stored extends AbstractStoredProcedure {

        private boolean stopped = false;

        /** Default constructor */
        public Stored() { }

        @Override
        public String getDescription() {
            return "Generates CosmosDB Authorization header and x-ms-date for a request";
        }

        @Override
        public String getName() {
            return "Stored"; // Procedure name in Denodo SQL
        }

        @Override
        public StoredProcedureParameter[] getParameters() {
            return new StoredProcedureParameter[] {
                new StoredProcedureParameter("masterKey", Types.VARCHAR, StoredProcedureParameter.DIRECTION_IN),
                new StoredProcedureParameter("verb", Types.VARCHAR, StoredProcedureParameter.DIRECTION_IN),
                new StoredProcedureParameter("resourceType", Types.VARCHAR, StoredProcedureParameter.DIRECTION_IN),
                new StoredProcedureParameter("resourceId", Types.VARCHAR, StoredProcedureParameter.DIRECTION_IN),
                new StoredProcedureParameter("authorization", Types.VARCHAR, StoredProcedureParameter.DIRECTION_OUT),
                new StoredProcedureParameter("xmsdate", Types.VARCHAR, StoredProcedureParameter.DIRECTION_OUT)
            };
        }

 

        @Override
        public boolean stop() {
            stopped = true;
            return true;
        }

 

        @Override
        public void doCall(Object[] inputValues) throws StoredProcedureException {

 

            Transaction transaction = null;

 

            try {
                transaction = getEnvironment().createTransaction();

 

                // Input parameters
                String masterKey = (String) inputValues[0];
                String verb = (String) inputValues[1];
                String resourceType = (String) inputValues[2];
                String resourceId = (String) inputValues[3];

 

                if (masterKey == null || masterKey.trim().isEmpty()) {
                throw new StoredProcedureException("Master key cannot be null or empty");
                }

 

                // Format current UTC date
            SimpleDateFormat sdf = new SimpleDateFormat("EEE, dd MMM yyyy HH:mm:ss 'GMT'", Locale.US);
            sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
                String xmsDate = sdf.format(new Date());

 

                // Build string to sign
                String textToSign = verb.toLowerCase() + "\n" +
                                resourceType.toLowerCase() + "\n" +
                                (resourceId != null ? resourceId : "") + "\n" +
                                xmsDate.toLowerCase() + "\n\n";

 

                // Ensure correct base64 padding (Java 8 compatible)
                int missingPadding = masterKey.length() % 4;
                if (missingPadding != 0) {
                StringBuilder sb = new StringBuilder(masterKey);
                for (int i = 0; i < 4 - missingPadding; i++) {
                    sb.append("=");
                }
                masterKey = sb.toString();
                }

 

                // Compute HMAC-SHA256 signature
                byte[] keyBytes = Base64.getDecoder().decode(masterKey);
                Mac mac = Mac.getInstance("HmacSHA256");
                mac.init(new SecretKeySpec(keyBytes, "HmacSHA256"));
                byte[] signatureBytes = mac.doFinal(textToSign.getBytes("UTF-8"));

            String signature = Base64.getEncoder().encodeToString(signatureBytes);
                String authHeader = java.net.URLEncoder.encode("type=master&ver=1.0&sig=" + signature, "UTF-8");

                // Return results as OUT parameters
            getProcedureResultSet().addRow(new Object[] { authHeader, xmsDate });

                if (!stopped && transaction != null) {
                transaction.commit();
                }

            } catch (Exception e) {
                if (transaction != null) {
                try { transaction.rollback(); } catch (Exception ignore) {}
                }
            getEnvironment().log(LOG_ERROR, e.getMessage());
                throw new StoredProcedureException("Error generating CosmosDB authorization header: " + e.getMessage());
            }
        }
}

Once the stored procedure is implemented, compile the Java class and export it as a JAR file,  for example, CosmosAuthGenerator.jar.

In Denodo Design Studio, import the JAR file by navigating to File > Extension Management > Extensions, then click Import and upload your JAR file. Verify that the uploaded JAR appears in the list of installed extensions. Next, create the stored procedure by going to File > New > Stored Procedure > Java Stored Procedure, clicking Select JARs, and choosing your uploaded JAR. Ensure that the Qualified Class Name matches your Java class, and then click Save to complete the setup.

After that, create a view from the stored procedure by executing the following command from a VQL Shell:

CREATE OR REPLACE VIEW <view_name> AS SELECT * FROM <stored_procedure_name>();

This view returns the dynamically generated values for authorization and x-ms-date.

To pass these header values dynamically to the REST API view, create a Join View in the Denodo Design Studio by navigating to File > New > Join. Drag both the stored procedure view (e.g., cosmos_auth_view) and the REST API JSON view into the workspace. Create a join on the common fields typically the header parameters such as auth and date. Finally, select the required output fields and click Save to finalize the join view.

Run a query on the final join view to confirm that the REST API call executes successfully and retrieves data from Cosmos DB.

Troubleshooting

Test connection fails with “Unrecognized ICU conversion error”

This error is related to charset transformation with some Windows configurations and non-unicode applications.

To solve this problem on Windows go to Start and type Region Settings:

  1. Go to Related Settings > Change date, time & regional setting.

  1. Go to Region > Change date, time, or number formats.

  1. Go to the Administrative tab and click on Change system locale…

  1. Check Use Unicode UTF-8 and click OK.

Multi-byte characters displayed incorrectly

If the Azure Cosmos DB tables include values with multi-byte characters, for instance using UTF-8, these characters will not be properly displayed with the default Denodo Virtual DataPort server configuration.

So they are properly displayed, add the property -Dfile.encoding=UTF-8 to the startup JVM options of the VDP server. To see how to update the JVM options check JVM Memory Settings Configuration.

References

ODBC Sources

Disclaimer

The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.

For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.
Recommendation

Recommended resources Recommendations generated by AI

How to connect to Azure SQL Data Warehouse from Denodo

This document provides detailed instructions on how to establish a connection between Denodo and Microsoft Azure Synapse SQL, enabling seamless data integration and virtualization across these platforms.
KB Document

How to connect to Azure Blob Storage from Denodo

This document provides detailed instructions on connecting Denodo Virtual DataPort to Azure Blob Storage, highlighting the use of the ABFS option for Denodo 9 and later versions, as well as offering guidance on utilizing the Distributed File Syste...
KB Document

How to configure published web services with Oauth and Azure AD

This article provides a detailed guide on configuring Microsoft Azure Active Directory (Azure AD) as the Authorization Server for OAuth 2.0 authentication in Denodo's Virtual DataPort Web Applications, enabling users to connect to Denodo web servi...
KB Document

How to connect to Databricks from Denodo

This document describes how to connect to Databricks as a data source in Denodo Virtual DataPort. Databricks is a cloud-based platform for data analytics and artificial intelligence. It is based on Apache Spark. In this document, we will see diffe...
KB Document

How to connect to Salesforce from Denodo

This resource provides guidance on connecting Denodo Virtual DataPort to Salesforce, as detailed in the Salesforce Sources section of the Virtual DataPort Administration Guide, which is essential for integrating Salesforce data into your virtualiz...
KB Document

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here