topic Re: SAS Connect To ODBC with Big Query issue in SAS Programming

SAS Connect To ODBC with Big Query issue

Gaetan — Fri, 07 Jun 2019 07:38:11 GMT

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

Re: SAS Connect To ODBC with Big Query issue

ErikLund_Jensen — Fri, 07 Jun 2019 08:14:21 GMT

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 ...

Re: SAS Connect To ODBC with Big Query issue

Gaetan — Fri, 07 Jun 2019 09:33:15 GMT

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.

Re: SAS Connect To ODBC with Big Query issue

SalmanMaher — Thu, 30 Jul 2020 15:44:01 GMT

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/allowlargeresults.htm for more details

Re: SAS Connect To ODBC with Big Query issue

Gaetan — Thu, 10 Nov 2022 11:50:49 GMT

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