CODEWITHSUNDEEP
bashgoogle-cloud-platformgoogle-bigquery
Dead pool
Dead pool

Reputation: 123

BigQuery 'MERGE: command not found' command is not working

I am trying to run a script file which contains creation of temp table, load the data, creation of test table, merge the data and delete the table in BigQuery. Intial 3 commands is working fine but MERGE command is not working.

bq mk --table --expiration 3600 --description "This is my source table" dataset_name.table_name 
emp_id:INTEGER,emp_name:STRING
bq load --source_format=CSV income_tracking.temp_table /home/username/physical.csv
bq mk --table --expiration 3600 --description "This is my target table" dataset_name.table_name 
emp_id:INTEGER,emp_name:STRING
MERGE dataset.test_data T
USING dataset.temp_table S
ON T.emp_id = S.emp_id
WHEN MATCHED THEN
UPDATE SET emp_name = S.emp_name
WHEN NOT MATCHED THEN
INSERT (emp_id, emp_name) VALUES(emp_id, emp_name)
bq rm -f -t project_id:dataset_name.temp_table

Here MERGE command is not working and error is - 

username@cloudshell:~ (bmg-dt-prd-svc)$ bash bq_merge.sh
Table 'project_id:dataset_name.table_name' successfully created.
Upload complete.
Waiting on bqjob_r75d2358a0232abdb_0000016f227e7a0f_1 ... (1s) Current status: DONE   
Table 'project_id:dataset_name.table_name' successfully created.
bq_merge.sh: line 4: MERGE: command not found
bq_merge.sh: line 5: USING: command not found
bq_merge.sh: line 6: ON: command not found
bq_merge.sh: line 7: WHEN: command not found
bq_merge.sh: line 8: UPDATE: command not found
bq_merge.sh: line 9: WHEN: command not found
bq_merge.sh: line 10: syntax error near unexpected token `emp_id,'
'q_merge.sh: line 10: `  INSERT (emp_id, emp_name) VALUES(emp_id, emp_name)

What would be the solution, Thanks

Upvotes: 1

Views: 641

Answers (1)

itroulli
itroulli

Reputation: 2094

The issue here is that you are trying to run the query as a bash command. You should replace the query in your bash script with the following command:

bq query --use_legacy_sql=false \
"""
MERGE dataset.test_data T
USING dataset.temp_table S
ON T.emp_id = S.emp_id
WHEN MATCHED THEN
UPDATE SET emp_name = S.emp_name
WHEN NOT MATCHED THEN
INSERT (emp_id, emp_name) VALUES(emp_id, emp_name)
"""

Upvotes: 4

Related Questions