topic Re: Issue while accessing Google BQ Tables in SAS Data Management

Issue while accessing Google BQ Tables

MarkNicholas — Mon, 17 Dec 2018 13:14:24 GMT

Hi,

I am attempting to access data from Google BQ table.

However, facing issues while attempting to read data partially from a relatively large table.The data step to read 5 obs out of 5 million observations using OBS set option displays the following message and my sas session is aborted.

NOTE: There were 50000 observations read from the data set
RAW_5million.System Aborting Status=803fc21e

But no issue while :
1)accessing Full dataset using datastep,
2)using monotonic function in sql
3)using where function in sql for partial retrieval.

Also, for smaller datasets having 1 million observations, even obs option seems to work for me.

I wanted to take the forum's help to understand the message "System Aborting Status=803fc21e" and suggestions further to find limit when this error pops up.

Thanks
Mark

Re: Issue while accessing Google BQ Tables

ballardw — Tue, 18 Dec 2018 00:20:17 GMT

Posting the entire code along with the errors from the log may help. Copy from the log and paste into a code box opened with the forums {I} icon to preserve formatting of error messages.

The text you show is not typical of the very odd error messages SAS can sometimes show with severe issues. Did you reduce the text of the error?

Re: Issue while accessing Google BQ Tables

MarkNicholas — Tue, 18 Dec 2018 07:33:19 GMT

Please find the details.

I am invoking SAS on Unix Environment. We are using ODBC for connecting to BQ .

tgt - > our Unix directory path . It has enough space to store data.
bq-> libref to the BQ path.

/*25k observations read succesfully both fully and partially*/

5? Data tgt.n_25k; set bq.N_25;run;

NOTE: There were 25000 observations read from the data set
      BQ.RAW_CDU_CDU060_CUSTCUSTRELN_25.
NOTE: The data set TGT.N_25K has 25000 observations and 14 variables.
NOTE: DATA statement used (Total process time):
      real time           14.98 seconds
      cpu time            3.53 seconds

/*50k observations read succesfully both fully and partially*/

6? Data tgt.n_50k; set bq.N_50 ;run;

NOTE: There were 50000 observations read from the data set
      BQ.RAW_CDU_CDU060_CUSTCUSTRELN_50.
NOTE: The data set TGT.N_50K has 50000 observations and 14 variables.
NOTE: DATA statement used (Total process time):
      real time           27.94 seconds
      cpu time            6.70 seconds

/*0.1 million observations read succesfully both fully and partially*/

7? Data tgt.n_01m; set bq.N_01M ;run;

NOTE: There were 100000 observations read from the data set
      BQ.RAW_CDU_CDU060_CUSTCUSTRELN_01M.
NOTE: The data set TGT.N_01M has 100000 observations and 14 variables.
NOTE: DATA statement used (Total process time):
      real time           56.67 seconds
      cpu time            13.03 seconds

/*0.5 million observations read succesfully fully but NOT PARTIALLY */

4? Data tgt.n_05m ; set bq.RAW_CDU_CDU060_CUSTCUSTRELN_05M ;run;

NOTE: There were 500000 observations read from the data set
BQ.RAW_CDU_CDU060_CUSTCUSTRELN_05M.
NOTE: The data set TGT.N_05M has 500000 observations and 14 variables.
NOTE: DATA statement used (Total process time):
real time 3:39.75

cpu time 1:02.70

options fullstimer nostsuffix sastrace=',,,d' sastraceloc=saslog ;

17? Data tgt.n_05m;set bq.N_05M (obs=5);run;

ODBC_1: Prepared: on connection 0
SELECT * FROM GCP.N_05M /* GCP is schema name here*/

ODBC_2: Executed: on connection 0
Prepared statement ODBC_1

NOTE: There were 5 observations read from the data set
BQ.N_05M.System Aborting Status=803fc21e

Control comes out of SAS session to Unix prompt. at the backend we see that lck file remains.

---------------------------

Please advise further.