3

We are trying to use a common data for more than one Outlook accounts. Lets say data is stored in a container which belongs to data@outlook.com and I want to read it as datasc1@outlook.com, my friend wants to read from datasc2@outlook.com.

I have common account's storage account name, container name (which is public container) but when I try to read the data using Hive with command below:

CREATE EXTERNAL TABLE deneme (t1 string, t2 string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION 'wasb://container@storageaccount.blob.core.windows.net/OUR_DATA.txt';

OR I also try command below

CREATE EXTERNAL TABLE deneme (t1 string, t2 string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE LOCATION 'wasb://container@storageaccount.blob.core.windows.net/OUR_DATA.txt?sig=ACCESS_KEY_OF_CONTAINER';

I get the error below:

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Got exception: org.apache.hadoop.fs.azure.AzureException Uploads to to public accounts using anonymous access is prohibited.)

We've tried some methods, we made the container type "Public Blob" it didn't work. We added our accounts to storage accounts default directory and it didn't work also. I tried to load data with PIG it seemed to work, but when I dump, PIG also failed.

A weird thing to me is when I run the code below on Hadoop command line it works perfectly :

hadoop fs -lsr wasb://container@storageaccount.blob.core.windows.net/

output is :

lsr: DEPRECATED: Please use 'ls -R' instead.
-rwxrwxrwx   1  145391417 2015-05-18 10:58 wasb://container@storageaccount.blob.core.windows.net/OUR_DATA.txt
-rwxrwxrwx   1   25634418 2015-05-18 10:44 wasb://container@storageaccount.blob.core.windows.net/OUR_OTHER_DATA.txt

To sum up up our problem is reading data from another Azure account with our Azure accounts, using HDInsight (Hive/PIG/Hadoop).

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Canburak Tümer
  • 993
  • 17
  • 36

2 Answers2

4

Does it work if you just point to the folder instead of a specific file? Hive expects locations to be folder paths, not specific files.

CREATE EXTERNAL TABLE deneme (t1 string, t2 string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
STORED AS TEXTFILE
LOCATION 'wasb://container@storageaccount.blob.core.windows.net/';

I was able to create a similar external table against a container configured as a "Public Container".

If you don't want to use a public container, you can include the storage key in a configuration variable directly in a Hive script like:

set fs.azure.account.key.storageaccount.blob.core.windows.net=ACCESS_KEY_OF_CONTAINER;

Or you can configure the cluster at provisioning time with access permissions to the storage account using the Additional Storage Accounts section of the custom create wizard, or by using the Add-AzureHDInsightStorage cmdlet to modify the cluster configuration prior to creating the cluster.

This article has a bunch of related information on the interactions between HDInsight and Azure Blob Storage: http://azure.microsoft.com/en-us/documentation/articles/hdinsight-hadoop-use-blob-storage/

RickH
  • 2,416
  • 16
  • 17
  • Worked perfectly, but I have one more question about this. As I've written above in the question I have two files in the folder. How does hive select the right file? Or does just concatenate two files into one external table? Because I need them as seperate tables. – Canburak Tümer May 19 '15 at 14:39
  • 1
    This question has some details about options for separating things out per-file: http://stackoverflow.com/questions/11269203/when-creating-an-external-table-in-hive-can-i-point-the-location-to-specific-fil The INPUT__FILE__NAME virtual column will have the full wasb URI of the source file, so if you want to do an exact match, you'd have to do something like: `select * from deneme WHERE INPUT__FILE__NAME='wasb://container@storageaccount.blob.core.windows.net/OUR_DATA.txt';` – RickH May 19 '15 at 14:55
2

I solved this issue by adding below mentioned properties in core-site.xml 

<property>
        <name>fs.azure.account.key.<storage account name>.blob.core.windows.net</name>
        <value><account key value></value>
</property>

Now, go to Storage account from azure portal and change access type to "container" (by default it is "private")

Aryan087
  • 516
  • 6
  • 25