MicroStrategy ONE

DSN-Less Connection to Hive Using the Get Tables with Query Setting

  1. In Web, click Create > Add External Data.
  2. Select one of the Hive data sources to connect to (Hortonworks Hive, Apache Hive, Cloudera Hive, etc.)

  3. Select an import option:

    • Build a Query: Use a graphical interface to build a 'SQL on Hadoop' query to import a table.
    • Type a Query: Paste or write a query script to import a table via 'SQL on Hadoop' or Pig.
    • Select Tables: Select a single or multiple tables to import via 'SQL on Hadoop.'
  4. Click Next.
  5. Click New Data Source next to Data Sources.
  6. Select DSN-less Data Sources from the Data Source window.

  7. From the Database and Version drop-downs, select the appropriate configuration.

  8. Select either the Kerberos or Standard Authentication option and fill out the required parameters.
  9. Click Show connection string at the bottom of the Data Source editor to expose additional settings.
  10. Select Edit connection string.
  11. Select MicroStrategy Hive ODBC Driver (Certified) from the Driver drop-down.
  12. In the Connection String dialog, you should see a few parameters that are only passed through the connection string:

    DRIVER={MicroStrategy Hive ODBC

    Driver};HOST=hivetest;PORT=10000;SCHEMA=ubd;HiveServerType=2;GetTablesWithQuery=1;

    UseNativeQuery=1;DefaultStringColumnLength=4000;

The default value for GetTablesWithQuery is set to 1, indicating that this setting is enabled. To disable, change the value to 0.

The Hive driver shipped with MicroStrategy allows you to set Hive server properties. Adding server side parameters can allow you to indicate which TEZ execution queue the query should go to when running Hive on TEZ (SSP_tez.queue.name), define the execution engine (SSP_hive.execution.mode), or indicate whether to execute jobs in parallel (SSP_hive.exec.parallel).

To use these properties, you can add the following connection string parameter:

{SPP_[SSPKEY]=[SSPValue]}

For example:

{SSP_mapred.queue.names=myQueue}

After the driver applies the server side property, the SSP_prefix is removed from the DSN entry and only the SSP Key and SSP Value remain.

To add these parameters in Windows, click Advanced Options in the MicroStrategy Hive ODBC Driver DSN Setup. Select Server Side Properties and enter the parameter.

After updating the connection string, provide your credentials for the Hive connection and provide a data source name.