SQL is Coming Home

Another in the SQLpy and SQL series. This is a shorter 5min format for the London Hack&Tell Meetup.

Transcript

1. Putting SQL inside your code or The art of transferable skills aka - Party Like it’s ANSI 1999 aka - SQL is Back, this time it’s Relational aka - SQL is Coming Home 1
2. Hi I’m Huss Co-founder & CTO @ 9fin 9fin.com @9finHQ 2 @HUSSTECH
3. Audience Participation: Part 1 Who has done (literally) any SQL before? Who feels like their mastery of SQL is that of Michael Stonebraker himself? 3
4. Audience Participation: Part 2 Who has done anything “data-sciencey” recently? Who has done that using Python as one of their tools? 4
5. Audience Participation: Part 1 - Results! Who has done (literally) any SQL before? Who feels like their mastery of SQL is that of Michael Stonebraker himself? 5 Select * from I AM CODD
6. Audience Participation: Part 2 - Results! Who has done anything “data-sciencey” recently? Who has done that using Python as one of their tools? 6
7. SQL and Apps Today: Object Relational Mapping • Model your entities using an ORM library for your application ◦ SQLAlchemy (Python) ◦ ActiveRecord (Rails) • Takes care of data retrieval and persistence • Great, all done...nothing more to do here? 7 Application runtime Models ORM Backend DB Driver
8. SQL and Apps Today: Object Relational Mapping • Great, all done...nothing more to do here? ◦ No! • There will be a point in your life, where you need to break out and write som raw SQL strings ◦ Not long passes before we have a growing number of string literals in our code 8 Application runtime Models ORM Backend DB Driver
9. 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 DB Driver • Genesis from YeSQL 9 In source control! queries.sql Application runtime Whatever you want DB Driver Source Control
10. SQLpy + Composition 10 -- 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 Query still passes through the DB2 .0 API driver sanitiser for safe execution
11. SQLpy: What, Why, How? • This was my first time writing a large web application ◦ Naive enough to (basically) ignore convention of an ORM ◦ Just starting to get enough Python experience to be a danger to myself and others ◦ I knew SQL, I liked SQL….I wanted SQL ◦ ...so I went looking and found, YeSQL….and then started Pythoning • I just made something which helped me at the start ◦ As I found limitations and wanted it to do more, it developed further • Using SQL directly is good in many ways ◦ It is a high performance machine...why not use it? ◦ Make use of advanced data structures ◦ Do your computation where the data is ◦ Enforce business rules 11 Thi t ra f le s il r
12. Data Science: 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? 12 Database Object Store --> Log Streaming --> Distributed Computation
13. Data Science: If you can’t beat em’ • Next generation datastores are cloud native, distributed and big in scale • Are converging on SQL as their data modelling language, BUT No traditional relational Database ORM support • All provide Python libraries that expose their SQL language interfaces ◦ kSQL (kafka), sparkSQL (Apache Spark), BigQuery (Google Cloud), Athena (AWS S3) 13 SQL SQL SQL SQL SQL
14. Example: 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 14 https://github.com/HUSSTECH/talks-code/tree/master/party-like-its-ansi-1999
15. sqlpy.readthedocs.io • github.com/9fin/sqlpy (MIT) • Made for PostgreSQL first ◦ Other DBs working in theory, but not tested! • Still <v1.0 ◦ ...so critical comments and other contributions welcome 15 /9fin/sqlpy
16. Thank You! References and Credits [slide#] [13] ksql: https://www.confluent.io/product/ksql/ [13] SparkSQL: https://spark.apache.org/sql/ [13] BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators [13] Athena: https://aws.amazon.com/athena/ [14] Google BigQuery sample applciation: https://cloud.google.com/bigquery/create-simple-app-api Emojis - https://clipart.info/emojipngclipart 16