是否可以通过java jdbc连接从S3向Redshift发出复制命令?
示例:从's3://‘CREDENTIALS 'aws_access_key_id=xxxxxxx;aws_secret_access_key=xxxxxxxxx’复制测试
发布于 2014-09-19 19:08:47
是,尝试如下代码
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();
}
发布于 2018-08-18 20:10:36
Sandesh的答案工作得很好,但它使用PostgreSql驱动程序。亚马逊网络服务提供了比PostgreSql驱动更好的红移驱动。其他的事情都会保持不变。我希望这些信息可以帮助其他人。
1)JDBC驱动由org.postgresql.Driver
改为com.amazon.redshift.jdbcXX.Driver
,其中XX为Redshift驱动的版本。例如42
。
2)Jdbc url由postgreSQL
改为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();
}
}
}
https://stackoverflow.com/questions/18465313
复制相似问题