Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today

Connect Google Cloud SQL

MVP & Luminary
MVP & Luminary

Connect Google Cloud SQL

Google Cloud SQL is a new service which runs mySQL databases in Google’s cloud. You can try out this service without charge until June 2013 under a introductory trial.

Usually, you would need a cloud database to develop and run an applications with the Google App Engine platform. Of course, there should be a possibility to connect Cloud SQL from external applications. This is possible with the command line tool and via JDBC. Here comes the next use case for our QlikView JDBC Connector.

To connect Google Cloud SQL you need to create OAuth 2.0 tokens to authorize applications to connect to your instance on your behalf. This can be done by using the command line tool (section Setup).

After following the command line tool setup steps the OAuth 2.0 tokens are created and stores in Windows registry under the key:

HKCU\Software\JavaSoft\Prefs\com.google.cloud.sqlservice

You need to copy this entries for the user who will be running the application on the deployment machine.

In the next step we configure the Google Cloud SQL JDBC driver in the connector:

JDBCConfig_Google_Cloud_SQL.png

Now we can start connecting Cloud SQL in QlikView. Just open the script editor choose JDBC Connector and click connect. Enter the JDBC connect string:

jdbc:google:rdbms://<your_instance_name>/<your_database_name>?connector.driverClass=com.google.cloud.sql.Driver;

Leave the user/password properties free.

JDBCConnector_Google_Cloud_SQL.png

Now we can select the objects from an example mySQL schema I’ve created before with the Google API Console SQL Prompt. Use the dialog as usual to build your SELECT statements to load the data into QlikView:

JDBCConnector_Google_Cloud_SQL_select.png

As we can see it takes only some additional configuration steps to connect QlikView with Google Cloud SQL. Then you can retrieve data into your QlikView application as you would work with a standard mySQL instance.

- Ralf

Labels (1)
Comments
MVP & Luminary
MVP & Luminary

The Google Cloud SQL JDBC driver (derived from mysql) loads the full resultset into memory before giving it back to QlikView. This could lead into a

java.lang.OutOfMemoryError: Java heap space

if you have large tables. To prevent this error just increase the JVM max heap space by setting parameter -Xmx in the JDBC Connector JVM Options tab as show in the picture below (remove old parameter, edit number in option field, add parameter):

GoogleCloudSQL.png

- Ralf

0 Likes
MVP & Luminary
MVP & Luminary

The URL parameter connector.driverClass= is obsolete in the latest JDBC connector version. We've added a config tab for "Advanced Settings". Please type the JDBC driver classname there:

config_GoogleCloudSQL.png

0 Likes
MVP & Luminary
MVP & Luminary

Please keep in mind that the Maximum Request/Response Size of Google Cloud SQL is:

16 MB (16777216 byte)

https://developers.google.com/cloud-sql/faq

So, if you get an error like this:

java.sql.SQLNonTransientException: error: 0 http_response { responsecode: 500 response: "HTTP response was too large: 16781601. The limit is: 16777216."

..reduce your resultset by rows or columns. You could split a SELECT query into multiple resultsets using a Where clause, for instance.

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2012-11-19 05:27 AM
Updated by: