Problem Note 67780: Writing rows to Google BigQuery tables might result in the message "ERROR: During insert: String data, right-truncated..."
 |  |  |  |  |
Inserting data through SAS/ACCESS® Interface to Google BigQuery might result in no data being inserted and an error message. This issue can occur under the following circumstances:
- You use both the SCAN_STRINGS=YES option and the MAX_CHAR_LEN= LIBNAME statement option.
- The MAX_CHAR_LEN= option is set to a number that is smaller than the length of character data to be inserted.
- The source table is in a SAS® library or in a library on a database engine that is not Google Cloud BigQuery.
- The target table is in Google Cloud BigQuery.
Here is an example of the error in the SAS log, as well as the syntax that causes the error:
1 LIBNAME BQ BIGQUERY connection-and-authentication-options SCAN_STRINGS=YES MAX_CHAR_LEN=1 ;
NOTE: Libref BQ was successfully assigned as follows:
Engine: BIGQUERY
Physical Name: xxx
2 DATA BQ.target_table ;
3 SET SASHELP.CLASS ;
4 RUN ;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
ERROR: During insert: String data, right-truncated : String data, right-truncated : String data, right-truncated
NOTE: The data set BQ.target_table has 0 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 4.93 seconds
cpu time 0.04 seconds
NOTE: Libref BQ was successfully assigned as follows:
Engine: BIGQUERY
Physical Name: xxx
2 DATA BQ.target_table ;
3 SET SASHELP.CLASS ;
4 RUN ;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
ERROR: During insert: String data, right-truncated : String data, right-truncated : String data, right-truncated
NOTE: The data set BQ.target_table has 0 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 4.93 seconds
cpu time 0.04 seconds
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to Google BigQuery | Linux for x64 | 9.41 | V.04.00 | 9.4 TS1M6 | Viya |
A fix for this issue for SAS/ACCESS Interface to Google BigQuery 9.41 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/H5A.html#67780
When you insert rows to Google BigQuery tables with the LIBNAME options SCAN_STRINGS=YES and MAX_CHAR_LEN=1, data might not be inserted and an error is returned.
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2021-09-29 12:07:08 |
| Date Created: | 2021-04-14 09:53:00 |