CODEWITHSUNDEEP
node.jsgoogle-bigquerygoogle-cloud-storageuser-defined-functions
Eunhee Cho
Eunhee Cho

Reputation: 11

How to use JS function in UDF BigQuery when checking file exists in Google Cloud Storage

I'd like to use JS function in BigQuery when checking if file exists in Google Cloud Storage. But BigQuery kept showing error when **select UDF_FUNCTION_NAME() **

ReferenceError: require is not defined at

My JS code is like this.

BUCKET PATH is gs://MY_BUCKET_PATH/FILE_NAME This function is checking if the file exists in the path.

function FileExist(){
  const {Storage} = require('@google-cloud/storage');
  const storage=new Storage();
  return (storage.bucket('MY_BUCKET_PATH').file('FILE_NAME').exists());
}

And my UDF is like this.

  My_Function ()
  RETURNS BOOL
  LANGUAGE js 
  OPTIONS (
    library=["gs://MY_BUCKET_PATH/FILE_NAME"]
  )
  AS r"""
    return FileExist();
  """;

How can I make this bigquery UDF use JS in google cloud storage?

I tried put JS code in udf but it didn't work because there is npm library problem I think. So now I'm trying to use function in library.

Upvotes: 1

Views: 801

Answers (1)

Samuel
Samuel

Reputation: 3528

How to list all files in google cloud storage (GCS).

Please read the manual first.

Lets assume that your GCS is gs://test1234 and in the region zone europe-west3.

First add a connection in BigQuery:

enter image description here

enter image description here

Set the zone and the name (here) test123

enter image description here

Create this connection and go to it in BigQuery. Find under Service account id the e-mail address of it and copy it.

Go to IAM, add an user with that e-mail address and give this the right to view cloud storage. enter image description here

In BigQuery run this query to generate a table showing all content of the gcs bucket:

CREATE EXTERNAL TABLE `yourdataset.yournewtable`
WITH CONNECTION `europe-west3.test123`
OPTIONS(
  object_metadata = 'SIMPLE',
  uris = ['gs://test1234/*'],
  max_staleness = INTERVAL 4 HOUR,
  metadata_cache_mode = 'MANUAL');

Upvotes: 0

Related Questions