"ORA-00933: SQL command not properly ended" error while querying Oracle DB using Spark

Question

I was trying to load data from Oracle database using Spark data source API.

Since I need to load data by query, I used the query below which I put together from some examples online:

Map<String, String> options = new HashMap<>();
options.put("driver", MYSQL_DRIVER);
options.put("user", MYSQL_USERNAME);
options.put("password", MYSQL_PWD);
options.put("url", MYSQL_CONNECTION_URL); 
options.put("dbtable", "(select emp_no, emp_id from employees) as employees_data");
options.put("lowerBound", "10001");
options.put("upperBound", "499999");
options.put("numPartitions", "10");

DataFrame jdbcDF = sqlContext.load("jdbc", options);

This gets an exception:

Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

I doubt that we can't give "as employees_data" for an Oracle query, so what am I doing wrong?

Your code has references like MYSQL_DRIVER; are you really connecting to an Oracle database, or a MySQL database? — Alex Poole
– Alex Poole, Commented Mar 1, 2016 at 18:31
sorry, i forgot to change the variable name... the values inside the variable are using Oracle drivers private static final String MYSQL_DRIVER = "oracle.jdbc.OracleDriver"; private static final String MYSQL_USERNAME = "qauser"; private static final String MYSQL_PWD = "qauser"; private static final String MYSQL_CONNECTION_URL ="jdbc:oracle:thin:@//"; — prakash
– prakash, Commented Mar 1, 2016 at 18:35

Alex Poole · Accepted Answer · 2016-03-01 18:43:13Z

5

I doubt that we can't give "as employees_data" for an Oracle query

You may doubt it, but you can't use AS for a table alias in Oracle. You can for column aliases, where it is optional, but it is not allowed for table aliases. You can see that in the syntax diagram.

Assuming Spark doesn't mind the alias itself, you can just remove the AS:

options.put("dbtable", "(select emp_no, emp_id from employees) employees_data");

edited Mar 1, 2016 at 18:43

answered Mar 1, 2016 at 18:34

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Michael Broughton Over a year ago

Worse, he's trying to alias the entire result set with those brackets! "(select emp_no, emp_id from employees) as employees_data"

Alex Poole Over a year ago

@MichaelBroughton - I think that's OK, from the Spark docs; the dbtable entry says it can be whatever can be in a from clause and "instead of a full table you could also use a subquery in parentheses", so this would be OK on that basis. Not sure the alias is actually useful though. I've never used this though (or seen it before...)

Michael Broughton Over a year ago

If it is passing that string to Oracle, I get that same ORA-00933 error with "(select 1 from dual) as mydual"

prakash Over a year ago

@AlexPoole if i don't use the alias "as employees_data", spark gives me Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name, I have tested this query using JdbcRDD works fine , since JDBCRDD is discouraged against the datasource API

Alex Poole Over a year ago

@prakash - what happens when you just remove the as?

|

drneel · Accepted Answer · 2016-05-19 12:40:44Z

0

Try this...

Map < String, String > oracle_options = new HashMap<>()
oracle_options.put("driver", "oracle.jdbc.OracleDriver");
oracle_options.put("url", "jdbc:oracle:thin:username/password@//hostName/instanceName);
oracle_options.put("dbtable", "tableName");
DataFrame dataFrame = hContext.read().format("jdbc").options(oracle_options).load().select(String col1,String col2.....));

Where hContext is HiveContex instance. if you are using selection means where condition use as follows:

DataFrame dataFrame = hContext.read().format("jdbc").options(oracle_options).load().select(String col1,String col2.....)).where(String expr);

edited May 19, 2016 at 12:40

drneel

2,9055 gold badges32 silver badges49 bronze badges

answered May 19, 2016 at 12:10

Manjunatha H C

111 bronze badge

Collectives™ on Stack Overflow

"ORA-00933: SQL command not properly ended" error while querying Oracle DB using Spark

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related