question Re: Can we export hive table to s3 or nfs share in Support Questions

Can we export hive table to s3 or nfs share

jayes — Mon, 14 Oct 2024 09:37:24 GMT

Hello Team,

can we import/export hive table to s3 location or nfs share ? as per what i searched it is only supported for hdfs location. can you please help me to know if we can do it and if yes how can we achieve it ?

Re: Can we export hive table to s3 or nfs share

MGreen — Wed, 16 Oct 2024 20:37:42 GMT

@jayes

Unfortunately the Hive Import/Export is only supported for HDFS. The only method I know of to get the table and data into S3 is as follows, see example below.

You need to create a table that is mapped onto S3 bucket and directory

CREATE TABLE tests3 (
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 tests3
select id, time, log
from testcsvimport;

Re: Can we export hive table to s3 or nfs share

jayes — Fri, 18 Oct 2024 06:20:40 GMT

Thanks for the help.

Also have a doubt can we export the data to hdfs in compressed format i.e., into zip file or something else

Re: Can we export hive table to s3 or nfs share

MGreen — Fri, 18 Oct 2024 12:47:33 GMT

Please see the accepted compression formats supported below.

https://docs.cloudera.com/cdp-private-cloud-base/7.1.9/managing-clusters/topics/cm-choosing-configuring-data-compression.html

When exporting in hive it will compress the data.

Re: Can we export hive table to s3 or nfs share

jayes — Mon, 21 Oct 2024 06:35:49 GMT

Hi @MGreen ,

i am unable to find how to pass which type of format we want the export to get dumped.

is it using beeline if yes can you please help me with the command or the argument to pass to export in compress format.

since today when i run export command the data size of table and data size of exported data approximately same.

Re: Can we export hive table to s3 or nfs share

MGreen — Tue, 22 Oct 2024 11:07:39 GMT

@jayes

Unfortunately there is no compression setting for the Hive Export. This feature was introduced when Hive CLI was used in the HDP days of Hortonworks.

You will need to create your tables with compression enabled, and in your case you will need to either do one of the following.

Alter the table and add compression to the table properties, and then do an insert overwrite to the table to compress it.

Or create a new table with compression added to the table properties, and then insert the data from the old table into the new one.

I would recommend using the Snappy Compression.

Re: Can we export hive table to s3 or nfs share

jayes — Tue, 22 Oct 2024 11:10:13 GMT

Thanks @MGreen for the help