BigQuery – missing some features: friendly presentation of nested repeated rows and rows as JSONs, …

by Nov 4, 2017

Hi during evalutation period I noticed following inconveniencies when using it with BigQuery:

BLOCKING for me:

- missing friendly formatting of nested repeated rows (seems it's provided for MongoDB), currently it's simple text instead of something like in last column:

- lack of json representation of results (also seems it's provided for MongoDB)

CRITICAL for me :

- cumbersome filtering tables and datasets, it's hidden under project. Could it be extracted to the projects/data sets tree?

MINOR for me:

- lack of Query Plan Explanation:

Anyway I see a lot of advantages comparing to BigQuery UI Console:

+ default parameters per project, especially enabling STANDARD SQL as default

+ colourful syntax of query

+ opening very fast query window

+ customised colours of query windows frames

+ pinned results

+ open api

Is there chance to provide fixes/enhancements for blocking and critical issues in near future?


Sachin Prakash over 6 years ago

– “Nested repeated rows” : MongoDB offers a FLATTEN_ARRAY option. This option works slightly differently than shown in your screenshot. FLATTEN_ARRAY will create a separate record for each array value as shown here. The BigQuery APIs do not support a FLATTEN_ARRAY type option. Until they do, we will not be able to offer an equivalent. Also, the current ADS Grid Format doesn’t support displaying one record broken out into multiple lines as shown in your screenshot. We wouldn’t be able to add this in the near future.

– “Json representation”: MongoDB offers a Tree view as well as a Text tab w/ JSON values. Which one are you interested in or both? I’ve logged an issue (#15224) to investigate whether BigQuery supports this in their APIs. If yes, & depending upon how straightforward it is to integrate, we could possibly add this for v19, targeted for October.

– “cumbersome filtering tables and datasets”: I didn’t understand the request. Can you explain this further, perhaps by attaching a screenshot?

– “lack of Query Plan Explanation” : I’ve logged an issue, #15225, to investigate this further. I see that BigQuery does provide APIs for query plan explanation. However, the implementation time might be too long to fit this into our v19 schedule.

Daniel Materna over 5 years ago
Thank you for response.

Regarding “JSON representation” I would be interested in both. It would be similar to BigQuery UI Console.

Regarding “cumbersome filtering tables and datasets” I realised after asking above question that in fact there are two options of filtering, after clicking on project and starting typing appears editable search pop up which changes focus on found dataset. Anyway it seems it doesn’t work for tables which is still uncomfortable comparing to searching within BigQuery UI Console and field “Filter by ID or label” which supports filtering both.

Sachin Prakash over 5 years ago
Regarding “after clicking on project and starting typing appears editable search pop up which changes focus on found dataset”, this search feature works as follows: The search works on any expanded nodes. For instance, take a look at the attached SchemaTreeSearch.png. Because the table node is expanded, the search matches on tables as well. This type of search is client side only. It does not query the server and only works on expanded nodes in ADS Schema Browser. ADS also offers a Filter feature at the server registration level. To access this, right click on your registered server > Filter tab. See this doc link for additional information on the Filter feature.

Neither of these features match the BigQuery UI Console “Filter by ID or label” feature. ADS offers a 3rd search option accessible via Tools > Object Search. Using this feature, you can specify the search string and ADS executes server side queries to find the matching objects. However, this feature is not available for BigQuery. I’ve logged an enhancement request to investigate whether BigQuery supports the necessary APIs for us to enable Object Search: #15233

Regarding “JSON representation”, we’ll investigate both. The “Text tab with JSON values” seems to be equivalent to BigQuery Console UI’s JSON view. I didn’t see an equivalent in BigQuery Console UI for the Tree view that ADS currently supports in MongoDB.