Azure Synapse无服务器SQL池：如何将CTE结果存储至外部表？

问题背景 #

你有一组链式CTE，希望将结果存储到Azure Data Lake Storage（ADLS）的外部表中，尝试的写法触发语法错误，且无法直接向外部表插入数据：

WITH CTE1 AS (
  <query logic>
), CTE2 AS (
  <query logic>
), CTE3 AS (
  <query logic>
)
CREATE EXTERNAL TABLE [tmp].[Table] WITH (
    LOCATION = 'TEMP/',
    DATA_SOURCE = [data_lake_external_data_source],
    FILE_FORMAT = [parquet_external_file_format]
) AS
SELECT * FROM CTE3

执行后报错：Incorrect syntax near the keyword 'CREATE'，同时无服务器SQL池不支持INSERT语句写入外部表。

可行解决方案 #

1. 将CTE嵌入CREATE EXTERNAL TABLE AS SELECT语句 #

无服务器SQL池不支持CTE后跟CREATE EXTERNAL TABLE，但可以把CTE直接嵌套在AS后的查询逻辑中，语法如下：

CREATE EXTERNAL TABLE [tmp].[Table] WITH (
    LOCATION = 'TEMP/',
    DATA_SOURCE = [data_lake_external_data_source],
    FILE_FORMAT = [parquet_external_file_format]
) AS
WITH CTE1 AS (
  <query logic>
), CTE2 AS (
  <query logic>
), CTE3 AS (
  <query logic>
)
SELECT * FROM CTE3

该写法会直接将CTE计算结果写入指定ADLS路径的外部表。

2. 使用本地临时表（仅当前会话有效） #

如果仅需临时存储结果供当前会话使用，可创建本地临时表：

WITH CTE1 AS (
  <query logic>
), CTE2 AS (
  <query logic>
), CTE3 AS (
  <query logic>
)
SELECT * INTO #TempTable FROM CTE3

本地临时表#TempTable仅在当前查询会话中存在，数据存储在Synapse临时存储中，无需关联ADLS。

3. 使用全局临时表（跨会话可见） #

若需让其他会话访问结果，可创建全局临时表：

WITH CTE1 AS (
  <query logic>
), CTE2 AS (
  <query logic>
), CTE3 AS (
  <query logic>
)
SELECT * INTO ##GlobalTempTable FROM CTE3

全局临时表##GlobalTempTable会在所有引用它的会话结束后自动清理。

4. 创建内部表（存储于Synapse专用存储） #

如果不需要将数据存到ADLS，可将结果写入Synapse内部表：

WITH CTE1 AS (
  <query logic>
), CTE2 AS (
  <query logic>
), CTE3 AS (
  <query logic>
)
CREATE TABLE [tmp].[InternalTable] AS SELECT * FROM CTE3

注意：无服务器SQL池的内部表数据存储在Synapse默认存储中，而非用户指定的ADLS。

内容的提问来源于stack exchange，提问作者lyubol