Allow users upload CSV files, and embed the data for a simple API

Question

Looking for the correct architecture, that should take into consideration - future edge-cases, bugs and pitfalls, performance, cloud pricing, ease of building and maintaining and security.

I have a serverless app, hosted at AWS. It uses Lambdas and several DynamoDB tables for most of the BE logic (managed with aws-amplify).

I want to add a feature where users can upload CSVs, see them as a table on the app, and create a simple public API to fetch one row, based on ID (no need for more complex queries). Structure of the CSV (columns) varies with each upload.

Each users will add about 0-10 CSVs, each CSV will contain 3-20 columns and around 1k-100k rows. Adding CSVs takes place once a month/week, reading a line with the API happens 10k-100k a day.

How should I build it? (not limited to Lambdas/DynamoDB)

---

The solutions I had in mind are:

1. Create a new table (sql/document) for each CSV upload, and save the name of the table under user.csvs[].

This way I'll have huge amount of tables. Is that a reasonable solution?

2. Add all CSV data to a document db, e.g. --

user { 
name: "john",
csvs: {
  csv123: {
    id345: {col1: 'x', col2: 'y'},
    id678: {...},
    ...
  }
 }
}

What should I index in this solution for best performance?

3. upload the file to a bucket, and create a lambda that opens it with every request, and returns the requested line. (this option skips DB indexing)

---

Hope to learn from your experience.

Answer 1

Okay so a CSV is a table where the column labels are unknown at design time. I don't think there is a firm spec for a CSV but let's assume it's comma separated and has the column lables in the first line.

first,last,age
john,doe,52
sue,mary,42

My MVP would be to use a single table / collection

userid fileid rowid content
100    1      1     first,last,age
100    1      2     john,doe,52
100    1      3     sue,mary,42

Querying a row costs two reads, row 1 and the row the user is looking for. At query time, build a response JSON from the column labels and values. Actually you haven't said what the output format was.

There is some in-memory string processing here but impact should be negligible.

If you have repeat access, you can write the response JSON or whichever response format you have chosen back to the DB and introduce a flag to indicate the content has been processed.

The key to this solution is to zone in on the constraint no queries are needed, just single row access. Thus we don't need any dynamic table magic, the database does not need to have knowledge of the structure of the data. We can process really small data sets in memory at query time. We let the database find the rows.

Answer 2

There's rarely, if ever, one correct solution when it comes to software solutions. I'm going to just provide some confirmation around one of your options and a few tips.

You can do this pretty easily with Dynamo and since you are already using it, it might be a good enough solution for your needs. It's resilient and if you design things to work with the way it's intended to be used, it will perform well. Probably one of the biggest advantages is that it's highly scalable so if your user base grows, you shouldn't need to re-design. In the meantime, you pay only for what you use. The biggest downside I find is that once you get past the free tier of storage, the cost per GB is not the cheapest. One table is the most cost effective solution here as there's a monthly overhead per table.

You ask with regard to this solution:

What should I index in this solution for best performance?

Simple: you should index things that you are querying on. You say prior:

... a simple public API to fetch one row, based on ID (no need for more complex queries).

This is one of the main reasons Dynamo could be a really good solution here, but I think you mean the user id, and not the CSV id. If you can make the CSV id your partition key, then you need no additional index. Querying by the partition key is the best solution in terms of performance and cost. This key needs to be unique across the entire table, however. Here is a document on choosing partition keys.

If you need to be able to pull CSVs by user, you might need to add a global secondary index (GSI.) Note that there's extra cost to creating and keeping an index, whether you use it.

As far as the structure you show, you seem to want to create one record per user and add all the CSVs to it. This is probably not the way I would go. I would probably instead create a record per CSV and never modify it. The only caveat is if all of your CSVs are really small (e.g. < 1Kb), then that could be inefficient from a cost perspective.

In the case that the id is the user's, I would probably use a well distributed random key as the partition key such as random UUID. Be careful with UUIDs (GUIDs) because most are not random. Use a 'version 4' UUID if you do use one. Another option is to create a composite partition key with the user as the partition key and doc id as sort key. This will put all the user's docs in the same partition. There are tradeoffs but this might work OK as long as the number of CVSs per user is reasonable and you aren't trying to access a lot of a single user's CSVs at the same time.

I think it's unlikely that a user is going to want to open all the CSVs they have uploaded at the same time. If you use the document Id as the partition key, I would create a GSI on the user id and project only the values from the record that the user would need to determine which they would like to view along with the partition key for each CSV. By limiting the projection you improve the cost effectiveness of the index. When the user selects the CSV they wish to view, you use the partition key to retrieve it. Note: you cannot index on values within maps or lists of your record in dynamo. This is important to consider when structuring your records. If you use a composite partition key, you shouldn't need this, because you can query on the partition key directly (i.e. the user id.)

Here's how I might structure each record, to start:

{ 
user: "john",
id: "csv123"
columns ['x', 'y'],
data [
  [100, 200],
  [200, 300],
  ...
],
}