As you saw in the previous lab, Big SQL provides web tooling to issue SQL statements and perform other functions.Â In addition, because Big SQL supports DB2-compliant JDBC and ODBC drivers, many popular query, analysis, and programming tools can work directly with Big SQL. Among these is SQuirrel SQL Client, a popular open source SQL tool.
After completing this lab, you will be able to:
- Configure SQuirrel SQL Client to connect to Big SQL.
- Execute Big SQL queries and inspect result sets.
- Create charts from your result sets.
Prior to beginning this lab, download and install the standalone SQuirrel SQL Client (http://www.squirrelsql.org/).Â Consult the SQuirrel SQL web site for instructions and download options.Â Examples in this lab are based on SQuirrel SQL 3.6 and presume you did not install the optional plugin for DB2 as part of the installation process.Â In addition, prior to starting this lab you need to have created and populated the sample tables presented in an earlier lab on Querying Structured Data.
Allow 30 – 45 minutes to complete this lab. Please post questions or comments about this lab to the forum on Hadoop Dev at https://developer.ibm.com/answers?community=hadoop.
To connect to Big SQL from SQuirrel SQL, you must create a JDBC driver specification and an alias (connection specification).Â This exercise takes you through the necessary steps.
__1. Â Â Â Â Â Â Â Â Download the Big SQL JDBC driver to the platform on which you have SQuirrel SQL Client installed.Â In particular, you must download the db2jcc4.jar file, which is typically found in the â€¦/db2/java subdirectory of your Big SQL home directory.Â (By default, db2jcc4.jar will be installed at /usr/ibmpacks/bigsql/4.0/db2/java.)Â Alternatively, you can download the IBM Data Server Driver for JDBC and SQLJ (JCC Driver) Version 10.5 directly from http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217.
__2. Â Â Â Â Â Â Â Â Launch SQuirrel SQL Client following the standard process for your environment.Â If necessary, consult the open source documentation for details.
__3. Â Â Â Â Â Â Â Â Click the vertical Drivers tab at left and note the list of potential JDBC data source drivers.Â (Ignore the red Xs, which indicate that additional information or JARs are needed.)
__4. Â Â Â Â Â Â Â Â Click the plus sign (+) in the upper left corner of the Drivers pane to add a new driver. Â A new menu will appear.
__7. Â Â Â Â Â Â Â Â In the pop-up menu, navigate to the local directory containing the Big SQL JDBC JAR file that you downloaded at the beginning of this exercise.Â Select the db2jcc4.jar file and click Open.
__8. Â Â Â Â Â Â Â Â Verify that the JAR file now appears in the Extra Class Path list.
__9. Â Â Â Â Â Â Â Â Supply values for remaining fields in this menu.Â In the Name field at top, enter Big SQL Driver.Â For the Example URL, enter the appropriate JDBC connection URL for your environment, such as jdbc:db2://myhost.ibm.com:51000/bigsql.Â For the Class Name (at bottom), enter com.ibm.db2.jcc.DB2Driver.Â Leave the Website URL field blank and click OK.
__10. Â Â Â Â Â To create a new connection to your Big SQL server based on this driver, click the vertical Alias tab at left.
__11. Â Â Â Â Â Click the plus sign (+) in the upper left corner of the Aliases pane to add a new alias (database connection).
__12. Â Â Â Â Â Enter appropriate connection information in the pop-up menu that appears.Â For the Name, enter Big SQL Lab Server.Â For the Driver, select the Big SQL driver you just created from the drop-down list.Â For the URL, verify that the appropriate JDBC URL for your environment was copied from the Example URL you defined in the driver specification (e.g., jdbc:db2://myhost.ibm.com:51000/bigsql). Â If not, edit the JDBC URL as needed. Finally, enter valid values for the User and Password fields (such as bigsql/bigsql).
__13. Â Â Â Â Â Click Test and Connect when prompted.Â Verify that your connection is successful.
__14. Â Â Â Â Â Click OK to save the new alias.
__15. Â Â Â Â Â In the Aliases pane, right mouse click on your new alias and select Connect.Â Review the connectivity information presented and select Connect again.
Now you’re ready to inspect the contents of your Big SQL database and execute queries.
In this exercise, you will explore the contents of your Big SQL database. Make sure that you created and populated the tables specified in the Querying Structured Data lab before proceeding.
__1. Â Â Â Â Â Â Â Â If necessary, connect to your Big SQL database following instructions in the previous exercise.
__2. Â Â Â Â Â Â Â Â Click the Objects tab associated with your Big SQL alias (connection).
__3. Â Â Â Â Â Â Â Â Note the list of schemas that appears in the pane at the far left.Â Expand the BIGSQL schema by clicking on the toggle icon just before its name.
__4. Â Â Â Â Â Â Â Â Explore the tables in this schema.Â (Click on the icon beside TABLE.)
__5. Â Â Â Â Â Â Â Â Click on one of the tables in the list, and in the pane at right, click on the Columns tab to display information about columns defined for this table. The example below displays column information for the BIGSQL.SLS_SALES_FACT table.
__6. Â Â Â Â Â Â Â Â Click on the Content tab to view a sampling of rows from this table.Â (A subset of the information in the BIGSQL.SLS_SALES_FACT table is shown below.)
__7. Â Â Â Â Â Â Â Â Explore the views in the SYSHADOOP schema; these views are based on system catalog data.Â In particular, display the contents of SYSHADOOP.HCAT_TABLES view, noting that information about each table’s INPUTFORMAT, TYPE, and other data is available.
In this exercise, you will 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.
__1. Â Â Â Â Â Â Â Â Â Click the SQL tab towards the top of your screen (next to the Objects tab).
__2. Â Â Â Â Â Â Â Â 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;
__4. Â Â Â Â Â Â Â Â Inspect the query results displayed in the lower pane.
__5. Â Â Â Â Â Â Â Â Optionally, click on the MetaData tab to review column names, data types, and other information about your result set.Â Use the horizontal scroll bar as needed to fully inspect the meta data available.
In this exercise, you will use built-in charting facilities to create a simple bar chart based on the results of a query. Â Your chart will enable you to visualize the popularity of different order methods for sales.
__1. Â Â Â Â Â Â Â Â In the SQL tab, change the Limit Rows value to 1000.
__2. Â Â Â Â Â Â Â Â Re-execute the query that you ran in the previous exercise.Â (If necessary, return to that exercise and copy the SQL statement into the tool.Â Then click on the run icon.)
__3. Â Â Â Â Â Â Â Â Click the Overview / Charts tab in the result set pane.
__4. Â Â Â Â Â Â Â Â Click Charts to display the menu for creating charts.
__5. Â Â Â Â Â Â Â Â Change the X axis value to the ORDER_METHOD_EN column.Â Accept the remaining default values and click Open chart window.
__6. Â Â Â Â Â Â Â Â Inspect the bar chart that appears in the pop-up window and note the relative popularity of each sales order method.
__7. Â Â Â Â Â Â Â Â Close the chart window.
Explore other capabilities of the SQuirrel SQL Client as desired, and close the tool when finished.
Congratulations!Â Youâ€™ve just learned many important aspects of Big SQL, IBMâ€™s query interface for big data.Â To expand your skills and learn more, enroll in free online courses offered by Big Data University (http://www.bigdatauniversity.com/) or work through free tutorials included in the BigInsights product documentation.Â The HadoopDev web site (https://developer.ibm.com/hadoop/) contains links to these and other resources.