title: How can I correct AWS Glue Crawler/Data Catalog inferring all fields in CSV as strings when they're clearly not?
body: <p>I have a big CSV text file uploaded weekly to an S3 path partitioned by upload date (maybe not important). The schema of these files are all the same, the formatting is all the same, the naming conventions are all the same. Each file contains ~100 columns and ~1M rows of mixed text/numeric types. The raw data looks like this:</p>
  <pre><code>id,date,string,int_values,double_values
  &quot;6F87U&quot;,2021-03-21,&quot;Text&quot;,0,1.1483
  &quot;8DU87&quot;,2021-03-22,&quot;More text, oh yes&quot;,1,2.525
  &quot;79LO2&quot;,2021-03-23,&quot;Moar, give me moar, text&quot;,2,3.485489
  </code></pre>
  <p>When I run a Crawler with <em>everything default</em>, querying with Athena like so:</p>
  <pre><code>select * from tb_csv_data
  </code></pre>
  <p>...the results in Athena are thus:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>date</th>
  <th>string</th>
  <th>int_values</th>
  <th>double_values</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>&quot;6F87U&quot;</td>
  <td>2021-03-21</td>
  <td>&quot;Text&quot;</td>
  <td>0</td>
  <td>1.1483</td>
  </tr>
  <tr>
  <td>&quot;8DU87&quot;</td>
  <td>2021-03-22</td>
  <td>&quot;More text</td>
  <td>oh yes&quot;</td>
  <td>1</td>
  </tr>
  <tr>
  <td>&quot;79LO2&quot;</td>
  <td>2021-03-23</td>
  <td>&quot;Moar</td>
  <td>give me moar</td>
  <td>text</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The problem at this level seems to be with proper detection (read: ignoring) of commas as delimiters within quotation marks. So I have a CSV classifier with the following characteristics that I have attached to the Crawler, I run the Crawler again with the classifier attached, and the resulting table properties are thus:</p>
  <pre><code>Input format    org.apache.hadoop.mapred.TextInputFormat
  Output format   org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
  Serde serialization lib org.apache.hadoop.hive.serde2.OpenCSVSerde
  
  Serde parameters    
  quoteChar &quot; 
  separatorChar ,
  
  Table properties    
  sizeKey 4356512114 
  objectCount 3 
  UPDATED_BY_CRAWLER crawler-name 
  CrawlerSchemaSerializerVersion 1.0 
  recordCount 3145398 
  averageRecordSize 1384 
  CrawlerSchemaDeserializerVersion 1.0 
  compressionType none 
  columnsOrdered true 
  areColumnsQuoted true
  delimiter , 
  typeOfData file
  </code></pre>
  <p>The resulting table with the same simple Athena query as above seems to be correct:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>date</th>
  <th>string</th>
  <th>int_values</th>
  <th>double_values</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>6F87U</td>
  <td>2021-03-21</td>
  <td>Text, yes</td>
  <td>0</td>
  <td>1.1483</td>
  </tr>
  <tr>
  <td>8DU87</td>
  <td>2021-03-22</td>
  <td>More text, oh yes</td>
  <td>1</td>
  <td>2.525</td>
  </tr>
  <tr>
  <td>79LO2</td>
  <td>2021-03-23</td>
  <td>Moar, give me moar, text</td>
  <td>2</td>
  <td>3.485489</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The expected automatic inference of data types is supposed to be this (let's simplify and presume the date is correct as a string):</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Column name</th>
  <th>Data type</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>id</td>
  <td>string</td>
  </tr>
  <tr>
  <td>date</td>
  <td>string</td>
  </tr>
  <tr>
  <td>string</td>
  <td>string</td>
  </tr>
  <tr>
  <td>int_values</td>
  <td>bigint (or long)</td>
  </tr>
  <tr>
  <td>double_values</td>
  <td>double</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>...but instead they're all strings!</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Column name</th>
  <th>Data type</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>id</td>
  <td>string</td>
  </tr>
  <tr>
  <td>date</td>
  <td>string</td>
  </tr>
  <tr>
  <td>string</td>
  <td>string</td>
  </tr>
  <tr>
  <td>int_values</td>
  <td>string</td>
  </tr>
  <tr>
  <td>double_values</td>
  <td>string</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need this data to be accurately queryable from Athena as it is, where it is, so what can I do without further processing of the raw data? I suppose I could manually adjust the table properties in the Console but is that really correct when I need the entire pipeline to be automated? I also want to avoid having to cast types in queries 80+ times for each field as most of these columns are numeric. What can I do?</p>
  <p>Thank you!</p>