Exploring a database with the Database Explorer

You just learned how to connect to an RDBMS from PDI. Before beginning to work with the data stored in a database, it would be useful to be familiar with that database or to verify if the database contains all the data that you need. For this, Spoon offers a Database Explorer. There are several ways to access the explorer:

• Right-click on the connection in the Database connections list and select Explore in the contextual menu.
• Click on Explore in the Database Connection window.
• Go to the menu option, Tools | Database | Explore. In the window that shows up, select the database to explore and click on OK.

Whichever way you choose, the following window appears:

Database Explorer

When you open the Database Explorer, the first thing that you see is a tree with the different objects of the database. When you right-click on a database table, a contextual menu appears with several available options for you to explore that table. The following is a full list of options and their purpose:

Option	Description
Preview first 100	Returns the first 100 rows of the selected table, or all the rows if the table has less than 100. Shows all the columns of the table.
Preview x Rows	Same as the previous option but you decide the number of rows to show.
Row Count	Tells you the total number of records in the table.
Show Layout	Shows you the metadata for the columns of the table.
DDL | Use CurrentConnection	Shows you the DDL statement that creates the selected table.
DDL | Select Connection	Lets you select another existent connection, then shows you the DDL just like the previous option. In this case, the DDL is written with the syntax of the database engine of the selected connection.
View SQL	Lets you edit a SELECT statement to query the table.
Truncate Table	Deletes all rows from the selected table.
Data Profile	Collects and displays some statistics for the fields in the selected table: maximum or minimum values, averages, and so on.

As an example, let's try one of the options on the sample database:

1. Right-click on the actors table and select View SQL. The following SQL editor window appears:

Simple SQL editor

2. Modify the text in there so you have the following:

SELECT first_name, last_name
FROM actor

3. Click on Execute. You will see this result:

Previewing the result of a SELECT statement

4. Close the Preview window—the one that tells the result of the execution as well as the Results of the SQL statements window, which is the window that tells us the SQL statements are executed. Finally, close the Simple SQL editor window.
5. Click on OK to close the Database Explorer window.