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
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 ...
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.