13.14. Google Spreadsheet Translator

13.14.1. Google Spreadsheet Translator

The Google spreadsheet translator is used to connect to a Google spreadsheet.
The Google spreadsheet translator is implemented by the org.teiid.translator.google.SpreadsheetExecutionFactory class and known by the translator type name google-spreadsheet.
The query approach expects the data in the worksheet to be in a specific format. Namely:
  • Any column that has data can be queried.
  • All datatypes (including strings) featuring empty cells are returned as NULL.
  • If the first row is present and contains string values, then it will be assumed to represent the column labels.
If you are using a dynamic VDB, the metadata for your Google account (worksheets and information about columns in worksheets) are loaded upon translator start up. If you make any changes in data types, it is advisable to restart your VDB.
The translator supports queries against a single sheet. It supports ordering, aggregation, basic predicates, and most of the functions supported by the spreadsheet query language.
There are no Google spreadsheet importer settings, but it does provide metadata for dynamic VDBs.

Note

The resource adapter for this translator is provided by configuring the google data source in the JBoss EAP instance. See the Red Hat JBoss Data Virtualization Administration and Configuration Guide for more configuration information.

13.14.2. Google Spreadsheet Translator: Native Queries

Google spreadsheet source procedures may be created using the teiid_rel:native-query extension (see Section 13.7, “Parameterizable Native Queries”) The procedure will invoke the native query similar to an native procedure call with the benefits that the query is predetermined and that result column types are known, rather than requiring the use of ARRAYTABLE ( Section 3.6.10, “Nested Tables: ARRAYTABLE”) or similar functionality.

13.14.3. Google Spreadsheet Translator: Native Procedure

Warning

This feature is turned off by default because of the security risk this exposes to execute any command against the source. To enable this feature, override the translator property called "SupportsNativeQueries" to true. See Section 13.6, “Override Execution Properties”.
Google spreadsheet translator provides a procedure with name native that gives ability to execute any ad hoc native Google spreadsheet queries directly against the source without any JBoss Data Virtualization parsing or resolving. Since the metadata of this procedure's execution results are not known to the JBoss Data Virtualization and they are returned as object array. Users can use ARRAYTABLE ( Section 3.6.10, “Nested Tables: ARRAYTABLE”) to construct a build a tabular output for consumption by client applications.
JBoss Data Virtualization exposes this procedure with a simple query structure:

Example 13.1. Select Example

SELECT x.* FROM (call pm1.native('worksheet=People;query=SELECT A, B, C')) w,
 ARRAYTABLE(w.tuple COLUMNS "id" string , "type" string, "name" String) AS x
The first argument takes semi-colon(;) separated name value pairs of following properties to execute the procedure:
Property
Description
Required
worksheet
Google spreadsheet name
yes
query
spreadsheet query
yes
limit
number rows to fetch
no
offset
offset of rows to fetch from limit or beginning
no

Note

By default the name of the procedure that executes the queries directly is called native , however the user can set the Override Execution Properties property (see Section 13.6, “Override Execution Properties”) on NativeQueryProcedureName in the vdb.xml file to change it to any other procedure name.