Examples

This section demonstrates a number of example use cases of the FaunaDB Data Manager:

Create a demonstration database
Test the source specification
Copy a FaunaDB database to a new FaunaDB database
Export a FaunaDB database to a directory
Import JSON or CSV documents into a FaunaDB database
Import records from a JDBC database
Export a FaunaDB database at a specific point in time
Apply a format transformation

In order to perform the following examples, you need the secret for an admin key for the database you wish to export data from, or import data into.

You can create an admin key for any database in your FaunaDB Console account:

Log in to FaunaDB Console.
Click on an existing database, or create a new database.
Click the Security button in the left navigation bar.
Click the New Key button.
Ensure that the Role field is set to Admin.
Enter a name for the key, e.g. FDM.
Click Save.
Copy the displayed secret, and make sure you can retrieve the secret later. The secret is only ever displayed once: if you lose the secret, you would have to delete the key and create a new one.

Later in these examples, replace <ADMIN_SECRET> with the value of the secret that you copied.

Before you get started, the FaunaDB Data Manager is a terminal application, so open a terminal window, and change the current directory to the one containing the fdm program.

Create a demonstration database

A tool such as the FaunaDB Data Manager needs a good way to test its operations without putting your own data at risk. The demo parameter makes it easy to create a database containing sample data. The demonstration database is created as a child database of the database associated with your admin key.

Run the following command:

./fdm -demo <ADMIN_SECRET>

Remember to replace <ADMIN_SECRET> with the secret for the database that should contain the demonstration database.

When you run that command, the output should resemble the following:

2020-04-08.22.14.25
===================

FDM   1.14
2020-04-09 04:19
Loaded property file path: /Users/eskwayrd/fdm-1.14/fdm.props
Connecting to: CLOUD
Connection was successful.
Connecting to: CLOUD
Connection was successful.
Database created.
Connecting to: CLOUD
Connection was successful.
Collection posts1 loaded with 101 docs
Collection posts2 loaded with 102 docs
Collection posts3 loaded with 103 docs
Collection posts4 loaded with 104 docs
Collection dept loaded with 4 docs
Collection employee loaded with 12 docs
Collection address loaded with 3 docs
Collections Created
Indexes Created
Functions Created
Roles Created
Database <fdm-demo> created with secret <fnADo-00000CAdAM1a-000000LJfYHJyrhP6wLzr>
Database <fdm-demo> created successfully.

The secret reported in the output is the one you should use to export data from, or import data into, the demonstration database. Remember to save this secret, as secrets are only ever provided once.

Test the source specification

The FaunaDB Data Manager provides a "dry run" capability where no documents are written to the Destination specification. This is useful when you want to verify that the Source specification is correct.

To use this capability, simply specify -dest dryrun:

./fdm -source key=<SOURCE SECRET> -dest dryrun

Replace <SOURCE SECRET> with the secret that belongs to the database that you wish to test.

When you run that command, the output should resemble the following:

2020-04-08.21.39.03
===================

FDM   1.14
2020-04-09 04:19
Loaded property file path: /Users/eskwayrd/fdm-1.14/fdm.props

Field Formatting
================
<<None>>

Config File
===========
   load:                      data.json
   host:                      cloud
   jdbc.property.serverTimezone:  UTC
   policy.data:               COPY

Connecting to: CLOUD
Connection was successful.
Execute a dry run, nothing will be inserted.
Starting Load at 2020-04-08 21:39:04.
Reading data at 2020-04-08 21:39:04  Fauna Timestamp: 1586407144944000
Collection address -> address documents 3 size 0 KB  execution time:    0.055 sec  0.000 MB/sec   54.5 docs/sec
Collection dept -> dept documents 4 size 0 KB  execution time:    0.050 sec  0.000 MB/sec   80.0 docs/sec
Collection employee -> employee documents 12 size 0 KB  execution time:    0.048 sec  0.000 MB/sec  250.0 docs/sec
Collection posts1 -> posts1 documents 101 size 0 KB  execution time:    0.231 sec  0.000 MB/sec  437.2 docs/sec
Collection posts2 -> posts2 documents 102 size 0 KB  execution time:    0.071 sec  0.000 MB/sec  1436.6 docs/sec
Collection posts3 -> posts3 documents 103 size 0 KB  execution time:    0.072 sec  0.000 MB/sec  1430.6 docs/sec
Collection posts4 -> posts4 documents 104 size 0 KB  execution time:    0.065 sec  0.000 MB/sec  1600.0 docs/sec
FDM finished at 2020-04-08 21:39:05.

Copy a FaunaDB database to a new FaunaDB database

The FaunaDB Data Manager can copy one FaunaDB database to another. To do so, you need a secret associated with each database. Follow the steps at the top of this page if you need to create a key for the databases involved in the copy.

Run the following command to copy one database to another:

./fdm -source key=<SOURCE SECRET> -dest <DESTINATION SECRET>

Replace <SOURCE SECRET> with the secret that belongs to the database that you wish to copy. Replace <DESTINATION SECRET> with the secret that belongs to the database that should receive the copy.

When you run that command, the output should resemble the following (especially if the <SOURCE SECRET> belongs to a demonstration database):

2020-04-08.22.17.52
===================

FDM   1.14
2020-04-09 04:19
Loaded property file path: /Users/eskwayrd/fdm-1.14/fdm.props

Field Formatting
================
<<None>>

Config File
===========
   load:                      data.json
   host:                      cloud
   jdbc.property.serverTimezone:  UTC
   policy.data:               COPY

Connecting to: CLOUD
Connection was successful.
Connecting to: CLOUD
Connection was successful.
Starting Load at 2020-04-08 22:18:03.
Reading data at 2020-04-08 22:18:03  Fauna Timestamp: 1586409483689000
Collection address -> address documents 3 size 0 KB  execution time:    0.115 sec  0.000 MB/sec   26.1 docs/sec
Collection dept -> dept documents 4 size 0 KB  execution time:    0.101 sec  0.000 MB/sec   39.6 docs/sec
Collection employee -> employee documents 12 size 0 KB  execution time:    0.117 sec  0.000 MB/sec  102.6 docs/sec
Collection posts1 -> posts1 documents 101 size 0 KB  execution time:    0.138 sec  0.000 MB/sec  731.9 docs/sec
Collection posts2 -> posts2 documents 102 size 0 KB  execution time:    0.198 sec  0.000 MB/sec  515.2 docs/sec
Collection posts3 -> posts3 documents 103 size 0 KB  execution time:    0.123 sec  0.000 MB/sec  837.4 docs/sec
Collection posts4 -> posts4 documents 104 size 0 KB  execution time:    0.180 sec  0.000 MB/sec  577.8 docs/sec
FDM finished at 2020-04-08 22:18:06.

Upon completion, the destination database should contain all of the documents, collections, indexes, functions, and roles from the source database.

The FaunaDB Data Manager does not copy document history. Nor does it copy GraphQL schema metadata; you need to import a GraphQL schema in the destination database in order to execute GraphQL queries there.

Also, keys and tokens are never copied because they belong to the source database; you have to create new keys and/or tokens in the destination database.

Export a FaunaDB database to a directory

To create a simple backup of a FaunaDB database, you can export the data from the database to a set of JSON files in a destination directory. Make sure that the destination directory exists before you run the export.

Run the following command to export a database to the path backup:

./fdm -source key=<SOURCE SECRET> -dest path=backup

Replace <SOURCE SECRET> with the secret that belongs to the database that you wish to export.

When you run that command, the output should resemble (especially if the <SOURCE SECRET> belongs to a demonstration database):

2020-04-08.22.20.05
===================

FDM   1.14
2020-04-09 04:19
Loaded property file path: /Users/eskwayrd/fdm-1.14/fdm.props

Field Formatting
================
<<None>>

Config File
===========
   load:                      data.json
   host:                      cloud
   jdbc.property.serverTimezone:  UTC
   policy.data:               COPY

Connecting to: CLOUD
Connection was successful.
Output to directory [/Users/eskwayrd/fdm-1.14/backup].
Starting Load at 2020-04-08 22:20:07.
Reading data at 2020-04-08 22:20:07  Fauna Timestamp: 1586409607384000
Collection address -> address documents 3 size 0 KB  execution time:    0.139 sec  0.000 MB/sec   21.6 docs/sec
Collection dept -> dept documents 4 size 0 KB  execution time:    0.104 sec  0.000 MB/sec   38.5 docs/sec
Collection employee -> employee documents 12 size 0 KB  execution time:    0.214 sec  0.000 MB/sec   56.1 docs/sec
Collection posts1 -> posts1 documents 101 size 0 KB  execution time:    0.150 sec  0.000 MB/sec  673.3 docs/sec
Collection posts2 -> posts2 documents 102 size 0 KB  execution time:    0.197 sec  0.000 MB/sec  517.8 docs/sec
Collection posts3 -> posts3 documents 103 size 0 KB  execution time:    0.141 sec  0.000 MB/sec  730.5 docs/sec
Collection posts4 -> posts4 documents 104 size 0 KB  execution time:    0.150 sec  0.000 MB/sec  693.3 docs/sec
FDM finished at 2020-04-08 22:20:08.

Upon completion, the destination folder should contain one or more files, where most files are named after a source database collection. Each file contains one JSON document per line from the source database collection.

One file is special: fauna_schema. This file contains one JSON document per line describing the source database’s collections, indexes, functions, and roles.

If you have exported a demonstration database, the folder listing should resemble:

$ ls backup/
address.json   employee.json  posts1.json    posts3.json
dept.json      fauna_schema   posts2.json    posts4.json

The FaunaDB Data Manager does not export document history. Nor does it export GraphQL schema metadata.

Also, keys and tokens are never exported because they belong to the source database.

Import JSON or CSV documents into a FaunaDB database

You can import a single JSON or CSV file, or a folder containing any number of JSON or CSV files, into a FaunaDB database. Each import file’s filename determines the name of the collection in the destination database.

The import process only handles collections and data documents. Indexes, functions, and roles are not imported.

If the source path contains a fauna_schema file from a previous export, all of the collections, indexes, functions, and role definitions are included in that file. You could use that information to manually recreate the missing schema documents in the destination database.

A future release of the FaunaDB Data Manager should automate this process.

Run the following command to import the files in the path backup:

./fdm -source path=backup -dest key=<DESTINATION SECRET>

Replace <DESTINATION SECRET> with the secret that belongs to the database that should receive the data.

When you run that command, the output should resemble the following:

2020-04-08.22.26.36
===================

FDM   1.14
2020-04-09 04:19
Loaded property file path: /Users/eskwayrd/fdm-1.4/fdm.props

Field Formatting
================
<<None>>

Config File
===========
   load:                      data.json
   host:                      cloud
   jdbc.property.serverTimezone:  UTC
   policy.data:               COPY

Connecting to: CLOUD
Connection was successful.
Starting Load at 2020-04-08 22:26:44.
File employee.json -> employee documents 12 size 3 KB  execution time:    0.004 sec  0.801 MB/sec  3000.0 docs/sec
File posts1.json -> posts1 documents 101 size 26 KB  execution time:    0.027 sec  0.967 MB/sec  3740.7 docs/sec
File address.json -> address documents 3 size 0 KB  execution time:    0.001 sec  0.630 MB/sec  3000.0 docs/sec
File posts2.json -> posts2 documents 102 size 26 KB  execution time:    0.028 sec  0.941 MB/sec  3642.9 docs/sec
File fauna_schema -> fauna_schema skipped
File posts3.json -> posts3 documents 103 size 27 KB  execution time:    0.034 sec  0.783 MB/sec  3029.4 docs/sec
File posts4.json -> posts4 documents 104 size 27 KB  execution time:    0.039 sec  0.689 MB/sec  2666.7 docs/sec
File dept.json -> dept documents 4 size 1 KB  execution time:    0.001 sec  1.242 MB/sec  4000.0 docs/sec
FDM finished at 2020-04-08 22:26:45

Import records from a JDBC database

If you have a third-party database that has a JDBC driver, it is possible to import records from such a database into a FaunaDB database. The name of each table involved in the import informs the name of the collection in the destination database. Records in each table become documents in the destination database.

To make this work, the JDBC driver for the source database needs to be available in the local filesystem. You can download drivers for MySQL or PostgreSQL. Refer to the vendor’s driver documentation to determine the namespace of the driver.

You also need a userid and password that can access the desired database on the source database server.

Finally, you need to know the URL to connect to the source database.

Once you have these details, you copy records from a JDBC database to a FaunaDB database. The following example command demonstrates copying records from the MySQL database:

./fdm -source \
  jdbc=~/Downloads/mysql-connector-java-8.0.19/mysql-connector-java-8.0.19.jar \
  driver=com.mysql.cj.jdbc.Driver \
  url=jdbc:mysql://192.168.1.3:3306/fauna \
  user=<USERID> \
  password=<PASSWORD> \
  database=<DATABASE NAME> \
  -dest key=<DESTINATION SECRET>

In that command, the following replacements are required:

Replace <USERID> with the userid that should access the JDBC database server.
Replace <PASSWORD> with the password for the userid that should access the JDBC database server.
Replace <DATABASE NAME> with the name of the database containing records that should be imported from the JDBC database server.
Replace <DESTINATION SECRET> with the secret that belongs to the database that should receive the records.

If you are not using MySQL, replace the jdbc path for the driver, and the driver namespace. Your url parameter needs to point to your JDBC server.

When you run that command, the output could resemble:

2020-04-08.22.51.36
===================

FDM   1.14
2020-04-09 04:19
Loaded property file path: /Users/eskwayrd/fdm-1.14/fdm.props

Field Formatting
================
<<None>>

Config File
===========
   load:                      data.json
   host:                      cloud
   jdbc.property.serverTimezone:  UTC
   policy.data:               COPY

URL in use: jdbc:mysql://192.168.1.3:3306/fauna
User name: rootd@192.168.1.7
DBMS name: MySQL
DBMS version: 8.0.19-0ubuntu0.19.10.3
Driver name: MySQL Connector/J
Driver version: mysql-connector-java-8.0.19 (Revision: a0ca826f5cdf51a98356fdfb1bf251eb042f80bf)
Connecting to: CLOUD
Connection was successful.
Starting Load at 2020-04-08 22:51:41.
Table categories -> categories documents 1 size 0 KB  execution time:    0.006 sec  0.000 MB/sec  166.7 docs/sec
Table stuff -> stuff documents 2 size 0 KB  execution time:    0.005 sec  0.000 MB/sec  400.0 docs/sec
FDM finished at 2020-04-08 22:51:41.

If you encounter an error connecting to the JDBC database server, it might be possible to resolve the problem by specifying appropriate JDBC properties.

For example, a default installation of MySQL often uses the UTC timezone. Clients connecting via JDBC must use the same timezone, or the following error occurs:

The server time zone value 'PDT' is unrecognized or represents more than
one time zone. You must configure either the server or JDBC driver (via
the 'serverTimezone' configuration property) to use a more specifc time
zone value if you want to utilize time zone support.

To solve this, edit the fdm.props file and include an entry specifying the correct timezone:

jdbc.property.serverTimezone=UTC

Export a FaunaDB database at a specific point in time

You can export records from FaunaDB database at a specific point in time. Simply specify a timestamp and all documents that existed prior to that timestamp are included in the export.

Run the following command to export a database to the path backup:

./fdm -source key=<SOURCE SECRET> pit=<TIMESTAMP> -dest path=backup

Replace <SOURCE SECRET> with the secret that belongs to the database that you wish to export. Replace <TIMESTAMP> with the desired ISO 8601 timestamp or date string.

When you run that command, the output should resemble (especially if the <SOURCE SECRET> belongs to a demonstration database):

2020-04-08.23.23.55
===================

FDM   1.14
2020-04-09 04:19
Loaded property file path: /Users/eskwayrd/Downloads/fdm-1.12.rc8/fdm.props

Field Formatting
================
<<None>>

Config File
===========
   load:                      data.json
   host:                      cloud
   jdbc.property.serverTimezone:  UTC
   snapshottime:              1586409269000000
   policy.data:               COPY

Connecting to: CLOUD
Connection was successful.
Output to directory [/Users/eskwayrd/Downloads/fdm-1.12.rc8/backup].
Starting Load at 2020-04-08 23:23:57.
Reading data at 2020-04-08 22:14:29  Fauna Timestamp: 1586409269000000
Collection address -> address documents 0 size 0 KB  execution time:    0.038 sec  0.000 MB/sec    0.0 docs/sec
Collection dept -> dept documents 0 size 0 KB  execution time:    0.037 sec  0.000 MB/sec    0.0 docs/sec
Collection employee -> employee documents 0 size 0 KB  execution time:    0.137 sec  0.000 MB/sec    0.0 docs/sec
Collection posts1 -> posts1 documents 101 size 0 KB  execution time:    0.176 sec  0.000 MB/sec  573.9 docs/sec
Collection posts2 -> posts2 documents 102 size 0 KB  execution time:    0.055 sec  0.000 MB/sec  1854.5 docs/sec
Collection posts3 -> posts3 documents 0 size 0 KB  execution time:    0.032 sec  0.000 MB/sec    0.0 docs/sec
Collection posts4 -> posts4 documents 0 size 0 KB  execution time:    0.031 sec  0.000 MB/sec    0.0 docs/sec
FDM finished at 2020-04-08 23:23:57.

Notice that 101 documents in the posts1 collection, and 102 documents in the posts2 collection were copied, but no documents from the posts3 or posts4 collections were copied: the documents in those collections did not exist at the timestamp specified.

Apply a format transformation

The FaunaDB Data Manager understands a few simple ETL tasks, called format transformations, include renaming or ignoring fields, changing a field type (where possible), and identifying the fields that provide the document id and timestamp. These transformations take place after a source document has been read and before the destination document is written.

For this example, we need to see the data that the FaunaDB Data Manager is going to process, and what it looks like afterwards.

Create the file daily_events.csv, in the folder containing fdm, containing the following content:

Event,Time
Breakfast,2020-04-08T06:45:00
Exercise,2020-04-08T07:30:00
Work,2020-04-08T09:00:00
Lunch,2020-04-08T12:00:00
Meeting,2020-04-08T15:30:00
Run errands,2020-04-08T17:30:00
Make dinner,2020-04-08T18:15:00
Watch TV,2020-04-08T19:00:00
Bedtime,2020-04-08T23:00:00

When imported, a collection called daily_events would be created, and each document in the collection would have the fields Event and Time.

Import the CSV file, like so:
```
./fdm -source daily_events.csv -dest key=<DESTINATION_SECRET>
```
Replace <DESTINATION SECRET> with the secret that belongs to the database that should receive the records.
In the FaunaDB Console, select the destination database, then click on the daily_events collection. You should see a list of nine documents containing the details from the daily_events.csv file. For example:
```
{ "Event": "Breakfast", "Time": "2020-04-08T06:45:00" }
```
The fields and values are all there, but the Time field values are all strings.
Now, let’s use a format transformation to rename the Time field to date, specify that the field is a date type, and specify that we only want to store the year and month using the date format string yyyy-MM:
```
./fdm -source daily_events.csv \
  -format "Time->date:date(yyyy-MM)" \
  -dest key=<DESTINATION_SECRET>
```
Replace <DESTINATION SECRET> with the secret that belongs to the database that should receive the records.
In the FaunaDB Console, reload the page for the daily_events collection. You should now see nine additional documents, this time with the Time field renamed to date, and the associated field values are now Date objects. For example:
```
{
  "Event": "Breakfast",
  "date": Date("2020-04-01")
}
```
Since our date_format string was yyyy-MM, the day number was stripped off. Since the field value is a Date, and dates always have a day number, the default value of 01 was used.

Was this article helpful?

We're sorry to hear that.
Tell us how we can improve! documentation@fauna.com

Thank you for your feedback!

Examples

Create a demonstration database

Test the source specification

Copy a FaunaDB database to a new FaunaDB database

Export a FaunaDB database to a directory

Import JSON or CSV documents into a FaunaDB database

Import records from a JDBC database

Export a FaunaDB database at a specific point in time

Apply a format transformation

Related