Export large datasets with MySQL

Today’s entrance is dedicated to export large datasets, that is, when the outcome of a SQL query is too large with millons of rows. I will be using a database with data from a fotovoltaic installation. Data is stores in a data table, and I want to extract a selection of parameters.

The following SQL sentence will query data every 5 minutes of a few parameters:

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

Execute the query, and I got the result.

In some cases,when the recordset is too large, to can notice that the connection will be desconected, obtaining no recordset. This is due to the connector, that it cannot support the recordset. If you got this error, the best way is to export by using SQL sentences, no graphical MySQL admin, such as heidiSQL.

Inside heidiSQL, one way to export is be doing right clic on the results, and select export grid:

Write the name of the file, the options, and … you’re done!

Export to CSV from the console

A second approach to export the recordset is from the console, by using INTO OUTFILE in your SQL sentence:

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

And, here you have it on the console.

The CSV generated is presented here:

As you can see, there are no header on it, so let’s modify the SQL to include it.

Second version of SQL using INTO OUTFILE

If you want to include the headers to your CSV file, you need to modify the SQL sentence, by using UNION. The first SELECT will be dedicated to the header while the second is for the data (the version one SQL sentence):

And now, you have the CSV file with headers.

Next entrance will be the same, but using Python.

I hope it helps you and … happy coding!