{"id":4462,"date":"2018-01-18T13:42:38","date_gmt":"2018-01-18T21:42:38","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=4462"},"modified":"2023-06-13T23:54:06","modified_gmt":"2023-06-14T06:54:06","slug":"csv-migrating-couchbase-relational","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/csv-migrating-couchbase-relational\/","title":{"rendered":"CSV tooling for migrating to Couchbase from Relational"},"content":{"rendered":"
\n

CSV (Comma-seperated values) is a file format that can be exported from a relational database (like Oracle or SQL Server). It can then be imported into Couchbase Server with the cbimport<\/a> utility.<\/p>\n<\/div>\n

\n

Note: cbimport comes with Couchbase Enterprise Edition. For Couchbase Community Edition, you can use the more limited cbtransfer<\/a> tool or go with cbdocloader<\/a> if JSON is an option.<\/em><\/p>\n<\/div>\n

\n

A straight relational→CSV→Couchbase ETL probably isn’t going to be the complete solution for data migration. In a later post, I’ll write about data modeling decisions that you’ll have to consider. But it’s a starting point: consider this data as “staged”.<\/p>\n<\/div>\n

\n

Note: for this post, I’m using SQL Server and a Couchbase Server cluster, both installed locally. The steps will be similar for SQL Server, Oracle, MySQL, PostgreSQL, etc.<\/em><\/p>\n<\/div>\n

\n

Export to CSV<\/h2>\n
\n
\n

The first thing you need to do is export to CSV. I have a relational database with two tables: Invoices and InvoiceItems.<\/p>\n<\/div>\n

\n

\"Relational<\/span><\/p>\n<\/div>\n

\n

I’m going to export the data from these two tables into two CSV files. With SQL Server Management Studio, this can be done a number of different ways. You can use sqlcmd<\/a> or bcp<\/a> at the command line. Or you can use Powershell’s Invoke-Sqlcmd<\/code><\/a> and pipe it through Export-Csv<\/code>. You can also use the SQL Server Management Studio UI.<\/p>\n<\/div>\n

\n

\"Export<\/span><\/p>\n<\/div>\n

\n

Other relational databases will have command line utilities, UI tools, etc to export CSV.<\/p>\n<\/div>\n

\n

Here is an example of a CSV export from a table called “Invoices”:<\/p>\n<\/div>\n

\n
\n
Id,InvoiceNum,InvoiceDate,BillTo,ShipTo\r\n1,ABC123,2018-01-15 00:00:00.000,Lynn Hess,\"Herman Trisler, 4189 Oak Drive\"\r\n2,XYZ987,2017-06-23 00:00:00.000,Yvonne Pollak,\"Clarence Burton, 1470 Cost Avenue\"\r\n3,FOO777,2018-01-02 00:00:00.000,Phillip Freeman,\"Ronda Snell, 4685 Valley Lane\"<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
Here’s an export from a related table called “InvoiceItems”:<\/p>\n<\/div>\n
\n
\n
InvoiceId,Product,Quantity,Price\r\n1,Tire,2,20.00\r\n1,Steering Wheel,5,10.00\r\n1,Engine Oil,10,15.00\r\n1,Brake Pad,24,1000.00\r\n2,Mouse pad,1,3.99\r\n2,Mouse,1,14.99\r\n2,Computer monitor,1,199.98\r\n3,Cupcake,12,.99\r\n3,Birthday candles,1,.99\r\n3,Delivery,1,30.00<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
Load CSV into Couchbase<\/h2>\n
\n
\n
Let’s import these into a Couchbase bucket. I’ll assume you’ve already created an empty bucket named “staging”.<\/p>\n<\/div>\n
\n
First, let’s import invoices.csv.<\/p>\n<\/div>\n
\n
Loading invoices<\/h3>\n
\n
\n
C:\\Program Files\\Couchbase\\Server\\bin\\cbimport csv -c localhost -u Administrator -p password -b staging -d file:\/\/invoices.csv --generate-key invoice::%Id%<\/code><\/pre>\n<\/div>\n<\/div>\n
\n
Note: with Linux\/Mac, instead of C:\\Program Files\\Couchbase\\Server\\bin<\/code>, the path will be different.<\/em><\/p>\n<\/div>\n
\n
Let’s break this down:<\/p>\n<\/div>\n
\n