Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Error on Creating External Table from CSV in Impala after latest CDP Update

avatar
New Contributor

Hi,

 

I am getting error when I am querying my table in impala editor.

The simple query:

select * from table GeoIP2_ISP_Blocks_IPv4;

The error:

Neel_Sharma_1-1659357815162.png

The table creation query is give below:

CREATE External TABLE GeoIP2_ISP_Blocks_IPv4 (
Client string,
ID int,
Number int,
Name string,
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
WITH SERDEPROPERTIES (
"separatorChar" = "\,",
"quoteChar" = "\""
)

LOCATION 'hdfs:///user/hive/warehouse/tips/'
tblproperties ("skip.header.line.count"="1");

 

The same error is coming when I am creating external tables from tab delimited files. All this is happening after latest CDP Update.

 

Please someone help me it would be really helpful.

1 ACCEPTED SOLUTION

avatar
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
2 REPLIES 2

avatar
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login

avatar
New Contributor

The issue is resolved by adding Stored as textfile in the table creation query.

CREATE External TABLE GeoIP2_ISP_Blocks_IPv4 (
Client string,
ID int,
Number int,
Name string,
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

Stored as textfile    <---------------------- this new line needs to be added
WITH SERDEPROPERTIES (
"separatorChar" = "\,",
"quoteChar" = "\""
)

LOCATION 'hdfs:///user/hive/warehouse/tips/'
tblproperties ("skip.header.line.count"="1");

 

Cloudera has changed the default file format from txt to parquet. So we need to specify this line for text files.

 

Thanks @mszurap  for the help.