Scribd
Upload
11 views

sql_lab1

The document outlines SQL queries and a Python script used for two data processing pipelines in Snowflake. The first pipeline involves creating a database and schema, loading stock data into a table, and inserting records for three symbols. The second pipeline creates a view for stock data, builds a machine learning forecast model, evaluates its metrics, makes predictions, and consolidates actual and forecasted data into a final table.

Uploaded by

Rajarajeswari
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
11 views

sql_lab1

The document outlines SQL queries and a Python script used for two data processing pipelines in Snowflake. The first pipeline involves creating a database and schema, loading stock data into a table, and inserting records for three symbols. The second pipeline creates a view for stock data, builds a machine learning forecast model, evaluates its metrics, makes predictions, and consolidates actual and forecasted data into a final table.

Uploaded by

Rajarajeswari
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

-- The following sql queries are run along with python script

-- For fist pipeline - 226_lab1_etl


-- loading extracted data into snowflake table
CREATE DATABASE dev;
CREATE SCHEMA dev.raw;

BEGIN;

Create the target table if it doesn't exist


CREATE TABLE IF NOT EXISTS dev.raw.stock (
symbol STRING,
date TIMESTAMP,
open NUMBER(38, 4),
high NUMBER(38, 4),
low NUMBER(38, 4),
close NUMBER(38, 4),
volume NUMBER(38, 0),
PRIMARY KEY (symbol, date)
);

--Deleting all existing records in the target table


DELETE FROM dev.raw.stock;

-- Inserting records into the table


-- The following line is just a sample.
INSERT INTO dev.raw.stock (symbol, date, open, high, low, close, volume)
VALUES ('JPM', ''2024-06-17', 190.2593, 192.3243, 189.4332,
191.7343, 8725400)
--- All the records for 3 symbols of alst 180 days were inserted using python
script.

COMMIT;
-- rollback if something goes wrong
--ROLLBACK;

-- For SECOND pipeline - train and predict


-- CREATING VIEW FOR STOCK DATA.
CREATE OR REPLACE VIEW dev.raw.stock_view AS
SELECT
CAST(DATE AS TIMESTAMP_NTZ) AS DATE,
CLOSE,
SYMBOL
FROM dev.raw.stock;

-- CREATING ML FORECAST MODEL.


CREATE OR REPLACE SNOWFLAKE.ML.FORECAST dev.raw.predict_stock_price (
INPUT_DATA => SYSTEM$REFERENCE('VIEW', 'dev.raw.stock_view'),
SERIES_COLNAME => 'SYMBOL',
TIMESTAMP_COLNAME => 'DATE',
TARGET_COLNAME => 'CLOSE',
CONFIG_OBJECT => { 'ON_ERROR': 'SKIP' }
);

-- EVALUATING MATRICS OF THE MODEL.


CALL dev.raw.predict_stock_price!SHOW_EVALUATION_METRICS();

-- MAKING PREDICTIONS USING THE MODEL


CALL dev.raw.predict_stock_price!FORECAST(
FORECASTING_PERIODS => 7,
CONFIG_OBJECT => { 'prediction_interval': 0.95 }
);

--Capturing forecasted results into a table


LET x := SQLID;
CREATE OR REPLACE TABLE dev.raw.stock_forecast AS
SELECT *
FROM TABLE(RESULT_SCAN(:x));

-- CREATING FINAL TABLE THAT CONTAINS ACTUAL AND FORECAST DATA


CREATE TABLE IF NOT EXISTS dev.raw.final_data AS
SELECT
SYMBOL,
DATE,
CLOSE AS actual,
NULL AS forecast,
NULL AS lower_bound,
NULL AS upper_bound
FROM dev.raw.stock

UNION ALL

SELECT
REPLACE(series, '"', '') AS SYMBOL,
ts AS DATE,
NULL AS actual,
forecast,
lower_bound,
upper_bound
FROM dev.raw.stock_forecast;

You might also like