google cloud platform – big query external tables

There are many files which going to be land on gcs bucket daily. We have to merge them all and create a new file copied to new location.

I can do this with reading all the files which starts from config*

CREATE OR REPLACE EXTERNAL TABLE `test-project.TEMP_PROCESSING.test_external_table`
OPTIONS (
  format="CSV",
  uris = ['gs://test-greenplum-bq-bucket/test_input/config*.csv'],
  skip_leading_rows = 1
);

file names will be like below

gs://test-greenplum-bq-bucket/test_input/config-7621049c2008e19dae82017fa8d5f189-20230801-1690846454-627484-28180770-66nwd.csv

I was trying to get the files matching pattern

CREATE OR REPLACE EXTERNAL TABLE `test-project.TEMP_PROCESSING.test_external_table`
OPTIONS (
  format="CSV",
  uris = ['gs://test-greenplum-bq-bucket/test_input/config-*-'||regexp_replace(cast(current_date as string),'-','')||'-*.csv'],
  skip_leading_rows = 1
);

am getting below error.

Found unsupported function call 'ARRAY[...]'; failed to evaluate 'uris' in external table OPTIONS

I did try execute immediate but no luck.

begin
declare gcs_bucket_location  string;
set gcs_bucket_location = 'gs://test-greenplum-bq-bucket/test_input/test_file_*-'||regexp_replace(cast(current_date as string),'-','')||'-*.csv';
EXECUTE IMMEDIATE FORMAT("""

CREATE EXTERNAL TABLE `test-project.TEMP_PROCESSING.test_external_table`
OPTIONS (
  format="CSV",
  uris = [%gcs_bucket_location],
  location = 'EU',
  skip_leading_rows = 1
  );
""",gcs_bucket_location);
end

getting this.

Found unsupported function call 'ARRAY[...]'; failed to evaluate 'uris' in external table OPTIONS

feel free to suggest if there is another approach to do so.

reference article https://medium.com/google-cloud/merge-clean-transform-your-csvs-easily-with-bigquery-3a73c0c26d57