Boda Lite: Writing Queries

Before we explain how to write queries in Boda, we need to know some relatively “new” concepts (at least for the “normal” guys). :wink:

Introducing some new concepts

You probably know something about SQL.

For example, when you have a table in a database (for example Table1), and want to see all its records, you can write something like:

Select * from Table1

image

Pretty simple, right?

But there’s something you may like to know. SQL is not the most appropriate language to deal with Big Data. And you’ve probably heard about Big Data, and all the revolution about to come - IoT, 5G and so on.

Without extra information (it would require a lot of study), the future database will probably of NoSQL type.
And we want to be ready for this future - it’s closer than you think ;).

Althought we are not currently using NoSQL database in Boda, we are using this approach!

And this introduction was needed because at first, Non SQL seems very hard to understand. It uses JSON (wow, now we’re getting super complicated!).

Ok, let’s forget about NoSQL, JSON and other new promising concepts for future.

But we do need to know the basic.

Instead of this:

Select * from Table1
image

We need to start thinking like this:

SELECT data::text FROM "Table1"
image

It is the same “Table1”, but now stored “NoSQL” way, as JSONB.

With the new JSONB format, introduced in PostgreSQSL 9.4, we have all the power of non-relational databases in PostgreSQL itself. With it we can perform faster and simpler searches.
That’s why NoSQL databases are recommended for big data applications and real-time analytics.

Of course it is not our intention here to explain all the differences, benefits, etc… of relational database (SQL) versus a non-relational (NoSQL) database.
Also, this is not so simple to write NoSQL queries, but together we can. So let’s go for it.

Writing Queries

Now we can start, and see how to write queries in Boda.

Let’s consider the CM module. All imported data are stored in tables using the format: vendor_cm.MONAME

The tables have 3 fields/columns: id , load_time and data.

  • id: Index (don’t worry about this for now)
  • load_time: contains the time data was loaded into database (also don’t worry about it now)
  • data: a column that holds the data in JSON format.

View database size

We can start with a simple query: to check database size.

  • SELECT pg_size_pretty(pg_database_size('boda'))

View all data from one table

To see all fields available in a table, you can …
TBD

View all data from Huawei UCELL MO

  • SELECT data::text FROM huawei_cm."UCELL"

View only CELLNAME and CELLID from Huawei UCELL MO

  • SELECT data->>'CELLNAME' AS "CELLNAME", data->>'CELLID' AS 'CELLID" FROM huawei_cm."UCELL"

View (to be continued)

Samples

Built-in Queries

Ok, maybe you’re scared with this new concept. And just would like to install… and use Boda!

Good news: we have a solution for you. If you don’t want to write query - not the best thing in the World :wink: - you can use the built-in queries already available.

(to be continued)

Now you may want to go to: Boda: Key Parameters

Resources