2

I'm new to this technology. We receive the file which may contain backdated records. From which we have to load the data into the hive table which will be insert only(partitioned on trans_Date). I need to know what should be the mechanism to insert the records in the table, where trans_date is backdated. Trans_date(column) is the transaction date and Record_date(column) is the date on which the record is inserted in the table.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Prathamesh H
  • 152
  • 5
  • 23
  • Is trans_date a partition key? what is a problem. How record_date related to trans_date? – leftjoin Jun 08 '18 at 10:27
  • Hello, I've edited the question.Also, I'm new to this technology. We receive the file which may contain backdated records. From which we have to load the data into the table(partitioned on trans_Date). I need to know what should be the mechanism to insert the records in the table, where trans_date is backdated. Trans_date is the transaction date and Record_date is the date on which the record is inserted in the table. – Prathamesh H Jun 08 '18 at 10:50
  • Have you tried it? What happens? And how are you loading the file? (The fact that you're new to the technology isn't important... I'm just not sure that it's clear what you're asking.) – Stephen Darlington Jun 08 '18 at 11:02

1 Answers1

1

You can do it in a number of ways and using different tools actually.

Create increment table on top of new files directory, or use LOAD command to put files into increment table, or use hadoop fs -cp command for the same.

Well, you have a table with incremental data now.

Next step is to load into main table into proper partitions if you do insert only, no updates, use

INSERT INTO TABLE PARTITION(trans_date)
select col1, col2, trans_date from incr_table; --filter if necessary

Drop incr_table or remove only data in table increment location and re-use the table. Or partition incr_table by record_date (or file_date) if applicable and never drop, load and select new partition.

If you need to update old records with incremental data, see this answer: https://stackoverflow.com/a/37744071/2700344

leftjoin
  • 36,950
  • 8
  • 57
  • 116