Step 4: Set Up a Hive Table to Run Hive Commands

To run Hive commands interactively

1. At the hadoop command prompt for the current master node, type hive.

   You should see a hive prompt: hive>

2. Enter a Hive command that maps a table in the Hive application to the data in Amazon DynamoDB. This table acts as a reference to the data stored in Amazon DynamoDB; the data is not stored locally in Hive and any queries using this table run against the live data in Amazon DynamoDB, consuming the table’s read or write capacity every time a command is run. If you expect to run multiple Hive commands against the same dataset, consider exporting it first.

   The following shows the syntax for mapping a Hive table to an Amazon DynamoDB table.

   CREATE EXTERNAL TABLE hive_tablename (hive_column1_name column1_datatype, hive_column2_name column2_datatype...)
   STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler' 
   TBLPROPERTIES ("dynamodb.table.name" = "dynamodb_tablename", 
   "dynamodb.column.mapping" = "hive_column1_name:dynamodb_attribute1_name,hive_column2_name:dynamodb_attribute2_name...");
                   

   When you create a table in Hive from Amazon DynamoDB, you must create it as an external table using the keyword EXTERNAL. The difference between external and internal tables is that the data in internal tables is deleted when an internal table is dropped. This is not the desired behavior when connected to Amazon DynamoDB, and thus only external tables are supported.

   For example, the following Hive command creates a table named "hivetable1" in Hive that references the Amazon DynamoDB table named "dynamodbtable1". The Amazon DynamoDB table "dynamodbtable1" has a hash-and-range primary key schema. The hash key element is "name" (string type), the range key element is "year" (numeric type), and each item has an attribute value for "holidays" (string set type).

   CREATE EXTERNAL TABLE hiveTableName (col1 string, col2 bigint, col3 array<string>)
   STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler' 
   TBLPROPERTIES ("dynamodb.table.name" = "dynamodbtable1", 
   "dynamodb.column.mapping" = "col1:name,col2:year,col3:holidays"); 
                   

   Line 1 uses the HiveQL CREATE EXTERNAL TABLE statement. For "hivetable1", you need to establish a column for each attribute name-value pair in the Amazon DynamoDB table, and provide the data type. These values are not case-sensitive, and you can give the columns any name (except reserved words).

   Line 2 uses the STORED BY statement. The value of STORED BY is the name of the class that handles the connection between Hive and Amazon DynamoDB. It should be set to 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'.

   Line 3 uses the TBLPROPERTIES statement to associate "hivetable1" with the correct table and schema in Amazon DynamoDB. Provide TBLPROPERTIES with values for the dynamodb.table.name parameter and dynamodb.column.mapping parameter. These values are case-sensitive.

   Note

   All Amazon DynamoDB attribute names for the table must have corresponding columns in the Hive table. Otherwise, the Hive table won't contain the name-value pair from Amazon DynamoDB. If you do not map the Amazon DynamoDB primary key attributes, Hive generates an error. If you do not map a non-primary key attribute, no error is generated, but you won't see the data in the Hive table. If the data types do not match, the value will be null.

To cancel a Hive request

When you execute a Hive query, the initial response from the server includes the command to cancel the request. To cancel the request at any time in the process, use the Kill Command from the server response.

1. Enter Ctrl+C to exit the command line client.

2. At the shell prompt, enter the Kill Command from the initial server response to your request.

   Alternatively, you can run the following command from the command line of the master node to kill the Hadoop job, where job-id is the identifier of the Hadoop job and can be retrieved from the Hadoop user interface. For more information about the Hadoop user interface, go to How to Use the Hadoop User Interface in the Amazon Elastic MapReduce Developer Guide.

   hadoop job -kill job-id