Hive + TableStore

Hive access to TableStore

• Prepare a data table

Let us prepare a pet table as an example, where name is the only primary key.

• The following example demonstrates how Hive consumes table data in TableStore.

1. Hive CLI.

   $ 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 in Hive.

   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 into this 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 table.

   > 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 complete sample code, see Hive+TableStore.