TheJach.com

Jach's personal blog

(Largely containing a mind-dump to myselves: past, present, and future)
Current favorite quote: "Supposedly smart people are weirdly ignorant of Bayes' Rule." William B Vogt, 2010

Can your SQL database do this?

New to LucidDB-dev (and soon 0.9.4) are a handful of scripting modules that support many different languages, such as Jython. Writing code in Java and loading it as a JAR in LucidDB is fun, but cumbersome; thanks to the standards in Java 6 though one can use JavaScript (bundled with Java) or, my preference, Python. As a fun example, let's grab a list of tweets to insert!

There's a Python twitter client here: http://code.google.com/p/python-twitter/ whose dependencies are easy to satisfy. (For Jython, see http://old.nabble.com/Easy_install-on-jython-2.5-td23525500.html for getting an "easy_install" on Jython to get the dependencies.) Just run the Jython's easy_install with "oauth2" and "simplejson" as arguments, and then run jython build.py install inside the twitter client's download folder. If you're good to go with Jython, go get a dev version of LucidDB. Make sure you add your Jython JAR to the classpath.gen file before starting it! For me, that's /home/kevin/jython2.5.2/jython.jar.

There is a caveat here: because we're relying on packages in Jython's site-packages folder, and because we're using the jython.jar in a ScriptEngine behind the scenes, we have to load the sys path ourselves. Fortunately this is done by adding the code import site to our Python, which the standard Jython does behind the scenes by itself.

Here then is our script:


import site
from java.sql import *
conn = DriverManager.getConnection("jdbc:default:connection")
# optionally do these
sql = """create or replace schema twitter
create table twitter.messages(message varchar(140))"""
for st in sql.split("\n"):
ps = conn.prepareStatement(st)
ps.execute()

# load twitter
import twitter
api = twitter.Api()
statuses = api.GetUserTimeline("Jachy")
sql = "insert into twitter.messages values "
sql += ",".join( ("(?)" for e in statuses) )
ps = conn.prepareStatement(sql)
idx = 1
for st in statuses:
ps.setString(idx, st.text)
idx += 1

ps.execute()
conn.close()


Notice the optional part is just to show that you can do anything. Normally we'd create the tables outside the script. Anyway, how do we run it? With LucidDB you can either run the script by saving it and passing in a filename, or instead of a filename you can just give a raw string to execute. I coded the script without single quotes (which are string delimiters in LucidDB) or semicolons (which LucidDB just freaks out on), so we can load it in a string. Here is my sqlLineClient output:


Connecting to jdbc:luciddb:http://localhost
Connected to: LucidDB (version 0.0.0)
Driver: LucidDbJdbcDriver (version 0.0)
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
sqlline version 1.0.8-eb by Marc Prud'hommeaux
0: jdbc:luciddb:http://localhost> call sys_root.change_default_character_set_to_unicode();
No rows affected (0.367 seconds)
0: jdbc:luciddb:http://localhost> call applib.execute_script('python', 'import site
. . . . . . . . . . . . . . . . > from java.sql import *
. . . . . . . . . . . . . . . . > conn = DriverManager.getConnection("jdbc:default:connection")
. . . . . . . . . . . . . . . . > # optionally do these
. . . . . . . . . . . . . . . . > sql = """create or replace schema twitter
. . . . . . . . . . . . . . . . > create table twitter.messages(message varchar(140))"""
. . . . . . . . . . . . . . . . > for st in sql.split("
"):
. . . . . . . . . . . . . . . . > ps = conn.prepareStatement(st)
. . . . . . . . . . . . . . . . > ps.execute()
. . . . . . . . . . . . . . . . >
. . . . . . . . . . . . . . . . > # load twitter
. . . . . . . . . . . . . . . . > import twitter
. . . . . . . . . . . . . . . . > api = twitter.Api()
. . . . . . . . . . . . . . . . > statuses = api.GetUserTimeline("Jachy")
. . . . . . . . . . . . . . . . > sql = "insert into twitter.messages values "
. . . . . . . . . . . . . . . . > sql += ",".join( ("(?)" for e in statuses) )
. . . . . . . . . . . . . . . . > ps = conn.prepareStatement(sql)
. . . . . . . . . . . . . . . . > idx = 1
. . . . . . . . . . . . . . . . > for st in statuses:
. . . . . . . . . . . . . . . . > ps.setString(idx, st.text)
. . . . . . . . . . . . . . . . > idx += 1
. . . . . . . . . . . . . . . . >
. . . . . . . . . . . . . . . . > ps.execute()
. . . . . . . . . . . . . . . . > conn.close()
. . . . . . . . . . . . . . . . > ');
No rows affected (2.05 seconds)
0: jdbc:luciddb:http://localhost> select * from twitter.messages;
MESSAGE I'm the [un]proud owner of a COBOL online manual that'll die on June 1st. Do I dare give it a serious reading? How much do COBOL devs make?

MESSAGE Ha, take that AT&T. I just curl mirror'd that site you're going to stop serving on June 1st.

MESSAGE I'm so happy, I have ssh to my computer again.

MESSAGE @rationalists Hurr. Not really. I bug myself when I think about a joke and realize it's not funny because it's not accurate. :/

MESSAGE @abuhatem @TheYoungTurks How in the world is that rational? Ah well. Heard of neocameralism?

MESSAGE A Lisp Alien friend drew a month ago after asking me what to draw http://kevinmccloud.deviantart.com/art/Lisp-Alien-by-Estic-205626045 #lisp

MESSAGE @bookhling Human lives are fractals, your age is your level of zoom...

MESSAGE All you non-atheists being raptured today? Ask god if he thinks UTF-8 is the best character coding scheme ever.

MESSAGE Couldn't sleep, so I ported my PHP blog over to unicode. http://www.thejach.com/view/2011/5/hello_unicode

MESSAGE Did Eragon get translated into Spanish recently or something?

MESSAGE Bit by the JSON-requiring-double-quotes bug again. Too used to choice!

MESSAGE @dhewlett I heard Linus Torvalds can recover files from /dev/null.

MESSAGE My mom just threw away a bottle of mustard that was in the fridge whose sell-by date was Dec. 22nd, 07.

MESSAGE I have bad priorities. I'd rather add utf-8 support on the PHP side of my blog (and do the code audit) than implement new comment emails.

MESSAGE "The wicked flee when no man pursueth: but the righteous are bold as a lion." doesn't seem to encourage religious people to debate.

MESSAGE Okay, closet Star Wars fan satiated.

MESSAGE Sir, would it help if you told him it was my pants? #replaceawordinastarwarslinewithpants

MESSAGE I almost forgot. There's a battle going on! Right here in our
pants. Come and look! #replaceawordinastarwarslinewithpants

MESSAGE Where are those pants you intercepted? #replaceawordinastarwarslinewithpants

MESSAGE From my blog: The FFP Machine on an FPGA (sorta) http://www.thejach.com/view/2011/5/the_ffp_machine_implemented_with_an_fpga

20 rows selected (0.122 seconds)
0: jdbc:luciddb:http://localhost>


Pretty cool, huh? (Note the first command I sent was to tell LucidDB to tell the fresh installation it should store unicode.)


Posted on 2011-05-25 by Jach

Tags: LucidDB, python, sql

Permalink: http://www.thejach.com/view/id/175

Trackback URL: http://www.thejach.com/view/2011/5/can_your_sql_database_do_this

Back to the top

Jach 14 November 2013 10:50:47 AM
I was thinking about how awesome LucidDB is in some respects and thought I'd come back to this post to point at the set of scripting routines available in 0.9.4. It can do more than just execute scripts in any arbitrary JVM language, it can also call user-defined functions/methods and user-defined transformations. The above twitter example could be better expressed as a user-defined transformation, you could pass in as the input-cursor values('username'), and the output would be the messages for that user name.
Back to the first comment

Comment using the form below

(Only if you want to be notified of further responses, never displayed.)

Your Comment:

LaTeX allowed in comments, use $$\$\$...\$\$$$ to wrap inline and $$[math]...[/math]$$ to wrap blocks.