Updating Primitive Data Types

This section describes how to use the UPDATE statement to update primitive data types in MapR Database JSON tables, using the Hive connector.

  1. Create a MapR Database JSON table and a Hive table:
    CREATE TABLE simple_types_update (
    >>>>>>> Incorporated edit comments
      doc_id string,
      bo boolean,
      d double,
      da date,
      f float,
      i int,
      s string,
      ts timestamp,
      ti tinyint,
      bi bigint,
      si smallint,
      bin binary)
    STORED BY 'org.apache.hadoop.hive.maprdb.json.MapRDBJsonStorageHandler'
    TBLPROPERTIES("maprdb.table.name" = "/simple_types_update","maprdb.column.id" = "doc_id");
  2. Insert data into the table:
    INSERT INTO TABLE simple_types_update VALUES ('1', true, 124.14, '2017-11-29', 9192.12, 
    214566190, 'text', '2017-03-17 00:14:13', 125, 9223372036854775806, 23434, "binary string");
  3. Run the UPDATE command on the table:
    UPDATE simple_types_update
    SET da = '2018-12-11',
    bo = FALSE,
    f = 91.777
    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 simple_types_update;
                                  
      1    false    124.14    2018-12-11    91.777    214566190    text    2017-03-17 00:14:13    125    9223372036854775806    23434    binary string
    • Verifying MapR Database JSON table data:
      find '/simple_types_update'
                                          
      {"_id":"1","bi":{"$numberLong":9223372036854775806},"bin":{"$binary":"YmluYXJ5IHN0cmluZwAAAAAAAA=="},
       "bo":false,"d":124.14,"da":{"$dateDay":"2018-12-11"},
       "f":{"$numberFloat":91.777},"i":{"$numberInt":214566190},
       "s":"text","si":{"$numberShort":23434},"ti":{"$numberByte":125},"ts":{"$date":"2017-03-17T00:14:13.000Z"}}