Desde el lanzamiento GA de N1QL, recibimos muchas preguntas sobre cómo mover contenido de una base de datos SQL a Couchbase. Hay muchas maneras diferentes de hacerlo. Hoy he elegido la que probablemente sea la más sencilla. Transformaré cada fila de cada tabla en un JsonDocument y lo almacenaré en Couchbase. Haré mi prueba con Postgres y su conjunto de datos de muestra inspirado en MySQL Muestra Sakila. Utilizaré Java, pero las directrices que aquí se presentan son aplicables a otros lenguajes.
Conexión a una base de datos SQL en ejecución
Como estoy usando Java, implementaré Spring Boot y su paquete JDBC, que maneja la conexión db por mí. Todo lo que tengo que hacer es establecer las dependencias y propiedades correctas para configurar el paquete JdbcTemplate. Este objeto facilita la ejecución de consultas SQL.
Dependencias
Para asegurarte de que tienes todo configurado de forma ordenada y automática necesitas las siguientes dependencias:
|
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" } |
Estoy probando con Postgres pero se podría añadir cualquier otro driver soportado por Spring JDBC. El spring-boot-starter-data-jpa me permitirá inyectar el JdbcTemplate preconfigurado.
Configuración
Para asegurarse de que el framework Spring encuentra su base de datos, añada las siguientes propiedades a su archivo de configuración (por ejemplo, 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 |
Por supuesto, usted tendrá que ajustar esto de acuerdo a la base de datos que está utilizando. Aquí estoy usando Postgres corriendo en 192.168.99.100 con el puerto por defecto 5432. El nombre de la base de datos que quiero usar es dvdrental.
Código
Si todo está configurado correctamente deberías poder inyectar el JdbcTemplate y empezar a consultar tu BD SQL.
|
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); } |
Conexión a Couchbase
Mi objetivo es mover el contenido de una base de datos SQL a Couchbase, por lo que también necesitamos una conexión Couchbase.
Dependencias
Trabajar con Couchbase en tu proyecto Java requiere que añadas la siguiente dependencia:
|
1 2 3 4 5 |
dependencies { compile "com.couchbase.client:java-client:2.2.3" } |
Esto le dará acceso a Couchbase SDK de Java.
Configuración
Una configuración básica de Couchbase requiere básicamente tres propiedades: una dirección IP del servidor, un nombre de bucket y una contraseña de bucket. Hacer esto de una manera Spring Boot se vería así:
|
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); } } |
Las propiedades hostname, bucket y password pueden añadirse directamente al archivo de propiedades de tu aplicación.
|
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: |
Código
Con Couchbase, el nivel de granularidad equivalente a una base de datos sería un bucket, que es donde almacenas los documentos. Con la configuración anterior puedes simplemente inyectar un bucket y empezar a jugar.
|
1 2 3 4 5 6 7 8 9 |
@Autowired Bucket bucket; @Override public void doStuff() throws Exception { JsonDocument doc = bucket.get("key"); } |
Tablas
En este punto tienes una conexión a una base de datos SQL y a Couchbase. Ahora podemos empezar a mover cosas. La forma más fácil de mover datos es considerar cada fila de cada tabla como un documento.
Obtención del esquema SQL
Empecemos por obtener el esquema de la base de datos automáticamente usando el JdbcTemplate. El objeto interesante aquí es DatabaseMetaDataque puede darnos la estructura completa de la base de datos. La API no es la más fácil de usar, pero al menos está documentada.
Voy a mapear el resultado de la consulta DatabaseMetaData a una lista de Tabla y Columna. Para ello he creado la siguiente clase Java:
|
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; } } |
Definitivamente no es el código más emocionante de escribir, pero al final obtienes una representación JSON de las tablas de tu base de datos SQL.
|
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); } |
Contenido
Aquí está la parte divertida. Aquí es donde empezamos a mapear una fila de tabla a un JsonDocument. La sección anterior nos pone en un estado en el que podemos recuperar el nombre de todas las tablas. A partir de un nombre de tabla, podemos crear una consulta SQL que devuelva todas las filas de la tabla.
Spring dispone de un mecanismo que permite definir un RowMapper. Para cada fila devuelta por la consulta, puedes devolver el objeto que quieras. Como estoy usando Couchbase, quiero un JsonDocument.
A continuación se muestra un ejemplo de implementación. Este RowMapper necesita un objeto Table previamente definido; por lo tanto, tenemos que implementar el método mapRow. Hay varias cosas que tenemos que hacer aquí.
La primera tarea es crear una clave única. Como las filas se clasifican por tablas, algunos identificadores pueden ser exactamente los mismos para filas de diferentes tablas. Pero los documentos se clasifican por bucket, por lo que necesitamos crear una clave de documento única que refleje el id de la fila y el nombre de la tabla. Para no perder de vista la procedencia del documento, también añadiré un campo _tableName para el nombre de la tabla.
Luego, el paso emocionante viene del mapeo de tipos. JSON soporta menos tipos que una base de datos SQL, así que tenemos que hacer alguna conversión aquí. Esto es lo que hace el método getJsonTypedValue. Se asegura de que la mayoría de los tipos JDBC puedan ser convertidos a un tipo JSON nativo (String, number, boolean, array, object, null). Al final, tenemos un JsonDocument que puede ser guardado en 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; } } } |
Con ese RowMapper hace las cosas realmente fáciles. Podemos hacer un bucle con el nombre de la tabla, ejecutar la consulta y guardar los resultados en Couchbase. Haciendo esto de una manera sincrónica se vería así:
|
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); |
Pero prefiero la versión asíncrona:
|
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)); |
Aquí no estoy utilizando todo el potencial de Rx; eche un vistazo a esta función que escribe un documento en Couchbase y gestiona el tiempo de espera y los errores.
Para mayor comodidad, he empaquetado todos los pasos implementados y mostrados anteriormente en un proyecto único. Todo lo que tienes que hacer es asegurarte de que tu archivo de propiedades está bien configurado y ejecutar el importador:
|
1 2 3 |
$ ./bin/couchbase-java-importer myConfiguration.properties |
Eche un vistazo al LÉAME para más información.
Conclusión
Hoy hemos aprendido a mover contenido SQL a Couchbase, pero todavía queda algo de trabajo por hacer. La próxima vez os contaré cómo mover la lógica de negocio SQL a la capa de aplicación.
