Big SQL provides web tools that enable you to inspect database metrics, issue SQL statements, and perform other functions. These tools are part of IBM Data Server Manager (DSM), a component you can install and configure on your Big SQL Head Node. This lab introduces you to a few features of DSM.
After completing this lab, you will know how to:
- Launch the BigInsights Home page and the Big SQL web tooling (DSM).
- Execute Big SQL queries and inspect result sets from DSM.
- Inspect metrics and monitoring information collected for your Big SQL database.
Prior to beginning this lab, you will need access to a BigInsights cluster in which BigInsights Home, Big SQL, DSM, and Ambari are running. You also need to have created and populated the sample tables presented in a prior lab on Querying Structured Data.
Allow 30 minutes to complete this lab.
For additional information about DSM capabilities, please visit IBM’s DSM site at http://www-03.ibm.com/software/products/en/ibm-data-server-manager. If you have questions or comments about this lab, please post them to the forum on Hadoop Dev at https://developer.ibm.com/answers?community=hadoop.
Big SQL web tooling is accessed through a link in the BigInsights Home page. BigInsights Home is a component provided with several IBM BigInsights offerings, including the Quick Start Edition, BigInsights Analyst, and BigInsights Data Scientist. In this exercise, you’ll verify that BigInsights Home and Big SQL web tooling (DSM) are installed and running on your cluster. Then you’ll launch the Home page and the web tooling.
__1. Launch Ambari and sign into its console. If necessary, consult an earlier lab for details on how to do this.
__2. From the Ambari Dashboard, inspect the list of services in the left pane. Verify that BigInsights Home and BigInsights – Big SQL services are running, as well as all pre-requisite services (e.g., HDFS, MapReduce2, Hive, and Knox).
__3. Click on the BigInsights – Big SQL service. Verify that all underlying components are running, including DSM.
__4. Click the Ambari Hosts tab and expand the information about the nodes in your cluster to locate where Knox is running.
__5. Launch BigInsights Home, providing the appropriate URL based on your installation’s configuration. Assuming you installed BigInsights with Knox and accepted default installation values, the BigInsights Home URL is similar to the link shown below. Substitute the location of the Knox gateway on your cluster for the italicized text in this example.
__6. When prompted, enter a valid user ID and password for the Knox gateway. (Defaults are guest / guest-password).
__7. Verify that BigInsights Home displays an item for Big SQL. Depending on the size of your browser window and other BigInsights components installed on your cluster, you may need to scroll through the BigInsights Home page to locate the Big SQL section.
__8. Click the Launch button in the Big SQL box. (Your screen may appear somewhat different than the image below when you launch the tool for the first time.)
In this exercise, you will work with the SQL Editor to execute a query and inspect the results. For simplicity, your query will be based on one that you already issued in a previous lab on Querying Structured Data. Make sure you created and populated the tables specified in that lab before proceeding.
__1. With the Big SQL web tooling launched, click the SQL Editor link at left.
__2. If necessary, click Options at far right to expose a Database Connection menu.
__3. For the Database Connection, leave NAME selected and use the drop-down arrow key in the box beside it to select BIGSQL as the database. Accept defaults for all other properties.
__4. When prompted, enter a valid user ID and password for your Big SQL database (e.g., bigsql / bigsql). Click OK.
__5. In the empty query box to the left of the database connection information, paste the following query:
-- Fetch the product name, quantity, and order method of products sold. -- Query 1 SELECT pnumb.product_name, sales.quantity, meth.order_method_en FROM sls_sales_fact sales, sls_product_dim prod, sls_product_lookup pnumb, sls_order_method_dim meth WHERE pnumb.product_language='EN' AND sales.product_key=prod.product_key AND prod.product_number=pnumb.product_number AND meth.order_method_key=sales.order_method_key;
__6. Click Run. Status information about the operation is shown in the lower pane.
__7. When the operation completes, adjust the size of the lower pane (if needed) and inspect the query results, a subset of which is shown below.
__8. Enlarge the result set pane by clicking on the icon in the upper right corner.
__9. Observe that you can see the full result set more easily and that you can page through its contents using buttons at the bottom of the page.
__10. Reset your display. Click the SQL Editor button in the menu at left.
Administrators can use the Big SQL web tooling to display metrics about their database and inspect the overall health of their environment. This exercise shows you how to launch this facility and begin exploring some metrics. For further details, consult the product Knowledge Center or online help information.
__1. With the Big SQL web tooling launched and your database connection active (based on your completion of the previous lab exercise), click the Monitor Database tab.
__2. Inspect the Overview information presented. (If necessary, click on the Overview tab.) Verify that a pie chart with your Database Time Breakdown and a set of Key Metrics are displayed. Note that specific data you see on your screen will vary from what’s shown below.
__3. Examine details about SQL execution and other aspects in the Database Time Breakdown section to determine which activities are consuming database resources. (Click the plus sign ‘+’ next to desired categories.)
__4. Investigate Key Metrics of your choice, such as CPU usage and average response time. Again, click the plus sign (+) to reveal details about an item.
__5. Optionally, click the Learn More link in the upper right corner to expose online help information.
__6. Explore the impact of recently executed SQL statements. Click the Statements tab (next to the Overview tab) and then click the Executed Statements tab beneath it.
__7. Scroll through statements executed on your database. Note that information is available regarding average CPU time, rows read, rows returned, etc.