Skip to content

Instantly share code, notes, and snippets.

@gopinath678

gopinath678/big query merge.sql

Forked from maximveksler/big query merge.sql
Created January 25, 2022 08:31
Show Gist options
  • Save gopinath678/f2a19dd99e402bcd45938c789a7df56a to your computer and use it in GitHub Desktop.
Save gopinath678/f2a19dd99e402bcd45938c789a7df56a to your computer and use it in GitHub Desktop.
Download ZIP
Big Query Table Merge
Raw
big query merge.sql
# Google BigQuery - Merge data
bigquery_merge() {
echo "Loading ${GC_BQ_WORK_TABLE} table into ${GC_BQ_MERGE}"
bq --location=US load --source_format=NEWLINE_DELIMITED_JSON --encoding=UTF-8 \
"${GC_BQ_MERGE}.${GC_BQ_WORK_TABLE}" "${TRANSFORM_STORE}/${filename}" \
schemas/${GC_BQ_TABLE}.json.schema
echo "Merging into ${GC_BQ_DATASET}.${GC_BQ_TABLE}"
cat <<bigtable-merge | bq --location=US query --use_legacy_sql=false --max_rows=0
MERGE \`${GC_BQ_DATASET}.${GC_BQ_TABLE}\` T
USING \`${GC_BQ_MERGE}.${GC_BQ_WORK_TABLE}\` S
ON T.user_id = S.user_id
WHEN MATCHED THEN DELETE
bigtable-merge
bq --location=US query --use_legacy_sql=false --append_table=true --max_rows=0 \
--destination_table="${GC_BQ_DATASET}.${GC_BQ_TABLE}" \
"select * from \`${GC_BQ_MERGE}.${GC_BQ_WORK_TABLE}\`"
echo "Cleaning..."
bq rm -f -t "${GC_BQ_MERGE}.${GC_BQ_WORK_TABLE}"
echo "******** DONE ${GC_BQ_DATASET}.${GC_BQ_TABLE} ********"
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment