wentium
wentium
/ postgres_queries_and_commands.sql
Created
March 1, 2018 07:17
— forked from rgreenjr/postgres_queries_and_commands.sql
Useful PostgreSQL Queries and Commands
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- show running queries (pre 9.2) | |
| SELECT procpid, age(query_start, clock_timestamp()), usename, current_query | |
| FROM pg_stat_activity | |
| WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' | |
| ORDER BY query_start desc; | |
| -- show running queries (9.2) | |
| SELECT pid, age(query_start, clock_timestamp()), usename, query | |
| FROM pg_stat_activity | |
| WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' |
wentium
/ creating_a_date_dimension_table_in_postgresql.sql
Created
February 23, 2018 09:40
— forked from duffn/creating_a_date_dimension_table_in_postgresql.sql
Creating a date dimension table in PostgreSQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DROP TABLE if exists d_date; | |
| CREATE TABLE d_date | |
| ( | |
| date_dim_id INT NOT NULL, | |
| date_actual DATE NOT NULL, | |
| epoch BIGINT NOT NULL, | |
| day_suffix VARCHAR(4) NOT NULL, | |
| day_name VARCHAR(9) NOT NULL, | |
| day_of_week INT NOT NULL, |
wentium
/ dim_calendar.hql
Created
November 1, 2017 03:17
— forked from PeterAttardo/dim_calendar.hql
Create calendar dimension table in hive query language (HQL)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| set hivevar:start_date=0000-01-01; | |
| set hivevar:days=1000000; | |
| set hivevar:table_name=[INSERT YOUR TABLE NAME HERE]; | |
| -- If you are running a version of HIVE prior to 1.2, comment out all uses of date_format() and uncomment the lines below for equivalent functionality | |
| CREATE TABLE IF NOT EXISTS ${table_name} AS | |
| WITH dates AS ( | |
| SELECT date_add("${start_date}", a.pos) as date |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DROP TABLE IF EXISTS dim_date; | |
| CREATE TABLE dim_date ( | |
| the_date datetime NOT NULL , | |
| date varchar(20) NOT NULL , | |
| the_year char(6) , | |
| the_halfyear char(6), | |
| the_quarter char(6), | |
| the_month char(6), | |
| the_day char(4), |
wentium
/ vpnsetup.sh
Last active
August 29, 2015 14:14
— forked from hwdsl2/.MOVED.md
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/sh | |
| # | |
| # Amazon EC2 user-data file for automatic configuration of IPsec/L2TP VPN | |
| # on a Ubuntu server instance. Tested with 14.04 (Trusty) AND 12.04 (Precise). | |
| # With minor modifications, this script *can also be used* on dedicated servers | |
| # or any KVM- or XEN-based Virtual Private Server (VPS) from other providers. | |
| # | |
| # DO NOT RUN THIS SCRIPT ON YOUR PC OR MAC! THIS IS MEANT TO BE RUN WHEN | |
| # YOUR AMAZON EC2 INSTANCE STARTS! | |
| # |