Blog: Tech Poet

String Encoding

Thu, 01 Jan 1970 00:00:00 +0000

String Encoding for Talend/JaspersoftETL for Basic Authentication/md5/sha-256

What is a convenient way to encode usernames/passwords for use in my ETL jobs?

First copy the java code on this Github Gist into yourclipboard:

Second Create a routine calledhashingUtils (name is important!). Paste the Java code from above and save theroutine

http://1.bp.blogspot.com/-sao3plG5UwI/VUzr3a2RoVI/AAAAAAAADlk/B5B2ckDyWj4/s200/routine.png

Third you can now use the following methodsin any component:

hashingUtils.basicAuth("username","password")

hashingUtils.md5encode("string")

hashingUtils.sha256encode("string")

For example, in a tHttpRequest Component:

http://1.bp.blogspot.com/-sQE7in1n64A/VUzsJJOwddI/AAAAAAAADls/Invobjf0YEM/s400/example.png

Dealing with Nested Documents in MongoDB and Talend (aka Baking a Cake)

Thu, 01 Jan 1970 00:00:00 +0000

Abstract:

MongoDB is a very popular document database that gives you a huge amount of flexibility when it comes to storing data. On the other hand, the traditional relational model is far less flexible in how data is stored - you're limited to columns and rows. Sometimes you want to go from a flexible model like MongoDB to a relational one, that's what this post attempts to explain using Talend/JaspersoftETL (ETL tools).

I do not want to get into the relational vs non-relational model argument in this post - it's only an example if you need to do this for some reason...I'm pro choice :)

Scenario:

We have a JSON document in MongoDB that looks like this:

 {        "id": "0001",        "type": "donut",        "name": "Cake",        "ppu": 0.55,        "batters":             {                  "batter":                       [                            { "id": "1001", "type": "Regular" },                            { "id": "1002", "type": "Chocolate" },                            { "id": "1003", "type": "Blueberry" },                            { "id": "1004", "type": "Devil's Food" }                       ]             },        "topping":             [                  { "id": "5001", "type": "None" },                  { "id": "5002", "type": "Glazed" },                  { "id": "5005", "type": "Sugar" },                  { "id": "5007", "type": "Powdered Sugar" },                  { "id": "5006", "type": "Chocolate with Sprinkles" },                  { "id": "5003", "type": "Chocolate" },                  { "id": "5004", "type": "Maple" }             ]   }

We want to put the document into three different tables, for use in a relational database:

Main Table:

ID	TYPE	NAME	PPU
0001	donut	cake	0.55

Batter Table:

BATTERID	BATTERTYPE	CAKEID
1001	regular	0001
1002	chocolate	0001
1003	blueberry	0001
1004	devil's food	0001

Topping Table:

TOPPINGID	TOPPINGTYPE	CAKEID
5001	None	0001
5002	Glazed	0001
5005	Sugar	0001
5007	Powdered Sugar	0001
5006	Chocolate with Sprinkles	0001
5003	Chocolate	0001
5004	Maple	0001

Talend tMongoDBInput Component

The tMongoDBInput component is what we'll use to extract the data from MongoDB and input into Talend. This is fairly straightforward, you can configure the connection and then you will have to map some fields. We map id, type, name, ppu as regular fields. The batter and topping fields are somewhat special.

Toppings:

The toppings field will be mapped a string and will contain an object in each row that we'll need to deal with later

Batters:

Batters is a nested document within another nested document and we'll need to define a parent node path. tMongoDBInput does not allow you to use node notation to select fields (like batters.batter) so you'll define a parent node like this:

In the end the schema for tMongoDBInput will look like this:

tMap Component - Splitting

Now we'll use a tMap component to split the outputs into three (main, topping, batter)

tExtractJSONFields - Expanding Documents
So now we have to deal with the special fields, batter and topping - each one of them contains a list that we want to denormalize into it's own rows. We can use the tExtractJSONFields component for this. The component has a couple of key things: schema, Loop XPath Query and XPath query. This determines what fields to map.

Schema:

Then set the Xpath queries:

And finally glue it all together:

Note that we have 3 input rows, then for each batter and topping we are extracting.

If you are interested in this example, you can download it here:

Data: This is a JSON object you can import with mongoimport (like mongoimport --jsonArray --collection nestedSample < doc.json)
JaspersoftETL Job: This job works with JaspersoftETL 5.4.1 Big Data Edition (or presumably the Open Source Big Data edition of Talend)

Geospatial Features in MongoDB with Jaspersoft

Thu, 01 Jan 1970 00:00:00 +0000

Lately I have been posting technical examples to the Jaspersoft community wiki instead of this blog. I'll cross-post this one because there are not many examples of doing this. When working with Geospatial data, I found the $near operator to be pretty sweet. Let me show you by example.

In the screenshots below you'll see that I constructed a report that shows "cases" on a map. When you click on a specific case you will be taken to a detail level report. The detail level report will show you cases nearby for that service. So for example, if someone reports a broken street light on Market Street, this might help you locate similar cases.

http://3.bp.blogspot.com/-AmWZsRUMTIc/UptlZkYSuCI/AAAAAAAADVI/ttNd-SOkUzo/s400/Screen+Shot+2013-12-01+at+4.31.44+PM.png

http://2.bp.blogspot.com/-JCz2rNSHVx4/UptlZpNmH-I/AAAAAAAADVE/9OLf8DICj4c/s400/Screen+Shot+2013-12-01+at+4.28.52+PM.png

So how is it done?

Quite simple really, take a look at this mongodb query:

Line 8: The $P{location} parameter is a java.util.List that contains longitude and latitude coordinates, something like [-122.4194200,37.7749300]Line 11: How many meters from the center (specified in line 8) should it searchLine 13: The $P{service} parameter is a string containing the name of the Service, in the screenshot above it's Missing SignYou will need to have an index on your location field, like so: ensureIndex({"location" : "2dsphere" } )

That's it, I've never really done anything with geospatial data and this seemed like it was a piece of cake.

References:

Download the example data and reports from the Jaspersoft Community Wiki
Read about the MongoDB $near operator
Watch me present on this subject

Backing Up JasperServer in Linux

Thu, 01 Jan 1970 00:00:00 +0000

This is a sysadmin how-to for backing up JasperReports Server (formerly JasperServer) and it's database (MySQL only for now).

I wrote a fairly robust script to achieve this task. It will read the database configuration from your default_master.properties file, work with several versions of JasperServer, delete old backups, write to the syslog and export the repository contents to file.

First drop my backup script into your \buildomatic folder if you're using 4.0 and abovie, 3.7 and below put in your /scripts folder.

Once the script is in place edit the variables at the top of the script:

This is to signify that you're using JasperServer 3.7.1 and below:
IS_PRE_4=false

This is a name for the product:
PRODUCT_NAME="JRS-4.0-Pro"

This is where backups will be stored:
BACKUP_DEST="/home/ernesto/Dropbox/Backups"

This will delete old backups if set to true
DELETE_OLD=true

How many days to keep old backups
DAYS=120

Now add it to your crontab. I will do it once a week in a lazy manner so will add the script to vim /etc/cron.weekly/js-backups

#!/bin/bash
/usr/local/jaspersoft/jasperserver4/buildomatic/js-backup.sh

The script will log to /var/log/messages:

http://4.bp.blogspot.com/-V2KqR4Isxwg/Tajnswn4XUI/AAAAAAAAArk/jW6hQy_gcy4/s640/log.png

The output files will look like this, I chose to stick them in my Dropbox folder for off-site backups:

http://1.bp.blogspot.com/-Nvnq569JUUQ/Tajn9xUTLuI/AAAAAAAAAro/nmKAmX-GJ0g/s400/backup_dir.png

You can download the script from this jasperforge forum post or view it below:

Code snippet - JasperServer Backup Script on Snipplr

Icinga Reporting with JasperReports Server - Part 1: Installation

Thu, 01 Jan 1970 00:00:00 +0000

A quick overview of how to install and configure the reporting add-on packages for Icinga (a Nagios Monitoring Fork), written for JasperReports Server.

Icinga Reporting with JasperReports Server - Part 2: Usage + Basic Report Development

Thu, 01 Jan 1970 00:00:00 +0000

Short tutorial, shows how to schedule a report, how to change the way you are prompted for parameters and some basic editing of reports within iReport

Using tGoogleAnalyticsInput in JasperETL/Talend

Thu, 01 Jan 1970 00:00:00 +0000

This is a short article on getting Google Analytics into JaspersoftETL/Talend ETL tools. Google Analytics has a nice interface and many great ways of analyzing web traffic, it'd be hard to beat the work Google did there. The purpose of this connector would be to bring the data into your own data warehouse and join it with other intelligence you might have. A great way to display the aggregated data would be with a BI tool like Jaspersoft.

I'm writing because the help for the tGoogleAnalyticsInput is in French and incomplete.

So let's get started. You'll need JasperETL 4.1.2 or older (commercial editions here, open source here).

Installing the plugin:

http://4.bp.blogspot.com/-L4o2riZbb8Y/TtgY5A-hoiI/AAAAAAAABlU/MJC_JhOi8N4/s1600/install.png

Create a new Job Design and drag the tGoogleAnalyticsInput component from the Palette (under Internet):

http://2.bp.blogspot.com/-3Nui1tv4YKo/TtgaOHVCR2I/AAAAAAAABlc/5EXfIquQkiM/s1600/component.png

Configure the component. First start with finding some fields you'd like to use from the Google API: http://code.google.com/apis/analytics/docs/gdata/dimsmets/dimsmets.html. Note that you need to have some metrics (can't be all dimensions, vice versa).

Here's an example schema (you'll have to put in the fields yourself):

http://2.bp.blogspot.com/-IpIT5eUJln0/TtgdoTPN8lI/AAAAAAAABls/RLELzw_lrb8/s1600/schema.png

Now configure the component. In order to get your Profile ID (note it doesn't start with UA) read the cryptic Google Documentation about it. I got mine by examining the URL while logged into GA and looking at the &id= parameter.

The "value" column comes directly from the Google API and will also have to be filled in. I've also found that I HAVE to filter on period. You can do 1980-2020 if you want to include everything.

http://2.bp.blogspot.com/-ql9dEfeMvUE/Ttgdkq_k8LI/AAAAAAAABlk/81KWdYA3hds/s1600/component_configure.png

Now you can run the the job and see your visitors:

http://2.bp.blogspot.com/-QNd9JPccXr8/TtguHWtNXiI/AAAAAAAABl0/_uJVbva41xU/s1600/output.png

Funny enough. If you're reading this right now then you will eventually flow through the GA database and the very job I'm displaying above ;)

Flush Jaspersoft OLAP Cache from Talend/JasperETL

Thu, 01 Jan 1970 00:00:00 +0000

JaspersoftETL is a Java code generation tool designed to load your operational data into a data warehouse and get it ready for reporting and analysis. Jaspersoft OLAP takes advantage of aggregate tables and an in-memory cache to speed up queries. If you're running a nightly job to load the DW, it's a good idea to clear the OLAP cache at that time, otherwise your users will not take advantage of new data.

There are several ways to clear the OLAP cache:

Login with superuser privileges and display the OLAP Settings page.
In the OLAP Settings panel, click Flush OLAP Cache.
Using utilities such as cURL and wget, send an HTTP request to this URL: http://server:8080/olap/flush.html?j_username=superuser&j_password=
Restart the application server instance.
Call the Java API.

I've created a small component that allows the clearing of cache via the URL method from a JasperETL/Talend job. Essentially it will prompt you for a username and password (and note that only superuser can clear the cache by default) and a server URL. The URL should point to something like http://server:8080/jasperserver-pro/olap/flush.html

You may find the tJasperOLAPCache component on talendforge.org

http://1.bp.blogspot.com/-yyetbAxaLXo/Tuqk2yNLmgI/AAAAAAAABpk/KE5nvTvqVSE/s400/Screen+shot+2011-12-15+at+5.29.28+PM.png

Another useful thing to do would be to use the tJasperServerExec plugin to re-populate the cache.

TalendDate

Thu, 01 Jan 1970 00:00:00 +0000

Not a lot of examples around TalendDate so thought I'd post a couple of quick examples:

Today's date:
TalendDate.getDate("yyyy-MM-dd")
Returns:
2012-04-12

Yesterday's date:
TalendDate.addDate(TalendDate.getDate("yyyy-MM-dd"),"yyyy-MM-dd",-1,"dd")
Returns:
2012-04-11

Tomorrow's date:
TalendDate.addDate(TalendDate.getDate("yyyy-MM-dd"),"yyyy-MM-dd",1,"dd")
Returns:
2012-04-13

Date format:
CC - century
YY - year
MM - month
DD - day of month
hh - hour
mm - minutes
ss - seconds

Return types:
string!

Other methods:

TalendDate.compareDate(myDate,myDate2,"yyyy-MM-dd")
TalendDate.diffDate(myDate,myDate2,"MM")
TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",myDate)
TalendDate.setDate(mydate,newValue,"MM")
TalendDate.getFirstDayOfMonth(mydate)
TalendDate.getLastDayOfMonth(mydate)
TalendDate.getRandomDate("2007-01-01","2008-12-31")
TalendDate.parseDate("yyyy-MM-dd HH:mm:ss","")

Jaspersoft Report Writing Best Practices

Thu, 01 Jan 1970 00:00:00 +0000

The following is a set of guidelines for developing reports with iReport and JasperReports Server. It was compiled with the help of Frau Klein

Best Practice - report resources:

http://1.bp.blogspot.com/-UyV8ur79eis/T-RLMXYsTHI/AAAAAAAACsc/fSMu64z_QFs/s1600/lightbulb.pngReport units contain references to the resources they use: images, styles, sub-reports, queries, input controls - example

Why?

Promotes re-usability (nothing is local to report unit). Easy maintenance around moving report units, replacing resources in a mass scale.

Best Practice - references:

http://1.bp.blogspot.com/-UyV8ur79eis/T-RLMXYsTHI/AAAAAAAACsc/fSMu64z_QFs/s1600/lightbulb.png Never hard code an entire repository path to resource, the image repo:/reports/images/image.png should come in as a reference like repo:image.png - example

Why?

When you use js-export with --uris option it only resolves repository dependencies, it doesn't look for expressions that maybe use an absolute repository path

Best Practice - styles:

http://1.bp.blogspot.com/-UyV8ur79eis/T-RLMXYsTHI/AAAAAAAACsc/fSMu64z_QFs/s1600/lightbulb.pngDo not hard code any style information to JRXML. Develop central style templates (jrtx, jrctx) for all your reports, add as a reference to reports - example

Why?

Fast change management, when the company style changes you will edit a couple of resources vs hundreds. Note that conditional styles are not in the jrtx

Best Practice - logged in user

http://1.bp.blogspot.com/-UyV8ur79eis/T-RLMXYsTHI/AAAAAAAACsc/fSMu64z_QFs/s1600/lightbulb.png Don't log in as superuser, use an organization specific user like jasperadmin - example

Why?

Complicates repo: paths. If logged in as superuser you must pass in the full uri ex: repo:/organizations/organization_1/reports/drill_down_report where being part of an organization you would just use repo:/reports/drill_down_report. Also, it's good for testing around multi-tenancy to be a "real" user.

Best Practice - input controls

http://1.bp.blogspot.com/-UyV8ur79eis/T-RLMXYsTHI/AAAAAAAACsc/fSMu64z_QFs/s1600/lightbulb.pngCreate shared input controls if they apply to more than one report, bring them in as a link - example

Why?

Reduce development time, useful for dashboards. Note: Datasources won't switch if your sub-report switches

Best Practice - Use JNDI Datasources (contributed by Guillaume AUTIER)
http://1.bp.blogspot.com/-UyV8ur79eis/T-RLMXYsTHI/AAAAAAAACsc/fSMu64z_QFs/s1600/lightbulb.png
Have a unique entry point to change datasource details (ip:port:credentials)
Why ?
When deploying the server in a different location, you would only have to change the datasource definition in one file: context.xml (this is valid in tomcat, see your app server settings for JNDI)

When using export scripts the datasource will remain untouched, so you can use the same export on multiple servers without having to change the datasource detail after the import

You can take advantage of Application Server pooling instead of creating many connections to the database (performance).

Best Practice – parametrized references with sub-reports (contributed by Guillaume AUTIER)

http://1.bp.blogspot.com/-UyV8ur79eis/T-RLMXYsTHI/AAAAAAAACsc/fSMu64z_QFs/s1600/lightbulb.pngUsing the previous advice you can also access a report (with subreports) both locally and on the server without touching your expressions.
Why?
This is useful when you want to quickly preview in iReport a report taken from the server. We assume that all reports and subreports .jasper files resides in the same local folder.

Create a parameter $P{SUB_REPORTDIR} (string type, use as prompt, default value “repo:”)
Then have the following formula in your subreport expressions

$P{SUB_REPORTDIR }.equals(“”)?”mySubReport.jasper”: $P{ SUB_REPORTDIR} + ”mySubReport.jrxml”

Publish your report on the server as usual : do not create(or link) an input control for the SUB_REPORTDIR parameter

Run your report :

In iReport you will be prompted for the SUB_REPORTDIR parameter leave it blank.
On the server : Without an input control for the SUB_REPORTDIR parameter the server will then take the default.

If you have any other best practices tips, post them in the comments and I'll include into the post, happy reporting!

MongoDB Aggregation Queries Basics on MongoLab

Thu, 01 Jan 1970 00:00:00 +0000

What is MongoLab?

MongoLab is a hosted MonoDB service that offers one free database to developers wanting to learn the technology. It's a very elegant way to start learning or developing a small application. I started using this earlier in the year and had created a couple of collections on it.

What is the MongoDB Aggregation Framework?

Traditionally in SQL databases you have the concept of a GROUP BY and an aggregation function like average or sum. In MongoDB, aggregation wasn't ever as easy, in order to do any aggregation you'd have to write your own complicated MapReduce jobs. Along comes MongoDB version 2.2, which includes the aggregation framework.

So consider this SQL statement:

SELECT sum(duration) FROM calls GROUP BY disposition

And here it is translated to MongoDB, I've attempted to color code the translations:

   }runCommand: {   aggregate : 'calls',   pipeline : [                {                  $group : {                    _id : '$disposition',                    durations: {                             $sum : '$duration'                                }                            }                 }               ]             }}

Easy enough! There's much more to this of course. Here's a great reference: http://docs.mongodb.org/manual/reference/aggregation/

MongoLabs and Aggregation Framework

So to use this on MongoLabs you'll have to start a new database, old ones are on an older version, according to this helpful article

All free/starter databases created after Nov 30, 2012 will be running 2.2.x will have Aggregation Framework Support

Why Do I care?

Well I had some mongoDB Jaspersoft samples that would bring in an entire un-aggregated dataset into memory and then use the JasperReports library to do aggregations in-memory. This is fine and dandy if you're dealing with tens of thousands of records. When you move into millions of records this becomes a bit harry and I'd rather my database do the work!

So using my example query from above, I was able to create this very cool looking report in iReport:

http://3.bp.blogspot.com/-HzfF8TdDjGs/UMHe79U7pGI/AAAAAAAACtc/fnzJR9libs0/s640/Screen+Shot+2012-12-07+at+11.35.15+AM.png

Where can I learn more about Reporting and Analytics on MongoDB?

First place to look is the Jaspersoft community wiki: http://community.jaspersoft.com/wiki/mongodb

My colleague, Matt Dahlman has some excellent examples on his blog: http://mdahlman.wordpress.com/tag/mongodb/

Report Bursting in JasperReports Server

Thu, 01 Jan 1970 00:00:00 +0000

What is report bursting?

Take this example. You have a report that shows all the accounts that a particular employee owns in your company. The user can log into your reporting system and ask for the report, he'll be asked to enter his employee ID or maybe it will be filled in automatically based on who he/she is.

Now your boss asks you (a BI professional) to send each employee their list of accounts on a weekly basis...yikes! You need to automate this. What do you do?

As of Jaspersoft version 5.0 there's no UI built into the web-application to handle the complex task of report bursting. Designing an UI that let's you iterate through any possibility of inputs, read any possibility of values and output to any possible medium is tricky, for now you have to build your own utilities using the rich SOAP or REST APIs that are available in JasperReports Server. The easiest way I found to do this using is using JaspersoftETL (Talend) and a component called tJasperServerExec

The custom component calls the API of Jaspersoft to handle the report execution end. There are more components like this coming one day soon that let you use the scheduler built into Jaspersoft, for this example we use the mail sending capabilities of Talend.

What the video doesn't cover is scheduling of this job. The community editions of ETL rely on you to schedule jobs yourself using cron or the Windows scheduler. The commercial editions allow you to publish the job to the Administration Server which have the ability to schedule from a web UI.

So here's how it's done:

New MongoDB Components in Talend/JaspersoftETL

Thu, 01 Jan 1970 00:00:00 +0000

I uploaded two videos with examples of using the tMongoDBInput and tMongoDBRow components that are shipping with JaspersoftETL 5.2.2 (Plus edition) and Talend 5.2.2+

I recommend you change the quality to 720p to watch the videos in better quality. I hope you find them useful!

Part 1: Using tMongoDBInput (and using Talend's built in aggregation engine):

Part 2: Using tMongoDBRow (and the aggregation framework):