SnowflakeADFConnectorDatabaseSetup

adf-snowflake.sql

CREATE DATABASE IF NOT EXISTS "ADF-DB";

USE DATABASE "ADF-DB";

CREATE SCHEMA IF NOT EXISTS "SALES";

USE SCHEMA "SALES";

CREATE TABLE IF NOT EXISTS LINEITEM (
	L_ORDERKEY NUMBER(38,0),
	L_PARTKEY NUMBER(38,0),
	L_SUPPKEY NUMBER(38,0),
	L_LINENUMBER NUMBER(38,0),
	L_QUANTITY NUMBER(12,2),
	L_EXTENDEDPRICE NUMBER(12,2),
	L_DISCOUNT NUMBER(12,2),
	L_TAX NUMBER(12,2),
	L_RETURNFLAG VARCHAR(1),
	L_LINESTATUS VARCHAR(1),
	L_SHIPDATE DATE,
	L_COMMITDATE DATE,
	L_RECEIPTDATE DATE,
	L_SHIPINSTRUCT VARCHAR(25),
	L_SHIPMODE VARCHAR(10),
	L_COMMENT VARCHAR(44)
);

CREATE OR REPLACE VIEW SupplierAgg
AS
  SELECT 
      L_SUPPKEY
      , YearMonth
      , SUM(L_EXTENDEDPRICE) AS TOTAL_PRICE
      , AVG(L_DISCOUNT) AS AVERAGE_DISCOUNT
      , SUM(L_TAX) AS TOTAL_TAX
  FROM (
      SELECT 
          L_SUPPKEY
          , L_EXTENDEDPRICE
          , L_DISCOUNT
          , L_TAX
          , L_COMMITDATE
          , Year(L_COMMITDATE) || '-' || LPAD(Month(L_COMMITDATE), 2, '0') AS YearMonth 
      FROM LINEITEM
  )
  GROUP BY L_SUPPKEY, YearMonth;

CREATE OR REPLACE STAGE azure_adf_stage
  URL='<azure storage container url>'
  CREDENTIALS=(AZURE_SAS_TOKEN='<azure storage container sas token>');