Overview

What You'll Learn

What You'll Build

Prerequisites

Understand Interactive Warehouses and Interactive Tables

Interactive Warehouses

Interactive Tables

Use cases

Limitations

Setup

Data operations

Optional: Create warehouse

CREATE OR REPLACE WAREHOUSE WH WITH WAREHOUSE_SIZE='X-SMALL';

Step 1: Create a Database and Schema

CREATE DATABASE IF NOT EXISTS MY_DEMO_DB;
CREATE SCHEMA IF NOT EXISTS MY_DEMO_DB.BENCHMARK_FDN;
CREATE SCHEMA IF NOT EXISTS MY_DEMO_DB.BENCHMARK_INTERACTIVE;

Step 2: Create a new stage

-- Define database and schema to use
USE SCHEMA MY_DEMO_DB.BENCHMARK_FDN;

-- Create a stage that includes the definition for the CSV file format
CREATE OR REPLACE STAGE my_csv_stage
  FILE_FORMAT = (
    TYPE = 'CSV'
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  );

Step 3: Upload CSV to a stage

Step 4: Create the Table and Load Data

-- Use your database and schema
USE SCHEMA MY_DEMO_DB.BENCHMARK_FDN;

-- Create the table with the correct data types
CREATE OR REPLACE TABLE HITS2_CSV (
    EventDate DATE,
    CounterID INT,
    ClientIP STRING,
    SearchEngineID INT,
    SearchPhrase STRING,
    ResolutionWidth INT,
    Title STRING,
    IsRefresh INT,
    DontCountHits INT
);

-- Copy the data from your stage into the table
-- Make sure to replace 'my_csv_stage' with your stage name
COPY INTO HITS2_CSV FROM  @my_csv_stage/synthetic_hits_data.csv
  FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1);

Step 5: Query the data

-- Query
SELECT * FROM MY_DEMO_DB.BENCHMARK_FDN.HITS2_CSV;

Performance Demo of Snowflake's Interactive Warehouses/Tables

Load libraries and define custom functions

import snowflake.connector as snow
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import time
import datetime
import random
import statistics
import tabulate
from concurrent.futures import ThreadPoolExecutor, as_completed

conn_kwargs={}

def execute_and_print(query):
    cursor.execute(query)
    print(tabulate.tabulate(cursor.fetchall()))

def run_and_measure(count, mode):

    if mode =="std":
        query = """
                SELECT SearchEngineID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM 
                BENCHMARK_FDN.HITS2_CSV
                WHERE SearchPhrase <> '' GROUP BY SearchEngineID, ClientIP ORDER BY c DESC LIMIT 10;
                """
        warehouse_query ="USE WAREHOUSE wh"
    else:
        query = """
                SELECT SearchEngineID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM 
                BENCHMARK_INTERACTIVE.CUSTOMERS
                WHERE SearchPhrase <> '' GROUP BY SearchEngineID, ClientIP ORDER BY c DESC LIMIT 10;
                """
        warehouse_query ="USE WAREHOUSE interactive_demo_b"
    timings = []
    with snow.connect(**conn_kwargs) as conn:
        with conn.cursor() as cur:
            cur.execute(warehouse_query)
            cursor.execute('ALTER SESSION SET USE_CACHED_RESULT = FALSE;')
            for i in range(count+1):
                t0 = time.time()
                cur.execute(query).fetchall()
                time_taken = time.time() - t0
                timings.append(time_taken)
                
    return timings
    
def plot_data(data, title, time_taken, color='blue'):
    # Separate titles and counts
    titles = [item[0] for item in data]
    counts = [item[1] for item in data]

    # Plot bar chart
    
    plt.figure(figsize=(12, 4))
    plt.bar(titles, counts, color=color)
    plt.xticks(rotation=45, ha='right')
    plt.ylabel("Counts")
    plt.xlabel("Title")
    plt.title(title)
    plt.text(6, 100, f'Time taken: {time_taken:.4f} seconds', ha='right',va='top', fontdict={'size': 16})
    #plt.tight_layout()
    plt.show()

Setting up connection to a Snowflake deployment and verifying versions

config = { }
cursor = snow.connect(**config).cursor()

query = """ USE DATABASE MY_DEMO_DB; """
execute_and_print(query)

query = """ USE ROLE SYSADMIN;  """
execute_and_print(query)

Create an interactive warehouse

query = """
CREATE or REPLACE INTERACTIVE WAREHOUSE interactive_demo_b
                WAREHOUSE_SIZE = 'XSMALL'
                MIN_CLUSTER_COUNT = 1
                MAX_CLUSTER_COUNT = 1
                COMMENT = 'Interactive warehouse demo';
"""
execute_and_print(query)
query = """
ALTER WAREHOUSE INTERACTIVE_DEMO_B RESUME;
"""
execute_and_print(query)

--------------------------------------------------------------  
INTERACTIVE WAREHOUSE INTERACTIVE_DEMO_B successfully created.  
--------------------------------------------------------------  

Create an interactive table

print("Switch to demo database")
print(cursor.execute("USE DATABASE MY_DEMO_DB").fetchall())

print("Use a standard warehouse for creating the interactive table's data")
print(cursor.execute("USE WAREHOUSE WH").fetchall())

query = """
CREATE OR REPLACE INTERACTIVE TABLE 
MY_DEMO_DB.BENCHMARK_INTERACTIVE.CUSTOMERS CLUSTER BY (ClientIP)
AS
 SELECT * FROM MY_DEMO_DB.BENCHMARK_FDN.HITS2_CSV
 
"""
execute_and_print(query)

Switch to demo database  
[('Statement executed successfully.',)]  
Use a standard warehouse for creating the interactive table's data  
[('Statement executed successfully.',)]  
-------------------------------------  
Table CUSTOMERS successfully created.  
-------------------------------------

Attach interactive table to a warehouse

query = """
USE DATABASE MY_DEMO_DB;
"""
execute_and_print(query)

query = """
ALTER WAREHOUSE interactive_demo_b ADD TABLES(BENCHMARK_INTERACTIVE.CUSTOMERS);
"""
execute_and_print(query)

--------------------------------  
Statement executed successfully.  
--------------------------------  
...

Run queries with interactive warehouse

print("Use a standard warehouse for creating the interactive table's data")
cursor.execute("USE WAREHOUSE interactive_demo_b")
cursor.execute('USE DATABASE MY_DEMO_DB;')
cursor.execute('ALTER SESSION SET USE_CACHED_RESULT = FALSE;')

query = """
SELECT Title, COUNT(*) AS PageViews
FROM BENCHMARK_INTERACTIVE.CUSTOMERS
WHERE CounterID = 62
  AND EventDate >= '2013-07-01'
  AND EventDate <= '2013-07-31'
  AND DontCountHits = 0
  AND IsRefresh = 0
  AND Title <> ''
  AND REGEXP_LIKE(Title, '^[\\x00-\\x7F]+$')
  AND LENGTH(Title) < 20
GROUP BY Title
ORDER BY PageViews DESC
LIMIT 10;
"""

start_time = time.time()
result = cursor.execute(query).fetchall()
end_time = time.time()
time_taken = end_time - start_time

plot_data(result, "Page visit analysis (Interactive)", time_taken)

Compare to a standard warehouse

print("Use a standard warehouse for creating the interactive table's data")
cursor.execute("USE WAREHOUSE WH")
cursor.execute('ALTER SESSION SET USE_CACHED_RESULT = FALSE;')
cursor.execute('USE DATABASE MY_DEMO_DB;')

query = """
SELECT Title, COUNT(*) AS PageViews
FROM BENCHMARK_FDN.HITS2_CSV
WHERE CounterID = 62
  AND EventDate >= '2013-07-01'
  AND EventDate <= '2013-07-31'
  AND DontCountHits = 0
  AND IsRefresh = 0
  AND Title <> ''
  AND REGEXP_LIKE(Title, '^[\\x00-\\x7F]+$')
  AND LENGTH(Title) < 20
GROUP BY Title
ORDER BY PageViews DESC
LIMIT 10;
"""

start_time = time.time()
result = cursor.execute(query).fetchall()
end_time = time.time()
time_taken = end_time - start_time

plot_data(result, "Page visit analysis (Interactive)", time_taken, 'green')

Run some queries concurrently

runs = 4

counts_iw = run_and_measure(runs,"iw")
print(counts_iw)

counts_std = run_and_measure(runs,"std")
print(counts_std)

titles = [f"R{i}" for i in range(1, len(counts_iw)+1)]

x = np.arange(len(titles))  # the label locations
width = 0.35  # bar width

fig, ax = plt.subplots(figsize=(8, 5))
ax.bar(x - width/2, counts_std, width, label="Standard", color="green")
ax.bar(x + width/2, counts_iw, width, label="Interactive", color="blue")

ax.set_ylabel("Latency")
ax.set_xlabel("Query run")
ax.set_title("Standard vs Interactive warehouse")
ax.set_xticks(x)
ax.set_xticklabels(titles)
ax.legend(
    loc='upper center',
    bbox_to_anchor=(0.5, -0.15),
    ncol=2
)
plt.show()

Conclusion And Resources

What You Learned

Related Resources