1

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.

Ricco D
  • 6,873
  • 1
  • 8
  • 18
Mudgal
  • 35
  • 4

1 Answers1

0

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:

enter image description here

Ricco D
  • 6,873
  • 1
  • 8
  • 18
  • Thanks!, I am able to successfully get filename in uris but my load is still Failing : Unable to parse external_data_config for CSV tabledef (table cev.cd.DE_bcc5ee3f-8066-4967-a937-5c58da03a98f_source). Errors: Invalid uri in load data options.. but same file I am able to parse If filename is hardcoded . Please assist ! – Mudgal Aug 23 '22 at 20:29
  • @Mudgal what is the value of your `filename` variable? – Ricco D Aug 23 '22 at 21:02
  • LOAD DATA INTO `dataset.tablename` FROM FILES( format='CSV', uris = [' gs:///test_dacom.csv'] ) – Mudgal Aug 23 '22 at 23:25
  • filename : ' gs:///test_dacom.csv' – Mudgal Aug 23 '22 at 23:41
  • This is working now @Ricco D , thanks for your help . I just added single codes to uris -> uris = ['%s'] and remove one CONCAT set filename = CONCAT('gs://mybucket/', SourceDir , infilename ); – Mudgal Aug 24 '22 at 01:12
  • @Mudgal You can consider accepting the answer to mark it as solved. See [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers) – Ricco D Aug 24 '22 at 01:24