Transcript Not a data warehouse

Using Cloud Data
Warehousing to Analyze
Structured and SemiStructured data sets
Kevin Bair
Solution Architect
[email protected]
Topics this presentation will cover
1. Cloud DW Architecture
2. ETL / Data Pipeline Architecture
3. Analytics on Semi-Structured Data
4. “Instant” Datamarts without replicating TB of data
5. Analyzing Structured with Semi-Structured Data
2
Introducing Snowflake: An experienced team of data
experts with a vision to reinvent the data warehouse
Bob Muglia
Benoit Dageville, PhD
Marcin Zukowski, PhD
Thierry Cruanes, PhD
CEO
CTO & Founder
Founder & VP of Engineering
Founder Architect
Former President of
Lead architect of Oracle parallel
Inventor of vectorized query
Leading expert in query
Microsoft’s Server and Tools
execution and a key
execution in databases
optimization and parallel
Business
manageability architect
execution at Oracle
3
Today’s data: big, complex, moving to cloud
Of workloads will
be processed In
cloud data centers
(Cisco)
Surge in cloud
spending and
supporting
technology
(IDC)
Data in the cloud today is
expected to grow in the
next two years.
(Gigaom)
4
Structured data and SemiStructured data
•
Transactional data
•
Machine-generated
•
Relational
•
Non-relational
•
Fixed schema
•
Varying schema
•
OLTP / OLAP
•
Most common in cloud
environments
What does Semi Structured
mean?
• Data that may be of any type
• Data that is incomplete
• Structure that can rapidly and unpredictably
change
• Usually Self Describing
• Examples
• XML
• AVRO
• JSON
XML Example
<?xml version="1.0" encoding="UTF-8"?>
<breakfast_menu>
<food>
<name>Belgian Waffles</name>
<price>$5.95</price>
<description>Two of our famous Belgian Waffles with plenty of real maple
syrup</description>
<calories>650</calories>
</food>
<food>
<name>Strawberry Belgian Waffles</name>
<price>$7.95</price>
<description>Light Belgian waffles covered with strawberries and whipped
cream</description>
<calories>900</calories>
</food>
<food>
<name>Berry-Berry Belgian Waffles</name>
<price>$8.95</price>
<description>Light Belgian waffles covered with an assortment of fresh berries and
whipped cream</description>
<calories>900</calories>
</food>
</breakfast_menu>
JSON Example
{
"custkey": "450002",
"useragent": {
"devicetype": "pc",
"experience": "browser",
"platform": "windows"
},
"pagetype": "home",
"productline": "none",
"customerprofile": {
"age": 20,
"gender": "male",
"customerinterests": [
"movies",
"fashion",
"music"
]
}
}
Avro Example
Schema
Data
}
JSON
}
Binary
Why is this so hard for a
traditional Relational DBMS?
• Pre-defined Schema
• Store in Character Large Object (CLOB) data
type
• Inefficient to Query
• Constantly Changing
Current architectures can’t keep up
Data Warehousing
• Complex: manage hardware, data
distribution, indexes, …
• Limited elasticity: forklift upgrades,
data redistribution, downtime
• Costly: overprovisioning, significant
care & feeding
Hadoop
• Complex: specialized skills, new tools
• Limited elasticity: data
redistribution, resource contention
• Not a data warehouse: batch-
oriented, limited optimization,
incomplete security
11
Data Pipeline / Data Lake Architecture – “ETL”
Source
Website
Logs
Operational
Systems
External
Providers
Stream
Data
Stage
Data
Lake
Stage
EDW
S3
Hadoop
S3
MPP
• 10TB
• 30 TB
• 5 TB
• Summary
• 10 TB Disk
One System for all Business Data
HDFS
Structured
Storage
Data Sink
Map-Reduce Jobs
z
Structured data
Apple
101.12
250
FIH-2316
Pear
56.22
202
IHO-6912
Orange
98.21
600
WHQ-6090
Semi-structured data
{
"firstName": "John",
"lastName": "Smith",
"height_cm": 167.64,
"address": {
"streetAddress": "21 2nd
Street",
"city": "New York",
"state": "NY",
"postalCode": "10021-3100"
},
Relational
Databases
Other Systems
Snowflake
 Multiple Systems
 One System
 Specialized Skillset
 One Common Skillset
 Slower/More Costly Data Conversion
 Faster/Less Costly Data Conversion
 For both Structured and SemiStructured Business Data
How have other Big Data / DW
vendors approached this?
Microsoft - SQL Server doesn't yet accommodate JSON queries, so instead the company
announced Azure DocumentDB, a native document DBaaS (database as a service) for the Azure
cloud (http://azure.microsoft.com/en-us/documentation/services/documentdb/)
Oracle Exadata - Oracle Exadata X5 has many new software capabilities, including faster pure
columnar flash caching, database snapshots, flash cache resource management, near-instant
server death detection, I/O latency capping, and offload of JSON and XML analytics
(https://www.oracle.com/corporate/pressrelease/data-center-012115.html)
IBM Neteeza - You can use the Jaql Netezza® module to read from or write to Netezza tables.
(www01.ibm.com/support/knowledgecenter/SSPT3X_3.0.0/com.ibm.swg.im.infosphere.biginsights.an
alyze.doc/doc/r0057926.html)
Postgres/Redshift - We recommend using JSON sparingly. JSON is not a good choice for storing
larger datasets because, by storing disparate data in a single column, JSON does not leverage
Amazon Redshift’s column store architecture.
(http://docs.aws.amazon.com/redshift/latest/dg/json-functions.html)
Hadoop – Hive and/or Map Reduce, somewhat vendor specific
14
Relational Processing of
Semi-Structured Data
1. Variant data type compresses storage of semistructured data
2. Data is analyzed during load to discern repetitive
attributes within the hierarchy
3. Repetitive attributes are columnar compressed
and statistics are collected for relational query
optimization
4. SQL extensions enable relational queries against
both semi-structured and structured data
15
Why Support Semi-structured
data via SQL?
• Integrate with existing data
• Reduced administrative costs
• Improved security
• Transaction management
• Better performance
• Better resource allocation
• Increased developer productivity
• SQL is proven model for performing queries,
especially joins.
Requirements for a Cloud-based Big
Data / Data Warehouse Platform
• No Contention (Writers can’t block readers)
• Continuous loading of data without “Windows”
• Compress, and don’t duplicate the data
• Segment the data (Datamarts) without replicating
• Ability to analyzed structured and Semi-structured
data, together, at Volume (TB-PB) using SQL
• Reduce Complexity, easy to manage and develop
with
• ELT vs. ETL, allowing processing to happen closer
to the data
• Security, encrypt all data at rest
Architectural Evolution of the Data
Warehouse
Scale Up
(SMP – Single Server)
RDBMS
Software
Storage
Architectural Evolution of the Data
Warehouse
Scale Up
(SMP – Single Server)
Scale Out
(MPP / Hadoop Cluster)
Scale Up, Out or Down
(Elastic / Cloud)
Optimizer
Optimizer
Metadata / Schema
Metadata / Schema
Optimizer
Leader Node
Metadata / Schema
Query
Engine
Query Engine
Storage
Query
Engine
Query
Engine
Query
Engine
Storage
1/X
Storage
2/X
Storage
3/X
Query
Engine
Query
Engine
Query
Engine
Storage
…..
Data Node(s)
1)
2)
3)
4)
Partition Keys / OLAP
Skew
Redundancy
Query Inefficiency
1)
2)
3)
4)
No Partitions
Multiple Clusters
Only data needed is accessed
Query Efficient
Data Warehousing
Cloud Service
ETL &
Data Loading
Database is separate
from Virtual Warehouse
One Virtual Warehouse,
multiple Databases
Finance
Users
S
Marketing
Users
Virtual
Warehouse
Virtual
Warehouse
One Database, multiple
Virtual Warehouses
Databases
Virtual Warehouse scales
independently from Database
Data loading does not
Impact query performance
Virtual
Warehouse
Test/Dev
Users
Virtual
Warehouse
S
Virtual
Warehouse
Virtual
Warehouse
Sales
Users
Biz Dev
User
20
Data Pipeline / Snowflake Architecture – “ELT”
Source
Website Logs
Operational
Systems
External
Providers
Stream Data
Stage
EDW
S3
Snowflake
• 10TB
• 2 TB Disk
Amazon Cloud Data Pipeline Architecture
Stream Data
“JSON”
“ Application”
IAM
EDW
Amazon
Kinesis
S3 buckets
Amazon SQS
“ Long Term Storage Files”
“ Notification”
Amazon SNS
Amazon Glacier
22
Amazon Cloud Data Pipeline Architecture (Near Real-time)
Stream Data
“JSON”
“ Application”
IAM
EDW
Amazon
Kinesis
S3 buckets
“ Near Real-time”
Amazon SQS
“ Long Term Storage Files”
Storm /
Spark
“ Notification”
Amazon SNS
Amazon Glacier
23
Typical customer environment
Data sources
Datamarts
OLTP
databa
ses
Enterpri
se
applicati
ons
EDW
ETL
Thirdparty
Web
applicati
ons
Other
Hadoop
BI / Analytics
Demo Time!
Demo Scenarios
• Clickstream Analysis (load JSON, multi-table insert)
• Which Product Category is most clicked on?
• Which Product line does the customer self identify as
having the most interest in?
• Twitter Feed (Join Structured and Semi-Structured)
• From our twitter campaign, is there a correlation
between twitter volume and sales?
Clickstream Example
{
"custkey": "450002",
"useragent": {
"devicetype": "pc",
"experience": "browser",
"platform": "windows"
},
"pagetype": "home",
"productline": "none",
"customerprofile": {
"age": 20,
"gender": "male",
"customerinterests": [
"movies",
"fashion",
"music"
]
}
}
What makes Snowflake unique for
handling Semi-Structured Data?
•
•
•
•
•
•
•
•
•
•
•
•
Compression
Encryption / Role Based Authentication
Shredding
History/Results
Clone
Time Travel
Flatten
Regexp
No Contention
No Tuning
Infinitely scalable
SQL based with extremely high performance
Where to Get More Info
• Visit us: http://www.snowflake.net/
• Email us:
• Sales: [email protected]
• General: [email protected]
• Q&A
THANK YOU!
Functions
•
•
•
•
•
•
•
REGEXP
REGEXP_COUNT
REGEXP_INSTR
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_SUBSTR
RLIKE
•
•
•
•
•
•
•
•
•
•
•
CHECK_JSON
PARSE_JSON
OBJECT_CONSTRUCT
OBJECT_INSERT
GET
GET_PATH
AS_type
IS_type
IS_NULL_VALUE
TO_JSON
TYPEOF
•
•
•
•
•
•
•
•
•
•
•
ARRAYAGG, ARRAY_AGG
ARRAY_APPEND
ARRAY_CAT
ARRAY_COMPACT
ARRAY_SIZE
ARRAY_CONSTRUCT
ARRAY_CONSTRUCT_COMPACT
ARRAY_INSERT
ARRAY_PREPEND
ARRAY_SLICE
ARRAY_TO_STRING
Parsing JSON using Snowflake SQL
(After loading JSON file into Snowflake table)
Parsing JSON using SQL from a VARIANT column in a Snowflake table
SELECT 'The First Person is '||fullrow:fullName||' '||
'He is '||fullrow:age||' years of age.'||' '||
'His children are: '
||fullrow:children[0].name||' Who is a '||
fullrow:children[0].gender||' and is '||
fullrow:children[0].age||' year(s) old '
||fullrow:children[1].name||' Who is a '||
fullrow:children[1].gender||' and is '||
fullrow:children[1].age||' year(s) old ' Result
FROM json_data_table
WHERE fullrow:fullName = 'John Doe';
FLATTEN() Function
and its Pseudo-columns
FLATTEN() Converts a repeated field into a set of rows.
FLATTEN() Returns Pseudo-columns in addition to the data result.
SELECT S.fullrow:fullName, t.value:name, t.value:age, t.SEQ, t.KEY, t.PATH, t.INDEX, t.VALUE
FROM json_data_table AS S, TABLE(FLATTEN(S.fullrow,'children')) t;
For maps or
objects. It contains
the key to the
exploded value
A unique sequence #
related to the input
expression
Path expression of
the exploded value in
the input expression
For arrays, It contains
the index in the array of
the exploded value
The expression
contained in the
collection
FLATTEN() in Snowflake SQL
(Removing one level of nesting)
FLATTEN() Converts a repeated field into a set of rows:
SELECT S.fullrow:fullName, t.value:name, t.value:age
FROM json_data_table as S, TABLE(FLATTEN(S.fullrow,'children')) t
WHERE s.fullrow:fullName = 'Mike Jones’
AND t.value:age::integer > 6 ;
FLATTEN(): Two levels of
un-nesting
Output
+---------------+-----+--------+-------------------+------------------------+
|
name
| age | gender | citiesLived.place | citiesLived.yearsLived |
+---------------+-----+--------+-------------------+------------------------+
| Mike Jones
| 35 | Male
| Los Angeles
|
1989 |
| Mike Jones
| 35 | Male
| Los Angeles
|
1993 |
| Mike Jones
| 35 | Male
| Los Angeles
|
1998 |
| Mike Jones
| 35 | Male
| Los Angeles
|
2002 |
| Mike Jones
| 35 | Male
| Washington DC
|
1990 |
| Mike Jones
| 35 | Male
| Washington DC
|
1993 |
| Mike Jones
| 35 | Male
| Washington DC
|
1998 |
| Mike Jones
| 35 | Male
| Washington DC
|
2008 |
| Mike Jones
| 35 | Male
| Portland
|
1993 |
| Mike Jones
| 35 | Male
| Portland
|
1998 |
| Mike Jones
| 35 | Male
| Portland
|
2003 |
| Mike Jones
| 35 | Male
| Portland
|
2005 |
| Mike Jones
| 35 | Male
| Austin
|
1973 |
| Mike Jones
| 35 | Male
| Austin
|
1998 |
| Mike Jones
| 35 | Male
| Austin
|
2001 |
| Mike Jones
| 35 | Male
| Austin
|
2005 |
FLATTEN() in Snowflake SQL
(Removing two levels of nesting)
Getting cities Mike Jones lived and when
TABLE (Snowflake syntax)
SELECT
p.fullrow:fullName::varchar as name,
p.fullrow:age::int as age,
p.fullrow:gender::varchar as gender,
cl.value:place::varchar as city,
yl.value::int as year
FROM json_data_table p,
TABLE(FLATTEN(p.fullrow,'citiesLived')) cl,
TABLE(FLATTEN(cl.value:yearsLived,'')) yl
WHERE name = 'Mike Jones';
LATERAL (ANSI syntax, also supported)
SELECT
p.fullrow:fullName::varchar as name,
p.fullrow:age::int as age,
p.fullrow:gender::varchar as gender,
cl.value:place::varchar as city,
yl.value::int as year
FROM json_data_table p,
LATERAL FLATTEN(p.fullrow,'citiesLived') cl,
LATERAL FLATTEN(cl.value:yearsLived,'') yl
WHERE name = 'Mike Jones';
Output
+---------------+-----+--------+-------------------+------------------------+
|
name
| age | gender | citiesLived.place | citiesLived.yearsLived |
+---------------+-----+--------+-------------------+------------------------+
| Mike Jones
| 35 | Male
| Los Angeles
|
1989 |
| Mike Jones
| 35 | Male
| Los Angeles
|
1993 |
| Mike Jones
| 35 | Male
| Los Angeles
|
1998 |
| Mike Jones
| 35 | Male
| Los Angeles
|
2002 |
| Mike Jones
| 35 | Male
| Washington DC
|
1990 |
| Mike Jones
| 35 | Male
| Washington DC
|
1993 |
| Mike Jones
| 35 | Male
| Washington DC
|
1998 |
| Mike Jones
| 35 | Male
| Washington DC
|
2008 |
| Mike Jones
| 35 | Male
| Portland
|
1993 |
| Mike Jones
| 35 | Male
| Portland
|
1998 |
| Mike Jones
| 35 | Male
| Portland
|
2003 |
| Mike Jones
| 35 | Male
| Portland
|
2005 |
| Mike Jones
| 35 | Male
| Austin
|
1973 |
| Mike Jones
| 35 | Male
| Austin
|
1998 |
| Mike Jones
| 35 | Male
| Austin
|
2001 |
| Mike Jones
| 35 | Male
| Austin
|
2005 |
Parsing JSON using Snowflake SQL
(Without loading the JSON file into a Snowflake table)
Parsing JSON using SQL directly from the file without loading into Snowflake
SELECT 'The First Person is '||
S.$1:fullName||' '||
'He is '||S.$1:age||' years of age.'||' '||
'His children are: '||S.$1:children[0].name||' Who is a
'||S.$1:children[0].gender||' and is '||S.$1:children[0].age||' year(s) old '
FROM @~/json/json_sample_data (FILE_FORMAT => 'json') as S
WHERE S.$1:fullName = 'John Doe';
Parsing JSON Records:
PARSE_JSON
Interprets an input string as a JSON document, producing a VARIANT value
SELECT
FROM
WHERE
s.fullrow:fullName Parent, c.value Children_Object,
c.value:name Child_Name, c.value:age Child_Age
json_data_table AS S,
TABLE(FLATTEN(S.fullrow,'children')) c
PARSE_JSON(c.value:age) > 8;
Parsing JSON Records:
CHECK_JSON
Valid JSON will produce NULL
SELECT CHECK_JSON('{"age": "15",
"gender": "Male",
"name": "John"}') ;
Valid JSON
Invalid JSON will produce error message
SELECT CHECK_JSON('{"age": "15",
"gender": "Male",
"name "John" ') ;
Missing :
Invalid JSON will produce error message
SELECT CHECK_JSON('{"age": "15",
"gender": "Male",
"name": "John" ') ;
Missing }
Parsing JSON Records:
CHECK_JSON
Validate JSON records in the S3 file before loading it. Use SELECT with CSV file format
SELECT S.$1, CHECK_JSON(S.$1)
FROM @~/json/json_sample_data (FILE_FORMAT => 'CSV') AS S ;
Missing matching ]
Missing : before [
Missing Attribute Value
Validate JSON records in the S3 file before loading it. Use COPY with JSON file format
COPY INTO json_data_table
FROM @~/json/json_sample_data.gz
FILE_FORMAT = 'JSON' VALIDATION_MODE ='RETURN_ERRORS';
Back up
Learn more at snowflake.net
Snowflake Architecture
User Interface
ODBC Driver
JDBC Driver
Optimization
Query Mgmt
Web UI
Cloud Services
Virtual Warehouse
Processing
EC2
Database Storage
S3
Customer Service
Data
Warehouse Mgmt
Financial Analysts
Sales
Security
Quality Control
Marketing
Metadata
Loading
Materials
Cloud Infrastructure
Amazon AWS
43
Snowflake Architecture
User Interface
ODBC Driver
Compute
EC2
Web UI
JDBC Driver
Cloud Services
Optimization
Financial Analysts
DML
1
4
9
B
F
H
J
M
Node
Node
Node
Node
Node
Node
Node
Node
Metadata
Database
1
9
H
2
A
I
Warehouse Mgmt Security
DDL
Cluster
Storage
S3
Query Mgmt
3
B
J
4
C
K
5
D
L
6
E
M
7
F
N
Metadata
Database
Metadata
Database
8
G
O
44
Snowflake Architecture
User Interface
JDBC Driver
ODBC Driver
Compute
EC2
Loading
Web UI
Cloud Services
Optimization
Financial Analysts
DML
Node Node Node Node
Node Node Node Node
Node Node Node Node
Node Node Node Node
Database
Cluster
Node Node Node Node
Cluster
Storage
S3
Data
Warehouse Mgmt Security
DDL
Metadata
Node Node Node Node
Query Mgmt
Sales
AWS cloud
Metadata
Database
Metadata
Database
Marketing
45
Snowflake High Availability Architecture
SQL
REST
Load Balancer
Cloud Services
Cluster
Cluster
Cluster
Cluster
Cluster
Cluster
Fully Replicated
Metadata
Virtual
Warehouses
Fully Replicated
Database Storage
Availability zone 1
Availability zone 2
Availability zone 3
46
Enterprise-class data warehouse:
Security
Authentication
• Embedded multi-factor authentication server
• Federated authentication via SAML 2.0 (in development)
Access control
…
.
X X
• Role-based access control model
• Granular privileges on objects & actions
Data encryption
• Encryption at rest for database data
• Encryption of Snowflake metadata
• Snowflake-managed keys
Controls & processes validated through SOC
certification & audit