CODEWITHSUNDEEP
hadoophive
Ashok Kumar
Ashok Kumar

Reputation: 3

How to append the data to existing hive table without partition

I have created hive table which contains historical stock data of past 10 years. From now i have to append the data on daily bases.

I thought of creating the partition based on date but it leads many partitions approximately 3000 plus a new partition for every new date, i think this is not feasible.

Can any one suggest a best approach to store all the historical data in the table and append the new data as it comes.

Upvotes: 0

Views: 4390

Answers (2)

Roberto Congiu
Roberto Congiu

Reputation: 5223

As for every partitioned table, the decision on how to partition your table depends primarily on how you are going to query the table. Another consideration is how much data you're going to have per partition, as partitions should not bee too small. Each one should be at least at as an absolute minimum as big as one HDFS block since it would otherwise take too many directories.

This said, I don't think 3000 partitions would be a problem. At a previous job we had a huge table with one partition per hour, each hour was about 20Gbytes, and we had 6 months of data, so about 4000 partitions, and it worked just fine.

In our case, most people care the most about the last week and the last day. I suggest as first thing you research how the table is going to be used, that is, are all the 10 years be used, or just mostly the most recent data ?

As second thing, study how big is the data, consider if it may grow in size with the new loads, and see how big each partition is going to be.

Once you've determined these 2 points, you can make a decision, you could just use daily partitions (which could be fine, 3000 partitions is not bad), or you could do weekly, or monthly.

Upvotes: 2

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3966

You can use this command

LOAD DATA LOCAL INPATH '<FILE_PATH>' INTO TABLE <TABLE_NAME>;

It will create new files under HDFS directory mapped to table name. Even though there are not too many partitions with it, you will still run into too many files issue.

Periodically, you need to do this:

  1. Create stage table
  2. Move data by running LOAD command from target table to stage table
  3. You can run insert command into target table selecting from stage table
  4. Now it will load data with number of files equal to number of reducers.
  5. You can delete stage table

You can run this process at regular intervals (probably once in a month).

Upvotes: 0

Related Questions