全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
E-MapReduce

Hive + TableStore

更新时间:2017-06-07 13:26:11

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.命令行

  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.创建一张外表

  1. CREATE EXTERNAL TABLE pet
  2. (name STRING, owner STRING, species STRING, sex STRING, birth STRING, death STRING)
  3. STORED BY 'com.aliyun.openservices.tablestore.hive.TableStoreStorageHandler'
  4. WITH SERDEPROPERTIES(
  5. "tablestore.columns.mapping"="name,owner,species,sex,birth,death")
  6. TBLPROPERTIES (
  7. "tablestore.endpoint"="YourEndpoint",
  8. "tablestore.access_key_id"="YourAccessKeyId",
  9. "tablestore.access_key_secret"="YourAccessKeySecret",
  10. "tablestore.table.name"="pet");

3.向外表中插入数据

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

4.查询数据

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

数据类型转换

TINYINT SMALLINT INT BIGINT FLOAT DOUBLE BOOLEAN STRING BINARY
INTEGER 支持, 损失精度 支持, 损失精度 支持, 损失精度 支持 支持, 损失精度 支持, 损失精度
DOUBLE 支持, 损失精度 支持, 损失精度 支持, 损失精度 支持, 损失精度 支持, 损失精度 支持
BOOLEAN 支持
STRING 支持
BINARY 支持

附录

完整示例代码请参考:

本文导读目录