0

I want to read a .tsv file from Hbase into hive. The file has a columnfamily, which has 3 columns inside: news, social and all. The aim is to store these columns in an table in hbase which has the columns news, social and all.

CREATE EXTERNAL TABLE IF NOT EXISTS topwords_logs (key String,  
columnfamily  String , wort String , col String, occurance int)ROW FORMAT 
DELIMITED FIELDS TERMINATED BY '\t'STORED AS TEXTFILE LOCATION '/home
/hfu/Testdaten';

load data local inpath '/home/hfu/Testdaten/part-r-00000.tsv' into table topwords_logs;

CREATE TABLE newtopwords (columnall int,  columnsocial int , columnnews int) PARTITIONED BY(wort STRING)  STORED AS SEQUENCEFILE;

Here i created a external table, which contain the data from hbase. Further on I created a table with the 3 columns.

What i have tried so far is this: 

insert overwrite table newtopwords partition(wort)
select occurance ,'1', '1' , wort from topwords_log;

This Code works fine, but i have for each column an extra where clause. How can I insert data like this? 

insert overwrite table newtopwords partition(wort)
values(columnall,(select occurance from topwords_logs where col =' all')),(columnnews,( select occurance from topwords_logs where col =' news')) ,(columnsocial,( select occurance from topwords_logs where col =' social')),(wort,(select wort from topwords_log));

This code isnt working ->NoViableAltException.

On every example I just see Code, where they insert data without a Where clause. How can I insert Data with a Where clause?

dino
  • 239
  • 3
  • 12
  • create a temporary table having the column information as per your requirement(that is using the desired where clause) and after that do insert overwrite.... – madhu Oct 26 '15 at 14:07
  • But then i still have the problem with the insert. My problem is that i cant use different where clause in the insert overwrite. I found a ( ugly) solution for my problem. I create 3 tables and fill each of them with a insert statement. After that i Join them together. But i bet there is a nicer solution for my problem. – dino Oct 26 '15 at 15:25
  • insert overwrite generally works for multiple where clauses provided we insert data row by row.... But in your case you want to insert each column separately.... – madhu Oct 27 '15 at 12:00
  • I made another question, because I think im doing something basicly wrong with converting hbase table into hive. http://stackoverflow.com/questions/33440186/how-transfer-a-table-from-hbase-to-hive – dino Oct 30 '15 at 16:03

0 Answers0