Hive INSERT OVERWRITE to Google Storage as LOCAL DIRECTORY not working

Question

I use the following Hive Query:

hive> INSERT OVERWRITE LOCAL DIRECTORY "gs:// Google/Storage/Directory/Path/Name" row format delimited fields terminated by ','
select * from <HiveDatabaseName>.<HiveTableName>;

I am getting the following error:

"Error: Failed with exception Wrong FS:"gs:// Google/Storage/Directory/PathName", expected: file:///

What could I be doing wrong?

Answer 1

There's a bug in Hive, including IIRC Hive 1.2.1, where it uses the configured fs.default.name or fs.defaultFS for its scratchdir even if the table path is in a different filesystem. In your case, it appears you have the out-of-the-box defaults setting fs.defaultFS to file:///, which is why it says "expected: file:///". On a distributed Hadoop cluster, you might see it say "expected: hdfs://..." instead.

You can fix it within the single hive prompt by overriding fs.default.name and fs.defaultFS:

> set fs.default.name=gs://your-bucket/
> set fs.defaultFS=gs://your-bucket/

You may also want to modify those entries inside your core-site.xml file to point at your GCS location to make it easier.

Answer 2

Remove Local from your syntax. See the below syntax

INSERT OVERWRITE DIRECTORY 'gs://Your_Bucket_Path/'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY "\n" 
SELECT * FROM YourExistingTable;