Steve Reads

SQL WITH-statement versus CREATE VIEW: have I been doing it all wrong? — February 9, 2016

SQL WITH-statement versus CREATE VIEW: have I been doing it all wrong?

Today I learned about the SQL WITH-statement. I’d been using CREATE VIEW whenever I needed to join a complex query to itself; have I been doing it all wrong all these years?

If nothing else, WITH is a lot more concise than what I had been doing, which was CREATE OR REPLACE TEMPORARY VIEW. Are there arguments for the one versus the other, apart from concision?

P.S.: I’m told — haven’t confirmed — that if you join a table A to itself using the WITH-statement, then A will be materialized just once, whereas it will be materialized twice under CREATE VIEW.

Querying Oracle databases from cx_Oracle under El Capitan — January 31, 2016

Querying Oracle databases from cx_Oracle under El Capitan

The following is my attempt to document a remarkably painful process that got even more remarkably painful under El Capitan than it was under earlier versions of Mac OS. It concerns querying an Oracle database from within a Python script via the cx_Oracle library. If you don’t query Oracle databases from Python scripts via the cx_Oracle library, you are hereby advised to do literally anything else.

The general process of the steps below is:

  1. Get Oracle Instant Client (aka sqlplus) running. This does a couple things. First, it lets you confirm that you can run Oracle queries at all, outside of Python. Second, it installs some libraries and executables that cx_Oracle uses.
  2. Muck around in the libraries that sqlplus installed, to change internal references from /ade/... to @loader_path/....
  3. Install cx_Oracle.
  4. Muck around in the cx_Oracle shared-object (.so) files to change their internal references from relative paths to absolute ones.

Here goes, start to finish:

  1. Download Oracle Instant Client. You want to download “Instant Client Package – Basic”, “Instant Client Package – JDBC Supplement”, “Instant Client Package – SQL*Plus”, “Instant Client Package – SDK”, and “Instant Client Package – WRC: Workload Replay Client”, because why download one file when you can download five? Additional notes about downloading these files:
    1. You need to accept the license-agreement radio button at the top of the page first.
    2. You likely want the 64-bit files, unless your machine is ancient.
    3. You need to log in to download these files, which means you need to create an Oracle login first. Because why make downloading easy when you can make it annoying?
  2. Unzip all the files you just downloaded. For the purposes of this exercise, I assume you downloaded these files to ${HOME}/Downloads.
    mkdir oracle
    cp *.zip !$
    for i in *.zip; do unzip $i; done
  3. Make a system-level directory that will house sqlplus. Since El Cap, you can’t touch anything under /usr, so you’ll want to put it in /usr/local or /opt. For reasons that now elude me, I put it in /opt/oracle.
  4. Change the ownership on /opt/oracle so that you, rather than root, own all the files therein.
    cp -r ~/Downloads/oracle/instantclient_11_2 /opt/oracle
    sudo chown -R $(whoami): !$

  5. In pre-El Cap versions of OS X, you’d set the ${DYLD_LIBRARY_PATH} environment variable to point to where your libraries lived. In the sqlplus case, you’d add /directory/where/sqplus/lives to ${DYLD_LIBRARY_PATH} by adding a line like

    export DYLD_LIBRARY_PATH=${DYLD_LIBRARY_PATH}:/directory/where/sqplus/lives

    to your ~/.bashrc file.

    El Cap seems to have changed the treatment of DYLD_LIBRARY_PATH as part of System Integrity Protection. Maybe DYLD_LIBRARY_PATH stops working? That’s a problem, because all the pre-El Cap steps to configure cx_Oracle that you’ll find on the Internet involve setting that DYLD_LIBRARY_PATH to override the library paths inside the sqlplus executable. The library paths that sqlplus comes with are all absolute paths that seem to refer to Oracle’s own internal build system — like so:

    (16:40 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ otool -L sqlplus
    /ade/dosulliv_sqlplus_mac/oracle/sqlplus/lib/libsqlplus.dylib (compatibility version 0.0.0, current version 0.0.0)
    /ade/b/2475221476/oracle/rdbms/lib/libclntsh.dylib.11.1 (compatibility version 0.0.0, current version 0.0.0)
    /ade/b/2475221476/oracle/ldap/lib/libnnz11.dylib (compatibility version 0.0.0, current version 0.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0)

    All those need to change to use the path to your sqlplus install.

  6. So if the $DYLD_LIBRARY_PATH option is out, one option is to muck around in the binary and change the library paths from /ade/... to some sort of variable that means “whatever path the executable is in”. Various places on the web suggested using @executable_path as that variable, but — again — that seems to work only under pre-El Cap versions of OS X. Now you need to use @loader_path instead (for reasons that I’ve not bothered to investigate). The method is like so:

    (16:43 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ sudo install_name_tool -change /ade/dosulliv_sqlplus_mac/oracle/sqlplus/lib/libsqlplus.dylib @loader_path/libsqlplus.dylib sqlplus
    (16:43 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ otool -L !$
    otool -L sqlplus
    @loader_path/libsqlplus.dylib (compatibility version 0.0.0, current version 0.0.0)
    /ade/b/2475221476/oracle/rdbms/lib/libclntsh.dylib.11.1 (compatibility version 0.0.0, current version 0.0.0)
    /ade/b/2475221476/oracle/ldap/lib/libnnz11.dylib (compatibility version 0.0.0, current version 0.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0)

    Note that the linked library is now @loader_path/.... Repeat this for all of the Oracle Instant Client files, and for all of the libraries linked within them. There’s a tool that does this. We end up, e.g., with

(16:45 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ otool -L sqlplus
    @loader_path/libsqlplus.dylib (compatibility version 0.0.0, current version 0.0.0)
    @loader_path/libclntsh.dylib.11.1 (compatibility version 0.0.0, current version 0.0.0)
    @loader_path/libnnz11.dylib (compatibility version 0.0.0, current version 0.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0)

Okay, so we fix up all the libraries. On my end, this allowed me to get a sqlplus console and connect to our Oracle server. Nice.

Next up: make the Python cx_Oracle library work with it. First, you need to create a couple of symlinks:

(16:49 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ sudo ln -s libclntsh.dylib{.11.1,}
(16:50 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ sudo ln -s libocci.dylib{.11.1,}

The reason here is apparently that cx_Oracle is trying to link in those libraries without referencing their version numbers; if you try to install cx_Oracle without those symlinks, you’ll get make(1) puke that contains this telling line:

    ld: library not found for -lclntsh

So you create the symlinks, then you do your classic pip install cx_oracle. Various places on the web suggested, more fully,

pip install --no-cache-dir --allow-external --allow-unverified cx_oracle

, and by this point I was past the point of arguing. That command worked, though it did warn me that --allow-external was deprecated and would eventually stop working.

Sure it’s installed, but that doesn’t mean it’ll actually work. The file now links to libclntsh.dylib.11.1 — a relative path with no /path/to/libraries/... prefix. You need to find the .egg file that Python actually loads when you do import cx_Oracle; in my case, that path is /Library/Python/2.7/site-packages/cx_Oracle-5.2.1-py2.7-macosx-10.11-intel.egg . That’s just a zip file, so

  1. copy it to some new empty directory under ${HOME}
  2. unzip it
  3. change the .so path from relative to absolute, via
    install_name_tool -change {,/opt/oracle/instantclient_11_2/}libclntsh.dylib.11.1 ./
  4. zip the directory back up into the .egg file and copy it from your ${HOME} subdirectory back into the global library path:
sudo mv cx_Oracle-5.2.1-py2.7-macosx-10.11-intel.egg /Library/Python/2.7/site-packages/cx_Oracle-5.2.1-py2.7-macosx-10.11-intel.egg
  1. Does it work?
(17:19 -0500) slaniel@work_laptop:~$ python -c 'import cx_Oracle' && echo 'worked'

Yes! It worked! (Contrast with what happens if you try ‘import cx_Oracl’ with no trailing ‘e’.)

And finally — finally — I can run queries against

LazyWeb request for a Time Magazine article — January 3, 2016
As a postscript to what I wrote the other day — December 29, 2015

As a postscript to what I wrote the other day

…about The Great Inversion, I’d direct you to the episode of The Weeds dealing with gentrification (inter alia). The Weeds is a podcast on the Panoply Network, which also hosts the quite excellent Amicus podcast starring Dahlia Lithwick. The Weeds features three people from Vox: Matt Yglesias, Sarah Kliff, and Ezra Klein. It has rapidly become my favorite podcast, and I eagerly listen to every new episode right when it comes out.

In the gentrification episode, Yglesias makes the point that there are lots of cities that would dream of experiencing a “great inversion”, where wealthy people move in and poor people move out. When we talk about a great inversion (which, as I mentioned in the aforelinked review of the book, should more accurately be called a “marginal inversion”), we’re really talking about a small number of cities on the coasts: Boston, New York, San Francisco, Seattle, L.A. … maybe 15 cities if we were really generous about it. We’re not talking about Cleveland or Detroit or Buffalo or Hartford or Erie. And when we’re talking about this “great” inversion, we’re really talking about a fraction of the rich people within those cities’ metro areas. But the media, the political system, and (I’m looking at you, San Francisco) too much elite discourse are dominated by people living on the coasts, so it’s not surprising that we’d be talking about a great inversion.

Now, granted, I’ve not looked at the data on this. Maybe Toledo and Gary are marginally inverting just as well as San Francisco and Boston. And maybe it’s more than marginal in New York and L.A. But my hunch is that it’s not. And The Great Inversion didn’t offer evidence in support of its claim.

Where to start reading Max Weber? — December 12, 2015

Where to start reading Max Weber?

This is a quick question. I read The Protestant Ethic a while ago, and found it very uninteresting. But everyone says that Weber is one of the founders of sociology. Francis Fukuyama’s most recent two books are entirely framed around Weber — specifically, that governments have reached their ideal form when they evolve out of clientelistic, patronage-based rule into professional, bureaucratized, meritocratic administration. As I recall, Fukuyama gave many hat tips to Weber’s Economy and Society; it seems to be Weber’s summa.

However, starting with Economy and Society wasn’t the right way to go. It’s a massive two-volume work, and I recall that it was actually lecture notes assembled by his students; it very much seems to be sociology for sociologists.

So my big-picture question is: how would you recommend that I get into Weber, assuming that he’s worth getting into? I hope the professional sociologists in the room don’t think that my dismissal of The Protestant Ethic is heresy; I’d be surprised if they did, so consequently I’d be surprised if they thought that that book was the proper entrée into Weber’s work.

So what is the right entry point? I am perfectly willing to read multiple books, if that’s what needs doing, and if the payoff justifies the investment. And of course, if the right answer is to start with secondary works — or to read secondary works in parallel with the primary works — I can absolutely do that.

(Those of you who think that the correct answer is “take a class” are probably right. However, my employer is not likely to pay for me to enroll in a sociology class. Hence: to the library we go.)

“If the misery of our poor be caused not by the laws of nature, but by our institutions, great is our sin” — December 11, 2015

“If the misery of our poor be caused not by the laws of nature, but by our institutions, great is our sin”

I’m reading Henry George’s Progress and Poverty, prompted in part by the 99% Invisible episode about the origins of Monopoly (the board game), but more prompted by having seen him cited many, many times; at the latest, I first saw him cited when I read The Worldly Philosophers as a callow youth.

Most of the attention the book gets is from its focus on land and the single tax. But I was really struck just now by his attack on Malthus. I’m embarrassed to say that I’ve seen Malthus cited not quite approvingly, but more as though he had proven some basic truths about the universe that were beyond all questioning. The Malthusian idea, of course, is that humanity will always remain teetering on a knife’s edge: whenever we accumulate a little extra in agricultural surplus, we immediately fritter it away by making more children, who consume all the surplus and return us to conditions of famine. The most anyone bothers to engage with this idea is to say that it used to be true, that it stopped being true at the Industrial Revolution, and that Malthus was unlucky enough to have written it five or ten seconds before the Industrial Revolution really got going in the early 19th century.

George rejects the whole Malthusian idea. As far as I’ve read, he doesn’t reject it because it’s obviously false, but rather because there’s no evidence that it’s true. The examples Malthus apparently cites are India, Ireland, and China, all of which — George says — are examples of famine caused by the intercession of a brutal government. (George disdains equally the Mughals and the Raj.) It may well be that humanity eventually reaches the carrying capacity of the land, kills itself off through famine, rebuild its numbers, and repeats the whole bloody cycle, but we’ve certainly (George says) not yet seen this pattern.

It embarrasses me that I’ve never read Malthus himself. Indeed, it embarrasses me that I’ve never even read anyone who questioned Malthus all that much. Gregory Clark’s annoying A Farewell To Alms more or less takes Malthus for granted, then explains that we in the UK and its dominions escaped the Malthusian curse by good old-fashioned Protestant self-abnegation. Malthus is alive and well; Henry George, 150 or so years ago, tells us that he shouldn’t be.

More generally, George feels that Malthus and his ilk are yet more examples of people ascribing the sad fate of the poor to their bestial natures rather than to the institutions that continually grind them into the dirt. My having accidentally gone along with this is what embarrasses me the most.

When people in fifty years ask, “How could you have let this happen?” — December 8, 2015

Get every new post delivered to your Inbox.