Oracle y SAS vía SQL pass trough

12 Jun

Para trabajar directamente con el motor de BBDD SAS cuenta con «Pass trougth». SAS crea una conexión al gestor de BBDD y desde ese momento podemos ejecutar sentencias de SQL directamente. Para seguir con la línea de trabajo habitual emplearemos ejemplos para conocer su funcionamiento. Los ejemplos que vamos a emplear serán sobre una BBDD Oracle ya que es muy común trabajar en entornos SAS con acceso a algún datamart de Oracle. Evidentemente la utilidad pass trough o pas thru convive perfectamente con

las librerías dinámicas de SAS a Oracle. Los ejemplos que vamos a ver serán:

  • Bajada de tabla Oracle a SAS vía pass thru. Usuario de consulta.
  • Subida de tabla SAS a un esquema Oracle. Usuario propierario asigna permisos.
  • Oracle como motor de consulta desde SAS. Usuario propietario crea tabla.
  • Borrar tabla Oracle. Usuario propietario hace DROP.

Estos 4 ejemplos pueden ayudarnos a conocer mejor como funciona la utilidad pass thru y sobre todo estoy seguro que pueden ser de utilidad para muchos profesionales que tengan que migrar procesos en PL/SQL a SAS. Lo primero que vamos a hacer es definir un usuario de consulta, un usuario propietario y un usuario de carga que tendrán sus respectivos roles en la BBDD Oracle (*con_role,  *car_role y *own_role) . Emplearemos un lenguaje SAS muy genérico:


*DECLARAMOS LOS USUARIOS PROPIETARIOS;
%let usuario_propietario=*******;
%let contrasenia_propietario=********;
*DECLARAMOS LOS USUARIOS DE CONSULTA;
%let usuario_consulta=*****;
%let contrasenia_consulta=********;
*DECLARAMOS LOS USUARIOS DE CARGA;
%let usuario_carga=*****;
%let contrasenia_carga=********;

Bajada de tabla Oracle a SAS vía pass thru:


proc sql;
connect to oracle as mycon
(user=&usuario_consulta password=&contrasenia_consulta path='PATH');
 create table TABLA_SAS as
 select * from connection to mycon (
  select CAMPO1 as CAMPO1,
  CAMPO2 as CAMPO2,...
  from ESQUEMA.TABLA_ORACLE) ;
%put &sqlxmsg;
disconnect from mycon;
quit;

Lo primero que hacemos es crear una conexión al path de Oracle. La tabla SAS es una selección del total de columnas de la select que enviámos al motor de BBDD. Esta select será en el lenguaje de la conexión. En este caso como la conexión es Oracle emplearemos PL/SQL. Si la conexión fuera a SQL Server emplearíamos ese lenguaje. Con la macrovariable &sqlxmsg indicamos que en el log deseamos ver los mensajes del motor de la BBDD, muy importante para chequear errores.

Subida de tabla SAS a un esquema Oracle:


libname ORASAS oracle user=&usuario_propietario pass=&contrasenia_propietario
 path= 'PATH' schema=ESQUEMA dbindex=yes;
*SUBIDA MEDIANTE PASO DATA;
data ORASAS.TABLA_ORACLE;
 set LIBSAS.TABLA_SAS;
run;
*DESASIGNAMOS LA LIBERIA;
libname ORASAS clear;
*ASIGNAMOS PERMISOS A LOS ROLES PARA LOS USUARIOS DE CARGA Y CONSULTA;
proc sql;
connect to oracle as mycon (user=&usuario_propietario.
 password=&contrasenia_propietario. path='PATH');
  execute(GRANT DELETE, INSERT, SELECT, UPDATE ON TABLA_ORACLE TO *_CON_ROLE) by mycon;
  execute(GRANT DELETE, INSERT, SELECT, UPDATE ON TABLA_ORACLE TO *_CAR_ROLE) by mycon;
  EXECUTE(COMMIT)by mycon;
%put &sqlxmsg;
  disconnect from mycon;
quit;

Creamos una librería dinámica a Oracle (la forma habitual de obtener tablas de la DBMS) pero hemos de crearla con el usuario propietario y sobre el esquema en el que tenemos permisos. Subir la tabla es tan fácil como hacer un paso data. Después como buen práctica desasignamos la librería dinámica (creada con el usuario propietario) y asignamos permisos a los roles de consulta y/o de carga para que puedan acceder a las tablas. El usuario de consulta hará consultas y el propietario, dará permisos, hará cargas (no empleamos usuario de carga), borrará tablas,… Esta metodología será más apropiada a la hora de trabajar con la BBDD.

Oracle como motor de consulta desde SAS:


proc sql;
 connect to oracle as mycon (user=&usuario_propietario
password=&contrasenia_propietario path='PATH');
 CREATE TABLE ESQUEMA1.TABLA_DESTINO AS SELECT * FROM CONNECTION TO MYCON(
  SELECT CAMPO1 AS CAMPO1, CAMPO2 AS CAMPO2,...
  FROM ESQUEMA2.TABLA_ORACLE
  WHERE ... );
disconnect from mycon;QUIT;
*ASIGNAMOS PERMISOS;
proc sql;
connect to oracle as mycon (user=&usuario_propietario.
  password=&contrasenia_propietario. path='PATH');
  execute(GRANT DELETE, INSERT, SELECT, UPDATE ON ESQUEMA1.TABLA_DESTINO TO *_CON_ROLE) by mycon;
  execute(GRANT DELETE, INSERT, SELECT, UPDATE ON ESQUEMA1.TABLA_DESTINO TO *_CAR_ROLE) by mycon;
  EXECUTE(COMMIT)by mycon;
%put &sqlxmsg;
  disconnect from mycon;
quit;

En este ejemplo se realiza una consulta y se crea una tabla en el esquema en el que asumimos que somos propietarios, pero podemos hacer la consulta sobre el mismo esquema de la instancia Oracle sobre la que trabajamos, posteriormente asignamos permisos a los usuarios de carga y consulta. Otra forma de realizar este proceso es crear la estructura y posteriormente cargar los datos, algunos profesionales consideran que es la forma óptima de trabajo:


proc sql;
 connect to oracle as mycon (user=&usuario_propietario
 password=&contrasenia_propietario path='PATH');
 CREATE TABLE TABLA_DESTINO AS SELECT * FROM CONNECTION TO MYCON(
  SELECT CAMPO1 AS CAMPO1, CAMPO2 AS CAMPO2,...
  FROM ESQUEMA.TABLA_ORACLE
  WHERE ... AND ROWNUM<1 );
disconnect from mycon;QUIT;
*ASIGNAMOS PERMISOS A LOS ROLES DE USUARIOS DE CARGA Y CONSULTA;
proc sql;
 connect to oracle as mycon (user=&usuario_propietario password=&contrasenia_propietario path='PATH');
  execute(GRANT SELECT, update, insert, delete ON TABLA_DESTINO TO *_car_ROLE) by mycon;
  execute(GRANT SELECT, update, insert, delete ON TABLA_DESTINO TO *_con_ROLE) by mycon;
  EXECUTE(COMMIT)by mycon;
disconnect from mycon;QUIT;
*CARGAMOS LA TABLA CON EL USUARIO DE CARGA;
proc sql;
 connect to oracle as mycon (user=&usuario_carga
 password=&contrasenia_carga. path='PATH');
  EXECUTE(INSERT INTO TABLA_DESTINO
  SELECT CAMPO1 AS CAMPO1, CAMPO2 AS CAMPO2,...
  FROM ESQUEMA.TABLA_ORACLE
  WHERE ...)BY MYCON;
disconnect from mycon;QUIT;

Esta metodología es más óptima pero mucho más compleja como puede comprobarse. Esto puede ser de gran utilidad para realizar consultas con el motor de Oracle y beneficiarnos de sus particiones e índices. Además no gastamos espacio de temporal con SAS e incluso si tenemos un buen tablespace las consultas irán mejor.

Borrar tabla Oracle


proc sql;
connect to oracle as mycon (user=&usuario_propietario
password=&contrasenia_propietario path='PATH');
 execute(DROP TABLE ESQUEMA_PROPIETARIO.TABLA) by mycon;
 execute(COMMIT) by mycon;
 %put &sqlxmsg;
disconnect from mycon;
quit;

Un buen ejemplo de ejecución de PL bajo SAS. Recordad que el código que ponemor desde el ; de la connect es el código que emplea el motor de BBDD. Espero que estos ejemplos genéricos os sean de utilidad sobre todo para aquellos que tenéis que migrar procesos a SAS. Por supuesto si tenéis dudas o un trabajo excelentemente remunerado…  rvaquerizo@analisisydecision.es

19 respuestas a «Oracle y SAS vía SQL pass trough»

  1. En realidad no es un artículo dedicado a los programadores de SAS, si no a aquellos que tienen exeriencia en entornos de BBDD y tienen que emplear SAS.

    Tengo algunos trucos sobre DB2 y entornos Mainframe que espero sean de mucha utilidad.

  2. Sí, buen artículo, además como curiosidad añadir que la opcion COMMIT no existe en PROC SQL de SAS, solo si lo hacemos servir vía SQL pass-trough.

  3. Hola Raul.

    Una pregunta, como puedo crear, borrar y analizar los indices de tablas oracle mediante este procedimiento de pass trough??? Me resultaría mas comodo que tener que crear, borrar y analizar los indices desde UNIX cada vez que inserto o updateo campos en una tabla….gracias de antemano.

  4. Irene, recuerda que en el momento en el que empleas pass trought empiezas a trabajar en PL. Por ello deberías hacer algo parecido a lo que haces en tu sesión de PL pero de este modo:

    proc sql;
    connect to oracle as mycon (user=&usuario_propietario
    password=&contrasenia_propietario path=’PATH’);

    execute(CREATE INDEX nombre_indice ON [esquema.] nombre_tabla (columna1 [, columna2, …]);) by mycon;
    execute(COMMIT) by mycon;

    execute(ANALYZE TABLE TABLA_ORACLE COMPUTE STATISTICS FOR ALL INDEXES;) by mycon;
    execute(COMMIT) by mycon;

    execute(DROP INDEX [schema.]index [FORCE];) by mycon;
    execute(COMMIT) by mycon;

    %put &sqlxmsg;
    disconnect from mycon;
    quit;

    Como norma habitual: execute(;) + execute(COMMIT)

    Que suerte tenéis algunos usuarios….

  5. Hola.
    Estoy intentando conectar my SAS 9.0 a una base de datos oracle.
    Utilizo el siguiente código:

    PROC SQL;
    CONNECT TO ORACLE ( USER=usuario PASSWORD=password PATH = ‘@mercurio’);
    SELECT * FROM CONNECTION TO ORACLE
    (SELECT^* FROM esquema.CAISSE);
    DISCONNECT FROM ORACLE;
    QUIT;
    No progresa porque me emite el siguiente error
    The SAS/ACCESS Interface to ORACLE cannot be loaded. ERROR: Se ha encontrado la imagen
    SASORA pero no puede cargarse.
    ¿Existe algún problema en mi SAS para que no me permita ver la bbdd?
    Muchísimas gracias.

  6. Gracias rvaquerizo.
    He ejecutado el código que me dices pero no aparece la línea ni en el log ni en el output.
    ¿Crees que es posible tener acceso?

  7. No. Es un SAS local.

    Francamente. No sé cómo se hace. Lo único que se me ocurre es intentar una conexión ODBC (que tamibén tendría que aprender)

  8. Hola, una consulta. Tengo un SAS E.Guide local (sin repositorio) y quiero registrar tablas en una librería que asigne, pero no me registra las tabla, si puedo verlas o traerlas. pero no deja registrar.

    LIBNAME mydblib
    OLEDB PROPERTIES=(«Initial Catalog» = NomreBASEdeDATOS)
    PROVIDER= sqloledb
    DATASOURCE=SERVIDOR_SQL_2005
    PROMPT=NO
    USER=’user_sql’ PASSWORD='{sas002}EFCF8C140BE5786dsdsdada950E02DAF2722ADD7F34237284469F1′;

    se agradece su ayuda.

  9. Hola, y como puedo conectarme a sas desde oracle, es decir desde un plsql o procedimiento almacenado lanzar una macro sas, se agradece respuesta. Muchas gracias

  10. Hola,
    Me gustaría saber si puedo hacer una conexion en SAS con una bbdd en MYSQL (5.2) que tengo alojada en mi local. no tengo montado un servidor, pero para trabajar por mi cuenta y hacer pruebas de automatizaciones en mi casa, me gustaría poder hacerlo.
    Y otra duda, sería necesario instalarme el SQL SERVER?

    Gracias!

    Alicia

  11. Hola! Quisiera saber que es lo mejor: Consultar tablas desde Oracle o tener la tabla en SAS y desde ahí consultar? Tiene afectación el tiempo de consulta? o como SAS básicamente no es una BD relacional al final lo mejor es tener las tablas en Oracle y desde ahí consultarlas?
    Muchas gracias.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *