Updating Complex Nested Data Types
This section describes how to use the UPDATE
statement to update
complex nested data types in MapR Database JSON tables, using the Hive connector.
Procedure
-
Create a MapR Database JSON table and a Hive table using Hive:
CREATE TABLE complex_nested_data_type_update ( entry STRING, num INT, postal_addresses MAP <STRING, struct <USER_ID:STRING,ADDRESS:STRING,ZIP:STRING,COUNTRY:STRING>> ) stored BY 'org.apache.hadoop.hive.maprdb.json.MapRDBJsonStorageHandler' tblproperties ( "maprdb.table.name" = "/complex_nested_data_type_update", "maprdb.column.id" = "entry" );
-
Insert data into the table:
INSERT INTO TABLE complex_nested_data_type_update SELECT '001', '1', MAP ( 'Bill', Named_struct ('user_id', '1', 'address', '3205 Woodlake ct', 'zip', '45040', 'country', 'USA'));
-
Run the UPDATE command on the table by updating the
COUNTRY
value inmap(struct)
:UPDATE complex_nested_data_type_update SET postal_addresses = MAP ('Bill', Named_struct ('user_id', '1', 'address', '3205 Woodlake ct', 'zip', '45040', 'country', 'Hun')) WHERE entry = '001';
-
Verify that the data is inserted in both Hive and MapR Database JSON tables.
- Verifying Hive table
data:
hive> SELECT * FROM complex_nested_data_type_update; 001 1 {"Bill":{"user_id":"1","address":"3205 Woodlake ct","zip":"45040","country":"Hun"}}
- Verifying MapR Database JSON table
data:
find '/complex_nested_data_type_update' {"_id":"001","num":{"$numberInt":1},"postal_addresses":{"Bill":{"address":"3205 Woodlake ct","country":"Hun","user_id":"1","zip":"45040"}}}
- Verifying Hive table
data: