50

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.

RichVel
  • 7,030
  • 6
  • 32
  • 48
Vibhor Nigam
  • 732
  • 1
  • 5
  • 12

5 Answers5

83

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.

jarmod
  • 71,565
  • 16
  • 115
  • 122
  • 1
    All items have same attributes, but data types are different. Will this query work with different data types ? – Vibhor Nigam Oct 27 '15 at 12:22
  • 2
    Just supply the appropriate data types in that case, for example: name.S, zipcode.N, isawesome.B. – jarmod Oct 27 '15 at 13:42
  • 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 Mar 27 '18 at 11:52
  • @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. – jarmod Mar 27 '18 at 14:12
  • @jarmod how can I limit the number of entries? Can you share any documentation that might come handy to me? – n0obcoder Apr 21 '21 at 12:53
  • 1
    @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](https://docs.aws.amazon.com/cli/latest/reference/dynamodb/scan.html) docs. – jarmod Apr 21 '21 at 13:01
  • Do not work when some cols are missing in some items – Stéphane M Jul 17 '23 at 10:42
  • @StéphaneM correct and the answer does state that upfront. – jarmod Jul 17 '23 at 13:14
22

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
ronchu
  • 271
  • 3
  • 3
  • 1
    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 – Blkc Sep 14 '20 at 16:03
  • 1
    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? – fig Jan 19 '21 at 16:15
  • It works out of the box for me, only had to change the table name. It should be the top answer. – Hasan Sep 14 '21 at 16:05
  • One of my fields could contain Persian characters, and the whole column called 'error' was skipped because of it. – Houman Feb 13 '22 at 22:03
  • 1
    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' – Marcin Rogacki Jun 14 '22 at 12:34
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.

Pardeep Dhingra
  • 3,916
  • 7
  • 30
  • 56
Yanish Pradhananga
  • 717
  • 8
  • 9
  • 1
    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. – Jim Jarrett Mar 24 '23 at 12:55
8

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
Kishan B
  • 4,731
  • 1
  • 19
  • 11
0

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

Fauzan Azhari
  • 1
  • 1