Updating Complex Nested Data Types

This section describes how to use the UPDATE statement to update complex nested data types in HPE Ezmeral Data Fabric Database JSON tables, using the Hive connector.

Procedure

  1. Create a HPE Ezmeral Data Fabric 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" 
);
  2. 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'));
  3. Run the UPDATE command on the table by updating the COUNTRY value in map(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';
  4. Verify that the data is inserted in both Hive and HPE Ezmeral Data Fabric 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 HPE Ezmeral Data Fabric 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"}}}