In this tutorial we'll learn how to use Blaze to discover, migrate, and query data living in other databases. Generally this tutorial will have the following format
odo - Move data to databaseblaze - Query data in databaseNumPy and Pandas provide accessible, interactive, analytic queries; this is valuable.
In [1]:
import pandas as pd
df = pd.read_csv('data/iris.csv')
df.head()
Out[1]:
In [2]:
df.groupby(df.Species).PetalLength.mean() # Average petal length per species
Out[2]:
But as data grows and systems become more complex, moving data and querying data become more difficult. Python already has excellent tools for data that fits in memory, but we want to hook up to data that is inconvenient.
From now on, we're going to assume one of the following:
When in-memory arrays/dataframes cease to be an option, we turn to databases. These live outside of the Python process and so might be less convenient. The open source Python ecosystem includes libraries to interact with these databases and with foreign data in general.
Examples:
sqlalchemy pyhiveimpylaredshift-sqlalchemypymongohappybasepysparkparamikopywebhdfsbotoToday we're going to use some of these indirectly with odo (was into) and Blaze. We'll try to point out these libraries as we automate them so that, if you'd like, you can use them independently.
odo (formerly into)Odo migrates data between formats and locations.
Before we can use a database we need to move data into it. The odo project provides a single consistent interface to move data between formats and between locations.
We'll start with local data and eventually move out to remote data.
Odo moves data into a target from a source
>>> odo(source, target)
The target and source can be either a Python object or a string URI. The following are all valid calls to into
>>> odo('iris.csv', pd.DataFrame) # Load CSV file into new DataFrame
>>> odo(my_df, 'iris.json') # Write DataFrame into JSON file
>>> odo('iris.csv', 'iris.json') # Migrate data from CSV to JSON
In [4]:
from odo import odo
import numpy as np
import pandas as pd
In [5]:
odo("data/iris.csv", pd.DataFrame, index_col=0)
Out[5]:
In [5]:
odo("data/iris.csv", list)
Out[5]:
In [6]:
odo("data/iris.csv", np.ndarray)
Out[6]:
Odo refers to foreign data either with a Python object like a sqlalchemy.Table object for a SQL table, or with a string URI, like postgresql://hostname::tablename.
URI's often take on the following form
protocol://path-to-resource::path-within-resource
Where path-to-resource might point to a file, a database hostname, etc. while path-within-resource might refer to a datapath or table name. Note the two main separators
:// separates the protocol on the left (sqlite, mongodb, ssh, hdfs, hive, ...):: separates the path within the database on the right (e.g. tablename)
In [7]:
odo("data/iris.csv", "sqlite:///my.db::iris")
Out[7]:
What kind of object did you get receive as output? Call type on your result.
In [8]:
type(_)
Out[8]:
Odo is a network of fast pairwise conversions between pairs of formats. We when we migrate between two formats we traverse a path of pairwise conversions.
We visualize that network below:
Each node represents a data format. Each directed edge represents a function to transform data between two formats. A single call to into may traverse multiple edges and multiple intermediate formats. Red nodes support larger-than-memory data.
A single call to into may traverse several intermediate formats calling on several conversion functions. For example, we when migrate a CSV file to a Mongo database we might take the following route:
DataFrame (pandas.read_csv)np.recarray (DataFrame.to_records)Iterator (np.ndarray.tolist)pymongo.Collection.insert)Alternatively we could write a special function that uses MongoDB's native CSV
loader and shortcut this entire process with a direct edge CSV -> Mongo.
These functions are chosen because they are fast, often far faster than converting through a central serialization format.
This picture is actually from an older version of odo, when the graph was still small enough to visualize pleasantly. See odo docs for a more updated version.
We can interact with remote data in three locations
sshFor most of this we'll wait until we've seen Blaze, briefly we'll use S3.
For now, we quickly grab a file from Amazon's S3.
This example depends on boto to interact with S3.
conda install boto
In [10]:
odo('s3://nyqpug/tips.csv', pd.DataFrame)
Out[10]:
In [11]:
import pandas as pd
df = pd.read_csv('data/iris.csv')
df.head(5)
Out[11]:
In [12]:
df.Species.unique()
Out[12]:
In [12]:
df.Species.drop_duplicates()
Out[12]:
In [13]:
import blaze as bz
d = bz.Data('data/iris.csv')
d.head(5)
Out[13]:
In [14]:
d.Species.distinct()
Out[14]:
Blaze does different things under-the-hood on different kinds of data
We'll play with SQL a lot during this tutorial. Blaze translates your query to SQLAlchemy. SQLAlchemy then translates to the SQL dialect of your database, your database then executes that query intelligently.
This translation process lets analysts interact with a familiar interface while leveraging a potentially powerful database.
To keep things local we'll use SQLite, but this works with any database with a SQLAlchemy dialect. Examples in this section use the iris dataset. Exercises use the Lahman Baseball statistics database, year 2013.
If you have not downloaded this dataset you could do so here - https://github.com/jknecht/baseball-archive-sqlite/raw/master/lahman2013.sqlite.
In [15]:
db = bz.Data('sqlite:///my.db')
#db.iris
#db.iris.head()
In [16]:
db.iris.Species.distinct()
Out[16]:
In [17]:
db.iris[db.iris.Species == 'versicolor'][['Species', 'SepalLength']]
Out[17]:
If we were using pandas we would read the table into pandas, then use pandas' fast in-memory algorithms for computation. Here we translate your query into SQL and then send that query to the database to do the work.
If we want to dive into the internal API we can inspect the query that Blaze transmits.
In [18]:
# Inspect SQL query
query = db.iris[db.iris.Species == 'versicolor'][['Species', 'SepalLength']]
print bz.compute(query)
In [19]:
query = bz.by(db.iris.Species, longest=db.iris.PetalLength.max(),
shortest=db.iris.PetalLength.min())
print bz.compute(query)
In [20]:
# db = bz.Data('postgresql://postgres:postgres@ec2-54-159-160-163.compute-1.amazonaws.com') # Use Postgres if you don't have the sqlite file
db = bz.Data('sqlite:///data/lahman2013.sqlite')
db.dshape
Out[20]:
In [21]:
# View the Salaries table
t = bz.Data('sqlite:///data/lahman2013.sqlite::Salaries')
t.dshape
Out[21]:
In [22]:
# What are the distinct teamIDs in the Salaries table?
t.teamID.distinct()
Out[22]:
In [23]:
from odo import odo
odo(t.teamID.distinct(), list)
Out[23]:
In [24]:
query = t.teamID.distinct()
print bz.compute(query)
In [25]:
# What is the minimum and maximum yearID in the Salaries table?
t.yearID.min()
Out[25]:
In [26]:
t.yearID.max()
Out[26]:
In [27]:
# For the Oakland Athletics (teamID OAK), pick out the playerID, salary, and yearID columns
t[t.teamID=='OAK'][['playerID', 'salary', 'yearID']]
Out[27]:
In [28]:
oak = t[t.teamID=='OAK'][['playerID', 'salary', 'yearID']]
In [29]:
oak
Out[29]:
In [30]:
odo(oak, 'oak.csv')
Out[30]:
In [31]:
# Sort that result by salary.
# Use the ascending=False keyword argument to the sort function to find the highest paid players
oak.sort('salary',ascending=False)
Out[31]:
In [32]:
import pandas as pd
iris = pd.read_csv('data/iris.csv')
iris.groupby('Species').PetalLength.min()
Out[32]:
In [33]:
iris = bz.Data('sqlite:///my.db::iris')
bz.by(iris.Species, largest=iris.PetalLength.max(),
smallest=iris.PetalLength.min())
print(_)
By default Blaze only shows us the first ten lines of a result. This provides a more interactive feel and stops us from accidentally crushing our system. Sometimes we do want to compute all of the results and store them someplace.
Blaze expressions are valid sources for odo. So we can store our results in any format.
In [34]:
result = bz.by(db.Salaries.teamID, avg=db.Salaries.salary.mean(),
max=db.Salaries.salary.max(),
ratio=db.Salaries.salary.max() / db.Salaries.salary.min()
).sort('ratio', ascending=False)
In [35]:
odo(result, list)[:10]
Out[35]:
In [36]:
odo(result, 'sqlite:///my.db::result')
Out[36]: