51

I am new to AWS CLI and I am trying to export my DynamoDB table in CSV format so that I can import it directly into PostgreSQL. Is there a way to do that using AWS CLI?

I came across this command: aws dynamodb scan --table-name <table-name> - but this does not provide an option of a CSV export.

With this command, I can see the output in my terminal but I am not sure how to write it into a file.

Improve this question

6 Answers 6

Reset to default
87

If all items have the same attributes, e.g. id and name both of which are strings, then run:

aws dynamodb scan \
    --table-name mytable \
    --query "Items[*].[id.S,name.S]" \
    --output text

That would give tab-separated output. You can redirect this to file using > output.txt, and you could then easily convert tabs into commas for csv.

Note that you may need to paginate per the scan documentation:

If the total number of scanned items exceeds the maximum dataset size limit of 1 MB, the scan stops and results are returned to the user as a LastEvaluatedKey value to continue the scan in a subsequent operation. The results also include the number of items exceeding the limit. A scan can result in no table data meeting the filter criteria.

Another option is the DynamoDBtoCSV project at github.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

All items have same attributes, but data types are different. Will this query work with different data types ?
Just supply the appropriate data types in that case, for example: name.S, zipcode.N, isawesome.B.
is there a different way to achieve that? because my output is showed up in console and this takes alot of time for large data...
@Khan a web search will yield other options for exporting DynamoDB tables, but the key factor impacting performance will be the provisioned read capacity of the table (RCU). You may want to increase that temporarily, do the export, then dial it back down.
@n0obcoder the scan actually limits the number of returned results implicitly (I'll update the answer) but you can also supply --max-items N. See the aws dynamodb scan docs.
|
29

A better way to do a full export of all columns without listign out is at Dynamo db export to csv

basically 

aws dynamodb scan --table-name my-table --select ALL_ATTRIBUTES --page-size 500 --max-items 100000 --output json | jq -r '.Items' | jq -r '(.[0] | keys_unsorted) as $keys | $keys, map([.[ $keys[] ].S])[] | @csv' > export.my-table.csv
Improve this answer

5 Comments

Thanks, this is a great solution! Downside is that if the field is a Set it seems to be empty...in which case you would need to use the answer from above
The .S means this only works for string fields. Replacing that with .N works for number fields only. How can you make it work for strings and numbers at the same time?
It works out of the box for me, only had to change the table name. It should be the top answer.
One of my fields could contain Persian characters, and the whole column called 'error' was skipped because of it.
I improved jq command, should support all types: jq -r '.Items' | jq -r '(.[0] | keys_unsorted) as $keys | $keys, map([.[ $keys[] ] | to_entries[] | [.value][0]])[] | @csv'
19

For localhost dynamodb:

$aws dynamodb scan --table-name AOP --region us-east-1 --endpoint-url
http://localhost:8000 --output json > /home/ohelig/Desktop/a.json

For dynamodb:

$aws dynamodb scan --table-name AOP --region us-east-1 --output json > /home/ohelig/Desktop/a.json

Then Convert JSON to CSV or whatever.

I have modified above answer to make it clear.

Improve this answer

1 Comment

The question was to get a CSV from a DynamoDB table. There are many ways to dump DynamoDB tables, including local DynamoDB, but it's non-trivial to convert DynamoDB JSON to CSV.
9

You can use jq convert the json output given by aws cli to csv

aws dynamodb scan --table-name mytable --query "Items[*].[id.S,name.S]" --output json | jq -r '.[] | @csv' > dump.csv
Improve this answer

4 Comments

I get jq: error (at <stdin>:1811): array ([{"N":"1559...) is not valid in a csv row
I like this answer the best.
this won't work if you have a map type though
you mean map type of attribute?
1

You can use jq to convert json into csv

aws dynamodb query \
    --table-name <table-name> \
    --index-name <index-name> \
    --select SPECIFIC_ATTRIBUTES \
    --projection-expression "attributes1, attributes2,..." \
    --key-condition-expression "#index1 = :index1 AND #index2 = :index2" \
    --expression-attribute-names '{"#index1": "index1","#index2": "index2"}' \
    --expression-attribute-values '{":index1": {"S":"key1"},":index2": {"S":"key2"}}' \
    --output json | jq -r '.Items' | jq -r '(.[0] | keys_unsorted) as $keys | $keys, map([.[ $keys[] ][]?])[] | @csv' > output.csv

But be careful if the column data length is different it will produce wrong output

Improve this answer

1 Comment

Finally, an answer that works properly for different data types. The key portion is: jq -r '.Items' | jq -r '(.[0] | keys_unsorted) as $keys | $keys, map([.[ $keys[] ][]?])[] | @csv'
0

jq with aws dynamodb scan can serve.

Below command scans the audit_history table, fetches all attributes, where UPDATE_FIELD was FNAME

aws dynamodb scan \
              --table-name audit_history \
              --select ALL_ATTRIBUTES --page-size 500 \
              --max-items 100000   \
              --filter-expression "UPDATE_FIELD = :val" \
              --expression-attribute-values "{\":val\":{\"S\":\"FNAME\"}}" \
              --output json | jq -r '.Items' | jq -r '(.[0] | keys_unsorted) as $keys | $keys, map([.[ $keys[] ].S])[] | @csv' > export_results.csv
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.