Updating Complex Data Types

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

1. Create a HPE Ezmeral Data Fabric 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");

2. 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');

3. 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';

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_types_update;
                                     
     1	{"year":32}	["Dog","Cat","Pig"]	{"name":"Vasco","surname":"da Gama","age":558,"gender":"MALE"}

   • Verifying HPE Ezmeral Data Fabric 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"}}