DEV Community

loading...
Cover image for Using IntelliJ/DataGrip with  Presto JDBC

Using IntelliJ/DataGrip with Presto JDBC

Phillip Fleischer
I'm a developer who stumbled into the role of software architect for VirginPulse, wellness apps with lots o' users.
Updated on ・3 min read

Presto is a cool technology that works with everything (within reason). So it should be totally well documented how to set it up with IntelliJ, right?? Not so fast... #googlefail

While it's not totally un-intuitive, many people trying to set it up might not see the options and give up. Don't give up, here's the coolness!

Step 1 - Download the JDBC Driver

If you don't know by now, there's two forks of presto in the universe. Not surprisingly there is a JDBC driver for each and mostly they seem to be nearly identical, however at the time of writing, PrestoSQL's driver performed a bit faster for me. I encourage folks to follow their releases for more critical information.

PrestoDB, the original project by Facebook.

Trino, the rename of the fork PrestoSQL the fork of the project in the community

So just click the link under "JDBC Driver" and download it..

To a personal location:
~/Documents/jdbc-drivers

Or copy it to intellij's location:
~/Library/Application\ Support/JetBrains/IntelliJIdea2020.2/jdbc-drivers/

Step 2 - Add IntelliJ Driver

  1. In "Database" panel click + or right click and select New -> "Driver"
  2. Enter "PrestoSQL" or "PrestoDB" in the name (whichever driver you chose.
  3. Under the "Driver Files" box, click the + and browse to the downloaded jdbc jar.
  4. Pick the driver class (facebook or prestosql)
  5. Add the Datasource and use the Driver (and click "Test connection".) jdbc:presto://<host>:<port>
  6. Right click on new Datasource->Database Tools->Manage Shown Schemas... select "All Databases" and also select "All Schemas".

The last step is particularly helpful if you presto is hosting multiple catalogs across database instances. If you're dealing with particularly large amount of tables, this step could take a while.

Driver File

Add IntelliJ Driver

Driver Class

Driver Class

Datasource

NOTE : make sure to use full url with jdbc protocol and port

jdbc:presto://<host>:<port>
Enter fullscreen mode Exit fullscreen mode

IntelliJ Presto Datasource

Shown Schemas

IntelliJ Shown Schemas

Step 3 - Profit!

Hopefully this will unlock all the presto possibilities for you!

  1. Queries across database instances.
  2. Single SQL syntax.
  3. Schema explorer and keyboard completion.

Demonstration

On the way, there could be a few "gotchas" so here's a quick list of things that could trip you up.

1. Connection URL

Make sure the protocols, address, and ports are correct. By default, presto runs on 8080 but if your team set it up with ssl 443 might be the right setup, or it could even be a custom port. You may also have basic networking issues, so test if you can even connect to the host/port (maybe through the ui if permitted by your systems team).

https://prestodb.io/docs/current/installation/jdbc.html

2. Credentials

By default presto doesn't require credentials, so even though it shows username and password, you might be able to just put in "presto" and either blank or some garbage into password. On the other hand if presto is secured with file based store or LDAP you'll need to be granted credentials.

3. Catalogs

If presto is missing or has catalog access restrictions, you may not be able to see or query all of the datasources and schemas that presto is attached to.

4. Driver Updates

Since IntelliJ isn't managing the driver, you'll be responsible for your own updates... Small price to pay, or we can all keep voting for this feature and maybe someday IntelliJ will add it.

5. DBeaver

If you don't like this, then have a look at DBeaver which supports PrestoDB and PrestoSQL without downloading the driver. Same caveats apply

Summary

IntelliJ's database explorer is slick and integrated experience and you can easily use any JDBC Driver. Presto in particular is one that is especially powerful.

Let me know if you have any comments or feedback. Enjoy!

Discussion (2)

Collapse
bitsondatadev profile image
Brian "bits" Olsen

Great article Phillip!

I’d like to make a few additional comments regarding the setup for Trino. As you pointed out, we rebranded so if you are connecting to a Trino server version >= 351, you need to make sure you’re using a Trino jdbc client >= 351 as the namespace has changed.

You will also need to change the connection scheme from “jdbc:presto” to “jdbc:trino”. We realize this adds a bit more confusion to the article but as you read in the Trino rebrand blog, our hands were forced as Facebook and the Linux Foundation enforced the Presto trademark.

Last thing is that if you have enabled SSL/TLS on your Trino (or Presto) cluster, you will need to add SSL=true to the parameter list of your jdbc connection string. For IDE and other tools like DBeaver, I typically add these params in the box where you specify which database/schema you’re connection to that shows up at the end of the string.

Finally, we like to think of Trino as the upgrade to Presto. We have a plethora of new features that don’t exist on Presto, an very active community that is quick to respond, the fix for a critical security vulnerability that affects Trino versions <= 337 and still affects PrestoDB and we wouldn’t recommend putting into production, and the original creators of Presto and other contributors that have collectively contributed over 95% of the code for PrestoDB project.

Come find us on slack to find out more!

trino.io/slack.html

Collapse
pcfleischer profile image
Phillip Fleischer Author

good points. we use ssl and I haven't had to add the SSL=true to a connection string but if anyone has issues, can't hurt.