Java sdk to copy in Redshift

Question

Is it possible to run a copy command from S3 to Redshift via java jdbc connection?

Example: copy test from 's3: //' CREDENTIALS 'aws_access_key_id = xxxxxxx; aws_secret_access_key = xxxxxxxxx '

Answer 1

Yes, try the code below

String dbURL = "jdbc:postgresql://xyus-east-1.redshift.amazonaws.com:5439/dev"; String MasterUsername = "userame"; String MasterUserPassword = "password"; Connection conn = null; Statement stmt = null; try{ //Dynamically load postgresql driver at runtime. Class.forName("org.postgresql.Driver"); System.out.println("Connecting to database..."); Properties props = new Properties(); props.setProperty("user", MasterUsername); props.setProperty("password", MasterUserPassword); conn = DriverManager.getConnection(dbURL, props); stmt = conn.createStatement(); String sql="copy test from 's3://' CREDENTIALS 'aws_access_key_id=xxxxxxx;aws_secret_access_key=xxxxxxxxx'" int j = stmt.executeUpdate(sql); stmt.close(); conn.close(); }catch(Exception ex){ //For convenience, handle all errors here. ex.printStackTrace(); } 

Answer 2

Sandesh's answer works great, but uses the PostgreSql driver. AWS provides the Redshift driver, which is better than the PostgreSql driver. Other things will remain the same. I hope this information can help others.

1) JDBC Driver will change from org.postgresql.Driver to com.amazon.redshift.jdbcXX.Driver , where XX is the version of Redshift driver. e.g. 42

2) Jdbc url will change from postgreSQL to redshift .

 import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import java.util.Properties; public class RedShiftJDBC { public static void main(String[] args) { Connection conn = null; Statement statement = null; try { //Make sure to choose appropriate Redshift Jdbc driver and its jar in classpath Class.forName("com.amazon.redshift.jdbc42.Driver"); Properties props = new Properties(); props.setProperty("user", "username***"); props.setProperty("password", "password****"); System.out.println("\n\nconnecting to database...\n\n"); //In case you are using postgreSQL jdbc driver. conn = DriverManager.getConnection("jdbc:redshift://********url-to-redshift.redshift.amazonaws.com:5439/example-database", props); System.out.println("\n\nConnection made!\n\n"); statement = conn.createStatement(); String command = "COPY my_table from 's3://path/to/csv/example.csv' CREDENTIALS 'aws_access_key_id=******;aws_secret_access_key=********' CSV DELIMITER ',' ignoreheader 1"; System.out.println("\n\nExecuting...\n\n"); statement.executeUpdate(command); //you must need to commit, if you realy want to have data copied. conn.commit(); System.out.println("\n\nThats all copy using simple JDBC.\n\n"); statement.close(); conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } }