Migrate Dynamodb Data to CloudSQL

Database technology is matured now. Most of the activity is avalialble with commandline. Especially in cloud they are available with respective cli. Here we will export dynamodb data with aws cli & import it to cloudsql with gcloud cli.

DynamoDB to CloudSQL

Export DynamoDB Data

From Dynamodb table list out the field names. In case of dynamodb fileds names may not show on top as it is based on sampling done by aws. However you can always select required fields & flatten them to output as tsv.

aws dynamodb scan   \  
    --table-name Prod.Country     
    --query "Items[*].[CountryCode.S,CountryName.S,RegionCode.N,IsdCode.S]"  \ 
     --output text \
     --profile=aws-prod \ 
     --region=ap-southeast-1 > country_dynamodb.tsv

Connect CloudSQL with Commandline

Below command works for Both MySQL & MSSQL.

gcloud sql connect common-service-01  --project=myproject -u trust
Allowlisting your IP for incoming connection for 5 minutes...done.

Import tsv data to CloudSQL

Now as we are connected to cloudsql. import to tsv is simple command

load data local infile '~/country_dynamodb.tsv' into table country;

Disclaimer:

This is simplest method and works for small tables where data modification is low. If you need realtime data, you need to rely on dynamodb streams too.