Since the GA release of N1QL, we get a lot of questions about moving content from a SQL database to Couchbase. There are many different ways to do so. Today, I have chosen what is probably the simplest. I will transform each row of each table in a JsonDocument and store it in Couchbase. I will do my test with Postgres and their sample dataset inspired by MySQL Sakila sample. I will use Java, but the guidelines presented here are applicable to other languages.
Connecting to a running SQL database
Since I am using Java, I will implement Spring Boot and their JDBC package, which handles the db connection for me. All I have to do is set up the right dependencies and properties to configure the JdbcTemplate. This object makes running a SQL query a breeze.
Dependencies
To make sure you have everything configured neatly and automatically you need the following dependencies:
|
1 2 3 4 5 6 7 |
dependencies { compile "org.springframework.boot:spring-boot-starter", "org.springframework.boot:spring-boot-starter-data-jpa", "org.postgresql:postgresql:9.4-1206-jdbc4" } |
I am testing with Postgres but you could add any other driver supported by Spring JDBC. The spring-boot-starter-data-jpa will allow me to inject the preconfigured JdbcTemplate.
Configuration
To make sure the Spring framework finds your database, add the following properties to your configuration file (for example, src/main/resources/application.properties).
|
1 2 3 4 5 6 7 8 9 10 |
spring.jpa.database=POSTGRESQL spring.datasource.platform=postgres spring.jpa.show-sql=true spring.jpa.hibernate.ddl-auto=create-drop spring.database.driverClassName=org.postgresql.Driver spring.datasource.url=jdbc:postgresql://192.168.99.100:5432/dvdrental spring.datasource.username=postgres spring.datasource.password=password |
Of course you would need to fine-tune this according to the database you are using. Here I am using Postgres running on 192.168.99.100 with default port 5432. The name of the database I want to use is dvdrental.
Code
If everything is configured correctly you should be able to inject the JdbcTemplate and start querying your SQL DB.
|
1 2 3 4 5 6 7 8 9 10 |
@Autowired JdbcTemplate jdbcTemplate; @Override public void doStuff() throws Exception { String sql = "SELECT id FROM table"; Long id = jdbcTemplate.queryForObject(sql, Long.class); } |
Connecting to Couchbase
My goal is to move content from a SQL database to Couchbase, so we also need a Couchbase connection.
Dependencies
Working with Couchbase on your Java project requires you to add the following dependency:
|
1 2 3 4 5 |
dependencies { compile "com.couchbase.client:java-client:2.2.3" } |
This will give you access to the Couchbase Java SDK.
Configuration
A basic Couchbase configuration requires basically three properties: one server IP address, a bucket name, and a bucket password. Doing this in a Spring Boot fashion would look like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
@Configuration public class Database { @Value("${hostname}") private String hostname; @Value("${bucket}") private String bucket; @Value("${password}") private String password; public @Bean Cluster cluster() { return CouchbaseCluster.create(hostname); } public @Bean Bucket bucket() { return cluster().openBucket(bucket, password); } } |
The properties hostname, bucket, and password can be added directly to your application properties file.
|
1 2 3 4 5 6 7 8 |
# Hostnames, comma separated list of Couchbase node IP or hostname hostnames: localhost,127.0.0.1 # Bucket name bucket: default # Bucket password password: |
Code
With Couchbase, the equivalent granularity level of a database would be a bucket, which is where you store documents. With the previous configuration you can simply inject a bucket and start playing around.
|
1 2 3 4 5 6 7 8 9 |
@Autowired Bucket bucket; @Override public void doStuff() throws Exception { JsonDocument doc = bucket.get("key"); } |
Tables
At this point you have a connection to a SQL database and Couchbase. Now we can start moving things around. The easiest way to move data is to consider each row of each table as a document.
Getting the SQL schema
Let’s start by getting the schema of the database automatically using the JdbcTemplate. The interesting object here is DatabaseMetaData, which can give us the complete structure of the database. The API is not the easiest to use, but at least it’s documented.
I will map the result of the DatabaseMetaData query to a list of Table and Column. I have created the following Java class to do so:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
public class Table { private String name; private List<Column> columns = new ArrayList<Column>(); private String primaryKey; public Table(String tableName) { this.name = tableName; } public void setPrimaryKey(String primaryKey) { this.primaryKey = primaryKey; } public void addColumn(String name, int type) { columns.add(new Column(name, type)); } public String getName() { return name; } public List<Column> getColumns() { return columns; } public String getPrimaryKey() { return primaryKey; } public JsonObject toJsonObject() { JsonObject obj = JsonObject.create(); JsonArray jsonColumns = JsonArray.create(); for (Column col : columns) { jsonColumns.add(col.toJsonObject()); } obj.put("tableName", name); obj.put("primaryKey", primaryKey); obj.put("columns", jsonColumns); return obj; } } public class Column { private String name; private int type; public Column(String name, int type) { this.name = name; this.type = type; } public String getName() { return name; } public int getType() { return type; } public JsonObject toJsonObject() { JsonObject obj = JsonObject.create(); obj.put("name", name); obj.put("type", type); return obj; } } |
It’s definitely not the most exciting code to write, but at the end you get a JSON representation of your SQL database tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
public void getDatabaseSchema() throws Exception { // get Database Medatadata objects to retrieve Tables schema DatabaseMetaData databaseMetadata = jdbcTemplate.getDataSource().getConnection().getMetaData(); List<String> tableNames = new ArrayList<String>(); // Get tables names ResultSet result = databaseMetadata.getTables(catalog, schemaPattern, tableNamePattern, types); while (result.next()) { String tablename = result.getString(3); String tableType = result.getString(4); // make sure we only import table(as oppose to Views, counter etc...) if (!tablename.isEmpty() && "TABLE".equals(tableType)) { tableNames.add(tablename); log.debug("Will import table " + tablename); } } // Map the tables schema to Table objects Map<String, Table> tables = new HashMap<String, Table>(); JsonObject tablesSchema = JsonObject.create(); for (String tableName : tableNames) { result = databaseMetadata.getColumns(catalog, schemaPattern, tableName, columnNamePattern); Table table = new Table(tableName); while (result.next()) { String columnName = result.getString(4); // Maps to JDBCType enum int columnType = result.getInt(5); table.addColumn(columnName, columnType); } result = databaseMetadata.getPrimaryKeys(catalog, schemaPattern, tableName); while (result.next()) { String columnName = result.getString(4); table.setPrimaryKey(columnName); } tables.put(tableName, table); tablesSchema.put(tableName, table.toJsonObject()); } JsonDocument schemaDoc = JsonDocument.create(tablesSchemaId, tablesSchema); JsonDocument doc = bucket.upsert(schemaDoc); } |
Content
Here’s the fun part. This is where we start mapping a table row to a JsonDocument. The previous section puts us in a state where we can retrieve the name of all the tables. From one table name, we can create a SQL query that returns every row of the table.
Spring has a mechanism that allows you to define a RowMapper. For each row returned by the query, you can return the object you want. Since I am using Couchbase, I want a JsonDocument.
Following is an implementation example. This RowMapper needs a Table object previously defined; therefore, we have to implement the mapRow method. There are several things we need to do here.
The first task is to create a unique key. As rows are scoped by tables, some id can be exactly the same for rows in different tables. But documents are scoped by bucket, so we need to create a unique document key that reflects the row id and the table name. To keep track of where the document comes from, I will also add a _tableName field for the table name.
Then, the exciting step comes from the type mapping. JSON supports fewer types than a SQL database, so we have some conversion to do here. This is what the getJsonTypedValue method does. It makes sure most JDBC type can be converted to a native JSON type (String, number, boolean, array, object, null). At the end, we have a JsonDocument that can be saved in Couchbase.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
public class JSONRowMapper implements RowMapper<Document> { Table table; public JSONRowMapper(Table table) { this.table = table; } public JsonDocument mapRow(ResultSet rs, int rowNum) throws SQLException { String id = table.getName() + "::" + rs.getString(table.getPrimaryKey()); JsonObject obj = JsonObject.create(); obj.put("_tableName", table.getName()); for (Column col : table.getColumns()) { Object value = getJsonTypedValue(col.type, rs.getObject(col.name), col.name); obj.put(col.name, value); } return JsonDocument.create(id, obj); } public Object getJsonTypedValue(int type, Object value, String name) throws SQLException { if (value == null) { return null; } JDBCType current = JDBCType.valueOf(type); switch (current) { case TIMESTAMP: Timestamp timestamp = (Timestamp) value; return timestamp.getTime(); case TIMESTAMP_WITH_TIMEZONE: Timestamp ts = (Timestamp) value; JsonObject tsWithTz = JsonObject.create(); tsWithTz.put("timestamp", ts.getTime()); tsWithTz.put("timezone", ts.getTimezoneOffset()); return tsWithTz; case DATE: Date sqlDate = (Date) value; return sqlDate.getTime(); case DECIMAL: case NUMERIC: BigDecimal bigDecimal = (BigDecimal) value; return bigDecimal.doubleValue(); case ARRAY: Array array = (Array) value; Object[] objects = (Object[]) array.getArray(); return JsonArray.from(objects); case BINARY: case BLOB: case LONGVARBINARY: return Base64.getEncoder().encodeToString((byte[]) value); case OTHER: case JAVA_OBJECT: // database specific, default to String value return value.toString(); default: return value; } } } |
With that RowMapper it makes things really easy. We can loop through the table’s name, run the query, and save the results in Couchbase. Doing this in a synchronous fashion would look like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
for (String tableName : tableNames) { String sql = "select * from " + tableName + ";"; List<JsonDocument> rs = jdbcTemplate.query(sql, new JSONRowMapper(tables.get(tableName))); if (!rs.isEmpty()) { for (JsonDocument doc : rs) { bucket.upsert(doc); } } } bucket.upsert(schemaDoc); |
But I prefer the async version:
|
1 2 3 4 5 6 7 8 |
Observable.from(tableNames).flatMap(s -> { String sql = String.format("Select * from %s;", s); return Observable.from(jdbcTemplate.query(sql, new JSONRowMapper(tables.get(s)))); }) // start by a jsonDocument containing the tables to be imported. .startWith(schemaDoc).flatmap(doc -> asyncBucket.upsert(doc)); |
Here I am not using the full potential of Rx; take a look at this function that writes a doc to Couchbase and handles timeout and error management.
For convenience, I have packaged all steps implemented and previously shown in a single project. All you have to do is make sure your properties file is configured right and run the importer:
|
1 2 3 |
$ ./bin/couchbase-java-importer myConfiguration.properties |
Take a look at the README file for more information.
Conclusion
Today we have learn how to move SQL content to Couchbase, but there is still some work to do. Next time I will tell you how to move the SQL business logic to the application layer.