KB255685: An error message is displayed when importing data from Google Big Query using the Simba Big Query ODBC driver on MicroStrategy Analytics Enterprise 10

SYMPTOM
An error message is displayed when importing data from Google BigQuery with Simba ODBC driver on Strategy Analytics Enterprise 10.x.  The following scenarios have been found to generate errors:

1. During data import, on the preview page, it will generate statement such as

select * from samples.natality limit 1000

select year Year from samples.natality 

select repository.url repository.url from samples.github

syntax error near 
’ select repository.url repository.<<<????>>>url from samples.github’ 

1. On Strategy 10.0.x Web, click “Add External data”, then click “Database”

1. Click  “Pick tables” in the “Select Import Options” dialog.

1. Click “Add new data source”, in DSN-less mode, choose “Google Big Query” as database and version, enter the Token, catalog, user name and password. Then click OK.

1. Click on the new created data source, drag a table into the right panel, Click “Prepare data”, it will show the following error, as below:

Failed to execute query.
Error in accessing information from the external data source (Database Instance:
(DataImport)(0), Error: Execute Query failed. Error type: Odbc error.
Odbc operation attempted: SQLExecDirect. 
[42000:31480: on SQLHANDLE][Simba][SQL Engine] (31480) syntax error near 
'select *from samples.wikipedia wikipedia limit <<< ??? >>> 1000'.)

1. On Strategy 10.0.x Web, click “Add External data”, then click “Database”

1. Click “Build a Query” in the “Select Import Options” dialog.

1. Click “Add new data source”, in DSN-less mode, choose “Google Big Query” as database and version, input Token, catalog, user name, password. Then click OK.

1. Click on the new created data source, drag a table into the right panel, double click on a column which contains keywords in SQL (click on Yearm for example) to add the column into the preview.

1. Click “Edit SQL”, then remove “limit 1000” from the SQL statement.

1. Click “Execute SQL”, it will show the following error message below:

SQL Pass 0 failed to execute
Error in accessing information from the external data source (Database 
Instance: (DataImport)(0), Error: Execute Query failed. Error type: Odbc 
error. Odbc operation attempted: SQLExecDirect [42000:31480: on 
SQLHANDLE][Simba][SQLEngine] (31480) syntax error near 'select year 
year<<< ??? >>> from samples.gsod gsod')

1. On Strategy 10.0.x Web, click “Add External data”, then click “Database”

1. Click “Build a Query” in the “Select Import Options” dialog.

1. Click “Add new data source”, in DSN-less mode, choose “Google Big Query” as a database and version. Input Token, catalog, user name and password. Then click OK.

1. Click on the new created data source, drag a table into the right panel, click on a column named as “a.b” to add the column into the preview.

1. Click “Execute SQL”, it will show the following error message below:

SQL Pass 0 failed to execute
Error in accessing information from the external data source (Database 
Instance: (DataImport)(0), Error: Execute Query failed. Error type: Odbc 
error. Odbc operation attempted: SQLExecDirect [42000:31480: on 
SQLHANDLE][Simba][SQLEngine] (31480) syntax error near 
'select repository.url repository.
<<< ??? >>>url from samples.github_nested github_nested'.)

1. Configure the client ID and client secret in google big query

1. On Strategy 10.0.x Web, click “Add External data”, then click “Google Big Query”, then click any one of “Build a Query/Type a Query/Pick Tables”.

 
3. Input the token, catalog, username and password information, then the above three cases can work well.