Powered By Snowflake And Tableau Embedded Analytics Applications PDF Free Download

1y ago

27 Views

1 Downloads

4.24 MB

64 Pages

Report/dmca

Download PDF

Transcription

VIRTUAL HANDS-ON LABEmbedded Analytics ApplicationsPowered by Snowflake and TableauTo participate in the virtual hands-on lab please begin a freetrial or login to your Snowflake and Tableau Online accounts. 2020 Snowflake Computing Inc. All Rights Reserved

HANDS-ON LAB SET UP Sign-in to your Snowflake free trial account. URL looks something like: https://XY12345.snowflakecomputing.com If you have not already signed up for a free trial account, sign up here: https://trial.snowflake.com Please select the region closest to you and the Enterprise Edition Tableau Online Trial: ial Please login to your Tableau Online Trial Install Tableau ODBC Drivers for Snowflake (if you are using Tableau Desktop) .html Download files from On24 resource section SQL script - .sql file - Required Embedded Portal zip - Required 2020 Snowflake Computing Inc. All Rights Reserved2

MEETTODAY’SHANDS-ONLAB LEADERS 2020 Snowflake Computing Inc. All Rights ReservedDavid SpeziaSales EngineerBailey FerrariSolutions Engineer

WHAT WE’LL COVER TODAY Get JSON data from IoT enabled bikes and ingest the data into Snowflake Integrate tables and views in Snowflake Integrate and deliver multi-tenant tables and views in Snowflake Use Tableau and enable end users and analysts to ask your data questions. Explore the data with Tableau Visualize data with Tableau in an Embedded Portal Showcase your data in the Snowflake Data Marketplace to further promote yourdata capabilities across all Snowflake customers. Q&A 2020 Snowflake Computing Inc. All Rights Reserved4

sks, StreamsStreaming:Semi-structuredGlobal ServicesLogical ModelSecurityQuery Planning & OptimisationTransactional Control 2020 Snowflake Computing Inc. All Rights ReservedOnlineVHOL Flow Create Table Ingest to Table Create Secure View ShareL

Tableau Online 2020 Snowflake Computing Inc. All Rights Reserved Tableau Creators are granted access to thesharing platform of their choice, eitherTableau Online or Tableau Server. Your sharing platform enables you toprovide analytics to your colleagues or yourcustomers directly through the browser oron-the-go with mobile applications. Tableau Online is fully hosted in the cloud.

Your Application Any existing application or portal Typically HTML*, CSS, and JavaScript Use the Tableau JavaScript API to integrateTableau visualizations into your own webapplications.*Requires HTML 5 or later 2020 Snowflake Computing Inc. All Rights Reserved

If you haven’t already, pleasedownload the SQL documentlinked here in your On24 screen ifyou plan on following along thesteps using your own Snowflakeenvironment

CODEDatabase--RoleUSE ROLE ACCOUNTADMIN;--Create Database, Schema, Warehouse, Stage and FileFormatcreate or replace database TABLEAU VHOL DATABASE;create or replace schema TABLEAU VHOL SCHEMA; 2020 Snowflake Computing Inc. All Rights Reserved

CODEWarehousecreate or replace warehouse TABLEAU VHOL WH WITHWAREHOUSE SIZE 'MEDIUM'WAREHOUSE TYPE 'STANDARD'AUTO SUSPEND 60AUTO RESUME TRUEMIN CLUSTER COUNT 1MAX CLUSTER COUNT 1SCALING POLICY 'STANDARD';alter warehouse TABLEAU VHOL WH SET WAREHOUSE SIZE 'LARGE';alter warehouse TABLEAU VHOL WH SET WAREHOUSE SIZE 'MEDIUM'; 2020 Snowflake Computing Inc. All Rights Reserved

CODEStage--Internal Stagecreate or replace STAGE VHOL STAGE;show stages;--External Stage on S3create or replace STAGE VHOL STAGEURL 's3://snowflake-workshop-lab/citibike-trips';show stages;--Lists Files on the S3 Bucketlist @VHOL STAGE/; 2020 Snowflake Computing Inc. All Rights Reserved

CODEFile Format--File Formatcreate or replace FILE FORMAT CSVTYPE 'CSV'COMPRESSION 'AUTO'FIELD DELIMITER ','RECORD DELIMITER '\n'SKIP HEADER 0FIELD OPTIONALLY ENCLOSED BY '\042'TRIM SPACE FALSEERROR ON COLUMN COUNT MISMATCH FALSEESCAPE 'NONE'ESCAPE UNENCLOSED FIELD '\134'DATE FORMAT 'AUTO'TIMESTAMP FORMAT 'AUTO'NULL IF ('');show File Formats; 2020 Snowflake Computing Inc. All Rights Reserved

CODETrips Data 2020 Snowflake Computing Inc. All Rights Reservedtrip duration in secondsstart time timestamp ESTstop time timestamp ESTstart station idstart station namestart station latitudestart station longitudeend station idend station nameend station latitudeend station longitudebike idmembership typeuser typebirth yeargender as integer

CODEQUERY FROM STAGE--select over stageselect 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16from @VHOL STAGE/trips 2013 0 0 0.csv.gz limit 100;--select over stage with file formatselect 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16from @VHOL STAGE/trips 2013 0 0 0.csv.gz(file format CSV) limit 100; 2020 Snowflake Computing Inc. All Rights Reserved

CODECREATE TABLE 2020 Snowflake Computing Inc. All Rights Reserved--create tablecreate or replace table trips(tripduration integer,starttime timestamp,stoptime timestamp,start station id integer,start station name string,start station latitude float,start station longitude float,end station id integer,end station name string,end station latitude float,end station longitude float,bikeid integer,membership type string,usertype string,birth year integer,gender integer);

CODECOPY INTO & SELECT--copy data over for tripscopy into trips from @VHOL STAGE file format CSV;--selectselect * from trips limit 20;--fancy selectselect date trunc('hour', starttime) as "date",count(*) as "num trips",avg(tripduration)/60 as "avg duration (mins)",avg(haversine(start station latitude,start station longitude, end station latitude,end station longitude)) as "avg distance (km)"from tripsgroup by 1 order by 1;--trips by dayselectdayname(starttime) as "day of week",count(*) as "num trips"from tripsgroup by 1 order by 2 desc;--clonecreate table trips dev clone trips; 2020 Snowflake Computing Inc. All Rights Reserved

CODETABLE, STAGE, COPY--variant tablecreate table json weather data (v variant);--weather stagecreate stage nyc weatherurl 's3://snowflake-workshop-lab/weather-nyc';--list weatherlist @nyc weather;--select over stageselect 1 from @nyc weather/weather 0 0 0.json.gzlimit 10;--copy intocopy into json weather datafrom @nyc weatherfile format (type json);--selectselect * from json weather data limit 10; 2020 Snowflake Computing Inc. All Rights Reserved

CODECREATE VIEW--create viewcreate view weather vw asselectv:time::timestamp as observation time,v:city.id::int as city id,v:city.name::string as city name,v:city.country::string as country,v:city.coord.lat::float as city lat,v:city.coord.lon::float as city lon,v:clouds.all::int as clouds,(v:main.temp::float)-273.15 as temp avg,(v:main.temp min::float)-273.15 as temp min,(v:main.temp max::float)-273.15 as temp max,v:weather[0].main::string as weather,v:weather[0].description::string as weather desc,v:weather[0].icon::string as weather icon,v:wind.deg::float as wind dir,v:wind.speed::float as wind speedfrom json weather datawhere city id 5128638;--select *select * from weather vw limit 10; 2020 Snowflake Computing Inc. All Rights Reserved

CODESELECT & DROPS--selectselect weather as conditions,count(*) as num tripsfrom tripsleft outer join weather vwon date trunc('hour', observation time) date trunc('hour', starttime)where conditions is not nullgroup by 1 order by 2 desc;--oh-no!drop table json weather data;--selectselect * from json weather data limit 10;--thank you!undrop table json weather data;--selectselect * from json weather data limit 10; 2020 Snowflake Computing Inc. All Rights Reserved

CODESTATIONScreate or replace table stations (station id number(38,0),station name varchar(100),station latitude float,station longitude float);insert into stationsselect distinct station id, max(STATION NAME),max(STATION LATITUDE), max(STATION LONGITUDE) from(select START STATION ID AS STATION ID,START STATION NAME AS STATION NAME,START STATION LATITUDE AS STATION LATITUDE,START STATION LONGITUDE AS STATION LONGITUDEfrom tripsunion allselect END STATION ID AS STATION ID,END STATION NAME AS STATION NAME,END STATION LATITUDE AS STATION LATITUDE,END STATION LONGITUDE AS STATION LONGITUDEfrom trips)group by station idorder by station id asc;select * from stations; 2020 Snowflake Computing Inc. All Rights Reserved

CODETENANTScreate or replace table tenant (tenant id number,tenant description string,tenant account string);--add tenant for your accountinsert into tenant values (1, 'My Account', current account());--selectselect * from tenant;--map tenant to subscribed station beaconscreate table tenant stations (tenant id number,station id number); 2020 Snowflake Computing Inc. All Rights Reserved

CODETENANTS MAPPING--valuesinsert into tenant stations (1,(1,(1,(1,(1,(1,(1,(1,(1,(1,(1,(1,(1,(1,; 2020 Snowflake Computing Inc. All Rights 5),296),297),298)

CODETENANTS TESTING--select *select * from tenant stations;--selectset tenant sv '1';select * from tripsjoin tenant stationson trips.start station id tenant stations.station idjoin tenanton tenant stations.tenant id tenant.tenant idwheretenant.tenant id tenant svlimit 100;--select bogusset tenant sv '0';select * from tripsjoin tenant stationson trips.start station id tenant stations.station idjoin tenanton tenant stations.tenant id tenant.tenant idwheretenant.tenant id tenant svlimit 100; 2020 Snowflake Computing Inc. All Rights Reserved

TENANTS SECUREVIEWCODE--secure viewcreate or replace secure view trips secure as(select tripduration, starttime, stoptime,start station id, bikeid, tenant.tenant id fromtripsjoin tenant stationson trips.start station id tenant stations.station idjoin tenanton tenant stations.tenant id tenant.tenant idwheretenant.tenant account current account());--current account?select current account();--select secure viewselect * from trips secure limit 100; 2020 Snowflake Computing Inc. All Rights Reserved

TENANTS SECUREVIEW TESTINGCODE--create a reader account for your tenantCREATE MANAGED ACCOUNT BIG IMPORTANT CLIENTadmin name 'USER',admin password 'P@ssword123',type reader,COMMENT 'Testing';--add tenant for your big important client via areader accountinsert into tenant values (1, 'Big Important Client, Wink Wink', 'locatorfrom above');--simulate your tenantalter session set simulated data sharing consumer 'locator from above';--select secure view as your tenantselect * from trips secure limit 100;--unsimulate your tenantalter session unset simulated data sharing consumer;--are you sure?select * from trips secure limit 100; 2020 Snowflake Computing Inc. All Rights Reserved

CODESECURE SHARING--create share and share to reader accountCREATE OR REPLACE SHARE TABLEAU EMBEDDED SHARECOMMENT 'Creating my Share to Share with my Reader';GRANT USAGE ON DATABASE TABLEAU VHOL DATABASE TOSHARE TABLEAU EMBEDDED SHARE;GRANT USAGE ON SCHEMATABLEAU VHOL DATABASE.TABLEAU VHOL SCHEMA TO SHARETABLEAU EMBEDDED SHARE;GRANT SELECT ON VIEWTABLEAU VHOL DATABASE.TABLEAU VHOL SCHEMA.TRIPS SECURE TO SHARE TABLEAU EMBEDDED SHARE;DESC SHARE TABLEAU EMBEDDED SHARE;ALTER SHARE TABLEAU EMBEDDED SHARE ADD ACCOUNTS locator from above;SHOW SHARES LIKE 'TABLEAU EMBEDDED SHARE'; 2020 Snowflake Computing Inc. All Rights Reserved

CODEMarketplace Listing (UI)CREATE LISTING identifier("FOO VZNRANLMZR") IN DATAEXCHANGE "SE SANDBOX"ALTER LISTING identifier("FOO VZNRANLMZR") SETis with anyshare trueALTER LISTING identifier("FOO VZNRANLMZR") SETMETADATA cription":"Ing","query":"Select * f. 2020 Snowflake Computing Inc. All Rights Reserved

“Live” Connection 2020 Snowflake Computing Inc. All Rights Reserved A “live” connection in Tableau directlyqueries snowflake with every interaction Live connections allow for real-time dataupdates Snowflake can query tens of millions ofrecords in sub-seconds

Build your Dashboard 2020 Snowflake Computing Inc. All Rights Reserved Dashboard is a collection of worksheets. In general, it's a good idea to limit thenumber of views you include in yourdashboard to three or four. If you add too many views, visual clarity andthe big picture can get lost in the details. Don’t bury the most important fact, your KPI,in a chart. Show it loud and proud as a BigA** Number (BAN)!

Key Features & API’sHere are some of the things that you can do withthe JavaScript API: Dynamically load and resize visualizations. Filter the data displayed in visualizationswith HTML controls in the page. Select marks in visualizations. Respond to events in visualizations. Export visualizations to an image or PDF fileCheck out our Embedded Analytics Playbook for more info! 2020 Snowflake Computing Inc. All Rights Reserved

Our Goal Open the “dashboard starter.html” file in thebrowser of your choice It should be primarily empty, withplaceholder actions and filters Our goal is to populate this file! Step 1: Embed the visualizationStep 2: Use JavaScript to interact with thevisualization 2020 Snowflake Computing Inc. All Rights Reserved

Embedding the Viz 2020 Snowflake Computing Inc. All Rights Reserved Once you’ve saved/published the workbook,grab the link that is automatically generatedby Tableau Online You will be pasting this line into a js filereferenced by the html of the application

embed starter.js 2020 Snowflake Computing Inc. All Rights Reserved Open the “embed starter.js” file in the texteditor of your choice First, we need to initialize the view Copy and paste your url into the line thatsays “YOUR URL HERE”

dashboard starter.html Next, we need to reference the js file withinthe html script type "text/javascript"src "js/embed starter.js" /script Finally, we want to render the viz on load, sowe’ll re-use the “initViz()” function body id "page-top" onload "initViz();" 2020 Snowflake Computing Inc. All Rights Reserved

Open in Browser Step 1 is complete! We embedded thevisualization successfully. You can now interact with the viz exactly likeyou’d interact with it in Tableau Online. 2020 Snowflake Computing Inc. All Rights ReservedFor example, existing dashboard actions will befully functioning.

embed starter.js Now we can use the TableauJavaScript API to customize the look,feel, and functionality Let’s start by adding export buttons. To find the functions, go to:Tableau JavaScript API Help - Samples 2020 Snowflake Computing Inc. All Rights ReservedWe’ll be using: viz.showExportPDFDialog(); viz.showExportCrossTabDialog(); viz.exportCrossTabToExcel();

dashboard starter.html Rinse and repeat -- with the functionsadded to the js file, we can nowreference them in our html fileonclick "exportToPDF();"onclick "exportToCSV();"onclick "downloadCrosstab();" 2020 Snowflake Computing Inc. All Rights Reserved

embed starter.js Next, lets create a few filters. We’ll startwith Gender.Apply Filter: sheets[i].applyFilterAsync(filterName, value,tableau.FilterUpdateType.REPLACE);Clear Filter: sheets[i].clearFilterAsync(filterName);Revert to Original: 2020 Snowflake Computing Inc. All Rights Reservedviz.revertAllAsync();

embed starter.js And another for Usertype.Apply Filter: sheets[i].applyFilterAsync(filterName, value,tableau.FilterUpdateType.REPLACE);Clear Filter: sheets[i].clearFilterAsync(filterName);Revert to Original: 2020 Snowflake Computing Inc. All Rights Reservedviz.revertAllAsync();

Conclusion Embedding and Platforming Data Applications on Snowflake & Tableau is Easy to get started andAnalytically Powerful Creating Tables & Ingesting CSV and JSON Creating Multi-Tenant Shares and Views Data Marketplaces Internal, Private and External Read the “Definitive Guide to Maximizing Your Free Trial” document al/ Attend a Snowflake virtual or in-person event to learn more about our capabilities and howcustomers use us: https://www.snowflake.com/about/events/ Read the “Best Practices for Using Tableau with Snowflake” best-practices-for-using-tableau-with-snowflake 2020 Snowflake Computing Inc. All Rights Reserved

Use Tableau and enable end users and analysts to ask your data questions. Explore the data with Tableau Visualize data with Tableau in an Embedded Portal Showcase your data in the Snowflake Data Marketplace to further promote your data capabilities across all Snowflake customers. Q&A