Python_and_sql_better_together

Python and SQL: Better Together

Python and SQL are complementary - we should focus on how best to integrate them rather than try to replace them!

By Alex Monahan
2021-08-15 (Yes, this is the one date format to rule them all)
LinkedIn
Twitter @__alexmonahan__
The views I express are my own and not my employer’s.

Welcome to my first blog post! I welcome any and all feedback over on Twitter - I’d love to learn from all of you!

There has been some spirited debate over SQL on Tech Twitter in the last few weeks. I am a huge fan of open discussions like this, and I consistently learn something when reading multiple viewpoints. It’s my hope to contribute to a friendly and productive dialog with fellow data folks! Let’s focus on a few positives from each perspective.

Here, Jamie Brandon makes some excellent points about SQL’s weaknesses. The points I agree with the most are related to SQL’s incompressibility. The way I encounter this most frequently is that it is difficult to execute queries on a dynamic list of columns purely in SQL. I also wish that it was easier to modularize code into functions in more powerful way, although there are some ways of doing so. Imagine a repository of SQL helper functions you could import! If only it were possible. If it is, please let me know on Twitter!

Pedram Navid responded to those points in an indirect way that I also found to be very impactful and thought provoking. Pedram focused on the organizational impacts of choosing to move away from SQL. I agree with Pedram that SQL is a tremendous data democratization tool and that it is important that SQL folks and other programming language folks work as a team. He also makes the case that SQL is often good enough, and I would go a step further and say there are many cases where SQL is a very expressive way to request and manipulate data! The cases where SQL is most useful are very accessible and can really empower people where data is just a portion of their job. SQL is the easiest to learn superpower as a data person!

Why use SQL?

Before mixing and matching, why would we want to use SQL in the first place? While I agree with Jamie that it is imperfect, it has many redeeming qualities! Toss any others I’ve forgotten on Twitter!

1. SQL is very widely used
   • It ranks 3rd in the Stack Overflow survey (see above graph!), and it was invented all the way back in 1979!
   • Excel and nearly every Business Intelligence tool provide a SQL interface. Plus Python’s standard library includes SQLite, which is in the top 5 most widely deployed pieces of software in existence!
   • I also agree with Erik Bernhardsson’s blog post I don’t want to learn your garbage query language. Let’s align on SQL so we only have to learn or develop things once!
2. The use of SQL is expanding
   • The growth of cloud data warehouses is a huge indication of the power of SQL. Stream processing is adding SQL support, and the SQL language itself continues to grow in power and flexibility.
3. SQL is easy to get started with
   • While I don’t necessarily have sources to cite here, I have led multiple SQL training courses that can take domain experts from 0 to introductory SQL in 8 hours. While I have not done the same for other languages, I feel like it would be difficult to be productive that quickly!
4. However, it’s hard to outgrow the need for SQL
   • Even the majority of data scientists use SQL “Sometimes” or more - placing it as the number 2 language in Anaconda’s State of Data Science 2021.
   • This also makes SQL great for your career!
5. SQL’s declarative nature removes the need to understand database internals
   • The deliberate separation between the user’s request and the specific algorithms used by the database is an excellent abstraction layer 99%+ of the time. You can write years of productive SQL queries before learning the difference between a hash join and a sorted merge loop join! And even then, the database will usually choose correctly on your behalf.
6. Chances are good you need to use SQL to pull your data initially anyway
   • If you already need to know some SQL to access your company’s valuable info, why not maximize your effectiveness with it?

Asterisks indicate libraries I have not used yet, but that I am excited to try!

• DuckDB
  • Think of this as SQLite for analytics! I list the many pros of DuckDB below. It is my favorite way to mix and match SQL and Pandas.
• SQLite
  • SQLite is an easy way to process larger than memory data in Python. It comes bundled in the standard library.
  • There are a few drawbacks that DuckDB addresses:
    • Slow performance for analytics (row-based instead of columnar like Pandas and DuckDB)
    • Requires data to be inserted into SQLite before executing a query on it
    • Overly flexible data types (This is debatable, but it makes it harder to interact with Pandas in my experience)
• SQLAlchemy
  • SQLAlchemy can connect to tons of different databases. It’s a huge advantage for the Python ecosystem.
  • When used in combination with pandas.read_sql, SQLAlchemy can pull from a SQL DB and load a Pandas DataFrame.
  • SQLAlchemy is traditionally known for its ORM (Object Relational Mapper) capabilities which allow you to avoid SQL. However, it also has powerful features for SQL fans like safe parameter escaping
  • There are many other tools for querying SQL DB’s
    • pyodbc - Uses ODBC drivers for querying which is flexible, but can be slow
    • turbodbc* - Uses Apache Arrow to speed up ODBC connections
    • A variety of DB-specific native connectors (Ex: cx_oracle, psycopg2) - These are fast, but not universal
• ipython-sql
  • Convert a Jupyter cell into a SQL language cell! This enables syntax highlighting for SQL in Jupyter.
  • I find this much nicer than working with SQL in multi-line strings, and you get syntax highlighting without having separate SQL files.
  • This builds on top of SQLAlchemy
  • See an example below!
• PostgreSQL’s PL/Python*
  • You can write functions and procedures on Postgres using Python!
  • I think this addresses some of the concerns highlighted by Jamie Brandon, but as popular as Postgres is, it’s not a standard feature of all DBs
  • This is a more DB-centric approach. I’ve found DB stored procedures to be more challenging to use with version control, but they certainly have value
• Dask-sql*
  • Process SQL statements on a Dask cluster (on your local machine or 1000’s of servers!)
  • The first bullet in the readme advertises easy Python and SQL interoperability, which sounds great!
  • This comes with some extra complexity over DuckDB - you’ll need Java for the Apache Calcite query parser and a few lines of code to set up a Dask cluster.
  • I’d like to benchmark this a bit to see how well it performs in my single machine use case
• dbt*
  • dbt is a way to build SQL pipelines and execute jinja-templated SQL
  • The templating seems like a powerful way to avoid some of SQL’s pain points like a rigid set of columns and requiring columns be specified in the SELECT and GROUP BY clauses.
  • dbt also works well with version control systems
  • I am very excited to try it out, but I am not a cloud data warehouse user so I am slightly outside their target audience
  • There are adapters for both DuckDB and Clickhouse (A fast, massively popular open source columnar data warehouse) so this should perform well. These are both community maintained, so I’ll just need to test them out a bit!

I plan to explore more of these in upcoming posts!

DuckDB - One powerful way to mix Python and SQL

DuckDB is best summarized as the SQLite of analytics. In under 10ms, you can spin up your own in-process database that is 20x faster than SQLite, and in most cases faster than Pandas!

Besides the speed, why do I love DuckDB?

1. It works seamlessly with Pandas
   • You can query a DataFrame without needing to insert it into the DB, and you can return results as DF’s as well. This is both simple and very fast since it is in the same process as Python.
2. Setup is easy
   • Just pip install duckdb and you’re all set.
3. DuckDB supports almost all of PostgreSQL’s syntax, but also smooths rough edges
   • When I first tested out DuckDB, it could handle everything I threw at it: Recursive CTE’s, Window functions, arbitrary subqueries, and more. Even lateral joins are supported! Since then, it has only improved by adding Regex, statistical functions, and more!
   • As an example of smoothing rough edges, Postgres is notoriously picky about capitalization, but DuckDB is not case sensitive. While most function names come from Postgres, many equivalent function names from other DB’s can also be used.

4. MIT licensed

5. Parquet, csv, and Apache Arrow structures can also be queried by DuckDB
   • Interoperability with Arrow and Parquet in particular expand the ecosystem that DuckDB can interact with.
6. DuckDB continues to dramatically improve, and the developers are fantastic!
   • Truthfully, this should be item 0! I’ve had multiple (sometimes tricky) bugs be squashed in a matter of days, and many of my feature requests have been added. The entire team is excellent!
7. Persistence comes for free, but is optional! This allows DuckDB to work on larger-than-memory data.
   • If you are building a data pipeline, it can be super useful to see all of the intermediate steps. Plus, I believe that DuckDB databases are going to become a key multi-table data storage structure, just as SQLite is today. The developers are in the midst of adding some powerful compression to DuckDB’s storage engine, so I see significant potential here.
8. Did I mention it’s fast?
   • DuckDB is multi-threaded, so you can utilize all your CPU cores without any work on your end - no need to partition your data or anything!
9. DuckDB has a Relational API that is targeting Pandas compatibility
   • While I am admittedly a SQL fan, having a relational API can be very helpful to add in some of the dynamism and flexibility of Pandas.

An example workflow with DuckDB

#I use Anaconda, so Pandas, and SQLAlchemy are already installed. Otherwise pip install those to start with
# !pip install pandas
# !pip install sqlalchemy

!pip install duckdb==0.2.8

#This is a SQLalchemy driver for DuckDB. It powers the ipython-sql library below 
#Thank you to the core developer of duckdb_engine, Elliana May! 
#She rapidly added a feature and squashed a bug so that it works better with ipython-sql!
!pip install duckdb_engine==0.1.8rc4 

#This allows for the %%sql magic in Jupyter to build SQL language cells!
!pip install ipython-sql

import duckdb
import pandas as pd
import sqlalchemy
#no need to import duckdb_engine - SQLAlchemy will auto-detect the driver needed based on your connection string!

Let’s play with a moderately sized dataset: a 1.6 GB csv

This analysis is inspired by this DuckDB intro article by Uwe Korn and can be downloaded here. Note that DuckDB can handle much larger datasets - this is only an example!

First we load the ipython-sql extension and default our output to be in Pandas DF format. We will also simplify what is printed after each SQL statement.
Next, we connect to an in-memory DuckDB and set it to use all our available CPU horsepower!

%load_ext sql
%config SqlMagic.autopandas=True
%config SqlMagic.feedback = False
%config SqlMagic.displaycon = False

%sql duckdb:///:memory:
%sql pragma threads=16 

Check out this super clean syntax for directly querying a CSV file! As a note, we could have also used pandas.read_csv and then queried the resulting DataFrame. DuckDB’s csv reader allows us to skip a step!

%%sql
create table taxis as
SELECT * FROM 'yellow_tripdata_2016-01.csv';

Our csv import took 15 seconds: 40% faster than the 25 seconds Pandas required!

(the csv was loaded into RAM prior to timing so it may take an extra few seconds in both cases if you weren’t already working with that file)

Now let’s take a quick sample of our dataset and load it into a Pandas DF.

%%sql
SELECT
    *
FROM taxis
USING SAMPLE 5

As you can see below, DuckDB did a great job auto-detecting our column types. One more traditional DB hassle eliminated!

Now let’s do some analysis!

First, let’s see how we can pass in a parameter through ipython-sql to better understand how VendorId 1 is behaving. Just for fun, we are going to pass in the entire WHERE clause. Why? It shows that you can build up dynamic SQL statements using Python! It’s not quite as easy to use as a templating language, but just as flexible.

my_where_clause = """
    WHERE
        vendorid = 1
"""

%%sql
SELECT
    vendorid
    ,passenger_count
    ,count(*) as count
    ,avg(fare_amount/total_amount) as average_fare_percentage
    ,avg(trip_distance) as average_distance
FROM taxis
{my_where_clause}
GROUP BY
    vendorid
    ,passenger_count

Next we’ll aggregate our data in DuckDB (which is exceptionally fast for Group By queries), and then pivot the result with Pandas. Pandas is a great fit for pivoting because the column names are not known ahead of time, which would require writing some dynamic SQL. However, DuckDB has a pivot_table function on their roadmap for their Python Relational (read, DataFrame-like) API! This will allow us to pivot larger than memory data and use multiple CPU cores for that pivoting operation.

Note: The more complex the query, the better DuckDB performs relative to Pandas! This is because it can do more work in less passes through the dataset, and because it is using multiple CPU cores.

%%sql aggregated_df <<
SELECT
    --Aggregate up to a weekly level. lpad makes sure week numbers always have 2 digits (Ex: '02' instead of '2')
    date_part('year',tpep_pickup_datetime) || 
        lpad(cast(date_part('week',tpep_pickup_datetime) as varchar),2,'0') as yyyyww
    ,passenger_count
    ,count(*) as count
    ,min(total_amount) as min_amount
    ,quantile_cont(total_amount,0.1) as _10th_percentile
    ,quantile_cont(total_amount,0.25) as _25th_percentile
    ,quantile_cont(total_amount,0.5) as _50th_percentile
    ,avg(total_amount) as avg_amount
    ,quantile_cont(total_amount,0.75) as _75th_percentile
    ,quantile_cont(total_amount,0.9) as _90th_percentile
    ,max(total_amount) as max_amount
    ,stddev_pop(total_amount) as std_amount
FROM taxis
GROUP BY
    date_part('year',tpep_pickup_datetime) || 
        lpad(cast(date_part('week',tpep_pickup_datetime) as varchar),2,'0')
    ,passenger_count
ORDER BY
    yyyyww
    ,passenger_count

Returning data to local variable aggregated_df

pivoted_df = pd.pivot_table(aggregated_df,
               values=['_50th_percentile'],
               index=['yyyyww'],
               columns=['passenger_count'],
               aggfunc='max'
            )
pivoted_df

Let’s summarize what we have accomplished!

To go from Python to SQL, we have used Python to generate SQL dynamically and have embedded our SQL code nicely alongside Python in our Jupyter Notebook programming environment. We also discussed using DuckDB to read a Pandas DF directly. When moving from SQL to Python, DuckDB can quickly and easily convert SQL results back into Pandas DF’s. Now we can mix and match Python and SQL to our heart’s content!

Thank you for reading! Please hop on Twitter and let me know if any of this was helpful or what I can cover next!