How to add arguments in the big query table name when running queries via script?

Question

We have many tables which are following a certain pattern for example:- project.dataset.ffm_{lang}_{event}.

I want to create a duplicate table for all these tables via a script by adding clustering and partitioning into them. I tried parameterized queries which is supported by big query but we cannot add parameters to table name.

I also tried wildcard function but was not able to find any way to create a table of a particular pattern from another table which follow same kind of pattern.

The query i want to run is something like this:-

   create table `project.dataset.ffm_demo_{lang}_{event}`
   like `project.dataset.ffm_{lang}_{event}`

Mazlum Tosun · Accepted Answer · 2022-11-22 13:52:07Z

You can develop you own Shell script to create your tables dynamically based on a configured list, example :

#!/usr/bin/env bash
set -e
set -o pipefail
set -u

# Declare and configure your lang_event list
declare -a StringArray=("Lang1_event1" "Lang2_event2")

# Iterate the string array using for loop
for val in "${StringArray[@]}"; do
  echo "Lang event : $val"
  
  # Use bq to create your current table
  bq mk \
    -t \
    --expiration 3600 \
    --description "This is my table" \
    --label organization:development \
    "mydataset.ff_demo_$val" \
    qtr:STRING,sales:FLOAT,year:STRING
done

Some explanations :

Declare and configure your list of lang_event
Loop over this list and for each element create a BigQuery table with bq and gcloud cli
In my example I used a fake table schema qtr:STRING,sales:FLOAT,year:STRING, you have to adapt it for your table

Collectives™ on Stack Overflow

How to add arguments in the big query table name when running queries via script?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related