Share via

SSRS - How to use Hive Database (Hortonworks) as data source for SSRS Report

  • Article

1)   Objective:

The objective of this article is to provide a way of consuming data from Hive database (Hortonworks) in SSRS report.

2)   Pre-requisites

  1. You need to download the ODBC driver from the Hortonworks add-ons page (http://hortonworks.com/hdp/addons/).
  2. Install and configure the HORTONWORKS ODBC driver on for your OS e.g. Windows7
  3. The Hortonworks ODBC driver enables you to access data in the Hortonworks Data Platform from Business Intelligence (BI) applications such as SSRS, Microsoft Excel, Tableau, Qlik, Micro Strategy, Cognos, and Business Objects.
  4. You can see steps into below site:
    https://hortonworks.com/hadoop-tutorial/how-to-install-and-configure-the-hortonworks-odbc-driver-on-windows-7/

3)   Steps to create data source for Hive database in Hortonworks

  1. ODBC connection string for Hive (Hortonworks) for Windows contains the following
    Data Source Name — Specify a name for the DSN.
    Description — Enter an optional description for the DSN.
    Host — Enter the hostname or IP of the server running HiveServer1 or HiveServer2.
    Port — Enter the listening port for the Hive service.
    Database — Leave as default to connect to the default Hive database, or enter a specific database name.
    Hive Server Type: — Set to HiveServer1 or HiveServer2.
    Authentication — If you are using HiveServer2, set the following.
    Mechanism: — Set to the authentication mechanism you're using. The MapR ODBC driver supports user name, user name and password, username and password over SSL authentication, and Kerberos.
    User Name: — Set the user to run queries as.
    Password: — The user's password, if your selected authentication mechanism requires one.

  2. Once you configure the ODBC driver for same, you can format you connection string in below form:

  3. Go to SQL Server Data Tools (SSDT), create a SQL Report project or use Report Builder.

  4. Right click on ‘Shared Data sources’ folderè Click on Create new data source

  5. Select Data source Type as ‘ODBC’ and paste the above mentioned connection string. Click ok

  6. Go to the credentials then Provide the required credential to connect to Hive Database and click ok.

4)   Steps to create data dataset from Hive database (Hortonworks)

  1. Below is the sample Hadoop Query format to pull the data from Hadoop HDFS environment for the report:

    SELECT   

    regexp_replace([ProductID],' "','') AS [ProductID],

    regexp_replace([Name],'" ','') AS [Name],

    regexp_replace([ProductModel],' "','') AS [ProductModel],

    regexp_replace([CultureID],'"  ','') AS [CultureID],

    regexp_replace([Description],' "','') AS [Description]

    FROM  exlog.main

  2. Right click on Shared data sets folder and create new data set using Hive data source

  3. Addition of filter is same as SQL Server dataset.

5)   Steps to create Report to pull data from  Hive database

  1. Create a new report and map the newly created shared dataset in the report

  2. Execute the report

6)   Conclusion:

By using the above steps, we can consume the data from Hive database (Hortonworks) in SSRS Report.