dbq

dbq

CLI tool to easily Decorate BigQuery table name

Description

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 value
• dbq also supports timezone calculation.

dbq will cut down on considerable data processed and spending :bowtie: 💰

• 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.

Installation

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.

Requirements

• bq-command-line-tool

Usage

# 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"

Placeholders

• @ will be replaced with @<time1>-<time2>
• required
• _tz(datetime) will be replaced with DATE_ADD('datetime', tz value, 'HOUR')
• optional

DryRun

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. 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)

Options

$ 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

CHANGELOG

See CHANGELOG