blog

En la primera parte de este post vimos como realizar una petición vía REST Webservice a la API de Geocoding de google con ODI 12c, a partir de una dirección almacenada en una tabla en nuestra BD Oracle.

En esta segunda parte veremos como acceder al fichero de respuesta obtenido en la primera parte y actualizar la tabla original con las coordenadas UTM contenidas en el fichero JSON, para lo que deberemos:

• Crear un fichero XSD que defina la estructura del fichero JSON
• Configurar la topología física para el acceso al fichero JSON
• Desarrollar un mapping que lea el fichero JSON y actualice la tabla, y un package que coordine la ejecución

Para comprender mejor los pasos que vamos a seguir en el package, sería interesante primero dar un vistazo rápido al capítulo de la descripción general del funcionamiento del procesamiento de ficheros complejos, en la documentación oficial de ODI 12c. Dicho documento queda resumido en el siguiente esquema, y descibre como el fichero complejo (JSON en nuestro caso) es traducido y cargado al vuelo por el driver en un esquema de BD, que será con el que trabajaremos en realidad (aunque de manera transparente).

A nivel de topología necesitamos definir dentro la tecnología “Complex File” un DataServer (al que llamamos Coordenates) y un modelo físico (al que también llamamos Coordenates) como se aprecia en la siguiente captura:

Utilizaremos el botón “Edit nXSD” para invocar un asistente que nos ayudará con la creación del fichero nXSD que define la estructura del fichero JSON a procesar.

Seleccionaremos en el primer paso un fichero JSON de muestra (es obvio pero es importante que elijamos para este paso un fichero “completito” que tenga informados todos los campos y evitar así problemas en la definición, como tipos de datos incorrectos o que falten campos). En el segundo paso del asistente indicaremos que se trata de un fichero de tipo JSON (“JSON Interchange format”) y en el tercer paso, correspondiente a la siguiente captura de pantalla, veremos una previsualización del fichero.

Podemos indicar el Target namespace que deseemos, pero el “Root element” debe coincidir con el que indiquemos en las propiedades del driver.

En la última pantalla del asistente, un botón “Test” permite validar el fichero XSD generado mostrando el resultado (la traducción del fichero JSON a XML). Interesante asistente, que puede ser útil incluso en otros proyectos.

Las propiedades de configuración del driver de acceso al fichero JSON más importantes, y de las que necesitamos informar en la pestaña “JDBC” del dataserver, son la siguientes:

Propiedad	Valor	Descripción
XML Properties
dtd	<absolut_path>/ get_coordenates.xsd	Ruta al fichero de descripción. Puede ser local como aquí pero podemos usar http, ftp …
Root_elt	Root-Element	El mismo nombre usado en la definición del XSD
schema	coordenates	Esquema* de BD donde se almacenan los datos XML*
load_data_on_connect	False	Cargar automáticamente los datos del fichero al esquema intermedio, al establecer la conexión.
Complex File
Translator_type	json	Indica que el fichero es JSON (vacio [default]=XML)
External DB
dp_driver	oracle.jdbc.OracleDriver
dp_numeric_length	24	Tamaño de la parte entera de todos los valores numéricos
dp_numeric_scale	16	Número de decimales para todos los valores numéricos
dp_password	w1x9YIrbzGVTR 7HRQPJNcQ=	Contraseña encriptada del usuario con el que conectamos a la BD (dp_user)
dp_schema	ODI_STAGING	Esquema donde crear las tablas en la que se cargará el contenido del fichero JSON
dp_url	jdbc:oracle:thin: @localhost:1521/pdb	URL de la conexión JDBC
dp_user	ODI_STAGING	Usuario de conexión a la BD (podría ser el mismo que DP_SCHEMA u otro)
dp_varchar_length	2048	Tamaño de TODOS los campos de tipo cadena usados
dp_create_tables	Y	[Y/N/AUTO] Indica si deben crearse siempre las tablas intermedias para cargar el fichero complejo (AUTO=cuando no existan las tablas)

*En realidad no se trata de un esquema, sinó de un prefijo usado en la creación de las tablas temporales en el esquema indicado en la propiedad “dp_schema”. Las tablas creadas reflejarán la estructura definida por el fichero XSD.

Es muy interesante probar diferentes combinaciones de inicialización de las propiedades que gestionan la creación/destrucción de las tablas intermedias donde se cargará el contenido del fichero JSON (load_on_connect, dp_create_tables, dp_drop_on_connect, dp_truncate_before_load), y, sobretodo, cuándo se refrescan los datos en el esquema en BD.

También es importante verificar el tamaño asignado a las variables alfanuméricas y numéricas (dp_varchar_length, dp_numeric_…) antes de verificar la conexión al fichero (o del primer acceso al mismo, que será cuando se creen). Un dimensionamiento incorrecto provocaría el truncado de los datos si las tablas intermedias tuvieran menor precisión o longitud que los datos contenidos en el fichero JSON, sobretodo si no se recrean las tablas en cada ocasión (poco aconsejable, pues es tiempo gastado). Si no las dimensionamos bien, arrastraremos el error hasta que las recreemos.

La propiedad “translator_type” que indica a ODI que vamos a trabajar con un fichero JSON (en lugar del predeterminado XML) será asignada automáticamente por ODI si utilizamos el editor nXSD.

Una pequeña pero remarcable limitación detectada es que el nombre del fichero JSON no admite el uso de variables. Nos obligará a utilizar un nombre de fichero constante e ir copiando los ficheros de respuesta obtenidos sobre dicho fichero para su proceso a medida (aunque también podríamos ahorrárnoslo sobrescribiendo el fichero de respuesta del webservice en cada ejecución).

Llegados a este punto, puede ser útil comentar que para utilizar la BD intermedia es necesario especificar usuario y contraseña de conexión a la BD. La contraseña debe encriptarse con la utilidad encode. Recordemos que es la misma utilidad que se utiliza para encriptar la contraseña de conexión al repositorio de ODI cuando configuramos un Agente, y que podremos encontrarla en la carpeta de binarios de cualquier agente (y que no encontraremos en una instalación de ODI Studio).

C:Oracleodi122126user_projectsdomainsbase_domainbin>encode.cmd -INSTANCE=OracleDIAgent1 <*contraseña*>

2017-05-04 08:37:12.444 NOTIFICATION New data source: [ODI122_ODI_REPO/*******@jdbc:oracle:thin:@//localhost:1521/PDB]

2017-05-04 08:37:18.062 NOTIFICATION Created OdiInstance instance id=1

w1x9YIrbzGVTR7HRQPJNcQ==

Tras completar todas las propiedades, validaremos el modelo físico (“Test Connection”) y seguiremos el procedimiento habitual para asignarlo a un modelo lógico. A partir de ahí ejecutaremos la ingeniería inversa para generar el data model correspondiente a la estructura de datos de nuestro fichero JSON, de la misma manera que lo haríamos con cualquier otra tecnología.

Es el momento de hacer una pequeña pausa para curiosear en el esquema de BD que hayamos utilizado para las tablas intermedias. Ahora podemos comprobar que se han creado tantas tablas como elementos de datos definen nuestro fichero XSD, con el prefijo especificado. Si interrogamos alguna de las tablas, veremos que también ha sido poblada de datos (si la propiedad “load_data_on_connect” así lo indica).

Tras desarrollar el mapping que lea del origen JSON y actualice la tabla inicial de direcciones en la BD Oracle, ampliaremos el package que desarrollamos en la primera parte del post para incluir el mapping y la actualización de las coordenadas.

Hemos añadido tres nuevos pasos: CopyNewFile (se encarga de copiar el fichero obtenido sobre el fichero definido en el driver); SYNC_JSON; y el mapping UPD_ADRESSES que se encarga de actualizar la tabla de direcciones.

El procedure SYNC_JSON, cuyo código reproducimos a continuación, se encarga de sincronizar el fichero de datos con la tabla de trabajo en el esquema en BD. Esto es necesario debido al movimiento de ficheros y por la limitación en el uso de variables en el nombre del fichero, pero también resulta interesante ver su uso. Para ello tendríamos que comprobar que si en lugar de leer el fichero JSON lo estuviéramos generando, sería imprescindible sincronizar para escribir el fichero (La sincronización en sentido inverso se haría con la sintaxis “FROM DATABASE”).

SYNCHRONIZE SCHEMA COORDENATES FROM FILE;

Concluiremos el post apuntando algunas variaciones que podríamos realizar:

• En este ejemplo procesamos completamente las direcciones en cada iteración. Si el volumen de registros y/o de datos de respuesta a actualizar es elevado, será más eficiente otro enfoque: jugando con las propiedades del driver podríamos acumular los datos de las respuestas en el esquema intermedio en BD e ir iterando únicamente las llamadas al WS de Geocoding para actualizar masivamente la tabla de direcciones al final, fuera del bucle.
• Vimos en la primera parte del post las propiedades del objeto OdiInvokeRESTfulService, la existencia de la propiedad “Response Data Container Resolver” para analizar los resultados de la respuesta. En nuestro ejemplo, los datos relevantes de la respuesta (las coordenadas) se encuentra en el elemento de la  estructura JSON: $.results[0].geometry.location. Informando este valor, esta ruta en la propiedad indicada, el fichero de resultado que ODI generaría contendría los datos que vemos en la siguiente captura de pantalla.

Podríamos procesar esta respuesta incluso como un fichero plano, troceando la cadena de resultado (aunque entonces, esta segunda mitad del post no habría sido necesaria 😉 ).