Azure Synapse 无服务器删除由 CREATE EXTERNAL TABLE AS SELECT 语句创建的文件

Question

我使用的 Synapse Serverless 不支持物化 View ，但出于性能原因，某些用例需要它们。作为一种解决方法，我将使用 CETAS 语句来保存文件并为其安排定期更新。但是，当我尝试删除并重新创建 CETAS 表时，我收到消息:

Cannot create external table. External table location already exists. Location provided: [location]

由 CETAS 语句创建文件后，我似乎无法使用另一个 CETAS 语句覆盖它们，并且我还没有找到用于修改突触内文件的任何其他选项。我知道我可以创建一个更大的工作流程，以便在重新运行 CETAS 之前从存储中删除，但我希望有某种方法可以将内容保留在 Synapse 中。

是否有某种方法可以从 Synapse 中删除/覆盖/修改由 CETAS 语句创建的文件？

Answer 1

正如您所发现的，删除表不会删除文件。文档中对此进行了概述(请参阅备注部分)...

https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-external-table-transact-sql?view=sql-server-ver15#remarks

这可能不适合您的要求，但为了克服这个问题，您可以删除该表并使用时间戳重新创建它。外部表名称保持不变，只是包含时间戳的文件，以便将其与以前的创建区分开来。

下面显示了一个示例，当然，它缺少一些上下文，但您应该了解总体思路...

DECLARE @CurrentTimeStamp varchar(100) = (SELECT REPLACE(REPLACE(REPLACE(CONVERT(varchar, CURRENT_TIMESTAMP, 126), '-', ''), 'T', '_'), ':', ''))

BEGIN TRY
    SET @Sql = '
        IF EXISTS (SELECT * FROM sys.external_tables WHERE name = ''' + @TableName + ''' AND schema_id = ' + CAST(@SchemaIdReporting as varchar(10)) + ')
        BEGIN
            DROP EXTERNAL TABLE [reporting].[' + @TableName + ']
        END         

        CREATE EXTERNAL TABLE [reporting].[' + @TableName + ']
        WITH (
            LOCATION = ''reporting/' + @TableName + '/' + @CurrentTimeStamp + ''',
            DATA_SOURCE = [Analytics],
            FILE_FORMAT = [Parquet]
        ) AS
        ' + @Sql
    
    EXEC (@Sql)
END TRY

BEGIN CATCH
    SET @ErrorSection = 'Part 1: SQL statement construction.'
    SELECT @ErrorMessage = @ErrorSection + ', ' + ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()

    PRINT @Sql

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH

...此外，当前不支持修改(更新/插入/删除)外部表中的数据，这是一个限制。可以在这里找到...

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-ver15&tabs=dedicated#limitations-and-restrictions

唯一需要考虑的是清理旧文件。我还没有这样做，但喜欢拥有所有旧数据快照的概念。我还不需要返回并访问其中的任何内容，但如果我需要的话，它就在那里。