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.
dbqsupports both Relative value and Absolute valuedbqalso supports timezone calculation.
dbq will cut down on considerable data processed and spending 
💰
- BigQuery offers on-demand pricing for queries and 1 TB of data processed costs $5.
- For example, a query to a 6TB table costs $30. Any SQL statements (ex. with LIMIT 1) don't affect the pricing.
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>- required
_tz(datetime)will be replaced withDATE_ADD('datetime', tz value, 'HOUR')- optional
The option of dryRun shows how much cut down full scan bytes, so I strongly recommend to use this option before running any queries.
- A query with no table decorator will process 6.0 TiB, then costs
6.0 * $5 = $30. - A query with table decorator will process 110 GiB, then costs
0.1 * $5 = $0.5.dbqwill 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