Using an HDInsight Cluster with Alternate Storage Accounts and Metastores

Using an HDInsight Cluster with Alternate Storage Accounts and Metastores




You can treat an HDInsight cluster as a resource that can access data and metadata beyond the storage accounts and metastore specified when you created the cluster. Multiple WebHCat jobs can be submitted to the cluster and each job can carry with it a description of its data and metadata. This works for Hive, MapReduce (MR), and MapReduce streaming. It currently works for Pig with storage accounts but not for metadata.

Common across Hive, Pig, MR, and MR streaming is that you can pass in a description of a new storage account (it's name and plain text key) in the arguments to a WebHCat job, and the job can access the data in the account. Any number of accounts can be specified with a job

The default behavior of HDInsight is to use the storage accounts defined in %HADOOP_HOME%/conf/core-site.xml and the metastore database defined in %HIVE_HOME%/conf/hive-site.xml. The method described below allows you to override the built-in storage accounts and metastore. Of course, users who can submit jobs to your cluster can access the storage accounts and metastore defined with the cluster.

You can edit your core-site.xml and hive-site.xml but this is not recommended. Your cluster head node may be re-imaged or migrated at any time, and any changes to those files will be lost.


↑ Return to Top


You can submit a Hive job through WebHCat and specify a per-job metastore database and per-job storage accounts using a technique similar to the following example, using the HDInsight PowerShell interface:

$subscriptionName = "<subscriptionname>"

$clusterName = "<cluster name>"

Select-AzureSubscription -SubscriptionName $subscriptionName

Use-AzureHDInsightCluster $clusterName

$querystring = "show tables; select name, salary from emp;"

[HashTable] $defines

$defines = @{}

$defines.Add("hive.metastore.uris", "")

$defines.Add("javax.jdo.option.ConnectionURL", "jdbc:sqlserver://<name>;database=<dbname>; encrypt=true;trustServerCertificate=true;create=false")

$defines.Add("<name1>", "<clear text key1>")

$defines.Add("<name2>", "<clear text key2>")

$defines.Add("javax.jdo.option.ConnectionUserName", "<user>@<dbserver>")

$defines.Add("javax.jdo.option.ConnectionPassword", "<password>")

Invoke-Hive -Defines $defines -Query $queryString


Important: there is a bug in WebHCat so that if your password contains an exclamation point (!) character, this will fail, because the exclamation point is not properly passed by WebHCat to the Hive command line when it is started up. It's recommended that you use passwords containing only upper and lower case letters, and numbers. Passwords of this form have been successfully tested with this method.        

Metastore database initialization: You must initialize an Azure SQL DB database with the metadata tables to do this. You can do that quickest by creating a new database with collation SQL_Latin1_General_CP1_CI_AS and then running a copy of the file %HIVE_HOME%/HiveMetaStore.sql from a production cluster on the Azure SQL DB database you want to use for metadata. Alternatively, you can create an Azure SQL DB database and then create a cluster, passing it this database. That will initialize the database. Then you can drop the cluster, but use the database for other purposes. This is slow, but it will work and can be automated.

Storage account keys: Use the clear text key for your storage account with this method. You can get this from the AzureDB web portal by clicking the MANAGE ACCESS KEYS button at the bottom of the screen and then copying the PRIMARY ACCESS KEY. The picture below shows where to find the PRIMARY ACCESS KEY.

How it works:  This method causes a local metastore to be started for the Hive job. This is caused by setting hive.metastore.uris="". It works whether or not the cluster’s metastore service is running. The local metastore runs inside the Hive client program so it is automatically cleaned up when the Hive client terminates.

↑ Return to Top


It's possible to pass new storage account definitions to HDInsight as part of a WebHCat MR job submission. To do so, pass one or more storage account names and plain text keys as a define argument to the job. For example, with MR, you can do the following using the HDInsight PowerShell interface:

[HashTable] $arguments

$arguments = @{ "<accountname>" = "<key>" }

# Define the MapReduce job

$wordCountJobDefinition = New-AzureHDInsightMapReduceJobDefinition -JarFile "wasb:///example/jars/hadoop-examples.jar" -ClassName "wordcount" -Arguments "wasbs://<containername>@<accountname>", "wasbs://<containername>@<accountname>" -Defines $arguments

# Submit the job

Select-AzureSubscription $subscriptionName

$wordCountJob = Start-AzureHDInsightJob -Cluster $clusterName -JobDefinition $wordCountJobDefinition | Wait-AzureHDInsightJob -WaitTimeoutInSeconds 3600

# Get the job output

Get-AzureHDInsightJobOutput -Cluster $clusterName -JobId $wordCountJob.JobId -StandardError

↑ Return to Top

MapReduce Streaming

The following example shows how to prepare and submit an MR streaming job, passing in storage account keys as job arguments:

# Prepare storage keys as job arguments

[HashTable] $arguments

$arguments = @{

"<accountname1>" = "<key1>";

"<accountname2>" = "<key2>"


# Define the MapReduce job

$wordCountJobDefinition = New-AzureHDInsightStreamingMapReduceJobDefinition -Files "/example/apps/wc.exe", "/example/apps/cat.exe" -InputPath "wasbs://<containername>@<accountname1>" -OutputPath "wasbs://<containername>@<accountname2>" -Mapper "cat.exe" -Reducer "wc.exe" -Defines $arguments


# Submit the job

$wordCountJob = Start-AzureHDInsightJob -Cluster $clusterName -JobDefinition $wordCountJobDefinition | Wait-AzureHDInsightJob -WaitTimeoutInSeconds 3600


# Get the job output

Get-AzureHDInsightJobOutput -Cluster $clusterName -JobId $wordCountJob.JobId -StandardError


↑ Return to Top


With Pig, you can specify additional storage accounts similar to how it's done with MR, as in the following example.


$subscriptionName = "<subscriptionname>"

$clusterName = "<clustername>"

Select-AzureSubscription -SubscriptionName $subscriptionName

Use-AzureHDInsightCluster $clusterName

$querystring = "emp = load 'wasbs://<containername>@<accountname>';" + "dump emp;"

[String[]] $arguments

$arguments = @( '<accountname><plain text key>')

$pigJobDefinition = New-AzureHDInsightPigJobDefinition -Query $queryString -Arguments $arguments

$pigJob = Start-AzureHDInsightJob -Cluster $clusterName -JobDefinition $pigJobDefinition

Wait-AzureHDInsightJob -Job $pigJob -WaitTimeoutInSeconds 3600

Get-AzureHDInsightJobOutput -Cluster $clusterName -JobId $pigJob.JobId -StandardOutput

↑ Return to Top

Best Practices

Keep your storage accounts and metastore database together as a unit in your application. It may, for example, not be possible to drop an external table from a metastore unless the storage account for the table is accessible to Hive when you run the DROP TABLE command to remove the table.

Caution: Concurrently updating metadata in the same metastore database from multiple different jobs that each set hive.metastore.uris to the empty string, and thus use a local metastore, has been observed to cause metastore database corruption (HIVE-6918). Consider serializing operations that update metadata, such as altering of table partitions, if multiple jobs will access the same metastore database using this approach.


↑ Return to Top

Sort by: Published Date | Most Recent | Most Useful
  • Thank you Mr. Eric

  • Hi,

    I am trying to submit a pig latin job using .Net sdk to submit a job. I am sending the key in the arguments


    in the script I am using the following


    and I am getting this error on the log.

    2014-08-22 08:34:12,574 [main] INFO  org.apache.pig.Main - Apache Pig version 0. (r: unknown) compiled Aug 15 2014, 22:20:08

    2014-08-22 08:34:12,574 [main] INFO  org.apache.pig.Main - Logging error message

    s to: C:\apps\dist\hadoop-\logs\pig_1408696452574.log

    SLF4J: Class path contains multiple SLF4J bindings.

    SLF4J: Found binding in [jar:file:/C:/apps/dist/hadoop-



    SLF4J: Found binding in [jar:file:/C:/apps/dist/pig-


    SLF4J: See for an explanation.

    SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

    2014-08-22 08:34:13,808 [main] ERROR org.apache.pig.Main - ERROR 2997: Encounter

    ed IOException. File '

    net=<mykey> does not exist

    Details at logfile: C:\apps\dist\hadoop-\logs\pig_140869645257


    This script works fine if I use the emulator and put the blob information in the core-site.xml

  • C