[New Hub] University of Texas

[colliand]

Hub Description

This is a first pilot hub for the University of Texas. The hub will be administered by James Howison ( @jameshowison ) and used by students in his course. The course is focused on databases so is deployed with a "database sidecar" strategy as suggested by @yuvipanda.

Community Representative(s)

@jameshowison

Important dates

• 2021-01-29 Hub was made available
• 2021-02-09 Class starts (correct @jameshowison?(

Target start date

2021-02-01

Preferred Cloud Provider

No preference (default)

Preferred Location of the Cloud Resources

No response

Do you have your own billing account?

• [ ] Yes, I have my own billing account.

Hub Authentication Type

GitHub Authentication (e.g., @mygithubhandle)

Hub logo

No response

Hub logo URL

No response

Hub image service

No response

Hub image

No response

Extra features you'd like to enable

• [ ] Dedicated Scalable Dask Cluster with Dask Gateway

Other relevant information

James Howison has some questions about the database sidecars.

Hub ID

utexas-demo

Hub Cluster

pilot

Hub URL

https://utexas-demo.pilot.2i2c.cloud/

Hub Type

basehub (with database sidecar)

Tasks to deploy the hub

• [x] Engineer who will deploy the hub is assigned
• [x] Deploy information filled in above
• [x] Initial Hub deployment PR:
• [x] Administrators able to log on
• [x] Community Representative satisfied with hub environment
• [ ] Hub now in steady-state

[jameshowison]

I'm checking it all out and getting acquainted. Thanks!

This is an introductory class, so in the past I've used phpmyadmin for students to browse tables and to create their first tables. That avoids having to teach the CREATE syntax and browsing the tables (rather than using a SELECT * query) helps build analogies to Excel, helping to scaffold students.

But phpmyadmin had its own issues, exposing lots of complexity. Looking at pgadmin as the equivalent in the postgres world seems even more complexity (or maybe that's just me adjusting to postgres rather than MySQL/MariaDB (which is fine, I'd been meaning to do that for years anyway).

For browsing, any thoughts on this: https://github.com/pbugnion/jupyterlab-sql

[colliand]

I am cutting a pasting comments from written by @yuvipanda on this hub from another channel:

@colliand I've now set up https://utexas-demo.pilot.2i2c.cloud/ with the postgres setup!

1. Login with GitHub. Your ID should get you in, and that should let you add other users as needed. We can also switch out the auth provider for something else.
2. Each user gets their own personal postgresql database server, so they can't affect anyone else's. They have full admin rights over their own database server. For this demo, there is no persistence for the database - when your server stops (from inactivity), the contents of your database will be lost. We'll provide persistence when we roll this out for real users - I can also do that earlier if necessary.
3. They can access the postgresql server from a terminal with psql -h localhost

4. They can also access it from inside python with:

   import psycopg2
   conn = psycopg2.connect(host='localhost')

5. We can install any other arbitrary postgresql extension if needed.
6. ipython-sql is also installed. They can access it in a notebook with:

   %load_ext sql
   %%sql postgresql://localhost
   <sql-query>

7. https://www.pgadmin.org/ is not currently set up, but I'll work on that if they need it.

They can try it out now! Let me know what else I can do, @colliand.

[colliand]

Next cut/paste from Yuvi:

As an alternative to the ipython-sql magic, I've installed an SQL kernel itself (https://github.com/jupyter-xeus/xeus-sql). You can see this in JupyterLab as an xsql kernel:

In notebooks using that, you can connect using:

%LOAD postgresql host=localhost

And then write SQL (with syntax highlighting!) directly in the notebook, without having to use magics. Maybe this is an option.

My suggestion is to avoid jupyterlab-sql for now. I can talk to Pascal (the maintainer) to see how current it is, but I'd rather avoid the setup - lab moves quite quickly, and we don't have any expertise to fix issues there.

As an alternative to pgadmin4 / phpmyadmin, we can also consider https://www.adminer.org/. It seems simpler and maybe less confusing.

We can also get them MySQL instead of postgres if that makes teaching easier! It's not much more difficult.

Finally, when you respond to the UTexas people can you include me too?

Thanks

[jameshowison]

Ah, thanks @colliand and @yuvipanda very cool stuff. I had played with the xsql bit, but now understand it a bit more. Might indeed be useful.

I'm happy to switch teaching to postgres, it's not a lot of work (thanks DBI-API in python!) and it's useful for me to know what is specific to MySQL and postgres in my materials. Plus postgres is a useful keyword for student resumes.

Adminer (hah, ok, it's admin-er rather than ad-miner) looks useful for both setting up tables and basic browsing, not much different to phpmyadmin. Ah, and it even allows you to sort the columns by clicking on them (and shows the SQL used to execute the query it is showing, which is fantastic).

So, I think adding adminer to the image would be great! Not sure if there is a way to get it to show up in the Launcher?

[yuvipanda]

@jameshowison great to hear adminer looks good to you. I'll try and get that running. What is your timeline for this class?

[jameshowison]

Yes, that would be great. Very tight, I'm afraid. Ideally this would be working next week.

On Wed, Feb 2, 2022 at 10:25 AM Yuvi Panda @.***> wrote:

@jameshowison https://github.com/jameshowison great to hear adminer looks good to you. I'll try and get that running. What is your timeline for this class?

— Reply to this email directly, view it on GitHub https://github.com/2i2c-org/infrastructure/issues/968#issuecomment-1028116489, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAWOUVYXLF7TJZ3F2E3PPTUZFLF7ANCNFSM5NKXIFUQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

You are receiving this because you were mentioned.Message ID: @.***>

[jameshowison]

I'm running into some issues with ipython-sql and CREATE DATABASE. Not sure of the right place to ask about this (or the level of engineer support) but I asked about this at the ipython-sql github. I'm also not sure how active that project has been, recently.

https://github.com/catherinedevlin/ipython-sql/issues/207

Any advice on location, fixes, support approach welcome!

[yuvipanda]

@jameshowison I'd say the xeus-sql project (with the xsql kernel) is more active, and might provide more help. So perhaps that's the avenue to explore?

[yuvipanda]

@jameshowison I spent a bunch of time trying to get adminer to work but failed, unfortunately. Instead, I got pgadmin4 working. You can go to https://utexas-demo.pilot.2i2c.cloud/hub/user-redirect/proxy/absolute/5050/ and after starting your jupyterhub server, it will ask you for credentials. use jovyan@jovyan.org, password jovyan. It'll still talk to each user's individual database server, although that will need to be setup in the GUI by the users too. Unideal for sure, but the field doesn't seem broad. If you have suggestions for other software we can try, I'm happy to give it a look.

Currently, users don't have any database persistence. We'll have to fix that before getting actual users on it. Disk space gets expensive fast. I'm thinking we can just give users a couple hundred MB of non-ssd storage. How does that sound to you?

[jameshowison]

Thanks. Yes, that sort of small amount of storage is fine. I will monitor if any student appears to be taking on a project with large datasources.

I'm having a little trouble, perhaps related to the persistence of the database? e.g.: in a xsql kernal notebook I run:

%LOAD postgresql host=localhost

CREATE DATABASE class_music_festival

CREATE TABLE venues (
    id SERIAL PRIMARY KEY,
    name TEXT,
    capacity INTEGER
);

INSERT INTO venues(name,capacity) VALUES ('HEB',3000)

SELECT * FROM venues

And that works fine. My expectation is that database (and table) would be available in both pgadmin and in other (python kernel) notebooks. But it doesn't seem to be?

In pgadmin4 I create a server using localhost as the host; it appears to connect and shows the database (but maybe not the table?), but then seems to disconnect and I get "internal server error" or errors from the dashboard.

In another python notebook I run:

import psycopg2 as pg

with pg.connect(host='localhost',database='class_music_festival') as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM venues")

and get a UndefinedTable: relation "venues" does not exist error. Same result if I go via sqlMagic connection.

I'm also having trouble via psql on the commandline (a Terminal via the Launcher):

psql -h localhost

\list

                                  List of databases
         Name         | Owner  | Encoding |  Collate   |   Ctype    | Access privileges 
----------------------+--------+----------+------------+------------+-------------------
 class_music_festival | jovyan | UTF8     | en_US.utf8 | en_US.utf8 | 
 jovyan               | jovyan | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres             | jovyan | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0            | jovyan | UTF8     | en_US.utf8 | en_US.utf8 | =c/jovyan        +
                      |        |          |            |            | jovyan=CTc/jovyan
 template1            | jovyan | UTF8     | en_US.utf8 | en_US.utf8 | =c/jovyan        +
                      |        |          |            |            | jovyan=CTc/jovyan
(5 rows)

DROP DATABASE class_music_festival 

But then class_music_festival is still there. Similarly a CREATE DATABASE test_db runs without error but doesn't seem to create the database ... at least it isn't showing up with a \list.

jovyan-# CREATE DATABASE test_db
jovyan-# \c test_db
FATAL:  database "test_db" does not exist
Previous connection kept

Any ideas?

With the sidecar approach the postgres server is running in a different container, right? But we are connecting to localhost, so are the ports on localhost forwarded to the other container? Using docker-compose I used the Docker hostnames and thus the internal networking. See the approach here: https://github.com/howisonlab/wrangling-docker

[jameshowison]

Apologies, I think the psql issues are just my unfamiliarity with postgres, got to remember to end all commands/queries with a semicolon!

jovyan=# CREATE DATABASE test_db;
CREATE DATABASE
jovyan=# \list
                                   List of databases
         Name         | Owner  | Encoding |  Collate   |   Ctype    | Access privileges 
----------------------+--------+----------+------------+------------+-------------------
 class_music_festival | jovyan | UTF8     | en_US.utf8 | en_US.utf8 | 
 jovyan               | jovyan | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres             | jovyan | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0            | jovyan | UTF8     | en_US.utf8 | en_US.utf8 | =c/jovyan        +
                      |        |          |            |            | jovyan=CTc/jovyan
 template1            | jovyan | UTF8     | en_US.utf8 | en_US.utf8 | =c/jovyan        +
                      |        |          |            |            | jovyan=CTc/jovyan
 test_db              | jovyan | UTF8     | en_US.utf8 | en_US.utf8 | 
(6 rows)

Let me try the other routes now (xeus-sql and psycopg).

[yuvipanda]

@jameshowison cool, let me know if the problems persist. Does pgadmin4 still have issues?

[jameshowison]

@yuvipanda pgadmin4 seems to connect to the server (user:jovyan pass:) and I can see objects and use the browse/query interface but then it seems to disconnect or lockup. Even "Disconnect Server" from the right-click menu doesn't work. Logging out and logging back in does seem to work.

[yuvipanda]

@jameshowison if you restart your server, you'll get persistent database storage now! Each user will get 1GB - which is the smallest possible individual disk on our cloud provider it turns out. I think I know what's up with pgadmin4, I'll try tweak that too

[yuvipanda]

Well, I'm not sure what is up with pgadmin4 :( The fundamental error is this:

::ffff:10.0.1.23 - - [04/Feb/2022:19:49:35 +0000] "GET /user/yuvipanda/proxy/absolute/5050/dashboard/dashboard_stats/2/16384?chart_names=session_stats,tps_stats,ti_stats,to_stats,bio_stats HTTP/1.1" 400 145 "https://utexas-demo.pilot.2i2c.cloud/user/yuvipanda/proxy/absolute/5050/browser/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:96.0) Gecko/20100101 Firefox/96.0"
2022-02-04 19:49:37,020: ERROR  pgadmin:        400 Bad Request: The CSRF tokens do not match.
Traceback (most recent call last):
  File "/venv/lib/python3.9/site-packages/flask_wtf/csrf.py", line 261, in protect
    validate_csrf(self._get_csrf_token())
  File "/venv/lib/python3.9/site-packages/flask_wtf/csrf.py", line 115, in validate_csrf
    raise ValidationError("The CSRF tokens do not match.")
wtforms.validators.ValidationError: The CSRF tokens do not match.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/venv/lib/python3.9/site-packages/flask/app.py", line 1514, in full_dispatch_request
    rv = self.preprocess_request()
  File "/venv/lib/python3.9/site-packages/flask/app.py", line 1857, in preprocess_request
    rv = self.ensure_sync(before_func)()
  File "/venv/lib/python3.9/site-packages/flask_wtf/csrf.py", line 229, in csrf_protect
    self.protect()
  File "/venv/lib/python3.9/site-packages/flask_wtf/csrf.py", line 264, in protect
    self._error_response(e.args[0])
  File "/venv/lib/python3.9/site-packages/flask_wtf/csrf.py", line 307, in _error_response
    raise CSRFError(reason)
flask_wtf.csrf.CSRFError: 400 Bad Request: The CSRF tokens do not match.

The general consensus is that this might be caused by multiple pgadmin4 processes running, but that's clearly not the case here. I'll try debug again on Monday.

[jameshowison]

I'm seeing some version mismatches between the pg client tools on the commandline and the server. Perhaps this is because different versions are used on the main image and the database 'sidecar' image?

(notebook) jovyan@jupyter-jameshowison:~$ pg_dump -sh localhost class_music_festival
pg_dump: server version: 14.1 (Debian 14.1-1.pgdg110+1); pg_dump version: 10.19 (Ubuntu 10.19-0ubuntu0.18.04.1)
pg_dump: aborting because of server version mismatch

psql shows similar messages (but it does run):

(notebook) jovyan@jupyter-jameshowison:~$ psql -h localhost
psql (10.19 (Ubuntu 10.19-0ubuntu0.18.04.1), server 14.1 (Debian 14.1-1.pgdg110+1))
WARNING: psql major version 10, server major version 14.
         Some psql features might not work.
Type "help" for help.

jovyan=# 

[yuvipanda]

@jameshowison https://github.com/2i2c-org/infrastructure/pull/980 I am just downgrading to postgres 10 on the server so it matches. Hope that works ok? Do you need any new features in the latest version?

[jameshowison]

That's fine, certainly not in this initial course.

[jameshowison]

Given the difficulties with pgadmin (and it's overall complexity), is there any chance that https://github.com/pbugnion/jupyterlab-sql can work? I think it's a useful addition to things (particularly ability to browse tables, which I find really helps orient students).

[yuvipanda]

@jameshowison I just tried it, unfortunately as suspected it doesn't actually work with a new enough version of JupyterLab:

Is GUI access a critical part of the course?

[yuvipanda]

@jameshowison separate from that, is GitHub authentication where you explicitly add student names good enough for authentication? https://docs.2i2c.org/en/latest/admin/howto/manage-users.html has info on how to add them.

[yuvipanda]

And finally, if this all works at some point I'll make utexas.pilot.2i2c.cloud (vs current utexas-demo.pilot.2i2c.cloud).

[jameshowison]

Yes to both. github is fine for now (we'll explore Single Sign On) down the track. And yes, that URL change would be great.

[yuvipanda]

@jameshowison alright, new hub up at utexas.pilot.2i2c.cloud! I'll decom the demo one soon.

[yuvipanda]

@jameshowison I don't think we can get pgadmin4 working properly within the next couple weeks :( Think you can get by without that to start with?

[jameshowison]

Understood. Yes, I can get by :) Wish there was something that could let the student browse tables, though.

On Mon, Feb 7, 2022 at 1:24 PM Yuvi Panda @.***> wrote:

@jameshowison https://github.com/jameshowison I don't think we can get pgadmin4 working properly within the next couple weeks :( Think you can get by without that to start with?

— Reply to this email directly, view it on GitHub https://github.com/2i2c-org/infrastructure/issues/968#issuecomment-1031833107, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAWOUQSELAP5R2PQND2LDTU2AMATANCNFSM5NKXIFUQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

You are receiving this because you were mentioned.Message ID: @.***>

[choldgraf]

Maybe we can ask around on Twitter or the jupyter forum - I'm sure there are others out there who are also trying to teach SQL etc with Jupyter 🤔

[jameshowison]

I'll give it a shot: https://discourse.jupyter.org/t/sql-browsing-and-or-db-table-creation/12944

Was adminer a definite no-go, not even installed on the same sidecar server as postgres?

[jameshowison]

I'm trying nbgitpuller but running into some issues where the hub returns a 404 error. I'm expecting these to pull a folder into a user's workspace (and then update that folder if the link is clicked again). But currently both produce a 404 at the hub.

Here is the folder in the repo I'm testing with: https://github.com/howisonlab/datawrangling-exercises/tree/main/objects_database

Here is the link I generated via the webform: https://utexas.pilot.2i2c.cloud/hub/user-redirect/git-pull?repo=https%3A%2F%2Fgithub.com%2Fhowisonlab%2Fdatawrangling-exercises%2F&urlpath=lab%2Ftree%2F%2Fobjects_database&branch=main

And the one from the extension: http://utexas.pilot.2i2c.cloud/hub/user-redirect/git-pull?repo=https%3A%2F%2Fgithub.com%2Fhowisonlab%2Fdatawrangling-exercises&branch=main&urlpath=lab%2Ftree%2Fdatawrangling-exercises%2Fobjects_database

[choldgraf]

@jameshowison can you confirm that nbgitpuller is installed in the user image for the hub? I just tried importing it in a session on your hub and got:

[jameshowison]

Running to class but that is probably the issue. reading the docs I just assumed that we were using the default image and it would be installed: https://docs.2i2c.org/en/latest/admin/howto/content.html#content-nbgitpuller

Perhaps the 404 could discuss that? But definitely I'd like to use nbgitpuller, so if we can get that installed that would be great.

[choldgraf]

It is in the default image but I bet that @yuvipanda created a custom one when he added the database stuff

[choldgraf]

Yep the environment repo is here: https://github.com/2i2c-org/utexas-image/

I added nbgitpuller here: https://github.com/2i2c-org/utexas-image/pull/1

That auto-pushed the updated image here: https://quay.io/repository/2i2c/utexas-image?tag=latest&tab=tags

I went into the hub's configurator and updated the image to the new tag that was uploaded to the link above: https://docs.2i2c.org/en/latest/admin/howto/configurator.html

Hit "submit".

Then closed my Jupyter server. Started a new one.

And when I click this link, it worked! http://utexas.pilot.2i2c.cloud/hub/user-redirect/git-pull?repo=https%3A%2F%2Fgithub.com%2Fhowisonlab%2Fdatawrangling-exercises&branch=main&urlpath=lab%2Ftree%2Fdatawrangling-exercises%2Fobjects_database

[jameshowison]

Ok, we did our first class today, with students logging in and using that nbgitpuller link. All worked. One or two servers were slow to spin up (~3 minutes) but eventually came up. One or two initially had trouble with the nbgitpuller link, getting bounced in various ways, logging out and logging back in worked there (so no idea what the issue was, whether it was time passing that resolved it or the actual log out and back in).

I have decided to switch the teaching order and teach the CREATE syntax first, and forgo the browsable tables. This is something I had considered anyway. One difficulty that persists, though, is that postgres uses \dt as a command to show tables within a database, something that users use pretty often. xues-sql doesn't offer that. I filed an issue and there is some great guidance on how that could be implemented, but I don't feel competent to pursue that.

https://github.com/jupyter-xeus/xeus-sql/issues/57#issuecomment-1033132423

[yuvipanda]

@jameshowison I'm still on vacation, but I managed to sneak some airplane time to work on this - and I think pgadmin4 properly works now. Try it out? https://utexas.pilot.2i2c.cloud/hub/user-redirect/proxy/absolute/5050/

[jameshowison]

Yes, this is great, thanks! Much easier to browse the tables (although I think for simplicity I will continue with xeus-sql). But this is crucial for debugging.

[jameshowison]

Just leaving this note here in case anyone else is using this setup in the future. I was having trouble finding tables I created after creating a database in a xues-sql notebook. In short when postgres creates a database you have to manually switch to that database (ie it's like mkdir and the needed cd into the new db). But you can't issue the \c command in xues-sql so you have to do a second connect cell.

See discussion and examples here: https://github.com/jupyter-xeus/xeus-sql/issues/57

[jameshowison]

One challenge we have is students running queries with large amounts of output. This locks up the browser and makes things unresponsive. Could adding an output limiter for jupyterhub help? Something like:

https://pypi.org/project/jupyterlab-limit-output/

[choldgraf]

@jameshowison does this seem to be the cause of the students needing to refresh the browser window? Or is this a different issue unrelated to that?

I'm surprised you didn't run into this issue when people were working on their own laptops - how did you manage queries with really large outputs then? The browser stuff is not unique to a JupyterHub, I think...

[jameshowison]

I think students are refreshing when hitting kernel issues as well.

Good point, @choldgraf. Hmmm, let me think how we did this before. Ah, we were running queries like this using phpmyadmin, which provides paging by default. So we could switch to pgadmin but then there is a lot of copy and paste between windows (and pgadmin is confusing). I'm teaching them to use (and remove) LIMIT 10 as they build up queries on tables. That's good practice anyway, but guard rails are still useful.

I also wonder if these issues are primarily when using the xsql kernel, rather than ipython, I will watch for that. Maybe just going to sql magics would help? I might give them that as an option.

[jameshowison]

I think we need to see some logs on the container when these errors are occurring. Is there anyway to do that? Can we set this up so that the logs are available outside the container, at least while we sort this out.

A friend wondered if endpoint/container networking might be causing intermittent issues?

[choldgraf]

Just to confirm - this was a major issue that everybody was experiencing at first, and when we bumped the memory guarantees for each user session, the problem went down substantially. Is that correct?

[jameshowison]

I'd say (somewhat guessing) that it is less common now, but still widespread. My TA and I encounter the kernel dying issues sporadically, as do others.

[jameshowison]

A second issue arose this morning, when I tried to offer pgadmin as an alternative for executing queries. About 30% of the class ran into "Internal Gateway" errors when trying to follow this link to their pgadmin sidecar: https://utexas.pilot.2i2c.cloud/hub/user-redirect/proxy/absolute/5050/ (pass:jovyan@jovyan.org / jovyan).

[yuvipanda]

@jameshowison you can look at logs for your running container by looking at ~/.jupyter-server-log.txt. I'm digging through to see what I can find.

Looking at the distribution of memory usage, I don't believe most of the kernel deaths are about memory exhaustion, although that is still possible that the memory spike is so quick and so high that our prometheus metrics (which are sampled every minute) don't catch it.

[jameshowison]

Great stuff, thanks @yuvipanda I can test stuff whenever you'd like! Good to know where the logs are, I'm not sure where I would have found to look for that, maybe https://infrastructure.2i2c.org/en/latest/topic/troubleshooting.html or https://infrastructure.2i2c.org/en/latest/howto/operate/index.html

But since I'm an administrator and not a 2i2c engineer probably here (where it mentions helping users debug): https://docs.2i2c.org/en/latest/admin/howto/control-user-server.html

[jameshowison]

Not related to 2i2c infrastructure setup, I'm guessing, but I ran into a confusing error using nbgitpuller to distribute files. Luckily this one only affected me, not the students.

https://github.com/jupyterhub/nbgitpuller/issues/234

[choldgraf]

I think that this hub is now roughly in a steady state, without major work to be done in setting it up, so I'll close this and we can debug other problems as they pop up via support channels. (note that we do have an incident issue about this still open: #1021 )