DataGrip 2018.3 EAP

Hello! No long preambles, this is DataGrip 2018.3 EAP! Let’s see what’s inside.

Code completion

Many feature requests are implemented in this area.

When you use the GROUP BY statement, DataGrip offers you a list of non-aggregated fields.

A list of all columns is available in completion after the SELECT keyword.

We also added this list to the MERGE statement and now it works with INSERT INTO when using table variables.

Completion now works well for named parameters.

In the case of ambiguous names, DataGrip now shows you the context.

We also introduced Postfix completion which might be familiar for those who use other IntelliJ-based IDEs.

Here is a movie of how all of them work.

Code completion also works for:

— Items in brackets. DBE-4246
— Numeric fields in SUM() and AVG () functions. DBE-2285
— FILTER (WHERE …) clause of an aggregate function.DBE-5591
— Table value types.DBE-5768
— Field types in SQLite. DBE-5660, DBE-6778

Now there is an ability to choose a dialect for Live Templates. It means that this live template will only work in the specified dialect.

This ability lets you create different dialect-based implementations for the same abbreviation. Say, we want to create a template for getting the first n rows. In SQL Server and PostgreSQL, this query would have different syntax.

One can use the same abbreviations for different live templates if they are in different groups. Create two new groups: SQL Server and PostgreSQL. In each group create a corresponding live template with the appropriate syntax.

Don’t forget to choose the right dialect for templates. Thus, they will work only in the needed context.

Another small improvement: the Create table intention (did you know about that?) now respects field types and generates the correct code. Press Alt+Enter if there is a table where you are going to insert data.

The result is:

Code insight

Now DataGrip will warn you if you use the DELETE or UPDATE statement without WHERE clause.

In case you are running this, there will be a caution notice.

Other enhancements

We support extensions in PostgreSQL.

Quick doc for the data source (Ctrl+Q for Windows/Linux, F1 for OSX) now contains some statistics: the number of different objects

Also, the long-awaited auto-reconnect feature is added: no more disonnections after timeouts!

Fixes

Support for:
— Stored procedures in PostgreSQL 11. DBE-6863
— ROWS FROM in PostgreSQL. DBE-7098
— Vector valued updates in SQLite. DBE-4449
Other:
— If JOIN has no condition, this error is highlighted. DBE-6759
— ‘@’symbol doesn’t break the spell checker, thus it works in variables’ names. DBE-2250
— JSON groovy extractor works OK with big numbers. DBE-7019
— Row count works if columns are sorted. DBE-2444
— Сorrect search path is set in the reopened data editor. DBE-7044
— Query plan visualiser works in Redshift. DBE-7129

That’s all for today. Your feedback is welcome in our twitter or forum. By the way, starting from this version, we have a handy thing: click Help -> Report problem… to create a new issue in our issue tracker.

DataGrip team.

Hello! This is another minor update for 2018.2. It includes several important bug-fixes.

Some people complained that the new SQL log was not really very friendly: the output panel would become hard to read due to the number of internal queries. So, we removed the internal queries from this panel: now you will see only the queries which were run explicitly by the user, including those, which were run from the UI.

If you still need to inspect literally all the queries that DataGrip has run, check the SQL log file: Help → Show SQL log.

Other fixes:

DBE-6889: DataGrip executes procedures fine with old MySQL jdbc connectors.
DBE-6982: No more deadlock in read-only mode.
DBE-6810: We fixed the highlighting in DB2.
DBE-5459: Internal files are not shown in the Go to File list.
DBE-6939: No more lags in code formatter settings preview.
DBE-6823: Custom code style can be used as default.

Hello! This is a first small update for 2018.2 Here is the list of important enhancements:

Owners are generated in PostgreSQL and Redshift

New experimental Search Everywhere

IntelliJ Platform 2018.3 will introduce some changes that you can already start playing with, namely a new Search Everywhere dialog. It is the result of merging Search Everywhere, Go to table/procedure/class, Go to file, Go to symbol, and Find action.

To turn it on, open the Maintenance dialog (Ctrl+Alt+Shift+/ on Windows and Linux or Cmd+Alt+Shift+/ on macOS), select Experimental features, and enable the new.search.everywhere check-box. Please keep in mind that this feature is still under heavy development.

Bug fixes

DBE-6751: Default precision for common data types are now generated when executing a procedure.
DBE-6818: Working with Postgres 11 is possible now.
DBE-6753: Drag and drop works to move data source to a group.
DBE-6796: Data source group name can be used when navigation to an object.
DBE-6797: Slashes work as a path separator when renaming a data source.

Hello! We’ve prepared a new build for our early adopters. Here’s a list of interesting things you will find inside.

Running stored procedures

A long-awaited feature! Now it’s implemented for Oracle and PostgreSQL. SQL Servers procedures are to come.

Choose Execute from the context menu of the procedure.

The SQL code will be generated. Put in the values of the parameters you need and press OK.

As you see, we deal with the output of this Oracle procedure, that’s because we have SQL code which retrieves the result-set from the JDBC driver.

In the cases where you run just a simple procedure with no output, DataGrip will generate SQL code just running this procedure. Again, you’ll need to add the values to the parameters. This is a PostgreSQL example.

Note, that you can run the procedure by clicking on a button in the toolbar when opening the source code.

Code style per data source

As you perhaps already know, you can create different schemes of Code Style.

Now any of them can be used specifically for each data source. To do this, go to the Options tab of the data source properties.

That’s all for today. Take DataGrip 2018.2 EAP for a spin!

Your DataGrip Team

Continuing the series of posts on how to connect DataGrip (or any other IntelliJ-based IDE) to various data sources, in this post we’ll show you how to connect to Google’s BigQuery. BigQuery is a low-cost enterprise data warehouse designed to handle data analytics at a massive scale.

Currently, DataGrip does not come with a built-in connector for BigQuery, so let’s connect the IDE to it.

First, let’s get all the details needed to connect to BigQuery from the Cloud Console. Make sure you have the right project selected, then click on the Create service account button. Type the name of the account, for example “datagrip”, in the Role section, select the BigQuery | BigQuery Data View, BigQuery | BigQuery Job User, and BigQuery | BigQuery User roles, then check the Furnish a new private key box. Make sure that the Key Type is set to JSON, then click on the Create button. A credentials file will be downloaded and you can click on the Close button on the dialog.

Note: Make sure you place that file in a safe place as it will allow anything with rights to read it to connect to your BigQuery dataset you are allowed to access.

Now, back to the IDE, we need to add a new Data Source. Go to Database Tool Window, then select Data Source Properties, click on the “+” button and then, at the bottom of the list you’ll see an entry named Driver, click on this.

Start by changing the name of the configuration to “BigQuery”. You will notice that there is a section called Driver files. You can download the BigQuery JDBC driver from this page. Download the “JDBC 4.2-compatible” zip file, named “SimbaJDBCDriverforGoogleBigQuery42_1.1.6.1006.zip” at the time of writing, then unpack it into a new directory.

Once you downloaded the driver, go back to the IDE, and in the Additional files section, click on “+” and add all the “.jar” files from the newly created directory. Under the Class drop-down, now you can select “com.simba.googlebigquery.jdbc42.Driver” from it. In the Dialect drop-down, select Generic SQL.

Next, let’s define the connection URL template. Click on the “+” button under the URL templates section and add a new entry named “default” with the value: “jdbc:bigquery://[{host::https\://www.googleapis.com/bigquery/v2}[:{port::443}]][<;,{:identifier}={:param}>]”. Go to the Advanced tab, and in the OAuthType field, type “0”(zero).

With the driver is configured, go to the “+” sign at the top left of the Data Sources and Drivers window and select “BigQuery”. Leave the User and Password fields empty.
If you don’t want to modify the data in BigQuery, or your user can only view the data without modifying it, then mark the connection as Read-only.

Click on the Advanced tab and add your service account to OAuthServiceAcctEmail. This is the email we have from the earlier step in the Google Cloud Console. In the OAuthPvtKeyPath type the path to the file downloaded earlier from the Cloud Console. Finally, type the project id in the ProjectId field.

Go back to the General tab, click on the Test Connection button and you should see a “Successful” message. This means you configured everything correctly and you can now connect to BigQuery.

Close the window by pressing the OK button, and now you’ll be presented with a database console ready for use.

Let’s run a few sample queries to see how everything. For this, we’ll use the BigQuery public dataset available, and borrow one query from the official documentation:

Press CTRL+ENTER and the query will start running.

Note: Every time you run a query against BigQuery which requires it to process data, you will pay for it. Make sure that you understand the data size and the query you are about to run before doing so. For more details about the pricing, please see the official BigQuery documentation or contact your Google Cloud administrators.

And that’s it. Now you can use BigQuery just like any other database and have all the IDE power for completion, data export, and visualization. Please let us know in the comments below if you have any questions, or features requests, we look forward to your feedback.