Loading data from a database using JDBC

This example shows how to connect an existing database accessible via JDBC, and create and populate a GoodData data set with results of a SELECT statement performed against an embedded database of companies’ fundamentals.

Run gdi.sh with the fundamentals.txt script. The script uses the fundamentals.config.xml configuration.

Windows:

c:> bin\gdi.bat -u <username> -p <password> examples\jdbc\fundamentals.txt
Project id = 'yz5uq4am9ip3vxiou6m8xntd81r8qdh6' created.

Unix like OS (Linux, Mac OS X and others):

$ ./bin/gdi.sh -u <username> -p <password> examples/jdbc/fundamentals.txt
Project id = 'yz5uq4am9ip3vxiou6m8xntd81r8qdh6' created.

What Next?

This particular example is built using the embedded Derby database.

When building an integration scenario connecting to your own database you can start with modifying this example.

Configure database access

In order to set up access to your database, grab an appropriate JDBC 4 driver (can be downloaded from your database vendor’s website, ask you database administrator if unsure) and place it to the lib/ folder of the installation directory.

Tip: for your convenience, this distribution has already bundled a couple of freely redistributable JDBC 4 drivers in the lib/ folder as follows:

• MySQL
• PostgreSQL
• Apache Derby
• jTDS (open source implementation of a JDBC connector for SQL Server and Sybase databases)

Then copy the fundamentals.txt script to myjdbc.txt (or pick up a better name) and find the UseJdbc command. Modify the driver and url parameters, the driver will refer to the JDBC driver’s Java class and url will be a JDBC URL of the target database.

If unsure, please consult your database administrator or the following list of common JDBC drivers and sample JDBC URLs:

• MySQL

  • driver: com.mysql.jdbc.Driver
  • sample url: jdbc:mysql://localhost/mydatabase

• PostgreSQL:

  • driver: org.postgresql.Driver
  • sample url: jdbc:postgresql://localhost/mydatabase

• Apache Derby

  • driver: org.apache.derby.jdbc.EmbeddedDriver
  • sample url: jdbc:derby:/path/to/my/data/folder

• SQL Server - driver: net.sourceforge.jtds.jdbc.Driver - sample url: jdbc:jtds:sqlserver://localhost:1433/AdventureWorks

Tip: If you’re retrieving date fields from MySQL and some of them could be empty, use the zeroDateTimeBehavior parameter in the JDBC URL. (This is specific to MySQL.)

jdbc:mysql://<host>/<database>?zeroDateTimeBehavior=convertToNull

Define your data set

The JDBC data set is a result of an SQL SELECT statement. Test this statement gainst your database and put it to the query parameter of the UseJdbc command. Don’t forget to specify your database credentials using the username and password parameters of the UseJdbc file.

The data set description is expected in the XML configuration file, such as the fundamentals.config.xml file from this example. Change the configFile parameter of the UseJdbc command to a new file name (let’s use myconfig.xml) and save the script file.

Generate new configuration file

This configuration file can be generated by the GenerateJdbcConfig command. An example of this command that was used to generate the fundamentals.config.xml file is provided in the fundamentals.txt script; note the command is commented out as we don’t need to run it again and again after the configuration file was generated.

Copy the GenerateJdbcConfig command to a new file and save it as configure.txt. Set the driver, url, username and password parameters as you have put in your UseJdbc command. Specify the path to the configuration file by editing the configFile parameter, set it to the same value as you have previously put into the UseJdbc command (e.g. myconfig.xml).

Then you can generate your configuration file by executing your configure.txt with the bin/gdi.sh or bin/gdi.bat tool as follows:

Windows:

c:> bin\gdi.sh -u <username> -p <password> configure.txt

Unix like OS (Linux, Mac OS X and others):

$ ./bin/gdi.sh -u <username> -p <password> configure.txt

Review the configuration file

Finally, review the generated configuration file (you have specified its name via the configFile parameter of the GenerateJdbcConfig command). Please pay attention to following cases:

- All fields you want to aggregate have set ldmType property as FACT - The fields to be used to break down your aggregated numbers have ldmType set to ATTRIBUTE. - If there is a field that can be used as a unique identifier of each row set its ldmType to CONNECTION_POINT. - If your data set contains any date fields, their ldmType should be specified as DATE and format should be yyyy-MM-dd. In this case, your myjdbc.txt script should also create a date dimension for every such field and this date dimension should be referenced by the schemaReference attribute of the configuration file, please refer to the Quotes example for more details