Lab 3: Using the Big SQL command line interface (JSqsh)

 
BigInsights supports a command-line interface for Big SQL through the Java SQL Shell (JSqsh, pronounced “jay-skwish”).  JSqsh is an open source project for querying JDBC databases. In this section, you will learn how to
 

  • Launch JSqsh.
  • Issue Big SQL queries.
  • Issue popular JSqsh commands to get help, retrieve your query history, and perform other functions.

 
Allow 30 minutes to complete this section. To work through these exercises, you must have a working BigInsights environment, as described in the first lab in this series.
 
Please post questions or comments about this lab to the forum on Hadoop Dev at https://developer.ibm.com/answers?community=hadoop.
 

3.1. Understanding JSqsh connections

 
To issue Big SQL commands from JSqsh, you need to define a connection to a Big SQL server.
 
__1.          On a node in your cluster in which JSqsh has been installed, open a terminal window.
 
__2.          Launch the JSqsh shell.  If JSqsh was installed at its default location of  /usr/ibmpacks/common-utils/jsqsh/2.14, you would invoke the shell with this command:
 
/usr/ibmpacks/common-utils/jsqsh/2.14/bin/jsqsh
 
__3.          If this is your first time invoking the shell, a welcome screen will display.  A subset of that screen is shown below.
 
2015-08-19 13_51_18-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word
 
__4.          If prompted to press Enter to continue, do so.
 
__5.          When prompted, enter c to launch the connection wizard.
 
2015-08-19 13_58_47-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word
 
In the future, you can enter \setup connections in the JSqsh shell to invoke this wizard.
 
__6.          Inspect the list of drivers displayed by the wizard, and note the number for the db2 driver (not the db2zos driver).  Depending on the size of your command window, you may need to scroll up to see the full list of drivers.  In the screen capture below, which includes a partial list of available drivers, the correct DB2 driver is 1. The order of your drivers may differ, as pre-installed drivers are listed first.
 
2015-08-19 14_02_18-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word

 

 2015-08-19 14_07_59-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word About the driver selection   You may be wondering why this lab uses the DB2 driver rather than the Big SQL driver.  In 2014, IBM released a common SQL query engine as part of its DB2 and BigInsights offerings.  Doing so provided for greater SQL commonality across its relational DBMS and Hadoop-based offerings.  It also brought a greater breadth of SQL function to Hadoop (BigInsights) users.  This common query engine is accessible through the “DB2” driver listed.  The Big SQL driver remains operational and offers connectivity to an earlier, BigInsights-specific SQL query engine.  This lab focuses on using the common SQL query engine.

 

__7.          At the prompt line, enter the number of the DB2 driver.
 
__8.          The connection wizard displays some default values for the connection properties and prompts you to change them.  (Your default values may differ from those shown below.)
 
2015-08-19 14_11_06-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word
 
__9.          Change each variable as needed, one at a time. To do so, enter the variable number and specify a new value when prompted. For example, to change the value of the password variable (which is null by default)
 
(1)                                         Specify variable number 5 and hit Enter.
 
(2)                                         Enter the password value and hit Enter.
 
2015-08-19 14_11_35-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word
 
(3)                                         Inspect the variable settings that are displayed again to verify your change.
 
Repeat this process as needed for each variable that needs to be changed. In particular, you may need to change the values for the db (database), port, and server variables.
 
After making all necessary changes, the variables should reflect values that are accurate for your environment.  In particular, the server property must correspond to the location of the Big SQL Head Node in your cluster.
 
Here is an example of a connection created for the bigsql user account (password bigsql) that will connect to the database named bigsql at port 51000 on the bdvs1052.svl.ibm.com server.
 
2015-08-19 14_12_00-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word
 

 2015-08-19 14_12_25-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word The Big SQL database name is defined during the installation of BigInsights.  The default is bigsql.  In addition, a Big SQL database administrator account is also defined at installation. This account has SECADM (security administration) authority for Big SQL. By default, that user account is bigsql.

 
__10.       When prompted, enter t to test your configuration.
 
__11.       Verify that the test succeeded, and hit Enter.
 
2015-08-19 14_14_52-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word
 
__12.       Save your connection.  Enter s, name your connection bigsql, and hit Enter.
 
2015-08-19 14_22_09-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word
 
__13.       Finally, quit the connection wizard when prompted.  (Enter q.)
 

 2015-08-19 14_22_38-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word You must resolve any connection errors before continuing with this lab.  If you have any questions, visit Hadoop Dev (https://developer.ibm.com/hadoop/) and review the product documentation or post a message to the forum.

 

3.2.          Getting help for JSqsh

 
Now that you’re familiar with JSqsh connections, you’re ready to work further with the shell.
 
__1.          From JSqsh, type \help to display a list of available help categories.
 
2015-08-19 14_22_59-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word
 
__2.          Optionally, type \help commands to display help for supported commands.  A partial list of supported commands is displayed on the initial screen.
 
2015-08-19 14_23_29-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word
 
Press the space bar to display the next page or q to quit the display of help information.

3.3.          Executing basic Big SQL statements

In this section, you will execute simple JSqsh commands and Big SQL queries so that you can become familiar with the JSqsh shell.
 
__1.          From the JSqsh shell, connect to your Big SQL server using the connection you created in a previous lesson.  Assuming you named your connection bigsql, enter this command:
 
\connect bigsql
 
__2.          Type \show tables -e | more to display essential information about all available tables one page at a time.  If you’re working with a newly installed Big SQL server, your results will appear similar to those below.
 
__3.          Next, cut and paste the following command into JSqsh to create a simple Hadoop table:
 
create hadoop table test1 (col1 int, col2 varchar(5));
 
Because you didn’t specify a schema name for the table it was created in your default schema, which is the user name specified in your JDBC connection. This is equivalent to
 
create hadoop table yourID.test1 (col1 int, col2 varchar(5));
 
where  yourID  is the user name for your connection.  In an earlier lab exercise, you created a connection using the bigsql user ID, so your table is BIGSQL.TEST1.
 

 2015-08-19 14_07_59-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word We’ve intentionally created a very simple Hadoop table for this exercise so that you can concentrate on working with JSqsh.  Later, you’ll learn more about CREATE TABLE options supported by Big SQL, including the LOCATION clause of CREATE TABLE. In these examples, where LOCATION is omitted, the default Hadoop directory path for these tables are at /…/hive/warehouse/<schema>.db/<table>.Big SQL enables users with appropriate authority to create their own schemas by issuing a command such ascreate schema if not exists testschema;Authorized users can then create tables in that schema as desired.  Furthermore, users can also create a table in a different schema, and if it doesn’t already exist it will be implicitly created.

 
__4.          Display all tables in the current schema with the  \tables command.
 
\tables
 
This screen capture was taken from an environment in which only 1 Big SQL table was created (BIGSQL.TEST1).
 
__5.          Optionally, display information about tables and views created in other schemas, such as the SYSCAT schema used for the Big SQL catalog.  Specify the schema name in upper case since it will be used directly to filter the list of tables.
 
\tables -s SYSCAT
 
Partial results are shown below.
 
__6.          Insert a row into your table.
 

insert into test1 values (1, 'one');

 

 2015-08-19 14_07_59-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word This form of the INSERT statement (INSERT INTO … VALUES …) should be used for test purposes only because the operation will not be parallelized on your cluster.  To populate a table with data in a manner that exploits parallel processing, use the Big SQL LOAD command, INSERT INTO … SELECT FROM statement, or CREATE TABLE AS … SELECT statement.  You’ll learn more about these commands later.

 
__7.          To view the meta data about a table, use the  \describe  command with the fully qualified table name in upper case.
 
\describe BIGSQL.TEST1
 
__8.          Optionally, query the system for metadata about this table:
 

select tabschema, colname, colno, typename, length
from syscat.columns
where tabschema = USER and tabname= 'TEST1';

 

 2015-08-19 14_07_59-Big_SQL4.0_HoL_2015-Aug-17.htm (Last saved by user) - Microsoft Word Once again, notice that we used the table name in upper case in these queries and \describe command.  This is because table and column names are folded to upper case in the system catalog tables.

 

You can split the query across multiple lines in the JSqsh shell if you’d like.  Whenever you press Enter, the shell will provide another line for you to continue your command or SQL statement.  A semi-colon or go command causes your SQL statement to execute.
 
In case you’re wondering, SYSCAT.COLUMNS is one of a number of views supplied over system catalog tables automatically maintained for you by the Big SQL service.
 
__9.          Issue a query that restricts the number of rows returned to 5.  For example, select the first 5 rows from SYSCAT.TABLES:
 
select tabschema, tabname from syscat.tables fetch first 5 rows only;
 
Restricting the number of rows returned by a query is a useful development technique when working with large volumes of data.
 
__10.       Leave JSqsh open so you can explore additional features in the next section.
 

3.4.          Optional:  Exploring additional JSqsh commands

If you plan to use JSqsh frequently, it’s worth exploring some additional features. This optional lab shows you how to recall previous commands, redirect output to local files, and execute scripts.
 
__1.          Review the history of commands you recently executed in the JSqsh shell.  Type \history and Enter. Note that previously run statements are prefixed with a number in parentheses.  You can reference this number in the JSqsh shell to recall that query.
 
__2.          Enter !! (two exclamation points, without spaces) to recall the previously run statement.  In the example below, the previous statement selects the first 5 rows from SYSCAT.TABLES. To run the statement, type a semi-colon on the following line.
 
__3.          Recall a previous SQL statement by referencing the number reported via the \history command. For example, if you wanted to recall the 4th statement, you would enter !4.  After the statement is recalled, add a semi-column to the final line to run the statement.
 
__4.          Experiment with JSqsh’s ability to support piping of output to an external program.  Enter the following two lines on the command shell:
 
select tabschema, tabname from syscat.tables
 
go | more
 
The go statement in the second line causes the query on the first line to be executed.  (Note that there is no semi-colon at the end of the SQL query on the first line.  The semi-colon is a Big SQL short cut for the JSqsh go command.)  The | more clause causes the output that results from running the query to be piped through the Unix/Linux more command to display one screen of content at a time.  Your results should look similar to this:
 
Since there are more than 400 rows to display in this example, enter q to quit displaying further results and return to the JSqsh shell.
 
__5.          Experiment with JSqsh’s ability to redirect output to a local file rather than the console display.    Enter the following two lines on the command shell, adjusting the path information on the final  line as needed for your environment:
 

select tabschema, colname, colno, typename, length
from syscat.columns
where tabschema = USER and tabname= 'TEST1'
go > $HOME/test1.out

 
This example directs the output of the query shown on the first line to the output file test1.out in your user’s home directory.
 
__6.          Exit the shell:
 
quit
 
__7.           From a terminal window, view the output file:
 
cat $HOME/test1.out
 
__8.          Invoke JSqsh using an input file containing Big SQL commands to be executed.  Maintaining SQL script files can be quite handy for repeatedly executing various queries.
 
__a.                               From the Unix/Linux command line, use any available editor to create a new file in your local directory named test.sql.   For example, type
 
vi test.sql
 
__b.                               Add the following 2 queries into your file
 
select tabschema, tabname from syscat.tables fetch first 5 rows only;
select tabschema, colname, colno, typename, length
from syscat.columns
fetch first 10 rows only;
 
__c.                               Save your file (hit ‘esc’ to exit INSERT mode then type :wq) and return to the command line.
 
__d.                               Invoke JSQSH, instructing it to connect to your Big SQL database and execute the contents of the script you just created.  (You may need to adjust the path or user information shown below to match your environment.)
 
/usr/ibmpacks/bigsql/4.0/jsqsh/bin/jsqsh bigsql < test.sql
 
In this example, bigsql is the name of the database connection you created in an earlier lab.
 
__e.                               Inspect the output.  As you will see, JSQSH executes each instruction and displays its output. (Partial results are shown below.)
 
__9.          Finally, clean up the your database.  Launch the JSqsh shell again and issue this command:
 
drop table test1;
 
There’s more to JSqsh than this short lab can cover.  Visit the JSqsh wiki (https://github.com/scgray/jsqsh/wiki) to learn more.

Join The Discussion

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