Ask a Question

Migration Tool

With the Dgraph migration tool you can import SQL data into Dgraph by converting the SQL tables into a schema and RDF file, and then loading the resulting dataset into Dgraph.

Command-line options

You can run the Dgraph migrate tool with:

dgraph migrate [flags]

Options:

Usage:
  dgraph migrate [flags]

Flags:
      --db string              The database to import
  -h, --help                   help for migrate
      --host string            The hostname or IP address of the database server. (default "localhost")
  -o, --output_data string     The data output file (default "sql.rdf")
  -s, --output_schema string   The schema output file (default "schema.txt")
      --password string        The password used for logging in
      --port string            The port of the database server. (default "3306")
  -q, --quiet                  Enable quiet mode to suppress the warning logs
  -p, --separator string       The separator for constructing predicate names (default ".")
      --tables string          The comma separated list of tables to import, an empty string means importing all tables in the database
      --user string            The user for logging in

Deriving a Dgraph schema from SQL

Before converting the data, the migration tool needs to derive the schema of each predicate. Dgraph follows two simple rules for converting the schema:

  1. For plain attributes, there is usually a one-to-one mapping between a SQL data type and the Dgraph datatype. For instance, a Body column in the Posts table is of type text, and hence, the predicate posts.Body is of type string: posts.Body: string .
  2. The predicates representing inter-object relationships, like posts.OwnerUserId., simply have the type [uid], meaning following the predicate leads us to a set of other objects.

Using the Migration tool

Create a config.properties file that has the following settings (values should not be in quotes):

user = <the username for logging in to the SQL database>
password = <the password for logging in to the SQL database>
db = <the SQL database to be migrated>

For example:

user = lucas
password = MySecretPassword123
db = stackoverflow

Next, export the SQL database into a schema and RDF file, e.g. the schema.txt and sql.rdf file below:

dgraph migrate --config config.properties --output_schema schema.txt --output_data sql.rdf

You should get an output such as:

Dumping table money
Dumping table posts
Dumping table users
Dumping table votes
Dumping table comments
Dumping table constraints votes
Dumping table constraints comments
Dumping table constraints money
Dumping table constraints posts
Dumping table constraints users
Note

If you are connecting to a remote DB (something hosted on AWS, GCP, etc…), you need to pass the following flags

-- host <the host of your remote DB>
-- port <if anything other than 3306>

Once the migration tool finishes, two new files are available:

  • an RDF file sql.rdf containing all the N-Quad entries,
  • a schema file schema.txt.

Importing the data

The two files can then be imported into Dgraph using the Dgraph Live Loader or Bulk Loader. Sometimes you might want to customize your schema. For example, you might add an index to a predicate, or change an inter-object predicate (edge) from unidirectional to bidirectional by adding the @reverse directive. If you would like such customizations, you should do it by editing the schema file generated by the migration tool before feeding the files to the Live Loader or Bulk Loader.

Tip Once you have converted your SQL tables to RDF N-Quad/Triple, you can use Dgraph Live Loader or Dgraph Bulk Loader to import your data.

For example, to import the data into Dgraph using the Live Loader (Dgraph Zero and Alpha servers running on the default ports):

dgraph live -z localhost:5080 -a localhost:9080 --files sql.rdf --format=rdf --schema schema.txt