CODEWITHSUNDEEP
databricksdatabricks-sql
Patterson
Patterson

Reputation: 2821

Databricks Merge destination only supports Delta Sources - Views cannot be converted to Delta Tables

I have created two Databricks Delta sources as follows using Databricks SQL as follows:

I am attempting to carry out a merge as follows:

CREATE OR REPLACE TEMP VIEW rs AS
SELECT *, ROW_NUMBER() OVER (ORDER BY rand()) AS seq
FROM basef1area.drivers;

MERGE INTO basef1area.races AS t
USING rs
ON t.race_ID = rs.seq
WHEN MATCHED THEN
  UPDATE SET t.reject_reason = rs.driver_ID;

When I attempt the merge I get the following error:

Error in SQL statement: AnalysisException: MERGE destination only supports Delta sources.

I have attempted to fix this issue by trying to convert the table to delta with the following:

CONVERT TO DELTA basef1area.races

However, I get the error:

Error in SQL statement: AnalysisException: Operation not allowed: `Converting a view to a Delta table` is not supported for Delta tables:basef1area.races

Therefore, can someone let me know how go about converting a view to a table and then to a Delta Table?

I tried the following solution to create a table from a view as follows:

CREATE TABLE basef1area.races_delta AS
SELECT *
FROM basef1area.races;

And then attempt to insert the data from the view into the newly created Delta table as follows:

INSERT INTO basef1area.races_delta
SELECT *
FROM basef1area.races;

But when I execute:

CREATE TABLE basef1area.races_delta AS
SELECT *
FROM basef1area.races;

I get the message 'Query returned no results'.

However, there definitely is data in basef1area.races;

Any thoughts on where creating the table is not returning any results?

Upvotes: 0

Views: 235

Answers (1)

Chen Hirsh
Chen Hirsh

Reputation: 1400

Running the query:

CREATE TABLE basef1area.races_delta AS
SELECT *
FROM basef1area.races;

Should return no results, since this query creates a table and doesn't return data.

You can check the type of created table by using:

describe extended basef1area.races_delta

where (after the column list) you should see "Provider = delta".

At this point, your merge should work into basef1area.races_delta.

Upvotes: 1

Related Questions