Creating column headers in CSV/TSV files using AWS Data Pipeline?

Question

I'm creating CSV & TSV files using AWS Data Pipeline. The files are creating just fine, but I can't figure out how to create files with column headers.

At first, I expected the headers to generate automatically based on the SQL query I'm running to get the export. That didn't work, but was ok.

Then I added a list of column definitions to the "column" attribute of the DefaultDataFormat3 feature/node.

{
  "escapeChar": "\\",
  "name": "DefaultDataFormat3",
  "column": [
    "id INT",
    "field1 STRING",
    "field2 STRING"
  ],
  "columnSeparator": "|",
  "id": "DataFormatId_jEXqL",
  "type": "TSV",
  "recordSeparator": "\\n"
}

I still just get CSVs and TSVs with no header row in the export.

AWS Datapipeline is very poorly documented. Most places it's difficult to understand what the field is supposed to do. — Anshul, Mar 14 '17 at 18:37

score 7 · Answer 1 · answered Aug 02 '17 at 22:46

7

I ran across a blog post explaining a solution for this. If you are using a query for your data you can add the column names as the first result:

SELECT 'firstName', 'lastName', 'email'
UNION ALL
SELECT firstName, lastName, email
FROM users

answered Aug 02 '17 at 22:46

MrHen

2,420
2
25
39

1

This is a most excellent solution. But, AWS should fix it too! : ) – T. Brian Jones Aug 03 '17 at 02:08
2

Agreed. One downside to this solution is that you have to convert everything to strings or SQL complains about the mismatched column types. – MrHen Aug 03 '17 at 15:02
Clever, much cleaner than adding another processing step. Thanks. – maniexx Nov 07 '18 at 14:22

Creating column headers in CSV/TSV files using AWS Data Pipeline?

1 Answers1