Json In Snowflake Masterclass Hands-On
Published 7/2024
MP4 | Video: h264, 1920x1080 | Audio: AAC, 44.1 KHz
Language: English | Size: 8.22 GB | Duration: 12h 13m
Learn all you need about semi-structured JSON data in Snowflake
What you'll learn
How to load and unload data in JSON format in and from Snowflake
How to query JSON data from a VARIANT data type
How to use built-in Snowflake functions for JSON objects and arrays
How to FLATTEN semi-structured data into tabular data
How to dynamically extract schema-on-read from JSON data
How to convert JSON data to other hierarchical formats
How to call the Snowflake COPY INTO commands for JSON data
How to design databases using both tabular and hierarchical topologies
How to create all sorts of simple to complex queries using JSON in Snowflake
Requirements
Basic SQL query programming skills
Basic Python programming skills
Basic beginner-level knowledge of Snowflake, as a data warehouse
Optional JavaScript programming skills
Optional knowledge of Streamlit
No prior knowledge of JSON or other semi-structured data format
Description
What you will learnAll you need to know about JSON used as a data storage format.How to design hybrid databases in Snowflake, using tabular data extended with JSON hierarchies.How to create and manipulate the two complex structures in JSON: OBJECT dictionaries and ARRAY lists.How to use LATERAL FLATTEN, to explode array elements and key-value pairs into tabular rows.How to aggregate key-values or row elements into single OBJECT or ARRAY values.How to create all sorts of simple to complex queries for JSON data in Snowflake.How to enter manually enter JSON data into Snowflake tablesHow to upload JSON files into tables, using the STRIP_OUTER_ARRAY option.How to unload Snowflake table data into a JSON format.How to switch between tabular and hierarchical topologies, to extract data.How to use the JSON dot notation, to locate specific elements in the hierarchy.How to interpret JSON data returned by system functions or as ACCOUNT_USAGE metadata.How to create custom JSON data profilers and schema on-read extractors.How to represent in a graphical manner JSON hierarchies.How to perform JSON transformations with higher-order functions, UDFs or with Snowpark DataFrame.How to find JSONPath equivalent queries in Snowflake.How to transpose data between relational and hierarchical representations.How to convert JSON to JSON, or other hierarchical formats, like YAML and XMLMy credentials for this courseThe only world-class expert from Canada selected for their Snowflake Data Superhero program in 2021.SnowPro Certification SME (Subject Matter Expert) - many SnowPro exam questions have been created by me.Passed five SnowPro certification exams to date, all on the first attempt.Certified Professional Couchbase Developer - in querying their JSON document stores using SQL.Expert in many NoSQL databases, including MongoDB and Couchbase JSON document stores.Professional workplace experience with MongoDB NoSQL JSON document stores since 2012.Enroll today, and learn how to master everything JSON in Snowflake!
Overview
Section 1: Introduction
Lecture 1 Welcome to this Course
Lecture 2 How to Benefit Most from this Course
Lecture 3 Create a Free Snowflake Trial Account
Lecture 4 Frequently Asked Questions
Section 2: JSON Fundamentals
Lecture 5 Introduction to JSON Fundamentals
Lecture 6 JSON Objects and Arrays
Lecture 7 JSON from Python Dictionaries and Lists
Lecture 8 JSON Viewers and Validators
Lecture 9 JSON Data Profiler and Modeler
Lecture 10 Hierarchical Data Formats
Lecture 11 JSON Graphical Representations
Lecture 12 JSONPath from XPath (Dot Notation)
Section 3: JSON Insertion in Snowflake
Lecture 13 Introduction to JSON Insertion in Snowflake
Lecture 14 JSON Document Databases
Lecture 15 Dynamic JSON with SELECT
Lecture 16 JSON Storage with INSERT
Lecture 17 OBJECT Creation Functions
Lecture 18 ARRAY Creation Functions
Lecture 19 JSON Data Visualization in Snowflake
Lecture 20 JSON Dot Notation in Snowflake
Lecture 21 JSON Extraction Functions
Section 4: JSON Uploading in Snowflake
Lecture 22 Introduction to JSON Uploading in Snowflake
Lecture 23 Manual JSON File Upload
Lecture 24 Data Transfer in Snowflake
Lecture 25 Bulk JSON File Upload
Lecture 26 Upload JSON with STRIP_OUTER_ARRAY
Lecture 27 Upload JSON with Inferred Schema
Lecture 28 Upload Validation and Errors
Lecture 29 Unload JSON Data
Section 5: JSON Transformations in Snowflake
Lecture 30 Introduction to JSON Transformations in Snowflake
Lecture 31 OBJECT Manipulation Functions
Lecture 32 ARRAY Manipulation Functions
Lecture 33 Higher-Order Functions for Array Elements
Lecture 34 LATERAL Keyword
Lecture 35 Flattening Arrays
Lecture 36 Flattening Dictionaries
Lecture 37 JSON from String Lists
Lecture 38 JSON from Row Aggregations
Lecture 39 JSON Transformations with UDFs
Lecture 40 JSON Transformations with Data Frames
Section 6: JSON Querying in Snowflake
Lecture 41 Introduction to JSON Querying in Snowflake
Lecture 42 JSONPath Equivalent in Snowflake
Lecture 43 More JSON Queries for Snowflake
Lecture 44 Even More JSON Queries for Snowflake
Lecture 45 Count Distinct Values with Arrays
Lecture 46 EXPLAIN Query in JSON Format
Lecture 47 Query Profile Info with JSON Data
Lecture 48 JSON Metadata for Data Lineage
Lecture 49 JSON Query Performance Optimizations
Section 7: JSON Modeling in Snowflake
Lecture 50 Introduction to JSON Modeling in Snowflake
Lecture 51 Hierarchical to Relational Model
Lecture 52 Relational to Hierarchical Model
Lecture 53 JSON to JSON Conversions
Lecture 54 JSON-Based Views
Lecture 55 Direct Access to Staged JSON Files
Lecture 56 External Tables
Lecture 57 Design Patterns for Data Pipelines
Lecture 58 Design Patterns for Data Modeling
Section 8: Wrapping Up
Lecture 59 Congratulations, You Made It!
Lecture 60 Bonus Lecture
Data Engineers collecting semi-structured data in their data pipelines,Database Administrators learning new design patterns for hybrid tabular-hierarchical data,Data Analysis who need querying hybrid data stores,Data Architects willing to learn how to flatten key-value JSON dictionaries and JSON arrays,SnowPro certification exam candidates, learning about LATERAL FLATTEN in detail,OLAP and OLTP practitioners, in need to understand how JSON fits into a relational model
What you'll learn
How to load and unload data in JSON format in and from Snowflake
How to query JSON data from a VARIANT data type
How to use built-in Snowflake functions for JSON objects and arrays
How to FLATTEN semi-structured data into tabular data
How to dynamically extract schema-on-read from JSON data
How to convert JSON data to other hierarchical formats
How to call the Snowflake COPY INTO commands for JSON data
How to design databases using both tabular and hierarchical topologies
How to create all sorts of simple to complex queries using JSON in Snowflake
Requirements
Basic SQL query programming skills
Basic Python programming skills
Basic beginner-level knowledge of Snowflake, as a data warehouse
Optional JavaScript programming skills
Optional knowledge of Streamlit
No prior knowledge of JSON or other semi-structured data format
Description
What you will learnAll you need to know about JSON used as a data storage format.How to design hybrid databases in Snowflake, using tabular data extended with JSON hierarchies.How to create and manipulate the two complex structures in JSON: OBJECT dictionaries and ARRAY lists.How to use LATERAL FLATTEN, to explode array elements and key-value pairs into tabular rows.How to aggregate key-values or row elements into single OBJECT or ARRAY values.How to create all sorts of simple to complex queries for JSON data in Snowflake.How to enter manually enter JSON data into Snowflake tablesHow to upload JSON files into tables, using the STRIP_OUTER_ARRAY option.How to unload Snowflake table data into a JSON format.How to switch between tabular and hierarchical topologies, to extract data.How to use the JSON dot notation, to locate specific elements in the hierarchy.How to interpret JSON data returned by system functions or as ACCOUNT_USAGE metadata.How to create custom JSON data profilers and schema on-read extractors.How to represent in a graphical manner JSON hierarchies.How to perform JSON transformations with higher-order functions, UDFs or with Snowpark DataFrame.How to find JSONPath equivalent queries in Snowflake.How to transpose data between relational and hierarchical representations.How to convert JSON to JSON, or other hierarchical formats, like YAML and XMLMy credentials for this courseThe only world-class expert from Canada selected for their Snowflake Data Superhero program in 2021.SnowPro Certification SME (Subject Matter Expert) - many SnowPro exam questions have been created by me.Passed five SnowPro certification exams to date, all on the first attempt.Certified Professional Couchbase Developer - in querying their JSON document stores using SQL.Expert in many NoSQL databases, including MongoDB and Couchbase JSON document stores.Professional workplace experience with MongoDB NoSQL JSON document stores since 2012.Enroll today, and learn how to master everything JSON in Snowflake!
Overview
Section 1: Introduction
Lecture 1 Welcome to this Course
Lecture 2 How to Benefit Most from this Course
Lecture 3 Create a Free Snowflake Trial Account
Lecture 4 Frequently Asked Questions
Section 2: JSON Fundamentals
Lecture 5 Introduction to JSON Fundamentals
Lecture 6 JSON Objects and Arrays
Lecture 7 JSON from Python Dictionaries and Lists
Lecture 8 JSON Viewers and Validators
Lecture 9 JSON Data Profiler and Modeler
Lecture 10 Hierarchical Data Formats
Lecture 11 JSON Graphical Representations
Lecture 12 JSONPath from XPath (Dot Notation)
Section 3: JSON Insertion in Snowflake
Lecture 13 Introduction to JSON Insertion in Snowflake
Lecture 14 JSON Document Databases
Lecture 15 Dynamic JSON with SELECT
Lecture 16 JSON Storage with INSERT
Lecture 17 OBJECT Creation Functions
Lecture 18 ARRAY Creation Functions
Lecture 19 JSON Data Visualization in Snowflake
Lecture 20 JSON Dot Notation in Snowflake
Lecture 21 JSON Extraction Functions
Section 4: JSON Uploading in Snowflake
Lecture 22 Introduction to JSON Uploading in Snowflake
Lecture 23 Manual JSON File Upload
Lecture 24 Data Transfer in Snowflake
Lecture 25 Bulk JSON File Upload
Lecture 26 Upload JSON with STRIP_OUTER_ARRAY
Lecture 27 Upload JSON with Inferred Schema
Lecture 28 Upload Validation and Errors
Lecture 29 Unload JSON Data
Section 5: JSON Transformations in Snowflake
Lecture 30 Introduction to JSON Transformations in Snowflake
Lecture 31 OBJECT Manipulation Functions
Lecture 32 ARRAY Manipulation Functions
Lecture 33 Higher-Order Functions for Array Elements
Lecture 34 LATERAL Keyword
Lecture 35 Flattening Arrays
Lecture 36 Flattening Dictionaries
Lecture 37 JSON from String Lists
Lecture 38 JSON from Row Aggregations
Lecture 39 JSON Transformations with UDFs
Lecture 40 JSON Transformations with Data Frames
Section 6: JSON Querying in Snowflake
Lecture 41 Introduction to JSON Querying in Snowflake
Lecture 42 JSONPath Equivalent in Snowflake
Lecture 43 More JSON Queries for Snowflake
Lecture 44 Even More JSON Queries for Snowflake
Lecture 45 Count Distinct Values with Arrays
Lecture 46 EXPLAIN Query in JSON Format
Lecture 47 Query Profile Info with JSON Data
Lecture 48 JSON Metadata for Data Lineage
Lecture 49 JSON Query Performance Optimizations
Section 7: JSON Modeling in Snowflake
Lecture 50 Introduction to JSON Modeling in Snowflake
Lecture 51 Hierarchical to Relational Model
Lecture 52 Relational to Hierarchical Model
Lecture 53 JSON to JSON Conversions
Lecture 54 JSON-Based Views
Lecture 55 Direct Access to Staged JSON Files
Lecture 56 External Tables
Lecture 57 Design Patterns for Data Pipelines
Lecture 58 Design Patterns for Data Modeling
Section 8: Wrapping Up
Lecture 59 Congratulations, You Made It!
Lecture 60 Bonus Lecture
Data Engineers collecting semi-structured data in their data pipelines,Database Administrators learning new design patterns for hybrid tabular-hierarchical data,Data Analysis who need querying hybrid data stores,Data Architects willing to learn how to flatten key-value JSON dictionaries and JSON arrays,SnowPro certification exam candidates, learning about LATERAL FLATTEN in detail,OLAP and OLTP practitioners, in need to understand how JSON fits into a relational model
Code:
Bitte
Anmelden
oder
Registrieren
um Code Inhalt zu sehen!
Code:
Bitte
Anmelden
oder
Registrieren
um Code Inhalt zu sehen!