“Not a file” exception on select after successful

Question

I have created a table:

DROP TABLE IF EXISTS sampleout;

CREATE EXTERNAL TABLE sampleout(
    id bigint,
    LNG FLOAT,
    LAT FLOAT,
    GMTDateTime TIMESTAMP,
    calculatedcolumn FLOAT
    )

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
STORED AS TEXTFILE LOCATION 'wasb://[e-mail]/';

I then got a success from this query:

INSERT into TABLE   sampleout  select *, 0 as calculatedcolumn from sampletable

sampleout is the same as sampletable except for the extra column calculatedcolumn. After the successful insert, I opened the blob storage and opened the text file to verify the data is in the specified textfile location.

However...

select * from sampleout limit 10

returns the following error:

Logging initialized using configuration in file:/C:/apps/dist/hive-0.13.0.2.1.12.1-0003/conf/hive-log4j.properties
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/C:/apps/dist/hadoop-2.4.0.2.1.12.1-0003/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/C:/apps/dist/hbase-0.98.0.2.1.12.1-0003-hadoop2/lib/slf4j-log4j12-1.6.4.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
OK
Failed with exception java.io.IOException:java.io.IOException: Not a file: wasb://[e-mail]/hive
Time taken: 3.032 seconds

how is it that I can successfully insert, but cannot select from the table? Note that the error shows '/hive' added to the textfile location specified in the create.

Answer 1

Basically, do not put the TEXTFILE LOCATION at the root. Even though it is blob storage (no true folders), the structure needed by HDFS or something else wants you to use at least one sub folder in the location.

Moving from

wasb://[e-mail]/

to

wasb://[e-mail]/somefolder/

fixed the issue. Credit: https://stackoverflow.com/users/4951010/andrew-moll