How to export a dynamodb table as a csv through aws-cli ( without using pipeline)

Question

I am new to aws-cli and I am trying to export my dynamodb table as a csv so that i can import it directly into postgresql. Is there a way to do that using aws-cli ?

So far i have came across this command aws dynamodb scan --table-name . But this does not provide an option of a csv export. Also, through this command I can get the output on my command prompt but I am not sure how to write it in a file.

Solution 1

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.

Solution 2

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.

Solution 3

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

Solution 4

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

View more solutions

Comments

[Vibhor Nigam]

All items have same attributes, but data types are different. Will this query work with different data types ?

[jarmod]

Just supply the appropriate data types in that case, for example: name.S, zipcode.N, isawesome.B.

[Khan]

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...

[jarmod]

@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.

[sja]

I get jq: error (at <stdin>:1811): array ([{"N":"1559...) is not valid in a csv row

[TryTryAgain]

I like this answer the best.

[Blkc]

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

[fig]

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?

[n0obcoder]

@jarmod how can I limit the number of entries? Can you share any documentation that might come handy to me?

[jarmod]

@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.

[Hasan]

It works out of the box for me, only had to change the table name. It should be the top answer.

[Houman]

One of my fields could contain Persian characters, and the whole column called 'error' was skipped because of it.

[Melih Yıldız']

this won't work if you have a map type though

[Marcin Rogacki]

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'