Java sdk for copying to Redshift

问题:

Is it possible to fire a copy command from S3 To Redshift through java jdbc connection?

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

回答1:

Yes try code as below

String dbURL = "jdbc:postgresql://x.y.us-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();
            }

回答2:

Sandesh's answer works perfectly fine, but it uses PostgreSql driver. AWS Provides Redshift driver, which is better than PostgreSql driver. Rest of things would remain same. I hope this information may 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();
        }
    }
}