Meetup Cluster de Hadoop con BigSQL

Laboratorio de BigSQL

Luis Reina Juliá

IBM Software Group

6 de Junio de 2014

INDICE

LAB 1 INTRODUCCIÓN.............................................................. 3

LAB 2 PREPARANDO EL ENTORNO............................................ 4 2.1 ARRANCAR BIGINSIGHTS (HADOOP Y OTROS COMPONENTES)....................................................................... 4 2.2 ARRANCAR ECLIPSE................................................................................................................................. 5 2.3 CREAR DESDE ECLIPSE UNA CONEXIÓN AL CLUSTER DE HADOOP ................................................................. 6 2.4 CREAR UNA CONEXIÓN AL SERVIDOR BIG SQL DESDE ECLIPSE.................................................................... 7

LAB 3 CONSULTANDO DATOS CON BIG SQL.............................. 9 3.1 CREAR UN PROYECTO Y UN SCRIPT DE SQL. .............................................................................................. 9 3.2 CREAR TABLAS DE EJEMPLO Y CARGAR DATOS .......................................................................................... 11 3.3 EJECUTANDO CONSULTAS SQL BÁSICAS .................................................................................................. 14 3.4 ANALIZANDO LOS DATOS CON BIG SQL.................................................................................................... 17 3.5 CREANDO Y TRABAJANDO CON VISTAS...................................................................................................... 21 3.6 USANDO BIG SQL DESDE UNA APLICACIÓN CLIENTE JDBC ........................................................................ 25

3

Lab 1 Introducción

En este laboratorio, aprenderás cómo trabajar con Big SQL, un componente de IBM InfoSphere BigInsights. En concreto, usarás Big SQL para hacer consultas a datos estructurados tradicionales gestionados por BigInsights.

Big SQL es una capa de software que permite a los profesionales de IT crear tablas y consultar datos en BigInsights (Hadoop) utilizando sentencias SQL tradicionales. Para hacer esto, los programadores utilizan sintaxis SQL estándar y, en algunos casos, extensiones del lenguaje SQL creadas por IBM para facilitar la explotación de ciertas tecnologías basadas en Hadoop.

Las organizaciones interesadas en Big SQL suelen tener personal con grandes conocimientos de SQL, así como una suite de aplicaciones de business intelligence y herramientas de consulta y reporting. La idea de ser capaz de utilizar estas herramientas y conocimientos existentes – y posiblemente reutilizar parte de las aplicaciones existentes– puede ser muy atractiva para las organizaciones que empiezan a trabajar con Hadoop. De hecho, algunas compañías con grandes data warehouses basados en bases de datos relacionales están buscando plataformas basadas en Hadoop como un destino potencial donde ubicar los datos “fríos” (e.g. Históricos), o usados con poca frecuencia, manteniendo la capacidad de realizar consultas. En otros casos, otras organizaciones están interesadas en Hadoop para analizar y filtrar datos no tradicionales (logs, datos de sensores, publicaciones en redes sociales, etc), volcando subconjuntos o agregados de esta información a sus data warehouses relacionales, extendiendo así la vista de sus productos, clientes o servicios.

Parte de este laboratorio está basado en el artículo What's the Big Deal about Big SQL?. Debes estar familiarizado con los conceptos básicos de Big SQL cubiertos en dicho artículo antes de comenzar este laboratorio.

Tras completar este laboratorio, serás capaz de:

• Crear una conexión a un servidor BigSQL

• Crear tablas de Big SQL

• Cargar datos de diversas fuentes en tablas de Big SQL

• Consultar big data usando Big SQL

• Crear, publicar, desplegar y ejecutar una aplicación BigSQL

• Trabajar con Jsqsh, un interfaz por línea de comandos para Big SQL

Este laboratorio se desarrolló para InfoSphere BigInsights 2.1.2 single node Quick Start Edition VMware image, que está preconfigurada con un nombre de host y un nombre de usuario de administración específicos. A lo largo de este laboratorio, usarás la siguiente información:

OS Hostname Username Password

Linux Bivm biadmin biadmin

4

Lab 2 Preparando el Entorno

En esta sección se verifica que los servicios y herramientas necesarias están corriendo para poder empezar a trabajar con Big SQL. Después de completar esta parte serás capaz de:

• Arrancar BigInsights y verificar que todos los servicios están corriendo. • Arrancar Eclipse y verificar que el plugin de BigInsights esta instalado. • Crear una conexión desde Eclipse al Servidor de BigInsights. • Crear una conexión al servidor de Big SQL.

2.1 Arrancar BigInsights (Hadoop y otros componentes)

Para arrancar los servicios de Hadoop y de los componentes del ecosistema de Hadoop incluidos en la imagen virtual de BigInsights, hacer lo siguiente:

__1. Abrir un terminal en linux: Usar el botón derecho sobre el desktop de Linux y seleccionar “Abrir en un terminal”

________2222. . . . Cambiar al usuario biadmin: Ejecutar su – biadmin

________3333. . . . Exportar el display para poder abrir una aplicación xWindows: Ejecutar export DISPLAY=:0.0

__4. Arrancar todos los componentes: Ejecutar el script start-all.sh (este script se encuentra en el directorio /opt/ibm/biginsights/bin pero no es necesario especificarlo porque se encuentra en el PATH del usuario biadmin (echo $PATH).

__5. Si se quiere comprobar desde la consola de BigInsights que todo está arrancado: Abrir, como antes, un terminal en linux y ejecutar firefox. Esto abrirá un navegador, ir a la página de la consola: http://bivm:8080/ (donde bivm es el hostname)

__6. Una vez abierta la consola os autentificáis con el usuario biadmin y password biadmin y pincháis en la pestaña que pone “cluster status”

5

2.2 Arrancar Eclipse

Para arrancar Eclipse, que viene con la imagen virtual de Linux, hacer lo siguiente:

__7. Abrir un terminal en linux: Usar el botón derecho sobre el desktop y seleccionar “open terminal”

________8888. . . . Cambiar al usuario biadmin: Ejecutar su – biadmin

__9. Cambiar al directorio donde se encuentra eclipse: cd /home/biadmin/eclipse

________10101010. . . . Arrancar eclipse: Ejecutar ./eclipse

__11. Aceptar el workspace por defecto pulsando ok, aparecerá entonces la ventana de Tareas de BigInsights: “Task Launcher for Big Data”. Si cerráis esta ventana se puede volver abrir, en cualquier momento, seleccionándola dentro del menu Help:

6

Nota: Si el Task Launcher for Big Data no aparece quiere decir que el plugin de Eclipse de BigInsights no esta instalado. Este plugin se puede bajar desde la consola de BigInsights en la primera

2.3 Crear desde Eclipse una conexión al Cluster de Hadoop

Este paso no es necesario porque viene preconfigurado en la imagen que os habéis bajado: Quick Start Edition, pero lo pongo para que sepáis que habría que hacerlo en otro caso. Por ejemplo si usáis un eclipse desde otra máquina.

__1. Dentro del Task Launcher for Big Data pulsad en la Tarea Create a BigInsights Server connection y meted la Información de conexión, el usuario y la contraseña.

________2222. . . . Probad la conexión Test Connection y finalizar Finish

7

__3. La conexión una vez definida aparece en el panel del BigInsights Server:

2.4 Crear una Conexión al servidor Big SQL desde Eclipse

Esta sección explica como configurar una conexión JDBC al servidor de Big SQL. En este caso, tampoco es necesario ya que viene preconfigurado en la imagen virtual. En cualquier caso se explica para vuestro conocimiento:

________1111. . . . Abrid la prespectiva Database Development: Window > Open Perspective > Other > Database Development.

__2. En el panel Data Source Explorer pulsar con el botón derecho sobre Database Connections > Add Repository.

__3. En la ventana que aparece seleccionar Big SQL JDBC Driver y meter el nombre que queráis para la nueva conexión (e.g., My Big SQL Connection). Pulsad Next.

8

Meter la información de conexión para tu instalación, incluyendo hostname ,Puerto donde escucha Big SQL (por defecto el 7052), usuario y contraseña. Verifica que has seleccionado el driver JDBC correcto arriba (el segundo de la lista).

__4. Pulsad el botón Test connection para verificar que la conexión es correcta.

__5. Pulsad Save password y Finish.

__6. En el panel de Data Source Explorer, expander la lista para verificar que tu nueva conexión aparece.

__7. Volved a la perspective de BigInsights.

Ya estamos listos para crear tablas y realizar queries desde eclipse.

Nota: Eclipse es una herramienta más para conectarse a Big SQL y realizar consultas, pero se puede usar cualquier otra estándar del mercado que permita conexión JDBC u ODBC (e.g. Squirrel). Big SQL proporciona drivers ODBC y JDBC.

9

Lab 3 Consultando datos con Big SQL

En este lab empezarás a trabajar con Big SQL para consultar y analizar los datos almacenados en un data warehouse en Hadoop. Big SQL proporciona un soporte amplio de SQL basado en el estándar ISO. Puedes realizar consultas usando los drivers JDBC u ODBC exactamente de la misma forma que accedes a las bases de datos relaciones desde tus aplicaciones empresariales. Las consultas se pueden ejecutar concurrentemente.

El motor de SQL soporta joins, unions, grouping, common table expressions, windowing functions y otros expresiones SQL familiares. Big SQL puede usar Map/Reduce para procesar las consultas en paralelo o ejecutar la consulta dentro del motor de Big SQL en un sólo nodo, lo que sea más apropiado para la consulta.

Este lab usa datos de una compañia ficticia, llamada Sample Outdoor, que vende y distribuye productos de exterior a tiendas en todo el mundo. Además también vende directamente a los consumidores desde su pagina web. En los últimos años, la compañía ha crecido de forma continuada vendiendo productos en casi cualquier parte del mudno. Vas a realizar consultas Big SQL que devuelven resultados para entender mejor sus productos y ventas.

Después de completar este modulo, entenderás como:

• Crear tablas Big SQL y usar Hive como el repositorio. • Cargas datos desde ficheros locales en tablas Big SQL. • Crear y ejecutar consultas Big SQL desde Eclipse. • Crear y ejecutar una programa JDBC que acceda a Big SQL desde Eclipse.

3.1 Crear un proyecto y un script de SQL.

Para empezar, vas a crear un proyecto de BigInsights y un script de Big SQL en Eclipse.

__1. Para crear un proyecto de BigInsights: Desde el menu de Eclipse pincha en File > New > Other. Expande la carpeta de BigInsights y selecciona BigInsights Project pulsa Next.

__2. Escribe myBigSQL en el nombre del proyecto y pulsa Finish.

__3. Si no estas en la perspectiva de BigInsights cambia a esa perspectiva de Eclipse.

__4. Crea un fichero de script de SQL. Desde el menu de Eclipse pulsa File > New > Other. Expande la carpeta de BigInsights y selecciona SQL Script, y pulsa Next.

10

__5. En la ventanana que aparece, Enter or select parent folder field, selecciona myBigSQL. El fichero se almacena bajo este proyecto.

__6. En el campo File name escribe aFirstFile. La extensión .sql se añade automáticamente. Pulsa Finish.

__7. Aparece una nueva ventana para seleccionar la conexión, selecciona Big SQL, cuando lo hagas la ayuda de contexto y el chequeo de sintaxis se activan en el editor que usas para escribir tu SQL

__8. Pulsa Finish para cerar la venta Select Connection Profile.

11

__9. En el editor SQL que abre el fichero aFirstFile.sql que acabas de crear añade los siguientes comentarios:

-- Este es un script simple de SQL.

-- Estos son comentarios.

-- Una línea que empieza con dos guiones es un comentario.

-- y no es parte de las sentencias SQL.

Más adelante usaremos este fichero.

__10. Guarda los cambios del fichero pulsando CTRL-S.

3.2 Crear tablas de ejemplo y cargar datos

Ahora vamos a crear tablas y cargar datos desde ficheros que se encuentra en el filesystem local (linux). Este tutorial utiliza tabla Big SQL gestionadas por Hive.

Los datos de ejemplo que usaras están en el directorio $BIGSQL_HOME/samples donde está instalado BigInsights server. La variable de entorno $BIGSQL_HOME contiene el directorio de instalación que típicamente es opt/ibm/biginsights/bigsql/.

El rango de tiempo de los datos de la compañía Sample Outdoor es de 3 años y 7 meses, empezando el 1 de enero de 2004 y terminando el 31 de julio de 2008. El periodo de 43 meses refleja la historia que esta disponible para el análisis.

El esquema que se usa en este tutorial es GOSALESDW. Contiene tablas de hechos y dimensiones para una variedad de areas como:

__1. Finanzas __2. Geografía __3. Marketing __4. Personal __5. Productos __6. Ventas __7. Tiempo

__1. Abre un terminal de Linux y cambia al usuario biadmin.

__2. Cambia al directorio $BIGSQL_HOME/samples. Abre y mira la información del fichero README.

12

__3. Ejecuta el script ./setup.sh, hazlo de la siguiente forma:

./setup.sh -u biadmin -p biadmin

Verás el mensaje:

Loading the data .. will take a few minutes to complete ..

Please check /var/ibm/biginsights/bigsql/temp/GOSALESDW_LOAD.OUT file for

progress information.

El script ejecuta tres ficheros que estan en el directorio $BIGSQL_HOME/samples/queries: GOSALESDW_drop.sql, GOSALESDW_ddl.sql, y GOSALESDW_load.sql. Puede tardar varios minutos en completarse.

__4. Cuando termine, vuelve al usuario biadmin (exit)

__5. Usa la pestaña Files de la consola Web de BigInsights para navegar por el directorio que contiene las nuevas tablas creadas: hdfs://biginsights/hive/warehouse/gosalesdw.db

13

__6. Opcionalmente vuelve a la shell y visualiza el contenido de los ficheros del subdirectorio $BIGSQL_HOME/samples/queries. Visualiza el contenido del fichero GOSALESDW_ddl.sql.

Observa que la sentencia CREATE TABLE contiene SQL estándar que encontrarías si trabajaras con una base de datos relacional. Hay también cláusulas adicionales, únicas de Hadoop, como la especificación del formato, en esta caso el carácter \t (tabulador) especifica el final de campo.

__7. Opcionalmente visualiza el contenido del fichero $BIGSQL_HOME/samples/queries/GOSALESDW_load.sql

14

Nótese que los datos serán cargados de ficheros locales (del directorio ../samples/data) en las tablas creadas en el paso previo. La cláusula overwrite en cada sentencia LOAD hace que cualquier dato previo cargado en la tabla se sobrescriba.

__8. Se pueden visualizar los datos cargados mostrando, por ejemplo, el fichero GOSALESDW.DIST_INVENTORY_FACT.txt que esta en $BIGSQL_HOME/samples/data. Notese que cada campo va delimitado por tabuladores como se especificó en la creación de la tabla.

3.3 Ejecutando consultas SQL básicas

Hasta ahora has creado y poblado tablas con datos acerca de la compañía Sample Outdoor. En esta sección explorarás algunas consultas SQL básicas, y empezarás a obtener información sobre el conjunto de datos de prueba con el que estás trabajando.

__1. Desde Eclipse Project Explorer, abre el proyecto myBigSQL, y haz doble click en el fichero aFirstFile.sql

__2. En el panel de edición de SQL, escribe la siguiente sentencia:

SELECT * FROM GOSALESDW.GO_REGION_DIM;

Cada sentencia SQL debe terminar con un punto y coma. Esta sentencia selecciona, o extrae, todas las filas que existen en la tabla GO_REGION_DIM.

15

__3. Pulsa el icono Run SQL (un botón verde en la esquina superior derecha del fichero).

Dependiento de cuántos datos hay en la tabla, una sentencia SELECT * puede tardar bastante tiempo en ejecutarse. Tu resultado debe contener 21 registros o filas.

También puedes tener un script que contenga varias consultas. Cuando quieras ejecutar un script completo, pulsa el ico Run SQL. Cuando quieras ejecutar un subconjunto de sentencias, márca aquellas que quieras ejecutar y pulsa F5.

__4. Mejora la sentencia SELECT * añadiendo un predicado para que devuelva menos filas.

SELECT * FROM GOSALESDW.GO_REGION_DIM WHERE REGION_EN LIKE 'Amer%';

__5. Pulsa Run SQL para ejecutar el script completo. Esta consulta devuelve cuatro registros, o filas.

16

Nota: si tienes algún error, escribe la consulta al completo en lugar usar copiar/pegar.

__6. Es posible obtener más información acerca de la estructura de la tabla realizando consultas a las tablas catálogo de Big SQL, las cuales contienen metadatos para respaldar la base de datos. El catálogo de Big SQL consiste en cuatro tablas dentro del esquema SYSCAT: schemas, tables, columns y indexcolumns. Escribe la siguiente consulta, y ahora selecciona dicha sentencia y pulsa F5.

SELECT * FROM syscat.columns

WHERE tablename= 'go_region_dim'

AND schemaname= 'gosalesdw';

Esta consulta utiliza dos predicados en una cláusula WHERE. La consulta encuentra toda la informacion de la tabla syscat.columns cuyas columnas tablename y schemename valgan go_region_dim y gosalesdw respectivamente. Dado que estás usando una condición AND con sendos predicados, ambos deben ser ciertos para que se devuelva una fila. Debes utilizar comillas simples para rodear los valores de tipo string.

El resultado de la consulta a la tabla syscat.columns son los metadatos, o la estructura de la tabla. Mira la pestaña SQL Results en Eclipse para ver la salida, 54 filas. Esto significa que hay 54 columnas en la tabla go_region_dim.

__7. Después, ejecuta una consulta que devuelva el número de filas en la tabla. Escribe la siguiente consulta, seleccionala y pulsa F5.

SELECT COUNT(*) FROM gosalesdw.go_region_dim;

La función de agregación COUNT devuelve el número de filas en el resultado de la consulta. Una fila que solo incluye valores nulos se incluye en la cuenta. En este ejemplo hay 21 filas en la tabla go_region_dim.

__8. Otra forma utilizar dicha función es mediante la sentencia COUNT (distinct <expresión>). Como su nombre indica, es posible determinar el número de valores únicos en una columna, como region_en. Escribe la siguiente sentencia en tu fichero SQL:

17

SELECT COUNT (distinct region_en) FROM gosalesdw.go_region_dim;

El resultado es 5. Esto significa que solamente hay cinco nombres únicos de región en Inglés (dentro de la columna llamada region_en).

__9. Otra sentencia útil en Big SQL es la claúsula LIMIT. Ésta determina un límite en el número de filas que son producidas como resultado de la sentencia SELECT. Escribe la siguiente sentencia en tu fichero SQL, seleccionala y después pulsa F5.

SELECT * FROM GOSALESDW.DIST_INVENTORY_FACT LIMIT 50;

__10. Guarda tu fichero SQL.

3.4 Analizando los datos con Big SQL

Hasta ahora has creado algunas tablas y emitido algunas consultas Big SQL básicas. Exploremos ahora unos pocos escenarios un poco más sofisticados. En esta lección crearás y ejecutarás consultas Big SQL para juntar datos de múltiples tablas, y realizarás agregaciones así como otras operaciones SQL. Nótese que las consulas incluidas en esta sección están basadas en consultas simples incluidas en BigInsights, en el directorio $BIGSQL_HOME/samples/queries.

__1. Crea un nuevo fichero SQL llamado companyInfo.sql dentro de tu proyecto. Desde el menú de Eclipse, selecciona File > New > Other. En la ventana “Select a wizard”, expande la carpeta BigInsights, selecciona SQL Script y pulsa Next. En la ventana “New SQL File”, selecciona el proyecto myBigSQL en el campo “Enter or select the parent folder”. En el campo File name, escribe companyInfo. La extensión .sql se añade automáticamente. Pulsa Finish.

Para averiguar qué productos de la compañía Sample Outdoor Company se pidieron, y por qué método fueron pedidos, debes juntar información de múltiples tablas.

__2. Escribe los siguientes comentarios y sentencias en el fichero companyInfo.sql:

18

-- Fetch the product name and the quantity and

-- the order method.

-- Query 1

SELECT pnumb.product_name, sales.quantity,

meth.order_method_en

FROM

gosalesdw.sls_sales_fact sales,

gosalesdw.sls_product_dim prod,

gosalesdw.sls_product_lookup pnumb,

gosalesdw.sls_order_method_dim meth

WHERE

pnumb.product_language='EN'

AND sales.product_key=prod.product_key

AND prod.product_number=pnumb.product_number

AND meth.order_method_key=sales.order_method_key;

Dado que hay más de una refencia a tabla en la cláusula FROM, la consulta une rilas de dichas tablas. Un predicado join especifica una relación entre al menos una columna de cada tabla a unir.

• Los predicados como prod.product_number=pnumb.product_number reducen los resultados a los números de productos que coinciden en ambas tablas.

• Esta consulta también utiliza un alias en la clausulas SELECT y FROM. Por ejemplo, pnumb.product_name, pnumb es el alias para la tabla gosalesdw.db.sls_product_lookup. Este alias puede ahora utilizarse en la cláusula where de forma que no es necesario repetir el nombre completo de la tabla, y la clausula WHERE no es ambigua.

19

• El uso del predicado and pnumb.product_language=’EN’ reduce el resultado únicamente a los productos en inglés. Esta base de datos contiene miles de filas de datos en diversos idiomas, por lo que restringir el idioma proporciona cierta optimización.

__3. Selecciona la sentancia, empezando por la palabra SELECT y terminando con el punto y coma, y pulsa F5. Revisa los resultados en la página SQL Results. Ahora puedes ver los productos que se han vendido, y cómo fueron encargados por clientes.

__4. La página SQL Results de Eclipse, por defecto limita la salida a 500 filas. Es posible modificar este valor en las preferencias de Data Management. En todo caso, para averiguar cuántas filas devuelve la consulta en un entorno Big SQL, escribe la siguiente consulta en el fichero companyInfo.sql, selecciona la consulta y pulsa F5:

--Query 2

SELECT COUNT(*)

--(SELECT pnumb.product_name, sales.quantity,

-- meth.order_method_en

FROM

gosalesdw.sls_sales_fact sales,

gosalesdw.sls_product_dim prod, gosalesdw.sls_product_lookup pnumb,

gosalesdw.sls_order_method_dim meth

WHERE

pnumb.product_language='EN'

20

AND sales.product_key=prod.product_key

AND prod.product_number=pnumb.product_number

AND meth.order_method_key=sales.order_method_key;

El resultado de la consulta es 446,023 filas.

__5. Actualiza la consulta que está etiquetada como --Query 1 para restringir el método de pedido a aquellos iguales a 'Sales visit'. Añade la siguiente cláusula justo antes del punto y coma:

AND order_method_en='Sales visit'

__6. Selecciona por completo la sentencia etiquetada como --Query 1 y pulsa F5. El resultado de la consulta se muestra en el panel SQL Results.

Los resultados ahora muestran los productos y cantidades pedidas por clientes visitando una tienda distribuidora.

21

__7. Para averiguar qué método de todos los existentes tiene el mayor número de cantidades pedidas, debes añadir una cláusula GROUP BY (group by pll.product_line_en, md.order_method_en). También usarás una función de agregación SUM (sum(sf.quantity)) para totalizar las ordenes por producto y método. Además, puedes limpiar la salida utilizando un alias (as Product) para cambiar el nombre de la columna por uno más legible.

-- Query 3

SELECT pll.product_line_en AS Product,

md.order_method_en AS Order_method,

sum(sf.QUANTITY) AS total

FROM gosalesdw.sls_order_method_dim AS md,

gosalesdw.sls_product_dim AS pd,

gosalesdw.sls_product_line_lookup AS pll,

gosalesdw.sls_product_brand_lookup AS pbl,

gosalesdw.sls_sales_fact AS sf

WHERE

pd.product_key = sf.product_key

AND md.order_method_key = sf.order_method_key

AND pll.product_line_code = pd.product_line_code

AND pbl.product_brand_code = pd.product_brand_code

GROUP BY pll.product_line_en, md.order_method_en;

__8. Selecciona la sentencia completa y pulsa F5.

Los resultados en la página SQL Results muestran 35 filas.

3.5 Creando y trabajando con vistas

Big SQL soporta vistas (tablas virtuales) basadas en una o más tablas físicas. En esta sección, crearás una vista que abarca múltiples tablas creadas anteriormente. Después, consultarás esta vista utilizando una sentencia SELECT. Haciendo esto verás que puedes trabajar con vistas en Big SQL de la misma forma puedes trabajar con vistas en un DBMS relacional.

__9. En Eclipse, dentro de tu proyecto de BigInsights, crea una nuevo script SQL llamado ViewExample.sql

22

__10. Copia y pega el siguiente código en tu script:

use gosalesdw; create view if not exists myview as

select product_name, product_key, quantity, order_day_key, sales_order_key, order_method_en from gosalesdw.mrk_promotion_fact mkt,

gosalesdw.sls_sales_fact sales, gosalesdw.sls_product_dim prod, gosalesdw.sls_product_lookup pnumb,

gosalesdw.sls_order_method_dim meth where mkt.order_day_key=sales.order_day_key and sales.product_key=prod.product_key

and prod.product_number=pnumb.product_number and pnumb.product_language='EN' and meth.order_method_key=sales.order_method_key;

Nótese que esta vista junta distintas tablas para extraer información acerca de los productos y las ventas.

__11. Selecciona ambas sentencias y pulsa F5. Inspecciona la página SQL Results para verificar que las operciones se han realizado con éxito.

__12. Desde la pestaña Files de la consola Web, expande los contenidos de la base de datos GOSALESDW creada anteriormente en tu data warehousr (es decir, expande /biginsights/hive/warehouse/gosalesdw.db) y navega por su contenido hasta encontrar la vista que acabas de crear (myview).

23

La vista no está presente. ¿Puedes imaginar porqué? Las vistas son tablas virtuales, y solo se materializan en el tiempo de ejecución de la consulta. La pestaña DFS File contiene únicamente carpetas (y ficheros) que representan estructuras físicas (tablas).

__13. Pulsa en la pestaña Catalog Table, a la derecha de DFS Files, en el navegador de ficheros. Expande la carpeta gosalesdw para explorar los registros en el catálogo de estructuras físicas (tablas) y virtuales (vistas) asociadas con esta base de datos. Ahora sí que encontrarás la vista myview.

24

__14. Vuelve al script SQL en Eclipse y añade la siguiente sentencia SELECT:

select * from gosalesdw.myview limit 20;

__15. Selecciona la sentencia y ejecútala. Verifica que los resultados que aparecen son similares a estos:

25

3.6 Usando Big SQL desde una aplicación cliente JDBC

Puedes escribir una aplicación cliente JDBC que utilice Big SQL para abrir una conexión a una base de datos, ejecute consultas y procese los resultados. En este ejercicio opcional verás que escribir una aplicación cliente JDBC para BigSQL es similar a escribir una aplicación cliente para cualquier DBMS relacional que soporte acceso JDBC.

__1. En el entorno Eclipse de IBM InfoSphere BigInsights, crea un proyecto Java pulsando File > New > Project. En la ventana New Project, selecciona Java Project. Pulsa Next.

__2. Escribe un nombre de proyecto en el campo Project Name, como MyJavaProject. Pulsa Next.

__3. Abre la pestaña de librerías y pulsa Add External Jars. Selecciona el driver Big SQL JDBC (bigsql-jdbc-driver.jar)de tu local path $BIGSQL_HOME/jdbc.

26

__4. Pulsa Finish. Selecciona No cuando te pregunten si quieres abrir alguna otra perspectiva.

__5. Pulsa con el botón detecho en el proyecto MyJavaProject, y pulsa New > Package. En el campo Name, dentro de la ventana New Java Package, escribe un nombre para el paquete, como aJavaPackage4me. Pulsa Finish.

__6. Pulsa con el botón derecho en el paquete aJavaPackage4me, y pulsa New > Class.

__7. En la ventana New Java Class, en el campo Name, escribe SampApp. Selecciona la casilla de validación en public static void main(String[] args). Pulsa Finish.

__8. Escribe o copia el siguiente código en el fichero SampApp.java:

package aJavaPackage4me;

//a. Import required package(s)

27

import java.sql.*;

public class SampApp {

/**

* @param args

*/

//b. set JDBC & database info

static final String db = "jdbc:bigsql://bivm:7052/default";

static final String user = "biadmin";

static final String pwd = "biadmin";

public static void main(String[] args) {

Connection conn = null;

Statement stmt = null;

System.out.println("Started sample JDBC application.");

try{

//c. Register JDBC driver

Class.forName("com.ibm.biginsights.bigsql.jdbc.BigSQLDriver");

//d. Get a connection

conn = DriverManager.getConnection(db, user, pwd);

System.out.println("Connected to the database.");

//e. Execute a query

stmt = conn.createStatement();

System.out.println("Created a statement.");

String sql;

sql = "select * from gosalesdw.sls_product_dim " +

"where product_key=30001";

ResultSet rs = stmt.executeQuery(sql);

System.out.println("Executed a query.");

//f. Obtain results

System.out.println("Result set: ");

while(rs.next()){

//Retrieve by column name

int product_key = rs.getInt("product_key");

int product_number = rs.getInt("product_number");

//Display values

System.out.print("* Product Key: " + product_key + "\n");

System.out.print("* Product Number: " + product_number + "\n");

}

28

//g. Close open resources

rs.close();

stmt.close();

conn.close();

}catch(SQLException sqlE){

// Process SQL errors

sqlE.printStackTrace();

}catch(Exception e){

// Process other errors

e.printStackTrace();

}finally{

// Ensure resources are closed before exiting

try{

if(stmt!=null)

stmt.close();

}catch(SQLException sqle2){

} // nothing we can do

try{

if(conn!=null)

conn.close();

}catch(SQLException sqlE){

sqlE.printStackTrace();

}// end finally block

}// end try block

System.out.println("Application complete");

}}

__a. Después de la declaración del paquete, asegúrate de que incluyes lo paquetes que contienen las clases JDBC que son necesarias.

__b. Configura la información de la base de datos a la que quieres conectar.

__c. Después, registra el driver JDBC para que puedas abrir un canal de comunicación con la base de datos.

__d. Abre la conexión.

__e. Ejecuta una query mandando una sentencia SQL a la base de datos.

__f. Extrae datos del resultado de la consulta.

__g. Limpia el entorno, cerrando todos los recursos de bases de datos.

__9. Guarda el fichero y pulsa con el botón derecho en el fichero Java y pulsa Run > Run as > Java Application.

__10. Los resultados se muestran en la vista Console de Eclipse:

Started sample JDBC application.

Connected to the database.

29

Created a statement.

Executed a query.

Result set:

* Product Key: 30001

* Product Number: 1110

Application complete

30