SAS Connect To ODBC with Big Query issue

[Gaetan]

Hi everyone,

I would like to know if someone is currently using SAS Connect To ODBC to request Big Query database.

Technical Context :

- Windows Server 2012 X86-64

- Simba ODBC Driver for GCP

- SAS 9.4M6

We encounter an issue when we request dataset over 1 million of rows.

Here is the sample code :

libname B1 odbc dsn = 'DBO' schema = bqdata;

data toto;
set B1.surface (obs = 10);
run;

2 differents issue appears according to Simba ODBC Driver for Big Query configuration.

1 - MSG1 (Unactive "Dataset name for large results datasets) :

ERROR: CLI open cursor error: [Simba][BigQuery] (100) Error interacting with REST API: Response too large to return. Consider

       setting allowLargeResults to true in your job configuration. For more information, see

       https://cloud.google.com/bigquery/troubleshooting-errors

2 - MSG2 (Active "Dataset name for large results datasets) :

ERROR: CLI prepare error: [Simba][BigQuery] (100) Error interacting with REST API: Access Denied: Project

       paj-dcproc-dev-prj-dataproc: The user paj-sas-dev-sac-bidata-1@paj-dsas-dev-prj-sas.iam.gserviceaccount.com does not have

       bigquery.datasets.create permission in project paj-dcproc-dev-prj-dataproc.

SQL statement: SELECT * FROM ntz_snapshot_20190413.TWH_ODS_AMC_ARTICLES.

Anyone have an idea ?

Gaetan

[ErikLund_Jensen]

Hi @Gaetan 

This is just an idea.

I think your query works in a way behind the scene, where the database is requested to return the full table, and a clientside limit to 10 rows is in effect. Try using an explicit passthru and limit the query serverside with Select top 10 ...

[Gaetan]

Hi @ErikLund_Jensen 

Thanks for your reply.

Indeed, explicit pass-through fixs this issue.

But, many SAS users are using implicit pass-through via SAS Enterprise Guide.

So, this neccesits to transform lots of SAS programs from implicit to explicit pass-through.

[SalmanMaher · Accepted solution]

Hi @Gaetan

Try setting the ODBC option AllowLargeResults=1 in your DSN.

Salman

See https://www.simba.com/products/BigQuery/doc/JDBC_InstallGuide/content/jdbc/bq/options/allowlargeresu... for more details

[Gaetan]

Hi,

I want just not notice that we do not use Simba ODBC connector, but Simba JDBC connector currently.

The connector is more performant, so I preconize it,

Gaetan