Skip to content

Instantly share code, notes, and snippets.

Marco Slot marcocitus

Block or report user

Report or block marcocitus

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View queries.sql
-- Q1
select ol_number,
sum(ol_quantity) as sum_qty,
sum(ol_amount) as sum_amount,
avg(ol_quantity) as avg_qty,
avg(ol_amount) as avg_amount,
count(*) as count_order
from order_line
where ol_delivery_d > '2007-01-02 00:00:00.000000'
group by ol_number order by ol_number;
@marcocitus
marcocitus / hammerdb.sql
Last active Oct 7, 2019
HammerDB Function Schema
View hammerdb.sql
CREATE TABLE public.customer (c_id numeric(5,0) NOT NULL, c_d_id numeric(2,0) NOT NULL, c_w_id numeric(4,0) NOT NULL, c_first character varying(16), c_middle character(2), c_last character varying(16), c_street_1 character varying(20), c_street_2 character varying(20), c_city character varying(20), c_state character(2), c_zip character(9), c_phone character(16), c_since timestamp without time zone, c_credit character(2), c_credit_lim numeric(12,2), c_discount numeric(4,4), c_balance numeric(12,2), c_ytd_payment numeric(12,2), c_payment_cnt numeric(8,0), c_delivery_cnt numeric(8,0), c_data character varying(500)) WITH (fillfactor='50');
CREATE UNIQUE INDEX customer_i2 ON public.customer USING btree (c_w_id, c_d_id, c_last, c_first, c_id) TABLESPACE pg_default ;
ALTER TABLE public.customer ADD CONSTRAINT customer_i1 PRIMARY KEY (c_w_id, c_d_id, c_id);
CREATE TABLE public.district (d_id numeric(2,0) NOT NULL, d_w_id numeric(4,0) NOT NULL, d_ytd numeric(12,2), d_tax numeric(4,4), d_next_o_id numeric, d_nam
View citus-cloud-create-dev.sh
#!/bin/sh
name=Marco
formation_name=${1:-test}
formations_url=https://console.citusdata.com/api/v1/formations
api_token=$(cat $HOME/.cituscloud/token)
formation_dir=$HOME/.cituscloud/formations/$formation_name/
worker_count=2
function log {
@marcocitus
marcocitus / citus-fdw.sql
Created Jul 11, 2018
Creating a distributed postgres_fdw table
View citus-fdw.sql
/* add the user mappings from the distributed table to the shards */
CREATE OR REPLACE FUNCTION bind_shards_to_remote_shards(table_name regclass)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
foreign_server text;
fdw_options text[];
user_mapping_options text[];
server_options text[];
@marcocitus
marcocitus / example.sql
Last active Nov 7, 2019
Safe incremental rollups on Postgres and Citus
View example.sql
-- Create the raw events table
CREATE TABLE page_views (
site_id int,
path text,
client_ip inet,
view_time timestamptz default now(),
view_id bigserial
);
-- Allow fast lookups of ranges of sequence IDs
@marcocitus
marcocitus / repair-placements.sql
Last active Apr 1, 2018
Repair all shard placements in shardstate 3
View repair-placements.sql
CREATE OR REPLACE FUNCTION public.repair_all()
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
PERFORM
master_copy_shard_placement(shardid, h.nodename, h.nodeport, u.nodename, u.nodeport)
FROM
(SELECT shardid, nodename, nodeport FROM pg_dist_shard_placement WHERE shardstate = 3) u
JOIN
@marcocitus
marcocitus / drain_node.sql
Last active Mar 22, 2018
Functions to drain and remove a node in Citus Enterprise
View drain_node.sql
CREATE OR REPLACE FUNCTION public.drain_node(remove_host text, remove_port int DEFAULT 5432, shard_transfer_mode citus.shard_transfer_mode DEFAULT 'auto'::citus.shard_transfer_mode)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
coordinator_name text;
coordinator_port int;
candidate_count int;
candidate_hosts text[];
candidate_ports int[];
@marcocitus
marcocitus / rollups.sql
Last active Nov 14, 2019
Efficient real-time rollups with backfilling in Citus
View rollups.sql
CREATE TABLE rollups (
name text,
rolled_up_generation bigint default -1
);
-- Create a stub on workers to allow usage as a default in distributed tables
SELECT run_command_on_workers($$
CREATE OR REPLACE FUNCTION current_rollup_generation(rollup_name text)
RETURNS bigint LANGUAGE sql
AS $function$
@marcocitus
marcocitus / pg_partman.sql
Last active Mar 16, 2019
Setting up pg_partman on cloud
View pg_partman.sql
-- as superuser:
CREATE EXTENSION pg_cron;
GRANT USAGE ON SCHEMA cron TO citus;
CREATE SCHEMA partman;
GRANT USAGE ON SCHEMA partman TO citus;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
GRANT ALL ON TABLE partman.part_config TO citus;
GRANT ALL ON TABLE partman.part_config_sub TO citus;
@marcocitus
marcocitus / load_github_data.sql
Last active Mar 12, 2019
Load data from GitHub in postgres
View load_github_data.sql
CREATE OR REPLACE FUNCTION public.load_github_data(events_date date, hour integer)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
BEGIN
CREATE TEMPORARY TABLE input (data jsonb) ON COMMIT DROP;
EXECUTE format($$COPY input FROM PROGRAM 'curl -s http://data.githubarchive.org/%s-%s.json.gz | zcat | grep -v \\u0000' CSV QUOTE e'\x01' DELIMITER e'\x02'$$, events_date, hour);
INSERT INTO github.events SELECT
(data->>'id')::bigint AS event_id,
You can’t perform that action at this time.