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.

Procedure

  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"}}