Companion tool for Google BigQuery UI used by myself and my peers at Viant Inc. Adds some extra functionality to BQ Native UI
Now: Finally (based on demand) decided to add BQ Mate for New UI - started with just cost estimation (most popular feature till now). Will see what next based on feedback and mood :o) Previous releases: CHANGE: Pivot Button moved to front (previously was available with JSON view only) - Build Pivot with Data available on current Page. IMPORTANT - to cover more data (up to 500 rows) - expand Result Panel with + Button NEW: Full Screen View Button FIX: issue with "Add Fields" feature NEW: Added Keyboard Shortcuts to view previous/next page - Ctrl-Left, Ctrl-Right and first/last page - Ctrl-Down, Ctrl-Up (When previewing complete tables or when looking at query results) Note: Mac users to use Alt with respective Arrows NEW: Support for BigQuery Named Parameters (Magnus style Parameters are still supported) FIX: Parameter replacement breaks UDFs FIX: Parameters not always replaced correctly respective SQL NEW: Added Switch SQL Version button for quick switch between Legacy and Standard SQL. Also ALT-L shortcut can be used for this! Double Click or SHIFT-ALT-L saves current SQL Version as default. NEW & IMPROVED: Query Outline; From Context Menu or Mate toolbar FIX: Keywords in Standard SQL Mode FIX: Support for "Run Selected" with CTRL-E or CTRL-F5 NEW: Contextual Help via F1 or Right-Click Menu FIX: Quick fix for Thousands Separator for null and empty FIX: Quick fix for Datasets Filter as Google yet another time changing stuff around! NEW: Thousands Separator for numeric fields! Features: 1. Control size of the Navigation Panel (retired since Jan.31 2016) 2. Hide/Show Navigation Panel 3. Datasets Filtering 4. Filtered / Total Datasets Count 5. Monetary Cost estimation based on processed bytes 6. Keywords Auto-Capitalization 7. Comment/Uncomment Selection or Outside Selection 8. Support for Parametrized Query / Saved Query with Parameters 9. Tables Decoration / Manipulation 10. Find / Replace Functionality (with support for regular expressions) 11. Tabs Auto naming after query name (saved query) 12. Navigator: list of all BQ sessions across your PC with quick info and ability to jump to New in v1.1: 13. Query Outline 14. Extended Result Panel 15. F5 to execute query New in v1.2: 16. Interactive Pivots / Charts of Query Result New in v1.4: 17. Search / Filter Recent Queries New in v1.5: 18. Tables Filtering 19. Add All Fields to Query Editor, with or w/o alias 20. Thousands separator for numeric fields 21. Contextual Help / F1 22. Quick switch between Legacy and Standard SQL 23. Save Default SQL Version 24. Support for BigQuery Named Paramaters (@param) 25. Keyboard Shortcuts to view previous/next and first/last page Some Details: 1. Control size of the Datasets Panel Datasets and tables names tend to be verbose. Allowing users to tune width of Datasets Panel is just little good to have. 2. Hide/Show Datasets Panel Most of your time you spend playing with Query itself. Saving your screen real estate by hiding Datasets panel is another good to have 3. Datasets Filtering Some of our Projects consist of hundreds and hundreds of datasets with tens of thousands tables in them available to users across the business. Ability to filter visible datasets helps navigate and locate needed dataset 4. Datasets Count (Filtered/Total) Just simple counts 5. Monetary Cost estimation based on processed bytes Estimated cost added to the validator and is based on estimated processing bytes. As well as it is based on billing tier 1 pricing ($5 per TB currently) 6. Keywords Auto-Capitalization “Auto-capitalization as you type” is added for GBQ Keywords. It is only html decoration so copy/paste of BQL still is as originally typed and not as it is shown. To address this – Preserve Format (in context menu) is introduced. After format is preserved – decoration is preserved as actual text. So you can copy paste “well-formatted” text. 7. Comment/Uncomment Selection or Outside Selection Ctrl+/ and Shift+Ctrl+/ respectively comments and uncomments selected lines Ctrl+Alt+/ and Shift+Ctrl+Alt+/ respectively comment and uncomment lines outside selection 8. Support for Parametrized Query / Saved Query with Parameters If you have you Saved Queries stored with parameters – now you can easily bulk set those parameters using Parameters Button. Note: parameters in this tool defined after another internal Viant’s BigQuery centric Orchestration and Scheduling Tool called Magnus. Magnus is using following naming rule for parameters: <var_parametername> You can adopt this naming to benefit Parameters Button functionality. Otherwise check Find / Replace Functionality 9. Tables Decoration / Manipulation Using Decorate Button allows to enumerate all recognized (by Native UI) tables in query and present them to user for further decoration. This can be useful during dev/testing phase to minimize cost or to do fancy stuff with querying table’s specific time slice, etc. 10. Find / Replace Functionality (with support for regular expressions) With regular expressions user can find any code fragments and replace them either individually or by replace pattern using captured matches. This is not text editor’s find/ replace - so feature somewhat limited - replace next is not supported yet. So far - either all or nothing 11. Tabs Auto naming after query name (saved query, new query) Unfortunately all BigQuery Sessions are named as “Google BigQuery” which makes it hard to navigate through many opened queries and to locate needed one. Partially this is addressed by this extension’s Navigator feature. In addition, this extension will automatically name user’s session with the name of opened Saved Query. This will be reflected in all respective browser’s Tabs and in Tool’s Navigator. Sessions with New Query will be named “New Query” User also can change session name outside of this extension and it will be reflected in tool’s Navigator – till when user loads new saved query or opens new query – in which case session is auto name to respectively name of saved query or “New Query” 12. Navigator: list of all BQ sessions across your PC with quick info and ability to jump to When user has multiple BigQuery sessions opened across one or multiple browser windows – this feature helps to see all of them in one place with brief info and ability to jump right to the one needed. This feature available in any BigQuery sessions extension icon on the very right in the address bar 13. Query Outline When dealing with complex and "bushy" query (especially with someone else's) you want to have high level idea about the query. Here this mode is handy. It strips out everything from query but main structure. Use then CTRL+Z to get back original query 14. Extended Result Panel If you want to play with your result and discover something just by exploring data - this is your option. Click + Button on the left to the Query Result. This will leave Result Panel ONLY in view with hundreds of rows of data available at the same time per page. Click - Button and this will toggle view back to normal 15. F5 to execute query Many data tools use F5 as a shortcut to execute script. I've decided to add this in addition to Native UI's CTRL-Enter. 16. Interactive Pivots / Charts of Query Result User can pivot / chart query result. Currently only result available on page is "supported", so use extended result panel to chart more data. Meantime there is an "easter egg" that allows to pivot / chart whole result. If you will find it - please "Use it at your own risk" as processing big volume of data on client side can crash your session. 17. Search / Filter Recent Queries Allows to find specific query within Recent ones by search term and status. Make sure Recent Queries Panel is visible and then use Search Tab in Mate Navigator. 18. Tables Filtering If your dataset has long list of tables - you will love this feature! You can filter tables in dataset browser panel that will appear when you click on dataset in nav panel 19. Add Fields Add All Fields to Query Editor, with or w/o alias. Quite frequently want to script all tables’ fields out to Query Editor. This feature allows doing so and even with alias use. Try and you will see how it works 20. Thousands separator for numeric fields Switch use of thousands separator on and off. Click BQ Mate Button to see respective checkbox. Tested for US English locale 21. Contextual Help via F1 or Right-Click Menu Using Contextual Menu: Right Cklick on Selected Keyword and use Mate Menu Item "Help for 'keyword'" Using F1: Select keyword or just click on it and press F1 Note: Help is SQL Version sensitive - help resource depends selected sql version - legacy or standard sql 22. "Switch SQL Version" button for quick switch between Legacy and Standard SQL. Also ALT-L shortcut can be used for this! 23. Save Default SQL Version - Double Click on SQL button or Shift-Alt-L 24. Support for BigQuery Named Paramaters - @param Magnus style Parameters are still supported - <var_param> 25. Added Keyboard Shortcuts to view previous/next page - Ctrl-ArrowLeft, Ctrl-ArrowRight and first/last page - Ctrl-ArrowDown, Ctrl-ArrowUp v1.3 - fix for 14 and 16 introduced by recent Google UI change v1.4.34 - removed keywords capitalization within UDF Editor v1.4.36 (1/8/2016) - fixed multiple broken (as a result of last night changes by Google Team in BQ UI) features v1.5.41 (1/30/2016) - fixed multiple broken (as a result of last week massive changes by Google Team in BQ UI) features: resizing/hide/show; dataset filtering; param, replace, outline; search v1.5.45 (2/6/2016) - again changes on Google side - small fixes. And added new feature - tables filtering v1.5.49 (2/28/2016) - Add All Fields to Query Editor, with or w/o alias. v1.5.52 (4/13/2016) - Thousands separator for numeric fields v1.5.61 (5/29/2016) - Contextual Help v1.5.83/84 (12/23/2016) - Light refreshing and bringing back some functionality that was recently broken by newer version of Web UI. Added small perks while was there :o) v1.5.87 (2/9/2017) - Added `Switch SQL Version` button for quick switch between Legacy and Standard SQL. Also ALT-L shortcut can be used for this! v1.5.94 (2/10/2017 - Added Save current SQL Version as default - Double Click on `Switch SQL Version` button or SHIFT-ALT-L v1.5.96 (5/15/2017 - FIX: Parameters not always replaced correctly respective SQL) v1.5.98 (5/25/2017 - Support for BigQuery Named Parameters) v1.5.102 (6/29/2017) - Keyboard Shortcuts to view previous/next and first/last page Warning: Google Team is constantly working on improving their BigQuery UI. Sometimes this breaks features of the Tool. When this is a case, please calm down, stay tuned and check periorically for extension updates. I am trying to fix issues as soon as they are introduced and I see them! **** BigQuery Mate adds some functionality to Native BigQuery UI that is found helpful in day on day activity.
- (2019-04-23) Ciprian Dirnu: Great tool, I only wish it would work on the new GBQ UI as well.
- (2019-03-14) Mikhail is my favourite stack overflow hero and now he's made this extremely useful tool for BQ too. It works great and helps me save costs on my queries. Thanks Mikhail!
- (2018-08-23) Andrea Zonzin: Really useful and free but does not support the new bigquery interface so you still have to use the old one.
- (2017-11-18) David L: This is a great extension for anyone working with BigQuery. Especially helpful is the pricing calculator.
- (2016-08-25) Robert Lancashire: This is excellent! So useful. Particularly love the parameters and find/replace. We have queries which are thousands of words long and previously we had to copy them into a text editor, find and replace all the date ranges to run and then copy them back hoping you didn't cut a letter or word off by accident. With this, pop in two parameters for the start and end date, save the query and then each time we need to run just replace the parameters. Fantastic!
- (2016-08-11) Haipeng Su: Wonderful tool, make it more beautiful and functional when using BigQuery. Love it!!!!!!
- (2016-07-21) Connie Chung: Absolutely amazing! Definitely a great extension with useful functionalities within GBQ.
- (2016-05-18) Steven Ohrnstein: A Great Tool! Makes GBQ much easier to navigate. A+
- (2015-12-02) tuan vu: Amazing! This makes my work a lot easier.
- (2015-12-02) Ahmed Dada: GBQ just got a lot better! This extension adds hidden functionality in the interface that allow you to manipulate the UI and make querying datasets more fluid
- (2015-12-02) Chris Doe: I spend a lot of time in GBQ native UI and this extension has made my life a lot easier. My favorite is the dataset filtering options and the expanded query builder textbox.
- (2015-12-02) Bruno Wu: Fantastic! Love the estimated cost feature. The non-editing features solve many of the little annoyances I find from using the BQ GUI.
- (2015-11-28) s minafar: If you work extensively with Google BigQuery Console you'll start to wish for things the console would do better. Such as being able to adjust the panel, search filter on data sets, and an easy way to modify templated queries. BigQuery Mate does all of these and more. It enriches the BigQuery Console with additional functionality that helps improve a user’s productivity.
- (2015-11-25) cynthia lok: I love it. The parameter substitution is especially useful for me since I work with a lot of template queries.
- (2019-11-09, v:3.0.07) Le Nhu Thi: Update extension to work with console.cloud.google.com
Bigquery will move to console.cloud.google.com soon. Can you please update this to work with console.cloud.google.com. Currently I cant use this extension with Google Consolve. Many thanks
- (2019-11-05, v:3.0.06) Nella Meyohas: Can't see price anymore (new UI)
Can't see price anymore (new UI)
- (2019-10-23, v:3.0.06) Frank Tiex: User/Data Profiling
Again: Does Big Query Mate log any (meta) data about the user or processed content? If so, which information and for what purpose? Without a response I cannot approve the usage.
- (2019-10-10, v:3.0.06) Frank Tiex: User/Data Profiling
Does Big Query Mate log any (meta) data about the user or processed content? If so, which information and for what purpose?
- (2018-08-17, v:3.0.04) Andrea Zonzin: BigQuery Mate does not support new BigQuery UI
I use BigQuery Mate to quickly check query cost ($). In the new BigQuery UI this is impossible so I have to use the old version of the UI :(
- (2018-07-24, v:3.0.04) New BigQuery UI
Do you plan to support the new BigQuery UI (currently in beta)? Would be very helpful to know if you have a timeline. Thanks a lot!
- (2018-04-24, v:2.0.28) Schemas
Would it be possible to add an option to copy the schema from a table in the text/JSON format?
- (2018-03-29, v:2.0.28) Default SQL dialect not remembered
Hi, love your plugin! I find the default SQL dialect, normally saved by double-clicking the SQL button, doesn't get saved between visits to the BigQuery console as intended. If you know a good workaround, please let me know. Thanks!
- (2018-01-19, v:2.0.18) Formatting Copy/Paste
Hi, Love your plugin. Makes my work so much easier and fun! One of the things I'm running into right now is that when I write a query in the query window, you make it all pretty and nice. But when I copy/paste the code into my pipeline I get the formatting as I wrote it. And because I'm lazy, and used to the nice auto formatting, the SQL keywords become lowercase when I paste. If you could fix this problem somehow, I can continue being lazy :) Best regards, Jökull
- (2017-12-22, v:2.0.18) Krutika Parab: Pivot
Can I download the results generated from pivot??