SELECT x.* FROM (call google_source.native('worksheet=People;query=SELECT A, B, C')) w,
ARRAYTABLE(w.tuple COLUMNS "id" string , "type" string, "name" String) AS xGoogle Spreadsheet Translator
The google-spreadsheet translator is used to connect to a Google Spreadsheet. To use the Google Spreadsheet Translator you need to configure and deploy the Google JCA connector - see the Admin Guide.
The query approach expects the data in the worksheet to be in a specific format. Namely:
-
Any column that has data is queryable.
-
Any column with an empty cell has the value retrieved as null. However differentiating between null string and empty string values may not always be possible as google treats them interchanably. Where possible the translator may provide a warning or throw an exception if there may be a confusion of null vs. empty strings.
-
If the first row is present and contains string values, then it will be assumed to represent the column labels.
If you are using the default native metadata import, 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 can provide metadata for VDBs.
|
Warning
|
A sheet with a header that is defined in Teiid, which later has all data rows removed is no longer valid for access through Teiid. The google api will treat the header as a data row at that point and thus queries will no longer be valid. |
|
Warning
|
Non-string fields are updated using the canonical Teiid SQL value - in cases where the spreadsheet is using a non-conforming locale, consider disallowing updates. See also TEIID-4854 and the allTypesUpdatable import property below. |
Importer Properties
-
allTypesUpdatable- Set to true to mark all columns as updatable. Set to false to enable update only on string/boolean columns, which are not affected by TEIID-4854. Defaults to true.
JCA Resource Adapter
The Teiid specific Google Spreadsheet Data Sources Resource Adapter should be used with this translator.
Native Queries
Google spreadsheet source procedures may be created using the teiid_rel:native-query extension - see 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 or similar functionality. See the Select format below.
Direct Query Procedure
|
Note
|
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 execution property called _SupportsDirectQueryProcedure to true. |
|
Tip
|
By default the name of the procedure that executes the queries directly is called native. Override the execution property _DirectQueryProcedureName to change it to another name. |
The Google spreadsheet translator provides a procedure to execute any ad-hoc query directly against the source without any Teiid parsing or resolving. Since the metadata of this procedure’s execution results are not known to Teiid, they are returned as an object array. ARRAYTABLE can be used construct tabular output for consumption by client applications. Teiid exposes this procedure with a simple query structure as below:
Select
Select Example
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 |