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

  1. 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");
  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 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"}}