I tried accessing my sample database on CrateCloud from JuypterLab.
%sql SELECT * FROM doc.sample_event LIMIT 10
But i get the error message:
(crate.client.exceptions.ConnectionError) No more Servers available, exception from last server: ('Connection aborted.', RemoteDisconnected('Remote end closed connection without response'))
I receive the same error message using SQL alchemy and reading in pandas.
I have no problems accessing the database from DBeaver or Metabase.
I did some more tests accessing Crate from JupyterLab. Using Crate Python driver (= same approach as with my working Python scripts):
from crate import client as crate
conn = crate.connect("HOST:4200", username="USERNAME", password="PASSWORD", verify_ssl_cert=True)
cursor = conn.cursor()
cursor.execute("SELECT * FROM sample_event LIMIT 10")
It seems that there is some issue running it from within JupyterLab.
Still get the same error:
ConnectionError: No more Servers available, exception from last server: ('Connection aborted.', RemoteDisconnected('Remote end closed connection without response'))
Are you using
https in front of the hostname? (CrateDB Cloud does not allow connections without TLS).
In the cloud console, there should be code snippets in “Learn how to connect” that should work.
Yes - that worked for the crate-driver!
conn = crate.connect(“
https://HOST:4200”, username=“USERNAME”, password=“PASSWORD”, verify_ssl_cert=True)
Do you have any idea where to put in Jupyter SQL? As far I understand the connection string should look like this:
But I have no idea how to enforce https here?!?
It would be much more convenient for the data analyst to work directly with the SQL-magic.
I need to check that, but I think
Table of contents Introduction, Connecting- Database URLs, Getting a connection., Tables- Table definition, Extension types., Querying- Aggregate f...
Will take this as input to improve documentation.
Awesome - yes, works perfectly! Thank you!
I think one challenge for me was that most examples in the documentation assume a deployment on localhost and the sample project for Jupyter did not include the SQL-magic.
For you reference this is my test code (perhaps useful for others):
from sqlalchemy import create_engine
import pandas as pd
%%sql rs <<
SELECT * FROM sample_event LIMIT 10
df = rs.DataFrame()