Consultas distribuidas en Data WareHouse

programming tutorials, programming manuals
programacion
Consultas distribuidas en Data WareHouse

Consultas distribuidas

Existen dos formas fundamentales de hacer una consulta distribuida usando SQL Server. La primera consiste en la función OPENROWSET u OPENDATASOURCE, que permite ejecutar una consulta sobre cualquier SGBDR que soporte OLE DB o tenga un driver ODBC compatible con Microsoft. Este tipo de procesamiento es llamado en la literatura como: “ad hoc query”. La otra vía es usando servidores enlazados (linked servers) que soporten también la tecnología OLE DB de Microsoft. Esta última es usada para hacer consultas frecuentes a diferencia de la primera que se usa para consultas con períodos de tiempo largos. (Microsoft Training, 2000)

Sintaxis de OPENROWSET:

OPENROWSET('provider_name'
{'data -source'; 'user_id' ; 'password' | 'provider_string'},
{[catalog.][schema.]object | 'query'})

Descripción de Parámetros:
Provider_name: Nombre del proveedor OLEDB. Ejemplo MSDASQL(Para ODBC)
Data-source: Fuente de donde se obtendrán los datos. Como veremos más adelante no es más que el nombre del servidor que contiene la BD.
User_id: nombre de usuario de acceso a la BD
Passord: Contraseña de acceso a la BD
Provider_string: En caso de ser necesaria una cadena de conección a la fuente de datos.
Catalog: No es más que el nombre de la base de datos.
Schema: Nombre del usuario dueño de los objetos de la BD. Ejemplo en SQL dbo.
Object: Tabla, procediemiento, función, u otro objeto de la base de datos.
Query: Consulta en cuestión. 
 
Para poder entender mejor la sintaxis vamos a ver un ejemplo de cómo podemos hacer una consulta sobre un servidor SQL, otro sobre como hacerlo sobre una base de datos Access y uno sobre como integrar una consulta de este tipo en una normal. (Microsoft Training, 2000)

Ejemplo 1:  Encuesta a un Servidor SQL

SELECT a.*
FROM OPENROWSET('SQLOLEDB', 'LONDON1';
'newcustomer';'mypassword',
'SELECT ProductID, UnitPrice
FROM Northwind.dbo.Products ORDER BY UnitPrice')
AS a

Ejemplo 2: Encuesta a un servidor Aceess
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'
'C:\MSOffice\Access\Samples\Northwind.mdb';
'newcustomer'; 'mypassword',
Orders)
AS a

Ejemplo 3:
USE Northwind
SELECT cust.* ord.*
FROM Customers as cust JOIN
OPENROWSET('Microsoft.Jet.OLEDB.4.0'
'C:\MSOffice\Access\Samples\Northwind.mdb'';
'newcustomer'; 'mypassword',
Orders)
AS ord
On cust.customerid = ord.customerid

Como vemos este tipo de consultas puede ayudarnos mucho a la hora de extraer datos desde los sistemas que nutren al Data Warehouse. Aunque ya hemos visto que SQL tiene servicios (DTS) y otras herramientas gráficas que pueden extraer datos y transformarlos muy fácilmente. Esta variante nos permite controlar el trabajo usando sentencias de programación que fácilmente pueden ser combinadas con otras para obtener resultados muy profesionales. Otra de las ventajas que nos ofrece esta filosofía de trabajo es que no tenemos que importar los datos a SQL para analizarlos. Hay ocasiones en que no queremos importar esa base de datos porque solo se consulta una sola vez al año o por otras cuestiones diferentes y de esta forma podemos consultarla sin mucho esfuerzo.
 
Existen varios tipos de proveedores de OLE DB que pueden ser consultados en la ayuda de SQL. Aquí queremos mostrar solo los ejemplos más usados: (Microsoft Training, 2000)

  1. SQL Server: N'SQLOLEDB'
  2. Microsoft OLE DB Provider for Access (Jet):       'Microsoft.Jet.OLEDB.4.0'
  3. Microsoft OLE DB Provider for Oracle: 'MSDAORA' data_source is the SQL*Net alias name for the Oracle database to be added as a linked server
  4. OLE DB Provider for ODBC (Using data_source parameter): provider_name is 'MSDASQL' data_source is 'LocalServer'
  5. OLE DB Provider for ODBC (Using provider_string parameter): provider_name is 'MSDASQL' provider_string is 'DRIVER={SQL Server} SERVER=servername UID=login;PWD=password;'

 

Nota: Para hacer consultas distribuidas es necesario que estén activadas las siguientes opciones. ANSI_NULLS y ANSI_WARNIGS. En clientes como Query Analyzer ya están activadas por defecto, pero en otros como osql no.       

Por otra parte el uso de servidores en lazados permite que las consultas sean ejecutadas con el previo conocimiento de los servidores disponibles para hacer esta. Este tipo de consultas es usada cuando su periodo de ejecución es relativamente bajo. En el caso nuestro de estudio podríamos estar hablando de una consulta hecha para obtener datos de las ventas de los Data Mart de cada servidor SQL de cada sucursal. Así el servidor central de la empresa podría estar enlazado con los restantes servidores y desde este se podrían crear este tipo de consultas o transacciones distribuidas como veremos más adelante.
Para enlazar un servidor con otro se usa el procedimiento almacenado sp_addlinkedserver. Vamos a ver su uso con algunos ejemplos:

Ejemplo 4: Uso de sp_addlinkedserver (Microsoft Training, 2000)

-- USando SQL Server
EXEC sp_addlinkedserver
@server = 'AccountingServer',
@svrproduct = 'SQL Server'

-- Usando Oracle
EXEC sp_addlinkedserver
@server = 'OracleFinance',
@svrproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'OracleDB'

Sintaxis de ejecución del procedimiento: (Microsoft Training, 2000)

sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]

@server Name of the linked server to create
@svrproduct Product name of the OLE DB data source
@provider The unique, friendly name for the OLE DB provider corresponding to
this data source
@datasrc Name of the data source as interpreted by the OLE DB provider
@location Location of the database as interpreted by the OLE DB provider
@server Name of the linked server to create
@svrproduct Product name of the OLE DB data source
@provider The unique, friendly name for the OLE DB provider corresponding to
this data source
@datasrc Name of the data source as interpreted by the OLE DB provider
@location Location of the database as interpreted by the OLE DB provider

En el caso de servidores  como SQL Server que necesitan de un usuario y una contraseña para poder conectarse a ellos es necesario que ocurra una de las dos condiciones:

  1. El usuario con que se ejecuta la consulta esté definido en los dos servidores y tenga derechos para ejecutar las tareas deseadas.
  2. Se ejecute previamente un mapeo de usuarios usando el procediemiento: sp_addlinkedsrvlogin.

Sintaxis del procedimiento sp_addlinkedsrvlogin: (Microsoft Training, 2000)

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'useself' ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword'

@rmtsrvname Name of a linked server to which the login mapping applies.
@useself Determines whether SQL Server login accounts use their own
credentials or the values of the @rmtuser and @rmtpassword
arguments to connect to the server specified by the @rmtsrvname
argument. A value of TRUE for @useself is invalid for a Windows
Authenticated login account.
@locallogin An optional login account on the local server. If used, @locallogin
must already exist on the local server. If this value is null, then all
login accounts on the local SQL Server will be mapped to the
account on the remote server specified by @rmtuser.
@rmtuser The optional user name for connection to @rmtsrvname when
@useself is FALSE.
@rmtpassword The optional password associated with @rmtuser.
Ejemplo 5: Uso de mapeo de usarios: (Microsoft Training, 2000)

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'AccountingServer',
@useself = 'false',
@locallogin = 'Accountwriter',
@rmtuser = 'rmtAccountWriter',
@rmtpassword = 'financepass'

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'AccountingServer',
@useself = 'false',
@rmtuser= 'allcustomers'

Como podemos ver aquí el procedimiento enlaza el usuario que ejecuta la consulta localmente con el que tiene los derechos para hacer el trabajo en el servidor remoto. Así cuando se ejecute la consulta, el servidor usará el usuario adecuado para ejecutar la parte que corresponde al servidor remoto con credenciales diferentes. En nuestro caso de estudio, el servidor de la Sucursal Ciego no tiene que tener la misma contraseña para el usuario sa que los restantes servidores de las demás sucursales, para que nuestro Data Warehouse funcione. Así en el servidor central puede existir un mapeo para cada servidor de cada sucursal. Esto aumenta la seguridad, pues si un hacker se hace de una de las contraseñas de algunos de los servidores de las sucursales no podrá acceder a las restantes ni al servidor central. Incluso sin pensar en hacker. En el caso de que algún administrador quiera consultar los datos de una sucursal diferente a la que administra esto le sería imposible debido a esta técnica, lo que daría la posibilidad de que solo los usuarios de la directiva de la empresa tuvieran acceso a reportes que incluyan varios territorios.

Hasta aquí ya sabemos como enlazar los servidores, pero no sabemos como hacer la consulta sobre estos. La única cosa que diferencia una consulta distribuida sobre servidores enlazados, de una consulta sobre un solo servidor es que la primera tiene que usar nombrado de cuatro partes completo. El nombrado de cuatro partes completo incluye además de los elementos que ya estamos acostumbrados a usar como: Catalogo.Squema.Objeto, el nombre del servidor que se va a encuestar. Así, si el Server de Ciego se llama sassql el nombre de cuatro partes completo de la tabla de ventas sería: sassql.datamartventas.dbo.ventasfact. Ahora mostraremos un ejemplo de cómo se aplica esto en la consulta.

 

Ejemplo 6: Consulta sobre servidores enlazados (Microsoft Training, 2000)

SELECT CompanyName, Phone
INTO PhoneList
FROM AccountingServer.NorthwindRemote.dbo.Suppliers

SELECT CompanyName
FROM AccountingServer.NorthwindRemote.dbo.Suppliers

SELECT ProductName, CompanyName
FROM Products p JOIN
AccountingServer.NorthwindRemote.dbo.Suppliers
ON p.supplierid = s.supplierid

Existen algunas  restricciones a la hora de hacer consultas sobre servidores distribuidos. Estas son:

  1. No se pueden usar sentencias create, alter, drop.
  2. No sepuede usar goup by cuando las tablas contienen objetos largos como TEXT, NTEXT, etc.
  3. No se pueden usar las sentencias: readtext, writetext, updatetext

Otra de las cosas más importantes que posee esta técnica es que normalmente las consultas son ejecutadas en el servidor local, pero el código puede ser modificado para que se hagan en el servidor remoto. Esto puede mejorar en gran medida el rendimiento del servidor, pues la carga de procesamiento se hace en el otro extremo. Imagine que necesita una consulta sobre las ventas de todas las sucursales. Esta puede ser una consulta que consuma mucho tiempo, dado la cantidad de datos a procesar. Si se divide el trabajo entre los servidores de cada sucursal el tiempo de respuesta puede mejorar en gran medida, para usar esta técnica se hace uso de la sentencia openquery, que veremos con un ejemplo: (Microsoft Training, 2000)

Sintaxis:

OPENQUERY (linked_server, 'query')

Ejemplo 7: Uso de la sentencia OPENQUERY

SELECT * FROM OPENQUERY
(AsiaServer, 'SELECT ProductID, Royalty
FROM Northwind.dbo.ProductInfo')

Como podemos ver solo debemos especificar el servidor que va a ejecutar la consulta y luego la consulta en cuestión. Así ese servidor la ejecutará y retornará los resultados:
Existen dos elementos más de alta importancia a la hora de trabajar con servidores enlazados. El primero de ellos es la ejecución de procedimientos almacenados en servidores remotos. Para esto se usa la misma forma de nombrado de cuatro partes ejemplo: sassql.datamartventas.dbo.sp_listarVetas. El otro elemento importante es el uso de transacciones distribuidas. Esta característica de miscrosoft nos permite hacer tareas que necesitan ejecutarse como un todo en servidores distribuidos como una única transacción. Para usar esta característica se usa la sentencia BEGIN DITRIBUTED TRANSACTION <NAME>, vamos a ver esto con un ejemplo:

 

 

Ejemplo 8: Uso de transacciones distribuidas

SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
EXEC Savingsdb.dbo.withdraw 1234, 100
EXEC Centralserver.Checkingdb.dbo.deposit 1234, 100
COMMIT TRAN

Nota: Recuerde que un rollback de la transacción puede llevar este proceso a cabo en todos los servidores involucrados disminuyendo mucho el rendimiento de los mismos. Además si no está la primera sentencia del ejemplo no se hace un rollback completo. Por lo que siempre es recomendado activar esa opción (XACT_ABORT) para en última instancia hacer un rollback completo y que la BD no quede inconsistente.


  Manual de Warehouse orientado a SQL Server
DW Resumen - introduccion
DW Data Warehouse, Conceptos Fundamentales.
DW El Modelo de Hechos Dimensionales (DFM-Dimention Fact Model)
DW Diagrama en Estrella.
DW Obtención y Transformación de los Datos
DW Técnicas de desarrollo del soporte de Hardware
DW Datos distribuidos
DW Consultas distribuidas
DW Salva y restaura de datos-Conclusiones- bibliografia