08-09-2023 03:39 AM
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!
11-20-2023 11:02 AM
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>
11-22-2023 03:52 AM
Hey @SparkJun, thanks for your response! I tried removing the `CatalogSchemaSwitch` parameter but got the same result.
01-16-2024 01:25 PM
01-16-2024 01:28 PM
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.
01-16-2024 01:39 PM
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
01-16-2024 03:33 PM
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#...
01-18-2024 07:46 AM
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).
01-17-2024 01:29 AM
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
01-17-2024 02:25 AM - edited 01-17-2024 02:26 AM
Here is how my PyCharm/DataGrip is set up. You'll need a separate connection for each catalog.
Regards
Toby
https://thedatacrew.com
01-18-2024 05:18 AM
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?
01-18-2024 05:14 AM
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!
01-18-2024 07:50 AM
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.
01-18-2024 08:03 AM
I also found that and got it working, was just about to post the same thing, cheers!
03-27-2024 08:28 AM - edited 03-27-2024 08:33 AM
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.
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.
If there isn’t a group near you, start one and help create a community that brings people together.
Request a New Group