Snippet Java for Redshift

With snippet Java for Redshift, you can free your mind

  1. Open JDBC Connection and Change Schema

    public Connection openRedShiftConnection(String schemaName) throws Exception {
      Class.forName("com.amazon.redshift.jdbc41.Driver");
      Connection conn = DriverManager.getConnection(CONFIG.HOST_URL, CONFIG.USERNAME, CONFIG.PASSWORD);
      conn.createStatement().execute("SET search_path TO " + schemaName);
      return conn;
    }
    
  2. Example Copy Data Gzipped to Table with Column Names

    public String generateSqlCopyDataGzippedToRedshiftTable(String TABLE_NAME, String[] COLUMN_NAMES, String S3_KEY_FILE) throws Exception {
      StringBuilder sql = new StringBuilder();
      sql.append("COPY myschema.").append(TABLE_NAME);
      sql.append("(").append(StringUtils.join(COLUMN_NAMES, ',')).append(") ");
      sql.append("FROM 's3://").append(CONFIG.AMAZON_BUCKET_NAME).append("/").append(S3_KEY_FILE).append("' ");
      sql.append("CREDENTIALS 'aws_access_key_id=").append(CONFIG.AMAZON_ACCESS_KEY_ID);
      sql.append(";aws_secret_access_key=").append(CONFIG.AMAZON_SECRET_KEY).append("' ");
      sql.append("DELIMITER '\\t' DATEFORMAT AS 'DD-MON-YYYY' TIMEFORMAT AS 'DD-MON-YYYY HH24:MI:SS' ");
      sql.append("IGNOREHEADER 1 IGNOREBLANKLINES REMOVEQUOTES GZIP COMPUPDATE ON STATUPDATE ON");
      return sql.toString();
    }
    public String generateSqlCopyListDataGzippedToRedshiftTableWithManifestFile(String TABLE_NAME, String[] COLUMN_NAMES, String S3_KEY_MANIFEST_FILE) throws Exception {
      StringBuilder sql = new StringBuilder();
      sql.append("COPY myschema.").append(TABLE_NAME);
      sql.append("(").append(StringUtils.join(COLUMN_NAMES, ',')).append(") ");
      sql.append("FROM 's3://").append(CONFIG.AMAZON_BUCKET_NAME).append("/").append(S3_KEY_MANIFEST_FILE).append("' ");
      sql.append("CREDENTIALS 'aws_access_key_id=").append(CONFIG.AMAZON_ACCESS_KEY_ID);
      sql.append(";aws_secret_access_key=").append(CONFIG.AMAZON_SECRET_KEY).append("' ");
      sql.append("MANIFEST DELIMITER '\\t' DATEFORMAT AS 'DD-MON-YYYY' TIMEFORMAT AS 'DD-MON-YYYY HH24:MI:SS' ");
      sql.append("IGNOREHEADER 1 IGNOREBLANKLINES REMOVEQUOTES GZIP COMPUPDATE ON STATUPDATE ON");
      return sql.toString();
    }
    
  3. Generate Manifest File Content

    private String generateManifestFileContent(String s3Bucket, Collection<String> s3Keys) {
        StringBuilder sb = new StringBuilder();
        sb.append("{\n");
        sb.append("  \"entries\": [\n");
        for (String key : s3Keys) {
            sb.append("    {\"url\":\"s3://");
            sb.append(s3Bucket);
            sb.append("/");
            sb.append(key);
            sb.append("\"},\n");
        }
        sb.delete(sb.length() - 2, sb.length() - 1);
        sb.append("  ]\n");
        sb.append("}\n");
        return sb.toString();
    }
    

Comments

Popular posts from this blog

Reduce TIME_WAIT Socket Connections