CS 744: SCOPE Shivaram Venkataraman Fall 2020 ADMINISTRIVIA - - PowerPoint PPT Presentation

CS 744: SCOPE

Shivaram Venkataraman Fall 2020

Hello!

ADMINISTRIVIA

• Assignment grades this week
• Midterm details on Piazza
• Course Project Proposal Submission

→

next

Thursday

→

Single PDF file

I

photo

ppf

↳

Hot CRP

↳ Peer

Anonymous

ppf upload

don't include your

Only

include

HotCRP

itself

Scalable Storage Systems Datacenter Architecture Resource Management Computational Engines Machine Learning SQL Streaming Graph Applications

Pytoiphipep

rear

upper

←I ✓

→ MapReduce

Ray

spark

SQL: STRUCTURED QUERY LANGUAGE

I

language

query

database

DATABASE SYSTEMS

Sou

÷:

: ..

• '

OLAP

↳

• m
• .
• O LTP

t

Transaction

processing

Airline

• reservation

PROCEDURAL VS. RELATIONAL

SELECT COUNT(*) FROM “users” WHERE age < 21 lines = sc.textFile(“users") csv = lines.map(x => x.split(‘,’)) young = csv.filter(x => x(1) < 21) println(young.count())

artie

schema

tendered

\

data great:b:! !

• ' "

Esv

)

← Men

. .

• an age

Ekin:&

:c.

!÷÷.int

'

÷

:*

easy

ftp.ograrre

"

SCOPE

SELECT query, COUNT(*) AS count FROM "search.log" USING LogExtractor GROUP BY query HAVING count > 1000 ORDER BY count DESC;

• Submit

→

r

←

to

%¥

hang

÷.

Motl

SCOPE OPERATORS

Input reading: What is different? EXTRACT column[:<type>] [, ...] FROM <input_stream(s) > USING <Extractor> [(args)] [HAVING <predicate>]

powiat

x RDD

A

① asthma

information

?

us

so

• # - filenames

X

class

• r

② pluggable

function

csr Extractor

& furring

geqrad-wgv.in?:::M:.ev::;.:ia

:p.com?l

SQL OPERATORS

Select – read rows that satisfy some predicate Join – Equijoin with support for Inner and Outer join GroupBy – Group by some column OrderBy – Sorting the output Aggregations – COUNT, SUM, MAX etc.

]

Yay

! these

• perators

→

A large

• r

→

muser

analytics

• perations

LANGUAGE INTEGRATION

R1 = SELECT A+C AS ac, B.Trim() AS B1 FROM R WHERE StringOccurs(C, “xyz”) > 2 #CS public static int StringOccurs(string str, string ptrn){ int cnt=0; int pos=-1; while (pos+1 < str.Length) { pos = str.IndexOf(ptrn, pos+1); if (pos < 0) break; cnt++; } return cnt; } #ENDCS C#

"

#

Trim

from

C#

stdtib

I

↳

Custom

function

C

#

compiler

User

• defined

functions

uDFs

MAPREDUCE-LIKE?

Process Reduce Combine

COMBINE S1 WITH S2 ON S1.A==S2.A AND S1.B==S2.B AND S1.C==S2.C USING MultiSetDifference PRODUCE A, B, C

→

map

• perator Lone UDF

inotnutpa

Rpf!Yet)

→

reduce huoperator→ongroy#

→Rxwsety

• l

pparciismediw;

← equi

• join

←

#← www.F#ihon

• 1. Commutative?

\,

produce

many

columns

Wk if

↳

combine

be

multiple

times

Sl comb 52 152 gaff

EXECUTION: COMPILER

SELECT query, COUNT() AS count FROM "search.log" USING LogExtractor GROUP BY query HAVING count > 1000 ORDER BY count DESC; Check syntax, resolve names Checks if columns have been defined Result: Internal parse tree

• ⇐
• I

←

←

2

=

• ÷

↳ smiter

.

• n

compiler

seamy J

OPTIMIZER

Rewrite the query expression à lowest cost Examples: Removing unnecessary columns Pushing down selection predicates Pre-aggregating Also need to reason about partitioning (See VLDBJ paper)

postman

:

chunk

• cost - based

every optimizer

→

itqie.gr?z

Quite

←

• nly

query

columns

't

)

↳ combiner similar

↳ filtering

before

y

grouping

I

71000 C

L

7

RUNTIME OPTIMIZATIONS

Hierarchical aggregation Locality-sensitive task placement Grouping heuristics?

Mmm

;÷g: dnt "

m!EodEEuy!µ

not

all

have

bw

⇒ Aff

within

→

links

agg Idiom

racks

→

similar

to

spark IMR

they

also

do

|

this,fas;

intermediate

¥

[

*

vague

in

the

paper

↳ Default

FI÷¥E¥

automatically

set

m

partitions)

*

after

group BT l )

↳ binary

SUMMARY, TAKEAWAYS

Relational API

• Enables rich space of optimizations
• Easy to use, integration with C#

Scope Execution

• Compiler to check for errors, generate DAG
• Optimizer to accelerate queries (static + dynamic)

Precursor to systems like SparkSQL

→

Schema .

↳

DISCUSSION

https://forms.gle/hL8VJ6uSG7Lzm164A

Consider you have a column-oriented data layout on your storage system (Example below). What are some reasons that a SCOPE query might be faster than running equivalent MR program?

http://dbmsmusings.blogspot.com/2017/10/apache-arrow-vs-parquet-and-orc-do-we.html

£

Apache

parquet

qs

notion of

Robin

• ffsets

Extractor

wk

forage

→

EITI

5

7

8

• se

D

↳

• edpmfofihow

→ Pre -filtering

→ Ogletree

via

is easier

touches

single

column

in

the

this

MN

extractor

this

efficient

well

Does SCOPE-like Optimizer help ML workloads? Consider the code in your

• Assignment2. What parts of your code would benefit and what parts would not?

Colum

filtering

feature

extraction !

workloads

• r
• ther
• ptimization

,

µ,,µ,

⇒ a.adieu

,

yn!

ag#

No

details

about

caching intermediate

• utputs

Hash →

• ptimizer

" ?

dgjfjkn.gg

Dort merge

join

NEXT STEPS

Next class: Elastic Data Warehousing with SnowFlake Project proposals due tomorrow! See Piazza! Midterm coming up!

÷

"

÷:*