datahub/metadata-ingestion/tests/integration/vertica/ddl.sql

-- Create a Top-k projection
CREATE TABLE readings (meter_id INT, reading_date TIMESTAMP, reading_value FLOAT);
CREATE PROJECTION readings_topk (meter_id, recent_date, recent_value) AS SELECT meter_id, reading_date, reading_value FROM readings LIMIT 5 OVER (PARTITION BY meter_id ORDER BY reading_date DESC);

-- Create a live agg projs
CREATE TABLE clicks(user_id IDENTITY(1,1), page_id INTEGER, click_time TIMESTAMP NOT NULL);
CREATE PROJECTION clicks_agg AS SELECT page_id, click_time::DATE click_date, COUNT(*) num_clicks FROM clicks GROUP BY page_id, click_time::DATE;


-- Create a VIEW
CREATE VIEW sampleview AS SELECT SUM(annual_income), customer_state
FROM public.customer_dimension
WHERE customer_key IN (SELECT customer_key FROM store.store_sales_fact)
GROUP BY customer_state ORDER BY customer_state ASC;

-- Step 1: Create library
\set libfile '\''/opt/sqlalchemy-vertica-dialect'/python/TransformFunctions.py\''
CREATE LIBRARY TransformFunctions AS '/opt/vertica/sdk/examples/python/TransformFunctions.py' LANGUAGE 'Python';


-- Step 2: Create functions
CREATE TRANSFORM FUNCTION tokenize AS NAME 'StringTokenizerFactory' LIBRARY TransformFunctions;
CREATE TRANSFORM FUNCTION topk AS NAME 'TopKPerPartitionFactory' LIBRARY TransformFunctions;

CREATE TABLE phrases (phrase VARCHAR(128));
COPY phrases FROM STDIN;
Word
The quick brown fox jumped over the lazy dog
\.

SELECT tokenize(phrase) OVER () FROM phrases;

-- Create a temp table

-- CREATE TEMPORARY TABLE sampletemp (a int, b int) ON COMMIT PRESERVE ROWS;
-- INSERT INTO sampletemp VALUES(1,2);

-- Create partition key
-- ALTER TABLE store.store_orders_fact PARTITION BY date_ordered::DATE GROUP BY DATE_TRUNC('month', (date_ordered)::DATE);
-- SELECT PARTITION_TABLE('store.store_orders_fact');
-- CREATE PROJECTION ytd_orders AS SELECT * FROM store.store_orders_fact ORDER BY date_ordered
--     ON PARTITION RANGE BETWEEN date_trunc('year',now())::date AND NULL;




SELECT start_refresh();