How to Ingest the Updates/New Inserts to ElasticSearch Index

Following my earlier post on “How to handle ES data ingestion through Hive”. This is the details I have given on how to handle Updates/New Inserts into ElasticSearch Index.

Incremental Load:

driverdata_delta.txt
1000-55-7908032332029JT3HN87R6X90246441|1000-55-7908|032332029|JT3HN87R6X9024644|1|RAMA GR|COLL|NEW RECORD ST,AZ
1000-54-7908032332029JT3HN87R6X90246441|1000-54-7908|032332029|JT3HN87R6X9024644|1|GIRI V|COLL|1 ST,AZ
1000-54-7907032332028JT3HN87R6X90246431|1000-54-7907|032332028|JT3HN87R6X9024643|1|WESLEY D|COMP|2 ST,AZ
1000-54-7906032332027JT3HN87R6X90246421|1000-54-7906|032332027|JT3HN87R6X9024642|1|JASWIND S|COLL|3 ST,AZ
1000-54-7905032332026JT3HN87R6X90246411|1000-54-7905|032332026|JT3HN87R6X9024641|1|KAMAL S|COMP|4 ST,AZ
1000-54-7904032332025JT3HN87R6X90246401|1000-54-7904|032332025|JT3HN87R6X9024640|1|ARUN N|COLL|5 ST,AZ
1000-54-7902032332024JT3HN87R6X90246451|1000-54-7902|032332024|JT3HN87R6X9024645|1|MATHAN R|COMP|6 ST,AZ
1000-54-7908032332029JT3HN87R6X90246442|1000-54-7908|032332029|JT3HN87R6X9024644|2|GIRI V|BI|1 ST,AZ
1000-54-7907032332028JT3HN87R6X90246432|1000-54-7907|032332028|JT3HN87R6X9024643|2|WESLEY D|BI|2 ST,AZ
1000-54-7906032332027JT3HN87R6X90246422|1000-54-7906|032332027|JT3HN87R6X9024642|2|JASWIND S|BI|3 ST,AZ
1000-54-7905032332026JT3HN87R6X90246412|1000-54-7905|032332026|JT3HN87R6X9024641|2|ARUN N|BI|4 ST,AZ
1000-54-7904032332025JT3HN87R6X90246402|1000-54-7904|032332025|JT3HN87R6X9024640|2|KAMAL S|BI|5 ST,AZ
1000-54-7902032332024JT3HN87R6X90246452|1000-54-7902|032332024|JT3HN87R6X9024645|2|MATHAN R|BI|6 ST,AZ

CREATE TABLE HIVETEST.DRIVERDATA_TMP_DELTA
(
KEY STRING,
CLAIMNUMBER STRING,
POL_NBR STRING,
VEHICLE_NBR STRING,
EXP_NBR STRING,
NAME STRING,
COVERAGE STRING,
ADDRESS STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘|’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH ‘/opt/md/WIP/giri/driverdata_delta.txt’ OVERWRITE INTO TABLE HIVETEST.DRIVERDATA_TMP_DELTA;

set es.write.operation=upsert;

–Updating/Inserting into the Same Hive ES table and ES index driverdata

INSERT INTO TABLE HIVETEST.DRIVERDATA_ES_JAN2
SELECT
KEY,
CLAIMNUMBER,
POL_NBR,
VEHICLE_NBR,
EXP_NBR,
NAME,
COVERAGE,
ADDRESS
FROM
HIVETEST.DRIVERDATA_TMP_DELTA;

Output in ES:

Tested in QA Hive/QA ES Servers:

1000-54-7908032332029JT3HN87R6X90246441 1000-54-7908 032332029 JT3HN87R6X9024644 1 GIRI V COLL 1 ST,AZ
1000-54-7907032332028JT3HN87R6X90246431 1000-54-7907 032332028 JT3HN87R6X9024643 1 WESLEY D COMP 2 ST,AZ
1000-54-7902032332024JT3HN87R6X90246452 1000-54-7902 032332024 JT3HN87R6X9024645 2 MATHAN R BI 6 ST,AZ
1000-55-7908032332029JT3HN87R6X90246441 1000-55-7908 032332029 JT3HN87R6X9024644 1 RAMA GR COLL NEW RECORD ST,AZ — New Record Added
1000-54-7906032332027JT3HN87R6X90246421 1000-54-7906 032332027 JT3HN87R6X9024642 1 JASWIND S COLL 3 ST,AZ
1000-54-7905032332026JT3HN87R6X90246411 1000-54-7905 032332026 JT3HN87R6X9024641 1 KAMAL S COMP 4 ST,AZ
1000-54-7904032332025JT3HN87R6X90246401 1000-54-7904 032332025 JT3HN87R6X9024640 1 ARUN N COLL 5 ST,AZ
1000-54-7902032332024JT3HN87R6X90246451 1000-54-7902 032332024 JT3HN87R6X9024645 1 MATHAN R COMP 6 ST,AZ
1000-54-7908032332029JT3HN87R6X90246442 1000-54-7908 032332029 JT3HN87R6X9024644 2 GIRI V BI 1 ST,AZ
1000-54-7907032332028JT3HN87R6X90246432 1000-54-7907 032332028 JT3HN87R6X9024643 2 WESLEY D BI 2 ST,AZ
1000-54-7906032332027JT3HN87R6X90246422 1000-54-7906 032332027 JT3HN87R6X9024642 2 JASWIND S BI 3 ST,AZ
1000-54-7905032332026JT3HN87R6X90246412 1000-54-7905 032332026 JT3HN87R6X9024641 2 ARUN N BI 4 ST,AZ
1000-54-7904032332025JT3HN87R6X90246402 1000-54-7904 032332025 JT3HN87R6X9024640 2 KAMAL S BI 5 ST,AZ

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s