How to load data to Hive table and make it also ac

Question

I have a table in Hive:

CREATE EXTERNAL TABLE sr2015(
creation_date STRING,
status STRING,
first_3_chars_of_postal_code STRING,
intersection_street_1 STRING,
intersection_street_2 STRING,
ward STRING,
service_request_type STRING,
division STRING,
section STRING )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES (
'colelction.delim'='\u0002',
'field.delim'=',',
'mapkey.delim'='\u0003',
'serialization.format'=',', 'skip.header.line.count'='1',
'quoteChar'= "\"")

The table is loaded data this way:

LOAD DATA INPATH "hdfs:///user/rxie/SR2015.csv" INTO TABLE sr2015;

Why the table is only accessible in Hive? when I attempt to access it in HUE/Impala Editor I got the following error:

AnalysisException: Could not resolve table reference: 'sr2015'

which seems saying there is no such a table, but the table does show up in the left panel.

In Impala-shell, error is different as below:

ERROR: AnalysisException: Failed to load metadata for table: 'sr2015' CAUSED BY: TableLoadingException: Failed to load metadata for table: sr2015 CAUSED BY: InvalidStorageDescriptorException: Impala does not support tables of this type. REASON: SerDe library 'org.apache.hadoop.hive.serde2.OpenCSVSerde' is not supported.

I have always been thinking Hive table and Impala table are essentially the same and difference is Impala is a more efficient query engine.

Can anyone help sort it out? Thank you very much.

Answer 1

Assuming that sr2015 is located in DB called db, in order to make the table visible in Impala, you need to either issue

invalidate metadata db;

or

invalidate metadata db.sr2015;

in Impala shell

However in your case, the reason is probably the version of Impala you're using, since it doesn't support the table format altogether