CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Sreekanth
Sreekanth

Reputation: 1947

bq command line for DML

1) Is there a way to run below update and merge statments from bq command line?

2) if i save this query in a file (update.sql or merge.sql) , can i pass this file as input to bq command and execute.

If we can do any of above , then i can call that though a script in Informatica job and make it a part of ETL jobs for post load processing.

Thanks for the help. regards, Sreekanth

UPDATE cloud-dw-poc.PREPARED_LAYER.DW_ORDER_LINE_I TGT SET TGT.BILLABLE_FLAG=SRC.BILLABLE_FLAG FROM cloud-dw-poc.PREPARED_LAYER.ORDER_LINE_BILLABLE_FLG_VW SRC WHERE TGT.INTEGRATION_ID=SRC.ORDER_LINE_ID

MERGE cloud-dw-poc.PREPARED_LAYER.DW_ORDER_LINE_I TGT USING cloud-dw-poc.PREPARED_LAYER.ORDER_LINE_BILLABLE_FLG_VW SRC ON (TGT.INTEGRATION_ID=SRC.ORDER_LINE_ID) WHEN MATCHED THEN UPDATE SET TGT.BILLABLE_FLAG=SRC.BILLABLE_FLAG

Upvotes: 1

Views: 1131

Answers (1)

Lefteris S
Lefteris S

Reputation: 1672

You can run DDL queries via command line with bq query, in the following manner:

bq query --use_legacy_sql=false 'MERGE cloud-dw-poc.PREPARED_LAYER.DW_ORDER_LINE_I TGT USING cloud-dw-poc.PREPARED_LAYER.ORDER_LINE_BILLABLE_FLG_VW SRC ON (TGT.INTEGRATION_ID=SRC.ORDER_LINE_ID) WHEN MATCHED THEN UPDATE SET TGT.BILLABLE_FLAG=SRC.BILLABLE_FLAG'

If you'd rather pass the query as a file, you can do:

cat merge.sql | bq query --use_legacy_sql=false

Upvotes: 1

Related Questions