Party Like it's ANSI 1999: Using SQL from Python Applications

SQL is back! Well it never went away. Relational database systems development is accelerating more so than ever before. To keep up and access the latest features from your application, you have to write SQL directly, bypassing ORMs. But how do you do that without filling your code with SQL strings?

Transcript

1. Party Like it’s ANSI 1999
   1
   

   View Slide

2. I used to work at an Investment Bank
   2
   

   View Slide

3. ...definitely not this!
   3
   

   View Slide

4. ...more like this
   4
   

   View Slide

5. ...specifically
   5
   

   View Slide

6. Hi I’m Huss
   Co-founder & CTO @ 9fin
   Investment Banking Technology
   ● Equities Trading
   ● Bespoke Trader Desk tools
   ● Risk and Control systems
   Aeronautical Engineering
   ● Lots of aeroplane stuff
   9fin.com
   @9finHQ
   6
   @HUSSTECH
   

   View Slide

7. American National Standards Institute
   We’re at SQL: 2016…
   but also, I like puns
   This better be fun….
   7
   Party Like it’s ANSI 1999
   

   View Slide

8. Let’s talk about Databases
   ● Relational DBMS specifically
   ● A long history since the early 1970s
   8
   Berkley Ingress
   Sybase SQL
   Server
   - fork - PostgreSQL
   MS SQL Server
   MySQL
   mSQL
   1970s 1980s 1990s
   PEP 248: DB
   1.0 API (1996)
   

   View Slide

9. Software Archeology: BigCo EE
   ● Silo groups with big databases
   ● Technology functions replicated for each system
   ● System of record and final destination for all data
   ○ So was treated very carefully
   ● Specific SQL developers and DBAs (Database
   Administrators aka wizards)
   9
   Sales Finance Inventory
   

   View Slide

10. Software Archeology: The “fat client”
    ● Direct connection from UI to Database
    ● Tightly coupled
    ○ Roles/Permissions were on the Database
    ○ Database down = application down
    ● Not just a pattern in the enterprise
    ● Web Servers were also multi-tasking
    ○ Hosting, executing app code, database calls
    10
    Application
    

    View Slide

11. Software Archeology: Three Tier
    ● Intermediate layer between UI/Client and Datastore
    ○ Server, Cache, Proxy, another client
    ○ Typically endpoints on enterprise message bus
    or direct HTTP
    ○ Decoupled the data from the application layer
    ● Evolved further today with microservices and mesh
    like layers
    ● Decoupling helped an explosion in dynamic languages
    with rich tooling for web development
    11
    Application
    Server
    

    View Slide

12. The Problem with State
    ● Application code has been living in source control for decades now
    ○ Code is stateless
    ○ Data only passes through it
    ○ Arbitrary progression backwards and forwards through time
    ○ Amazing tooling and support
    ● Source control of a database still unsolved problem
    ○ Database holds code as well as data
    ○ Data is resident in the database
    ○ Points in time are linked to a database state
    ○ Some good solutions which are source control for the
    structure of the database exist
    ● So it’s best to assume nothing of the database and treat it as a
    dumb container? 12
    Application
    nope!
    Source Control
    Source Control
    

    View Slide

13. SQL and Apps Today: Object Relational
    Mapping
    ● Model your entities using an ORM library
    for your application
    ● Takes care of data retrieval and
    persistence
    ● Great for bootstrapping
    ○ Focus on app code not storage
    ○ Strong control of your business
    objects
    ● Rich libraries in many languages
    13
    Application runtime
    Models
    ORM
    Backend
    DB Driver
    

    View Slide

14. SQL and Apps Today: We can do more!
    ● It is a high performance machine...why not use it?
    ○ Make use of advanced data structures
    ■ JSON, XML, Hstore, IP, Geo…
    ○ Do your computation where the data is
    ■ Aggregates, pivots, windowing
    ■ Save bandwidth
    ○ Enforce business rules
    ■ Guarantee the integrity of your data
    ■ Asynchronously act on events
    ● How can we use all of this in a simple and easy way from our Python code?
    ● How do we solve the source control problem?
    14
    

    View Slide

15. SQL and Apps Today: JSON Example
    15
    {
    "forms": [
    {
    "url": "...api/v2/pokemon-form/25/",
    "name": "pikachu"
    }
    ],
    "abilities": [
    {
    "slot": 3,
    "is_hidden": true,
    "ability": {
    "url": ".../api/v2/ability/31/",
    "name": "lightning-rod"
    }
    },
    ...
    SELECT
    jsonb_array_elements(
    data->'abilities'
    )
    ->'ability'
    ->>'name'
    FROM pokemon
    ability |
    --------------|
    lightning-rod |
    static |
    

    View Slide

16. SQLpy
    ● Write SQL *next* to your
    application code
    ● Prepares a ready to execute
    functools.partial object
    for you
    ● No query strings dotted all over
    source files
    ● A light wrapper around your
    DB2.0 API
    ● Genesis from YeSQL
    16
    In source control!
    queries.sql
    Application runtime
    Whatever you want
    DB Driver
    Source Control
    

    View Slide

17. That awkward moment when you need SQL
    17
    # set up 2 query objects one for subquery one for window function
    subquery =
    db.session.query(func.DATE(Payment.payment_date).label('payme
    nt_date'),
    func.sum(Payment.amount).label('amount')
    ).group_by(func.DATE(Payment.payment_date))\
    .order_by(func.DATE(Payment.payment_date))\
    .subquery('s')
    window = db.session.query(subquery.c.payment_date,
    subquery.c.amount,
    func.sum(subquery.c.amount).over(
    order_by=subquery.c.payment_date)
    ).select_entity_from(subquery).order_by(subquery.c.payment_date)
    ● A nested data question
    ● 10+ (readable) lines
    ● This is quite nice syntax
    actually
    ● But the semantics are
    halfway between Python
    object orientation and SQL
    declarative style
    How to do the same in SQL?
    

    View Slide

18. That awkward moment when you need SQL
    18
    How to do the same in SQL?
    SELECT payment_date, amount, sum(amount) OVER (ORDER BY payment_date)
    FROM ( SELECT CAST(payment_date AS DATE) AS payment_date, SUM(amount)
    AS amount
    FROM payment
    GROUP BY CAST(payment_date AS DATE)) p
    ORDER BY payment_date;
    from itertools import accumulate
    # List of dates and amount pairs
    payments = [(payment_date, amount)]
    for (pay_dt, amt), cum_pay in zip(payments, accumulate(payments)):
    print(f'{pay_dt} {amt} {cum_pay}')
    Easier in Python!
    

    View Slide

19. SQLpy + Models
    ● Still like linking behaviours and actions via
    objects?
    ● Use the DAO pattern - define the models
    how you want
    ○ Add extra methods to serialize or
    validate eg. Marshmallow
    19
    class Hello:
    def __init__(self, queryfunc, cur, args):
    self._query = queryfunc
    self._result = self.result(cur, args)
    @property
    def result(self):
    return self._result
    @result.setter
    def result(self, cur, args):
    self._result = self._query(cur, args)
    def refresh(self, cur, args):
    return self._query(cur, args)
    def jsonout(self):
    return json.dumps(self.result)
    

    View Slide

20. SQLpy + Composition
    ● Constructing SQL queries is legitimately dangerous!
    ● How to do this in SQLpy, but not destroy the valid SQL syntax in queries file?
    ● Answer: BUILT SQL
    ○ Clauses added based on parameters passed to the wrapping function
    ○ Transparent and lightweight
    ● Query still passes through the DB2 .0 API library sanitiser for safe execution
    20
    

    View Slide

21. SQLpy + Composition
    21
    -- name: built_sql_query$
    SELECT * FROM messages
    WHERE 1=1
    AND id = %(id_low)s
    OR id = %(id_high)s
    AND message = %(msg)s;
    sql = sqlpy.Queries('queries.sql')
    ....
    args = {'id_low': 1}
    results = sql.BUILT_SQL_QUERY(cur, args)
    SELECT * FROM messages
    WHERE 1=1
    AND id = 1;
    id message next_message
    1 hello there
    2 SQLpy NULL
    3 databases rule!
    4 hello friend
    

    View Slide

22. Beyond web apps: Python taking over!
    ● Multitude of tools have made Python the interface language of choice
    ● But the glue has been clunky bash scripts, python scripts, CSV dumps and loads
    ● Why not use use Python all the way through and really integrate your systems and
    pipelines with one universal language?
    22
    Database
    Object
    Store
    --> Log Streaming -->
    Distributed Computation
    

    View Slide

23. Data Science
    ● Moving data from A to B is a very frequent task
    ○ Data cleaning and transformation takes up a much higher portion of our time
    than we’d like :(
    ○ Any efficiency gain on the ingestion and processing pipeline is a win
    ● ORMs sometimes do not fit very well here as there are no “entities” to model
    ○ We’re experimenting: cutting, pivoting, adding and dropping attributes of
    interest on the fly
    ● Use SQLpy to have your data modelling language next to your computation language
    23
    

    View Slide

24. Data Science - SQL is coming for you
    ● Next generation datastores are cloud native, distributed and big in scale
    ● They’re not traditionally relational
    ○ But many are converging on SQL as their data modelling language
    ○ Not full SQL standard, but most common operations present
    ○ No traditional relational Database ORM support
    ● All provide Python libraries that expose their query language interfaces
    ○ kSQL (kafka)
    ○ sparkSQL (Apache Spark)
    ○ BigQuery (Google Cloud)
    ○ Athena (AWS S3)
    ● Let’s modify SQLpy to work with these too
    24
    

    View Slide

25. Google BigQuery API with SQL
    ● Since SQLpy has no opinion on the backend datastore interface mechanism, it just
    expects a DB 2.0 API compliant cursor object
    ○ Make an object defining execute, fetchone, fetchall etc… methods
    25
    https://github.com/HUSSTECH/talks-code/tree/master/party-like-its-ansi-1999
    

    View Slide

26. sqlpy.readthedocs.io
    ● github.com/9fin/sqlpy (MIT)
    ● Made for PostgreSQL first
    ○ Other DBs working in theory, but not
    tested!
    ● Still ○ ...so critical comments and other
    contributions welcome
    v0.3.0 stable today!
    (develop-0-3-0 branch)
    26
    /9fin/sqlpy
    

    View Slide

27. Thank You!
    References and Credits [slide#]
    [3] image: By Own Oil Industry News - Own Own work, Public Domain,
    https://commons.wikimedia.org/w/index.php?curid=8266714
    [4] image: CC0 license
    [5] image: An unnamed colleague
    [8] Database timeline based on: https://hpi.de/naumann/projects/rdbms-genealogy.html
    [15] PokeAPI for json data: https://pokeapi.co/docsv2/
    [24] ksql: https://www.confluent.io/product/ksql/
    [24] SparkSQL: https://spark.apache.org/sql/
    [24] BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators
    [24] Athena: https://aws.amazon.com/athena/
    [25] Google BigQuery sample applciation: https://cloud.google.com/bigquery/create-simple-app-api
    27
    

    View Slide