This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

GoogleSQL for BigQuery supports the following ObjectRef functions.

This topic includes functions that let you create and interact withObjectRefandObjectRefRuntimevalues.

Function list

OBJ.FETCH_METADATA

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

OBJ.FETCH_METADATA(objectref)

Description

TheOBJ.FETCH_METADATA function returns Cloud Storage metadata for a partiallypopulatedObjectRefvalue.

To fetch object metadata, you must have thebigquery.objectRefs.readpermission on the Cloud resource connection specified in theauthorizerfield of the inputObjectRef value. You can get this permission from theBigQuery ObjectRef Reader (roles/bigquery.objectRefReader)orBigQuery ObjectRef Admin (roles/bigquery.objectRefAdmin)role.

This function still succeeds if there is a problem fetching metadata. In thiscase, thedetails field contains anerror field with theerror message, as shown in the following example:

{  "details": {    "errors":{      "OBJ.FETCH_METADATA":"Connection credential for projects/myproject/locations/us/connections/connection1 cannot be used. Either the connection does not exist, or the user does not have sufficient permissions to use it."    }  }}

Definitions

• objectref: A partially populatedObjectRef value, in which theuri andauthorizer fields are populated and thedetails field isn't.

Output

A fully populatedObjectRef value. The metadata is provided in thedetailsfield of the returnedObjectRef value.

Example

This example returns the metadata for a JPG object.

SELECTOBJ.FETCH_METADATA(OBJ.MAKE_REF("gs://mybucket/path/to/file.jpg","us.connection1"));

OBJ.GET_ACCESS_URL

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

OBJ.GET_ACCESS_URL(objectref,mode[,duration])

Description

TheOBJ.GET_ACCESS_URL function returns JSON that contains referenceinformation for the inputObjectRefvalue, and alsoaccess URLs that you can use to read or modify the Cloud Storage object.

To create a URL to read the object, you must have thebigquery.objectRefs.read permission on the Cloud resource connectionspecified in theauthorizer field of the inputObjectRef value. You can getthis permission from theBigQuery ObjectRef Reader (roles/bigquery.objectRefReader)orBigQuery ObjectRef Admin (roles/bigquery.objectRefAdmin)role.

To create a URL to modify the object, you must have thebigquery.objectRefs.write permission on the Cloud resource connectionspecified in theauthorizer field of the inputObjectRef value. You can getthis permission from theBigQuery ObjectRef Admin (roles/bigquery.objectRefAdmin) role.

If the function encounters an error, the returned JSON contains aruntime_errors field with the error message instead of theaccess_urls field with the access URLs. This is shown in the followingexample:

{  "objectref": {    "authorizer": "myproject.us.connection1",    "uri": "gs://mybucket/path/to/file.jpg"  },  "runtime_errors": {    "OBJ.GET_ACCESS_URL": "Connection credential for projects/myproject/locations/us/connections/connection1 cannot be used. Either the connection does not exist, or the user does not have sufficient permissions to use it."  }}

Definitions

• objectref: AnObjectRef value that represents a Cloud Storage object.

• mode: ASTRING value that identifies the type of URL that you want tobe returned. The following values are supported:

  • r: Returns a URL that lets you read the object.
  • rw: Returns two URLs, one that lets you read the object, andone that lets you modify the object.

• duration: An optionalINTERVAL value that specifies how longthe generated access URLs remain valid. You can specify a value between30 minutes and 6 hours. For example, you could specifyINTERVAL 2 HOUR to generate URLs that expire after 2 hours. The defaultvalue is 6 hours.

Output

A JSON value that contains the Cloud Storage object referenceinformation from the inputObjectRef value, and also one or more URLs thatyou can use to access the Cloud Storage object.

The JSON output is returned in theObjectRefRuntimeschema:

obj_ref_runtime json {  obj_ref json {    uri string, // Cloud Storage object URI    version string, // Cloud Storage object version    authorizer string, // Cloud resource connection to use for object access    details json { // Cloud Storage managed object metadata      gcs_metadata json {      }    }  }  access_urls json {    read_url string, // read-only signed url    write_url string, // writeable signed url    expiry_time string // the URL expiration time in YYYY-MM-DD'T'HH:MM:SS'Z' format  }}

Example

This example returns read URLs for all of the image objects associated withthe films in themydataset.films table, where theposter column is astruct in theObjectRef schema. The URLs expire in 45 minutes.

SELECTOBJ.GET_ACCESS_URL(poster,'r',INTERVAL45MINUTE)ASread_urlFROMmydataset.films;

OBJ.MAKE_REF

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

OBJ.MAKE_REF(uri,authorizer)

OBJ.MAKE_REF(objectref_json)

Description

Use theOBJ.MAKE_REF function to create anObjectRefvalue that contains reference information for a Cloud Storage object.You can use this function in workflows similar to the following:

1. Transform an object.
2. Save it to Cloud Storage using a writable signed URL that you created byusing theOBJ.GET_ACCESS_URL function.
3. Create anObjectRef value for the transformation output by using theOBJ.MAKE_REF function
4. Save theObjectRef value by writing it to a table column.

Definitions

• uri: ASTRING value that contains the URI for the Cloud Storageobject, for example,gs://mybucket/flowers/12345.jpg. You can alsospecify a column name in place of a string literal. For example, if youhave URI data in auri field, you can specifyOBJ.MAKE_REF(uri, "myproject.us.conn").

• authorizer: ASTRING value that contains theCloud Resource connectionused to access the Cloud Storage object. To read or write usingObjectRef returned from this function, the connection's service account musthave the following roles on the Cloud Storage bucket that stores theobject:

  • Storage Object User (roles/storage.objectUser) for reads and writes
  • Storage Object Viewer (roles/storage.objectViewer) for reads

  Theauthorizer value must be in the formatlocation.connection_id. For example,use-west1.myconnection. You can get the connection ID or service account byviewing the connection detailsin the Cloud console. You can copy the value in the last section of thefully qualified connection ID that is shown inConnection ID. For example,projects/myproject/locations/connection_location/connections/myconnection.

  The connection must be in the same project and region as the query whereyou are calling the function.

• objectref_json: AJSON value that represents a Cloud Storage object,using the following schema:

  obj_ref json {  uri string,  authorizer string}

No validations are performed on the input values.

Output

AnObjectRef value.

AnObjectRef value represents a Cloud Storage object, including the objectURI, size, type, and similar metadata. It also contains an authorizer, whichidentifies theCloud resource connectionto use to access the Cloud Storage object from BigQuery. AnObjectRefvalue is a struct in the following format:

struct{uristring,//CloudStorageobjectURIversionstring,//CloudStorageobjectversionauthorizerstring,//Cloudresourceconnectiontouseforobjectaccessdetailsjson{//CloudStoragemanagedobjectmetadatagcs_metadatajson{"content_type":string,//forexample,"image/png""md5_hash":string,//forexample,"d9c38814e44028bf7a012131941d5631""size":number,//forexample,23000"updated":number//forexample,1741374857000000}}}

When you use theuri andauthorizer arguments as input, the outputObjectRef value contains a reference to a Cloud Storage object. When you usetheobjectref_json argument as input, the outputObjectRef value contains astruct that is equivalent to the input JSON value.

Examples

This example creates anObjectRef value using a URI and a Cloudresource connection as input:

CREATEORREPLACETABLE`mydataset.movies`AS(SELECTf.title,f.directorOBJ.MAKE_REF(p.uri,'asia-south2.storage_connection')ASmovie_posterFROMmydataset.movie_posterspjoinmydataset.filmsfusing(title)whereregion='US'andrelease_year=2024);

This example creates anObjectRef value using JSON input:

OBJ.MAKE_REF(JSON'{"uri": "gs://mybucket/flowers/12345.jpg", "authorizer": "asia-south2.storage_connection"}');

Limitations

You can't have more than 20 connections in the project where you are runningqueries that referenceObjectRef orObjectRefRuntime values.