title: Troubleshooting COPY errors on AWS Redshift
body: <h3>Update: If figured this out but am still interested in an explanation. The problem was that I was running the code below while also connected to my Redshift cluster from SqlWorkbenchJ (both running on the same laptop). The second I disconnect my SqlWorkbenchJ session and re-run my code, it doesn't hang. <strong>Why?</strong></h3>
  
  <hr>
  
  <p><strong>Please note:</strong> Although I mention Java/JDBC in this question, it is strictly a question about troubleshooting Redshift and is language/framework-agnostic!!!</p>
  
  <hr>
  
  <p>Also here's an SSCCE repo that perfectly reproduces the hanging issue:
  <a href="https://github.com/bitbythecron/redshift-copy-troubleshooting" rel="nofollow noreferrer">https://github.com/bitbythecron/redshift-copy-troubleshooting</a></p>
  
  <p>I'm trying to run the following Redshift COPY command from Java code (using Postgres JDBC driver):</p>
  
  <pre><code>COPY my_schema.mytable
  FROM 's3://com.example.mybucket/mydata.csv/part-00000-bc1b179d-b4c1-459f-8f5e-8fe361d4b40f-c000.csv'
  iam_role 'arn:aws:iam::blah:role/MyRedshiftRole'
  csv;
  </code></pre>
  
  <p>If I've read the docs right, this should:</p>
  
  <ol>
  <li>Read a CSV file stored on S3</li>
  <li>Copy its contents into a Redshift table (<code>my_schema.mytable</code>)</li>
  </ol>
  
  <p>When I run this command in my Redshift UI client (SqlWorkbenchJ) it executes correctly and runs in a few seconds. However when I execute the following JDBC code (using the <strong>exact</strong> same connection URL, credentials, etc.) the code just hangs at the <code>executeUpdate</code> command:</p>
  
  <pre><code>Connection conn = null;
  Statement statement = null;
  try {
    Class.forName("org.postgresql.Driver");
    Properties props = new Properties();
    props.setProperty("user", redshiftInfo.username);
    props.setProperty("password", redshiftInfo.password);
  
    log.info("\n\nAttempting to connect!\n\n");
  
    conn = DriverManager.getConnection("jdbc:postgresql://&lt;sameExactUrl_thatIUser_inSqlWorkbenchJ&gt;", props);
  
    log.info("\n\nConnection made!\n\n");
  
    statement = conn.createStatement();
  
    String command = "COPY my_schema.my_table FROM 's3://com.example.mybucket/mydata.csv/part-00000-bc1b179d-b4c1-459f-8f5e-8fe361d4b40f-c000.csv' iam_role 'arn:aws:iam::blah:role/MyRedshiftRole' csv";
  
    log.info("\n\nExecuting...\n\n");
  
    statement.executeUpdate(command);
  
    log.info("\n\nHey I think it worked!!!\n\n");
  
    statement.close();
    conn.close();
  } catch (Exception ex) {
      log.info(ExceptionUtils.getStackTrace(ex));
  }
  </code></pre>
  
  <p>When this runs, in the logs I get to the <code>Executing...</code> log statement, but then the software just hangs. I've waited for as long as 30 minutes to see if it was just slow for some reason. I've also refreshed my SqlWorkbenchJ connection throughout (and after) this 30 minutes and ran <code>SELECT COUNT(*) FROM my_schema.my_table</code> and the count is always 0. So its making the connection but then nothing is actually being copied, or if it is, its not being committed.</p>
  
  <p>I'd like to see what's happening on the Redshift side of things: are there any tables or logs (in the AWS console or otherwise) I can tail or inspect to see if records are actually being copied and staged somewhere, or to see if there are any errors being thrown reported from Redshift's perspective?</p>