Import from SQL
-
SQL Migration allows you to import your existing SQL Database into RavenDB.
-
Currently supported:
- MySQL
- SQL Server
- Oracle
- PostgresSQL
-
The import process is semi-automatic. It needs to take into account SQL relations, represented by links between foreign keys and primary keys.
Identifying one-to-one, one-to-many and many-to-one relations to perform a successful import, requires clear resolving of the original business model. -
The NoSQL data model allows nested arrays/objects. In SQL such relations are rendered as additional tables, often unrelated to the natural data form. They are created to satisfy Third normal form (3NF), e.g. by embedding
OrderLines
within anOrder
object to solve a potential select n+1 problem. -
Another case in which artificial tables are used is the creation of many-to-many relationships. In NoSQL, this can be modeled as a list of foreign object ids and require no extra table/collection.
-
The SQL Migrator tries to detect and automatically suggest the best data model.
-
Every change made during data model preparations, maintains data integrity and cohesion.
-
Primary/Foreign keys must be defined in the source SQL database, so the migrator would be able to perform correctly.
-
In this page:
Pointing to source database
Welcome screen is divided to 2 columns. Left hand side is used when you start import from scratch. Right hand side is used when you want to continue import started in the past.
Creating new import configuration
Start with selecting database driver and provide connection string. Test Connection can be used to validate connection string and to verify connectivity to data source.
Few advanced options are available:
-
You can convert property name to PascalCase. Ex.
ZIP_CODE
is converted toZipCode
. -
Remove suffix from property name. Ex.
ADDRESS_ID
becomesAddress
. -
Detect many-to-many relationship. If all table columns are defined as foreign keys it is detected as many-to-many relationship. Such table is not imported and both sides of relationship are linked. This allows efficient modelling of 2-way relationship.
Continue existing configuration
If you previously exported migration configuration, you can upload file to continue process.
After providing valid connection RavenDB obtains information about tables, primary keys and foreign keys.
At this point user have many options to alter target document structure.
Sql Migration - View Properties
SQL Migration - Actions panel
-
Toggle all tables - Select none/all tables for sql migration.
-
Migrate button - Starts actual migration
-
Filter input - Allows to filter tables by its name.
-
Export configuration - Exports SQL configuration to file, so it can be finished later, by using Continue existing migration option.
-
Fullscreen mode - Toggles fullscreen mode.
-
Settings menu - Contains few settings options.
-
Batch size - Allows to change batch size of document insertion
-
Convert binary columns to attachments - If this option is checked, then binary columns are added to document as attachment, in other case binary content is inserted as base64 string to target document.
-
Partial migration - Allows to limit number of items being imported per each table. Please notice this option can introduce broken links, between documents.
Properties conversion explained
-
SQL Table Name - Table name from source SQL database
-
Collection Name - Target collection name, this name can be changed by clicking on it.
-
Incoming relationships counter. Shows count of incoming relationships to this collection. Details can be obtain by click on this button.
-
Document Identifier preview. Ex. If table contains column OrderID which is primary key. Rows contain values: 1, 2, 3. Then generated document identifiers are:
Orders/1
,Orders/2
,Orders/3
. -
SQL Column Name - Column name from source SQL database
-
Document Property - Property name and type to use for corresponding column value. Property name can be changed by clicking on it.
-
Relationships area - Shows information about incoming and outgoing references.
Relationship information can be read as in following picture:
Relationship information
Sql Migration - Handling relationships
Let's assume SQL database contains following tables:
ERD of sample database
Database contains 2 relationships. Those relationships can be represented as additional document properties.
SQL Migration - Relationships
We can see that inside OrderLines document OrderId property will be created (with value: Orders/{ID}). It is called linking property.
On reverse side of relationship, we have skip mode. It means this document property will be skipped.
With such settings we end up with documents with following structure:
OrderLine:
{
"Quantity": 10,
"OrderId": "Orders/1",
"ProductId": "Products/2"
}
Order:
{
"Date": "2018-05-05",
"Username": "accountName",
"ExtraInfo": "Please call before delivery"
}
Product:
{
"Name": "Phone",
"UnitsInStock": 4
}
If we embed OrderLines inside Orders, and uncheck OrderLines table (as we don't need this artificial collection), we end up with following structure:
SQL Migration - Relationships
Order:
{
"Date": "2018-05-05",
"Username": "accountName",
"ExtraInfo": "Please call before delivery",
"OrderLines: [
{
"Quantity": 10,
"ProductId": "Products/2"
}
]
}
Product:
{
"Name": "Phone",
"UnitsInStock": 4
}
Target document structure depends on side of relationship and chosen mode:
-
One-to-many relationship: Source table contains primary key, target table contains foreign key
-
Mode set to skip - Property is skipped
-
Mode set to link - Property value contains array of document identifiers
-
Mode set to embed - Property value contains array of nested documents
-
-
Many-to-one relationship: Source table contains foreign key, target table contains primary key.
-
Mode set to skip - Property is skipped
-
Mode set to link - Property value contains single document identifier or null
-
Mode set to embed - Property value contains single nested document
-
Note
Embedded documents don't contain identifier as it is redundant.
Filtering / Transforming documents
Sql Migration allows to filter and/or transform rows being imported. Click on 'Filter/Transform' button to access those settings.
Flow goes like this:
-
Rows are read from source SQL database by executing query defined in filter section. This query can contain where which gives the ability to filter out as SQL statement level.
-
Initial transformation is applied: embedded properties are calculated, foreign key columns are filled in based on user choice.
-
Document is passed though transformation script (if any). Please notice that document passed to transformation contains embedded properties, which allows to calculate values, etc.
-
Document is inserted to RavenDB database.
Filter / Transform
Note
You can write throw 'skip' in transformation script to skip given document
Test 'filter / transform' scripts
Test option can be used for simulate transformation scripts.
Let's say we want to compute and store amount of ordered products per order.
Computing 'ProductsCount' per order
We have 2 test modes available:
- Use first record matching query - it runs the query defined in filter section and gets first result.
- Specify primary key values to use - user is presented with extra fields to provide value for each primary key column.
Test script results
Data integrity helpers
Sql Migrator tries to maintain model integrity during entire process. It contains following checks:
- Table can not be deselected when at least on incoming relationship is defined:
Table is in use by incoming references
- Embedded table can be deselected. If table was embedded and it is not used by any other relationship, migrator suggest to deselect root collection. Ex. OrderLines was embedded inside Orders. OrderLines collection won't be needed, as it was embedded inside Orders.
Suggestion to deselect root table
- Target table for linking is not selected. This could create broken link, so migrator suggest to select target table first.
Suggestion to select target
- Deselecting all tables
Before deselecting all tables, migration asks if user wants to set all relationships to skip.
Skipping all relationships