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:

    Copy
    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.

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