hui-zheng

# cat multiple files and show filenames
grep ^ /dev/null $@

hui-zheng

-- base_time has t
WITH rows_by_key AS(
  SELECT 
    surrogate_key, 
    array_agg(base_table) as _rows, 
    count(*) as _count
  FROM `gcp_project.data_set.original_table` as base_table
  WHERE stamp BETWEEN "2020-03-12T00:00:00" AND "2020-03-14T00:00:00"
  GROUP BY surrogate_key
)

hui-zheng

BY_LIST_FILE="NONE"
COMPLETED="1/."
AGE="3"
NAME_PATTERN=".*"


while [[ $# -gt 0 ]]; do
key="$1"

case $key in

hui-zheng

-- Below is a fancy version of non-null-greatest() for multi-columns. 
-- it is more extensible for more two columns.
WITH base AS (
  SELECT
    (SELECT ARRAY_AGG (x IGNORE NULLS) AS Y FROM UNNEST ([col_1, col_2, col_3, col_4]) AS x)
    AS array,
  FROM source_table AS nl
)
SELECT
  (SELECT MAX(y) FROM UNNEST(array) AS Y

hui-zheng

-- WARNING: back up the table before this operation
-- FOR large size timestamp partitioned table 
-- -------------------------------------------
-- -- To de-duplicate rows of a given range of a partition table, using surrage_key as unique id
-- -------------------------------------------

DECLARE dt_start DEFAULT TIMESTAMP("2019-09-17T00:00:00", "America/Los_Angeles") ;
DECLARE dt_end DEFAULT TIMESTAMP("2019-09-22T00:00:00", "America/Los_Angeles");

MERGE INTO `gcp_project`.`data_set`.`the_table` AS INTERNAL_DEST