Cómo exportar los resultados de una consulta SQL a un archivo CSV en PostgreSQL

En la siguiente publicación comparto cómo exportar los resultados de una consulta SQL realizada en PostgreSQL a un archivo CSV.
Usando la aplicación psql se envian los argumentos nombre_bdd con el nombre de la base de datos dónde se desea ejecutar la consulta, usuario corresponde al nombre de usuario de la base de datos.

El argumento -p permite definir el puerto en dónde está levantado el servidor de PostgreSQL. Por defecto es el puerto 5432.

En la sección de COPY () se coloca la consulta SQL que se desea ejecutar para que luego sus resultados sean exportados a un archivo CSV que se define en el último argumento como nombre_archivo.csv

Cómo saber las 10 tablas más grandes de una base de datos de PostgreSQL

En ésta publicación comparto una consulta SQL para conocer las 10 tablas (Top 10) más grandes de una base de datos PostgreSQL.

SELECT nspname || '.' || relname AS "tabla",
      pg_size_pretty(pg_total_relation_size(c.oid)) AS "tamanio"
FROM  pg_class c
LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE  n.nspname NOT IN ('pg_catalog', 'information_schema') AND
      c.relkind <> 'i' AND
      n.nspname !~ '^pg_toast'
ORDER BY  pg_total_relation_size(c.oid) DESC
LIMIT 10;

Cómo activar el log de Mondrian en Pentaho BI Server para visualizar consultas MDX y SQL

En la siguiente publicación explico el procedimiento para habilitar el log de Mondrian en Pentaho BI Server poder visualizar los logs de las consultas MDX y SQL que se realizan. Esto es muy útil cuando queremos optimizar las consultas SQL.

1. Ir al directorio de configuración de Mondrian

cd biserver-ce/pentaho-solutions/system/mondrian

2. Abir el archivo mondrian.properties para editarlo

3. Ubicar las propiedades mondrian.trace.level y mondrian.rolap.generate.formatted.sql  y asignar los siguientes valores

mondrian.trace.level=1
mondrian.rolap.generate.formatted.sql=true

4. Ir al directorio de aplicaciones web de Tomcat y editar el archivo log4.xml

cd biserver-ce/tomcat/webapps/pentaho/WEB-INF/classes

5. Descomentar los appenders MDXLOG y SQLLOG como se muestra a continuación:

 <appender name="MDXLOG" class="org.apache.log4j.RollingFileAppender">
     <param name="File" value="../logs/mondrian_mdx.log"/>
     <param name="Append" value="false"/>
     <param name="MaxFileSize" value="500KB"/>
     <param name="MaxBackupIndex" value="1"/>
     <layout class="org.apache.log4j.PatternLayout">
       <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
     </layout>
   </appender>

   <category name="mondrian.mdx">
      <priority value="DEBUG"/>
      <appender-ref ref="MDXLOG"/>
   </category>

   <appender name="SQLLOG" class="org.apache.log4j.RollingFileAppender">
     <param name="File" value="../logs/mondrian_sql.log"/>
     <param name="Append" value="false"/>
     <param name="MaxFileSize" value="500KB"/>
     <param name="MaxBackupIndex" value="1"/>
     <layout class="org.apache.log4j.PatternLayout">
       <param name="ConversionPattern" value="%d %-5p [%c] %m%n"/>
     </layout>
   </appender>

   <category name="mondrian.sql">
      <priority value="DEBUG"/>
      <appender-ref ref="SQLLOG"/>
   </category>

6. Finalmente, reiniciar Pentaho BI Server

Cómo acceder como SYSDBA a una base de datos Oracle desde SQuirreL

En ésta publicación explico el procedimiento para acceder desde SQuirreL SQL Client a una base de datos Oracle usando los privilegios SYSDBA

1. Ir a las propiedades de la conexión (Properties)

2. Ir a las propiedades del controlador (Driver Properties), marcar la opción Use driver properties y asignar el valor SYSDBA a la variable internal_logon como se muestra a continuación:

Consulta SQL para conocer la tabla destino de las transformaciones en PDI

En ésta publicación comparto una consulta SQL a ejecutarse sobre el repositorio de la herramienta Spoon de Pentaho Data Integration (PDI_REPO) para conocer las tablas destino (Table Output) de todas las transformaciones creadas en la herramienta Spoon.

select trs.NAME as trs_name, 
       upper(to_char(sta.VALUE_STR)) as dest_table 
from   R_STEP rs, 
       R_STEP_TYPE rst, 
       R_TRANSFORMATION trs, 
       R_STEP_ATTRIBUTE sta 
where  rs.ID_STEP_TYPE = rst.ID_STEP_TYPE 
and    rst.CODE = 'TableOutput'
and    rs.ID_TRANSFORMATION = trs.ID_TRANSFORMATION 
and sta.ID_STEP = rs.ID_STEP and sta.CODE = 'table'
ORDER BY 1

Aspectos a recordar sobre SQL Power Architect

En ésta publicación explico algunos aspectos a recordar sobre SQL Power Architect v1.0.8

Directorio de Instalación
La instalación por defecto de la herramienta se realiza en el directorio: C:\Program Files\SQL Power Architect

Controladores JDBC
Los controladores JDBC se encuentran en el directorio C:\Program Files\SQL Power Architect\jdbc. En éste directorio se actualizan los controladores o se copian nuevos controladores JDBC para que SQL Power Architect trabaje con otras bases de datos.

NOTA: la instalación de la versión 1.0.8 tiene soporte para los siguientes DBMS

• Oracle 8i, 9i y 10g
• PostgreSQL
• SQL Server 2000, 2005, 2008
• MySQL
• DB2
• Derby Embedded
• HSQLDB
• SQLstream
• H2 Database

Cuando se actualicen o copien nuevos controladores JDBC ir al menú File > User Preferences > Local JDBC Drivers marcar el DBMS y asegurarse que se encuentre agregado el controlador JDBC en la herramienta, éste se muestra como un archivo de Java (JAR), por ejemplo: 

Sí no se encuentra el archivo JAR hacer clic en el botón Add JAR... e ir al directorio C:\Program Files\SQL Power Architect\jdbc para seleccionar el JAR correspondiente y seleccionar el Driver.

Creación de Conexiones

Para crear conexiones a bases de datos ir al menú Connections > Add Source Connection > New Connection... o ir a la opción Database Connection Manager > Add 

Agregar Conexiones

Para agregar conexiones a bases de datos ir al menú Connections > Add Source Connection > NombreConexiónCreada.

Creación de Relaciones Entre Tablas

Para crear relaciones, en el área de trabajo presionar SHIFT + R hacer clic en la Primary Key origen y luego hacer clic en la tabla destino.

Forward Engineer

Para construir un modelo físico a partir de un modelo lógico, primero crear la conexión a la base de datos, hacer clic derecho sobre el nombre de la conexión y seleccionar la opción Set As Target Database, luego ir a Tools > Forward Engineer

NOTA: no olvidar hacer clic en Properties y seleccionar nuevamente el campo Database Type.

Instalación y Configuración de SQuirreL SQL Client en Windows

SQuirreL SQL Client es un cliente universal de bases de datos que permite conectarse a cualquier base de datos siempre y cuando ésta tenga un controlador JDBC, entre las más populares están: Oracle, MySQL, PostgreSQL, DB2, HSQLDB, Sybase, Informix

Con SQuirreL se pueden realizar consultas SQL tanto de definición de datos (DDL) así como también de control de datos (DCL)

Instalación

Para instalar SQuirreL tenemos que realizar los siguientes pasos:

1. Descargar SQuirreL del siguiente link: http://squirrel-sql.sourceforge.net/#installation

NOTA: previo a la instalación asegurarse tener instalado y configurado correctamente el JDK. En la publicación Instalación y Configuración del JDK se explica el procedimiento.

2. Se descargará un archivo de nombre squirrel-sql-x.x.x-standard.jar, en dónde x.x.x representa la última versión de la herramienta. Guardar el archivo en un directorio. Por ejemplo: c:\downloads\squirrel-sql-x.x.x-standard.jar

3. Abrir una consola de comandos (Inicio > Ejecutar > cmd)

4. Ir al directorio donde se descargó SQuirreL en el punto 2, en mi caso c:\downloads\

5. Ejecutar el siguiente comando: java -jar squirrel-sql-x.x.x-standard.jar

6. Seguir los pasos de la instalación y en la opción de selección de paquetes a instalar, seleccionar los siguientes:

Optional Plugin: PostgreSQL
Optional Plugin: Session Scripts
Optional Plugin: Smart Tools
Optional Plugin: SQL Parametrisation
Optional Plugin: SQL Replace
Optional Plugin: SQL Validator

7. Hacer clic en Next hasta finalizar con la instalación. La instalación por defecto se realizará en el siguiente directorio: C:\Program Files\squirrel-sql-x.x.x\

Configuración

En éste paso menciono el procedimiento para preparar a SQuirreL para que se conecte a cualquier base de datos usando un controlador JDBC.

1. Descargar los controladores JDBC para las bases de datos que se desea conectar.

2. Por ejemplo, para PostgreSQL descargar del siguiente link: http://jdbc.postgresql.org/download.html

NOTA: de preferencia descargar el controlador JDBC Tipo 4

3. El controlador descargado (postgresql-x.x-xxx.jdbc4.jar) copiar al directorio lib del directorio de instalación de SQuirreL, en mi caso: C:\Program Files\squirrel-sql-x.x.x\lib

4. Realizar el mismo procedimiento para todas las bases de datos que se desee conectar: Oracle, MySQL, PostgreSQL, etc.

5. Iniciar SQuirreL desde el Menú de Inicio, haciendo clic en el ícono del escritorio, o ejecutando el archivo C:\Program Files\squirrel-sql-x.x.x\squirrel-sql.bat