Welcome Guest! Log in


As explained in the presentation article, Stambia can work with Google BigQuery to perform operations on its tables, load data, reverse Metadata, etc.

To accomplish this, you'll first have to install the Stambia's Google BigQuery connector and prepare your Designer and environment.

You'll then be able to reverse your first Metadata and prepare your first Mappings or Processes.

You can find below all the explanations and instructions to install and set up everything to get ready to work with Google BigQuery.

Prerequisites:
  • Stambia DI Designer S18.3.8 or higher
  • Stambia DI Runtime S17.4.7 or higher
  • Java 1.8

 

Installation

Introduction

The installation is composed of the following steps:

  1. Download of the connector and Templates
  2. Installation of the connector
  3. Installation of the Templates
  4. Configuration of the JDBC Drivers Preferences

 

Download

The first step is to download all the necessary materials that you'll need to perform the installation.

Lead to the presentation article and retrieve the following items from the download section:

  • Google BigQuery Connector
  • Google BigQuery Templates
  • Generic Templates

 

Connector Installation

Once you've downloaded the Google BigQuery Connector, proceed to the installation of the plugin as usual.

You can follow this link to find how to install plugins in the Designer.

The Google BigQuery's one is no different than any other additional plugin.

When the installation is finished, you can start the Designer and, optionally, check in the Installation Details if it is correctly installed.

 Click on the Help Menu > About Stambia Designer > Installations Details > Plug-ins tab

 

installationDetails

You should find the Google BigQuery plugin in the list.

 

Templates Installation

The next step consists of importing the generic and Google BigQuery Templates into your workspace.

Start your Designer and import them as usual in your workspace.

 

JDBC Drivers Configuration

The final part is about the JDBC Drivers configuration.

It is necessary to configure it if you want to connect, reverse or consult data through the Designer on Google BigQuery.

The entry in the Designer is already pre-configured and all you have to do is to modify it to add the additional libraries.

Open the Window Menu > Preferences > SQL Explorer > JDBC Drivers > select "Google BigQuery" and click on Edit

 

Then, lead to the "Extra Class Path" tab and click on "Add JARs".

Use the Popup to select all the Google BigQuery libraries which have been automatically extracted by the connector to <Designer Installation Directory>/stambiaRuntime/lib/addons/bigquery

You can use shortcut as "CTRL+a" to select everything if required.

Then, click on OK to save your modifications.

JDBCDriverConfiguration

The screenshot only shows some of the additional libraries as an example, you should add here all the libraries

If the Google BigQuery entry does not exist in JDBC Drivers, you can add it manually. The needed information will be the following:

Example URL: jdbc:stambia:bigquery / Driver Class Name: com.stambia.jdbc.bigquery.BigQueryDriver (retrieved from stambia.udriver.bigquery.jar)

 

Metadata

Everything being configured and ready, you can now create a BigQuery Metadata and try to reverse your first tables.

 

Metadata creation

Create the Metadata, as usual, by selecting the technology in the Metadata Creation Wizard:

metadataCreation

 

Click next, choose a name and click on finish.

 

Metadata Configuration

When the Metadata is created, close the connection wizard that appears automatically (we'll come back on it later.).

Before connecting, there are two specific Google BigQuery properties to define:

 

metadataProperties

 

To add a property, simply right click on the root node and choose "New > Property"

The following properties need to be added:

Property Name Description Example
credentialsFile

Path to the Google credential file that will be used to connect to Google BigQuery.

This file is most commonly a JSON file that can be retrieved from the Google Cloud Console, through the "APIs & Services / Credentials / Service account keys" menu.

The service account from which the credentials is issued must have the permission to perform BigQuery operations on the Google Project specified by the "ProjectId" Property.

Optionally, having also permission on Google Storage can be interesting for performance improvements.

 

D:\googe\credentials\credentialfile.json
projectId The Google Project ID in which to perform BigQuery operations bigqueryprojectid

 

Connection and Reverse

The two properties being configured, you can now try connecting and reversing BigQuery datasets and tables.

Right click on the root node and choose "Actions > Launch Wizard".

There is nothing else to configure, click on connect, and next to continue to dataset selection.

metadataConnection

 

On the next window, click on refresh on the Catalog Name, then select the Google Project from the list.

Click on refresh on the Schema Name and select the Google BigQuery dataset to reverse from the list.

 

metadata project dataset selection

 

Finally click next, refresh the list of tables, and choose the ones to reverse.

After having clicked on finish the tables will be reversed in the Metadata:

 

metadataReversed

 

You can now start using them in your Mappings and Processes.

A few examples can be found in the next section, and a Demonstration Project is available for more complete examples.

 

Examples

Below are some examples of usages in Mappings in Processes:

 

Example of Mapping loading data from an HSQL database to a Google BigQuery table

mapping hsqlToBigQuery

 

Example of Mapping loading data from multiple BigQuery tables with joins to an HSQL table

mapping BigQueryToHsql

 

Example of a Process dropping and re-creating a BigQuery table

process recreateTable

 

Demonstration Project

A demonstration project presenting common and advanced usage of the Google BigQuery Connector in Stambia can be found on the download page.

You can download and import it in your workspace and then have a look at the Mappings and Processes examples.

It is a good start to familiarize with its usage and to see how the Metadata are configured, for instance.

 

 

You have no rights to post comments

Articles

Suggest a new Article!