Updating Primitive Data Types

This section describes how to use the UPDATE statement to update primitive 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 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 HPE Ezmeral Data Fabric 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 HPE Ezmeral Data Fabric 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"}}