MongoDB in Dataki: a CSV export, or a copy in BigQuery

02·Set it up

3 steps, about 5 minutes

1. 01

   Route one: export a collection to CSV

   CSV needs the fields named. Dot notation reaches into embedded documents:

   mongoexport --uri="mongodb+srv://user:password@cluster.example.net/shop" \
     --collection=orders --type=csv \
     --fields=_id,status,total,createdAt,customer.country \
     --out=orders.csv

   • Upload orders.csv at app.dataki.ai/connect, under files. Dataki loads it into a table and queries it with full SQL.
   • Dataki loads every CSV column as text and casts in SQL, so name the number and date columns in your first question and check the casts in the SQL it shows.
2. 02

   Route two: copy it into BigQuery

   In the Google Cloud console, open Dataflow › Create job from template and pick MongoDB to BigQuery. Give it the connection URI, the database, the collection and an output table such as your-project:mongo.orders.

   Set the userOption parameter to FLATTEN, which makes each top-level field a column. Left at NONE, each document lands as one JSON string in a source_data column.

   Each run appends to the table, so a second run doubles every document. To refresh the copy, empty the table first with TRUNCATE TABLE mongo.orders, then run the job again.

3. 03

   Connect the dataset in Dataki

   Open app.dataki.ai/connect and, under Linked Google Cloud Projects, choose Link New Google Project. Sign in with a Google account that can manage IAM in the project holding the data, and pick that project. Dataki creates a service account named Dataki in it with two roles, BigQuery Data Viewer and BigQuery Job User: it can read tables and run queries.

   The project's datasets then appear among your sources. Pick mongo. Each dataset is one source in Dataki, and the free plan includes one.