Skip to main content

Ingest manually uploaded CSV and TSV files into the Glue Catalog

This Lambda automatically creates or deletes AWS Glue Catalog tables whenever a user from an enabled department uploads or removes CSV or TSV files in the dataplatform-prod-user-uploads S3 bucket.

1. Prerequisites​

  • UTF-8 CSV or TSV files with a single header row at the top. Lowercase .tsv files use Tab. For .csv, the delimiter is detected as comma, Tab, or pipe (|).
  • The automation is currently enabled for Parking, Housing, Data and Insight, Child Fam Services, Environmental Services, and Revenues. If you need it enabled for another department, contact the Data Platform team.

2. Folder Structure & File Naming​

Upload files to your departmental prefix inside the production bucket:

s3://dataplatform-prod-user-uploads/<department>/<target_table_name>/<file_name>.csv
s3://dataplatform-prod-user-uploads/<department>/<target_table_name>/<file_name>.tsv
  • <department> identifies your team (e.g. parking, housing).
  • <target_table_name> is the folder immediately below the department and becomes the Glue table name (e.g. ringgo_permits).
  • <file_name> is ignored when generating the Glue table name. Every file in the same <target_table_name> folder contributes data to the same table.
  • Only lowercase .csv and .tsv files are processed.
  • Files in the same table folder must use the same extension and delimiter.

Table Names​

The table name is generated automatically as:

normalize(<target_table_name>)

Normalization replaces non-alphanumeric characters with underscores and collapses consecutive underscores into one.

Required Path​

The Lambda accepts exactly one table folder beneath the department:

<department>/<target_table_name>/<file_name>.csv
<department>/<target_table_name>/<file_name>.tsv

Files that do not match this path structure are rejected, an error is logged, and no Glue table is created. Other file types do not trigger this automation. For example:

parking/permits.csv                         # Missing target table folder
parking/ringgo/permits/permits.csv         # Too many folders
parking/ringgo_permits/permits.xlsx        # Unsupported and not processed

Notes​

  • Visibility: Every member of your department can currently see the uploaded files and Glue tables, not just those stored under a particular <target_table_name>.
  • Schema: All columns in the generated Glue tables are currently created as string types.
  • Table isolation: Each <target_table_name> folder is a separate table and has its own S3 location.
  • Parsing: TSV uses Tab directly; CSV delimiters are detected automatically. Double quotes can wrap fields containing the delimiter.
  • Multiple files: Files in one table folder must use the same extension, delimiter, header, and schema. Mixed formats are rejected.
  • Limitation: Quoted fields cannot contain line breaks.
  • Data format: Uploaded files are not converted to Parquet. Athena reads the original files directly from the user uploads bucket.

Using Parking as an Example​

Parking users can upload monthly files to the same table folder:

s3://dataplatform-prod-user-uploads/parking/ringgo_permits/january.csv
s3://dataplatform-prod-user-uploads/parking/ringgo_permits/february.csv

These two files jointly make up ringgo_permits; they do not create two tables. The table is created inside the parking_user_uploads_db Glue database and points to the parking/ringgo_permits/ S3 folder.

To create a separate table, use another table folder:

s3://dataplatform-prod-user-uploads/parking/ringgo_payments/payments.csv

This creates the separate ringgo_payments table.

3. Upload CSV or TSV files (Console)​

  1. Sign in to the AWS Console and open S3.
  2. Navigate to the dataplatform-prod-user-uploads bucket.
  3. Browse into your department folder (e.g. parking/).
  4. Inside the <department> folder, create a subfolder named after the target table (<target_table_name>), then open it.
  5. Click Upload → Add files and choose CSV or TSV files that use the same extension, delimiter, header, and schema.
  6. Leave the default permissions and encryption settings unchanged.
  7. Click Upload.

After the upload is complete, the automated processing takes less than a minute. When processing is complete, Parking users can query the table in Athena as:

parking_user_uploads_db.<target_table_name>

4. Delete CSV or TSV files (Console)​

Deleting a file removes the Glue table only when no other supported files remain in the same <target_table_name> folder.

  1. In S3, select the CSV or TSV file under <department>/<target_table_name>/.
  2. Choose Delete and confirm.
  3. If it was the final supported file in the folder, the table disappears from Glue/Athena within less than a minute. Otherwise, the table remains.