Return-Path: X-Original-To: apmail-hive-user-archive@www.apache.org Delivered-To: apmail-hive-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 823E217FB5 for ; Wed, 8 Apr 2015 21:47:01 +0000 (UTC) Received: (qmail 43641 invoked by uid 500); 8 Apr 2015 21:46:59 -0000 Delivered-To: apmail-hive-user-archive@hive.apache.org Received: (qmail 43576 invoked by uid 500); 8 Apr 2015 21:46:59 -0000 Mailing-List: contact user-help@hive.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@hive.apache.org Delivered-To: mailing list user@hive.apache.org Received: (qmail 43566 invoked by uid 99); 8 Apr 2015 21:46:59 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 08 Apr 2015 21:46:59 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [83.138.144.103] (HELO sulu.netzoomi.net) (83.138.144.103) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 08 Apr 2015 21:46:31 +0000 Received: from vulcan.netzoomi.net (unknown [212.100.249.54]) by sulu.netzoomi.net (Postfix) with ESMTP id F0BFE6A4DB5 for ; Wed, 8 Apr 2015 22:46:01 +0100 (BST) X-Envelope-From: Received: from vista (cpc7-seve18-2-0-cust228.13-3.cable.virginm.net [86.19.240.229]) by vulcan.netzoomi.net (Postfix) with ESMTPA id BADBA1248660 for ; Wed, 8 Apr 2015 22:46:01 +0100 (BST) From: "Mich Talebzadeh" To: Subject: Hive support for concurrency with Oracle Metastore Date: Wed, 8 Apr 2015 22:45:54 +0100 Message-ID: <01bd01d07245$642e9a30$2c8bce90$@co.uk> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01BE_01D0724D.C5F30230" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AdByRWPK6Vf5vccsQQWePrh8CxmMsQ== Content-Language: en-gb X-domainnameshop-MailScanner-Information: Please contact the ISP for more information X-domainnameshop-MailScanner-ID: F0BFE6A4DB5.A2DC9 X-domainnameshop-MailScanner: Found to be clean X-domainnameshop-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=1.352, required 5, autolearn=disabled, HTML_MESSAGE 0.00, RDNS_NONE 1.27, TW_TX 0.08) X-domainnameshop-MailScanner-SpamScore: 1 X-domainnameshop-MailScanner-From: mich@peridale.co.uk X-domainnameshop-MailScanner-Watermark: 1429134363.47894@prsTPyHt5S5xAJ+o6N6YXw X-Virus-Checked: Checked by ClamAV on apache.org X-Old-Spam-Status: No This is a multi-part message in MIME format. ------=_NextPart_000_01BE_01D0724D.C5F30230 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi, Thanks for all the useful info. I managed to set up hive concurrency using Oracle Metastore for Hive. Had to modify hive-txn-schema-0.14.0.oracle.sql script in order to drop the existing tables as I had created the metastore with hive-schema-0.14.0.oracle.sql initially. All the transactions seem to behave as expected (meaning conforming to behaviour expected from an ACID compliant RDBMS). Except a question that comes to mind with locking when deleting all rows from the table (as opposed to truncating the table) I created a test table create table txtest (col1 int, col2 varchar(30)) clustered by (col1) into 10 buckets STORED AS orc TBLPROPERTIES('transactional'='true'); Inserted 10 rows and updatedstats insert into table txtest values (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'f'),(8,'g'),(9,'h'),(10, 'i'); analyse table txtest compute statistics; Updated three columns followed by a single column update txtest set col2 = col2 where col1 in (1,3,5); update txtest set col2 = 'row1' where col1 = 1; >From another session looked at locks show locks; +----------+---------------+---------+------------+-------------+----------- ----+-----------------+-----------------+----------------+---------+-------- ---+--+ | lockid | database | table | partition | lock_state | lock_type | transaction_id | last_heartbeat | acquired_at | user | hostname | +----------+---------------+---------+------------+-------------+----------- ----+-----------------+-----------------+----------------+---------+-------- ---+--+ | Lock ID | Database | Table | Partition | State | Type | Transaction ID | Last Hearbeat | Acquired At | User | Hostname | | 21 | oraclehadoop | txtest | NULL | ACQUIRED | SHARED_WRITE | 6 | 1428525777008 | 1428525774206 | hduser | rhes564 | +----------+---------------+---------+------------+-------------+----------- ----+-----------------+-----------------+----------------+---------+-------- ---+--+ Now see what happens if we try to delete two different rows from two different sessions concurrently (well almost) Session 1 hive> delete from txtest where col1 = 10; Session 2 delete from txtest where col1 = 4; The locks are shown below show locks; +----------+---------------+---------+------------+-------------+----------- ----+-----------------+-----------------+----------------+---------+-------- ---+--+ | lockid | database | table | partition | lock_state | lock_type | transaction_id | last_heartbeat | acquired_at | user | hostname | +----------+---------------+---------+------------+-------------+----------- ----+-----------------+-----------------+----------------+---------+-------- ---+--+ | Lock ID | Database | Table | Partition | State | Type | Transaction ID | Last Hearbeat | Acquired At | User | Hostname | | 23 | oraclehadoop | txtest | NULL | ACQUIRED | SHARED_WRITE | 7 | 1428526836905 | 1428526833132 | hduser | rhes564 | | 24 | oraclehadoop | txtest | NULL | WAITING | SHARED_WRITE | 8 | 1428526865246 | NULL | hduser | rhes564 | +----------+---------------+---------+------------+-------------+----------- ----+-----------------+-----------------+----------------+---------+-------- ---+--+ Now delete all rows from the table hive> delete from txtest; 0: jdbc:hive2://rhes564:10010/default> show locks; +----------+---------------+---------+------------+-------------+----------- ----+-----------------+-----------------+----------------+---------+-------- ---+--+ | lockid | database | table | partition | lock_state | lock_type | transaction_id | last_heartbeat | acquired_at | user | hostname | +----------+---------------+---------+------------+-------------+----------- ----+-----------------+-----------------+----------------+---------+-------- ---+--+ | Lock ID | Database | Table | Partition | State | Type | Transaction ID | Last Hearbeat | Acquired At | User | Hostname | | 25 | oraclehadoop | txtest | NULL | ACQUIRED | SHARED_WRITE | 9 | 1428527740870 | 1428527737658 | hduser | rhes564 | +----------+---------------+---------+------------+-------------+----------- ----+-----------------+-----------------+----------------+---------+-------- ---+--+ The question I have is if we delete from the whole table it seems that "only" one lock is applied to whole table. Does that mean a full table lock rather that locks for every row deleted? Thanks, Mich Talebzadeh http://talebzadehmich.wordpress.com Author of the books "A Practitioner's Guide to Upgrading to Sybase ASE 15", ISBN 978-0-9563693-0-7. co-author "Sybase Transact SQL Guidelines Best Practices", ISBN 978-0-9759693-0-4 Publications due shortly: Creating in-memory Data Grid for Trading Systems with Oracle TimesTen and Coherence Cache Oracle and Sybase, Concepts and Contrasts, ISBN: 978-0-9563693-1-4, volume one out shortly NOTE: The information in this email is proprietary and confidential. This message is for the designated recipient only, if you are not the intended recipient, you should destroy it immediately. Any information in this message shall not be understood as given or endorsed by Peridale Ltd, its subsidiaries or their employees, unless expressly so stated. It is the responsibility of the recipient to ensure that this email is virus free, therefore neither Peridale Ltd, its subsidiaries nor their employees accept any responsibility. ------=_NextPart_000_01BE_01D0724D.C5F30230 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

 

Thanks for = all the useful info.  I managed to set up hive concurrency using = Oracle Metastore for Hive. Had to modify = hive-txn-schema-0.14.0.oracle.sql script in order to drop the = existing tables as I had created the metastore with = hive-schema-0.14.0.oracle.sql initially.

 

All the = transactions seem to behave as expected (meaning conforming to behaviour = expected from an ACID compliant RDBMS). Except a question that comes to = mind with locking when deleting all rows from the table (as opposed to = truncating the table)

 

I created a = test table

 

create table txtest (col1 int, col2 varchar(30)) = clustered by (col1) into 10 buckets  STORED AS orc = TBLPROPERTIES('transactional'=3D'true');

 

Inserted 10 = rows and updatedstats

 

insert = into table txtest values = (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'f'),(8,'g'),(9,'h'),(= 10,'i');

analyse = table txtest compute statistics;

 

U= pdated three columns followed by a single column

 

update = txtest set col2 =3D col2 where col1 in (1,3,5);

 

update = txtest set col2 =3D 'row1' where col1 =3D 1;

 

F= rom another session looked at locks

 

show = locks;

+----------+---------------+---------+------------+-----= --------+---------------+-----------------+-----------------+------------= ----+---------+-----------+--+

|  lockid  |   = database    |  table  | partition  | = lock_state  |   lock_type   | = transaction_id  | last_heartbeat  |  = acquired_at   |  user   | hostname  = |

+----------+---------------+---------+------------+-----= --------+---------------+-----------------+-----------------+------------= ----+---------+-----------+--+

| Lock ID  | = Database      | Table   | = Partition  | State       | = Type          | Transaction = ID  | Last Hearbeat   | Acquired At    | = User    | Hostname  |

| 21       | = oraclehadoop  | txtest  | = NULL       | ACQUIRED    | = SHARED_WRITE  | = 6            =    | 1428525777008   | 1428525774206  | = hduser  | rhes564   |

+----------+---------------+---------+------------+-----= --------+---------------+-----------------+-----------------+------------= ----+---------+-----------+--+

 

N= ow see what happens if we try to delete two different rows from two = different  sessions concurrently (well = almost)

 <= /span>

Session = 1

hive> = delete from txtest where col1 =3D 10;

 

S= ession 2

delete = from txtest where col1 =3D 4;

<= o:p> 

T= he locks are shown below

<= o:p> 

show = locks;

+----------+---------------+---------+------------+-----= --------+---------------+-----------------+-----------------+------------= ----+---------+-----------+--+

|  lockid  |   = database    |  table  | partition  | = lock_state  |   lock_type   | = transaction_id  | last_heartbeat  |  = acquired_at   |  user   | hostname  = |

+----------+---------------+---------+------------+-----= --------+---------------+-----------------+-----------------+------------= ----+---------+-----------+--+

| Lock ID  | = Database      | Table   | = Partition  | State       | = Type          | Transaction = ID  | Last Hearbeat   | Acquired At    | = User    | Hostname  |

| 23       | = oraclehadoop  | txtest  | = NULL       | ACQUIRED    | = SHARED_WRITE  | = 7            =    | 1428526836905   | 1428526833132  | = hduser  | rhes564   |

| 24       | = oraclehadoop  | txtest  | = NULL       | = WAITING     | SHARED_WRITE  | = 8            =    | 1428526865246   | = NULL           | = hduser  | rhes564   |

+----------+---------------+---------+------------+-----= --------+---------------+-----------------+-----------------+------------= ----+---------+-----------+--+

<= o:p> 

N= ow delete all rows from the table

<= o:p> 

hive> = delete from txtest;

<= o:p> 

0: = jdbc:hive2://rhes564:10010/default> show = locks;

+----------+---------------+---------+------------+-----= --------+---------------+-----------------+-----------------+------------= ----+---------+-----------+--+

|  lockid  |   = database    |  table  | partition  | = lock_state  |   lock_type   | = transaction_id  | last_heartbeat  |  = acquired_at   |  user   | hostname  = |

+----------+---------------+---------+------------+-----= --------+---------------+-----------------+-----------------+------------= ----+---------+-----------+--+

| Lock ID  | = Database      | Table   | = Partition  | State       | = Type          | Transaction = ID  | Last Hearbeat   | Acquired At    | = User    | Hostname  |

| 25       | = oraclehadoop  | txtest  | = NULL       | ACQUIRED    | = SHARED_WRITE  | = 9            =    | 1428527740870   | 1428527737658  | = hduser  | rhes564   |

+----------+---------------+---------+------------+-----= --------+---------------+-----------------+-----------------+------------= ----+---------+-----------+--+

<= o:p> 

T= he question I have is if we delete from the whole table it seems that =  “only” one lock is applied to whole table. Does that = mean a full table lock rather that locks for every row = deleted?

<= o:p> 

<= o:p> 

T= hanks,

<= o:p> 

Mich = Talebzadeh

 <= /span>

h= ttp://talebzadehmich.wordpress.com

 

Author of the = books "A Practitioner’s Guide to Upgrading to = Sybase ASE = 15", ISBN = 978-0-9563693-0-7. =

co-author = "Sybase Transact SQL Guidelines Best Practices", ISBN = 978-0-9759693-0-4

Publications due shortly:

Cr= eating in-memory Data Grid for Trading Systems with Oracle TimesTen and = Coherence Cache

Oracle and = Sybase, Concepts and Contrasts, = ISBN: = 978-0-9563693-1-4, volume one out = shortly

<= o:p> 

NO= TE: The information in this email is proprietary and confidential. This = message is for the designated recipient only, if you are not the = intended recipient, you should destroy it immediately. Any information = in this message shall not be understood as given or endorsed by Peridale = Ltd, its subsidiaries or their employees, unless expressly so stated. It = is the responsibility of the recipient to ensure that this email is = virus free, therefore neither Peridale Ltd, its subsidiaries nor their = employees accept any responsibility.

 

------=_NextPart_000_01BE_01D0724D.C5F30230--