INVALID_FUNCTION_ARGUMENT: Invalid format: ""

Question

From the S3 bucket I created a table in Athena using:
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   'separatorChar' = ';',
   'quoteChar' = '"',
   'escapeChar' = '\'
   )
LOCATION 's3://'
TBLPROPERTIES ('skip.header.line.count'='1');

Now I'm trying to convert everyting to the correct datatype. For example 'date'. But I get the following error: INVALID_FUNCTION_ARGUMENT: Invalid format: "". Sometimes with double quotes and sometimes with single. I think it's because you can't have a 'blank' date. So I'm trying to remove the blanks. But I don't know how. I've tried using -IS NOT NULL. But that doesn't work.

Accepted Answer

I think that you are seeing this error because of the presence of empty string '' or "" but not the **Null** value. Please understand that there is a difference between Null value and empty string.
* A **Null** is a value that is UNKNOWN so nothing is present.
* An empty string is of type string but it has some value which is an empty one.

As far as I know, Null should not occupy any space in the memory as opposed to empty string.

This might be the reason why IS NOT NULL is not working for you. Therefore, please try running a query that will ignore empty strings instead of NULL values. 
For example, I have a table named 'test' and in one of the columns I stored a '' (empty string). I successfully ignored it using the below query:

```
SELECT * FROM test WHERE column1 <> ''
```
Try this on your end as well.

Answer

Thank you for your answer! You are right :)

INVALID_FUNCTION_ARGUMENT: Invalid format: ""

Relevant content