0

I am executing BigQuery command via api in CloudShell for extracting view definitions using shell script as shown below

for file_name in $(cat view_list.txt)
do
    file_name=${file_name/.sql/}
    echo "$file_name"

    query_output=`bq query --batch --use_legacy_sql=false --quiet --format=sparse \
    "
    SELECT view_definition
    FROM projectid.datasetid.INFORMATION_SCHEMA.VIEWS
    where table_name = '$file_name'
    " | awk '{if(NR>2)print}'`
    echo "$query_output" >> "./views/$file_name".sql

done

Some of the views are getting extracted in full and some are just getting truncated with ellipsis showing up. Out of just sample 10 that I ran 5 of them are in this manner. Can someone help me where I am going wrong with this?

enter image description here

VKarthik
  • 1,379
  • 2
  • 15
  • 30
  • For me I was able to get the result as it is. You are using **{if(NR>2)print}** to remove the column header right ? Did you tried to run a sample query in your BQ console and check whether its returning the proper result or not ? It feels like more of a scripting issue. – Sayan Bhattacharya Jul 06 '22 at 12:46
  • Hi @SayanBhattacharya, When you say sample query, I take it you are referring to checking view definition in BigQuery? If so, yes the actual view definition is being returned and is not having any ellipsis. – VKarthik Jul 07 '22 at 22:36
  • Your script runs fine in my GCP shell. Can you try to test a bq comand with erroneous sample and check what is the output it is giving .Something like this : `bq query --use_legacy_sql=false --format=sparse "query with where clause'" | sed '1,2d' > output/abc.sql`. And later on add `--batch` , `--quiet` .I have used `sed` here you can try `awk 'NR>1' ` or `tail +3` whatever you want. – Sayan Bhattacharya Jul 08 '22 at 05:35

0 Answers0