Process Table Store data in Hive

Connect Hive to Table Store

• Prepare a data table

  Create a table named pet and set the name field as the primary key.

  name	owner	species	sex	birth	death
  Fluffy	Harold	cat	f	1993-02-04
  Claws	Gwen	cat	m	1994-03-17
  Buffy	Harold	dog	f	1989-05-13
  Fang	Benny	dog	m	1990-08-27
  Bowser	Diane	dog	m	1979-08-31	1995-07-29
  Chirpy	Gwen	bird	f	1998-09-11
  Whistler	Gwen	bird	-	1997-12-09
  Slim	Benny	snake	m	1996-04-29
  Puffball	Diane	hamster	f	1999-03-30

• The following example describes how to process Table Store data in Hive.
  1. Command line

     $ HADOOP_HOME=YourHadoopDir HADOOP_CLASSPATH=emr-tablestore-<version>.jar:tablestore-4.1.0-jar-with-dependencies.jar:joda-time-2.9.4.jar bin/hive

  2. Create an external table

     CREATE EXTERNAL TABLE pet
       (name STRING, owner STRING, species STRING, sex STRING, birth STRING, death STRING)
       STORED BY 'com.aliyun.openservices.tablestore.hive.TableStoreStorageHandler'
       WITH SERDEPROPERTIES(
         "tablestore.columns.mapping"="name,owner,species,sex,birth,death")
       TBLPROPERTIES (
         "tablestore.endpoint"="YourEndpoint",
         "tablestore.access_key_id"="YourAccessKeyId",
         "tablestore.access_key_secret"="YourAccessKeySecret",
         "tablestore.table.name"="pet");

  3. Insert data to the external table

     INSERT INTO pet VALUES("Fluffy", "Harold", "cat", "f", "1993-02-04", null);
     INSERT INTO pet VALUES("Claws", "Gwen", "cat", "m", "1994-03-17", null);
     INSERT INTO pet VALUES("Buffy", "Harold", "dog", "f", "1989-05-13", null);
     INSERT INTO pet VALUES("Fang", "Benny", "dog", "m", "1990-08-27", null);
     INSERT INTO pet VALUES("Bowser", "Diane", "dog", "m", "1979-08-31", "1995-07-29");
     INSERT INTO pet VALUES("Chirpy", "Gwen", "bird", "f", "1998-09-11", null);
     INSERT INTO pet VALUES("Whistler", "Gwen", "bird", null, "1997-12-09", null);
     INSERT INTO pet VALUES("Slim", "Benny", "snake", "m", "1996-04-29", null);
     INSERT INTO pet VALUES("Puffball", "Diane", "hamster", "f", "1999-03-30", null);

  4. Query data

     > SELECT * FROM pet;
     Bowser  Diane   dog     m       1979-08-31      1995-07-29
     Buffy   Harold  dog     f       1989-05-13      NULL
     Chirpy  Gwen    bird    f       1998-09-11      NULL
     Claws   Gwen    cat     m       1994-03-17      NULL
     Fang    Benny   dog     m       1990-08-27      NULL
     Fluffy  Harold  cat     f       1993-02-04      NULL
     Puffball        Diane   hamster f       1999-03-30      NULL
     Slim    Benny   snake   m       1996-04-29      NULL
     Whistler        Gwen    bird    NULL    1997-12-09      NULL
     > SELECT * FROM pet WHERE birth > "1995-01-01";
     Chirpy  Gwen    bird    f       1998-09-11      NULL
     Puffball        Diane   hamster f       1999-03-30      NULL
     Slim    Benny   snake   m       1996-04-29      NULL
     Whistler        Gwen    bird    NULL    1997-12-09      NULL

Data type conversion

Appendix

For the complete sample code, see

• Process Table Store data in Hive