Updating Complex Data Types
This section describes how to use the UPDATE
statement to update complex data types in MapR Database JSON tables, using the Hive connector.
Procedure
-
Create a MapR Database JSON table and a Hive table:
CREATE TABLE complex_types_update ( doc_id string, info MAP<STRING, INT>, pets ARRAY<STRING>, user_info STRUCT<name:STRING, surname:STRING, age:INT, gender:STRING>) STORED BY 'org.apache.hadoop.hive.maprdb.json.MapRDBJsonStorageHandler' TBLPROPERTIES("maprdb.table.name" = "/complex_types_update","maprdb.column.id" = "doc_id");
-
Insert data into the table:
INSERT INTO TABLE complex_types_update SELECT '1', map('age', 28), array('Cat', 'Cat', 'Cat'), named_struct('name', 'Santa', 'surname', 'Claus','age', 1000,'gender', 'MALE');
-
Run the UPDATE command on the table:
UPDATE complex_types_update SET info = map('year', 32), pets = array('Dog', 'Cat', 'Pig'), user_info = named_struct('name', 'Vasco', 'surname', 'da Gama','age', 558,'gender', 'MALE') WHERE doc_id = '1';
-
Verify that the data is inserted in both Hive and MapR Database JSON tables.
- Verifying Hive table
data:
hive> SELECT * FROM complex_types_update; 1 {"year":32} ["Dog","Cat","Pig"] {"name":"Vasco","surname":"da Gama","age":558,"gender":"MALE"}
- Verifying MapR Database JSON table
data:
find '/complex_types_update' {"_id":"1","info":{"year":{"$numberInt":32}},"pets":["Dog","Cat","Pig"],"user_info":{"age":{"$numberInt":558}, "gender":"MALE","name":"Vasco","surname":"da Gama"}}
- Verifying Hive table
data: