The latest installment for our SQL-on-Hadoop hands-on lab focuses on two popular topics: ¬†security and performance.
Lab 7 introduces you to fine-grained access control mechanisms available to Big SQL administrators. ¬†You’ll see how you can define roles, assign these roles to users, and associate row- and column-based data access restrictions with these roles. ¬†For example, one exercise shows you how to define a column mask that hides gross profit information from all users who don’t possess a MANAGER role. ¬†After doing so, all users with SELECT privileges will be able to query the table, but information in the GROSS_PROFIT column will display as 0.0 unless the user was granted the role of a MANAGER. ¬†Any user who is a MANAGER will be able to see the underlying data values for GROSS_PROFIT. ¬†Another exercise enables you to explore row-based permissions. ¬†In this case, users with a CONSULT role are permitted to see only rows related to a specific retailer ID. ¬†Other rows are automatically filtered from the query result set for such users.
Lab 8 introduces you to two performance-related features: ¬†statistics collection and data access plans. ¬†In particular,¬†¬†you‚Äôll learn how to use the ANALYZE TABLE command to collect statistics about your data so that the Big SQL query optimizer can more accurately estimate the costs of various data access plans. ¬†this, in turn, helps the optimizer select an efficient plan. ¬†The second part of this lab introduces you to the¬†Big SQL EXPLAIN feature, which enables you to examine the data access plan the query optimizer chose for a given query. ¬†In doing so, you’ll see Big SQL query rewrite technology in action. ¬†In particular, you’ll see how the Big SQL query engine can transparently rewrite a submitted query into a logical equivalent that is more efficient to execute. ¬†The example shown in this lab eliminates an unnecessary sort operation. ¬†In the world of Big Data, eliminating unnecessary work can significantly improve runtime query performance.
Both of these labs build on a sample database environment involving FACT and DIMENSIONS tables, which you can easily create following instructions included in labs that I blogged about earlier. ¬† Together, Labs 7 and 8 should take about an hour of your time — a modest time investment, given the importance many organizations place on SQL-related security and performance topics. ¬†So why not get started?
Of course, if you want to read more about BigInsights and Big SQL 3.0, you can always visit the product¬†Knowledge Center, which contains the official IBM documentation, including ample¬†reference materials¬†and¬†tutorials¬†on key tasks.