# Connect using the Amazon DocumentDB ODBC driver Connect using the ODBC driver The ODBC driver for Amazon DocumentDB provides an SQL-relational interface for developers and enables connectivity from BI tools such as Power BI Desktop and Microsoft Excel. For more detailed information, refer to the [Amazon DocumentDB ODBC Driver documentation on GitHub](https://github.com/aws/amazon-documentdb-jdbc-driver/blob/develop/src/markdown/index.md). **Topics** + [ ## Getting started ](#connect-odbc-get-started) + [ # Setting up the Amazon DocumentDB ODBC driver in Windows ](connect-odbc-setup-windows.md) + [ # Connect to Amazon DocumentDB from Microsoft Excel ](connect-odbc-excel.md) + [ # Connect to Amazon DocumentDB from Microsoft Power BI Desktop ](connect-odbc-power-bi.md) + [ # Automatic schema generation ](connect-odbc-schema.md) + [ # SQL support and limitations ](connect-odbc-sql-support.md) + [ # Troubleshooting ](connect-odbc-troubleshooting.md) ## Getting started **Step 1. Create Amazon DocumentDB Clusters** If you don't already have an Amazon DocumentDB cluster, there are a number of ways to get started. Amazon DocumentDB is a Virtual Private Cloud (VPC)-only service. If you are connecting from a local machine outside the cluster's VPC, you will need to create an SSH connection to an Amazon EC2 instance. In this case, launch your cluster using the instructions in [Connect with EC2](https://docs.aws.amazon.com/documentdb/latest/developerguide/connect-ec2.html). See [Using an SSH Tunnel to Connect to Amazon DocumentDB](https://github.com/aws/amazon-documentdb-odbc-driver/blob/develop/src/markdown/setup/setup.md#using-an-ssh-tunnel-to-connect-to-amazon-documentdb) for more information on SSH tunneling and when you might need it. **Step 2. JRE or JDK Installation** Depending on your BI application, you may need to ensure a 64-bit JRE or JDK installation version 8 or later installed on your computer. You can download the Java SE Runtime Environment 8 [here](https://www.oracle.com/ca-en/java/technologies/downloads/#java8). **Step 3. Download the Amazon DocumentDB ODBC Driver** Download the Amazon DocumentDB ODBC driver [here](https://github.com/aws/amazon-documentdb-odbc-driver/releases). Choose the proper installer (for example, documentdb-odbc-1.0.0.msi). Follow the installation guide. **Step 4. Using an SSH Tunnel to Connect to Amazon DocumentDB** Amazon DocumentDB clusters are deployed within an Amazon Virtual Private Cloud (Amazon VPC). They can be accessed directly by Amazon EC2 instances or other Amazon services that are deployed in the same Amazon VPC. Additionally, Amazon DocumentDB can be accessed by Amazon EC2 instances or other Amazon services in different VPCs in the same Amazon region or other regions via VPC peering. However, suppose that your use case requires that you (or your application) access your Amazon DocumentDB resources from outside the cluster's VPC. This will be the case for most users not running their application on a VM in the same VPC as the Amazon DocumentDB cluster. When connecting from outside the VPC, you can use SSH tunneling (also known as port forwarding) to access your Amazon DocumentDB resources. To create an SSH tunnel, you need an Amazon EC2 instance running in the same Amazon VPC as your Amazon DocumentDB cluster. You can either use an existing EC2 instance in the same VPC as your cluster or create one. You can set up an SSH tunnel to the Amazon DocumentDB cluster `sample-cluster.node.us-east-1.docdb.amazonaws.com` by running the following command on your local computer: ``` ssh -i "ec2Access.pem" -L 27017:sample-cluster.node.us-east-1.docdb.amazonaws.com:27017 ubuntu@ec2-34-229-221-164.compute-1.amazonaws.com -N ``` The `-L` flag is used for forwarding a local port. This is a prerequisite for connecting to any BI tool running on a client outside your VPC. Once you run the step above you can move on to the next steps for the BI tool of your choice. For further information on SSH tunneling, please refer to the documentation on [Using an SSH Tunnel to Connect to Amazon DocumentDB](https://github.com/aws/amazon-documentdb-odbc-driver/blob/develop/src/markdown/setup/setup.md#using-an-ssh-tunnel-to-connect-to-amazon-documentdb). # Setting up the Amazon DocumentDB ODBC driver in Windows Setting up the ODBC driver in Windows Use the following procedure to set up the Amazon DocumentDB ODBC driver in Windows: 1. Open **Control Panel** in Windows and search for ODBC (or in the menu, select **Windows Tools** > **ODBC Data Sources (32-bit)** or **ODBC Data Sources (64-bit)**): ![\[Windows Control Panel interface showing setup links for ODBC 32-bit and 64-bit data sources.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/odbc-control-panel-1.png) 1. Select the appropriate ODBC Driver Data Source Administrator: opt for the 32-bit version if it is installed, otherwise, choose the 64-bit version. 1. Select the Sytem DSN tab and then click **Add...** to add a new DSN: ![\[ODBC Data Source Administrator interface showing Add button.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/odbc-add-dsn-1.png) 1. Choose **Amazon DocumentDB** from the data source driver list: ![\[The Create New Data Source interface with the Amazon DocumentDB driver option selected.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/create-data-source-1.png) 1. In the **Configure Amazon DocumentDB DSN** dialog, complete the **Connection Settings**, **TLS** tab, and **Test Connection** fields, then click **Save**: ![\[The Configure Amazon DocumentDB DSN interface with Connection Settings, TLS, and Test Connection fields. The Save button is on the bottom.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/config-docdb-dsn-1.png) 1. Ensure you complete the Windows form accurately, as connection details will differ depending on your chosen SSH tunneling method to the EC2 instance. See SSH tunneling methods [here](https://github.com/aws/amazon-documentdb-odbc-driver/blob/develop/src/markdown/setup/setup.md#using-an-ssh-tunnel-to-connect-to-amazon-documentdb). See [Connection String Syntax and Options](https://github.com/aws/amazon-documentdb-odbc-driver/blob/develop/src/markdown/setup/connection-string.md) for more information about each property. ![\[The Configure Amazon DocumentDB DSN interface with SSH Tunnel fields completed.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/config-docdb-dsn-ssh-1.png) For more information about configuring the Amazon DocumentDB ODBC Driver on Windows, click [here](https://github.com/aws/amazon-documentdb-odbc-driver/blob/develop/src/markdown/setup/windows-dsn-configuration.md). # Connect to Amazon DocumentDB from Microsoft Excel Connect from Microsoft Excel 1. Ensure that the Amazon DocumentDB driver has been correctly installed and configured. For additional information, refer to [Setting up the ODBC driver in Windows](https://docs.aws.amazon.com/documentdb/latest/developerguide/connect-odbc-setup-windows.html). 1. Launch Microsoft Excel. 1. Navigate to **Data** > **Get Data** > **From Other Sources**. 1. Choose **From ODBC**: ![\[The Get Data dropdown shows the From Other Sources submenu. The From ODBC option is selected.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/excel-odbc-1.png) 1. Select the data source from the **Data source name (DSN)** drop down menu that is associated with Amazon DocumentDB: ![\[The Data source name dropdown with the DocumentDB DSN option selected.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/excel-odbc-dsn-select-1.png) 1. Choose the collection from which you want to load data into Excel: ![\[The Navigator interface with the salaries table selected and a preview of its data.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/excel-odbc-collect-1.png) 1. Load data into Excel: ![\[Excel spreadsheet showing five rows of data from the selected salaries table.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/excel-data-load-1.png) # Connect to Amazon DocumentDB from Microsoft Power BI Desktop Connect from Microsoft Power BI Desktop **Topics** + [ ## Prerequisites ](#odbc-power-bi-prerequisites) + [ ## Adding Microsoft Power BI Desktop custom connector ](#odbc-adding-power-bi) + [ ## Connecting using the Amazon DocumentDB custom connector ](#odbc-connect-custom-connector) + [ ## Configuring Microsoft Power BI Gateway ](#odbc-power-bi-gw) ## Prerequisites Before beginning, ensure that the Amazon DocumentDB ODBC driver is correctly installed. ## Adding Microsoft Power BI Desktop custom connector Copy the `AmazonDocumentDBConnector.mez` file to the `\Documents\Power BI Desktop\Custom Connectors\` folder (or to `\OneDrive\Documents\Power BI Desktop\Custom Connectors` if using OneDrive). This will allow Power BI to access custom connector. You can get the connector to Power BI Desktop [here](https://github.com/aws/amazon-documentdb-odbc-driver/releases). Restart Power BI Desktop to make sure the connector is loaded. **Note** The custom connector only supports Amazon DocumentDB username and password for authentication. ## Connecting using the Amazon DocumentDB custom connector 1. Select Amazon DocumentDB (Beta) from **Get Data** and click **Connect**. If you get a warning for using a third-party service, click **Continue**. ![\[The Get Data interface with Amazon DocumentDB (Beta) option highlighted.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/get-data-1.png) 1. Enter all necessary information to connect to your Amazon DocumentDB cluster, then click **OK**: ![\[Form with connection detail input fields for an Amazon DocumentDB cluster.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/docdb-form-1.png) **Note** Depending on the configuration of your ODBC driver's Data Source Name (DSN), the SSH connection details screen may not be displayed if you have already provided the necessary information within the DSN settings. 1. Choose the data connectivity mode: + **Import** - loads all data and stores the information on disk. The data must be refreshed and reloaded in order to show data updates. + **Direct Query** - does not load data, but does live queries on the data. This means that data does not need to be refreshed and reloaded in order to show data updates. ![\[Interface showing Data Connectivity mode options for DocumentDB.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/data-connectivity-1.png) **Note** If you are using a very large dataset, importing all of the data may take a longer period of time. 1. If this is the first time connecting to this data source, select the authentication type and input your credentials when prompted. Then click **Connect**: ![\[Authentication interface showing input fields for username and password credentials.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/docdb-credentials-1.png) 1. In the **Navigator** dialog, select the database tables you want, then either click **Load** to load the data or **Transform Data** to continue transforming the data. ![\[Navigator interface showing list of database tables to choose from. The Load and Transform Data buttons are in the bottom right.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/navigator-1.png) **Note** Your data source settings are saved once you connect. To modify them, select **Transform Data** > **Data Source Settings**. ## Configuring Microsoft Power BI Gateway **Prerequisites**: + Enure that the custom connector will work with Power BI Gateway. + Make sure that the ODBC DSN is created in the ODBC data sources in the **System** tab on the machine where Power BI Gateway is installed. If you are using the internal SSH tunnel feature, the file `known_hosts` needs to be located where the Power BI service account has access to it. ![\[The known_hosts properties interface showing permissions for the PBIEgwService.\]](http://docs.amazonaws.cn/en_us/documentdb/latest/developerguide/images/ssh-known-hosts-1.png) **Note** This also applies to any file(s) that you might need to be able to establish a connection to your Amazon DocumentDB cluster, such as a certificate authority (CA) certificate file (pem file). # Automatic schema generation The ODBC driver is utilizing the Amazon DocumentDB JDBC driver through JNI (Java Native Interface) - making the automatic schema generation feature to work similarly in the JDBC driver. For more information on automatic schema generation, see [JDBC automatic schema generation](https://docs.aws.amazon.com/documentdb/latest/developerguide/connect-jdbc-autoschemagen.html). Additionally, to learn more about the ODBC driver architecture, click [here](https://github.com/aws/amazon-documentdb-odbc-driver/blob/develop/src/markdown/index.md). # SQL support and limitations The Amazon DocumentDB ODBC driver is a read-only driver that supports a subset of SQL-92 and some common extensions. Refer to the [ODBC support and limitations](https://github.com/aws/amazon-documentdb-odbc-driver/blob/develop/src/markdown/support/odbc-support-and-limitations.md) documentation for more information. # Troubleshooting If you are having problems using the Amazon DocumentDB ODBC driver, refer to the [Troubleshooting Guide](https://github.com/aws/amazon-documentdb-odbc-driver/blob/develop/src/markdown/support/troubleshooting-guide.md).