Migrate Data from MongoDB to Snowflake

Migrate Data from Redshift to Snowflake
March 2, 2018
Integrating Spark with Snowflake
March 8, 2018

Migrate Data from MongoDB to Snowflake

How To: Migrate Data from MongoDB into Snowflake

Unload data to a local filesystem using the mongoexport command. The file format for MongoDB data is NDJSON. Each row of data conforms to the JSON RFC but contains MongoDB Extended JSON. Since the output format is NDJSON, the output from mongoexport can be sent to tools like split(1) to make files of a given size, etc.

Example

Unload data from MongoDB to the local filesystem using the mongoexport command:
mongoexport --db twitter --collection tweets --out tweets.json

Create a table in Snowflake named mongo_import with a single VARIANT column:
create or replace table mongo_import(v variant);

Stage the tweets.json file from the local tmp directory to a mongo_import table staging location using the PUT command using SnowSQL:
PUT file:///tmp/tweets.json @%mongo_import;

Note: By default, the PUT command compresses files using gzip.

Load the data from the table staging location into the mongo_import table:
copy into mongo_import
from @%mongo_import/tweets.json.gz
file_format = (type = json);

Note that instances of MongoDB Extended JSON are identified by $. These extended keys are valid JSON.

For example:
"created_at": {"$date": "2015-06-20T00:33:32.000Z"},
"id": {"$numberLong": "2853454624"},

Relevant Documentation

https://docs.snowflake.net/manuals/user-guide/data-load.html
https://docs.snowflake.net/manuals/sql-reference/sql/put.html
https://docs.snowflake.net/manuals/sql-reference/sql/copy-into-table.html

Find out more about all the benefits Snowflake has to offer for you and your business. Enter your name and email below and we'll be in touch!

Leave a Reply

Your email address will not be published. Required fields are marked *