Export data from DynamoDB

Question

Is it possible to export data from DynamoDB table in some format?

The concrete use case is that I want to export data from my production dynamodb database and import that data into my local dynamodb instance so my application can work with local copy of data instead of production data.

I use the standard DynamoDB-local as a local instance of DynamoDB.

Answer 1

This will export all items as jsons documents

aws dynamodb scan --table-name TABLE_NAME > export.json

This script will read from remote dynamodb table and import into the local the full table.

TABLE=YOURTABLE
maxItems=25
index=0

DATA=$(aws dynamodb scan --table-name $TABLE --max-items $maxItems)
((index+=1)) 
echo $DATA | jq ".Items | {\"$TABLE\": [{\"PutRequest\": { \"Item\": .[]}}]}" > inserts.jsons
aws dynamodb batch-write-item --request-items file://inserts.jsons --endpoint-url http://localhost:8000


nextToken=$(echo $DATA | jq '.NextToken')
while [[ "${nextToken}" != "" ]]
do
  DATA=$(aws dynamodb scan --table-name $TABLE --max-items $maxItems --starting-token $nextToken)
  ((index+=1))
  echo $DATA | jq ".Items | {\"$TABLE\": [{\"PutRequest\": { \"Item\": .[]}}]}" > inserts.jsons
  aws dynamodb batch-write-item --request-items file://inserts.jsons --endpoint-url http://localhost:8000
  nextToken=$(echo $DATA | jq '.NextToken')
done

Here are a version of the script using files to keep the exported data on disk.

TABLE=YOURTABLE
maxItems=25
index=0
DATA=$(aws dynamodb scan --table-name $TABLE --max-items $maxItems)
((index+=1))
echo $DATA | cat > "$TABLE-$index.json"

nextToken=$(echo $DATA | jq '.NextToken')
while [[ "${nextToken}" != "" ]]
do
  DATA=$(aws dynamodb scan --table-name $TABLE --max-items $maxItems --starting-token $nextToken)
  ((index+=1))
  echo $DATA | cat > "$TABLE-$index.json"
  nextToken=$(echo $DATA | jq '.NextToken')
done

for x in `ls *$TABLE*.json`; do
  cat $x | jq ".Items | {\"$TABLE\": [{\"PutRequest\": { \"Item\": .[]}}]}" > inserts.jsons
  aws dynamodb batch-write-item --request-items file://inserts.jsons --endpoint-url http://localhost:8000
done

Answer 2

There is a tool named DynamoDBtoCSV

that can be used for export all the data to a CSV file. However, for the other way around you will have to build your own tool. My suggestion is that you add this functionality to the tool, and contribuite it to the Git repository.

---

Another way is use AWS Data Pipeline for this task (you will save all the costs of reading the data from outside AWS infraestructure). The approach is similar:

1. Build the pipeline for output
2. Download the file.
3. Parse it with a custom reader.

Answer 3

Here is a way to export some datas (oftentime we just want to get a sample of our prod data locally) from a table using aws cli and jq. Let's assume we have a prod table called unsurprisingly my-prod-table and a local table called my-local-table

To export the data run the following:

aws dynamodb scan --table-name my-prod-table \
| jq '{"my-local-table": [.Items[] | {PutRequest: {Item: .}}]}' > data.json

Basically what happens is that we scan our prod table, transform the output of the scan to shape into the format of the batchWriteItem and dump the result into a file.

To import the data in your local table run:

aws dynamodb batch-write-item \
--request-items file://data.json \
--endpoint-url http://localhost:8000

Note: There are some restriction with the batch-write-item request - The BatchWriteItem operation can contain up to 25 individual PutItem and DeleteItem requests and can write up to 16 MB of data. (The maximum size of an individual item is 400 KB.).

Answer 4

Export it from the DynamoDB interface to S3.

Then convert it to Json using sed:

sed -e 's/$/}/' -e $'s/\x02/,"/g' -e $'s/\x03/":/g' -e 's/^/{"/' <exported_table> > <exported_table>.json

Source

Answer 5

I extend Valy dia solution to allow all the process of exporting with only aws-cli | jq

aws dynamodb scan --max-items 25 --table-name <TABLE_NAME> \
| jq '{"<TABLE_NAME>": [.Items[] | {PutRequest: {Item: .}}]}' > data.json

aws dynamodb describe-table --table-name <TABLE_NAME> > describe.json | jq ' .Table | {"TableName": .TableName, "KeySchema": .KeySchema, "AttributeDefinitions": .AttributeDefinitions,  "ProvisionedThroughput": {
      "ReadCapacityUnits": 5,
      "WriteCapacityUnits": 5
}}' > table-definition.json

aws dynamodb create-table --cli-input-json file://table-definition.json  --endpoint-url http://localhost:8000 --region us-east-1

aws dynamodb batch-write-item --request-items file://data.json --endpoint-url http://localhost:8000

aws dynamodb scan --table-name <TABLE_NAME> --endpoint-url http://localhost:8000

Answer 6

I think my answer is more similar to Ivailo Bardarov , if planning to run this from linux instance run this

1.Login to your AWS account and go to IAM to create a user with limited policy for a role(for security purpose of course). This should be only limited to read dynamodb table that you would like to backup.

2.Copy the access key and secret and update below command to run it on Linux (but make sure your table is not huge and possibly creating a space issue for the box you are running this on)

AWS_ACCESS_KEY_ID='put_your_key' AWS_SECRET_ACCESS_KEY='put_your_secret' aws --region='put_your_region' dynamodb scan --table-name 'your_table_name'>> export_$(date "+%F-%T").json

Note similar command can be executed on Windows/Powershell I have not tested so I'm not adding it here.

Answer 7

Try my simple node.js script dynamo-archive. It exports and imports in JSON format.

Answer 8

I found the best current tool for simple import/exports (including round-tripping through DynamoDB Local) is this Python script:

https://github.com/bchew/dynamodump

This script supports schema export/import as well as data import/export. It also uses the batch APIs for efficient operations.

I have used it successfully to take data from a DynamoDB table to DynamoDB local for development purposes and it worked pretty well for my needs.

Answer 9

Expanding on @Ivailo Bardarov's answer I wrote the following script duplicate tables that are in a remote DynamoDB to a local one:

#!/bin/bash
declare -a arr=("table1" "table2" "table3" "table4")
for i in "${arr[@]}"
do
    TABLE=$i
    maxItems=25
    index=0
    echo "Getting table description of $TABLE from remote database..."
    aws dynamodb describe-table --table-name $TABLE > table-description.json
    echo
    echo "Creating table $TABLE in the local database..."
    ATTRIBUTE_DEFINITIONS=$(jq .Table.AttributeDefinitions table-description.json)
    KEY_SCHEMA=$(jq .Table.KeySchema table-description.json)
    BILLING_MODE=$(jq .Table.BillingModeSummary.BillingMode table-description.json)
    READ_CAPACITY_UNITS=$(jq .Table.ProvisionedThroughput.ReadCapacityUnits table-description.json)
    WRITE_CAPACITY_UNITS=$(jq .Table.ProvisionedThroughput.WriteCapacityUnits table-description.json)
    TABLE_DEFINITION=""

    if [[ "$READ_CAPACITY_UNITS" > 0 && "$WRITE_CAPACITY_UNITS" > 0 ]]
    then
    TABLE_DEFINITION="{\"AttributeDefinitions\":$ATTRIBUTE_DEFINITIONS,\"TableName\":\"$TABLE\",\"KeySchema\":$KEY_SCHEMA,\"ProvisionedThroughput\":{\"ReadCapacityUnits\":$READ_CAPACITY_UNITS,\"WriteCapacityUnits\":$WRITE_CAPACITY_UNITS}}"
    else
    TABLE_DEFINITION="{\"AttributeDefinitions\":$ATTRIBUTE_DEFINITIONS,\"TableName\":\"$TABLE\",\"KeySchema\":$KEY_SCHEMA,\"BillingMode\":$BILLING_MODE}"
    fi

    echo $TABLE_DEFINITION > create-table.json
    aws dynamodb create-table --cli-input-json file://create-table.json --endpoint-url http://localhost:8000
    echo "Querying table $TABLE from remote..."
    DATA=$(aws dynamodb scan --table-name $TABLE --max-items $maxItems)
    ((index+=1))
    echo "Saving remote table [$TABLE] contents to inserts.json file..."
    echo $DATA | jq ".Items | {\"$TABLE\": [{\"PutRequest\": { \"Item\": .[]}}]}" > inserts.json
    echo "Inserting rows to $TABLE in local database..."
    aws dynamodb batch-write-item --request-items file://inserts.json --endpoint-url http://localhost:8000

    nextToken=$(echo $DATA | jq '.NextToken')        
    while [[ "$nextToken" != "" && "$nextToken" != "null" ]]
    do
      echo "Querying table $TABLE from remote..."
      DATA=$(aws dynamodb scan --table-name $TABLE --max-items $maxItems --starting-token $nextToken)
      ((index+=1))
      echo "Saving remote table [$TABLE] contents to inserts.json file..."
      echo $DATA | jq ".Items | {\"$TABLE\": [{\"PutRequest\": { \"Item\": .[]}}]}" > inserts.json
      echo "Inserting rows to $TABLE in local database..."
      aws dynamodb batch-write-item --request-items file://inserts.json --endpoint-url http://localhost:8000
      nextToken=$(echo "$DATA" | jq '.NextToken')
    done
done

echo "Deleting temporary files..."
rm -f table-description.json
rm -f create-table.json
rm -f inserts.json

echo "Database sync complete!"

This script loops over the string array and for each table name it first gets the description of the table and builds a create JSON file with the minimum required parameters and creates the table. Then it uses rest of the @Ivailo Bardarov's logic to generate inserts and pushes them to the created table. Finally it cleans up the generated JSON files.

Keep in mind, my purpose was to just create a rough duplicate (hence the minimum required parameters) of tables for development purposes.

Answer 10

For those of you that would rather do this using java, there is DynamodbToCSV4j.

JSONObject config = new JSONObject();
config.put("accessKeyId","REPLACE");
config.put("secretAccessKey","REPLACE");
config.put("region","eu-west-1");
config.put("tableName","testtable");
d2csv d = new d2csv(config);

Answer 11

I have created a utility class to help developers with export. This can be used if you don't want to use data-pipeline feature of AWS. Link to git hub repo is -here

Answer 12

Export the dynamoDb data to a json file in your local using AWS CLI. Below is the example with the filters:

aws dynamodb scan --table-name activities --filter-expression "Flag = :val" --expression-attribute-values "{\":val\": {\"S\": \"F\"}}" --select "SPECIFIC_ATTRIBUTES" --projection-expression "Status" > activitiesRecords.json

Answer 13

Based on @IvailoBardarov's answer combined with @JorgeTovar's answer:

A shell script that fetches from remote DynamoDB and import to local DynamoDB

Extra features:

• create the local table!
• support 'AWS NoSql Workbench'

# REMOTE - SOURCE OF DATA
TABLE=<TABLE_NAME>
PROFILE=<AWS_PROFILE>
REGION=us-east-1

# LOCAL - TARGET OF DATA
export AWS_ACCESS_KEY_ID=d7ac2v # Align the credentials with those that appears in NoSql Workbench
export AWS_SECRET_ACCESS_KEY=4d0x6t

maxItems=25
index=0

# Create Local Table
aws dynamodb --profile $PROFILE --region $REGION describe-table --table-name $TABLE | jq ' .Table | {"TableName": .TableName, "KeySchema": .KeySchema, "AttributeDefinitions": .AttributeDefinitions,  "GlobalSecondaryIndexes": .GlobalSecondaryIndexes | map({"IndexName": .IndexName, "KeySchema": .KeySchema, "Projection": .Projection, "ProvisionedThroughput": {"ReadCapacityUnits": 5, "WriteCapacityUnits": 5}}), "ProvisionedThroughput": {"ReadCapacityUnits": 5, "WriteCapacityUnits": 5}}' > table-definition.json
aws dynamodb create-table --cli-input-json file://table-definition.json  --endpoint-url http://localhost:8000 --region localhost

# Import data 
DATA=$(aws dynamodb --profile $PROFILE --region $REGION scan --table-name $TABLE --max-items $maxItems)
((index+=1)) 
echo $DATA | jq ".Items | {\"$TABLE\": [{\"PutRequest\": { \"Item\": .[]}}]}" > inserts.jsons
aws dynamodb batch-write-item --request-items file://inserts.jsons --endpoint-url http://localhost:8000 --region localhost


nextToken=$(echo $DATA | jq '.NextToken')
while [[ "${nextToken}" != "" ]]
do
  DATA=$(aws dynamodb scan --profile $PROFILE --region $REGION --table-name $TABLE --max-items $maxItems --starting-token $nextToken)
  ((index+=1))
  echo $DATA | jq ".Items | {\"$TABLE\": [{\"PutRequest\": { \"Item\": .[]}}]}" > inserts.jsons
  aws dynamodb batch-write-item --request-items file://inserts.jsons --endpoint-url http://localhost:8000 --region localhost
  nextToken=$(echo $DATA | jq '.NextToken')
done

Answer 14

DynamoDB now has a native Export to S3 feature (in JSON and Amazon Ion formats) https://aws.amazon.com/blogs/aws/new-export-amazon-dynamodb-table-data-to-data-lake-amazon-s3/

Answer 15

You can try this code locally. But first the following command should be executed npm init -y && npm install aws-sdk

const AWS = require('aws-sdk');
AWS.config.update({region:'eu-central-1'}); 
const fs = require('fs');
const TABLE_NAME = "YOURTABLENAME"

const docClient = new AWS.DynamoDB.DocumentClient({
    "sslEnabled": false,
    "paramValidation": false,
    "convertResponseTypes": false,
    "convertEmptyValues": true
});

async function exportDB(){
    let params = {
        TableName: TABLE_NAME
    };
    let result = [];
    let items;
    do  {
        items =  await docClient.scan(params).promise();
        items.Items.forEach((item) => result.push(item));
        params.ExclusiveStartKey  = items.LastEvaluatedKey;
    }   while(typeof items.LastEvaluatedKey != "undefined");

    await fs.writeFileSync("exported_data.json", JSON.stringify(result,null, 4)); 
    console.info("Available count size:", result.length);
}
exportDB();

And run node index.js

I hope it works for you

Answer 16

You an use dynoport https://www.npmjs.com/package/dynoport it will do it for you at a very high performant way exports and imports

Answer 17

Dynamo DB now provides a way to export and import data to/from S3 http://aws.amazon.com/about-aws/whats-new/2014/03/06/announcing-dynamodb-cross-region-export-import/

Answer 18

In a similar use-case, I have used DynamoDB Streams to trigger AWS Lambda which basically wrote to my DW instance. You could probably write your Lambda to write each of the table changes to a table in your non-production account. This way your Devo table would remain quite close to Prod as well.

Answer 19

I used the awesome cyberchef site... https://gchq.github.io/CyberChef

With the csv to json tool.

Answer 20

For really big datasets, running a continuous (and parallel) scan might be time consuming and fragile process (imagine it dying in the middle). Fortunately, AWS recently added an ability to export your DynamoDB table data straight to S3. This is probably the easiest way to achieve what you wanted because it does not require you to write any code and run any task/script because it's fully managed.

After it's done, you can download it from S3 and import to the local DynamoDB instance using logic like foreach record in file: documentClient.putItem or use some other tooling.

Answer 21

In DynamoDB web console select your table, than Actions -> Export/Import