Google spreadsheet translator

The google-spreadsheet translator is used to connect to a Google Sheets spreadsheet.

The query approach expects that the data in the worksheet has the following characteristics:

  • All columns that contains data can be queried.

  • 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 interchangeably. Where possible, the translator may provide a warning or throw an exception if it cannot differentiate between null and empty strings.

  • If the first row is present and contains string values, then the row is 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) is loaded upon translator start up. If you make any changes in data types, it is advisable to restart your virtual database.

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.

The google-spreadsheet translator does not provide importer settings, but it can provide metadata for VDBs.

Warning
 If you remove all data rows from a sheet with a header that is defined in Teiid, you can no longer access the sheet through Teiid. The Google API will treat the header as a data row at that point, and queries to it 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. For more information, see TEIID-4854 and the following information about the allTypesUpdatable import property.
Importer properties

  • allTypesUpdatable- Set to true to mark all columns as updatable. Set to false to enable update only on string or Boolean columns that 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. For more information, see Parameterizable native queries in Translators. 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. For more information, see the Select section that follows.

Note
Direct query procedure
This feature is turned off by default, because of the security risk in permitting any command to execute against the data source. To enable this feature, set the property SupportsDirectQueryProcedure to true. For more information, see Override execution properties in Translators.
Tip
 By default the name of the procedure that executes the queries directly is called native. You can change its name by overriding the execution property DirectQueryProcedureName. For more information, see Override execution properties in Translators.

The Google spreadsheet translator provides a procedure to execute any ad-hoc query directly against the source without any Teiid parsing or resolving. Because the metadata of this procedure’s execution results are not known to Teiid, they are returned as an object array. You can use ARRAYTABLE to construct tabular output for consumption by client applications. For more information, see ARRAYTABLE.

Teiid exposes this procedure with a simple query structure as shown in the following example:

Select example
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 x

The first argument takes semicolon-separated (;) name-value pairs of the following properties to execute the procedure:

Property Description Required

worksheet

Google spreadsheet name.

yes

query

Spreadsheet query.

yes

limit

Number of rows to fetch.

no

offset

Offset of rows to fetch from limit or beginning.

no

results matching ""

    No results matching ""