CLI tool to easily Decorate BigQuery table name
MIT License
CLI tool to easily Decorate BigQuery table name
dbq
enables you to use Table Range Decorators to perform a more cost-effective query to BigQuery without complex calculation.
dbq
supports both Relative value and Absolute valuedbq
also supports timezone calculation.dbq
will cut down on considerable data processed and spending :bowtie: 💰
To install dbq, please use go get.
$ go get github.com/yoheimuta/dbq
...
$ dbq help
...
Or you can download a binary from github relases page and place it in $PATH directory.
# no-option equal to `--beforeHour=3`
$ dbq query "SELECT * FROM [foo.bar@]"
# equal to SELECT * FROM [foo.bar@-10800000-]
$ dbq query "SELECT * FROM [foo.bar@]" --beforeHour=3
# equal to SELECT * FROM [foo.bar@1436371200000-]
$ dbq query "SELECT * FROM [foo.bar@]" --startDate="2015-07-08 17:00:00"
# equal to SELECT * FROM [foo.bar@1436371200000-1436382000000]
$ dbq query "SELECT * FROM [foo.bar@]" --startDate="2015-07-08 17:00:00" --endDate="2015-07-08 18:00:00"
# equal to SELECT * FROM [foo.bar@1436338800000-]
$ dbq query "SELECT * FROM [foo.bar@]" --startDate="2015-07-08 17:00:00" --tz="-9"
# equal to SELECT * FROM [foo.bar@1436338800000-] WHERE DATE_ADD('2015-07-08 17:00:00', -9, 'HOUR') <= time and time <= DATE_ADD('2015-07-08 18:00:00', -9, 'HOUR')
$ dbq query "SELECT * FROM [foo.bar@] WHERE _tz(2015-07-08 17:00:00) <= time and time <= _tz(2015-07-08 18:00:00)" --startDate="2015-07-08 17:00:00" --tz="-9"
@
will be replaced with @<time1>-<time2>
_tz(datetime)
will be replaced with DATE_ADD('datetime', tz value, 'HOUR')
The option of dryRun
shows how much cut down full scan bytes, so I strongly recommend to use this option before running any queries.
6.0 * $5 = $30
.0.1 * $5 = $0.5
. dbq
will save $29.5
.$ dbq query "SELECT * FROM [foo.bar@]" --dryRun
Raw: SELECT * FROM [foo.bar]
Query successfully validated. Assuming the tables are not modified, running this query will process 6630178173385 bytes of data.
- 6630178173385 bytes equal to 6,630,178,173,385 bytes
- 6630178173385 bytes equal to 6.0TiB
- 6630178173385 bytes equal to $30.15056 (= 6.03011 TiB * $5)
Decorated: SELECT * FROM [foo.bar@-10800000-]
Query successfully validated. Assuming the tables are not modified, running this query will process 117636313873 bytes of data.
- 117636313873 bytes equal to 117,636,313,873 bytes
- 117636313873 bytes equal to 110GiB
- 117636313873 bytes equal to $0.53495 (= 0.10699 TiB * $5)
$ dbq help query
NAME:
query - Run bq query with complementing table range decorator
USAGE:
command query [command options] [arguments...]
DESCRIPTION:
OPTIONS:
--beforeHour '3' a decimal to specify the hour ago, relative to the current time
--startDate a datetime to specify date range with end flag
--endDate a datetime to specify date range with start flag
--tz '0' a decimal of hour or -hour to add to start and end datetime, considering timezone
--buffer '1' a decimal of hour to add to start and end datetime, it's heuristic value
--gflags no support. Use onlyStatement instead
--cflags no support. Use onlyStatement instead
--verbose a flag to output verbosely
--dryRun a flag to run without any changes
--onlyStatement a flag to output only a decorated statement
See CHANGELOG