AWS Athena: "HIVE_BAD_DATA: Error parsing column 'X' : empty String"

user_7997598 0
Viewed 5644

I want to create an external table on AWS Athena based on a CSV file, using OpenCSVSerde.

CREATE EXTERNAL TABLE `table`(name string, value double, group string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ("separatorChar" = ",", "escapeChar" = "\\","skip.header.line.count"="1",  "serialization.null.format"="") 
LOCATION 's3://path' ;

The file contains some missing values for the 'value' field (double type). It looks like this:

name,value,group    
name1,0.5,a
name2,0.2,a
name3,,a
name4,0.11,b

After creating it, when I try to run some queries (like a simple "select * from table"), Athena gives this error:

HIVE_BAD_DATA: Error parsing column '1': empty String

As you can see from the create external table query, I tried to add 'serialization.null.format'='' but it doesn't seem to be working. The only solution I found was to define the column 'value' as a string, but it's obviously not the ideal one (especially if you have many columns like this).

Do you have any idea of how to resolve this?

Thank you

amazon-athena hive amazon-web-services
2 Answers

As Vamsi Prabhala mentions OpenCSVSerde simply does not support this, from the documentation:

The parser does not recognize empty or null values in columns defined as a numeric data type, leaving them as the default data type of STRING. The workaround is to declare the column as STRING and then CAST it in a SELECT query or view.

So if you do want to stick with OpenCSVSerde, create the table as you did ("serialization.null.format"="" does not work):

CREATE EXTERNAL TABLE `table`(
    name string,
    value double,
    group string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES (
    "separatorChar" = ",",
    "escapeChar" = "\\",
    "skip.header.line.count"="1") 
LOCATION 's3://path';

Then create a view as:

CREATE VIEW "typed_table" AS
SELECT
"name",
CAST(
    "value" CASE
        "value"
        WHEN '' THEN NULL
        ELSE "value"
    END AS DOUBLE
),
"group"
FROM "table";
user_46834880

There are a few things to watch out for here.

  1. Whilst creating a table in Athena (eg from a CSV), you need to ensure that the table headers are not appearing. If you notice the row 1 has the table headers, this may well be the reason why the HIVE_BAD_DATA error is appearing. You can ignore headers by simply by adding the table property

    TBLPROPERTIES("skip.header.line.count"="1")

Eg.

CREATE EXTERNAL TABLE `table`(name string, value double, group string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ("separatorChar" = ",", "escapeChar" = "\\","skip.header.line.count"="1",  "serialization.null.format"="") 
LOCATION 's3://path' 
TBLPROPERTIES("skip.header.line.count"="1");

Alternately, if the table is already created, you can alter the table using

ALTER TABLE table_name SET TBLPROPERTIES ("skip.header.line.count"="1)

  1. Whilst Querying the table, you might have to parse the string into the desired data_type. You could do so by using cast or even date_parse Eg.

    SELECT * FROM table_name order by date_parse(field,'%d/%m/%Y') desc limit 100;

user_12522370
Related