title: Redshift : unmatched number of columns between table and file which was generated by spark in parquet format
body: <p><strong>Input dataset was generated by pyspark below command,</strong> </p>
  
  <pre><code>dfs_ids1.write.parquet('./outputs-parquet/i94-apr16.parquet', mode='overwrite')    
  </code></pre>
  
  <p>COPY statement &amp; error message are, </p>
  
  <pre><code>COPY staging_ids FROM 's3://sushanth-dend-capstone-files/i94-apr16.parquet/' IAM_ROLE 'arn:aws:iam::164084742828:role/dwhRole' FORMAT AS PARQUET ;
  
  S3 Query Exception (Fetch)
  DETAIL:  
    -----------------------------------------------
    error:  S3 Query Exception (Fetch)
    code:      15001
    context:   Task failed due to an internal error. Unmatched number of columns between table and file. Table columns: 23, Data columns: 22, File name: https://s3.us-west-2.amazonaws.com/sushanth-dend-capstone-files/i94-apr16.parquet/part-00000-6034cb60-860e-4a6c-a86d-6
    query:     1867
    location:  dory_util.cpp:1119
    process:   fetchtask_thread [pid=1331]
    -----------------------------------------------
  </code></pre>
  
  <p>When checking the parquet using S3 "Select from", found that for some JSON sets, column <code>occup</code> is missing, this specific column mostly contains "null or STU". </p>
  
  <pre><code> {
          "CoC": 101,
          "CoR": 101,
          "PoE": "BOS",
          "landing_state": "MA",
          "age": 58,
          "visa_issued_in": "TIA",
          "biryear": 1958,
          "gender": "M",
          "airline": "LH",
          "admnum": 2147483647,
          "fltno": "00422",
          "visatype": "B1",
          "arrival_mode": "Air",
          "visit_purpose": "Business",
          "arrival_dt": "2016-04-01",
          "departure_dt": "2016-04-05",
          "DaysinUS": 4,
          "added_to_i94": "2016-04-01",
          "allowed_until": "2016-04-06",
          "entry_exit": "exit",
          "month": 4
      }
  </code></pre>
  
  <p>To verify this column is missing, i read this parquet file in Athena using below parameters,<br>
  S3 Path : <code>s3://sushanth-dend-capstone-files/i94-apr16.parquet/</code>    </p>
  
  <p>Bulk Load of columns : <code>CoC int, CoR int, PoE string, landing_state string, age int, visa_issued_in string, occup string, biryear int, gender string, airline string, admnum int, fltno string, visatype string, arrival_mode string, visit_purpose string, arrival_dt date, departure_dt date, DaysinUS int, added_to_i94 date, allowed_until date, entry_exit string, month int</code>    </p>
  
  <p><strong>Athena DDL</strong></p>
  
  <pre><code>CREATE EXTERNAL TABLE IF NOT EXISTS capstone.staging_ids (
    `coc` int,
    `cor` int,
    `poe` string,
    `landing_state` string,
    `age` int,
    `visa_issued_in` string,
    `occup` string,
    `biryear` int,
    `gender` string,
    `airline` string,
    `admnum` int,
    `fltno` string,
    `visatype` string,
    `arrival_mode` string,
    `visit_purpose` string,
    `arrival_dt` date,
    `departure_dt` date,
    `daysinus` int,
    `added_to_i94` date,
    `allowed_until` date,
    `entry_exit` string,
    `month` int 
  )
  ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  WITH SERDEPROPERTIES (
    'serialization.format' = '1'
  ) LOCATION 's3://sushanth-dend-capstone-files/i94-apr16.parquet/'
  TBLPROPERTIES ('has_encrypted_data'='false');
  </code></pre>
  
  <p>When i run the query i could see column 'occup' with STU for some rows.</p>
  
  <p>Question is,   </p>
  
  <ul>
  <li>How to write all the columns from spark dataframe in parquet format
  file ?</li>
  <li>How to load this sort of file into redshift via COPY statement, is parquet not the right format to load into redshift ? </li>
  </ul>