Writing SQL Query Lookup Activity in Synapse Pipeline Returns Error Message

user_17441242 0
Viewed 178

When I try to create a view in Synapse using Lookup activity, it returns me an error.

The code I have is:

CREATE OR ALTER VIEW view_name AS 
SELECT * FROM OPENROWSET(BULK 'https://datalake/container/Delta folder/', FORMAT='DELTA') AS result; 
SELECT 1

I'm receiving error message as:

A database operation failed with the following error: 'Incorrect syntax near the keyword 'SELECT'.' Incorrect syntax near the keyword 'SELECT'.

The problem is I just need to create a view on top of delta files so it can be accessed by my SSMS and Power BI. (It seems right now if I just create table/views in Spark DB on Delta files, SQL on-demand can't query them.) That's why I'm using a lookup activity to execute the SQL Query.

Any idea why this is happening?

azure-synapse azure-data-factory-2
2 Answers

I tried adding a GO statement after, as well as switching the order of the SELECT and CREATE statements (stupid attempt anyway), but the solution that works is to wrap the CREATE statement with '', using EXEC to execute it, and then doing the SELECT 1.

DECLARE @SQLQUERY NVARCHAR(MAX)
SET @SQLQUERY = 

CONCAT('CREATE OR ALTER VIEW <view_name> AS
SELECT
    *
FROM
    OPENROWSET(
        BULK ''https://','@{pipeline().parameters.DataLakeAccountName}','.dfs.core.windows.net/<folder_location>/'',
        FORMAT=''DELTA''
    ) AS [result]')

EXEC(@SQLQUERY)

SELECT 1

Note that I do have a pipeline parameter to set the datalake instance name, and that's why I'm using a parameter value inside my dynamic query generation piece.

user_174412420

Try

CREATE OR ALTER VIEW view_name AS SELECT * FROM OPENROWSET(BULK 'https://datalake/container/Delta folder/', FORMAT='DELTA') AS result

GO

SELECT 1

I’m not really sure why you need the SELECT 1 part but that should work.

user_50704400
Related