title: Create table from CSV with values containing commas enclosed in quotes
body: <p>I'm trying to create a table in Impala from a CSV that I've uploaded into an HDFS directory.  The CSV contains values with commas enclosed inside quotes.  </p>
  
  <p>Example:</p>
  
  <pre><code>1.66.96.0/19,"NTT Docomo,INC.","Ntt Docomo",9605,"NTT DOCOMO, INC."
  1.66.128.0/17,"NTT Docomo,INC.","Ntt Docomo",9605,"NTT DOCOMO, INC."
  1.67.0.0/17,"NTT Docomo,INC.","Ntt Docomo",9605,"NTT DOCOMO, INC."
  1.67.128.0/18,"NTT Docomo,INC.","Ntt Docomo",9605,"NTT DOCOMO, INC."
  1.67.192.0/19,"NTT Docomo,INC.","Ntt Docomo",9605,"NTT DOCOMO, INC."
  </code></pre>
  
  <p>The <a href="http://www.cloudera.com/documentation/archive/impala/1-x/1-1-1/Installing-and-Using-Impala/ciiu_txtfile.html" rel="noreferrer">Impala documentation</a> says that this can be solved with an <code>ESCAPED BY</code> clause.  Here's my current code:</p>
  
  <pre><code>DROP TABLE IF EXISTS GeoIP2_ISP_Blocks_IPv4;
  
  CREATE TABLE GeoIP2_ISP_Blocks_IPv4 (
    network STRING
   ,isp STRING
   ,organization STRING
   ,autonomous_system_number STRING
   ,autonomous_system_organization STRING
    )
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ESCAPED BY '\\'
  
  LOCATION 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/';
  
  INVALIDATE METADATA GeoIP2_ISP_Blocks_IPv4;
  
  LOAD DATA INPATH 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/' 
  INTO TABLE GeoIP2_ISP_Blocks_IPv4;
  </code></pre>
  
  <p>I've also tried using the <code>ESCAPED BY '"'</code> clause.  In both cases, Impala takes the comma within the quotes and uses it as a delimiter, splitting the value into two columns.  </p>
  
  <p>Any ideas on how to fix the code so that this won't happen?</p>
  
  <p><strong>EDIT (6/9/2015)</strong></p>
  
  <p>So, I've gone through the following variations, based on suggestions from @K S Nidhin and @JTUP.  However, each variation returned the same result as the queries written without the <code>SERDEPROPERTIES</code> operator, with the commas still causing values to appear in the wrong columns:</p>
  
  <p><em>Variation 1</em></p>
  
  <pre><code>DROP TABLE IF EXISTS GeoIP2_ISP_Blocks_IPv4;
  
  CREATE TABLE GeoIP2_ISP_Blocks_IPv4 (
    network STRING
   ,isp STRING
   ,organization STRING
   ,autonomous_system_number STRING
   ,autonomous_system_organization STRING
    )
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
  WITH SERDEPROPERTIES ( "quoteChar" = "'", "escapeChar" = "\\" ) 
  
  LOCATION 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/';
  
  INVALIDATE METADATA GeoIP2_ISP_Blocks_IPv4;
  
  LOAD DATA INPATH 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/' 
  INTO TABLE GeoIP2_ISP_Blocks_IPv4;
  </code></pre>
  
  <p><em>Variation 2</em></p>
  
  <pre><code>DROP TABLE IF EXISTS GeoIP2_ISP_Blocks_IPv4;
  
  CREATE TABLE GeoIP2_ISP_Blocks_IPv4 (
    network STRING
   ,isp STRING
   ,organization STRING
   ,autonomous_system_number STRING
   ,autonomous_system_organization STRING
    )
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ESCAPED BY '\\'
  WITH SERDEPROPERTIES ( 'quoteChar' = '"', 'escapeChar' = '\\' )
  
  LOCATION 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/';
  
  INVALIDATE METADATA GeoIP2_ISP_Blocks_IPv4;
  
  LOAD DATA INPATH 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/' 
  INTO TABLE GeoIP2_ISP_Blocks_IPv4;
  </code></pre>
  
  <p><em>Variation 3</em></p>
  
  <pre><code>DROP TABLE IF EXISTS GeoIP2_ISP_Blocks_IPv4;
  
  CREATE TABLE GeoIP2_ISP_Blocks_IPv4 (
    network STRING
   ,isp STRING
   ,organization STRING
   ,autonomous_system_number STRING
   ,autonomous_system_organization STRING
    )
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ESCAPED BY '\\'
  WITH SERDEPROPERTIES (
     "separatorChar" = "\,",
     "quoteChar"     = "\""
  )
  
  LOCATION 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/';
  
  INVALIDATE METADATA GeoIP2_ISP_Blocks_IPv4;
  
  LOAD DATA INPATH 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/' 
  INTO TABLE GeoIP2_ISP_Blocks_IPv4;
  </code></pre>
  
  <p>Any other ideas, or further variations of the <code>SERDEPROPERTIES</code> operator to try?</p>
  
  <p><strong>EDIT (6/10/2016)</strong></p>
  
  <p>I was able to get a different variation of the query using the <code>SERDE</code> and <code>SERDEPROPERTIES</code> operators to work in Hive (based on code provided in the <a href="https://cwiki.apache.org/confluence/display/Hive/CSV+Serde" rel="noreferrer">Hive Documentation</a>), with the proper table being created:</p>
  
  <pre><code>DROP TABLE IF EXISTS GeoIP2_ISP_Blocks_IPv4;
  
  CREATE TABLE GeoIP2_ISP_Blocks_IPv4(network STRING
   ,isp STRING
   ,organization STRING
   ,autonomous_system_number STRING
   ,autonomous_system_organization STRING)
  
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
  
  WITH SERDEPROPERTIES (
     'separatorChar' = ',',
     'quoteChar'     = '"',
     'escapeChar'    = '\\'
  )   
  STORED AS TEXTFILE;
  
  LOAD DATA INPATH 'hdfs://.../GeoIP2_ISP_Blocks_IPv4/' 
  INTO TABLE GeoIP2_ISP_Blocks_IPv4;
  </code></pre>
  
  <p>Since the <code>SERDE</code> operator isn't available in Impala, this solution wouldn't work there.  I'm fine creating the tables in Hive, but it's still irksome that I can't find a viable solution in Impala.</p>