Ad hoc Share: HDInsight on Windows: Building PowerPivot Report from Hive in a Few Easy Steps

Misc:

32 bit ODBC on 64 bit windows - c:\windows\syswow64\odbcad32.exe

Source:

http://blogs.msdn.com/b/karang/archive/2013/01/04/hdinsight_2d00_on_2d00_windows_2d00_building_2d00_powerpivot_2d00_report_2d00_from_2d00_hive_2d00_in_2d00_a_2d00_few_2d00_easy_2d00_steps.aspx

Karan Gulati (SSAS Maestro)

4 Jan 2013 3:20 PM

What are we Covering:

Hive
- Create table
- Load Data
- Query Data
Configure ODBC
Create PowerPivot
- With DataSource as HDInsight
- Import Data from HDInsight
Create Pivot Chat using imported data

(1)Open Hadoop Command Line

After installing HDInsight for Windows you will get Hadoop Command Line Shortcut on desktop
Click on Hadoop Command Line Shortcut and it will open Cmd Line for Hadoop

(2)Type Hive and it will change to Hive Command

(3)Create Tables

CREATE TABLE emp(id int,name string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
CREATE TABLE sales(id int,sales int)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

(4)Load Data using CSV Files

LOAD DATA LOCAL INPATH 'c:/data/emp.csv' OVERWRITE INTO TABLE emp;

LOAD DATA LOCAL INPATH 'c:/data/sales.csv' OVERWRITE INTO TABLE sales;

(5) Open HDInsight Dashboard (You will find shortcut on desktop) and click on Download

(6)Click on the appropriate link for your Excel.

(7)Configure ODBC
For configuring ODBC, Click start and type in ODBC. This will bring up the Data Sources dialog. Go to the System DSN tab and click Add. Configure it with a Data Source Name, Your Host Information, and a Username. In Single Node HDInsight Installation you don’t need to provide Username but if you are using HDInsight on Azure you can provide Username .

(8) Provider Data Source Name and Default Port is 10000

(9)Open Excel Sheet, select PowerPivot in ribbon and click on Manage

(10)Click on Get External Data

(11)Select – Get External Data and Data Import window and Select Others(OLEDB/ODBC)

(12)Click Build

(13)Select OLE DB Provider for ODBC

(14)Select DSN which we have created earlier in step number 8, Select Database (In Single Node HDInsight installation you don’t need to provider User Name and Password in current release but in case of HDInsight on Azure you need to provide User Name and Password) and follow the wizard for selecting tables in our case its EMP and Sales