Difference between revisions of "Databases Manager Tutorial"

From Gcube Wiki
Jump to: navigation, search
(Submitting Queries)
 
(12 intermediate revisions by 2 users not shown)
Line 27: Line 27:
 
* Smart Sampling
 
* Smart Sampling
 
* Random Sampling
 
* Random Sampling
 +
 +
==== Table Sampling====
 +
 +
Sampling executes a Select over all the columns of a DB'sTable with limit on the row number. It adds the possibility to visualise the result and download it as .csv file.
 +
As previously stated there are 3 types of sampling available:
 +
 +
* Sampling: a standard table sampling, which retrieves the first 100 rows of the table
 +
* Smart Sampling: a sampling strategy that takes 100 rows of the table, at the same time maximising the number of non-empty columns. This process aims at reporting the most significant rows in the table
 +
* Random Sampling: 100 randomly taken rows of the table
 +
 +
[[File:DBManager 3.png | 800px]]
 +
 +
'''Fig. 3''' - A Screenshot of the Databases Manager WebApp showing a result of a Table Sampling.
 +
 +
==== Submitting Queries ====
 +
 +
Select the DB and click on Submit Query on your top-left corner. This process submits the user's query to the database. It allows executing read-only queries and will alert the user if the query is forbidden because it could alter the database.
 +
 +
The query should be expressed in the native language of the database. For example, a query for a Postgres DB should be executed using the SQL dialect of Postgres. The query could also be expressed in SQL standard.
 +
 +
Should the user not be familiar with the DB dialect, an optional checkbox allows applying "''smart corrections''"; this executes an automatic translation algorithm that transforms the query from one SQL dialect to the dialect required by the queried database.
 +
 +
The smart-corrections process autonomously detects the SQL dialect in which the query is written and translates it into the destination dialect. Behind the scenes, the system applies a statistical algorithm, thus the correctness of the translation is not guaranteed.

Latest revision as of 09:50, 24 June 2015

The Databases Manager WebApp allows you to Submit Queries (and therefore retrieve information) to any of the Relational Databases registered in the Infrastructure, if you are entitled to.

DBManager 1.png

Fig. 1 - A Screenshot of the Databases Manager WebApp showing the available Databases in the Infrastructure on the left.

Databases Resources

The Databases Resources panel shows the Databases (DBs) available in the infrastructure. By clicking on one of the item in the panel (a DB) in the related "Database Information" panel the following info are shown:

  • Database Name
  • URL
  • Driver name
  • Dialect Name

Fig. 1 shows a DB named "testdb" opened in the Database Information Panel. Specifically a DB hosted in a PostgreSQL environment.

By clicking on a DB the SubmitQuery button gets activated. It is also possible to see the list of the table names present in a DB by clicking on the "Tables List" button located on the very top-left corner, see Fig. 2.

DBManager 2.png

Fig. 2 - A Screenshot of the Databases Manager WebApp showing the list of the available tables of a selected DB.

It is possible to select and work on a Table by clicking OK in the "Tables" window. The selected Table information are visualised into the same Database Information Panel. From here the following functionalities and related buttons get activated:

  • Table Details
    • Shows the details of the selected table in the Information Details panel (located under the Databases Information one) in terms of: Create Statement, Column Names, Number of rows;
  • Sampling
  • Smart Sampling
  • Random Sampling

Table Sampling

Sampling executes a Select over all the columns of a DB'sTable with limit on the row number. It adds the possibility to visualise the result and download it as .csv file. As previously stated there are 3 types of sampling available:

  • Sampling: a standard table sampling, which retrieves the first 100 rows of the table
  • Smart Sampling: a sampling strategy that takes 100 rows of the table, at the same time maximising the number of non-empty columns. This process aims at reporting the most significant rows in the table
  • Random Sampling: 100 randomly taken rows of the table

DBManager 3.png

Fig. 3 - A Screenshot of the Databases Manager WebApp showing a result of a Table Sampling.

Submitting Queries

Select the DB and click on Submit Query on your top-left corner. This process submits the user's query to the database. It allows executing read-only queries and will alert the user if the query is forbidden because it could alter the database.

The query should be expressed in the native language of the database. For example, a query for a Postgres DB should be executed using the SQL dialect of Postgres. The query could also be expressed in SQL standard.

Should the user not be familiar with the DB dialect, an optional checkbox allows applying "smart corrections"; this executes an automatic translation algorithm that transforms the query from one SQL dialect to the dialect required by the queried database.

The smart-corrections process autonomously detects the SQL dialect in which the query is written and translates it into the destination dialect. Behind the scenes, the system applies a statistical algorithm, thus the correctness of the translation is not guaranteed.