"Big Data"- Reporting Over Hadoop using Hive

Intellicus 5.2 forays into the "Big Data" world by providing Reporting over Hadoop, using Hive as the JDBC Interface. This enables you to leverage your Hadoop System to get meaningful Business Insights into the huge datasets in the underlying HDFS. 

Intellicus provides a much richer experience to the user, thru Dashboards, Charts, tabular reports etc., for doing analysis on the data. Intellicus Ad-hoc reporting also adds another layer of ease and power,  in terms of filtering, grouping and summaries over the data present on a Hadoop system.

The Intellicus Solution:

 Note : The Hive JDBC driver has been enhanced to add some of the missing implementations and thus allows Intellicus to seamlessly communicate with a Hive Server as it does with any other regular RDBMS data source.

 

Connection

The connection to a Hive server can be made easily thru the existing interface in Intellicus, as shown

Creating Report Objects using Hive QL

The Report Objects (viz. Query objects and Parameter Objects) can be created using the existing interface of SQL Editor. You can also manually write Hive queries, as you write for other RDBMS.

The underlying data is available as Tables and query can be generated by drag-and-drop.

Note : The SQL generated by Intellicus SQL designer(Drag and Drop) for a "join" condition on Hive tables needs to be modified before being used. The generated SQL does not conform to the Hive QL syntax.

The Join (in the image above) generates the SQL as :

This query needs to be modified as:

Note "join" in place of "," and "on" in place of "where"

 

 

Support for execution of multiple queries on Hive

Building on the facility to analyze "Big Data" in 5.2,  Intellicus 5.2 SP1 now provides support for execution of multiple queries through Query object on Hive. This facilitates execution of Map Reduce (MR) jobs in form of Hive SQLs. These jobs can be used to populate summary data in different tables. You can then do business analysis by creating reports on these populated summary tables.

Below image shows Hive SQLs in the SQL -Editor.