Update And Delete For Version 0.14 of Hive

 Update And Delete For  Version 0.14 of Hive (hands on explanation).

 -------------------------
step 1: run a command to see which version are u running

Run this at CLI
cli>hive --version ( will give a result)
or
cli>ls /usr/lib/hive*/lib | grep metastore


Step 2:Set hive.optimize.sort.dynamic.partition=false;

Step 3: Run the command
hive>update customers_temp SET category="A" where fn="Lisa" AND ln="Scott";

Hint: Sub queries are not supported, Partition columns cannot be updated, Bucketing Columns cannot be updated

Arithmetic operators ,UDF's ,casts,literals , etc are supported. If a WHERE clause is defined then only rows that match the WHERE clause will be updated

___-
hive> delete from customers_temp where custid = 256 ;

------------------------
Major and Minor compaction  must be specified. Any changes to the table is stored as delta files in HDFS. Hdfs data adheres to  is write once and read many. So any changes to data  are stored as delta files. 

some of the properties to work with. 

Configure the following Hive properties to enable transactions:

    hive.txn.manager

    hive.compactor.initiator.on

    hive.compactor.worker.threads
-------------------------------------------
If you experience problems while enabling Hive transactions, check the Hive log file at /tmp/hive/hive.log.

Performing Manual Compactions

Hive administrators use the ALTER TABLE DDL command to queue requests that compact base and delta files for a table or partition:

ALTER TABLE tablename [PARTITION (partition_key='partition_value' [,...])] COMPACT 'compaction_type'

Use the SHOW COMPACTIONS command to monitor the progress of the compactions:
hive >SHOW COMPACTIONS

Note:

ALTER TABLE will compact tables even if the NO_AUTO_COMPACTION table property is set.

hive>show transactions;
 
hive>show locks;

Lock state:

    Acquired - transaction initiator hold the lock

    Waiting - transaction initiator is waiting for the lock

    Aborted - the lock has timed out but has not yet been cleaned

Lock type:

    Exclusive - the lock may not be shared

    Shared_read - the lock may be shared with any number of other shared_read locks

    Shared_write - the lock may be shared by any number of other shared_read locks but not with other shared_write locks
------------------------------------

hive > SHOW COMPACTIONS

Compaction state:

    Initiated - waiting in queue

    Working - currently compacting

    Ready for cleaning - compaction completed and old files scheduled for removal

hive >SHOW TRANSACTIONS

    Transaction ID

    Transaction state

    Hive user who initiated the transaction

    Host machine where transaction was initiated
-------------------------------------------
Transaction Limitations

HDP currently has the following limitations for ACID-based transactions in Hive:

    The BEGIN, COMMIT, and ROLLBACK SQL statements are not yet supported. All operations are automatically committed as transactions.

    The user initiating the Hive session must have write permission for the destination partition or table.

    Zookeeper and in-memory locks are not compatible with transactions.

    Only ORC files are supported.

    Destination tables must be bucketed and not sorted.

    Snapshot-level isolation, similar to READ COMMITTED. A query is provided with a consistent snapshot of the data during execution.
-----------------------------------------------------
to insert into hive:
hive>
insert into table product values ("Samsung Galaxy Mini","samsung galaxy mini version no 345",phones,3)

------------------
Metastore Mysql installation in hive:
directory where mysql is installed. 
/usr/share/doc/mysql-server-5.1.73

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
-s root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'
----------------------------

Few more configurations to peek