How to insert into ARRAY column in BQ stored Proc

Question

I am trying to create BQ routine which takes ARRAY as parameter and insert into table .

filename can be NULL too .

CREATE OR REPLACE PROCEDURE `##.history`( Job_ID STRING, filename ARRAY<STRING> , Status STRING )
BEGIN
 
  
  EXECUTE IMMEDIATE format 
  ("""
  INSERT INTO  
  `##.tablename`
  ( JOB_ID,
   FILENAME,
   STATUS,
    )
  values
   (GENERATE_UUID(),
    '"""||Job_ID||"""',
     '"""||filename|| """',
     '"""||Status||"""'
    )
   """);

when I am invoking procedure it's giving error:

CALL ###.history('text1', ['f1.txt','f2.txt'], 'start');

No matching signature for operator || for argument types: STRING, ARRAY. Supported signatures: STRING || STRING; BYTES || BYTES; ARRAY || ARRAY

Which datatype of column **FILENAME** in your table? String or array? — Max Zolotenko, Sep 07 '22 at 10:51

score 2 · Accepted Answer · answered Sep 07 '22 at 11:06

Your error is coming from the format statement and trying to concatenate the array, concatenation being a string function only. As Max Zolotenko pointed out, you probably don't need the EXECUTE IMMEDIATE in the example you provided, you can proceed with something like:

CREATE OR REPLACE PROCEDURE `so_test.history`( Job_ID STRING, filename ARRAY<STRING> , Status STRING )
BEGIN
 
  
INSERT INTO so_test.struct_insert (job_id, filename, status)
VALUES
(
  Job_ID,
  filename,
  Status
);

END;

I do want to point out however in your example you have GENERATE_UUID() that you are also trying to insert, however your values list only contains 3 columns. You'll need to adjust that as well.

How to insert into ARRAY column in BQ stored Proc

1 Answers1