Welcome to the eighth installment of the Learn to Build Better series. This multi-part series showcases the tools and techniques to rapidly build, test, and prototype energy-focused applications, analytics and use cases on the Awesense Energy Transition Platform, using the AI Data Engine to process data, and TGI or APIs to access and visualize the data structured according to the Awesense open Energy Data Model (EDM). This episode covers SQL clients, the unsung heroes in managing and querying databases and a crucial aspect in handling the vast data landscapes of the energy sector.
What are SQL Clients, and why do we use them?
SQL Clients are essential for direct interaction with databases, providing a straightforward way to query and manage data. They offer a granular level of control and simplicity, which is beneficial before transitioning to more advanced data analytics tools. By mastering SQL Clients, individuals can better understand the data structure, which is crucial for accurate analytics. Awesense’s EDM APIs offer a SQL interface option, so being able to connect SQL Client to this interface is an important ability.
Join us as we explore the realm of SQL Clients, spotlighting tools like pgAdmin, psql, and SQuirreL, and delving deeper into the versatile world of DBeaver. Here are their descriptions at a glance:
| Tool | Interface | Database Support | Primary Features |
| pgAdmin | Provides a user-friendly graphical interface, making it easier for beginners. | Primarily designed for managing PostgreSQL databases. | Offers a range of database interactions, SQL querying, and an intuitive dashboard for monitoring database performance. |
| psql | Preferred by users comfortable with command-line interfaces. | A terminal-based front-end to PostgreSQL. | Simplistic, efficient, and allows direct SQL execution, data import/export, and scripting. |
| DBeaver | Provides a robust graphical interface for database management. | A universal database tool with support for all popular databases. | Offers SQL editing, data export/import, ER diagrams, and more, making it a versatile tool for database administrators and developers. |
| SQuirreL | Features a graphical interface for database interaction. | A universal SQL client that supports various databases via JDBC drivers. | Extensibility through plugins, robust querying and data exploration capabilities, integration with various databases and tools, and monitoring and performance tuning features. |
In summary, pgAdmin and psql cater more towards PostgreSQL databases, with pgAdmin being more beginner-friendly due to its graphical interface, while psql is for those comfortable with the command-line interface. On the other hand, DBeaver and SQuirreL are universal SQL clients with graphical interfaces that support a wide range of databases, making them more versatile for varied database environments. All the GUI tools can feel overwhelming upon opening them for the first time, given their interfaces are packed not just with data querying capabilities but also with features for database designers and managers; the latter are not needed for analysts and data scientists simply building on top of the Awesense EDM SQL APIs. So let’s start with looking more closely at psql.
psql – Fastest Route to Data Querying
Start by opening a command line terminal. If you have ever installed PostgreSQL on your computer, then you probably already have psql. You can check that with the following command:
psql --version
If you do not have it yet, there are plenty of instructions on the internet on how to install it without having to install all of PostgreSQL (e.g. here).
Once you have psql installed, connect to the Awesense server you’ve been granted access to with the following command:
psql -U <username> -d <edm_database_name> -h <server>-edm.awesense.com
You’ll be prompted to enter the password for the specified username after you execute the command. Then you’ll be presented with an interactive prompt, and you’re good to go for running SQL queries and commands to explore the schema and retrieve data. Below are a few examples. First, the handy “\d” command for seeing available tables & views and details about their structure:
edm=> \d
List of relations
Schema | Name | Type | Owner
--------+--------------------------+-------+----------
ext | geography_columns | view | rdsadmin
ext | geometry_columns | view | rdsadmin
ext | spatial_ref_sys | table | rdsadmin
public | data_version_log | view | awesense
public | grid | view | awesense
public | grid_element | view | awesense
public | grid_element_data_source | view | awesense
(7 rows)edm=> \d grid_element
View "public.grid_element"
Column | Type | Collation | Nullable | Default
--------------------------+-------------------------+-----------+----------+---------
grid_id | text | | |
grid_element_id | text | | |
type | text | | |
customer_type | text | | |
phases | character varying(3) | | |
is_underground | boolean | | |
is_producer | boolean | | |
is_consumer | boolean | | |
is_switchable | boolean | | |
switch_is_open | boolean | | |
terminal1_cn | text | | |
terminal2_cn | text | | |
power_flow_direction | text | | |
upstream_grid_element_id | text | | |
geometry | geometry(Geometry,4326) | | |
meta | jsonb | | | edm=> SELECT DISTINCT(type) FROM grid_element;
Next, here’s a SELECT example to retrieve the list of types of grid elements present in the system:
type
----------------
CircuitBreaker
Busbar
Meter
Disconnector
Cabinet
Pole
Battery
Switch
Substation
Jumper
Photovoltaic
ACLineSegment
EVCharger
Transformer
Fuse
(15 rows)For ideas on SQL queries, how-tos, and best practices, we recommend visiting the Awesense EDM-Examples GitHub repository (ask us for access), where Awesense collects many jupyter notebooks with SQL queries.
To exit the interactive prompt, use either the \q command or the `Ctrl + D` key combination.
psql can also be used in non-interactive mode, which can be useful when wanting to retrieve large amounts of data without displaying them on the screen, but routing them elsewhere, for instance, to a file. You can do this at your main command line terminal as follows:
psql -U <username> -d <edm_database_name> -h <server>-edm.awesense.com -c "SELECT * FROM grid_element WHERE type = ‘EVCharger’;" > evchg.csv
DBeaver – Community Cross-Platform GUI Hero
DBeaver is a highly adaptable SQL client praised for its intuitive graphical interface that simplifies database management and SQL querying. Its strong support for SQL editing, highlighted by syntax assistance and auto-completion, bolsters productivity and reduces the likelihood of errors. The software’s open-source status and cross-platform availability make it an accessible and flexible choice for various IT infrastructures. Notably, DBeaver’s geospatial visualization capabilities offer valuable insights when working with spatial data, which is a critical aspect of the Awesense EDM’s schema.
DBeaver’s wide-ranging features and strong community support make it a go-to SQL client for analysts and developers. When connected to the Awesense EDM SQL API, it makes it especially appealing for the energy sector and energy data analysis and management.
Following is a step-by-step guide on how to connect DBeaver to the Awesense EDM SQL API. After downloading and installing the DBeaver client, click New Database Connection and choose PostgreSQL connection. On the next window, populate your login information which you obtained from your Awesense representative (image below).
After you successfully tested the connection, click next, and your connection to the Awesense EDM SQL API is successfully created.
Next, we will execute some queries using the DBeaver client. Let us choose a query that will return the closest number of transformers from the desired consumer meter. To get such insight, it is first necessary to open the SQL script editor.
After the script editor is open, we can write down the query. The example query is a query returning three geospatially closest transformers to the desired meter (meter m_10).
After executing the query, we can explore the output in the bottom pane of the DBeaver. A beneficial feature of the DBeaver is its geospatial browser, which can be handy for geospatial analytics like the above query example. The geospatial browsing example is below (meter in blue, closest transformers in red).
Summary
In this Build Better blog series post, we explained SQL Client tools and what they are. We demonstrated how to connect to Awesense’s EDM SQL API using the psql and DBeaver SQL clients. We showed how to utilize DBeaver’s geospatial visualization to understand the output of the queries better.
Free For a Chat?
We love to connect with our wide audience and would love for you to share our content! Follow along with this series and let us know what ideas you would like to see us write about. Whether it’s more content about the topics we’ve already written on or even a specific use case or tool you would like to know more about, let us know. If you or your team are interested in building a custom application or use case using the Awesense Energy Transition Platform, or you have an analytical tool you would like us to demonstrate with our platform, please feel free to reach out at tools@awesense.com.
