CODEWITHSUNDEEP
sqlgoogle-bigqueryiterationsharding
ΑΘΩ
ΑΘΩ

Reputation: 121

Efficient method for sharding BigQuery table collection

I would like to ask for advice regarding the following task: assume a collection of BQ tables bearing names with structure name_YYYYMM and containing each a DATETIME type column called date_time whose values all belong to the YYYYMM month specified by the table suffix (a form of pseudo-sharding, in other words). The objective is to properly shard this collection, by creating a new collection of derived tables, bearing names with structure name_YYYYMMDD.

What I envision in principle is an INSERT statement iteration over the range of all YYYYMMDD dates emerging from the month-suffixes of the original tables. Within this iteration each individual statement would look as follows:

INSERT INTO `name_(#YYYYMMDD format date#)`
SELECT ...
FROM name_YYYYMM
WHERE FORMAT_DATETIME("%Y%m%d", date_time)=#YYYYMMDD format date#

What I am not aware of is a concrete & efficient method to somehow pass the iterated string variable #YYYYMMDD format date# as a suffix of the name of each correctly sharded table in the desired new collection. If anyone could enlighten me in this regard I would be very grateful.

Upvotes: 0

Views: 74

Answers (0)

Related Questions