如何在ADF中将查询输出加载至Blob容器内的.csv文件

Hey there! Let me walk you through exactly how to export your query results to a CSV file in an Azure Blob Storage container using Azure Data Factory (ADF). It’s a common use case, and the process is pretty straightforward once you break it down:

First, you need to link your data source and Blob storage to ADF:

• Data Source Linked Service: Go to your ADF workspace → Manage tab → Linked Services → New. Pick the type of your data source (e.g., Azure SQL Database, SQL Server, PostgreSQL) and fill in the connection details (server name, database, credentials). Don’t forget to test the connection to make sure it works.
• Blob Storage Linked Service: Create another linked service, select Azure Blob Storage. You can authenticate using an account key, managed identity, or SAS token. Again, test the connection to confirm it’s linked properly.

Datasets define the structure of your source data and target CSV:

• Source Dataset: Go to Author tab → Datasets → New. Select your data source linked service. If you want to use a custom query (instead of pulling an entire table), switch to the Connection tab, choose "Query" instead of "Table", and paste your SQL statement here. Save the dataset.
• Target CSV Dataset: Create another dataset, select your Blob storage linked service. Set the container and folder path where you want the CSV to live. For the filename, you can use a static name (e.g., output.csv) or dynamic content to avoid overwriting files, like:

  @concat('query_results_', utcnow('yyyyMMddHHmmss'), '.csv')
  

  Then go to the Settings tab, set the format to CSV, and configure options like delimiter (comma is default), whether to include headers, encoding, etc. Save this dataset too.

Now put it all together with a Copy Data activity:

1. Go to Author tab → Pipelines → New pipeline.
2. Drag a Copy Data activity from the Activities pane into the pipeline canvas.
3. Configure the Source tab: Select your source dataset. If you set a custom query in the dataset, it’ll show up here — you can adjust it if needed.
4. Configure the Sink tab: Select your target CSV dataset. Choose the write behavior (e.g., "Overwrite" if you want to replace existing files, "Append" if you’re adding to a file).
5. Check the Mapping tab: ADF will auto-map columns between your source query and the CSV. Double-check to make sure all columns are correctly mapped; you can add/remove or adjust mappings if needed.
6. Optional: Head to the Settings tab to tweak performance (like parallel copy count) or error handling (e.g., skip invalid rows).

• Click the Debug button at the top to run the pipeline manually. Once it succeeds, go to your Blob storage container — you should see the CSV file with your query results.
• If everything works as expected, publish your pipeline. You can also set up a trigger (schedule, tumbling window, or event-based) to run it automatically on a regular basis.

A couple of quick tips:

• If your query returns a large dataset, consider enabling staging in the Copy Data activity to improve performance.
• Use dynamic content for filenames to keep each run’s output unique — this prevents overwriting previous results.

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