1. Create a SQLite database by exporting the itp sensor_data from PostgreSQL. Follow the instructions to get started. Document the steps you took to create the database. A screenshot of the terminal or cmd prompt is fine. Make sure your database includes the person and device tables. Optionally create some views.

SQlite File:

devices.db

To create an sqlite database for the itp sensor data I used the following steps:

Create a new database (.db) file for importing the postgres datafile

nboss@Nicks-MacBook-Pro-466695 week4 % sqlite3 farm.db 
SQLite version 3.36.0 2021-06-18 18:58:49

Create table to import data to

sqlite> CREATE TABLE sensor_data (
   ...>     device TEXT,
   ...>     measurement TEXT,
   ...>     reading NUMERIC(6, 2),
   ...>     recorded_at TIMESTAMP NOT NULL default current_timestamp
   ...> );

Set the view mode to csv and import csv file exported from postgres

sqlite> .mode csv
sqlite> .import sensor_data.csv data_temp

Set a temporary database with the corresponding table schema

sqlite> .schema data_temp
CREATE TABLE IF NOT EXISTS "data_temp"(
  "id" TEXT,
  "device" TEXT,
  "measurement" TEXT,
  "reading" TEXT,
  "recorded_at" TEXT
);

Insert data into new database note the modification for timestamptz from datetime

sqlite> INSERT INTO sensor_data (device, measurement, reading, recorded_at) SELECT device, measurement, reading, datetime(recorded_at || ':00') FROM data_temp;

Set view mode to columns and enable headers

sqlite> .mode columns
sqlite> .headers on

Drop the temp dataset and then clean up memory space

sqlite> DROP TABLE data_temp;
sqlite> VACUUM;

Some queries

  1. Write some queries using your new SQLite database of ITP sensor data. Refer to the SQLite and PostgreSQL queries we went over in class. Experiment. Spend some time trying to use queries to answer questions with data.

Check to make sure that the data is correctly imported

sqlite> SELECT * FROM sensor_data LIMIT 10; 

device      measurement  reading  recorded_at        
----------  -----------  -------  -------------------
rootcellar  humidity     67       2019-11-28 07:46:28
rootcellar  temperature  44.1     2019-11-28 07:46:28
basement    humidity     53       2019-11-28 07:46:34
basement    temperature  53.8     2019-11-28 07:46:34
loft        humidity     70.8     2019-11-28 07:47:14
loft        temperature  41.72    2019-11-28 07:47:14
basement    humidity     53       2019-11-28 07:47:45
basement    temperature  53.8     2019-11-28 07:47:45
basement    humidity     53       2019-11-28 07:48:36
basement    temperature  53.8     2019-11-28 07:48:36

Create a view and check the weekly moving average temperature of kitchen.

sqlite> CREATE VIEW kitchen_temps AS SELECT * FROM sensor_data WHERE measurement LIKE 'temperature' AND device LIKE 'kitchen';
sqlite> SELECT *, AVG(reading) OVER(ORDER BY recorded_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as weekly_avg from kitchen_temps LIMIT 10;

device   measurement  reading  recorded_at          weekly_avg      
-------  -----------  -------  -------------------  ----------------
kitchen  temperature  73.22    2015-12-26 20:58:28  73.22           
kitchen  temperature  73.04    2015-12-26 20:59:28  73.13           
kitchen  temperature  73.04    2015-12-26 21:00:29  73.1            
kitchen  temperature  73.76    2015-12-26 21:02:31  73.265          
kitchen  temperature  71.6     2015-12-26 21:03:32  72.932          
kitchen  temperature  70.16    2015-12-26 21:04:32  72.47           
kitchen  temperature  69.26    2015-12-26 21:05:33  72.0114285714286
kitchen  temperature  68.72    2015-12-26 21:06:33  71.3685714285714
kitchen  temperature  68.18    2015-12-26 21:07:34  70.6742857142857
kitchen  temperature  67.82    2015-12-26 21:08:34  69.9285714285714

Relational Database Reflection