Exporting Hive Table to a S3 bucket

Question

I've created a Hive Table through an Elastic MapReduce interactive session and populated it from a CSV file like this:

CREATE TABLE csvimport(id BIGINT, time STRING, log STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

LOAD DATA LOCAL INPATH '/home/hadoop/file.csv' OVERWRITE INTO TABLE csvimport;

I now want to store the Hive table in a S3 bucket so the table is preserved once I terminate the MapReduce instance.

Does anyone know how to do this?

Tremulant

Answer 1

Yes you have to export and import your data at the start and end of your hive session

To do this you need to create a table that is mapped onto S3 bucket and directory

CREATE TABLE csvexport (
  id BIGINT, time STRING, log STRING
  ) 
 row format delimited fields terminated by ',' 
 lines terminated by '\n' 
 STORED AS TEXTFILE
 LOCATION 's3n://bucket/directory/';

Insert data into s3 table and when the insert is complete the directory will have a csv file

 INSERT OVERWRITE TABLE csvexport 
 select id, time, log
 from csvimport;

Your table is now preserved and when you create a new hive instance you can reimport your data

Your table can be stored in a few different formats depending on where you want to use it.

Quittor

the file in S3 is created without headers and with a random file name. is there a way to solve this problem? checked some of the posts on SO, couldn't find any relevant answers. – Taciturnity

Same comment as above + how to fill the AWS credentials in there? I mean the ID and Secret keys. Thanks. – Orgeat

Answer 2

Above Query needs to use EXTERNAL keyword, i.e:

CREATE EXTERNAL TABLE csvexport ( id BIGINT, time STRING, log STRING ) 
row format delimited fields terminated by ',' lines terminated by '\n' 
STORED AS TEXTFILE LOCATION 's3n://bucket/directory/';
INSERT OVERWRITE TABLE csvexport select id, time, log from csvimport;

An another alternative is to use the query

INSERT OVERWRITE DIRECTORY 's3n://bucket/directory/'  select id, time, log from csvimport;

the table is stored in the S3 directory with HIVE default delimiters.

Laius

This doesn't copy the header. is there a way to copy header also? – Affront

@MohammadAdnan did you find a way to copy header? – Membership

@MohammadAdnan did u find a way to copy the headers? – Diatomite

Answer 3

If you could access aws console and have the "Access Key Id" and "Secret Access Key" for your account

You can try this too..

CREATE TABLE csvexport(id BIGINT, time STRING, log STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION 's3n://"access id":"secret key"@bucket/folder/path';

Now insert the data as other stated above..

INSERT OVERWRITE TABLE csvexport select id, time, log from csvimport;

Bielefeld