Hive + TableStore

Hive接入TableStore

• 准备一张数据表

  创建一张表 pet，其中 name 为主键。

  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	-

• 下面这个例子示例了如何在 Hive 中处理 TableStore 中的数据。
  1. 命令行

     $ 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 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 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. 查询数据

     > 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

数据类型转换

附录

完整示例代码请参见：Hive+TableStore。