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,[email protected]
101,Jameson,Loverval,[email protected]
102,Damon,Morinville,[email protected]
103,Chancellor,Blairgowrie,[email protected]
104,Kenyon,Shimla,[email protected]
105,Caldwell,Gianico,[email protected]
106,Levi,Troyes,[email protected]
107,Yasir,Langford,[email protected]
108,Upton,Aartrijke,[email protected]
109,Nicholas,Placilla,[email protected]
110,Emerson,Minervino di Lecce,[email protected]
111,Reese,Juseret,[email protected]
112,Giacomo,Lampernisse,[email protected]
113,Mohammad,Sandviken,[email protected]
114,Fulton,Lawton,[email protected]
115,Barry,Kitchener,[email protected]
116,Valentine,Nandyal,[email protected]
117,Lawrence,Hachy,[email protected]
118,Micah,Wolfsberg,[email protected]
119,Chandler,Lago Verde,[email protected]
120,Myles,Abolens,[email protected]
121,Len,Souvret,[email protected]
122,Blaze,Brunn am Gebirge,[email protected]
123,Rafael,Hofstade,[email protected]
124,Ahmed,St. Clears,[email protected]
125,Deacon,Ramskapelle,[email protected]
126,Steel,Newbury,[email protected]
127,Isaac,Brixton,[email protected]
128,Andrew,Smithers,[email protected]
129,Cullen,Lerum,[email protected]
130,Lucian,Oudenburg,[email protected]
131,Kevin,Flin Flon,[email protected]
132,Abbot,Hulshout,[email protected]
133,Hammett,Torrevecchia Teatina,[email protected]
134,Troy,Alençon,[email protected]
135,Chase,Poole,[email protected]
136,Bruce,Forgaria nel Friuli,[email protected]
137,Ali,Kozhikode,[email protected]
138,Avram,Springdale,[email protected]
139,Kadeem,Kalyan,[email protected]
140,Abel,Molfetta,[email protected]
141,Beau,Oppido Mamertina,[email protected]
142,Hakeem,Beerse,[email protected]
143,Berk,Tirúa,[email protected]
144,Jakeem,Beaumaris,[email protected]
145,Rudyard,Częstochowa,[email protected]
146,Oscar,Koekelberg,[email protected]
147,Clarke,Basildon,[email protected]
148,Linus,Gateshead,[email protected]
149,Christian,Baracaldo,[email protected]
150,Ferris,Birmingham,[email protected]
151,Ray,Langley,[email protected]
152,Jason,Naperville,[email protected]
153,Cain,Dieppe,[email protected]
154,Vance,Iqaluit,[email protected]
155,Daniel,Sambreville,[email protected]
156,John,Vänersborg,[email protected]
157,Colton,Rostock,[email protected]
158,Kamal,Ternat,[email protected]
159,Kadeem,Sparwood,[email protected]
160,Keefe,Rocca Santo Stefano,[email protected]
161,Tate,Mango,[email protected]
162,Zachery,Pointe-Claire,[email protected]
163,Kelly,Versailles,[email protected]
164,Oscar,Lille,[email protected]
165,Castor,Cametá,[email protected]
166,Rashad,Logroño,[email protected]
167,Merritt,Marchihue,[email protected]
168,Isaac,Steyr,[email protected]
169,Matthew,Tewkesbury,[email protected]
170,Tiger,Kawerau,[email protected]
171,Harrison,Oderzo,[email protected]
172,Zahir,Pali,[email protected]
173,Alden,Castelnovo del Friuli,[email protected]
174,Chadwick,Sialkot,[email protected]
175,Kevin,Gibsons,[email protected]
176,Leo,Offida,[email protected]
177,Cameron,Oudegem,[email protected]
178,Carlos,São Luís,[email protected]
179,Kieran,Manchester,[email protected]
180,Acton,Calgary,[email protected]
181,Aladdin,Aylmer,[email protected]
182,Barry,Tampa,[email protected]
183,Gregory,Coaldale,[email protected]
184,Dean,Couthuin,[email protected]
185,Castor,Pathankot,[email protected]
186,Yuli,Jemeppe-sur-Sambre,[email protected]
187,Rooney,Cerreto di Spoleto,[email protected]
188,Uriel,Marchienne-au-Pont,[email protected]
189,Theodore,Villa Cortese,[email protected]
190,Callum,Clearwater Municipal District,[email protected]
191,Gabriel,Fort Good Hope,[email protected]
192,Berk,Filacciano,[email protected]
193,Noah,Lebu,[email protected]
194,Cruz,Stirling,[email protected]
195,Hakeem,Pelarco,[email protected]
196,Devin,Castlegar,[email protected]
197,Cedric,Montleban,[email protected]
198,Adrian,Swan Hills,[email protected]
199,Lars,Buguma,[email protected]

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','[email protected]');

[[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