PostgreSQL (also known as Postgres) is an open source relational database management system built on SQL (Structured Query Language). We are using Postgres to store sensor data in tables; each row will contain readings from each of the sensors taken at a unique timestamp.
The database schema for storing sensor data is as follows:
CURRENT_TIMESTAMP : the current timestamp (automatically generated and used as each row’s primary key)
x_acc : the x-component from the linear acceleration reading
y_acc : the y-component from the linear acceleration reading
z_acc : the z-component from the linear acceleration reading
x_alpha : the x-component from the rotational velocity reading
y_alpha : the y-component from the rotational velocity reading
z_alpha : the z-component from the rotational velocity reading
pressure : the pressure/force reading
The SQL command that can be used to create the table is as follows:
CREATE TABLE arduino_data(created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP PRIMARY KEY, x_acc FLOAT(5), y_acc FLOAT(5), z_acc FLOAT(5), x_alpha FLOAT(5), y_alpha FLOAT(5), z_alpha FLOAT(5), pressure INT); Integrating PostgreSQL and Google Cloud
We used Google Cloud’s relational database management system, Cloud SQL, to remotely host our Postgres database on the web.
The database instance can be accessed directly from the Google Cloud Console, but in order to store our sensor readings in the database, we created a REST API with Python and Flask to send local data from the microcontroller to the database on the web. This API will provide the functionality to receive sensor data from the microcontroller, prepare it for insertion into an SQL database, and then send the data to the database stored in Cloud SQL.
The first step to setting up a Postgres database on Google Cloud is to create a Cloud SQL instance. An instance is an allocation of computing, memory, and storage resources provided by Google that we can use to store our database and run our workloads. After the instance is created, an empty Postgres database can be created in the schema.
References
https://cloud.google.com/sql
https://www.postgresql.org/about/
https://stackoverflow.com/questions/12035998/what-do-instances-mean-in-terms-of-cloud-computing
댓글