quick start guide to test ACID functionality in hive

HiveAcidQuickTest

hive> set hive.support.concurrency;
hive.support.concurrency=true
hive> set hive.enforce.bucketing;
hive.enforce.bucketing=true
hive> set hive.exec.dynamic.partition.mode;
hive.exec.dynamic.partition.mode=nonstrict
hive> set hive.txn.manager;
hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
hive> set hive.compactor.initiator.on;
hive.compactor.initiator.on=true
hive> set hive.compactor.worker.threads;
hive.compactor.worker.threads=1


create table census(
ssn int,
name string,
city string,
email string) 
row format delimited 
fields terminated by ',';

cat > /tmp/census.csv
100,Oscar,Londerzeel,ac.tellus.Suspendisse@loremut.org
101,Jameson,Loverval,Cras.dolor@magnaUt.net
102,Damon,Morinville,Sed.neque.Sed@urna.co.uk
103,Chancellor,Blairgowrie,ante.dictum.mi@nonummyultricies.net
104,Kenyon,Shimla,posuere.at.velit@pedenec.net
105,Caldwell,Gianico,libero.Proin@semPellentesque.com
106,Levi,Troyes,augue@elit.net
107,Yasir,Langford,est.mauris.rhoncus@turpisIn.edu
108,Upton,Aartrijke,mi.tempor.lorem@nuncid.net
109,Nicholas,Placilla,cursus@primisin.edu
110,Emerson,Minervino di Lecce,molestie@acturpis.net
111,Reese,Juseret,lacinia@risusNunc.co.uk
112,Giacomo,Lampernisse,In.nec.orci@vitaesodales.edu
113,Mohammad,Sandviken,egestas.ligula.Nullam@rutrum.co.uk
114,Fulton,Lawton,tristique.neque@utaliquamiaculis.edu
115,Barry,Kitchener,Fusce@ametrisusDonec.org
116,Valentine,Nandyal,nonummy.ut.molestie@nasceturridiculusmus.ca
117,Lawrence,Hachy,eget.venenatis.a@Aliquamnisl.com
118,Micah,Wolfsberg,ultricies.ornare@ultriciesornareelit.org
119,Chandler,Lago Verde,primis.in.faucibus@seddui.org
120,Myles,Abolens,et@mollis.ca
121,Len,Souvret,vitae.posuere.at@rhoncus.com
122,Blaze,Brunn am Gebirge,pede.Cras@imperdietullamcorper.net
123,Rafael,Hofstade,eget.laoreet@tellus.ca
124,Ahmed,St. Clears,Phasellus@aceleifendvitae.co.uk
125,Deacon,Ramskapelle,Ut.tincidunt@egetipsumDonec.net
126,Steel,Newbury,amet.risus@Nullamscelerisque.ca
127,Isaac,Brixton,non.enim.Mauris@gravidaPraesenteu.ca
128,Andrew,Smithers,massa.Mauris@tortor.ca
129,Cullen,Lerum,cubilia.Curae.Donec@acfacilisis.ca
130,Lucian,Oudenburg,Ut.semper.pretium@anteiaculis.edu
131,Kevin,Flin Flon,non@elementumlorem.ca
132,Abbot,Hulshout,Nunc.lectus@Maurisnon.net
133,Hammett,Torrevecchia Teatina,Nullam@quis.edu
134,Troy,Alençon,amet.ornare.lectus@dictumplacerat.org
135,Chase,Poole,ac.sem@consequat.co.uk
136,Bruce,Forgaria nel Friuli,pulvinar.arcu.et@disparturient.com
137,Ali,Kozhikode,semper@felisadipiscing.net
138,Avram,Springdale,risus@vestibulumneceuismod.com
139,Kadeem,Kalyan,orci@acsem.org
140,Abel,Molfetta,aliquet@egestasrhoncus.net
141,Beau,Oppido Mamertina,Class.aptent.taciti@nec.net
142,Hakeem,Beerse,sit@lorem.edu
143,Berk,Tirúa,ad.litora.torquent@enim.com
144,Jakeem,Beaumaris,arcu.Sed.et@lectus.ca
145,Rudyard,Częstochowa,Aliquam.erat@dignissimmagna.edu
146,Oscar,Koekelberg,at.velit@taciti.ca
147,Clarke,Basildon,mollis@eget.co.uk
148,Linus,Gateshead,et@facilisisSuspendisse.net
149,Christian,Baracaldo,non.sollicitudin.a@semperrutrumFusce.org
150,Ferris,Birmingham,penatibus@facilisisvitaeorci.edu
151,Ray,Langley,eu.tellus.Phasellus@Donec.com
152,Jason,Naperville,Vivamus.sit@diam.net
153,Cain,Dieppe,Vivamus.molestie.dapibus@Naminterdumenim.ca
154,Vance,Iqaluit,condimentum.Donec.at@Namac.edu
155,Daniel,Sambreville,dictum.eu@Innecorci.co.uk
156,John,Vänersborg,sodales@Quisquefringillaeuismod.net
157,Colton,Rostock,quis.tristique.ac@acurna.edu
158,Kamal,Ternat,eleifend@mitemporlorem.edu
159,Kadeem,Sparwood,non.sollicitudin@dapibusid.edu
160,Keefe,Rocca Santo Stefano,risus.In.mi@eget.edu
161,Tate,Mango,arcu@vulputatelacus.org
162,Zachery,Pointe-Claire,nibh.sit@auctor.org
163,Kelly,Versailles,justo@a.net
164,Oscar,Lille,rutrum@congueelit.com
165,Castor,Cametá,et@sapiengravidanon.ca
166,Rashad,Logroño,non@Nullaaliquet.co.uk
167,Merritt,Marchihue,Cum.sociis@enimnonnisi.com
168,Isaac,Steyr,non.lobortis@a.ca
169,Matthew,Tewkesbury,Sed.eu.nibh@ut.co.uk
170,Tiger,Kawerau,tortor.dictum@ultrices.edu
171,Harrison,Oderzo,Duis@pede.org
172,Zahir,Pali,in.tempus@sociis.edu
173,Alden,Castelnovo del Friuli,facilisis.non@feugiatnon.ca
174,Chadwick,Sialkot,malesuada.id@luctus.ca
175,Kevin,Gibsons,orci.luctus.et@urnaNunc.com
176,Leo,Offida,eleifend.nunc.risus@auctor.net
177,Cameron,Oudegem,Praesent@ategestas.org
178,Carlos,São Luís,Class@iaculisodio.co.uk
179,Kieran,Manchester,luctus.lobortis@semmolestie.co.uk
180,Acton,Calgary,sit@sempertellus.com
181,Aladdin,Aylmer,lacinia.at.iaculis@anteMaecenasmi.ca
182,Barry,Tampa,mauris@semvitae.org
183,Gregory,Coaldale,dictum.eu.placerat@Phasellus.ca
184,Dean,Couthuin,porttitor@risusDonec.org
185,Castor,Pathankot,non@faucibusleoin.org
186,Yuli,Jemeppe-sur-Sambre,at.velit@velarcueu.org
187,Rooney,Cerreto di Spoleto,sollicitudin.orci.sem@pede.org
188,Uriel,Marchienne-au-Pont,Cras.eu.tellus@sociisnatoquepenatibus.net
189,Theodore,Villa Cortese,Aliquam.erat@magnaPhasellusdolor.org
190,Callum,Clearwater Municipal District,sociosqu.ad@milorem.edu
191,Gabriel,Fort Good Hope,aliquet@sit.org
192,Berk,Filacciano,dui.Fusce@velmauris.edu
193,Noah,Lebu,magna.tellus@pede.net
194,Cruz,Stirling,elit@facilisis.co.uk
195,Hakeem,Pelarco,Aenean.euismod.mauris@pedesagittis.net
196,Devin,Castlegar,Cras.eu.tellus@Curabitur.edu
197,Cedric,Montleban,Morbi.metus.Vivamus@congueturpis.co.uk
198,Adrian,Swan Hills,lacus.Cras@nibhsit.org
199,Lars,Buguma,gravida.non.sollicitudin@Maecenasmi.com

load data local inpath '/tmp/census.csv' into table census;

create table census_clus(
ssn int,
name string,
city string,
email string) 
clustered by (ssn) into 4 buckets  stored as orc TBLPROPERTIES ('transactional'='true');

insert into  table census_clus select *  from census;

[hive@rksnode tmp]$ hadoop fs -ls /apps/hive/warehouse/census_clus
Found 1 items
drwxr-xr-x   - hive hdfs          0 2016-11-30 07:26 /apps/hive/warehouse/census_clus/delta_0000001_0000001

[hive@rksnode tmp]$ hadoop fs -ls /apps/hive/warehouse/census_clus/delta_0000001_0000001
Found 8 items
-rw-r--r--   3 hive hdfs       1410 2016-11-30 07:25 /apps/hive/warehouse/census_clus/delta_0000001_0000001/bucket_00000
-rw-r--r--   3 hive hdfs       1480 2016-11-30 07:26 /apps/hive/warehouse/census_clus/delta_0000001_0000001/bucket_00001
-rw-r--r--   3 hive hdfs       1495 2016-11-30 07:25 /apps/hive/warehouse/census_clus/delta_0000001_0000001/bucket_00002
-rw-r--r--   3 hive hdfs       1483 2016-11-30 07:25 /apps/hive/warehouse/census_clus/delta_0000001_0000001/bucket_00003
-rw-r--r--   3 hive hdfs       1559 2016-11-30 07:26 /apps/hive/warehouse/census_clus/delta_0000001_0000001/bucket_00004
-rw-r--r--   3 hive hdfs       1601 2016-11-30 07:25 /apps/hive/warehouse/census_clus/delta_0000001_0000001/bucket_00005
-rw-r--r--   3 hive hdfs       1495 2016-11-30 07:25 /apps/hive/warehouse/census_clus/delta_0000001_0000001/bucket_00006
-rw-r--r--   3 hive hdfs       1509 2016-11-30 07:25 /apps/hive/warehouse/census_clus/delta_0000001_0000001/bucket_00007

insert into table census_clus values(200,'Lars1','Buguma1','gravida.non.sollicitudin1@Maecenasmi.com');

[[hive@rksnode tmp]$ hadoop fs -ls /apps/hive/warehouse/census_clus
Found 2 items
drwxr-xr-x   - hive hdfs          0 2016-11-30 07:26 /apps/hive/warehouse/census_clus/delta_0000001_0000001
drwxr-xr-x   - hive hdfs          0 2016-11-30 07:27 /apps/hive/warehouse/census_clus/delta_0000002_0000002

[hive@rksnode tmp]$ hadoop fs -ls /apps/hive/warehouse/census_clus/delta_0000002_0000002
Found 8 items
-rw-r--r--   3 hive hdfs        988 2016-11-30 07:27 /apps/hive/warehouse/census_clus/delta_0000002_0000002/bucket_00000
-rw-r--r--   3 hive hdfs        214 2016-11-30 07:27 /apps/hive/warehouse/census_clus/delta_0000002_0000002/bucket_00001
-rw-r--r--   3 hive hdfs        214 2016-11-30 07:27 /apps/hive/warehouse/census_clus/delta_0000002_0000002/bucket_00002
-rw-r--r--   3 hive hdfs        214 2016-11-30 07:27 /apps/hive/warehouse/census_clus/delta_0000002_0000002/bucket_00003
-rw-r--r--   3 hive hdfs        214 2016-11-30 07:27 /apps/hive/warehouse/census_clus/delta_0000002_0000002/bucket_00004
-rw-r--r--   3 hive hdfs        214 2016-11-30 07:27 /apps/hive/warehouse/census_clus/delta_0000002_0000002/bucket_00005
-rw-r--r--   3 hive hdfs        214 2016-11-30 07:27 /apps/hive/warehouse/census_clus/delta_0000002_0000002/bucket_00006
-rw-r--r--   3 hive hdfs        214 2016-11-30 07:27 /apps/hive/warehouse/census_clus/delta_0000002_0000002/bucket_00007


delete from census_clus where ssn=101;

hive> show compactions;
OK
Database	Table	Partition	Type	State	Worker	Start Time
Time taken: 0.336 seconds, Fetched: 1 row(s)


hive> ALTER TABLE census_clus COMPACT 'MINOR';
Compaction enqueued.
OK
Time taken: 0.243 seconds
hive> show compactions;
OK
Database	Table	Partition	Type	State	Worker	Start Time
default	census_clus	NULL	MINOR	working	rksnode.hdp242-25	1480491151000
Time taken: 0.072 seconds, Fetched: 2 row(s)

After Minor Compaction (will consolidate multiple deltas into one)
[hive@rksnode tmp]$ hadoop fs -ls /apps/hive/warehouse/census_clus
Found 2 items
drwxrwxrwx   - hive hdfs          0 2016-11-30 07:29 /apps/hive/warehouse/census_clus/.hive-staging_hive_2016-11-30_07-29-12_777_4287555820822128178-1
drwxrwxrwx   - hive hdfs          0 2016-11-30 07:35 /apps/hive/warehouse/census_clus/delta_0000001_0000003

hive> show compactions;
OK
Database	Table	Partition	Type	State	Worker	Start Time
default	census_clus	NULL	MINOR	succeeded	NULL	1480491151000
default	census_clus	NULL	MAJOR	succeeded	NULL	1480491336000
Time taken: 0.032 seconds, Fetched: 3 row(s)

After Major Compaction (will merge delta files into the base file)
[hive@rksnode tmp]$ hadoop fs -ls /apps/hive/warehouse/census_clus
Found 2 items
drwxrwxrwx   - hive hdfs          0 2016-11-30 07:29 /apps/hive/warehouse/census_clus/.hive-staging_hive_2016-11-30_07-29-12_777_4287555820822128178-1
drwxrwxrwx   - hive hdfs          0 2016-11-30 07:36 /apps/hive/warehouse/census_clus/base_0000003