In Hive How to Insert Data into a Single File

I don't think you can tell hive to write to a specific file like wasb:///hiveblob/foo.csv directly.

What you can do is:

• Tell hive to merge the output files into one before you run the query. This way you can have as many reducers as you want and still have single output file.

• Run your query, e.g. INSERT OVERWRITE DIRECTORY ...

• Then use dfs -mv within hive to rename the file to whatever.

This is probably less painful than using separate hadoop fs -getmerger /your/src/folder /your/dest/folder/yourFileName as suggested by Ramzy.

The way to instruct to merge the files may be different depending on the runtime engine you are using.

For example, if you use tez as the runtime engine in your hive queries, you can do this:

-- Set the tez execution engine
-- And instruct to merge the results
set hive.execution.engine=tez;
set hive.merge.tezfiles=true;

-- Your query goes here.
-- The results should end up in wasb:///hiveblob/000000_0 file.
INSERT OVERWRITE DIRECTORY 'wasb:///hiveblob/' SELECT * from table1;


-- Rename the output file into whatever you want
dfs -mv 'wasb:///hiveblob/000000_0' 'wasb:///hiveblob/foo.csv'

(The above worked for me with these versions: HDP 2.2, Tez 0.5.2, and Hive 0.14.0)

For MapReduce engine (which is the default), you can try these, although I haven't tried them myself:

-- Try this if you use MapReduce engine.
set hive.execution.engine=mr;
set hive.merge.mapredfiles=true;

You can coerce hive to build to build one file by forcing reducers to one. This will copy any fragmented files in one table and combine them in another location in HDFS. Of course forcing one reducer breaks the benefit of parallelism. If you plan on doing any transformation of data I recommend doing that first then doing this in a last and separate phase.

To produce a single file using hive you can try:

set hive.exec.dynamic.partition.mode=nostrict;
set hive.exec.compress.intermediate=false;
set hive.exec.compress.output=false;
set hive.exec.reducers.max=1;

create table if not exists db.table
stored as textfiel as
select * from db.othertable;

db.othertable is the table that has multiple fragmented files. db.table will have a single text file containing the combined data.

You will be having multiple output files by default, equal to the number of reducers. That is decided by Hive. However you can configure the reducers. Look here. However, the performance can be a hit, if we reduce the reducers and will run into more execution time. Alternatively, once the files are present, you can use get merge, and combine all the files into one file.

hadoop fs -getmerger /your/src/folder /your/dest/folder/yourFileName

. The src folder contains all the files to be merged.