Ce contenu n'est pas disponible dans la langue sélectionnée.
12.16. Google Spreadsheet Translator
12.16.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.
12.16.2. Google Spreadsheet Translator: Native Queries
Google spreadsheet source procedures may be created using the teiid_rel:native-query extension (see Section 12.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 2.6.10, “Nested Tables: ARRAYTABLE”) or similar functionality.
12.16.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 12.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 2.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 12.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 12.6, “Override Execution Properties”) on NativeQueryProcedureName in the
vdb.xml file to change it to any other procedure name.
