BigDataDave BigDataDave1
BigDataDave1
/ snowflake_resource_optimization.sql
Created
November 6, 2023 15:51
Snowflake Resource and Performance Optimizations
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --+----------------------------------------------------------------------------------------------------------------------+-- | |
| -- OS200H | |
| -- Resource Optimization in Snowflake | |
| -- Hands on Lab for Snowflake Summit 2023 | |
| -- David A Spezia | |
| -- Principal Sales Engineer | |
| -- HighTech, TelCo & Media | |
| -- david.spezia@snowflake.com | |
| -- June 2023 | |
| -- SQL: https://docs.google.com/document/d/1v97BH450BBTJu1IUKXThZVuyavdvD7WNeDXG-b8MpJ4/edit?usp=sharing |
BigDataDave1
/ dynamic_in.sql
Last active
November 4, 2023 17:52
Snowflake SQL for Dynamic IN() Clauses from Session Variables
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --+----------------------------------------------------------------------------------------------------------------------+-- | |
| -- Dynamic IN() Clause from a Session Variable | |
| -- David A Spezia | |
| -- Solution Architect | |
| -- Sigma Computing | |
| -- david.spezia@sigmacomputing.com | |
| -- 04 Novemebr 2023 | |
| --+----------------------------------------------------------------------------------------------------------------------+-- | |
| -- <SQL> | |
| --+----------------------------------------------------------------------------------------------------------------------+-- |
BigDataDave1
/ hybrid_json_step3.sql
Created
December 24, 2020 17:52
Hybrid JSON Tables: View over JSON
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --view to unwind for analysts | |
| create or replace view demo_db.iot.json_hybrid_vw as ( | |
| select | |
| log_ts as LogDate, | |
| v:date::date as Date, | |
| v:serial_number::string as SerialNumber, | |
| v:model::string as Model, | |
| v:capacity_bytes::number as CapacityBytes, | |
| v:failure::number Failure, | |
| round(CapacityBytes/power(1024,3),2) as Capacity_GB, |
BigDataDave1
/ hybrid_json_step2.sql
Created
December 24, 2020 17:51
Hybrid JSON Tables: Create and Copy Table
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --create a hybrid table | |
| create or replace table demo_db.iot.json_hybrid ( | |
| log_ts timestamp, | |
| serial_number string, | |
| v variant, | |
| path_name string, | |
| file_name string | |
| ) cluster by (date(log_ts) , serial_number); | |
| --copy data from stage |
BigDataDave1
/ hybrid_json_dictionary.json
Last active
December 25, 2020 17:56
Hybrid JSON Tables: Dictionary
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| //AUTOMATICALLY GENERATED DATA DICTIONARY EXAMPLE JSON INGESTED INTO SNOWFLAKE | |
| { | |
| "columns":[ | |
| { | |
| "columnId":3, | |
| "columnName":"V", | |
| "dataType":"FIXED", | |
| "distinctValues":1, | |
| "minValue":100, | |
| "nullCount":0, |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --I am using demo_db.iot for my example work | |
| create database if not exists demo_db; | |
| create schema if not exists demo_db.iot; | |
| --file format | |
| create or replace file format demo_db.iot.json | |
| type = 'json' | |
| compression = 'auto' | |
| enable_octal = false | |
| allow_duplicate = false |
BigDataDave1
/ covid_quadrant_utah_cte_tableau.sql
Created
December 1, 2020 16:02
COVID Quadrant Custom SQL for Tableau Workbook that Allocates Utah Special Admin Zones by County
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --County Data with Populations, Deaths and Cases and Utah County Areas Allocated by Population for Tableau TWB | |
| WITH | |
| cases AS ( | |
| WITH counties AS ( | |
| SELECT JHU.FIPS, JHU.PROVINCE_STATE, JHU.COUNTY, JHU.DATE, GEOSQL.COUNTY_POPULATION, | |
| SUM(JHU.CASES) AS COUNTY_CASES, | |
| DIV0(COUNTY_CASES,GEOSQL.COUNTY_POPULATION) AS CASES_PER_CAPITA_COUNTY, | |
| CASES_PER_CAPITA_COUNTY * 100000 AS CASES_PER_100K | |
| FROM STARSCHEMA_COVID19.PUBLIC.JHU_COVID_19 JHU | |
| LEFT JOIN ( |
BigDataDave1
/ covid_quadrant_utah_cte.sql
Created
December 1, 2020 16:01
COVID Quadrant CTE to do Population Allocation for Utah Special Admin Zones
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --MAKE JOIN AND ALLOCATION SQL IF NULL FOR UTAH | |
| WITH counties AS ( | |
| SELECT | |
| JHU.FIPS, | |
| JHU.PROVINCE_STATE, | |
| JHU.COUNTY, | |
| JHU.DATE, | |
| GEOSQL.COUNTY_POPULATION, | |
| SUM(JHU.CASES) AS COUNTY_CASES, | |
| DIV0(COUNTY_CASES, GEOSQL.COUNTY_POPULATION) AS CASES_PER_CAPITA_COUNTY, |
BigDataDave1
/ covid_quadrant_utah_allocation.sql
Created
December 1, 2020 15:59
COVID Quadrant filling the hole with Population Allocation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /*--------- Utah FIPS Mapping Allocation by Population --------------------------------------------------------- | |
| County Name JHU UID Code | |
| Bear River 84070015 | |
| Central Utah 84070016 | |
| Southeast Utah 84070017 | |
| Southwest Utah 84070018 | |
| TriCounty 84070019 | |
| Weber-Morgan 84070020 | |
| --https://github.com/CSSEGISandData/COVID-19/issues/3066 |
BigDataDave1
/ covid_quadrant_utah.sql
Created
December 1, 2020 15:56
COVID Quadrant Hole in Utah JHU
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| JHU.FIPS, | |
| JHU.PROVINCE_STATE, | |
| JHU.COUNTY, | |
| JHU.DATE, | |
| GEOSQL.COUNTY_POPULATION, | |
| SUM(JHU.CASES) AS COUNTY_CASES, | |
| DIV0(COUNTY_CASES, GEOSQL.COUNTY_POPULATION) AS CASES_PER_CAPITA_COUNTY, | |
| CASES_PER_CAPITA_COUNTY * 100000 AS CASES_PER_100K | |
| FROM |
NewerOlder