Big Query External Table Usuage

Usage:-

1. Read the data  from the file on Google storage  without loading it into Big query by treating the file as temporary table.
2. We can query the data of file  and also perform DML operations using this data of file.

Examples:-

1. Query the file data:-

bq --location=US query --external_table_definition=ORDER_DEL::INTEGRATION_ID:STRING@CSV=gs://cloud-dw-poc.appspot.com/PREPARED_LAYER/results-20180801-181812.csv 'SELECT INTEGRATION_ID FROM ORDER_DEL;'

2. Update the  Main table using the data of File.

bq --location=US query   --use_legacy_sql=false --external_table_definition=ORDER_DEL::INTEGRATION_ID:STRING@CSV=gs://cloud-dw-poc.appspot.com/PREPARED_LAYER/results-20180801-181812.csv 'UPDATE  `cloud-dw-poc.Z_NOT_USED.ORDER_DEL_NEW1` TGT SET   DELETE_FLG ="Y" FROM ORDER_DEL SRC  WHERE  TGT.INTEGRATION_ID =SRC.INTEGRATION_ID;'

3. Append to   Main table using the data of File.

bq query --append_table=true --batch=false --use_legacy_sql=false --destination_table=my-second-project-196504:DML_Operations.CRIME_DL --external_table_definition=TABLE::unique_key:INTEGER@CSV=gs://my-second-project-196504/CRIME.csv 'SELECT unique_key FROM TABLE;'