Synapse中CETAS生成CSV无表头，求TSQL/Synapse解决方案

方案1：联合表头行与数据行（纯TSQL实现） #

通过构造包含表头的联合查询，将列名作为第一行导出，需注意把所有数据列转换为字符串类型以匹配表头的类型。

固定列场景示例代码 #

CREATE EXTERNAL TABLE population_by_year_state_with_header
WITH (
    LOCATION = 'aggregated_data_with_header/',
    DATA_SOURCE = population_ds,
    FILE_FORMAT = census_file_format -- 确保该文件格式为CSV，配置FIELD_TERMINATOR = ',', STRING_DELIMITER = '"'等参数
)
AS
-- 表头行
SELECT 'decennialTime' AS decennialTime, 'stateName' AS stateName, 'population' AS population
UNION ALL
-- 数据行，所有列转换为字符串类型
SELECT 
    CAST(decennialTime AS VARCHAR(50)), 
    CAST(stateName AS VARCHAR(100)), 
    CAST(SUM(population) AS VARCHAR(20))
FROM
    OPENROWSET(BULK 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/release/us_population_county/year=*/*.parquet',
    FORMAT='PARQUET') AS [r]
GROUP BY decennialTime, stateName;
GO

动态适配列场景示例代码 #

适合列较多或需自动适配结果集的情况：

DECLARE @columns_list NVARCHAR(MAX);
DECLARE @casted_columns NVARCHAR(MAX);
DECLARE @sql_script NVARCHAR(MAX);

-- 获取目标列名（替换为你的数据源元数据来源，这里以外部表为例）
SELECT 
    @columns_list = STRING_AGG('''' + column_name + ''' AS ' + QUOTENAME(column_name), ', '),
    @casted_columns = STRING_AGG('CAST(' + QUOTENAME(column_name) + ' AS VARCHAR(MAX)) AS ' + QUOTENAME(column_name), ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID('your_existing_external_table'); -- 替换为你的外部表名

-- 构造CETAS语句
SET @sql_script = N'
CREATE EXTERNAL TABLE output_with_header
WITH (
    LOCATION = ''dynamic_aggregated_data/'',
    DATA_SOURCE = population_ds,
    FILE_FORMAT = census_file_format
)
AS
SELECT ' + @columns_list + '
UNION ALL
SELECT ' + @casted_columns + '
FROM your_existing_external_table; -- 替换为你的数据源查询
';

EXEC sp_executesql @sql_script;
GO

方案2：生成独立表头文件后合并（Synapse管道+TSQL） #

先用CETAS分别导出表头和数据，再通过Synapse数据管道合并两个文件：

1. 导出表头文件：用CETAS导出仅包含列名的单行数据

CREATE EXTERNAL TABLE csv_header
WITH (
    LOCATION = 'aggregated_data/header/',
    DATA_SOURCE = population_ds,
    FILE_FORMAT = census_file_format
)
AS
SELECT 'decennialTime,stateName,population' AS header_row;
GO

2. 导出数据文件：执行原有的CETAS查询生成数据
3. 合并文件：在Synapse Studio中创建数据管道，使用复制活动将表头文件和数据文件合并为单个带表头的CSV，或用Get Metadata + ForEach活动批量合并多份数据文件与表头。

方案3：使用Synapse Spark池导出（支持复杂场景） #

Spark导出CSV原生支持包含表头，适合处理JSON数据、复杂转换等场景：

1. 在Synapse Studio中创建Spark notebook，使用以下代码读取数据并导出：

// 读取JSON数据示例
val jsonDf = spark.read.json("abfss://<container>@<storage-account>.dfs.core.windows.net/path/to/json/files/")

// 读取外部表数据示例
val externalTableDf = spark.read.table("your_external_table_name")

// 执行聚合或转换逻辑
val resultDf = externalTableDf.groupBy("decennialTime", "stateName").sum("population").withColumnRenamed("sum(population)", "population")

// 导出带表头的CSV
resultDf.write
  .option("header", "true")
  .mode("overwrite")
  .csv("abfss://<container>@<storage-account>.dfs.core.windows.net/aggregated_data_with_header/")

2. 若需用TSQL触发，可使用sp_spark_job存储过程提交Spark作业。

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