{"id":208,"date":"2018-02-14T10:12:02","date_gmt":"2018-02-14T04:42:02","guid":{"rendered":"http:\/\/blog.tenthplanet.in\/?p=208"},"modified":"2026-09-09T15:01:06","modified_gmt":"2026-09-09T09:31:06","slug":"querying-google-bigquery-from-pentaho-report-designer","status":"publish","type":"post","link":"https:\/\/tenthplanet.in\/blogs\/querying-google-bigquery-from-pentaho-report-designer\/","title":{"rendered":"Querying Google BigQuery from Pentaho+ Report Designer"},"content":{"rendered":"<h3>Connecting Pentaho+ Report Designer to Google Big Query<\/h3>\n<h3>1. Pre-requisites<\/h3>\n<ul class=\"blog-list\">\n<li>The following Pre-Requisites are in place to enable connectivity of Pentaho+ Report Designer 5.4 to Google Big Query.<\/li>\n<li>Pentaho+ Report Designer 5.4 or higher and optional Pentaho+ BA Server deployed and running<\/li>\n<li>BigQuery JDBC Drivers &amp; Dependency JARs<\/li>\n<li>Google Account<\/li>\n<li>API Access to Google Big Query<\/li>\n<li>Google Chrome Browser<\/li>\n<\/ul>\n<h3>2. Enable Google BigQuery API<\/h3>\n<h4>Pre-requisites<\/h4>\n<p>To enable Google BigQuery for a prototype the following pre-requisites are applied:<\/p>\n<p>\u2713A Google Account is available<\/p>\n<p>\u2713Credit Card details can be used and entered for billing of BigQuery Usage (linked to the Google Account)<\/p>\n<h4>Enable Google BigQuery API<\/h4>\n<p style=\"padding-left: 30px\">1. Enabling the Google BigQuery API can be achieved via the following steps:<\/p>\n<p style=\"padding-left: 30px\">2. Connect with the browser to <a href=\"https:\/\/code.google.com\/apis\/console\" target=\"_blank\" rel=\"noopener\">Console 1<\/a><\/p>\n<p style=\"padding-left: 30px\">3. Login with the Google Account that will be used for the connectivity<\/p>\n<p style=\"padding-left: 30px\">4. The Google API Project main page will be shown<\/p>\n<p style=\"padding-left: 30px\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-209\" src=\"https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_API-project-main-page.jpg\" alt=\"\" width=\"469\" height=\"280\" title=\"\" srcset=\"https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_API-project-main-page.jpg 469w, https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_API-project-main-page-300x179.jpg 300w\" sizes=\"auto, (max-width: 469px) 100vw, 469px\" \/><\/p>\n<p style=\"padding-left: 30px\">5. Click on \u201cBigQuery API\u201d<\/p>\n<p style=\"padding-left: 30px\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-210\" src=\"https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_Big-Query-API.jpg\" alt=\"\" width=\"578\" height=\"196\" title=\"\" srcset=\"https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_Big-Query-API.jpg 578w, https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_Big-Query-API-300x102.jpg 300w\" sizes=\"auto, (max-width: 578px) 100vw, 578px\" \/><\/p>\n<p style=\"padding-left: 30px\">6. There are limitations in consuming the BigQuery API, as it costs resources of Google Cloud Storage. Review the Pricing for BigQuery as per your needs, in<\/p>\n<p style=\"padding-left: 30px\">7. Sometimes, it will take few minutes or an hour to get activated (as it involves pricings and approvals of financial transactions)<\/p>\n<h4>Billing for Google BigQuery<\/h4>\n<p>Google BigQuery are subject to be charged based on consumption. Pricing or any related doubts you can verify in below URLs.<\/p>\n<p><b>\u2713Free Trial:<\/b> <a href=\"https:\/\/cloud.google.com\/free-trial\/?hl=en_US\" target=\"_blank\" rel=\"noopener\">Trial<\/a><\/p>\n<p><b>\u2713Pricing Details:<\/b> <a href=\"https:\/\/cloud.google.com\/pricing\/\" target=\"_blank\" rel=\"noopener\">Pricing<\/a><\/p>\n<p><b>\u2713FAQs:<\/b> <a href=\"https:\/\/cloud.google.com\/free-trial\/?hl=en_US#faq\" target=\"_blank\" rel=\"noopener\">FAQ<\/a><\/p>\n<p><b>\u2713Contact Sales:<\/b> <a href=\"https:\/\/cloud.google.com\/contact\/\" target=\"_blank\" rel=\"noopener\">Sales<\/a><\/p>\n<p>Billing can be enabled for a Google Account by visiting URL<\/p>\n<p><a>Console<\/a><\/p>\n<p>This can be enabled by another way of Google BigQuery \u2018Try it Free\u2019 option, a 3 step process.<\/p>\n<h3>3. Validating Google BigQuery<\/h3>\n<p>To ensure the Google BigQuery API is successfully activated, a simple test can be executed via the Google BigQuery Web Interface. Google provides a set of samples that can be used for the validation of the BigQuery connectivity. To validate the activation of the BigQuery API for the account defined in the previous section, navigate to <a href=\"https:\/\/bigquery.cloud.google.com\/\" target=\"_blank\" rel=\"noopener\">Bigquery<\/a> Check between different browsers, if you face any inconsistency in Web UI interface.<\/p>\n<h4>Sample steps to validate the activation of the Google BigQuery API:<\/h4>\n<p style=\"padding-left: 30px\">1. Using Chrome Browser, navigate to <a href=\"https:\/\/bigquery.cloud.google.com\/\" target=\"_blank\" rel=\"noopener\"><\/a><\/p>\n<p style=\"padding-left: 30px\">2. Login with the Google Account registered for the BigQuery API<\/p>\n<p style=\"padding-left: 30px\">3. Click or Expand item <b>\u201cPublicdata:samples\u201d<\/b> and select a sample database<\/p>\n<p style=\"padding-left: 30px\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-211\" src=\"https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_public-data-samples.jpg\" alt=\"\" width=\"328\" height=\"360\" title=\"\" srcset=\"https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_public-data-samples.jpg 328w, https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_public-data-samples-273x300.jpg 273w\" sizes=\"auto, (max-width: 328px) 100vw, 328px\" \/><\/p>\n<p style=\"padding-left: 30px\">4. Choose table \u2018natality\u2019. You can see the list of fields in the table in the right pane of the window.<\/p>\n<p style=\"padding-left: 30px\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-212\" src=\"https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_natality.jpg\" alt=\"\" width=\"553\" height=\"102\" title=\"\" srcset=\"https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_natality.jpg 553w, https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_natality-300x55.jpg 300w\" sizes=\"auto, (max-width: 553px) 100vw, 553px\" \/><\/p>\n<p style=\"padding-left: 30px\">5. You can see results in the results pane at the bottom. Note, these query running will result in charges in your account. All is fine to connect Google Big Query from external client apps.<br \/>\nInstallation<\/p>\n<h3>4. Installation of Google BigQuery JDBC Drivers<\/h3>\n<p><a href=\"https:\/\/code.google.com\/p\/starschema-bigquery-jdbc\/downloads\/list\" target=\"_blank\" rel=\"noopener\">Visit URL<\/a> . You can see the list of downloads available for different tools.<\/a><\/p>\n<p>Please select the <b>\u2018bqjdbc-1.4.jar\u2019<\/b> in <a href=\"https:\/\/storage.googleapis.com\/google-code-archive-downloads\/v2\/code.google.com\/starschema-bigquery-jdbc\/bqjdbc-1.4.jar\" target=\"_blank\" rel=\"noopener\">storage<\/a><\/p>\n<p>Place the downloaded jar file in report-designer tool at path, <b>\u2026\/report-designer\/lib<\/b> (use slash accordingly based on Windows or Linux systems)<\/p>\n<p>Start Pentaho+ Report Designer tool now using <b>report-designer.bat<\/b> or <b>report-designer.sh <\/b>accordingly.<br \/>\nInstallation<\/p>\n<h3>5. Datasource Configuration<\/h3>\n<p>\u2713In Pentaho+ Report Designer tool, go to File &gt; New Report or choose option \u2018New Report\u2019 in the Report Designer default wizard.<\/p>\n<p>\u2713Go to menu, <b>Data &gt; Add Data Source &gt; JDBC<\/b><\/p>\n<p>\u2713Choose Database type as \u2018Generic Database\u2019 and Connection Type as \u2018JDBC\u2019, in the data source creation dialog box.<\/p>\n<p>\u2713Fill in the details as below (currently we are using authentication approach using Google Service Account credentials; Other option available is by accessing via OAuth 2.0 Web Client credentials). Depends on the choice of authentication we follow, respective access parameters to be used. For our case,<\/p>\n<p><b>Name:<\/b> GBQ_Connection<\/p>\n<p><b>JDBC URL:<\/b> jdbc:BQDriver:?withServiceAccount=true?transformQuery=true<\/p>\n<p><b>Driver:<\/b> net.starschema.clouddb.jdbc.BQDriver<\/p>\n<p><b>User: <\/b><span style=\"color: #e36c0a\">&lt;Client ID of the service Account, created under your Google account&gt;<\/span><\/p>\n<p><b>Password: <\/b><span style=\"color: #e36c0a\">&lt;Location of the .p12 key file (for the service account), stored in your local drive&gt;<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-213\" src=\"https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_data-source-configuration.jpg\" alt=\"\" width=\"475\" height=\"192\" title=\"\" srcset=\"https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_data-source-configuration.jpg 475w, https:\/\/tenthplanet.in\/blogs\/wp-content\/uploads\/sites\/21\/2018\/02\/GBQ_PRD_data-source-configuration-300x121.jpg 300w\" sizes=\"auto, (max-width: 475px) 100vw, 475px\" \/><\/p>\n<p>For Reference, Creating Service Accounts and an associated key file can be done by using options in URL<\/p>\n<p><a href=\"https:\/\/console.cloud.google.com\/iam-admin\/serviceaccounts\" target=\"_blank\" rel=\"noopener\">Service Accounts<\/a><\/p>\n<p>Click on Test option, to ensure that you could able to establish connection with Google Account successfully.<\/p>\n<h3>6. Validate Client Connectivity with Google BigQuery (from PRD)<\/h3>\n<p>You can validate the client connectivity Pentaho+ Report Designer(PRD to able to connect with Google Big Query Database tables), by running below sample query in the data source creation wizard of Pentaho+ Report Designer as usual.<\/p>\n<pre>SELECT\nDATE(pickup_datetime) as pickup_date,\nSUM(passenger_count) as passenger_count,\nSUM(trip_distance) as trip_distance,\nSUM(fare_amount) as fare_amount,\nSUM(total_amount) as total_amount\nFROM\n[nyc-tlc:green.trips_2014]\nGROUP BY\npickup_date\nORDER BY\npickup_date;<\/pre>\n<p>You can see the result of above query, as you preview results.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The following Pre-Requisites are in place The following Pre-Requisites are in place to enable connectivity of Pentaho Plus Report Designer 5.4 to Google Big Query.<\/p>\n","protected":false},"author":23,"featured_media":1137,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[424],"tags":[438,439,440],"class_list":["post-208","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-pentaho","tag-bigquery","tag-pentaho-report-designer","tag-pentaho-reports"],"_links":{"self":[{"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/posts\/208","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/users\/23"}],"replies":[{"embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/comments?post=208"}],"version-history":[{"count":4,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/posts\/208\/revisions"}],"predecessor-version":[{"id":12400,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/posts\/208\/revisions\/12400"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/media\/1137"}],"wp:attachment":[{"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/media?parent=208"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/categories?post=208"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/tags?post=208"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}