Error in connecting to oracle local database from apache spark java

Question

Below is the code that I am using to connect to oracle local database from apache spark java program, but I am getting error:

java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name

Here is my code:

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();
  }

}

Below is the POM file

<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>

BHANUMATHI H M · Accepted Answer · 2017-05-08 06:09:46Z

You can try this below approach.. It will work.

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();

 }
}

T. Gawęda · Accepted Answer · 2017-01-11 08:44:30Z

0

Your dbtable is incorrect, try:

 options.put("dbtable", "(select LOC_ITEM_ID from loc_item_master) as loc_item_id");

Everything in dbtable should be a valid FROM fragment.

You cannot write: select ... from select LOC_ITEM_ID from loc_item_master, but you can write select ... from (select LOC_ITEM_ID from loc_item_master) as loc_item_id

Of course also table's schema must be ok, you can specify it using schema.table style in query

answered Jan 11, 2017 at 8:44

T. Gawęda

16.1k5 gold badges51 silver badges62 bronze badges

8 Comments

Sandesh Puttaraj Over a year ago

After trying the above line of code previous error got resolved and now it is showing this error (java.sql.SQLSyntaxErrorException: ORA-00911: invalid character)

T. Gawęda Over a year ago

@SandeshPuttaraj Could you post whole error? There should be info what character is wrong

Sandesh Puttaraj Over a year ago

Full error is ( Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties java.sql.SQLSyntaxErrorException: ORA-00911: invalid character at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)

T. Gawęda Over a year ago

Check options.put("dbtable", "(select LOC_ITEM_ID from loc_item_master) loc_item_id"); - without as

T. Gawęda Over a year ago

@SandeshPuttaraj remove also ` options.put("partitionColumn", "LOC_ITEM_ID");`. I don't have idea what else could be wrong

|

Collectives™ on Stack Overflow

Error in connecting to oracle local database from apache spark java

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related