Migrating from Relational Databases

  • concept
    +
    Migration guidelines for relational database users. In this section, we use MySQL as an example relational database.

    When migrating from MySQL to Couchbase Server, there are several things that you might want to think about, starting with the data model, data types, and feature set differences.

    Data Model — Mapping from MySQL to Couchbase Server

    Data modeling for RDBMS has been a well-defined discipline for many years. Professionals, including novice users, have been practicing techniques such as logical to physical mapping and normalization / de-normalization. However, the old-school RDBMS data modeling techniques still play a meaningful role for those who are new to the NoSQL technology.

    Table 1. Concept mapping between MySQL and Couchbase Server
    MySQL Couchbase Server

    Database

    Bucket

    Table

    Bucket(s)/Keyspaces

    Row

    Document

    Column

    Field

    Fixed schema

    Flexible schema

    Table 2. Datatype mapping between MySQL and Couchbase Server
    Data type MySQL Couchbase Server

    Case sensitive

    Yes/No

    Yes

    Numbers

    Yes

    Yes

    String

    Yes

    Yes

    Boolean

    Yes (as tinyint)

    Yes

    Date time

    Yes

    Yes (as a string in JSON)

    Spatial data

    Yes

    Yes

    MISSING

    No

    Yes

    NULL

    Yes

    Yes

    Object/Arrays

    No

    Yes

    Blobs

    Yes

    Yes

    Feature Set

    Like MySQL, Couchbase Server offers a rich set of features and functionality far beyond those offered in simple key-value stores.

    With Couchbase Server, you also get an expressive SQL-like query language and query engine called SQL++, which is combined with a new powerful indexing mechanism — Global Secondary Indexes.

    Table 3. Feature differences between MySQL and Couchbase Server
    Feature Key difference

    Keys/Indexes

    Primary keys on keys of (key, value) pair

    SQL statements

    1. The result is set in JSON instead of rows and columns.

    2. NEST, UNNEST

    3. Operations on datetime fields require datetime functions in SQL++.

    4. JSON-induced functions in SQL++: JSON, Object, and array functions.

    5. Type and comparison functions.

    6. JOIN, sub-query format differences.

    7. USING KEYS and ON KEYS functions

    Explain and metadata

    Variation in command and results (JSON).

    ETL Tools

    You might have a spectrum of relational, operational, and analytical data sources in your environment. You might also need more sophistication applied to a data movement situation, such as more than just simple extract-load. Various tools are available, but the most common use cases are best served by combining our JDBC drivers with our Java SDK.