Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SnowflakeADFConnectorDatabaseSetup
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>');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment