Example: writing metrics to an Excel file

Overview

In this example, we're going to use the Metrics code filter for Postgres and write the metrics to an Excel file.

To do so, we'll be using the Apache POI library, which will demonstrate the use of Gallium Data's library system.

Important: this example assumes that you have a running Gallium Data with a Postgres database. If that's not the case, go over the tutorial to set it up.

Step 1: Add Apache POI to your repository

Go into Gallium Data and select the Libraries page (left nav).

Select the Search tab, enter org.apache.poi in the Organization field and poi-ooxml in the Artifact field, and click Find Libraries. You should see a list of versions of this library. Select 5.0.0 and add it to the repository.

You've just made the Apache POI library available to all your filter code, so let's use it now.

Note: the Apache POI library is quite large, so the first time you publish to Gallium Data could take a minute or more while the library and its numerous dependencies are being downloaded.

Step 2: Add a filter

Now open your project and create a new duplex filter of type Postgres metrics code. This filter will collect metrics on Postgres requests and responses, and call our code every once in a while (every minute by default) with some metrics for us to display in whatever manner we like.

Now enter the following code for this filter:

const fc = context.filterContext;

if ( ! fc.XSSFWorkbook) {

fc.XSSFWorkbook = Java.type("org.apache.poi.xssf.usermodel.XSSFWorkbook");

fc.FileOutputStream = Java.type("java.io.FileOutputStream");

fc.tmpDir = Java.type("java.lang.System").getProperty("java.io.tmpdir");

}

const wb = new fc.XSSFWorkbook();

const sheet = wb.createSheet("Gallium Data metrics");

const headerRow = sheet.createRow(0);

headerRow.createCell(0).setCellValue("SQL");

headerRow.createCell(1).setCellValue("Avg rows");

headerRow.createCell(2).setCellValue("Avg bytes");

headerRow.createCell(3).setCellValue("# execs");

for (let i in context.stats["Statement infos"]) {

let stmt = context.stats["Statement infos"][i];

let row = sheet.createRow(i + 1);

row.createCell(0).setCellValue(stmt['Statement']);

row.createCell(1).setCellValue(stmt['Average rows']);

row.createCell(2).setCellValue(stmt['Average bytes']);

row.createCell(3).setCellValue(stmt['Num execs']);

}

var outName = fc.tmpDir + "Report" + (new Date().getTime()) + ".xlsx";

const outFile = new fc.FileOutputStream(outName);

wb.write(outFile);

log.debug("Metrics written to Excel file: " + outName);

When this code is invoked by Gallium Data, there will be a variable called context.stats defined (see the documentation for the Metrics code filter for details). The code simply creates a new Excel file and adds some of the data available in the context.stats variable, then writes the Excel file to disk.

Click Publish -- note: the publishing can take a fairly long time the first time because Gallium Data has to download many different Java libraries which are dependencies of Apache POI (which is a relatively heavy library).

Step 3: Get some metrics

Once you have published to Gallium Data, you can exercise the database a bit using pgAdmin and, every minute or so, a new Excel report will be written to disk (as long as there is database activity).

When a report is created, the name of the file will be output to the log, which you can see with:

docker logs gallium-data

Remember that the file is created in the Docker container. You can retrieve it with docker cp.

As an example, if the log says:

[DEBUG uselog] 18:57:59.557 [forwardFromClientThread-50] - Metrics written to Excel file: /var/tmp/Report1619147275638.xlsx

You can retrieve the file to your local drive with:

docker cp gallium-data:/var/tmp/Report1619147275638.xlsx .

Don't forget the period at the end!

The Excel file should look something like:

Step 4: Going further

If you feel like playing around with this, there are a number of things you can do at this point:

you can add more information to the spreadsheet, as the example only uses some of the data available from the context.stats object. See the documentation for the Metrics code filter for details.
you can format the spreadsheet to give it styles, colors, fonts, etc... See the documentation for Apache POI for many excellent examples (which are in Java but trivially translated to JavaScript).
you can reuse the same Excel file and create additional sheets for additional data.
if you feel adventurous, you could even add graphs to the spreadsheet, though be aware that Apache POI only supports limited graph types.
you could use an existing Excel file (saved in a format compatible with Apache POI) as a template.
you can also explore using other parts of Apache POI to produce Word or PowerPoint files.

Google Sites

Report abuse