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.
You can run the Dgraph migrate tool with:
dgraph migrate [flags]
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:
- For plain attributes, there is usually a one-to-one mapping between a SQL data type and the
Dgraph datatype. For instance, a
Bodycolumn in the
Poststable is of type
text, and hence, the predicate
posts.Bodyis of type
posts.Body: string .
- 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
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>
user = lucas password = MySecretPassword123 db = stackoverflow
Next, export the SQL database into a schema and RDF file, e.g. the
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
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.rdfcontaining all the N-Quad entries,
- a schema file
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.
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