ODBC tools

Connect to Azure Cosmos DB using BI analytics tools with the ODBC driver
August 22, 2019 – 09:29 am
SQL SERVER - Simple Example of BCP Command Line Utility - SQL

The Azure Cosmos DB ODBC driver enables you to connect to Azure Cosmos DB using BI analytics tools such as SQL Server Integration Services, Power BI Desktop, and Tableau so that you can analyze and create visualizations of your Azure Cosmos DB data in those solutions.

The Azure Cosmos DB ODBC driver is ODBC 3.8 compliant and supports ANSI SQL-92 syntax. The driver offers rich features to help you renormalize data in Azure Cosmos DB. Using the driver, you can represent data in Azure Cosmos DB as tables and views. The driver enables you to perform SQL operations against the tables and views including group by queries, inserts, updates, and deletes.

Why do I need to normalize my data?

Azure Cosmos DB is a schemaless database, so it enables rapid development of apps by enabling applications to iterate their data model on the fly and not confine them to a strict schema. A single Azure Cosmos DB database can contain JSON documents of various structures. This is great for rapid application development, but when you want to analyze and create reports of your data using data analytics and BI tools, the data often needs to be flattened and adhere to a specific schema.

This is where the ODBC driver comes in. By using the ODBC driver, you can now renormalized data in Azure Cosmos DB into tables and views fitting to your data analytic and reporting needs. The renormalized schemas have no impact on the underlying data and do not confine developers to adhere to them, they simply enable you to leverage ODBC-compliant tools to access the data. So now your Azure Cosmos DB database will not only be a favorite for your development team, but your data analysts will love it too.

Now lets get started with the ODBC driver.

Step 1: Install the Azure Cosmos DB ODBC driver

  1. Download the drivers for your environment:
  2. Complete the installation wizard using the default input to install the ODBC driver.
  3. Open the ODBC Data source Administrator app on your computer, you can do this by typing ODBC Data sources in the Windows search box. You can confirm the driver was installed by clicking the Drivers tab and ensuring Microsoft Azure Cosmos DB ODBC Driver is listed.

Step 2: Connect to your Azure Cosmos DB database

  1. After, in the ODBC Data Source Administrator window, click Add. You can create a User or System DSN. In this example, we are creating a User DSN.
  2. In the Create New Data Source window, select Microsoft Azure Cosmos DB ODBC Driver, and then click Finish.
  3. In the Azure Cosmos DB ODBC Driver SDN Setup window, fill in the following:

    • Data Source Name: Your own friendly name for the ODBC DSN. This name is unique to your Azure Cosmos DB account, so name it appropriately if you have multiple accounts.
    • Description: A brief description of the data source.
    • Host: URI for your Azure Cosmos DB account. You can retrieve this from the Azure Cosmos DB Keys blade in the Azure portal, as shown in the following screenshot.
    • Access Key: The primary or secondary, read-write or read-only key from the Azure Cosmos DB Keys blade in the Azure portal as shown in the following screenshot. We recommend you use the read-only key if the DSN is used for read-only data processing and reporting.
    • Encrypt Access Key for: Select the best choice based on the users of this machine.
  4. Click the Test button to make sure you can connect to your Azure Cosmos DB account.
  5. Click Advanced Options and set the following values:

    • Query Consistency: Select the consistency level for your operations. The default is Session.
    • Number of Retries: Enter the number of times to retry an operation if the initial request does not complete due to service throttling.
    • Schema File: You have a number of options here.
      • By default, leaving this entry as is (blank), the driver scans the first page data for all collections to determine the schema of each collection. This is known as Collection Mapping. Without a schema file defined, the driver has to perform the scan for each driver session and could result in a higher start up time of an application using the DSN. We recommend that you always associate a schema file for a DSN.
      • If you already have a schema file (possibly one that you created using the ), you can click Browse, navigate to your file, click Save, and then click OK.
      • If you want to create a new schema, click OK, and then click Schema Editor in the main window. Then proceed to the information. Upon creating the new schema file, please remember to go back to the Advanced Options window to include the newly created schema file.
  6. Once you complete and close the Azure Cosmos DB ODBC Driver DSN Setup window, the new User DSN is added to the User DSN tab.

Step 3: Create a schema definition using the collection mapping method

There are two types of sampling methods that you can use: collection mapping or table-delimiters. A sampling session can utilize both sampling methods, but each collection can only use a specific sampling method. The steps below create a schema for the data in one or more collections using the collection mapping method. This sampling method retrieves the data in the page of a collection to determine the structure of the data. It transposes a collection to a table on the ODBC side. This sampling method is efficient and fast when the data in a collection is homogenous. If a collection contains heterogenous type of data, we recommend you use the as it provides a more robust sampling method to determine the data structures in the collection.

Source: docs.microsoft.com
Related Posts