Introspecting catalogs and schemas JDBC in Pycharm

[primaj]

Hey,

I've managed to add my SQL Warehouse as a data source in Pycharm using the JDBC driver and can query the warehouse from an SQL console within Pycharm. This is great, however, what I'm struggling with is getting the catalogs and schemas to show in the "Database" pane, I can see the data source, but no catalogs or schemas are being retrieved. Any idea how I might correct this?

Here's my jdbc URL:
jdbc:<host>:443/default;transportMode=http;CatalogSchemaSwitch=1;ssl=1;AuthMech=3;httpPath=<http-path>;UID=token;PWD=<db-pat>

Thanks!

[JunYang]

hi ,Have you tried the JDBC ULR without the catalog schema switch?

This option decides whether the driver treats Spark catalogs as schemas or as catalogs. l 1: The driver treats Spark catalogs as schemas for filtering.

jdbc:<host>:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=<http-path>;UID=token;PWD=<db-pat> 

@primaj

[primaj]

Hey @JunYang, thanks for your response! I tried removing the `CatalogSchemaSwitch` parameter but got the same result.

[hombre-invisibl]

For what it's worth, I'm experiencing the same behavior as you.
SQL Tools in VS Code does display tables etc for me.

[hombre-invisibl]

Oh, I thought it might have been a Unity Catalog vs Hive thing but I created a table in Hive as well and that doesn't show up for me either.

[thedatacrew]

I had the same problem in Datagrip, is there a startup script option in PyCharm database connection properties, if so try putting in use catalog your_unity_catalog_name;

Regards
Toby
https://thedatacrew.com

[JunYang]

Are there any errors in the event log in PyCharm? Did the steps 3 and 4 help from this document? 
https://www.jetbrains.com/help/pycharm/cannot-find-a-database-object-in-the-database-tree-view.html#...

[hombre-invisibl]

Thank you, Jun! I hadn't seen that document.

It turns out the last item it recommended (a last resort I think) worked for me. The option of turning on introspection via the Advanced>Expert options.

The weird thing is it works fine in Datagrip but the Database Tool within PyCharm requires that extra setting (for me at least).

[thedatacrew]

You will also need to download and use the Databricks JDBC Driver https://www.databricks.com/spark/jdbc-drivers-download and install it in the User Drivers area of the connection properties.

Your connection string 

jdbc:databricks://{SERVER_HOSTNAME}:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath={HTTP_PATH};uid=token;pwd={PAT_TOKEN}

In Startup Script

use catalog your_unity_catalog_name;

Regards
Toby
https://thedatacrew.com

[thedatacrew]

Here is how my PyCharm/DataGrip is set up. You'll need a separate connection for each catalog.

Regards
Toby
https://thedatacrew.com

[primaj]

Hey Toby, thanks for your response, it's very helpful. I tried doing this in Pycharm to no avail, I then tried the same in Datagrip and it worked, very strange! Were you able to get it working in Pycharm?

[primaj]

Hey Toby, thanks for your response, it's very helpful! I tried doing this is Pycharm to no avail, I then tried the same in Datagrip and it worked, very strange!

[hombre-invisibl]

Check out the link Jun dropped above. What worked for me was turning on introspection via the advanced>expert options. I was seeing the same thing as you - datagrip showed database structure, pycharm database tool did not. With that option it's now (oddly) working in both.

[primaj]

I also found that and got it working, was just about to post the same thing, cheers!

[gem7318]

You need to explicitly tell your JetBrains tool to introspect the database using JDBC metadata.

I think the reason it (sometimes) works in Datagrip but not PyCharm, IntelliJ, etc is because the default settings can be different across tools and even versions of those tools.

For PyCharm, this is how you enable introspection via JDBC metadata.

I'm happy to post my exact configs and connection string if it's helpful, but I think this is what's missing from the answers above.