This article provides an introduction to Stambia's JDBC driver for reading and writing Google Sheets.
Adding the driver to Stambia Designer
The driver is included in your Stambia Runtime's installation, inside the stambiaRuntime/lib/jdbc directory. It is named "stambia.jdbc.driver.google.jar".
In order to run SQL queries from the Designer (Consult data, for example), you need to add this driver to the Designer:
- Open the Preferences panel and search for the JDBC section
- Click on the Add button and set the new driver properties:
- Name: Stambia GoogleSheet Driver
- Example URL: jdbc:stambia:google:spreadsheet
- Go to the "Extra Class Path" tab and click "Add JARs..."
- In the popup window, locate the "stambiaRuntime/lib/jdbc/stambia.jdbc.driver.google.jar" file and click OK
- Click the "List Drivers" => the Driver Class Name property should be automatically set to "com.stambia.jdbc.driver.google.SpreadsheetDriver"
Reading a Google Sheet in a mapping
Creating the Google Sheet "TC0020_source"
First, we create a google sheet with the following data. The sheet tab is named "People".
| ID | FIRST_NAME | LAST_NAME |
| 501 | Bobby | FISCHER |
| 502 | Peter | TRUWAVE |
| 503 | Steven | ROOTHERS |
Here is a screenshot of the Google Sheet:
The Google Sheet Metadata
Create a new Stambia Project
Add a Google Spreadsheet Metadata:
Name it "GoogleDrive" and click Finish.
The Server Wizard appears.
Get a JDBC URL with the help of this article: Getting a Google oAuth2 JDBC URL.
Make sure "User name is not required for this database" is checked, and click Connect.
Click the "Refresh Values" button and choose the document name : "TC0020_source". Then click "Next":
Click "Refresh" to list the sheets => the "$People" sheet should be listed.
Check it and click Finish:
Stambia will reverse the Sheet :
The mapping
This mapping reads data from the Google Sheet, and populates a Mysql table.
There is nothing particular on this mapping. The execution poplates the target table with the three rows of data :
Writing to a Google Sheet
Now, we are going to add content to our Google Sheet, from the Hotel Demo Database.
There are some requirements when writing to a google sheet:
- The mapping must be set to "NO_LOAD" mode. This ensures that Stambia will not try to create Load tables.
- The Integration template must be set to "Append mode"
Setting the "NO_LOAD" mode is done by adding a "Criteria" on the mapping and clicking the "+" button.
Please see the screenshots below:
The "Truncate target table" mode is optional. You can uncheck it if you want.
Here is the statistics after execution:
And this is the data written to the Google Sheet:
Comments