I am trying to create a HIVE table in Apache Spark using Databricks.

I am using the following code:

%sql
CREATE OR REPLACE VIEW testdatabase.testtable

AS

SELECT
*
FROM location '/mnt/mylocation'

But I'm getting the following error:

Error in SQL statement: ParseException: 
extraneous input ''/mnt/mylocation'' expecting {<EOF>, ';'}(line 7, pos 14)

== SQL ==
CREATE OR REPLACE VIEW testdatabase.testtable

AS

SELECT
*
FROM location '/mnt/mylocation'

Can someone let me know where I'm going wrong?

CodePudding user response：

I don't believe VIEWS support specifying a Data Source in the SELECT query.

CREATE VIEW Spark 3.0.1 Doc

I believe only TABLES support the location keyword. Keep in mind that creating a Hive "Table" is just a pointer to the files when you specify the location path.

Changing this to a CREATE TABLE statement and specifying the schema should work.

CREATE TABLE testdatabase.testtable (
 col1 INT,
 col2 STRING,
 ...
)
USING PARQUET
LOCATION '/mnt/mylocation'

If you just want something ephemeral, you might read this in as a DataFrame and then register a temp table.

df = spark.read.parquet('/mnt/mylocation')
df.createOrReplaceTempView('testtable')

Now you can use default.testtable in SQL cells.

CodePudding user response：

You can do that, but you need to change syntax - instead of location use format (it's described in documentation):

<file_format>.`<location>`

For example:

%sql
CREATE OR REPLACE VIEW testdatabase.testtable
AS SELECT *
FROM delta.`/mnt/mylocation`