Big Data Analytics Using Hive on Microsoft Azure

1. Add-AzureAccount
   
   Authenticates Azure PowerShell to your Azure subscription.
   
   
2. New-AzureHDInsightHiveJobDefinition
   
   Creates a new job definition using the specified HiveQL statements.
   
   
3. Start-AzureHDInsightJob
   
   Sends the job definition to HDInsight, starts the job and returns a job object that can be used to check the status of the job.
   
   
4. Wait-AzureHDInsightJob
   
   Uses the job object to check the status of the job. It will wait until the job completes or the wait time is exceeded.
   
   
5. Get-AzureHDInsightJobOutput
   
   Used to retrieve the output of the job.
   
   
6. Invoke-Hive
   
   Used to run HiveQL statements. This will block the query completion, then the results will be returned.
   
   
7. Use-AzureHDInsightCluster
   
   Sets the current cluster to use for the Invoke-Hive command.

Executing HiveQL Queries

In the following example, a new table webactivity shall be created containing aggregated data from the table cleanlog (created in the previous example).

The HiveQL to be used is as follows:

1. Drop the table webactivity if it exists as in the following:
   
   1. DROP TABLE webactivity;
2. Create an external table webactivity as in the following:
   
   1. CREATE EXTERNAL TABLE webactivity(
   2. log_date STRING, page_hits INT, bytes_recvd INT,
   3. bytes_sent INT
   4. ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE LOCATION '/data/webactivity';
3. Aggregate data from the table cleanlog and insert into the table webactivity as in the following:
   
   1. FROM
   2. cleanlog INSERT INTO TABLE webactivity
   3. SELECT
   4. log_date,
   5. COUNT(*),
   6. SUM(sc_byte),
   7. SUM(cs_byte)
   8. GROUP BY
   9. log_date
   10. ORDER BY
   11. log_date;
   12. "

Using AzureHDInsightHiveJobDefinition Cmdlet

The following are the PowerShell scripts to execute the queries explained above.

1. Specify the cluster into that to do the queries.
   
   $clusterName = "chervinehadoop"
   
   
2. The HiveQL Queries
   
   1. $hiveQL = "DROP TABLE webactivity;CREATE EXTERNAL TABLE webactivity(log_date STRING, page_hits INT, bytes_recvd INT, bytes_sent INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' STORED AS TEXTFILE LOCATION '/data/webactivity'; FROM cleanlog INSERT INTO TABLE webactivity SELECT log_date, COUNT(*), SUM(sc_byte), SUM(cs_byte) GROUP BY log_date ORDER BY log_date;"
3. Define the hive job for the Azure HDInsight service.
   
   $jobDef = New-AzureHDInsightHiveJobDefinition -Query $hiveQL
   
   
4. Starts the Azure HDInsight job in the cluster chervinehadoop.
   
   $hiveJob = Start-AzureHDInsightJob –Cluster $clusterName –JobDefinition $jobDef
   
   
5. Awaits the completion or failure of the HDInsight job and shows its progress.
   
   Wait-AzureHDInsightJob -Job $hiveJob -WaitTimeoutInSeconds 3600
   
   
6. Retrieve the log output for a job as in the following:
   
   Get-AzureHDInsightJobOutput -Cluster $clusterName -JobId $hiveJob.JobId -StandardError
   
   
   
   
   
   
   

Using the Invoke-Hive Cmdlet

Once the table has been created and populated, it can then be queried. The easiest way to execute Hive Queries is to use the Invoke-Hive cmdlet.

1. Define the cluster and the query to be used as in the following:
   
   $clusterName = "chervinehadoop"
   
   1. $hiveQL = "SELECT * FROM webactivity;"
2. Select the Azure HDInsight cluster to be used by the Invoke-Hive cmdlet for job submission as in the following:
   
   Use-AzureHDInsightCluster $clusterName
   
   
3. Submit Hive queries to the HDInsight cluster.
   
   Invoke-Hive -Query $hiveQL
   
   
   

Querying Hive from the Azure Portal

So far, this article has focused on the technical parts, how Hive works and how to automate the process using PowerShell. Using the command line and PowerShell is most commonly intended for developers and is not user friendly.

Hopefully, Hive Queries can also be executed from the portal that makes it much easier to use. To use this feature, go to the HDInsight Homepage on Azure and click on the Query Console.



Once you are logged in, you will be routed to the Microsoft Azure HDInsight Query Console Homepage. From here, you can view some sample queries from the gallery, write your own queries using the Hive editor, view the jobs execution history and browse the files in the cluster.

In this example, the same queries as in the PowerShell example shall be written but from the Azure Portal.

To do so, click on the Hive Editor and paste in the queries.



Once you click on submit, the processing will start and you will be able to monitor the status from the Job Session Pane.



When it completes, the file will be saved in the blob at the path specified.


Conclusion and Next Steps

This article explained the basics of Hive then provided examples of using Hive from the command line, Power Shell and from the portal.

The main advantages of Hive is that it's very easy to use and quicker to develop Big Data solutions since HiveQL is very similar to SQL but translates to Map Reduce code behind the scenes despite being transparent to the user.

Moreover, the use of external tables makes it possible to process data without actually storing in HDFS and keeps the data, even if the cluster is deleted, and is also a very good benefit in terms of cost.

1. http://azure.microsoft.com/en-in/documentation/articles/hdinsight-use-hive/
   
2. http://azure.microsoft.com/en-in/documentation/articles/hdinsight-hadoop-use-hive-powershell/
   
3. https://msdn.microsoft.com/en-us/library/dn593749.aspx
   
4. https://msdn.microsoft.com/en-us/library/dn593743.aspx
   
5. https://msdn.microsoft.com/en-us/library/dn593748.aspx
   
6. https://msdn.microsoft.com/en-us/library/dn593738.aspx