C. Map existing HBase tables to Hive

This is the third step to integrate HCL® Campaign with Hive-based Apache Hadoop data sources. If you do not have existing tables that were created in Apache HBase, you can skip this step.

Before you begin

Complete B. Configure the Hive ODBC Driver.

About this task

This step is required only if you have existing tables that were created in Apache HBase. In this case, you must make the existing HBase tables available to Apache Hive by running the CREATE EXTERNAL TABLE query. After you expose the HBase tables to Hive, the tables are then available to IBM Campaign for table mapping within Campaign.

The EXTERNAL keyword lets you create a table and provide a LOCATION so that Hive does not use a default location for the table. This is useful if you already have data generated. An EXTERNAL table points to any HDFS location for its storage, rather than being stored in a folder specified by the configuration property. When dropping an EXTERNAL table, data in the table is NOT deleted from the file system.

For information about Hive HBase integration: https://cwiki.apache.org/confluence/display/Hive/HBaseIntegration

Procedure

  1. Open the Hue editor and open the Hive Query Editor.
  2. Create and execute the CREATE EXTERNAL TABLE command.

    Use the following query as an example, substituting your own table name, field names, and other parameters. This example uses 'CampaignAccounts' as the table name and 'f' as the family name.

    CREATE EXTERNAL TABLE HiveExt_CampaignAccounts(Acct_ID INT,Indiv_ID INT,HHold_ID INT,Acct_Type_Code STRING,Acct_Status_Code INT,Acct_Open_Date INT,Acct_Balance STRING,Acct_Balance_Last_Month STRING,Acct_Balance_Avg_6Month STRING,Credit_Limit STRING,Acct_Number STRING,Last_Contact_Date STRING,Due_Date STRING) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ('hbase.columns.mapping' = ':key,f:Indiv_ID, f:HHold_ID, f:Acct_Type_Code, f:Acct_Status_Code, f:Acct_Open_Date, f:Acct_Balance, f:Acct_Balance_Last_Month, f:Acct_Balance_Avg_6Month, f:Credit_Limit, f:Acct_Number, f:Last_Contact_Date, f:Due_Date') TBLPROPERTIES ('hbase.table.name' = 'CampaignAccounts');

What to do next

D. Import and configure the BigDataODBCHiveTemplate data source template in Campaign