Client Access to Altus Data Warehouse Clusters

You can use JDBC or ODBC to connect to Altus Data Warehouse clusters and query the data using standard business intelligence client tools, such as Tableau, MicroStrategy, or Power BI.

To connect to Altus Data Warehouse clusters using JDBC, use the Impala JDBC Connector for Cloudera Enterprise.

To connect to Altus Data Warehouse clusters using ODBC, use the Impala ODBC Connector for Cloudera Enterprise.

JDBC Connection to Altus Data Warehouse Clusters

Effective version 2.6.9, the Impala JDBC Connector for Cloudera Enterprise uses the Altus credentials file to connect to an Altus Data Warehouse cluster. If you install the Altus client and configure it with an API access key, the client configuration process creates an Altus credentials file. If you do not install the Altus client, you can generate an API access key and download the Altus credentials file.

To connect to an Altus Data Warehouse cluster through the Impala JDBC Connector, the cluster must have public IP addresses. You can create clusters with public IP addresses if you use an environment with the Public IPs option enabled. For more information, see Enable Public IPs. If the Altus Data Warehouse cluster does not have public IPs set up, you can connect to the cluster using private IP addresses if you set up a network connection between the client tool and the coordinator node in the cluster.

When you use the Impala JDBC connector, you can connect to a secure or unsecure Altus Data Warehouse cluster using the same connection URL.

For instructions on using Impala JDBC Connector Version 2.6.4 or older, see Using Impala JDBC Connector Version 2.6.4 or Older with MicroStrategy.

JDBC Connection Setup

To set up a JDBC connection to an Altus Data Warehouse cluster, complete the following steps:
  1. Set up the Altus credentials file.
  2. Download the Cloudera JDBC driver for Impala.
  3. Set up the JDBC connection.

The Impala JDBC Connector for Cloudera Enterprise works for any client tool. You can use the same procedure to set up a JDBC connection to an Altus Data Warehouse cluster from Tableau, MicroStrategy, or other business intelligence client tools.

Setting Up the Altus Credentials File

If you have installed the Altus client and configured it with an access key, you can use the same credentials file with the Impala JDBC connector to access the Altus Data Warehouse cluster.

If you have not installed the Altus client, you can generate an access key and download the credentials file. For more information about generating an access key, see Generating an API Access Key.

Before you set up the JDBC connection, verify that you have the Altus credentials file in your home directory: ~/.altus/credentials

Downloading the Cloudera JDBC Driver for Impala

Download and install the latest version of the Impala JDBC Connector for Cloudera Enterprise from the Cloudera JDBC driver download page.

For more information about downloading the JDBC driver, see Cloudera JDBC Driver Documentation for Impala

Setting Up the JDBC Connection

The Impala JDBC connector simplifies the connection setup process by requiring only the name of the Altus Data Warehouse cluster as a parameter in the connection URL. When you provide the name of the Altus Data Warehouse cluster that you want to access, the JDBC driver uses the Altus credentials file to get the cluster connection information and verify your login credentials.

When you set up the JDBC connection, select the following JDBC driver name:
com.cloudera.impala.jdbc41.Driver
Use the following connection URL to connect to the Altus Data Warehouse cluster:
jdbc:impala://AltusDWClusterName

AltusDWClusterName is the name of the Altus Data Warehouse cluster that you want to access.

To use a specific credentials file or profile name, you can include the optional parameters in the connection URL:
jdbc:impala://AltusDWClusterName;AltusCredFile=CredentialsFilePathAndFilename;AltusProfileName=ProfileName
 
The following table describes the optional parameters:
Parameter Description
AltusCredFile

Location and file name of the Altus credentials file.

Optional.

If the credentials file has a different file name or is located in a directory other than the default, use the AltusCredFile parameter to specify the path and file name of the credentials file.

If the credentials file has the default name and location, you can omit the AltusCredFile parameter from the URL. The default Altus credentials file is ~/.altus/credentials.

AltusProfileName

Profile name of the user account that owns the credentials. The Altus credentials file can contain more than one set of credentials. Each set of credentials is preceded by the profile name.

Optional.

If you want to use a specific set of credentials, use the AltusProfileName parameter to specify the profile name of the credentials to use to connect to the cluster.

If you do not include the AltusProfileName parameter in the connection URL, Altus uses the credentials with the profile name [default].

The Impala JDBC connector uses the default port number 21050 to connect to the cluster.

Example: Setting Up the JDBC Connection in Tableau on macOS

You must use Tableau version 2018.3 or higher to set up a JDBC connection. Older versions of Tableau do not support JDBC.

Before you set up the JDBC connection in Tableau, verify that you have completed the following requirements:
  • The Altus credentials file is correctly set up:

    Linux, Unix, MacOS: ~/.altus/credentials

    Windows: \Users\UserName\.altus\credentials

  • The Cloudera JDBC driver for Impala is in the following directory: ~/Library/Tableau/Drivers/
To set up the JDBC connection in Tableau, complete the following steps:
  1. Open Tableau Desktop.
  2. On the Connect panel, go to the To a Server section and click More... > Other Databases (JDBC).
  3. On the Other Databases (JDBC) page, enter the URL to connect to the Altus Data Warehouse cluster using the following format:
    jdbc:impala://AltusDWClusterName
  4. Select the SQL dialect you want to use.

    You do not need to enter the database login credentials. The Impala JDBC driver uses the Altus credentials file to log in to the Altus Data Warehouse cluster and ignores the database user name and password that you provide.

    The following image shows the connection setup on Tableau:


  5. Click Sign In.

For more information about using a JDBC driver not listed on the Tableau connection setup page, see the Tableau documentation about Other Databases (JDBC).

ODBC Connection to Altus Data Warehouse Clusters

When you install and configure the Cloudera ODBC database driver to connect to a Data Warehouse cluster, you can set up a secure connection from the client tool to the cluster.

Altus uses self-signed certificates for the secure connection to the Data Warehouse clusters. To set up a secure connection to Data Warehouse clusters, you must enable self signed certificates in the database driver.

The database driver setup requires information about the Data Warehouse cluster that you want to access. If you created the cluster, you can view the cluster on the Altus console and get the information you need. Otherwise, contact the Altus administrator or the Altus user who created the cluster to get the information.

For more information about getting the Altus Data Warehouse cluster credentials, see Getting the Cluster Credentials on the Console or Getting the Cluster Credentials Using the CLI.

Before you set up a secure ODBC connection to a Data Warehouse cluster, you must have the following information:
  • Port number for SQL requests to the Data Warehouse cluster: 21050
  • IP address of the coordinator node of the Data Warehouse cluster.
  • User login credentials for the cluster.

The secure ODBC connection setup process depends on the client tool that you use.

ODBC Connection from Tableau Desktop

Use the Cloudera ODBC Driver for Impala to connect from Tableau to an Altus Data Warehouse cluster. Tableau uses a Tableau Datasource Customization (.tdc) file to customize an ODBC connection. You can use the .tdc file to create a secure connection to the Altus Data Warehouse cluster.

The process to set up a secure ODBC connection to an Altus Data Warehouse cluster from Tableau Desktop includes the following steps:
  1. Creating a datasource customization file for the connection.
  2. Installing the Impala ODBC Connector for Cloudera Enterprise and setting up a connection to the Altus Data Warehouse cluster.

You must restart Tableau for the configuration change to take effect. Create test queries to verify that your connection to the Altus Data Warehouse cluster is secure.

Setting Up the Tableau Datasource Customization (.tdc) File

To set up Tableau datasource customization (.tdc) file:
  1. Create a .tdc file in the Tableau data sources folder:
    • Windows: \Users\UserName\Documents\My Tableau Repository\Datasources
    • macOS: /Users/UserName/Documents/My Tableau Repository/Datasources

    You can use any name for the .tdc file.

  2. In the .tdc file, set the connection-customization property to the Cloudera Hadoop ODBC driver and allow self-signed certificates.
    Include the following properties:
    • AllowSelfSignedCert. Set this property to 1 to use a self-signed certificate.
    • CAIssuedCertNamesMismatch. Set this property to 1 so that the Common Name (CN) in the self-signed certificate does not need to match the host name of the Impala server.

    For example:

    <connection-customization class='hadoophive' enabled='true' version='9.0'>
        <vendor name='hadoophive' />
        <driver name='hadoophive' />
        <customizations>
           <customization name='odbc-connect-string-extras' value='AllowSelfSignedCert=1;CAIssuedCertNamesMismatch=1' />
        </customizations>
    </connection-customization>

    Save the file.

  3. Restart Tableau Desktop for the new settings to take effect.

Installing and Setting Up the Impala ODBC Connector for Cloudera Enterprise

To download and set up the Impala ODBC Connector for Cloudera Enterprise:
  1. Download and install the Cloudera ODBC database driver for your operating system.

    Download the latest version of the Impala ODBC Connector for Cloudera Enterprise from the Cloudera ODBC driver download page.

    For more information about downloading the ODBC driver, see Cloudera ODBC Driver Documentation for Impala

  2. In Tableau, configure the Cloudera ODBC driver to connect to the Altus Data Warehouse cluster.
    To configure the Cloudera ODBC driver, complete the following steps:
    1. In Tableau Desktop, on the Data tab of the main book page, click Connect to Data.
    2. On the Connect page, go to the To a Server section and click More....

      The list of available servers displays on the page.

    3. Click Cloudera Hadoop.
    4. On the Cloudera Hadoop window, enter the following information:
      • Server. Enter the IP address of the coordinator node of the Altus Data Warehouse cluster that you want to access.
      • Port. Enter the port number for SQL requests: 21050.
      • Type. Select Impala.
      • Authentication. Select Username and Password.
      • Transport. Select SASL.
      • Username. Enter the user name to use for the cluster that you want to access.

        You can get user credentials for the Altus Data Warehouse cluster in Altus. For more information about getting the Altus Data Warehouse cluster credentials, see Getting the Cluster Credentials on the Console and Getting the Cluster Credentials Using the CLI.

      • Password. Enter the password for the user account to access the cluster.
    5. Select the Require SSL option.
    6. Click Sign In.

ODBC Connection from MicroStrategy on Windows

You can use MicroStrategy on Windows to send queries to an Altus Data Warehouse cluster. Use the Cloudera ODBC Driver for Impala to connect from MicroStrategy to the cluster.

The process to set up a secure connection to a Altus Data Warehouse cluster from MicroStrategy on Windows includes the following steps:
  1. Installing the Impala ODBC Connector for Cloudera Enterprise and setting up a connection to the Altus Data Warehouse cluster.
  2. Setting up a DSN-based connection in In MicroStrategy.

Setting Up the Impala ODBC Connector

To download and set up the Impala ODBC Connector for Cloudera Enterprise:
  1. Download and install the Cloudera ODBC database driver for Windows.

    Download the latest version of the Impala ODBC Connector for Cloudera Enterprise from the Cloudera ODBC driver download page.

    For more information about downloading the ODBC driver, see Cloudera ODBC Driver Documentation for Impala

  2. Open the Windows ODBC data source administrator.

    Run the ODBC Datasource Administrator (64-bit) application.

  3. In the System DSN tab, select the sample Impala DSN: Sample Cloudera Impala DSN.
  4. Click Configure.
  5. In the Cloudera ODBC Driver for Impala DSN Setup window, configure the following properties:
    • Data Source Name. Assign a name to the data source.
    • Host. Enter the IP address of the coordinator node of the Altus Data Warehouse cluster that you want to access.
    • Port. Enter the port number for SQL requests: 21050.
  6. In the Authentication section, configure the following properties:
  7. Select the Use Simple Authentication and Security Layer (SASL) option.
  8. Click SSL Options.
  9. On the SSL Options window, select the following options:
    • Enable SSL
    • Allow Self-signed Server Certificate
    • Allow Common Name Host Name Mismatch
  10. Keep the default value for Trusted Certificates.
  11. Click Test to verify that the connection works.
  12. Click OK.

Setting Up a DSN-Based Connection in MicroStrategy

To set up a DSN-based connection MicroStrategy:
  1. Open MicroStrategy.
  2. Create a dossier.

    You create a data source connection for a specific MicroStrategy dossier.

  3. On the Untitled Dossier window, in the Datasets section, click New Data.
  4. On the Data Sources window, go to Hadoop.
  5. From the All Datasets list, select Impala.
  6. On the Select Import Options window, select Build a Query and click Next.
  7. On the Import from Table page, select New Data Source.
  8. On the Data Source window, select DSN Data Sources.
  9. Configure the following properties:
    • DSN. Select the ODBC data source that you created using the Windows ODBC data source administrator.
    • Version. Select Impala 2.x.
    • User. Enter the user name to use for the cluster that you want to access.

      You can get user credentials for the Altus Data Warehouse cluster in Altus. For more information about getting the Altus Data Warehouse cluster credentials, see Getting the Cluster Credentials on the Console and Getting the Cluster Credentials Using the CLI.

    • Password. Enter the password for the user account to access the cluster.
    • Data Source Name. Assign a name to the data source.
  10. Click OK.

Create test queries to verify your connection to the Altus Data Warehouse cluster.

ODBC Connection from Power BI Desktop

You can use Microsoft Power BI Desktop to send queries to an Altus Data Warehouse cluster on Windows. Use the Impala ODBC Connector for Cloudera Enterprise to connect from Power BI to the cluster.

The process to set up a secure connection to Altus Data Warehouse clusters from Power BI includes the following steps:
  1. Installing the Impala ODBC Connector for Cloudera Enterprise and setting up a connection to the Altus Data Warehouse cluster.
  2. Setting up a data source in Power BI.

Setting Up the Impala ODBC Connector

To download and set up the Impala ODBC Connector for Cloudera Enterprise:
  1. Download and install the Cloudera ODBC database driver for Windows.

    Download the latest version of the Impala ODBC Connector for Cloudera Enterprise from the Cloudera ODBC driver download page.

    For more information about downloading the ODBC driver, see Cloudera ODBC Driver Documentation for Impala

  2. Open the Windows ODBC data source administrator.

    Run the ODBC Datasource Administrator (64-bit) application.

  3. In the System DSN tab, select the sample Impala DSN: Sample Cloudera Impala DSN.
  4. Click Configure.
  5. In the Cloudera ODBC Driver for Impala DSN Setup window, configure the following properties:
    • Data Source Name. Assign a name to the data source.
    • Host. Enter the IP address of the coordinator node of the Altus Data Warehouse cluster that you want to access.
    • Port. Enter the port number for SQL requests: 21050.
  6. In the Authentication section, configure the following properties:
  7. Select the Use Simple Authentication and Security Layer (SASL) option.
  8. Click SSL Options.
  9. On the SSL Options window, select the following options:
    • Enable SSL
    • Allow Self-signed Server Certificate
    • Allow Common Name Host Name Mismatch
  10. Keep the default value for Trusted Certificates.
  11. Click Test to verify that the connection works.
  12. Click OK.

Setting Up a Connection in Power BI

To set up a DSN-based connection MicroStrategy:
  1. Open Power BI.
  2. Click Get Data.
  3. Select ODBC.

    You might need to scroll down and click More or search for ODBC.

  4. On the From ODBC window, select the ODBC data source that you created using the Windows ODBC data source administrator and click OK.
  5. On the ODBC driver window, configure the following properties:
  6. Click Connect.

Create test queries to verify your connection to the Altus Data Warehouse cluster.