Databases SQL, NoSQL, ORMs, REST/GraphQL, JSON/gRPC SQL QL (1974) - - PowerPoint PPT Presentation

SQL, NoSQL, ORMs, REST/GraphQL, JSON/gRPC

Databases

SQL QL (1974) 4)

 Initially, relational databases with query languages based on

mathematical logic

 SQL (Chamberlin, Boyce @ IBM Research)

 Query language accessible to those without formal training in

mathematics or computer programming

 Hugely successful

 DB2, Oracle, SQL Server, PostgreSQL

 Databases support ACID properties

 Atomic (transactions fully completed or not begun at all)  Consistent (system must remain in valid state, errors rolled back)  Isolated (each transaction executes as if it is the only one)  Durable (all changes permanent)

 Then, the Internet and WWW happened….

Portland State University CS 430P/530 Internet, Web & Cloud Systems

WWW WWW

 High volume, high velocity data  Non-uniform, "dirty" data  Can not be handled by traditional relational database  ACID properties not required (and difficult to scale-up to large

amounts of data)

 Motivates move to non-relational databases

 Bigtable, Dynamo, MongoDB, Cassandra, etc.  Eschew the use of SQL to store, query, and retrieve data “NoSQL”

Portland State University CS 430P/530 Internet, Web & Cloud Systems

NoSQL QL

 Large-scale datastores with no standard query language  Often schemaless (e.g. key-value storage of arbitrary types)

 Like a Python dictionary  Akin to static typing (SQL) vs. dynamic typing (NoSQL)

 Limited querying support (i.e. no JOINs)  Often with weak consistency (BASE)

 Basically Available (requests may return failure or a transient inconsistent

state)

 Soft state (system always moving towards consistent state)  Eventual consistency (when input stops, system will reach a consistent

state)

 But, get performance and scale!  Huge variety of implementations tailored to specific application

 http://nosql-database.org/  How to choose? (Take 486/586 + Cloud and Cluster course)

Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL QL vs.

• s. NoSQL

QL

Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL NoSQL (initially) Relational (structured) data Non-relational (unstructured) data Complex querying (JOINs) Simple key-value lookup Schema No schema ACID (Atomic, Consistent, Isolated, Durable) consistency BASE (Basic Availability Soft-state Eventual) consistency

NewS wSQL: QL: Reven enge ge of SQL QL?

 Bolt on SQL features to NoSQL

 Transaction processing (OLTP)  SQL interfaces on top of Hadoop and Spark

 Bolt on NoSQL features to SQL

 Horizontally scalable SQL systems (H-Store, Spanner)

 Get the best of both worlds

 SQL querying and consistency  NoSQL scaling

Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL QL as s th the un e unifyi ifying ng lang nguage? uage?

 Timescale blog

 https://blog.timescale.com/why-sql-beating-nosql-what-this-means-

for-future-of-data-time-series-database-348b777b847a

Portland State University CS 430P/530 Internet, Web & Cloud Systems

http://www.w3schools.com/sql

SQL

SQL QL: : Str truct ucture ured d Qu Quer ery y Lang ngua uage ge

 Standard language for accessing and manipulating databases

 MySQL, Postgres SQL, SQLite, SQL Server, Access, Oracle, Sybase,

DB2, etc.

 Major commands are same between them

 Includes two things

 Data Definition Language (DDL)  Data Manipulation Language (DML)

Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL Data Definition Language

SQL QL DD DDL (Da Data ta De Defini inition) tion)

 Permits databases and database tables to be created or deleted

(among other operations).

 CREATE creates a new database, table, index  DROP : deletes a database, table, index

Portland State University CS 430P/530 Internet, Web & Cloud Systems

CREA EATE TE

 CREATE DATABASE

 Used to create a database

 CREATE TABLE

 Used to create a table within database

Portland State University CS 430P/530 Internet, Web & Cloud Systems

CREATE DATABASE database_name; CREATE DATABASE my_db;

CREATE TABLE table_name( column_name1 data_type, column_name2 data_type, . . . ); CREATE TABLE persons ( P_Id INT, LastName VARCHAR(25), FirstName VARCHAR(25), Address VARCHAR(25), City VARCHAR(15), PRIMARY KEY (P_Id) );

Ba Basi sic c SQL QL Da Dataty tatypes pes

 Note: Data must be clean! (Not easy to guarantee)

Portland State University CS 430P/530 Internet, Web & Cloud Systems

Data type Description Storage Byte Allows whole numbers from 0 to 255 1 byte Integer Allows whole numbers between -32,768 and 32,767 2 bytes Long Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes Single Single precision floating-point. Will handle most decimals 4 bytes Double Double precision floating-point. Will handle most decimals 8 bytes

SQL Data Manipulation Language

Ba Basi sic c fun unctions ctions us used ed in web eb app pps

 CRUD

 Create = INSERT INTO  Read = SELECT  Update = UPDATE  Delete = DELETE

Portland State University CS 430P/530 Internet, Web & Cloud Systems

SEL ELEC ECT

 Used to read data from a database

 Result stored in a table, called the result-set

Portland State University CS 430P/530 Internet, Web & Cloud Systems

P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger

Persons Table SELECT FirstName, LastName FROM Persons; SELECT * FROM Persons; SELECT column_name(s) FROM table_name;

WHER ERE E clause ause

 Add a predicate to limit what is returned

 Text values should be quoted by single quotes or double quotes  Numeric values do not need to be enclosed in quotes

Portland State University CS 430P/530 Internet, Web & Cloud Systems

SELECT column_name(s) FROM table_name WHERE column_name operator value; SELECT * FROM persons WHERE city='Sandnes';

P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger

Persons Table

INSER ERT T INTO

 Used to create new records in a table  Example

Portland State University CS 430P/530 Internet, Web & Cloud Systems

P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger

Persons Table

CREATE DATABASE test; CREATE TABLE persons ( P_Id INT, LastName VARCHAR(25), FirstName VARCHAR(25), Address VARCHAR(25), City VARCHAR(15), PRIMARY KEY (P_Id) ); INSERT INTO persons VALUES (1, 'Hansen', 'Ola', 'Timoteivn10', 'Sandnes'); INSERT INTO persons VALUES (2, 'Svendson', 'Tove', 'Borgvn23','Sandnes'); INSERT INTO persons VALUES (3, 'Pettersen', 'Kari', 'Storgt20', 'Stavanger');

UPDATE TE

 Used to update records in a table

Portland State University CS 430P/530 Internet, Web & Cloud Systems

UPDATE table_name SET column=value, column2=value2,… WHERE some_column=some_value; UPDATE Persons SET Address='Nissestien 67', city='Sandnes' WHERE lastname='Tjessem' AND firstname='Jakob';

DE DELET ETE E st statement ement

 Used to delete records in a table

Portland State University CS 430P/530 Internet, Web & Cloud Systems

DELETE FROM table_name WHERE some_column=some_value; DELETE FROM persons WHERE lastname='Tjessem' AND firstname='Jakob';

Ot Other er comm mmon

• n SQL

QL pr primi mitiv tives es

 Pattern matching

 % wildcard for matching 0 or more characters  _ wildcard for matching exactly 1 character

 Result processing

 LIMIT  ORDER BY / DESC / ASC  DISTINCT

 Table combining

 JOIN (INNER, LEFT, RIGHT, FULL)

 Constraints

 NOT NULL  UNIQUE  PRIMARY KEY  FOREIGN KEY  CHECK (constraint)

Portland State University CS 430P/530 Internet, Web & Cloud Systems

Gu Gues estbo tbook

• k app

pp

 https://bitbucket.org/wuchangfeng/cs430-src  WebDev_Guestbook_v3_nginx_uwsgi

 Simple MVC app in Python/Flask  Models

 sqlite3 model in model_sqlite3.py based on previously

defined interface in Model.py

 sqlite3 uses a file in the file system to store database (see iPhone)

Portland State University CS 430P/530 Internet, Web & Cloud Systems

. ├── Model.py ├── model_pylist.py ├── model_sqlite3.py ├── app.py ├── requirements.txt ├── templates │ ├── layout.html │ └── index.html └── static └── style.css

model_sqlite3.py

 Derived class that implements Model using

sqlite3

Portland State University CS 430P/530 Internet, Web & Cloud Systems

DB_FILE = 'entries.db' # file for our Database class model(Model): def __init__(self): """ Create db on startup if it does not exist """ connection = sqlite3.connect(DB_FILE) cursor = connection.cursor() try: cursor.execute("select count(rowid) from guestbook") except sqlite3.OperationalError: cursor.execute("create table guestbook (name text, email text, signed_on date, message)") cursor.close() def select(self): """ Gets all rows from the database Each row contains: name, email, date, message """ connection = sqlite3.connect(DB_FILE) cursor = connection.cursor() cursor.execute("SELECT * FROM guestbook") return cursor.fetchall() . ├── Model.py ├── model_pylist.py ├── model_sqlite3.py ├── app.py ├── requirements.txt ├── templates │ ├── layout.html │ └── index.html └── static └── style.css

. ├── Model.py ├── model_pylist.py ├── model_sqlite3.py ├── app.py ├── requirements.txt ├── templates │ ├── layout.html │ └── index.html └── static └── style.css

model_sqlite3.py

Portland State University CS 430P/530 Internet, Web & Cloud Systems

def insert(self, name, email, message): """ Inserts entry into database (name, email, date, message) """ params = {'name':name, 'email':email, 'date':date.today(), 'message':message} connection = sqlite3.connect(DB_FILE) cursor = connection.cursor() cursor.execute("insert into guestbook (name, email, signed_on, message) VALUES (:name, :email, :date, :message)", params) connection.commit() cursor.close() return True

Note use of parameterized queries to avoid SQL injection vulnerability

SQL Functions

SQL QL agg ggregat egate e func unctions tions

 SQL aggregate functions: return a single value calculated from values

in a column

 AVG(), COUNT(), MAX(), MIN(), SUM()

Portland State University CS 430P/530 Internet, Web & Cloud Systems

SELECT AVG(column_name) FROM table_name SELECT AVG(UnitPrice) AS UnitAverage FROM Products SELECT ProductName FROM products WHERE UnitPrice>( SELECT AVG(UnitPrice) FROM Products) P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder 1 Jarlsberg 10.45 16 15 2 Mascarpone 32.56 23 3 Gorgonzola 15.67 9 20

Iss ssue ues

 Computation (SQL functions) and data selection (WHERE) on

large-scale, high-velocity data

 Can a database scale to store a non-stop firehose of data?  Can a database handle queries and functions on top of the firehose?  The issues Google faced when building their initial search engine

 Motivates

 Non-standard data storage

 NoSQL, Distributed key-value stores

 Non-standard querying, parallel data stream processing approaches

 Map-Reduce, Hadoop, Spark and other data processing pipelines

 "Data Science"

 Will revisit both when we get to cloud topics

Portland State University CS 430P/530 Internet, Web & Cloud Systems

Object-Relational Mapping (Write Python instead of SQL)

OR ORMs

 Take objects and map them directly to relational database

 SQL taken care of by underlying library

 Sacrifice a little bit of performance and SQL purity for faster

development

 Developers do not have to learn a second language!  Potentially cleaner code  Minimal SQL injection risks  Unify insertion, querying, updating and deleting to/from SQL, NoSQL,

and NewSQL databases

 Will see examples in GCP cloud database APIs

Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL QL CREA EATE TE TABLE BLE (sq sqlit lite3) e3)

Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL QLAlchem Alchemy OR ORM eq equiv uivale alent nt

Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL QL vs.

• s. SQL

QLAlchem Alchemy: : Inser sertio tion

 Insert into DB with SQL (sqlite3)  Insert into DB via ORM

Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL QLAlchem Alchemy y exa xample: ple: Qu Quer erying ying

 SQL (sqlite3)  SQLAlchemy

Portland State University CS 430P/530 Internet, Web & Cloud Systems

Database queries via REST/JSON (Write URLs instead of SQL)

Web APIs, Web EndPoints

Progra

• grammatic

mmatically ally call all into databas atabase e via Web eb APIs Is (En EndP dPoints

• ints)

 Key for modern client-side web frameworks

 Directly interacting with "model"

 Explosive growth

 Protecting an estimated $2.2 billion in assets

 https://www-03.ibm.com/press/us/en/pressrelease/48026.wss

 Each API with 5-6 versions per year (PeachTech)

Portland State University CS 430P/530 Internet, Web & Cloud Systems

Gr Growth wth in Web eb API se security urity iss ssue ues

Portland State University CS 430P/530 Internet, Web & Cloud Systems

Web eb APIs/EndP s/EndPoints

• ints

 2 parts

 API (REST, GraphQL)  Data-exchange format (JSON, gRPC)

Portland State University CS 430P/530 Internet, Web & Cloud Systems

RES EST APIs Is

 Representational State Transfer  Style of web software architecture that simplifies application  Not a standard, but a design pattern

Portland State University CS 430P/530 Internet, Web & Cloud Systems

RES EST APIs Is

 Take all resources for web application (data, files, functions)

 Identify each resource and action on resource via an HTTP method

and URL.

 Method selects action  Send arguments via the HTTP request (e.g. in URL, URL parameters,

• r payload)

Portland State University CS 430P/530 Internet, Web & Cloud Systems

Where (URL) What (App- defined) How (HTTP method) Resources (Data, files, functions)

RES EST toy exa xample ple

 http://foo.com/bar/users  Server foo.com  Database bar  Table users  URL returns table users in database bar in a particular format

(XML, JSON)

 Effectively turn URLs into pre-canned SQL  Common examples

 Twitter, Flickr, Amazon

Portland State University CS 430P/530 Internet, Web & Cloud Systems

RES EST and nd HTTP TP met methods ds

 HTTP request methods indicate the desired action  GET

 Requests a representation of the specified resource.  Use for operations that have NO side-effects (safe operations)  Works with robots and crawlers.

 POST

 Submits data to be processed (e.g., from an HTML form) to the

identified resource. Data is included in the body of the request.

 PUT

 Uploads a representation of the specified resource.

 DELETE

 Deletes the specified resource.

Portland State University CS 430P/530 Internet, Web & Cloud Systems

Iss ssue ues s wi with th RES EST

 URLs into SQL  Problem

 Must provide a fixed mapping between what app wants and what the API

actually implements

 Overfetching

 API returns much more data than user asks for

 Underfetching

 API must be queried multiple times to get all the data user needs  Example: queries to different tables in a backend require multiple API requests

 Slows down DevOps

 Front-end developers must coordinate with backend engineers to get additional API

endpoints defined when needed

Portland State University CS 430P/530 Internet, Web & Cloud Systems

Gr Graph aphQL QL

 Facebook (2015)  REST mapping to SQL query too coarse

 Rigid queries based on URL and a JSON result that must then be parsed  Queries on multiple tables require multiple requests  Results returned as text that must be parsed

 GraphQL

 Language that allows client to specify details of what it wants

 Give front-end programmable control over query

 Specify the structure of how the result data should be returned

 Helps strongly typed client code ingest data

 Akin to giving clients direct SQL-like interface to backend database

Portland State University CS 430P/530 Internet, Web & Cloud Systems

 Example

 Return only the balance, email, and subscriptions columns for a

particular user

 Similar to a custom "SELECT" issued from the client  Reduces payload size of returned results (making it good for mobile)

 Batch multiple queries together in a single request

Portland State University CS 430P/530 Internet, Web & Cloud Systems

getUser(id: "user_123") { balance, email, subscriptions } userA: getUser(id: "user_123") { email } userB: getUser(id: "user_456") { email }

Da Data ta-exchang change e formats mats

 JavaScript Object Notation (JSON)

 De-facto web object data format (versus XML)

 Subset of JavaScript  Minimal, lightweight, text-based syntax  Easy to parse and generate

 Prevalent in most web sites and web APIs, often as part of a REST

architecture

 Designed to enable stateful, real-time communication between browser

and web application

 Often used via AJAX to allow web server to directly modify elements of a page

without refresh

 AJAX = Asynchronous Javascript And XML

 But now it's all AJAJ

Portland State University CS 430P/530 Internet, Web & Cloud Systems

JS JSON ON objects ects

 Objects are unordered containers of key/value pairs similar to

Python dictionaries

 Keys are strings  Values are JSON values  Wrapped in { }

 , separates key/value pairs  : separates keys and values

 Parsed into native language data structures as struct, record,

hashtable, or dictionary

Portland State University CS 430P/530 Internet, Web & Cloud Systems

JS JSON ON Values lues

 Strings

 Sequence of 0 or more Unicode characters wrapped in double quotes

 Numbers

 Integer, Real, Scientific  No octal or hex  No NaN or Infinity

 Booleans

 true, false

 null

 A value that isn't anything

 Objects  Arrays

Portland State University CS 430P/530 Internet, Web & Cloud Systems

JS JSON ON exa xample ple

Portland State University CS 430P/530 Internet, Web & Cloud Systems

{ "firstName": "John", "lastName": "Smith", "address": { "streetAddress": "21 2nd Street", "city": "New York", "state": "NY", "postalCode": 10021 }, "phoneNumbers": [ "212 555-1234", "646 555-4567" ] } Name/Value Pairs Number data type String Array Child properties

RES EST wi with th JS JSON ON

 Common pattern

Portland State University CS 430P/530 Internet, Web & Cloud Systems

import requests response = requests.get('https://api.adviceslip.com/advice') advice_dict = response.json() print(advice_dict["slip"]["advice"])

JS JSON ON iss ssues ues

 Text format

 Binary data must be encoded (Base64)  Space overhead in using text

 Flat format

 Object treated as a string  Must be parsed to obtain original object (e.g. JSON.parse())  Processing overhead in going to/from string and into programming-

language specific data structure

Portland State University CS 430P/530 Internet, Web & Cloud Systems

gR gRPC

 Initially developed by Google as ProtoBufs (protocol buffers)  Support serialization of structured data in an arbitrary (potentially

binary) format

 Derived from FlatBuffer's zero-copy deserialization idea  Minimal processing to go from wire-line format to language-specific

representation

 ProtoBuf generators for C++, Java, C#, Python, etc.  Each protobuf contains an interface description language specifying

format of the stream of bytes transmitted

 Uses HTTP/2 as a transport

 Used throughout Google's infrastructure, but now open-sourced

 Mandatory for certain cloud services that are data-intensive (e.g.

BigQuery)

 When to use…

 https://codeclimate.com/blog/choose-protocol-buffers/

Portland State University CS 430P/530 Internet, Web & Cloud Systems

Cloud SQL AWS RDS (Relational Database Service) Azure SQL Database

SQL databases in the cloud

"Lif ift t and nd Shift" t"

 Move application running on-premises directly into the cloud  Simplest way to take advantage of cloud services  Keeps application largely the same  Can be done in entirety (e.g. application and backend servers moved

to the cloud)

 Can be done as a "hybrid" deployment (e.g. only backend runs in the

cloud and application connects from on-premises)

Portland State University CS 430P/530 Internet, Web & Cloud Systems

Go Google gle Cloud ud SQL QL

 Drop-in replacement for MySQL (or Postgres) relational databases

 No need to purchase hardware or install software  No need reconfigure code  Automatic backups and software updates  Data encrypted in flight and at rest  Accessible only to project (unless external access allowed)  Hosted by a Compute Engine instance you configure

 Security

 No access to database from outside project unless specified

Portland State University CS 430P/530 Internet, Web & Cloud Systems

AWS Rel elational ational Da Database tabase Ser ervice ice

 Similar to Cloud SQL, but with more

• ptions

 Amazon Aurora, MySQL, PostgreSQL,

Oracle, Microsoft SQL Server, Maria DB

 As with Google Cloud SQL, can

customize hardware used for server

Portland State University CS 430P/530 Internet, Web & Cloud Systems

Database Labs #1-3