Tutorial | DBeaver SQL Client - #2 How to search, filter and SQL query

lpessin(61)

Published in

#utopian-io

Words

924

Reading

5 min

Listen

Play

In my previous tutorial about DBeaver SQL Client you've learned how to install and connect with SteemSQL

In this tutorial I'll show how to use DBeaver to do basic searches, filter content, generate and run SQL queries and export the results.

Again I'm using SteemSQL Data Base as an example, since we have the SteemSQL Date Base running a lot of steemians started to do analysis with these data so I think is very useful.

Here is the link of part #1 DBeaver tutorial:
#1:Tutorial | DBeaver SQL Client - #1 How to Install and Connect with SteemSQL

Let's begin, open DBeaver:

If you followed the steps in part #1 you should get this screen above.

For this tutorial I'll search and filter information in the Data Base with the objective to produce analysis over one subject.

Let's say I want to do one over account creations in Steemit:

Searching
The easiest way to search is using the 'Data Base Navigator' on your left panel:

As I pointed before all data for steem is stored in the 'dbo' file, select 'dbo'. Select 'views' to see the tables inside. We are looking for accounts creations, this info is stored at 'TxAccountCreates' , select. You can see all columns that 'TxAccountCreates' stores, and the data type of each one.
-Double-click 'TxAccountCreates'

Now let's take a look at the data inside this table:

Click 'Data' tab. And wait till it loads

All columns were feed with the data:

You can notice that Dbeaver only fetched 200 rows. This panel is mostly to check the content before apply any filters, check data type, how many inputs and so...

Let's check how many rows are in this table, witch will give us the total number of account creations so far:

Right side of the panel, Click 'Calculate total row count'. This action will not feed the columns with all rows, only calculate them.

This action, and most actions we perform in the 'Data' tab, may take a few moments to load:

Always wait to complete before performing another action or you'll get errors. Click 'cancel' if you want to stop it
Result:

542.299 rows. Or 542.299 accounts creations.

How to filter Data
OK, we find the data that we were looking for. Now let's say I want to analyse accounts creations only in a period of time. For that I'll need to apply filters! To keep it simple I'll seek for accounts creations in 2018 so far:
The column that stores the date of an account creation is 'timestamp'

Right mouse click on 'timestamp' > 'Order / Filter' > 'timestamp >...'
Input filter parameter:

As the data type for 'timestamp' is 'datetime' we need to put in this format above.

Result:
Notice at top right that DBeaver created one command line for this action we performed. This is the SQL criteria for the filter we just did. Keep that in mind, we are going to use this line later when dealing with SQL queries

At bottom left we can see how many rows are inside our filter parameter for this column - 11.287, witch means, that's the number of accounts created in 2018 so far!
Nice we got what we were looking for!

Now we need to transfer that result to an 'Office' software in order to produce charts and analysis.
To do that let's use the 'Advanced Copy' DBeaver feature:

First, we need to fetch all rows in:

Click 'Fetch all rows' and wait till it loads (may take a few seconds)

Result:

Use Multiple Select (hold 'CTRL' + mouse click) on columns to be copied:
Here I'm selecting only 'delegation', 'creator', 'new_account_name' and 'timestamp' columns

Right mouse click > 'Advanced Copy' > 'Advanced Copy'

Click 'OK'

Go to your prefer 'Office' software:

Rigth mouse click 'Paste'

Here I'm using 'LibreOffice':

Click 'OK'

Result:

Done. Now you can work with it, produce charts and make conclusions on what you got.

Working with SQL queries
As I pointed before, the actions we perform in 'Data' tab creates SQL lines.
Let's go back to our 'Data' tab to check it out:

This is the line for our filter criteria, but is not the whole SQL query.

Single Click on 'TxAccountCreates':
Now you can see the whole query

To open the SQL Editor Double Click on 'TxAccountCreates'

SQL Editor:

Here it is our SQL Editor with our SQL query filtering 'timestamp'. To run the script Click 'Execute SQL statement'

Result:

We got the data but I want to gather only 'delegation', 'creator', 'new_account_name' and 'timestamp' columns

Let's do that by typing a code line in the SQL Editor script:

As you can see here DBeaver will help with that giving you suggestions and settings. When I started to write 'delegation' he auto completed for me with the columns names available for this query.