AnalysisException: It is not allowed to add database prefix

Question

I am attempting to read in data from a table that is in a schema using JDBC. However, I'm getting an error:

org.apache.spark.sql.AnalysisException: It is not allowed to add database prefix `myschema` for the TEMPORARY view name.;

The code is pretty straight forward, error occurs on the third line (others included just to show what I am doing). myOptions includes url, dbtable, driver, user, password.

SQLContext sqlCtx = new SQLContext(ctx);
Dataset<Row> df = sqlCtx.read().format("jdbc").options(myOptions).load();

df.createOrReplaceTempView("myschema.test_table");
df = sqlCtx.sql("select field1, field2, field3 from myschema.test_table");

So if database/schema qualifiers are not allowed, then how do you reference the correct one for your table? Leaving it off gives an 'invalid object name' from the database which is expected.

The only option I have at the database side is to use default schema, however this is user-based and not session-based so I would have to create one user and connection per schema I want to access.

What am I missing here? This seems like a common use case.

Edit: Also for those attempting to close this... "a problem that can no longer be reproduced or a simple typographical error" how about a comment as to why this is the reason to close? If I have made a typo or made a simple mistake, leave a comment and show me what. I can't be the only person who has run into this.

registerTempTable in Spark 1.2 used to work this way, and we were told that createOrReplaceTempView was supposed to replace it in 2.x. Yet the functionality is not there.

Answer 1

I figured it out.

The short answer is... dbtable name and the temp view/table name are two different things and don't have to have the same value. dbtable defines were in the database to go for the data, temp view/table is used to define what you call this in your Spark SQL.

This was confusing at first because in Spark 1.6 it allowed the view name to match the full table name (and so the software I am using plugged it in for both for 1.6). If you were coding this by hand, you would just use a nonqualified table name for the temp table or view on either 1.6 or 2.2.

In order to reference a table in a schema in Spark 1.6, I had to do the following because the dbtable and view name were the same:

1. dbtable to "schema.table"
2. registerTempTable("schema.table")
3. Reference table as `schema.table` (include the ticks to treat the entire thing as an identifier to match the view name) in the SQL

However, in Spark 2.2, you need to, since schema/database is not allowed in the view name:

1. dbtable to "schema.table"
2. createOrReplaceTempView("table")
3. Reference table (not schema.table) in the SQL (matching the view)