Databases
SQL
The best way to connect to a SQL database is with next-jdbc. You’ll also need the relevant JDBC driver for the type of db you are trying to access. MySQL, PostgreSQL, Microsoft SQL Server, and Sqlite are supported. There are more details in the next-jdbc getting started guide. We’ll use Sqlite here for demonstration purposes, but beyond the backing driver that’s installed, the usage should be the same for any underlying database.
First, we add the correct dependencies to our deps.edn file:
com.github.seancorfield/next.jdbc {:mvn/version "1.3.939"}
org.xerial/sqlite-jdbc {:mvn/version "3.46.1.0"}Then require the necessary libraries:
(require '[next.jdbc :as jdbc])Next, connect to the db. We’ll use the Chinook sample database for demonstration purposes:
(def db {:dbname "data/databases/Chinook_Sqlite.sqlite"
:dbtype "sqlite"})Then we have to create a “datasource” from the db, against which we can run queries:
(def data-source (jdbc/get-datasource db))Now we can run SQL queries against the db:
(-> data-source
(jdbc/execute! ["SELECT * FROM artist"]))[#:Artist{:ArtistId 1, :Name "AC/DC"}
#:Artist{:ArtistId 2, :Name "Accept"}
#:Artist{:ArtistId 3, :Name "Aerosmith"}
#:Artist{:ArtistId 4, :Name "Alanis Morissette"}
#:Artist{:ArtistId 5, :Name "Alice In Chains"}
#:Artist{:ArtistId 6, :Name "Antônio Carlos Jobim"}
#:Artist{:ArtistId 7, :Name "Apocalyptica"}
#:Artist{:ArtistId 8, :Name "Audioslave"}
#:Artist{:ArtistId 9, :Name "BackBeat"}
#:Artist{:ArtistId 10, :Name "Billy Cobham"}
#:Artist{:ArtistId 11, :Name "Black Label Society"}
#:Artist{:ArtistId 12, :Name "Black Sabbath"}
#:Artist{:ArtistId 13, :Name "Body Count"}
#:Artist{:ArtistId 14, :Name "Bruce Dickinson"}
#:Artist{:ArtistId 15, :Name "Buddy Guy"}
#:Artist{:ArtistId 16, :Name "Caetano Veloso"}
#:Artist{:ArtistId 17, :Name "Chico Buarque"}
#:Artist{:ArtistId 18, :Name "Chico Science & Nação Zumbi"}
#:Artist{:ArtistId 19, :Name "Cidade Negra"}
#:Artist{:ArtistId 20, :Name "Cláudio Zoli"}
#:Artist{:ArtistId 21, :Name "Various Artists"}
#:Artist{:ArtistId 22, :Name "Led Zeppelin"}
#:Artist{:ArtistId 23, :Name "Frank Zappa & Captain Beefheart"}
#:Artist{:ArtistId 24, :Name "Marcos Valle"}
#:Artist{:ArtistId 25, :Name "Milton Nascimento & Bebeto"}
#:Artist{:ArtistId 26, :Name "Azymuth"}
#:Artist{:ArtistId 27, :Name "Gilberto Gil"}
#:Artist{:ArtistId 28, :Name "João Gilberto"}
#:Artist{:ArtistId 29, :Name "Bebel Gilberto"}
#:Artist{:ArtistId 30, :Name "Jorge Vercilo"}
#:Artist{:ArtistId 31, :Name "Baby Consuelo"}
#:Artist{:ArtistId 32, :Name "Ney Matogrosso"}
#:Artist{:ArtistId 33, :Name "Luiz Melodia"}
#:Artist{:ArtistId 34, :Name "Nando Reis"}
#:Artist{:ArtistId 35, :Name "Pedro Luís & A Parede"}
#:Artist{:ArtistId 36, :Name "O Rappa"}
#:Artist{:ArtistId 37, :Name "Ed Motta"}
#:Artist{:ArtistId 38, :Name "Banda Black Rio"}
#:Artist{:ArtistId 39, :Name "Fernanda Porto"}
#:Artist{:ArtistId 40, :Name "Os Cariocas"}
#:Artist{:ArtistId 41, :Name "Elis Regina"}
#:Artist{:ArtistId 42, :Name "Milton Nascimento"}
#:Artist{:ArtistId 43, :Name "A Cor Do Som"}
#:Artist{:ArtistId 44, :Name "Kid Abelha"}
#:Artist{:ArtistId 45, :Name "Sandra De Sá"}
#:Artist{:ArtistId 46, :Name "Jorge Ben"}
#:Artist{:ArtistId 47, :Name "Hermeto Pascoal"}
#:Artist{:ArtistId 48, :Name "Barão Vermelho"}
#:Artist{:ArtistId 49,
:Name "Edson, DJ Marky & DJ Patife Featuring Fernanda Porto"}
#:Artist{:ArtistId 50, :Name "Metallica"}
#:Artist{:ArtistId 51, :Name "Queen"}
#:Artist{:ArtistId 52, :Name "Kiss"}
#:Artist{:ArtistId 53, :Name "Spyro Gyra"}
#:Artist{:ArtistId 54, :Name "Green Day"}
#:Artist{:ArtistId 55, :Name "David Coverdale"}
#:Artist{:ArtistId 56, :Name "Gonzaguinha"}
#:Artist{:ArtistId 57, :Name "Os Mutantes"}
#:Artist{:ArtistId 58, :Name "Deep Purple"}
#:Artist{:ArtistId 59, :Name "Santana"}
#:Artist{:ArtistId 60, :Name "Santana Feat. Dave Matthews"}
#:Artist{:ArtistId 61, :Name "Santana Feat. Everlast"}
#:Artist{:ArtistId 62, :Name "Santana Feat. Rob Thomas"}
#:Artist{:ArtistId 63, :Name "Santana Feat. Lauryn Hill & Cee-Lo"}
#:Artist{:ArtistId 64, :Name "Santana Feat. The Project G&B"}
#:Artist{:ArtistId 65, :Name "Santana Feat. Maná"}
#:Artist{:ArtistId 66, :Name "Santana Feat. Eagle-Eye Cherry"}
#:Artist{:ArtistId 67, :Name "Santana Feat. Eric Clapton"}
#:Artist{:ArtistId 68, :Name "Miles Davis"}
#:Artist{:ArtistId 69, :Name "Gene Krupa"}
#:Artist{:ArtistId 70, :Name "Toquinho & Vinícius"}
#:Artist{:ArtistId 71, :Name "Vinícius De Moraes & Baden Powell"}
#:Artist{:ArtistId 72, :Name "Vinícius De Moraes"}
#:Artist{:ArtistId 73, :Name "Vinícius E Qurteto Em Cy"}
#:Artist{:ArtistId 74, :Name "Vinícius E Odette Lara"}
#:Artist{:ArtistId 75, :Name "Vinicius, Toquinho & Quarteto Em Cy"}
#:Artist{:ArtistId 76, :Name "Creedence Clearwater Revival"}
#:Artist{:ArtistId 77, :Name "Cássia Eller"}
#:Artist{:ArtistId 78, :Name "Def Leppard"}
#:Artist{:ArtistId 79, :Name "Dennis Chambers"}
#:Artist{:ArtistId 80, :Name "Djavan"}
#:Artist{:ArtistId 81, :Name "Eric Clapton"}
#:Artist{:ArtistId 82, :Name "Faith No More"}
#:Artist{:ArtistId 83, :Name "Falamansa"}
#:Artist{:ArtistId 84, :Name "Foo Fighters"}
#:Artist{:ArtistId 85, :Name "Frank Sinatra"}
#:Artist{:ArtistId 86, :Name "Funk Como Le Gusta"}
#:Artist{:ArtistId 87, :Name "Godsmack"}
#:Artist{:ArtistId 88, :Name "Guns N' Roses"}
#:Artist{:ArtistId 89, :Name "Incognito"}
#:Artist{:ArtistId 90, :Name "Iron Maiden"}
#:Artist{:ArtistId 91, :Name "James Brown"}
#:Artist{:ArtistId 92, :Name "Jamiroquai"}
#:Artist{:ArtistId 93, :Name "JET"}
#:Artist{:ArtistId 94, :Name "Jimi Hendrix"}
#:Artist{:ArtistId 95, :Name "Joe Satriani"}
#:Artist{:ArtistId 96, :Name "Jota Quest"}
#:Artist{:ArtistId 97, :Name "João Suplicy"}
#:Artist{:ArtistId 98, :Name "Judas Priest"}
#:Artist{:ArtistId 99, :Name "Legião Urbana"}
#:Artist{:ArtistId 100, :Name "Lenny Kravitz"}
#:Artist{:ArtistId 101, :Name "Lulu Santos"}
#:Artist{:ArtistId 102, :Name "Marillion"}
#:Artist{:ArtistId 103, :Name "Marisa Monte"}
#:Artist{:ArtistId 104, :Name "Marvin Gaye"}
#:Artist{:ArtistId 105, :Name "Men At Work"}
#:Artist{:ArtistId 106, :Name "Motörhead"}
#:Artist{:ArtistId 107, :Name "Motörhead & Girlschool"}
#:Artist{:ArtistId 108, :Name "Mônica Marianno"}
#:Artist{:ArtistId 109, :Name "Mötley Crüe"}
#:Artist{:ArtistId 110, :Name "Nirvana"}
#:Artist{:ArtistId 111, :Name "O Terço"}
#:Artist{:ArtistId 112, :Name "Olodum"}
#:Artist{:ArtistId 113, :Name "Os Paralamas Do Sucesso"}
#:Artist{:ArtistId 114, :Name "Ozzy Osbourne"}
#:Artist{:ArtistId 115, :Name "Page & Plant"}
#:Artist{:ArtistId 116, :Name "Passengers"}
#:Artist{:ArtistId 117, :Name "Paul D'Ianno"}
#:Artist{:ArtistId 118, :Name "Pearl Jam"}
#:Artist{:ArtistId 119, :Name "Peter Tosh"}
#:Artist{:ArtistId 120, :Name "Pink Floyd"}
#:Artist{:ArtistId 121, :Name "Planet Hemp"}
#:Artist{:ArtistId 122, :Name "R.E.M. Feat. Kate Pearson"}
#:Artist{:ArtistId 123, :Name "R.E.M. Feat. KRS-One"}
#:Artist{:ArtistId 124, :Name "R.E.M."}
#:Artist{:ArtistId 125, :Name "Raimundos"}
#:Artist{:ArtistId 126, :Name "Raul Seixas"}
#:Artist{:ArtistId 127, :Name "Red Hot Chili Peppers"}
#:Artist{:ArtistId 128, :Name "Rush"}
#:Artist{:ArtistId 129, :Name "Simply Red"}
#:Artist{:ArtistId 130, :Name "Skank"}
#:Artist{:ArtistId 131, :Name "Smashing Pumpkins"}
#:Artist{:ArtistId 132, :Name "Soundgarden"}
#:Artist{:ArtistId 133, :Name "Stevie Ray Vaughan & Double Trouble"}
#:Artist{:ArtistId 134, :Name "Stone Temple Pilots"}
#:Artist{:ArtistId 135, :Name "System Of A Down"}
#:Artist{:ArtistId 136,
:Name "Terry Bozzio, Tony Levin & Steve Stevens"}
#:Artist{:ArtistId 137, :Name "The Black Crowes"}
#:Artist{:ArtistId 138, :Name "The Clash"}
#:Artist{:ArtistId 139, :Name "The Cult"}
#:Artist{:ArtistId 140, :Name "The Doors"}
#:Artist{:ArtistId 141, :Name "The Police"}
#:Artist{:ArtistId 142, :Name "The Rolling Stones"}
#:Artist{:ArtistId 143, :Name "The Tea Party"}
#:Artist{:ArtistId 144, :Name "The Who"}
#:Artist{:ArtistId 145, :Name "Tim Maia"}
#:Artist{:ArtistId 146, :Name "Titãs"}
#:Artist{:ArtistId 147, :Name "Battlestar Galactica"}
#:Artist{:ArtistId 148, :Name "Heroes"}
#:Artist{:ArtistId 149, :Name "Lost"}
#:Artist{:ArtistId 150, :Name "U2"}
#:Artist{:ArtistId 151, :Name "UB40"}
#:Artist{:ArtistId 152, :Name "Van Halen"}
#:Artist{:ArtistId 153, :Name "Velvet Revolver"}
#:Artist{:ArtistId 154, :Name "Whitesnake"}
#:Artist{:ArtistId 155, :Name "Zeca Pagodinho"}
#:Artist{:ArtistId 156, :Name "The Office"}
#:Artist{:ArtistId 157, :Name "Dread Zeppelin"}
#:Artist{:ArtistId 158, :Name "Battlestar Galactica (Classic)"}
#:Artist{:ArtistId 159, :Name "Aquaman"}
#:Artist{:ArtistId 160, :Name "Christina Aguilera featuring BigElf"}
#:Artist{:ArtistId 161,
:Name "Aerosmith & Sierra Leone's Refugee Allstars"}
#:Artist{:ArtistId 162, :Name "Los Lonely Boys"}
#:Artist{:ArtistId 163, :Name "Corinne Bailey Rae"}
#:Artist{:ArtistId 164, :Name "Dhani Harrison & Jakob Dylan"}
#:Artist{:ArtistId 165, :Name "Jackson Browne"}
#:Artist{:ArtistId 166, :Name "Avril Lavigne"}
#:Artist{:ArtistId 167, :Name "Big & Rich"}
#:Artist{:ArtistId 168, :Name "Youssou N'Dour"}
#:Artist{:ArtistId 169, :Name "Black Eyed Peas"}
#:Artist{:ArtistId 170, :Name "Jack Johnson"}
#:Artist{:ArtistId 171, :Name "Ben Harper"}
#:Artist{:ArtistId 172, :Name "Snow Patrol"}
#:Artist{:ArtistId 173, :Name "Matisyahu"}
#:Artist{:ArtistId 174, :Name "The Postal Service"}
#:Artist{:ArtistId 175, :Name "Jaguares"}
#:Artist{:ArtistId 176, :Name "The Flaming Lips"}
#:Artist{:ArtistId 177, :Name "Jack's Mannequin & Mick Fleetwood"}
#:Artist{:ArtistId 178, :Name "Regina Spektor"}
#:Artist{:ArtistId 179, :Name "Scorpions"}
#:Artist{:ArtistId 180, :Name "House Of Pain"}
#:Artist{:ArtistId 181, :Name "Xis"}
#:Artist{:ArtistId 182, :Name "Nega Gizza"}
#:Artist{:ArtistId 183, :Name "Gustavo & Andres Veiga & Salazar"}
#:Artist{:ArtistId 184, :Name "Rodox"}
#:Artist{:ArtistId 185, :Name "Charlie Brown Jr."}
#:Artist{:ArtistId 186, :Name "Pedro Luís E A Parede"}
#:Artist{:ArtistId 187, :Name "Los Hermanos"}
#:Artist{:ArtistId 188, :Name "Mundo Livre S/A"}
#:Artist{:ArtistId 189, :Name "Otto"}
#:Artist{:ArtistId 190, :Name "Instituto"}
#:Artist{:ArtistId 191, :Name "Nação Zumbi"}
#:Artist{:ArtistId 192, :Name "DJ Dolores & Orchestra Santa Massa"}
#:Artist{:ArtistId 193, :Name "Seu Jorge"}
#:Artist{:ArtistId 194, :Name "Sabotage E Instituto"}
#:Artist{:ArtistId 195, :Name "Stereo Maracana"}
#:Artist{:ArtistId 196, :Name "Cake"}
#:Artist{:ArtistId 197, :Name "Aisha Duo"}
#:Artist{:ArtistId 198, :Name "Habib Koité and Bamada"}
#:Artist{:ArtistId 199, :Name "Karsh Kale"}
#:Artist{:ArtistId 200, :Name "The Posies"}
#:Artist{:ArtistId 201, :Name "Luciana Souza/Romero Lubambo"}
#:Artist{:ArtistId 202, :Name "Aaron Goldberg"}
#:Artist{:ArtistId 203, :Name "Nicolaus Esterhazy Sinfonia"}
#:Artist{:ArtistId 204, :Name "Temple of the Dog"}
#:Artist{:ArtistId 205, :Name "Chris Cornell"}
#:Artist{:ArtistId 206,
:Name "Alberto Turco & Nova Schola Gregoriana"}
#:Artist{:ArtistId 207,
:Name
"Richard Marlow & The Choir of Trinity College, Cambridge"}
#:Artist{:ArtistId 208, :Name "English Concert & Trevor Pinnock"}
#:Artist{:ArtistId 209,
:Name
"Anne-Sophie Mutter, Herbert Von Karajan & Wiener Philharmoniker"}
#:Artist{:ArtistId 210,
:Name
"Hilary Hahn, Jeffrey Kahane, Los Angeles Chamber Orchestra & Margaret Batjer"}
#:Artist{:ArtistId 211, :Name "Wilhelm Kempff"}
#:Artist{:ArtistId 212, :Name "Yo-Yo Ma"}
#:Artist{:ArtistId 213, :Name "Scholars Baroque Ensemble"}
#:Artist{:ArtistId 214,
:Name
"Academy of St. Martin in the Fields & Sir Neville Marriner"}
#:Artist{:ArtistId 215,
:Name
"Academy of St. Martin in the Fields Chamber Ensemble & Sir Neville Marriner"}
#:Artist{:ArtistId 216,
:Name
"Berliner Philharmoniker, Claudio Abbado & Sabine Meyer"}
#:Artist{:ArtistId 217,
:Name "Royal Philharmonic Orchestra & Sir Thomas Beecham"}
#:Artist{:ArtistId 218,
:Name
"Orchestre Révolutionnaire et Romantique & John Eliot Gardiner"}
#:Artist{:ArtistId 219,
:Name "Britten Sinfonia, Ivor Bolton & Lesley Garrett"}
#:Artist{:ArtistId 220,
:Name
"Chicago Symphony Chorus, Chicago Symphony Orchestra & Sir Georg Solti"}
#:Artist{:ArtistId 221,
:Name "Sir Georg Solti & Wiener Philharmoniker"}
#:Artist{:ArtistId 222,
:Name
"Academy of St. Martin in the Fields, John Birch, Sir Neville Marriner & Sylvia McNair"}
#:Artist{:ArtistId 223,
:Name "London Symphony Orchestra & Sir Charles Mackerras"}
#:Artist{:ArtistId 224,
:Name "Barry Wordsworth & BBC Concert Orchestra"}
#:Artist{:ArtistId 225,
:Name
"Herbert Von Karajan, Mirella Freni & Wiener Philharmoniker"}
#:Artist{:ArtistId 226, :Name "Eugene Ormandy"}
#:Artist{:ArtistId 227, :Name "Luciano Pavarotti"}
#:Artist{:ArtistId 228,
:Name "Leonard Bernstein & New York Philharmonic"}
#:Artist{:ArtistId 229,
:Name "Boston Symphony Orchestra & Seiji Ozawa"}
#:Artist{:ArtistId 230,
:Name "Aaron Copland & London Symphony Orchestra"}
#:Artist{:ArtistId 231, :Name "Ton Koopman"}
#:Artist{:ArtistId 232, :Name "Sergei Prokofiev & Yuri Temirkanov"}
#:Artist{:ArtistId 233,
:Name "Chicago Symphony Orchestra & Fritz Reiner"}
#:Artist{:ArtistId 234, :Name "Orchestra of The Age of Enlightenment"}
#:Artist{:ArtistId 235,
:Name "Emanuel Ax, Eugene Ormandy & Philadelphia Orchestra"}
#:Artist{:ArtistId 236, :Name "James Levine"}
#:Artist{:ArtistId 237,
:Name "Berliner Philharmoniker & Hans Rosbaud"}
#:Artist{:ArtistId 238, :Name "Maurizio Pollini"}
#:Artist{:ArtistId 239,
:Name
"Academy of St. Martin in the Fields, Sir Neville Marriner & William Bennett"}
#:Artist{:ArtistId 240, :Name "Gustav Mahler"}
#:Artist{:ArtistId 241,
:Name
"Felix Schmidt, London Symphony Orchestra & Rafael Frühbeck de Burgos"}
#:Artist{:ArtistId 242, :Name "Edo de Waart & San Francisco Symphony"}
#:Artist{:ArtistId 243,
:Name "Antal Doráti & London Symphony Orchestra"}
#:Artist{:ArtistId 244,
:Name "Choir Of Westminster Abbey & Simon Preston"}
#:Artist{:ArtistId 245,
:Name "Michael Tilson Thomas & San Francisco Symphony"}
#:Artist{:ArtistId 246,
:Name
"Chor der Wiener Staatsoper, Herbert Von Karajan & Wiener Philharmoniker"}
#:Artist{:ArtistId 247, :Name "The King's Singers"}
#:Artist{:ArtistId 248,
:Name "Berliner Philharmoniker & Herbert Von Karajan"}
#:Artist{:ArtistId 249,
:Name "Sir Georg Solti, Sumi Jo & Wiener Philharmoniker"}
#:Artist{:ArtistId 250, :Name "Christopher O'Riley"}
#:Artist{:ArtistId 251, :Name "Fretwork"}
#:Artist{:ArtistId 252, :Name "Amy Winehouse"}
#:Artist{:ArtistId 253, :Name "Calexico"}
#:Artist{:ArtistId 254,
:Name "Otto Klemperer & Philharmonia Orchestra"}
#:Artist{:ArtistId 255, :Name "Yehudi Menuhin"}
#:Artist{:ArtistId 256,
:Name "Philharmonia Orchestra & Sir Neville Marriner"}
#:Artist{:ArtistId 257,
:Name
"Academy of St. Martin in the Fields, Sir Neville Marriner & Thurston Dart"}
#:Artist{:ArtistId 258,
:Name "Les Arts Florissants & William Christie"}
#:Artist{:ArtistId 259,
:Name "The 12 Cellists of The Berlin Philharmonic"}
#:Artist{:ArtistId 260, :Name "Adrian Leaper & Doreen de Feis"}
#:Artist{:ArtistId 261,
:Name "Roger Norrington, London Classical Players"}
#:Artist{:ArtistId 262,
:Name "Charles Dutoit & L'Orchestre Symphonique de Montréal"}
#:Artist{:ArtistId 263,
:Name
"Equale Brass Ensemble, John Eliot Gardiner & Munich Monteverdi Orchestra and Choir"}
#:Artist{:ArtistId 264,
:Name "Kent Nagano and Orchestre de l'Opéra de Lyon"}
#:Artist{:ArtistId 265, :Name "Julian Bream"}
#:Artist{:ArtistId 266, :Name "Martin Roscoe"}
#:Artist{:ArtistId 267, :Name "Göteborgs Symfoniker & Neeme Järvi"}
#:Artist{:ArtistId 268, :Name "Itzhak Perlman"}
#:Artist{:ArtistId 269, :Name "Michele Campanella"}
#:Artist{:ArtistId 270, :Name "Gerald Moore"}
#:Artist{:ArtistId 271,
:Name "Mela Tenenbaum, Pro Musica Prague & Richard Kapp"}
#:Artist{:ArtistId 272, :Name "Emerson String Quartet"}
#:Artist{:ArtistId 273,
:Name
"C. Monteverdi, Nigel Rogers - Chiaroscuro; London Baroque; London Cornett & Sackbu"}
#:Artist{:ArtistId 274, :Name "Nash Ensemble"}
#:Artist{:ArtistId 275, :Name "Philip Glass Ensemble"}]Of course now that you have the power of SQL at your fingertips, there may be fewer things you need tablecloth for, but for the scenarios where it is still desirable, we can simply pass the results of any SQL query to tablecloth and it will handle it correctly, creating a dataset:
(-> data-source
(jdbc/execute! ["SELECT * FROM artist"])
(tc/dataset))_unnamed [275 2]:
| :Artist/ArtistId | :Artist/Name |
|---|---|
| 1 | AC/DC |
| 2 | Accept |
| 3 | Aerosmith |
| 4 | Alanis Morissette |
| 5 | Alice In Chains |
| 6 | Antônio Carlos Jobim |
| 7 | Apocalyptica |
| 8 | Audioslave |
| 9 | BackBeat |
| 10 | Billy Cobham |
| … | … |
| 265 | Julian Bream |
| 266 | Martin Roscoe |
| 267 | Göteborgs Symfoniker & Neeme Järvi |
| 268 | Itzhak Perlman |
| 269 | Michele Campanella |
| 270 | Gerald Moore |
| 271 | Mela Tenenbaum, Pro Musica Prague & Richard Kapp |
| 272 | Emerson String Quartet |
| 273 | C. Monteverdi, Nigel Rogers - Chiaroscuro; London Baroque; London Cornett & Sackbu |
| 274 | Nash Ensemble |
| 275 | Philip Glass Ensemble |
If you need to pass a parameter to a query, you can use a positional parameter (?)
(-> data-source
(jdbc/execute! ["SELECT * FROM artist WHERE Name = ?" "Aerosmith" ])
(tc/dataset))_unnamed [1 2]:
| :Artist/ArtistId | :Artist/Name |
|---|---|
| 3 | Aerosmith |
You can also pass multiple parameters, they’re passed in sequentially:
(-> data-source
(jdbc/execute! ["SELECT * FROM Track
INNER JOIN Album ON Track.AlbumId = Album.AlbumId
INNER JOIN Artist ON Album.ArtistId = Artist.ArtistId
WHERE (Artist.Name = ? AND Track.Milliseconds > ?)
ORDER BY Track.Milliseconds DESC"
"Queen"
300000])
(tc/dataset))_unnamed [4 14]:
| :Track/MediaTypeId | :Track/AlbumId | :Track/Milliseconds | :Track/GenreId | :Track/Bytes | :Track/TrackId | :Album/ArtistId | :Artist/ArtistId | :Track/Composer | :Track/Name | :Album/AlbumId | :Track/UnitPrice | :Artist/Name | :Album/Title |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 36 | 387761 | 1 | 12664591 | 424 | 51 | 51 | Queen | Innuendo | 36 | 0.99 | Queen | Greatest Hits II |
| 1 | 186 | 386194 | 1 | 12519388 | 2280 | 51 | 51 | May | It’s Late | 186 | 0.99 | Queen | News Of The World |
| 1 | 185 | 358948 | 1 | 11619868 | 2254 | 51 | 51 | Mercury, Freddie | Bohemian Rhapsody | 185 | 0.99 | Queen | Greatest Hits I |
| 1 | 36 | 343745 | 1 | 11358573 | 421 | 51 | 51 | Roger Taylor | Radio GA GA | 36 | 0.99 | Queen | Greatest Hits II |
Writing SQL strings by hand like this can become unwieldy, so you can also use honeysql to add some structure to your queries. It’s a lightweight SQL “DSL”. Really it’s a very lightweight way to express SQL queries as Clojure data structures. To use it we’ll add the dependency to our deps.edn file and require the library:
com.github.seancorfield/honeysql {:mvn/version "2.6.1147"}(require '[honey.sql :as sql])The query above written using honeysql looks like this:
(def tracks-query
{:select [:*]
:from [:Track]
:join [:Album [:= :Track.AlbumId :Album.AlbumId]
:Artist [:= :Album.ArtistId :Artist.ArtistId]]
:where [:and
[:= :Artist.Name :?artist]
[:> :Track.Milliseconds :?duration]]
:order-by [[:Track.Milliseconds :desc]]})(-> data-source
(jdbc/execute! (sql/format tracks-query
{:params {:artist "Queen" :duration 300000}}))
(tc/dataset))_unnamed [4 14]:
| :Track/MediaTypeId | :Track/AlbumId | :Track/Milliseconds | :Track/GenreId | :Track/Bytes | :Track/TrackId | :Album/ArtistId | :Artist/ArtistId | :Track/Composer | :Track/Name | :Album/AlbumId | :Track/UnitPrice | :Artist/Name | :Album/Title |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 36 | 387761 | 1 | 12664591 | 424 | 51 | 51 | Queen | Innuendo | 36 | 0.99 | Queen | Greatest Hits II |
| 1 | 186 | 386194 | 1 | 12519388 | 2280 | 51 | 51 | May | It’s Late | 186 | 0.99 | Queen | News Of The World |
| 1 | 185 | 358948 | 1 | 11619868 | 2254 | 51 | 51 | Mercury, Freddie | Bohemian Rhapsody | 185 | 0.99 | Queen | Greatest Hits I |
| 1 | 36 | 343745 | 1 | 11358573 | 421 | 51 | 51 | Roger Taylor | Radio GA GA | 36 | 0.99 | Queen | Greatest Hits II |
SPARQL
There are many different ways to connect to a graph database, depending on which one you’re using. One easy way to query a graph database that exposes a SPARQL endpoint is to use sparql-endpoint. We’ll also use some tools from grafter to help work with the resulting RDF values.
First, we add the dependency to our deps.edn file:
ont-app/sparql-endpoint {:mvn/version "0.2.1"}
io.github.swirrl/grafter.core {:mvn/version "3.0.0"}Then require the necessary libraries:
(require '[ont-app.sparql-endpoint.core :as ep])(require '[grafter-2.rdf.protocols :as pr])We can connect directly to a SPARQL endpoint using sparql-endpoint. We’ll use DBpedia as an example, a crowd-sourced community effort to extract structured content from Wikipedia. They expose a SPARQL endpoint that we can directly connect to:
(def endpoint "https://dbpedia.org/sparql")Working with graph databases can be confusing if you don’t already know what you’re looking for. To get started we can explore, for example, a list of the 100 most common things in this database. Running SELECT queries will return tabular results that will be easier for us to work with:
(def top-100
(let [query "SELECT DISTINCT ?uri (COUNT(?instance) as ?count)
WHERE {
?instance a ?uri .
}
GROUP BY ?uri
ORDER BY DESC(?count)
LIMIT 100"]
(ep/sparql-select endpoint query)))top-100[{"uri" {"type" "uri", "value" "http://xmlns.com/foaf/0.1/Document"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "16916808"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Image"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "12079364"}}
{"uri" {"type" "uri", "value" "http://www.w3.org/2002/07/owl#Thing"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "5465151"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/PhysicalEntity100001930"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "2822489"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Object100002684"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "2720459"}}
{"uri"
{"type" "uri", "value" "http://dbpedia.org/ontology/CareerStation"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "2708561"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Person"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "2293328"}}
{"uri"
{"type" "uri", "value" "http://dbpedia.org/ontology/TimePeriod"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "2242487"}}
{"uri"
{"type" "uri",
"value" "http://www.w3.org/2004/02/skos/core#Concept"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "2215203"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/YagoLegalActorGeo"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "2190191"}}
{"uri"
{"type" "uri",
"value"
"http://www.ontologydesignpatterns.org/ont/dul/DUL.owl#TimeInterval"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "2091566"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Whole100003553"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "2061272"}}
{"uri"
{"type" "uri", "value" "http://dbpedia.org/ontology/Eukaryote"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1988553"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Species"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1978481"}}
{"uri"
{"type" "uri", "value" "http://www.wikidata.org/entity/Q19088"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1968825"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Animal"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1963418"}}
{"uri" {"type" "uri", "value" "http://www.wikidata.org/entity/Q729"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1957963"}}
{"uri" {"type" "uri", "value" "http://schema.org/Person"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1860206"}}
{"uri"
{"type" "uri",
"value"
"http://www.ontologydesignpatterns.org/ont/dul/DUL.owl#NaturalPerson"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1860206"}}
{"uri"
{"type" "uri", "value" "http://www.wikidata.org/entity/Q215627"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1860206"}}
{"uri" {"type" "uri", "value" "http://xmlns.com/foaf/0.1/Person"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1860206"}}
{"uri" {"type" "uri", "value" "http://www.wikidata.org/entity/Q5"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1860206"}}
{"uri"
{"type" "uri",
"value"
"http://dbpedia.org/class/yago/YagoPermanentlyLocatedEntity"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1654844"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/YagoLegalActor"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1548331"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/LivingThing100004258"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1366066"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Organism100004475"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1365759"}}
{"uri"
{"type" "uri",
"value" "http://www.w3.org/2003/01/geo/wgs84_pos#SpatialThing"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1270302"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/CausalAgent100007347"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1229050"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Person100007846"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1216439"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Abstraction100002137"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "1079890"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/YagoGeoEntity"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "989272"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Place"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "804794"}}
{"uri" {"type" "uri", "value" "http://schema.org/Place"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "759924"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Location"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "759924"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Artifact100021939"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "689249"}}
{"uri"
{"type" "uri", "value" "http://dbpedia.org/ontology/Settlement"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "660470"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/WikicatLivingPeople"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "659093"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Location100027167"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "644115"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Work"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "640198"}}
{"uri" {"type" "uri", "value" "http://schema.org/CreativeWork"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "623392"}}
{"uri"
{"type" "uri", "value" "http://www.wikidata.org/entity/Q386724"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "623392"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Region108630985"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "620586"}}
{"uri"
{"type" "uri", "value" "http://dbpedia.org/ontology/PopulatedPlace"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "602105"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Athlete"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "578933"}}
{"uri"
{"type" "uri", "value" "http://dbpedia.org/ontology/PersonFunction"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "561568"}}
{"uri"
{"type" "uri", "value" "http://www.wikidata.org/entity/Q486972"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "530146"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/District108552138"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "497592"}}
{"uri"
{"type" "uri",
"value"
"http://dbpedia.org/class/yago/AdministrativeDistrict108491826"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "468228"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/ontology/SportsTeamMember"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "457298"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/ontology/OrganisationMember"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "456914"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/GeographicalArea108574314"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "455794"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Group100031264"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "421838"}}
{"uri"
{"type" "uri", "value" "http://dbpedia.org/ontology/Organisation"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "416343"}}
{"uri"
{"type" "uri",
"value"
"http://dbpedia.org/class/yago/PsychologicalFeature100023100"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "407890"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Agent"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "390190"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/SocialGroup107950920"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "373140"}}
{"uri"
{"type" "uri", "value" "http://www.wikidata.org/entity/Q24229398"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "372873"}}
{"uri"
{"type" "uri",
"value"
"http://www.ontologydesignpatterns.org/ont/dul/DUL.owl#Agent"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "372873"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Tenure"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "358117"}}
{"uri"
{"type" "uri", "value" "http://www.wikidata.org/entity/Q43229"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "352132"}}
{"uri" {"type" "uri", "value" "http://schema.org/Organization"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "352132"}}
{"uri"
{"type" "uri",
"value"
"http://www.ontologydesignpatterns.org/ont/dul/DUL.owl#SocialPerson"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "352132"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Event100029378"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "336611"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Organization108008335"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "333269"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Contestant109613191"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "329883"}}
{"uri"
{"type" "uri", "value" "http://dbpedia.org/ontology/SoccerPlayer"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "301990"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Village"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "300874"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Tract108673395"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "297711"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Athlete109820263"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "274439"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Site108651247"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "273147"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/ontology/ArchitecturalStructure"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "270716"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Instrumentality103575240"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "266889"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Player110439851"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "249470"}}
{"uri"
{"type" "uri", "value" "http://dbpedia.org/ontology/MusicalWork"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "249146"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Leader109623038"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "248122"}}
{"uri"
{"type" "uri", "value" "http://www.wikidata.org/entity/Q2188189"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "245177"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Creator109614315"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "220621"}}
{"uri" {"type" "uri", "value" "http://www.wikidata.org/entity/Q532"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "218968"}}
{"uri"
{"type" "uri", "value" "http://dbpedia.org/ontology/Politician"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "200848"}}
{"uri"
{"type" "uri", "value" "http://www.wikidata.org/entity/Q82955"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "200765"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Intellectual109621545"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "197175"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Album"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "195832"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Scholar110557854"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "192127"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Communicator109610660"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "189783"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Medium106254669"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "186057"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Worker109632518"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "176997"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Film"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "176722"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Artist109812338"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "176040"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/SocialEvent107288639"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "175404"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Communication100033020"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "171596"}}
{"uri"
{"type" "uri", "value" "http://www.wikidata.org/entity/Q937857"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "171544"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Insect"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "170627"}}
{"uri" {"type" "uri", "value" "http://dbpedia.org/ontology/Building"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "165493"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Alumnus109786338"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "162135"}}
{"uri"
{"type" "uri", "value" "http://dbpedia.org/class/yago/Act100030358"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "159783"}}
{"uri" {"type" "uri", "value" "http://schema.org/MusicAlbum"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "159425"}}
{"uri"
{"type" "uri", "value" "http://www.wikidata.org/entity/Q482994"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "159425"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Creation103129123"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "154294"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/Institution108053576"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "153363"}}
{"uri"
{"type" "uri",
"value" "http://dbpedia.org/class/yago/SkilledWorker110605985"},
"count"
{"type" "typed-literal",
"datatype" "http://www.w3.org/2001/XMLSchema#integer",
"value" "151864"}}]To get these values into a tablecloth dataset, we can just throw it at tabecloth and it will know what to do with this list of maps:
(def ds (tc/dataset top-100))ds_unnamed [100 2]:
| uri | count |
|---|---|
| {type uri, value http://xmlns.com/foaf/0.1/Document} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 16916808} | |
| {type uri, value http://dbpedia.org/ontology/Image} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 12079364} | |
| {type uri, value http://www.w3.org/2002/07/owl#Thing} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 5465151} | |
| {type uri, value http://dbpedia.org/class/yago/PhysicalEntity100001930} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 2822489} | |
| {type uri, value http://dbpedia.org/class/yago/Object100002684} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 2720459} | |
| {type uri, value http://dbpedia.org/ontology/CareerStation} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 2708561} | |
| {type uri, value http://dbpedia.org/ontology/Person} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 2293328} | |
| {type uri, value http://dbpedia.org/ontology/TimePeriod} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 2242487} | |
| {type uri, value http://www.w3.org/2004/02/skos/core#Concept} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 2215203} | |
| {type uri, value http://dbpedia.org/class/yago/YagoLegalActorGeo} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 2190191} | |
| … | … |
| {type uri, value http://dbpedia.org/class/yago/Communication100033020} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 171596} | |
| {type uri, value http://www.wikidata.org/entity/Q937857} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 171544} | |
| {type uri, value http://dbpedia.org/ontology/Insect} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 170627} | |
| {type uri, value http://dbpedia.org/ontology/Building} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 165493} | |
| {type uri, value http://dbpedia.org/class/yago/Alumnus109786338} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 162135} | |
| {type uri, value http://dbpedia.org/class/yago/Act100030358} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 159783} | |
| {type uri, value http://schema.org/MusicAlbum} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 159425} | |
| {type uri, value http://www.wikidata.org/entity/Q482994} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 159425} | |
| {type uri, value http://dbpedia.org/class/yago/Creation103129123} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 154294} | |
| {type uri, value http://dbpedia.org/class/yago/Institution108053576} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 153363} | |
| {type uri, value http://dbpedia.org/class/yago/SkilledWorker110605985} | {type typed-literal, |
| datatype http://www.w3.org/2001/XMLSchema#integer, | |
| value 151864} |
You’ll notice that RDF data is very rich. Every value comes with metadata that helps machines understand what to make of it, like its datatype and where to find the datatypes definition. For our purposes we only care about the values, so we can use grafter to extract those from the resulting RDF, updating the column values using tablecloth’s update-column:
(-> ds
(tc/update-columns :all (partial map #(get % "value"))))_unnamed [100 2]:
| uri | count |
|---|---|
| http://xmlns.com/foaf/0.1/Document | 16916808 |
| http://dbpedia.org/ontology/Image | 12079364 |
| http://www.w3.org/2002/07/owl#Thing | 5465151 |
| http://dbpedia.org/class/yago/PhysicalEntity100001930 | 2822489 |
| http://dbpedia.org/class/yago/Object100002684 | 2720459 |
| http://dbpedia.org/ontology/CareerStation | 2708561 |
| http://dbpedia.org/ontology/Person | 2293328 |
| http://dbpedia.org/ontology/TimePeriod | 2242487 |
| http://www.w3.org/2004/02/skos/core#Concept | 2215203 |
| http://dbpedia.org/class/yago/YagoLegalActorGeo | 2190191 |
| … | … |
| http://dbpedia.org/class/yago/Communication100033020 | 171596 |
| http://www.wikidata.org/entity/Q937857 | 171544 |
| http://dbpedia.org/ontology/Insect | 170627 |
| http://dbpedia.org/ontology/Building | 165493 |
| http://dbpedia.org/class/yago/Alumnus109786338 | 162135 |
| http://dbpedia.org/class/yago/Act100030358 | 159783 |
| http://schema.org/MusicAlbum | 159425 |
| http://www.wikidata.org/entity/Q482994 | 159425 |
| http://dbpedia.org/class/yago/Creation103129123 | 154294 |
| http://dbpedia.org/class/yago/Institution108053576 | 153363 |
| http://dbpedia.org/class/yago/SkilledWorker110605985 | 151864 |
Any data that comes as a list of maps, including SPARQL results, fits easily into tablecloth.