Lab 8: Exploring Big SQL web tooling (Data Server Manager)

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.

8.1.          Launching the BigInsights Home and Big SQL web tooling

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.

 

2015-09-03 06_11_52-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__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.

 

2015-09-03 06_12_36-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__4.          Click the Ambari Hosts tab and expand the information about the nodes in your cluster to locate where Knox is running.

 

2015-09-03 06_14_18-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__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.

 

https://yourKnoxGatewayNode:8443/gateway/default/BigInsightsWeb/index.html#/welcome

https://yourKnoxGatewayNode:8443/gateway/default/BigInsightsWeb/index.html#/welcome
 
__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.

 

2015-09-03 06_16_45-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__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.)

 

2015-09-03 06_17_19-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

 

8.2.          Issuing queries and inspecting results

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.

 

2015-09-03 06_17_51-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__2.          If necessary, click Options at far right to expose a Database Connection menu.

 

2015-09-03 06_18_22-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__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.

 

2015-09-03 06_18_52-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__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.

 

2015-09-03 06_19_31-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__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.

 

2015-09-03 06_20_04-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__8.          Enlarge the result set pane by clicking on the icon in the upper right corner.

 

2015-09-03 06_20_38-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__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.

 

2015-09-03 06_21_06-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__10.       Reset your display.  Click the SQL Editor button in the menu at left.

 

2015-09-03 06_21_42-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

8.3.          Examining database metrics

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.

 

2015-09-03 06_22_45-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__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.

 

2015-09-03 06_23_18-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__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.)

 

2015-09-03 06_23_54-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__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.

 

2015-09-03 06_24_27-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__5.          Optionally, click the Learn More link in the upper right corner to expose online help information.

 

2015-09-03 06_24_59-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__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.

 

2015-09-03 06_25_22-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

 

__7.          Scroll through statements executed on your database.  Note that information is available regarding average CPU time, rows read, rows returned, etc.

 

2015-09-03 06_25_56-bigsql4-150421142453-conversion-gate02.pdf - Adobe Reader

Join The Discussion

Your email address will not be published. Required fields are marked *