2

We are trying to connect MS 9.4 to HBase via Impala connector.

First we created the hive tables liking them to HBase tables with following create table (as we saw in the docs):

CREATE TABLE hiveTableName1 
(key int, columnName1 codClient, columnName2 clientName)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,columnfamily1:columnName1,columnfamily1:columnName2")
TBLPROPERTIES ("hbase.table.name" = "hbaseTableName1");

We did this twice, since we want to crete two hive tables and their correspondent hbase tables, in order to perform a join between them later with MS.

For the connection between MS with HBase, we follow the steps by selecting the MicroStrategy ODBC Driver for Impala Wire Protocol, and filling in the Data Source Name (Impala Data Source previously created with the Impala Driver), host and port (both for Impala installation in our AWS infraestructure) and impala/impala for credentials.

The thing is that when we finish complete the wizard and select the default namespace (which is the only one available. No any other ns has been created), we can see the hive tables that we created before, instead of the hbase tables.

I mean:

hiveTableName1
hiveTableName2

instead of 

hbaseTableName1
hbaseTableName2

And, since these are the only tables availables, we only can perform our report with these two tables: a very easy join between these two tables by one field. Both tables have 200.000 records and the join takes more than 1 minute to complete. I'm sure that we are missing something here, and the process of linking hive tables to hbase ones are not completely right. Is there a way to be able to connect to these two hbase tables instead of hive ones? Any help will be really appreciated.

Sateesh Pagolu
  • 9,282
  • 2
  • 30
  • 48
josele
  • 79
  • 1
  • 9

1 Answers1

0

1. HBase does not support SQL and does not support the concept of "join" anyway.

2. Mapping Hive tables on HBase tables means that every Hive query triggers a full scan on HBase side, then the result is fed to a MapReduce batch job that does the filters and the joins.

Bottom line: 1 min is quite fast for what you are doing.

If you expect sub-second results, try some "small data" technologies (e.g. MySQL, Oracle, even MS Access) or forget about joins. For sub-minutes results, you might give a try to Apache Phoenix: it's a HBase wrapper with indexes and some kind of SQL. Not sure about ODBC/JDBC drivers though.

Samson Scharfrichter
  • 8,884
  • 1
  • 17
  • 36
  • Thank you very much for your quick answer, Samson. Since the for the MS - HBase connection we had to use the MS Impala connector, and Impala provides sql join feature, we thought a join between two tables as a good performance test. It's supposed that this Impala join is "translated" in some way to perform the neccesary query on hbase, isn't it? – josele Aug 24 '15 at 06:08
  • Anyway, based on your 2nd point I think that mapping Hive tables to HBase ones in order to use the MS Impala connector doesn't seem to be a good idea for low latency database access. This is why I don't see the point in using hbase here (since we still have to go through hive, at list for using MS) Phoenix was the other option. But Phoenix connector for MS is only provided by MS 10 release, so we should upgrade. I think this could be a better option. Although the use of any other database like Oracle or Mongo doesn't seem to be a bad idea. – josele Aug 24 '15 at 06:09
  • Impala claims that it's "faster than Hive" (and cynics add: "what isn't?") but that's using its own query processor and its own storage engine. Chaining Hive and HBase means that the weakest link, etc. – Samson Scharfrichter Aug 24 '15 at 06:27
  • In the future, maybe Presto will be an option for you. Teradata started to support it, both the Open Source core (courtesy of Facebook) and some future paid extensions. – Samson Scharfrichter Aug 24 '15 at 06:29