STEP-1) -Create a Hive table CLARITY.TEST_PATIENT_BIGINT CREATE TABLE CLARITY.TEST_PATIENT_BIGINT ( PAT_ID BIGINT , PAT_NAME STRING , ADD_LINE_1 STRING , ADD_LINE_2 STRING , CITY STRING , STATE_C STRING ) CLUSTERED BY (PAT_ID) into 100 buckets stored as orc TBLPROPERTIES ("orc.compress"="SNAPPY", "orc.compress.size"="8192", "transactional"="true"); hive> > describe clarity.test_patient_bigint; OK pat_id bigint pat_name string add_line_1 string add_line_2 string city string state_c string Time taken: 0.518 seconds, Fetched: 6 row(s) OK Time taken: 13.743 seconds STEP-2) - Populate Hive table clarity.test_patient_bigint hive> select * from clarity.test_patient_bigint; OK 1304439300 ABC 123, USA 52555 Chicago 222 1402634508 ABC 123, USA 52555 Chicago 222 1502914232 ABC 123, USA 52555 Chicago 222 1402823238 ABC 123, USA 52555 Chicago 222 1401485246 ABC 123, USA 52555 Chicago 222 1502455566 ABC 123, USA 52555 Chicago 222 1304240869 ABC 123, USA 52555 Chicago 222 1501728476 ABC 123, USA 52555 Chicago 222 1304732476 ABC 123, USA 52555 Chicago 222 150456491 ABC 123, USA 52555 Chicago 222 Time taken: 0.114 seconds, Fetched: 10 row(s) hive> STEP-3) - Create phoenix table CLARITY_STAGE.TEST_STG_PATIENT_BIGINT ***Create phoenix table CREATE TABLE CLARITY_STAGE.TEST_STG_PATIENT_BIGINT ( PAT_ID BIGINT , L.PAT_NAME VARCHAR , L.ADD_LINE_1 VARCHAR , L.ADD_LINE_2 VARCHAR , L.CITY VARCHAR , L.STATE_C VARCHAR CONSTRAINT PK_PATIENT PRIMARY KEY ( PAT_ID ) ) COMPRESSION='SNAPPY', SALT_BUCKETS = 12; STEP-4) - *** Create hive over hbase table CLARITY_STAGE.TEST_PATIENT_HIVE_HBASE_BIGINT CREATE EXTERNAL TABLE CLARITY_STAGE.TEST_PATIENT_HIVE_HBASE_BIGINT ( PAT_ID BIGINT , PAT_NAME STRING , ADD_LINE_1 STRING , ADD_LINE_2 STRING , CITY STRING , STATE_C STRING ) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ('hbase.columns.mapping' = ' :key , L:PAT_NAME , L:ADD_LINE_1 , L:ADD_LINE_2 , L:CITY , L:STATE_C ') tblproperties ('hbase.table.name' = 'CLARITY_STAGE.TEST_STG_PATIENT_BIGINT'); STEP-5) - *** Insert the data from hive table CLARITY.TEST_PATIENT_BIGINT to hive over hbase table (CLARITY_STAGE.TEST_PATIENT_HIVE_HBASE_BIGINT) hive> INSERT INTO CLARITY_STAGE.TEST_PATIENT_HIVE_HBASE_BIGINT SELECT * FROM CLARITY.TEST_PATIENT_BIGINT; hive> INSERT INTO CLARITY_STAGE.TEST_PATIENT_HIVE_HBASE_BIGINT SELECT * FROM CLARITY.TEST_PATIENT_BIGINT; Query ID = svchadoop_20160324105544_7cadaf86-61b6-4349-9000-fa56bf891fe6 Total jobs = 1 Launching Job 1 out of 1 Status: Running (Executing on YARN cluster with App id application_1458767998028_0854) -------------------------------------------------------------------------------- VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED -------------------------------------------------------------------------------- Map 1 .......... SUCCEEDED 1 1 0 0 3 0 -------------------------------------------------------------------------------- VERTICES: 01/01 [==========================>>] 100% ELAPSED TIME: 10.37 s -------------------------------------------------------------------------------- OK Time taken: 13.282 seconds hive> STEP-6) - *** Select all the records from Hive over Hbase table (CLARITY_STAGE.TEST_PATIENT_HIVE_HBASE_BIGINT) hive> SELECT * FROM CLARITY_STAGE.TEST_PATIENT_HIVE_HBASE_BIGINT; OK 1304240869 ABC 123, USA 52555 Chicago 222 1304439300 ABC 123, USA 52555 Chicago 222 1304732476 ABC 123, USA 52555 Chicago 222 1401485246 ABC 123, USA 52555 Chicago 222 1402634508 ABC 123, USA 52555 Chicago 222 1402823238 ABC 123, USA 52555 Chicago 222 1501728476 ABC 123, USA 52555 Chicago 222 1502455566 ABC 123, USA 52555 Chicago 222 1502914232 ABC 123, USA 52555 Chicago 222 150456491 ABC 123, USA 52555 Chicago 222 Time taken: 2.5 seconds, Fetched: 10 row(s) hive> STEP-7) - *** Select all the records from phoenix table CLARITY_STAGE.TEST_STG_PATIENT_BIGINT; *** Query the phoenix table 0: jdbc:phoenix:lnxhdpdp07.smrcy.com,lnxhdpdp> SELECT * FROM CLARITY_STAGE.TEST_STG_PATIENT_BIGINT; +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+ | PAT_ID | PAT_NAME | ADD_LINE_1 | ADD_LINE_2 | CITY | STATE_C | +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+ | -5534866551809755082 | ABC | 123, USA | 52555 | Chicago | 222 | | -5534866543236009168 | ABC | 123, USA | 52555 | Chicago | 222 | | -5534866530351565769 | ABC | 123, USA | 52555 | Chicago | 222 | | -5462812247649340876 | ABC | 123, USA | 52555 | Chicago | 222 | | -5462811139631729360 | ABC | 123, USA | 52555 | Chicago | 222 | | -5462811131058638285 | ABC | 123, USA | 52555 | Chicago | 222 | | -5390754640826977225 | ABC | 123, USA | 52555 | Chicago | 222 | | -5390753554150116042 | ABC | 123, USA | 52555 | Chicago | 222 | | -5390753532742454733 | ABC | 123, USA | 52555 | Chicago | 222 | | -5390751350815180495 | ABC | 123, USA | 52555 | Chicago | 222 | +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+ 10 rows selected (0.357 seconds) 0: jdbc:phoenix:lnxhdpdp07.smrcy.com,lnxhdpdp> Observations: The PAT_ID in the phoenix table CLARITY_STAGE.TEST_STG_PATIENT_BIGINT is showing negative values for all PAT_ID. The corresponding Hbase table has the correct value.