Usage:-
- Read the data from the file on Google storage
without loading it into Big query by treating the file as temporary
table.
- We can query the data of file and also perform
DML operations using this data of file.
Examples:-
- 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;'
- 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;'
- 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;'
