从apache spark java连接到oracle本地数据库时出错
提问于 2017-01-10 05:52:59
下面是我使用的代码，用于从程序连接到oracle本地数据库，但是我得到了错误：
java.sql.SQLSyntaxErrorException: ORA-00903:无效表名
这是我的代码：
public static void main( String[] args )
{
String MYSQL_DRIVER = "oracle.jdbc.driver.OracleDriver";
String MYSQL_CONNECTION_URL = "jdbc:oracle:thin:@//192.168.2.85:1521/stage";
JavaSparkContext sc = new JavaSparkContext(new SparkConf().setAppName("SparkJdbcDs").setMaster("local[*]"));
SQLContext sqlContext = new SQLContext(sc);
Map<String, String> options = new HashMap<String, String>();
options.put("driver", MYSQL_DRIVER);
options.put("url", MYSQL_CONNECTION_URL);
options.put("dbtable", "select LOC_ITEM_ID from loc_item_master");
options.put("partitionColumn", "LOC_ITEM_ID");
options.put("user", "ad_tables");
options.put("password", "ad_tables");
options.put("lowerBound", "4467");
options.put("upperBound", "4749");
options.put("numPartitions", "10");
try {
Dataset<Row> jdbcDF = sqlContext.load("jdbc", options);
List<Row> employeeFullNameRows = jdbcDF.collectAsList();
for (Row employeeFullNameRow : employeeFullNameRows) {
System.out.println(employeeFullNameRow);
}
sc.close();
} catch (Exception e) {
e.printStackTrace();
}
}下面是POM文件
<dependencies>
<dependency>
<groupId>com.databricks</groupId>
<artifactId>spark-csv_2.11</artifactId>
<version>1.5.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.univocity/univocity-parsers -->
<dependency>
<groupId>com.univocity</groupId>
<artifactId>univocity-parsers</artifactId>
<version>2.3.0</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-mllib_2.11</artifactId>
<version>2.0.1</version>
</dependency>
<dependency> <!-- Spark dependency -->
<groupId>org.apache.spark</groupId>
<artifactId>spark-core_2.11</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-sql_2.11</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
</dependencies>
回答 2
Stack Overflow用户
发布于 2017-05-07 22:09:46
你可以试试下面的方法。看起来不错。
public class OracleDatabaseConnection {
public static void main(String[] args) {
System.setProperty("hadoop.home.dir", "C:\\AD_classfication\\Apachespark\\winutil");
JavaSparkContext sc = new JavaSparkContext(new SparkConf().setAppName("SparkJdbcDs").setMaster("local[*]"));
SQLContext sqlContext = new SQLContext(sc);
SparkSession spark = SparkSession.builder().appName("JavaTokenizerExample").getOrCreate();
Properties connectionProperties = new Properties();
connectionProperties.put("user", "ad_tables");
connectionProperties.put("password", "ad_tables");
String query = "sparkour.people";
query = "(select LOC_ITEM_ID from loc_item_master)";
Dataset<Row> jdbcDF2 = spark.read().jdbc("jdbc:oracle:thin:@//192.168.2.85:1522/stage", query,connectionProperties);
jdbcDF2.show();
}
}Stack Overflow用户
发布于 2017-01-11 00:44:30
您的dbtable不正确，请尝试：
options.put("dbtable", "(select LOC_ITEM_ID from loc_item_master) as loc_item_id");dbtable中的所有内容都应该是有效的FROM片段。
你不能写：select ... from select LOC_ITEM_ID from loc_item_master，但是你可以写select ... from (select LOC_ITEM_ID from loc_item_master) as loc_item_id
当然，表的模式也必须是确定的，您可以在查询中使用schema.table样式指定它
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41570500
复制相关文章
腾讯云开发者
