[FIXED] How to pass URIs as variable in BQ stored procedure

Issue

I am new to BQ and trying to load table via file from GCS bucket and getting error for uris .

Query error: Found unsupported function call 'ARRAY[...]'; failed to set 'uris' in OPTIONS()

Code Snippet :

begin
declare filename STRING;
declare SourceDir STRING  ;
declare infilename STRING ;

set SourceDir='dir/';
set infilename='file.csv';
set filename = CONCAT('gs://mybucket/' , SourceDir,infilename);

LOAD DATA INTO `dataset.tablename`
FROM FILES(
format='CSV',
uris = [filename]
);
end

I am getting the above error.

Solution

Consider below approach using EXECUTE IMMEDIATE to pass filename as a parameter to your LOAD DATA clause:

begin
declare filename STRING;
declare SourceDir STRING;
declare infilename STRING;

set SourceDir='dir/';
set infilename='test.csv';
set filename = CONCAT("'",CONCAT('gs://mybucket/' , SourceDir,infilename),"'");

EXECUTE IMMEDIATE FORMAT("""
LOAD DATA INTO `dataset.tablename`
FROM FILES(
  format='CSV',
  uris = [%s]
  );
""",filename);
end

Output:

Answered By - Ricco D

Answer Checked By - Candace Johnson (PHPFixing Volunteer)