This guide (https://newcastlerse.github.io/5gir-docs) is in our GitHub repository (https://github.com/NewcastleRSE/5gir-docs).
To install:
To run the python package Data Wherehouse Client, you need to create a conda environment. To create it, follow the instructions in here.
You need to log in to the 5GIR database with either Bash, CMD or Python code to export data from the 5GIR data warehouse. For instructions on how to log in, click here.
You can get and export the data from the data warehouse via PostgreSQL queries or python code.
Once you logged in to the data warehouse database, you can get and export the data from the data warehouse via PostgreSQL queries from the command line. You run the same SQL queries, regardless of your terminal (Bash or CMD) and Operating System (OS; Linux, Mac or Windows).
When you are connected to the database, you can run commands of your local (Bash or CMD) terminal by using the below
format and replacing <command> with the command that you want to run.
\! <command>
For example, if you are connected to the database by a bash terminal, you can get the current working directory with the
bash command pwd with this syntax:
\! pwd
You can list the files and subdirectories of your current working directory with command ls by using the below format:
\! ls
You can change your working directory with command cd by using the below format and replacing </path/to/directory>
with any other directory path in your local PC.
\! cd </path/to/directory>
If you connected to the database via Windows Command Prompt (CMD), you can run the below to get your current working directory
\! echo %cd%
You can list the files and the subdirectories in your current directory with the CMD command dir as it follows:
\! dir
You can also change your current directory with command cd in the following way by replacing <C:\path\to\directory>
with the destination directory path in your local PC.
\! cd /d <C:\path\to\directory>
View all table names in the DW with:
\dt
select * from study;
select * from measurementtype;
select * from measurement;
select count(*) from measurement;
SELECT column_name,data_type
FROM information_schema.columns
WHERE table_catalog = 'fgir'
AND table_schema = 'public'
AND table_name = 'measurement';
select * from measurement where study = 0 and measurementgroup = 0;
select * from measurement where study = 0 and measurementgroup = 0 and time >= '2025-10-26 00:00:00.000' and time < '2025-10-26 00:05:00.000' order by groupinstance, id;
select m.id, m.groupinstance, m.measurementtype, m.participant, m.study, m.source, m.valtype, t.textval, m.valinteger, m.valreal, m.time, m.measurementgroup, m.trial
from measurement as m left join textvalue as t on m.id = t.measurement
where m.study = 0 and m.measurementgroup = 1 and m.time >= '2025-10-26 00:00:00.000' and m.time < '2025-10-26 00:10:00.000'
order by m.groupinstance, m.id;
select m.id, m.groupinstance, m.measurementtype, m.participant, m.study, m.source, m.valtype, t.textval, m.valinteger, m.valreal, m.time, m.measurementgroup, m.trial
from (select * from measurement as m where m.study = 0 and m.measurementgroup = 1 and m.time >= '2025-10-26 00:00:00.000' and m.time < '2025-10-26 00:10:00.000') as m
left join textvalue as t on m.id = t.measurement
order by m.groupinstance, m.id;
\COPY study TO 'study.csv' DELIMITER ',' CSV HEADER
\COPY (select * from measurement where study = 0 and measurementgroup = 0) TO 'wet150.csv' DELIMITER ',' CSV HEADER
\COPY (select * from measurement where study = 0 and measurementgroup = 1) TO 'apogee.csv' DELIMITER ',' CSV HEADER
Display all users and their attributes with:
\du
select * from current_user;
To create a user james_smith that is valid until the end of September 2026, run the below. Note that the username
should not be in quotations while the password should be in single quotations. Also, note to that you need to replace
jw8s0F4 with an appropriate password for james_smith:
CREATE ROLE james_smith WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2026-10-01';
To create a user james_smith with no expiration date, run this:
CREATE ROLE james_smith WITH LOGIN PASSWORD 'jw8s0F4';
To create a user james_smith that can manage other users (create and remove users) and is valid until the end of
September 2026, run the following:
CREATE ROLE james_smith WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2026-10-01' CREATEROLE;
To create a user james_smith that can manage other users and does not expire, run this:
CREATE ROLE james_smith WITH LOGIN PASSWORD 'jw8s0F4' CREATEROLE;
Change the password of the existing user james_smith with:
ALTER USER james_smith WITH PASSWORD 'hr9k7zg';
Change the date when the existing user james_smith expires, run this:
ALTER USER james_smith WITH VALID UNTIL '2030-10-01';
To give the existing user james_smith the permission to manage other users (create and remove other users), run this:
ALTER USER james_smith WITH CREATEROLE;
To remove the permission to manage other users of the existing user james_smith, run this:
ALTER USER james_smith WITH NOCREATEROLE;
If you want to give james_smith read-only permission to the DW, then add james_smith to the user group
data_warehouse_read_only by running:
GRANT data_warehouse_read_only TO james_smith;
If you want to give the user james_smith read and write permission to the DW, then add james_smith to the user group
data_warehouse_read_write by running the below:
GRANT data_warehouse_read_write TO james_smith;
To remove the existing user james_smith, run this:
DROP ROLE james_smith;
TODO
TODO