FR: Add Automatic Big Query Driver Installation and Authentication

[ZeroCool2u]

This one might be a bit more of an ask than my last FR (#2679 ) but I'm hoping I can help get the right folks talking to each other.

Over in the JetBrains public YouTrack instance there is a 4 year old ticket requesting BigQuery dialect support. This has just been made available in the latest JetBrains EAP! 🎉🥳 However, there are a couple outstanding issues, which the cloud-code-intellij plugin may be in a good position to solve. First, users must manually download and install the JDBC driver and then second track down a specific comment in the YouTrack ticket that explains how to automate authentication with GCP and BigQuery as well as the correct URL template pattern.

Since the cloud-code-plugin already handles authentication and dependency installation in general, maybe it could handle the last couple pieces here? Especially if the Simba developed BigQuery JDBC driver licensing makes it difficult for JetBrains to package up.

Hope this info is helpful at least!

[ivanporty]

Hi @ZeroCool2u, thank you for this feature request! We are indeed thinking about various levels of support for databases and their query language dialects, including BigQuery and Spanner, for Cloud Code. Your request definitely helps us understand demand for this.

We do have the authentication pieces in place, what is exactly your idea on what bits are most critical? Is that DataGrip database driver/selection support, where we could enhance driver string parsing and auto-populate auth information, or something separate would fit your needs better, like an independent tool window with Google Databases selection, authentication and connection tools (i.e. right click and copy driver string, or connect Cloud SQL proxy)?

What describes your development process best - is it an IDE like IntelliJ for coding, and DataGrip for data, running separately, or mostly using IDE with plugins for databases?

[ivanporty]

@jmah-cc interesting FR to discuss with the wider team.

[ZeroCool2u]

Hi @ivanporty! Absolutely. There was a bit more information from JetBrains in that post that I linked. It looks like they're planning on integrating BQ support into DataGrip the same way they do for other databases, including packaging the driver now, which is great news, but you bring up a good point about the broader support for other GCP DB products.

Personally, I use PyCharm/IntelliJ/CLion (for Rust)/Android Studio (for Flutter) depending on the project. Since DataGrip is directly integrated with most of those when using the paid version, I typically like to stick with that, so I don't have to leave the IDE. Though, I totally get that there's a tradeoff there with broader exposure with paid vs free users of the JetBrains IDE's.

I think the most critical stuff for me, and probably most users, is getting parsing support for the SQL dialects used by the popular GCP DB's like BQ, Cloud SQL, Firebase, and though maybe not as popular(?) Spanner. After that, the integrated connection support would be ideal, so the queries can be written and run within the IDE. One thing I'd really like to see is the estimated query cost populated somewhere on screen before running my query like in the web interface. This is one of my favorite features of the online editor and it's one of the API's that I take advantage of in my analysis applications that let you query a public dataset hosted in BQ.

Though I personally don't have a use case for it, I think the Cloud SQL Proxy support is an especially great idea! If you can use the proxy info that the IDE uses, that could be a real QoL improvement for devs dealing with corporate network proxies.

These are just some of my preferences and I know there could be some redundancy with the built-in BQ support now, but maybe this work could be used by the VS Code plugin as well where there may be less support overall for these SQL dialects etc.

[ivanporty]

Thank you @ZeroCool2u for your detailed answers, this is very helpful, especially your description of your development process! You mentioned web interface, and I think a few people in Youtrack discussions above said the BQ web interface is not ideal. Do you consider DataGrip/IDE paid versions query support to be more convenient and productive, or the main reason for using IDE is leaving it to go to web interface to run queries is just more distracting during development? What are the main benefits of JetBrains SQL support for you in general?

[ivanporty]

@russwolf FYI

[ZeroCool2u]

Hey @ivanporty happy to help! In answer to your question, I think it's a couple things. With a regular DB, say a PostgresSQL instance that I'm working with via the IDE I get a lot of the convenience in terms of setup and connection. Connections can be shared between projects, autocomplete is fast and sensible, and I can view all the tables/views available directly in the IDE. I can also compose parameterized queries for applications there and just treat them as another source code file. In the web interface there is the convenience of viewing tables and other datasets as well as saved queries, but code completion and the entire GCP console interface feels somewhat slow. When it's the only thing I have to switch over to the browser for, it is a distraction. If I want to test a query I have to run it in the BQ web interface and then edit it there, but I almost certainly have a file with this query in the IDE already, because I need it to be in VCS, so then I need to copy and paste back and forth, which can be bad too if I end up forgetting to replace the stale version in the IDE. Another huge benefit of being in the IDE is the local history function that has saved myself and some colleagues a couple times. If I'm editing in the web interface I don't have this exactly. I have the query history, but it doesn't capture exactly the same information.

[jmah-cc]

@ZeroCool2u We're still discussing database integration / support. We'll keep this feature in mind as we form a plan. Keeping it in our backlog for now and leaving it open so other users can upvote this as well.

[ZeroCool2u]

@jmah-cc Thanks so much! Really appreciate the teams great communication too. 😁

[ivanporty]

BigQuery has been integrared with databases window.