topic Re: Access to BigQuery | Disconnect when execute query in Administration and Deployment

Access to BigQuery | Disconnect when execute query

MariaD — Fri, 08 Mar 2024 10:20:57 GMT

Hi Folks,

We're recently installed a SAS Access to BigQuery on our Linux 9.4 M7 environment.

When we tried to execute the query below, the query executes (we verfied it on GCP) but instead of showing the results, the following error occurs. Any idea?

proc sql ;
connect to bigquery as perm (project='xx-yyyy-mm' credfile='/aaaa/bq/access.json' 
schema='bb-yyyy-aa');
Create table teste as
select * from connection to perm
(select * from bb-yyyy-aa.schema.table_name );
;
quit;

Re: Access to BigQuery | Disconnect when execute query

LinusH — Fri, 08 Mar 2024 12:55:31 GMT

And nothing more in the SAS log?

Try to query using a libname instead, and adding these options <might> give you more information in the log:

options sastrace=',,,d' sastraceloc=saslog msglevel=i;

Re: Access to BigQuery | Disconnect when execute query

MariaD — Fri, 08 Mar 2024 13:14:12 GMT

So, I tríed to use libname instead of SQL pass-through but it’s not working for the structure inside GCP.

The user has access to a project id and once on it, he connect to other project id, schema and table. In SQL pass-through I can configure this type of access, defining the project id and schema on the connection and then in the FROM I can use project_id2.schema2.table.

There is any to use LIBNAME if I had this structure en GCP?

Re: Access to BigQuery | Disconnect when execute query

LinusH — Fri, 08 Mar 2024 15:31:55 GMT

You can use the same parameters that you use in your CONNECT statement in a LIBNAME statement:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n1bqkrtqhw4344n13kmsimffinu3.htm

Not sure if you need to use the three level notation in your SQL since project and schema are mentioned in the connection parameter.

Re: Access to BigQuery | Disconnect when execute query

MariaD — Fri, 08 Mar 2024 15:49:48 GMT

Thanks, it’a because I access a project that it’s empty, it’s only for the connection (for billing purposes). After I connect in this first project, I need to access the table that is located in other project and schema.

Do you know which Google permission the services account needs to query from SAS ? The user will not save anything in Google, only access the data.

Re: Access to BigQuery | Disconnect when execute query

MariaD — Fri, 08 Mar 2024 17:30:48 GMT

I executed againg the query using the sastrace option. Follows the results:

024-03-08T14:06:39,961 INFO [00000009] :user - 27 options sastrace=',,,d' sastraceloc=saslog msglevel=i;
2024-03-08T14:06:39,961 INFO [00000009] : user - 28
2024-03-08T14:06:39,967 INFO [00000015] : user - 29 proc sql ;
2024-03-08T14:06:39,967 INFO [00000015] : user - 30 connect to bigquery as perm (project='aa-tedm-dtm' credfile='/files/access/bq/tedm-dtm.json'
2024-03-08T14:06:39,967 INFO [00000015] : user - 31 schema='aa-base-prd');
2024-03-08T14:06:41,765 INFO [00000015] : user -
2024-03-08T14:06:41,766 INFO [00000015] : user - 32 Create table teste as
2024-03-08T14:06:41,766 INFO [00000015] : user - 33 select * from connection to perm
2024-03-08T14:06:41,766 INFO [00000015] : user - 34 (select * from aa-base-prd.analytics.table_2 );
2024-03-08T14:06:42,412 INFO [00000015] : user - 0 1709917602 no_name 0 SQL (2)
2024-03-08T14:06:42,412 INFO [00000015] : user - SASGBQ_1: Prepared: on connection 0 1 1709917602 no_name 0 SQL (2)
2024-03-08T14:06:42,413 INFO [00000015] : user - select * from aa-base-prd.analytics.table_2 2 1709917602 no_name 0 SQL (2)
2024-03-08T14:06:42,413 INFO [00000015] : user - 3 1709917602 no_name 0 SQL (2)
2024-03-08T14:06:43,951 INFO [00000015] : user - 4 1709917603 no_name 0 SQL (2)
2024-03-08T14:06:43,951 INFO [00000015] : user - SASGBQ_2: Executed: on connection 0 5 1709917603 no_name 0 SQL (2)
2024-03-08T14:06:43,951 INFO [00000015] : user - Prepared statement SASGBQ_1 6 1709917603 no_name 0 SQL (2)
2024-03-08T14:06:43,951 INFO [00000015] : user - 7 1709917603 no_name 0 SQL (2)