As explained in the presentation article, Stambia can work with Google BigQuery powered by Google Cloud Platform (GCP) 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 and Google Cloud Platform (GCP).
- Stambia DI Designer S18.3.8 or higher
- Stambia DI Runtime S17.4.7 or higher
- Java 1.8
The installation is composed of the following steps:
- Download of the connector and Templates
- Installation of the connector
- Installation of the Templates
- Configuration of the JDBC Drivers Preferences
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
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
You should find the Google BigQuery plugin in the list.
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.
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)
Everything being configured and ready, you can now create a BigQuery Metadata and try to reverse your first tables.
Create the Metadata, as usual, by selecting the technology in the Metadata Creation Wizard:
Click next, choose a name and click on finish.
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:
To add a property, simply right click on the root node and choose "New > Property"
The following properties need to be added:
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.
|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.
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.
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:
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.
Below are some examples of usages in Mappings in Processes:
Example of Mapping loading data from an HSQL database to a Google BigQuery table
Example of Mapping loading data from multiple BigQuery tables with joins to an HSQL table
Example of a Process dropping and re-creating a BigQuery table
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.