Azure Synapse Failure

Question

使用下面的代码，我试图将数据从这个代码复制到一个专用的表。数据来自一个Parquet，我需要在将其加载到表中之前进行更改。我得到以下错误:

在目标上的操作Move InvItemDist to DP failed:失败发生在"源"端。ErrorCode = SqlOperationFailed '类型= Microsoft.DataTransfer.Common.Shared。HybridDeliveryException,Message=数据库操作失败，出现以下错误:'FORMAT'附近语法不正确'，Source=， " Type=System.Data.SqlClient. error "。SqlException,Message= 'FORMAT'附近语法错误。Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message= 'FORMAT'附近语法错误，}，]，'

代码:

SELECT 
A.[NDC]
, A.[ProductID]
, A.[Customer_Site]
, A.[ZipFileName]
FROM 
(
SELECT
DISTINCT
[NDC]
, [ProductID]
, [Customer_Site]
, LEFT
(
SUBSTRING([ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName]), 0) + 1), 0) + 1, LEN([ZipFileName]))
, LEN(SUBSTRING([ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName]), 0) + 1), 0) + 1, LEN([ZipFileName]))) - 4
) AS [ZileFileOrder]
, [ZipFileName]
, ROW_NUMBER() OVER (PARTITION BY [NDC], [ProductID], [Customer_Site] 
ORDER BY LEFT
(
SUBSTRING([ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName]), 0) + 1), 0) + 1, LEN([ZipFileName]))
, LEN(SUBSTRING([ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName], NULLIF(CHARINDEX('-', [ZipFileName]), 0) + 1), 0) + 1, LEN([ZipFileName]))) - 4
) DESC) AS UN
FROM
OPENROWSET(
BULK 'https://stonexiacctolleson3154ba.dfs.core.windows.net/fs-tolleson/InvItemDist - Fact/2022/06/15/*.parquet',
FORMAT = 'PARQUET'
) AS [result]
) AS A
WHERE A.[UN] = 1

Answer 1

OPENROWSET()目前在synapse专用池中不支持。

您可以从门户提出一个特性请求。

使用外部文件格式访问文件中的数据。您可以参考此Microsoft文档来创建外部文件格式以读取parquet文件