CODEWITHSUNDEEP
hadoophive
stackoverflowuser2010
stackoverflowuser2010

Reputation: 40869

Hive: Creating smaller table from big table

I currently have a Hive table that has 1.5 billion rows. I would like to create a smaller table (using the same table schema) with about 1 million rows from the original table. Ideally, the new rows would be randomly sampled from the original table, but getting the top 1M or bottom 1M of the original table would be ok, too. How would I do this?

Upvotes: 4

Views: 6352

Answers (3)

Aman Mundra
Aman Mundra

Reputation: 864

This query will pull out top 1M rows and overwrite them in a new table.

CREATE TABLE new_table_name AS 
  SELECT col1, col2, col3, ....
  FROM original_table 
  WHERE (if you want to put any condition) limit 100000;

Upvotes: 1

Lukas Vermeer
Lukas Vermeer

Reputation: 5940

As climbage suggested earlier, you could probably best use Hive's built-in sampling methods.

INSERT OVERWRITE TABLE my_table_sample 
SELECT * FROM my_table 
TABLESAMPLE (1m ROWS) t;

This syntax was introduced in Hive 0.11. If you are running an older version of Hive, you'll be confined to using the PERCENT syntax like so.

INSERT OVERWRITE TABLE my_table_sample 
SELECT * FROM my_table 
TABLESAMPLE (1 PERCENT) t;

You can change the percentage to match you specific sample size requirements.

Upvotes: 7

seedhead
seedhead

Reputation: 3805

You can define a new table with the same schema as your original table.

Then use INSERT OVERWRITE TABLE <tablename> <select statement>

The SELECT statement will need to query your original table, use LIMIT to only get 1M results.

Upvotes: 1

Related Questions