19. Querying Relational Database Systems

This notebooks shows how to query data in relational database systems.

[1]:
%load_ext raw_magic

The first step is to register credentials.

For the purposes of this notebook, we assume there is a credential registered for a PostgreSQL server under name psql01.

[6]:
%rdbms_show psql01
type: postgresql
host: test-psql.raw-labs.com
port: 5432
database: demos
username: rawtest
password: None

(Password is shown as None because it is hidden from the API response for public clients.)

We can now list schemas and tables in this server as in:

[16]:
%%rql

SELECT * FROM LS_SCHEMAS("pgsql:psql01")
[16]:
string
pgsql:psql01/information_schema
pgsql:psql01/pg_catalog
pgsql:psql01/public
[17]:
%%rql

SELECT * FROM LS_TABLES("pgsql:psql01/public")
[17]:
string
pgsql:psql01/public/episode_parenttconst_tconst_idx
pgsql:psql01/public/name_b_pkey
pgsql:psql01/public/title_b_pkey
pgsql:psql01/public/title_p_nconst_idx
pgsql:psql01/public/title_p_tconst_idx
pgsql:psql01/public/episode
pgsql:psql01/public/name_b
pgsql:psql01/public/title_b
pgsql:psql01/public/title_p
pgsql:psql01/public/movies
pgsql:psql01/public/names
pgsql:psql01/public/series

Finally, we can do query over a table as:

[23]:
%%rql

SELECT * FROM READ_PGSQL("psql01", "public", "movies") LIMIT 10
[23]:
tconststartyearprimarytitleoriginaltitleprimaryname
tt00271251935Top HatTop HatFred Astaire
tt00283331936Swing TimeSwing TimeFred Astaire
tt00348621942Holiday InnHoliday InnFred Astaire
tt00504191957Funny FaceFunny FaceFred Astaire
tt00531371959On the BeachOn the BeachFred Astaire
tt00373821944To Have and Have NotTo Have and Have NotLauren Bacall
tt00383551946The Big SleepThe Big SleepLauren Bacall
tt00393021947Dark PassageDark PassageLauren Bacall
tt00405061948Key LargoKey LargoLauren Bacall
tt00458911953How to Marry a MillionaireHow to Marry a MillionaireLauren Bacall

Note that many operations will be pushed down and executed in the database system directly.

[25]:
%%rql

SELECT * FROM READ_PGSQL("psql01", "public", "movies") WHERE startyear=1935 LIMIT 10
[25]:
tconststartyearprimarytitleoriginaltitleprimaryname
tt00259131935Triumph of the WillTriumph des WillensKarl Attenberger
tt00259131935Triumph of the WillTriumph des WillensAdolf Hitler
tt00259131935Triumph of the WillTriumph des WillensHermann Göring
tt00259131935Triumph of the WillTriumph des WillensMax Amann
tt00259131935Triumph of the WillTriumph des WillensMartin Bormann
tt00259131935Triumph of the WillTriumph des WillensLeni Riefenstahl
tt00259131935Triumph of the WillTriumph des WillensWalter Ruttmann
tt00259131935Triumph of the WillTriumph des WillensHerbert Windt
tt00259131935Triumph of the WillTriumph des WillensSepp Allgeier
tt00260291935The 39 StepsThe 39 StepsDerek N. Twist