-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcommands.sh
94 lines (79 loc) · 3.25 KB
/
commands.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
#####################################################
####################### DOCKER ######################
#####################################################
# run postgres in docker
docker run --name postgres -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres -d postgres:latest
# run pgAdmin
docker run --name pgadmin -p 5050:80 -e '[email protected]' -e 'PGADMIN_DEFAULT_PASSWORD=postgres' -d dpage/pgadmin4
# http://localhost:5050
# run Grafana
docker run -d --name=grafana -p 3000:3000 -e GF_SECURITY_ADMIN_USER=postgres -e GF_SECURITY_ADMIN_PASSWORD=postgres grafana/grafana
# http://localhost:3000
# export datasource
curl -u postgres:postgres http://localhost:3000/api/datasources
#####################################################
################## DOCKER-COMPOSE ###################
#####################################################
docker-compose -f docker-compose.yaml -f docker-compose.overwrite.yaml up -d # with persistence
docker-compose up -d # no persistence
#####################################################
###################### DATABASE #####################
#####################################################
# get postgres ip
docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' postgres
# connect to postgres
docker run --rm --link postgres:postgres -it postgres psql -h postgres -U postgres -d nyc_motor_vechicle_collisions
pgcli -h localhost -U postgres -d nyc_motor_vechicle_collisions
# create db database
docker run --rm --link postgres:postgres -v $(pwd)/data/database:/tmp -it postgres psql -h postgres -U postgres -a -f /tmp/create_db.sql
# create db tables
docker run --rm --link postgres:postgres -v $(pwd)/data/database:/tmp -it postgres psql -h postgres -U postgres -d nyc_motor_vechicle_collisions -a -f /tmp/create_tables.sql
# Database commands
SELECT contributing_factor_vehicle_1, contributing_factor_vehicle_2, contributing_factor_vehicle_3, contributing_factor_vehicle_4, contributing_factor_vehicle_5 FROM staging;
SELECT contributing_factor_vehicle_1 FROM staging where contributing_factor_vehicle_1 == NULL;
(SELECT contributing_factor_vehicle_1 FROM staging)
UNION
(SELECT contributing_factor_vehicle_2 FROM staging)
UNION
(SELECT contributing_factor_vehicle_3 FROM staging)
UNION
(SELECT contributing_factor_vehicle_4 FROM staging)
UNION
(SELECT contributing_factor_vehicle_5 FROM staging)
ORDER BY 1
(SELECT vehicle_type_code_1 FROM staging)
UNION
(SELECT vehicle_type_code_2 FROM staging)
UNION
(SELECT vehicle_type_code_3 FROM staging)
UNION
(SELECT vehicle_type_code_4 FROM staging)
UNION
(SELECT vehicle_type_code_5 FROM staging)
ORDER BY 1
SELECT *
FROM fact_accidents fa
JOIN dim_timestamps dt ON fa.timestamp_id = dt.timestamp_id;
SELECT
n_vehicles AS "Vehivles Involved",
n_victims AS "Victims",
n_injured AS "Injured",
n_killed AS "Killed",
fa.timestamp_id,
dt.timestamp_id AS "timestamp_id_dt",
hour,
day,
month,
year
FROM fact_accidents fa
JOIN dim_timestamps dt ON fa.timestamp_id = dt.timestamp_id
LIMIT 50
SELECT
SUM(n_killed) AS "Killed",
fa.timestamp_id,
dt.timestamp_id AS "timestamp_id_dt",
year
FROM fact_accidents fa
JOIN dim_timestamps dt ON fa.timestamp_id = dt.timestamp_id
GROUP BY year
LIMIT 50