example

How to deploy a new version of the cloud function?

1. Install gcloud dependency following GCP instructions here.

2. Setup PROJECT_ID and REGION environment variables

export PROJECT_ID="my_project"
export REGION="us-central1"

1. Run gcloud functions deploy command

gcloud "--project=${PROJECT_ID}" \
        functions deploy "example-bigquery-cloud-function" \
        "--set-env-vars=PROJECT_ID=${PROJECT_ID}" \
        "--entry-point=bigquery" \
        "--region=${REGION}" \
        --gen2 \
        --runtime=go120 \
        --memory=256MiB \
        --source=. \
        --trigger-http \
        --allow-unauthenticated \
        --ingress-settings=internal-only

How to create a UDF (user defined funcition) in Big Query

1. Create a connection to your Big Query project

bq mk --connection --display_name="Connection to remote Cloud Function" --connection_type=CLOUD_RESOURCE --project_id=${PROJECT_ID} --location=US remote-function-connection

1. Verify if your connection was created with success

bq show --location=US --connection  remote-function-connection

1. Get service account from your connection

export BQ_SVC_ACCOUNT=`bq show --format=json --location=US --connection  remote-function-connection  | jq -r '.cloudResource.serviceAccountId'`

echo ${BQ_SVC_ACCOUNT}

1. Allow service account from connect to call your cloud function

gcloud functions add-iam-policy-binding example-bigquery-cloud-function \
  --member="serviceAccount:${BQ_SVC_ACCOUNT}" \
  --role="roles/cloudfunctions.invoker"

1. Create your UDF inside Big Query to call your cloud function

bq --format=json query \
    --dataset_id=$PROJECT_ID:dataset  \
    --location=US \
    --nouse_legacy_sql  \
    "CREATE OR REPLACE FUNCTION example_function(x STRING)
    RETURNS STRING REMOTE WITH CONNECTION `${PROJECT_ID}.US.remote-function-connection` OPTIONS (endpoint = 'https://${REGION}-${PROJECT_ID}.cloudfunctions.net/example-bigquery-cloud-function', user_defined_context = [])"

1. Use your function inside any query

    SELECT `your_project.your_dataset.example_function`("I'M A TEST")