The key outcome of this blog is to teach you how you can easily migrate your relational data over to Couchbase, lowering your TCO and empowering your data platform to respond to the fast paced release cycles we know and love! Whilst migrating you can even retain the individual name/keyspaces you already have structured using scopes and collections. This blog is somewhat of an amalgamation of the newly released features explained in other blogs, and recent updates, but differing to those blogs it includes some examples how you can migrate your relational data into Couchbase.
One of the brilliant things about Couchbase is that we truly listen to the needs of our community and continue to develop our products to offer the best possible experience to our clients. I wrote this blog as “hands on” if you’d like to follow along, but you can also skip the extraction step and just work with a TSV I’ve provided!
If you’re following along, the prerequisites are an RDBMS to extract data from, a python interpreter, and a Couchbase Server (be it in Docker, VM, physical etc.).
I’m going to use an online example for our demo database. Linked here is the sample database with three tables; customer, agents, and orders.
Migrating data to Couchbase is really easy, we’re going to mimic our table structure using our new scopes and collections feature. To start with we need a logical storage space, in this example we’re using one bucket per database, so we only need to create one bucket. The Schema will map to a Scope, so one scope. Finally, the Tables will each map to a Collection of the same name, so a total of three collections.
Creating Buckets, Scopes, and Collections:
We have so many options available to us here. The SDK, REST API, Couchbase CLI (also not to forget Couchbase shell), and Web GUI. I’m going to use the REST API, the beauty here is that we can access it from the command line, and there’s no need to install anything! For anyone shy of the REST interface, the documentation here will explain other alternative methods.
The steps below will specify the commands needed to setup your bucket, scope and collections:
Run the following two commands to create your data bucket and your scope:
|
1 2 |
curl -X POST -u Administrator:password http://127.0.0.1:8091/pools/default/buckets -d name=migration -d bucketType=couchbase -d ramQuotaMB=100 curl -X POST -u Administrator:password <a href="http://127.0.0.1:8091/pools/default/buckets/migration/collections">http://127.0.0.1:8091/pools/default/buckets/migration/collections</a> -d name=reference |
Now create the three Collections which we will be mapping our table data to:
|
1 |
curl -u Administrator:password -X POST http://localhost:8091/pools/default/buckets/migration/collections/reference -d name=$TABLE_NAME |
Simply change the variable $TABLE_NAME as needed to create the agents, orders, and customers collections respectively.
(There are links to the documentation pages for these API’s in the Appendix)
With everything created, head over to the Buckets section and click the “Scopes & Collections” link next to the name of the bucket which you created, mine is named migration. If you then expand the scope which we have created, you should see our three collections we created.
Extracting Data From Our RDBMS
Now that we have our bucket, scope and collections in place, we’re ready to export our relational data. I’ve written a short script which extracts the data stored in the sample MySQL database to TSV, which is happily supported by our import tools, or you can export the data yourself. If you’ve got the database setup, simply run the script and change the variables to use the username and password which you configured. After running the script, you should see three .tsv files created containing the data from each table of the sample database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
#!/usr/bin/python3 import subprocess import json import re sql_commands='{ "commands": { "agents": "SELECT * FROM agents;", "customer": "SELECT * FROM customer", "orders": "SELECT * FROM orders;" } }' db_name = "sample" username = "root" password = "rootroot" sql_commands = json.loads(sql_commands) for command_name in sql_commands["commands"]: print("Running command: " + command_name) sql_statement = "--execute={0}".format(sql_commands["commands"][command_name]) command = subprocess.run(["/usr/local/mysql/bin/mysql", db_name, "--user={0}".format(username), "--password={0}".format(password), sql_statement], capture_output=True) with open(command_name+"_out.tsv", 'w') as command_output_file: command_without_trailing_white_space = re.sub(b" +\\t", b'\\t', command.stdout) command_output_file.write(command_without_trailing_white_space.decode('ascii')) |
Here’s an example of the TSV output, if you’re only working on the importing part, go ahead and use this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
AGENT_CODE AGENT_NAME WORKING_AREA COMMISSION PHONE_NO COUNTRY A007 Ramasundar Bangalore 0.15 077-25814763 A003 Alex London 0.13 075-12458969 A008 Alford New York 0.12 044-25874365 A011 Ravi Kumar Bangalore 0.15 077-45625874 A010 Santakumar Chennai 0.14 007-22388644 A012 Lucida San Jose 0.12 044-52981425 A005 Anderson Brisban 0.13 045-21447739 A001 Subbarao Bangalore 0.14 077-12346674 A002 Mukesh Mumbai 0.11 029-12358964 A006 McDen London 0.15 078-22255588 A004 Ivan Torento 0.15 008-22544166 A009 Benjamin Hampshair 0.11 008-22536178 |
Importing Data Into Couchbase
Our feature rich import tool, cbimport, makes it really easy to import data of many formats. Recently we have implemented some import functionality into our web gui, making the process even more accessible. Below is a screenshot of our Web Gui import tool. To get here login to the Couchbase Web GUI, then go to “Documents” -> “Import”.
Here we’re going to import our TSV’s using the “Select File to Import” button, notice that when uploading a file, a lot of fields are actually filled out for you. Take a look at the imported data and make sure that it corresponds to what you’re expecting considering the previous relational structure. We only have a few short steps to complete to import our data.
First, specify a collection to import the data to, e.g. agents_out.tsv maps to the ‘agents’ collection. We’re also going to correspond the individual rows of our previous relational database to document ID’s in Couchbase, again this is easily done with our import tool using the “Import With Document ID” section. Throughout my data imports I’ve used the primary key of the relational data as the document ID in Couchbase, this is really helpful as each key in a collection must be unique. For example in the agents_out.tsv I’ve used the primary key “AGENT_CODE”. Perform this step for each of the .tsv’s; agents, orders, and customers.
And there you have it, just to recap, we have done the following:
- Exported our data from our legacy database to TSV
- Created a scope, and collections based on the tables we are importing
- Imported the data using the data migration tool to a specific collection, within a scope.
Okay so now that I’ve got my data in Couchbase, I need to learn a new way to query data? Quite the opposite, why not head over to the Couchbase web GUI, and query some of your newly imported data using N1QL? It’s SQL++ compliant and if you’ve ever written a SQL query before you’ll feel right at home; the gate’s open, come on in! Not only will we make it easy for you to write queries, we’ll even help you build the right index with our Index Advisor service, just enter the query which you’d like to perform and the advisor will suggest an index. We even support JOIN’s:
|
1 2 3 4 |
SELECT orders.AGENT_CODE, orders.ORD_AMOUNT, agents.AGENT_NAME FROM `bigdata`.reference.orders as orders JOIN `bigdata`.reference.agents as agents ON orders.AGENT_CODE = meta(agents).id; |
This is a great start in the right direction to transitioning from relational to NoSQL, if you’d like to learn more about modelling your data to get the most out of Couchbase, check out our data modeling guide.
Want a deeper dive on scopes and collections? I recommend Shivani’s blog
Appendix:
- The following link will tell users exactly how to setup their sample database.
- Couchbase Docker container
- 7.0 REST API – Scope Creation Documentation
- 7.0 REST API Bucket Create Documentation
- 7.0 REST API – Collection Creation Documentation
