Using CrateDB Cloud from Jupyter Lab

CrateDB Cloud
1

I tried accessing my sample database on CrateCloud from JuypterLab.

%sql crate://[user]:[password]@[host]:4200
%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.

Any hints?

2

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'))

Any hints?

3

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.

image
image1784Ă—630 58.2 KB

image
image2364Ă—1284 234 KB

1 Like
4

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:

%sql crate://USERNAME:PASSWORD@HOST:4200

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.

5

I need to check that, but I think

%sql crate://USERNAME:PASSWORD@HOST:4200/?ssl=true

should work.

Will take this as input to improve documentation.

1 Like
6

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):

import crate
import sqlalchemy
from sqlalchemy import create_engine
import pandas as pd

%load_ext sql

%sql crate://USERNAME:PASSWORD@HOST:4200?ssl=True

%%sql rs <<
SELECT * FROM sample_event LIMIT 10

df = rs.DataFrame()
df
2 Likes