edit-icon download-icon

Hive + TableStore

Last Updated: Dec 04, 2017

Hive access to TableStore

  • Prepare a data table

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

name owner species sex birth death
Fluffy Harold cat f 02/04/1993 -
Claws Gwen cat m 03/17/1994 -
Buffy Harold dog f 05/13/1989 -
Fang Benny dog m 08/27/1990 -
Bowser Diane dog m 08/31/1979 07/29/1995
Chirpy Gwen bird f 09/11/1998 -
Whistler Gwen bird - 12/09/1997 -
Slim Benny snake m 04/29/1996 -
Puffball Diane hamster f 03/30/1999 -
  • The following example demonstrates how Hive consumes table data in TableStore.
  1. Hive CLI.

    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 an external table in Hive.

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

    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. Query table.
    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

Data type conversion

TINYINT SMALLINT INT BIGINT FLOAT DOUBLE BOOLEAN STRING BINARY
INTEGER Yes, loss of precision Yes, loss of precision Yes, loss of precision Yes Yes, loss of precision Yes, loss of precision - - -
DOUBLE Yes, loss of precision Yes, loss of precision Yes, loss of precision Yes, loss of precision Yes, loss of precision Yes - - -
BOOLEAN - - - - - - Yes - -
STRING - - - - - - - Yes -
BINARY - - - - - - - - Yes

Appendix

For complete sample code, see Hive+TableStore.

Thank you! We've received your feedback.