Exportar grandes datasets en MySQL

La entrada de hoy está dedicada a exportaciones de grandes recordsets, esto es, que el resultado obtenido con una consulta es …. de varios miles o millones de filas. Como ejemplo, voy a usar uno de los proyectos en los que esty haciendo un trabajo de investigación relacionado con instalaciones fotovoltaicas. Los datos los tengo cargados en la tabla datos, y ahora quiero extraer una selección de ellos.

La consulta trata de recuperar los datos cada 5 minutos de unos cuantos parámetros:

select fecha as "date"
,SUM(CASE WHEN idparametro = 2 THEN valor ELSE NULL END) as "IntSolIrr(W/m^2)"  ,SUM(CASE WHEN idparametro = 221 THEN valor ELSE NULL END) as "Irr Corrected(W/m^2)"
,SUM(CASE WHEN idparametro = 172 THEN valor ELSE NULL END) as "NRG AC (I·V)(W·h)"  ,SUM(CASE WHEN idparametro = 163 THEN valor ELSE NULL END) as "NRG AC (P)(W·h)"
,SUM(CASE WHEN idparametro = 15 THEN valor ELSE NULL END) as "Pac(W)"  ,SUM(CASE WHEN idparametro = 4 THEN valor ELSE NULL END) as "TmpAmb C(°C)"
,SUM(CASE WHEN idparametro = 19 THEN valor ELSE NULL END) as "Uac(V)"
FROM datos
WHERE anulado = 0 AND idinstalacioninversor = 413
AND (idParametro = 2 OR idParametro = 221 OR idParametro = 172 OR idParametro = 163 OR idParametro = 15 OR idParametro = 4 OR idParametro = 19)
AND fecha BETWEEN "20130101000000" AND "20131231235959" GROUP BY fecha ORDER BY fecha ;

Ejecuto la consulta, y …. obtengo el resultado.

En algunos casos, cuando el resultado es mayor, puede dar el caso de que directamente se nos desconecte el proceso y no obtengamos resultado ninguno. Esto se debe a que el conector que trabaja con el administrador no es capaz de soportar el resultado devuelto. En estos casos, hay que recurrir a otras formas de exportar los resultados sin administrador GUI de MySQL.

Con la consulta y los resultados, desde heidiSQL tengo la opción de exportar:

Selecciono el nombre del fichero, así como el resto de opciones, y … a esperar que se guarde el resultado.

Exportar a CSV desde consola

La segunda de las soluciones para exportar esta consulta es, desde la consola, mediante el uso de INTO OUTFILE.

select fecha as "date"
,SUM(CASE WHEN idparametro = 2 THEN valor ELSE NULL END) as "IntSolIrr(W/m^2)"  ,SUM(CASE WHEN idparametro = 221 THEN valor ELSE NULL END) as "Irr Corrected(W/m^2)"
 ,SUM(CASE WHEN idparametro = 172 THEN valor ELSE NULL END) as "NRG AC (I·V)(W·h)"  ,SUM(CASE WHEN idparametro = 163 THEN valor ELSE NULL END) as "NRG AC (P)(W·h)"
 ,SUM(CASE WHEN idparametro = 15 THEN valor ELSE NULL END) as "Pac(W)"  ,SUM(CASE WHEN idparametro = 4 THEN valor ELSE NULL END) as "TmpAmb C(°C)"
 ,SUM(CASE WHEN idparametro = 19 THEN valor ELSE NULL END) as "Uac(V)"
INTO OUTFILE "l:/result.csv"
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
 FROM datos
 WHERE anulado = 0 AND idinstalacioninversor = 413
 AND (idParametro = 2 OR idParametro = 221 OR idParametro = 172 OR idParametro = 163 OR idParametro = 15 OR idParametro = 4 OR idParametro = 19)
 AND fecha BETWEEN "20130101000000" AND "20131231235959" GROUP BY fecha ORDER BY fecha ;

Y aquí, en la consola.

En este segundo caso, el resultado obtenido es el siguiente:

Puede comprobarse que NO aparece el nombre de las columnas.

Segunda versión usanto INTO OUTFILE

Así que, si además de los datos se desea mantener la cabecera, hay que modificar aun más la sentencia SQL, y usar una unión, donde la primera SELECT es el nombre de las columnas.

Ahora sí tenemos el resultado con las cabeceras.

La siguiente entrada estará dedicada a la exportación, exáctamente igual que esta entrada, pero desde Python.

En fin, espero que os sirva.