Database Reference
In-Depth Information
Querying DynamoDB data
Querying data using SQL in DynamoDB is one the main reasons why we integrate Dy-
namoDB with EMR. To perform the same, we just need to map DynamoDB table attributes
with a hive table, and once done, you can simply write your own queries in Hive in order to
get the desired output. The following are some sample examples that would help you to
form your own queries.
Getting the total count of employees in Employee table
To get the count of employees from DynamoDB table
Employee
, we first need to create a
table with mappings in Hive. Once the table is created, you can simply run the
count(*)
query to get the exact count of employees.
CREATE EXTERNAL TABLE packtPubEmployee (empid String, yoj
String, department String, salary bigint, manager String)
STORED BY
'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES ("dynamodb.table.name" = "Employee",
"dynamodb.column.mapping" =
"empid:empId,yoj:yoj,department:dept,salary:salary,manager:manager");
SELECT COUNT(*) FROM packtPubEmployee;
Getting the total count of employees department wise
Here, we would need to
GROUP BY
employees according to their department. The steps
are simple: create a table with attribute mappings and then fire a
SELECT
query to
GROUP
BY
employees to get their count. Have a look at the following commands:
CREATE EXTERNAL TABLE packtPubEmployee (empid String, yoj
String, department String, salary bigint, ,manager String)
STORED BY
'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES ("dynamodb.table.name" = "Employee",
"dynamodb.column.mapping" =
"empid:empId,yoj:yoj,department:dept,salary:salary,manager:manager");
