How to Insert SQL queries with multiple quotes into a JSON parameter as a shell variable-CodePudding

I have sql file that contain multiple queries inside (as a single line) that contain lots of single quote ('), double quote (") and a backtick (`) inside.

e.g.:

declare x ... ; create table ...; select ... ; . . .

And I have shell script in which this script will update the queries in previous sql file to the existing scheduled query in BigQuery. It will read the file and put it in a shell variable and then use that variable in the parameter "params" which read a JSON format string

query=`cat queries.sql`
bq update \
--project_id=$project \
--location=$location \
--display_name=$job_name \
--schedule="every day 21:00" \
--params="{\"query\":\"$queries\"}" \
--transfer_config \
projects/<id>/locations/<location>>transferConfigs/<config-id>

When I run this shell script, I got this error:

BigQuery error in update operation: Parameters should be specified in JSON format when creating the transfer configuration.

I am pretty sure that its because of these quotes inside the sql script but how should I put in the parameter "params" which receive a json format input. How should I do this?

CodePudding user response：

It's quite confusing work with multiple strings one inside another, reading files, using variables and so one. The way I could make it was as following:

#Avoid substitute any * for a wildcard :
set -f

# Change " -> \" and ' -> \" :
query=$(sed 's/"/\\"/g' queries.sql | sed 's/'"'"'/\\"/g')

#create the json var:
json_query='{"query":"'$query'"}'

#Generate de bq command:
cmd="bq update --project_id=$project --location=$location --display_name=$job_name --schedule='every day 21:00' --params='${json_query}' --transfer_config projects/<id>/locations/<location>>transferConfigs/<config-id>"

# Send to temp file and execute:
echo $cmd > tmp
bash tmp

CodePudding user response：

I would use jq for getting rid of the escaping problems:

#!/bin/bash

json=$(jq -nc --arg query "$(<queries.sql)" '{ "query": $query }')

bq update \
--project_id=$project \
--location=$location \
--display_name=$job_name \
--schedule="every day 21:00" \
--params="$json" \
--transfer_config \
projects/<id>/locations/<location>>transferConfigs/<config-id>