How to read quoted CSV with NULL values into Amazon Athena : <p>I'm trying to create an external table in Athena using quoted CSV file stored on S3. The problem is, that my CSV contain missing values in columns that should be read as INTs. Simple example:</p>

<p>CSV:</p>

<pre><code>id,height,age,name
1,,26,"Adam"
2,178,28,"Robert"
</code></pre>

<p>CREATE TABLE DEFINITION:</p>

<pre><code>CREATE EXTERNAL TABLE schema.test_null_unquoted (
  id INT,
  height INT,
  age INT,
  name STRING
)
ROW FORMAT 
SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'separatorChar' = ",",
'quoteChar' = '"',
'skip.header.line.count' = '1'
)
STORED AS TEXTFILE
LOCATION 's3://mybucket/test_null/unquoted/'
</code></pre>

<p><code>CREATE TABLE</code> statement runs fine but as soon as I try to query the table, I'm getting <code>HIVE_BAD_DATA: Error parsing field value ''</code>.</p>

<p>I tried making the CSV look like this (quote empty string):</p>

<pre><code>"id","height","age","name"
1,"",26,"Adam"
2,178,28,"Robert"
</code></pre>

<p>But it's not working.</p>

<p>Tried specifying <code>'serialization.null.format' = ''</code> in <code>SERDEPROPERTIES</code> - not working.</p>

<p>Tried specifying the same via <code>TBLPROPERTIES ('serialization.null.format'='')</code> - still nothing.</p>

<p>It works, when you specify all columns as <code>STRING</code> but that's not what I need.</p>

<p>Therefore, the question is, is there <strong>any way</strong> to read a quoted CSV (quoting is important as my real data is much more complex) to Athena with correct column specification?</p>
