使用 SQL 访问 IBM InfoSphere BigInsights – BigSQL 使用详解

 biadmin@imtebi1:/opt/ibm/biginsights/bin> $BIGINSIGHTS_HOME/bin/stop.sh bigsql 
 biadmin@imtebi1:/opt/ibm/biginsights/bin> $BIGINSIGHTS_HOME/bin/start.sh bigsql 
 biadmin@imtebi1:/opt/ibm/biginsights/bin> $BIGINSIGHTS_HOME/bin/status.sh bigsql

 biadmin@imtebi1:/opt/ibm/biginsights/bin> $BIGINSIGHTS_HOME/bin/stop-all.sh 
 biadmin@imtebi1:/opt/ibm/biginsights/bin> $BIGINSIGHTS_HOME/bin/start-all.sh

 biadmin@imtebi1:/opt/ibm/biginsights/bigsql>$BIGSQL_HOME/bin/jsqsh 
--user=biadmin --password=password --server localhost --port 7052 --driver=bigsql 

 JSqsh Release 1.5-ibm, Copyright (C) 2007-2013, Scott C. Gray 
 Type help for available help topics. Using JLine. 
 [localhost][biadmin] 1> select * from hbase_staff;

 biadmin@imtebi1:/opt/ibm/biginsights/bigsql/samples/queries> 
$BIGSQL_HOME/bin/jsqsh --user=biadmin --password=password --server localhost
 --port 7052 --driver=bigsql -i GOSALESDW_starSchemaJoin.sql

 biadmin@imtebi1:/opt/ibm/biginsights/bigsql/bin> ./jsqsh --setup

 biadmin@imtebi1:/opt/ibm/biginsights/bigsql/bin> ./jsqsh mybigsql

 biadmin@imtebi1:/opt/ibm/biginsights/bigsql/samples/queries>
 export CLASSPATH=$CLASSPATH:/opt/ibm/biginsights/bigsql/samples/queries/bigsql-jdbc-driver.jar

 countbrand.java 

 import java.io.*; 
 import java.sql.*; 
 import java.util.*; 
 class countbrand { 
 public static void main(String args[]) throws SQLException,Exception { 
 try { 
        //load the driver class 
        Class.forName("com.ibm.biginsights.bigsql.jdbc.BigSQLDriver"); 
        } catch (ClassNotFoundException e) { 
                System.out.print(e); } 
 try { 
        //set connection properties 
        String user="biadmin"; 
        String password="password"; 
        Connection con = DriverManager.getConnection("jdbc:bigsql://172.16.42.202:7052/gosalesdw",
        user,password); 
         Statement st = con.createStatement(); 
        //query execution 
        ResultSet rs = st.executeQuery("SELECT count(*) FROM GOSALESDW.GO_BRANCH_DIM AS BD, 
GOSALESDW.DIST_INVENTORY_FACT AS IF WHERE IF.BRANCH_KEY = BD.BRANCH_KEY /*+ joinMethod = 'mapSideHash',
 buildTable = 'bd' +*/ AND BD.BRANCH_CODE > 20"); 
        while(rs.next()) { 
                              System.out.println(rs.getString(1)); 
                         } 
     } catch(SQLException sqle) 
                           { System.out.print(sqle); } 
 } 
 }

 biadmin@imtebi1:/opt/ibm/biginsights/bigsql/samples/queries> javac countbrand.java 
 biadmin@imtebi1:/opt/ibm/biginsights/bigsql/samples/queries> java countbrand 
 33318

 biadmin@imtebi1:/opt/> $BIGSQL_HOME/bin/jsqsh -U biadmin -P password 
 JSqsh Release 1.5-ibm, Copyright (C) 2007-2013, Scott C. Gray 
 Type help for available help topics. Using JLine. 
 [localhost][biadmin] 1> create schema if not exists gosalesdw; 
 0 rows affected (total: 1m4.56s) 
 [localhost][biadmin] 1> quit; 
 biadmin@imtebi1:/opt/$HADOOP_HOME/bin/hadoop fs -ls /biginsights/hive/warehouse 
 drwxr-xr-x   - biadmin biadmgrp         
  0 2013-12-21 21:20 /biginsights/hive/warehouse/gosalesdw.db

 biadmin@imtebi1:/opt/> $BIGSQL_HOME/bin/jsqsh -U biadmin -P password 
 JSqsh Release 1.5-ibm, Copyright (C) 2007-2013, Scott C. Gray 
 Type help for available help topics. Using JLine. 
 [localhost][biadmin] 1> create schema if not exists gosalesdw1 location
 '/usr/biadmin/gosalesdw1.db'; 
 0 rows affected (total: 0.87s) 
 [localhost][biadmin] 1> quit 

 biadmin@imtebi1:/opt/> $HADOOP_HOME/bin/hadoop fs -ls /usr/biadmin 
 Found 1 items 
 drwxr-xr-x   - biadmin supergroup          0 2013-12-21 21:26 /usr/biadmin/gosalesdw1.db 

 more $HIVE_HOME/conf/hive-site.xml 

 hive.metastore.warehouse.dir 
  /biginsights/hive/warehouse

 USE GOSALESDW; 
 CREATE TABLE SLS_SALES_FACT ( ORDER_DAY_KEY int, ORGANIZATION_KEY int,
 EMPLOYEE_KEY int, RETAILER_KEY int, RETAILER_SITE_KEY int, PRODUCT_KEY int,
 PROMOTION_KEY int, ORDER_METHOD_KEY int, SALES_ORDER_KEY int, SHIP_DAY_KEY int, 
CLOSE_DAY_KEY int, QUANTITY int, UNIT_COST decimal(19,2),
 UNIT_PRICE decimal(19,2), UNIT_SALE_PRICE decimal(19,2), GROSS_MARGIN double,
 SALE_TOTAL decimal(19,2), GROSS_PROFIT decimal(19,2) ) ROW FORMAT DELIMITED FIELDS TERMINATED BY 't'; 

 CREATE TABLE SLS_SALES_ORDER_DIM ( SALES_ORDER_KEY int, 
ORDER_DETAIL_CODE int, ORDER_NUMBER int, WAREHOUSE_BRANCH_CODE int )
 ROW FORMAT DELIMITED FIELDS TERMINATED BY 't'; 

 --GOSALESDW.SLS_ORDER_METHOD_DIM 
 CREATE TABLE SLS_ORDER_METHOD_DIM ( ORDER_METHOD_KEY int, ORDER_METHOD_CODE int,
 ORDER_METHOD_EN varchar(180), ORDER_METHOD_DE varchar(180), ORDER_METHOD_FR varchar(180),
 ORDER_METHOD_JA varchar(180), ORDER_METHOD_CS varchar(180), ORDER_METHOD_DA varchar(180),
 ORDER_METHOD_EL varchar(180), ORDER_METHOD_ES varchar(180), ORDER_METHOD_FI varchar(180),
 ORDER_METHOD_HU varchar(180), ORDER_METHOD_ID varchar(180), ORDER_METHOD_IT varchar(180), 
ORDER_METHOD_KO varchar(180), ORDER_METHOD_MS varchar(180), ORDER_METHOD_NL varchar(180), 
ORDER_METHOD_NO varchar(180), ORDER_METHOD_PL varchar(180), ORDER_METHOD_PT varchar(180), 
ORDER_METHOD_RU varchar(180), ORDER_METHOD_SC varchar(180), ORDER_METHOD_SV varchar(180),
 ORDER_METHOD_TC varchar(180), ORDER_METHOD_TH varchar(180) )
 ROW FORMAT DELIMITED FIELDS TERMINATED BY 't';

 use gosalesdw; 
 CREATE EXTERNAL TABLE SLS_PRODUCT_DIM_EXT ( PRODUCT_KEY int, PRODUCT_LINE_CODE int, 
PRODUCT_TYPE_KEY int, PRODUCT_TYPE_CODE int, PRODUCT_NUMBER int, BASE_PRODUCT_KEY int, 
BASE_PRODUCT_NUMBER int, PRODUCT_COLOR_CODE int, PRODUCT_SIZE_CODE int, 
PRODUCT_BRAND_KEY int, PRODUCT_BRAND_CODE int, PRODUCT_IMAGE varchar(120),
 INTRODUCTION_DATE timestamp, DISCONTINUED_DATE timestamp ) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' stored as textfile 
 location '/biginsights/hive/warehouse/gosalesdw.db/sls_product_dim';

 use gosalesdw; 
 CREATE TABLE SLS_PRODUCT_DIM_1( PRODUCT_KEY int, PRODUCT_LINE_CODE int,
 PRODUCT_TYPE_KEY int, PRODUCT_TYPE_CODE int, PRODUCT_NUMBER int,
 BASE_PRODUCT_KEY int, BASE_PRODUCT_NUMBER int, PRODUCT_COLOR_CODE int, 
PRODUCT_SIZE_CODE int, PRODUCT_BRAND_KEY int, PRODUCT_BRAND_CODE int,
 PRODUCT_IMAGE varchar(120), INTRODUCTION_DATE timestamp,
 DISCONTINUED_DATE timestamp ) ROW FORMAT DELIMITED FIELDS TERMINATED 
BY 't' stored as textfile with hints (tablesize='small') ;

 use gosalesdw; 
 CREATE  TABLE SLS_PRODUCT_DIM_PART( PRODUCT_KEY int, PRODUCT_TYPE_KEY int,
 PRODUCT_TYPE_CODE int, PRODUCT_NUMBER int, BASE_PRODUCT_KEY int,
 BASE_PRODUCT_NUMBER int, PRODUCT_COLOR_CODE int, PRODUCT_SIZE_CODE int,
 PRODUCT_BRAND_KEY int, PRODUCT_BRAND_CODE int, PRODUCT_IMAGE varchar(120),
 INTRODUCTION_DATE timestamp, DISCONTINUED_DATE timestamp )PARTITIONED 
BY (PRODUCT_LINE_CODE int) ROW FORMAT DELIMITED FIELDS TERMINATED BY 't'; 

 biadmin@imtebi1:/opt/ibm/biginsights/bin> 
hadoop fs -ls /biginsights/hive/warehouse/gosalesdw.db 
 Found 70 items 
 drwxr-xr-x   - biadmin biadmgrp            
0 2013-12-15 07:47 /biginsights/hive/warehouse/gosalesdw.db/sls_product_dim_part

 sls_product_dim_load_991.sql 

 load hive data local inpath '../samples/data/sls_product_prt_991.txt'
 overwrite into table gosalesdw. sls_product_dim_part partition (product_line_code=991); 

 sls_product_dim_load_992.sql 

 load hive data local inpath '../samples/data/sls_product_prt_992.txt' 
into table gosalesdw.sls_product_dim_part partition (product_line_code=992); 
 ~                                                                            
 sls_product_dim_load_993.sql 

 load hive data local inpath '../samples/data/sls_product_prt_993.txt'
 into table gosaleSDW.sls_product_dim_part partition (product_line_code=993); 

 biadmin@imtebi1:/opt/ibm/biginsights/bin>
 hadoop fs -ls /biginsights/hive/warehouse/gosalesdw.db/sls_product_dim_part 
 Found 3 items 
 drwxr-xr-x   - biadmin supergroup        
  0 2013-12-15 07:52 /biginsights/hive/warehouse/gosalesdw.db/sls_product_dim_part/product_line_code=991 
 drwxr-xr-x   - biadmin biadmgrp          
  0 2013-12-15 07:52 /biginsights/hive/warehouse/gosalesdw.db/sls_product_dim_part/product_line_code=992 
 drwxr-xr-x   - biadmin biadmgrp         
   0 2013-12-15 07:53 /biginsights/hive/warehouse/gosalesdw.db/sls_product_dim_part/product_line_code=993

 db25.sql 

 CREATE TABLE customer 
 ( 
  id int, 
  name VARCHAR(100), 
  phones ARRAY (20)>, 
  address STRUCT(100), city:VARCHAR(100), state:VARCHAR(10), Country:VARCHAR(10),zip:VARCHAR(5)> 
 ) 
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' COLLECTION ITEMS TERMINATED BY ':'; 

 biadmin@imtebi1:/opt/ibm/biginsights/bigsql/samples/queries> 
jsqsh --user=biadmin --password=password --server localhost --port 7052 --driver=bigsql -i db25.sql 
 0 rows affected (total: 0.73s) 

 Data.txt 

 1|David Li|63618888-12345:63618888|PGP25F:Beijing:Beijing:China:100101 
 2|David Wang|63618888-12346:63618888|PGP25F:Beijing:Beijing:China:100101 
 3|Jason Zhang|63618888-12347:63618888|PGP25F:Beijing:Beijing:China:100101 
 4|David Yang|63618888-12348:63618888|PGP25F:Beijing:Beijing:China:100101 
 5|Jason Wang|63618888-12349:63618888|PGP25F:Beijing:Beijing:China:100101 

 loadcomp.sql 

 load hive data local inpath '../samples/data/data.txt' overwrite into table customer; 

 biadmin@imtebi1:/opt/> $BIGSQL_HOME/bin/jsqsh --user=biadmin
 --password=password --server localhost --port 7052 --driver=bigsql -i loadcomp.sql 
 ok. (total: 15.17s) 

 biadmin@imtebi1:/opt/ibm/biginsights/bigsql/samples/queries> 
jsqsh --user=biadmin --password=password --server localhost --port 7052 --driver=bigsql 
 JSqsh Release 1.5-ibm, Copyright (C) 2007-2013, Scott C. Gray 
 Type help for available help topics. Using JLine. 
 [localhost][biadmin] 1> select name,phones[1],address.country from customer; 
 +-------------+----------------+---------+ 
 | name        |                | Country | 
 +-------------+----------------+---------+ 
 | David Li    | 63618888-12345 | China   | 
 | David Wang  | 63618888-12346 | China   | 
 | Jason Zhang | 63618888-12347 | China   | 
 | David Yang  | 63618888-12348 | China   | 
 | Jason Wang  | 63618888-12349 | China   | 
 +-------------+----------------+---------+ 
 5 rows in results(first row: 0.28s; total: 0.40s)

 --GOSALESDW.SLS_SALES_FACT 
 load hive data local inpath '../samples/data/GOSALESDW.SLS_ORDER_METHOD_DIM.txt' 
overwrite into table SLS_ORDER_METHOD_DIM; 
 load hive data local inpath '../samples/data/GOSALESDW.SLS_SALES_FACT.txt'
 overwrite into table SLS_SALES_FACT; 
 load hive data local inpath '../samples/data/GOSALESDW.SLS_ORDER_METHOD_DIM.txt'
 overwrite into table SLS_SALES_ORDER_DIM;

 db21.sql 

 create  table staff_sales ( 
 id smallint, name varchar(100), 
 years smallint, salary decimal, comm decimal) 
 partitioned by (dept smallint, job char(5)) ; 

 LOAD 
 USING JDBC CONNECTION URL 'jdbc:db2://imtebi1:50001/SAMPLE'
 WITH PARAMETERS (user = 'db2inst1',password='password') 
 FROM TABLE STAFF 
 WHERE "dept=66 and job='Sales'"
 INTO TABLE staff_sales 
  PARTITION ( dept=66 , job='Sales') APPEND 
  WITH LOAD PROPERTIES 
  (bigsql.load.num.map.tasks = 1) ; 

 biadmin@imtebi1:/opt/ibm/biginsights/bigsql/samples/queries> 
jsqsh --user=biadmin --password=password --server localhost 
--port 7052 --driver=bigsql -i db21.sql 
 0 rows affected (total: 0.65s) 
 3 rows affected (total: 1m28.84s) 

 biadmin@imtebi1:/opt/ibm/biginsights/bigsql/samples/queries>
 jsqsh --user=biadmin --password=password --server localhost --port 7052 --driver=bigsql 
 JSqsh Release 1.5-ibm, Copyright (C) 2007-2013, Scott C. Gray 
 Type help for available help topics. Using JLine. 
 [localhost][biadmin] 1> select * from staff_sales; 
 +-----+----------+-------+--------+------+------+-------+ 
 |  id | name     | years | salary | comm | dept | job   | 
 +-----+----------+-------+--------+------+------+-------+ 
 | 280 | Wilson   |     9 |  78674 |  811 |   66 | Sales | 
 | 310 | Graham   |    13 |  71000 |  200 |   66 | Sales | 
 | 320 | Gonzales |     4 |  76858 |  844 |   66 | Sales | 
 +-----+----------+-------+--------+------+------+-------+ 
 3 rows in results(first row: 0.36s; total: 0.37s)

 use gosalesdw; 
 CREATE HBASE TABLE SLS_HBASE_SALES_FACT ( ORDER_DAY_KEY int,
 ORGANIZATION_KEY int, EMPLOYEE_KEY int, RETAILER_KEY int,
 RETAILER_SITE_KEY int, PRODUCT_KEY int, PROMOTION_KEY int,
 ORDER_METHOD_KEY int, SALES_ORDER_KEY int, SHIP_DAY_KEY int, 
CLOSE_DAY_KEY int, QUANTITY int, UNIT_COST decimal(19,2),
 UNIT_PRICE decimal(19,2), UNIT_SALE_PRICE decimal(19,2), 
GROSS_MARGIN double, SALE_TOTAL decimal(19,2), GROSS_PROFIT decimal(19,2) ) 
 column mapping 
 (key mapped by (ORDER_DAY_KEY,ORGANIZATION_KEY, EMPLOYEE_KEY,
 RETAILER_KEY, RETAILER_SITE_KEY, PRODUCT_KEY, PROMOTION_KEY, 
ORDER_METHOD_KEY) encoding binary, 
   cf1:sales_order mapped by (SALES_ORDER_KEY,
SHIP_DAY_KEY,CLOSE_DAY_KEY) separator '|' encoding string, 
   cf2:sales_data mapped by (QUANTITY, UNIT_COST, UNIT_PRICE, 
UNIT_SALE_PRICE, GROSS_MARGIN, SALE_TOTAL,
 GROSS_PROFIT) separator '|' encoding string ) 
 column family options 
   (cf1 compression(gz) bloom filter( none) in memory, 
   cf2 compression(gz) bloom filter( none) in memory) 
 ROW FORMAT DELIMITED FIELDS TERMINATED BY 't';

 CREATE INDEX index1 ON TABLE gosalesdw.SLS_HBASE_SALES_FACT(SALES_ORDER_KEY) AS 'HBASE';

 CREATE external HBASE TABLE EXTERNAL_SALES_FACT ( ORDER_DAY_KEY int, 
ORGANIZATION_KEY int, EMPLOYEE_KEY int, RETAILER_KEY int, RETAILER_SITE_KEY int,
 PRODUCT_KEY int, PROMOTION_KEY int, ORDER_METHOD_KEY int, QUANTITY int, 
UNIT_COST decimal(19,2), UNIT_PRICE decimal(19,2), 
UNIT_SALE_PRICE decimal(19,2), GROSS_MARGIN double, 
SALE_TOTAL decimal(19,2), GROSS_PROFIT decimal(19,2) ) 
 column mapping 
 (key mapped by (ORDER_DAY_KEY,ORGANIZATION_KEY, 
EMPLOYEE_KEY, RETAILER_KEY, RETAILER_SITE_KEY, PRODUCT_KEY, 
PROMOTION_KEY, ORDER_METHOD_KEY) encoding binary, 
   cf1:sales_data mapped by (QUANTITY, UNIT_COST, UNIT_PRICE,
 UNIT_SALE_PRICE, GROSS_MARGIN, SALE_TOTAL, GROSS_PROFIT)
 separator '|' encoding string ) 
 hbase table name 'gosalesdw.sls_hbase_sales_fact';

 create hbase table hbase_staff ( 
 id smallint, name varchar(100))  
 COLUMN MAPPING ( 
 key mapped by (id), 
 cf:name mapped by (name) 
 ); 
 insert into hbase_staff(id,name) values(202,'David Li');

 load hbase data inpath 'hdfs://imtebi1.imte.com:9000/biginsights/GOSALESDW.SLS_SALES_FACT.txt'
 DELIMITED FIELDS TERMINATED BY 't' into table gosalesdw.SLS_HBASE_SALES_FACT;

 db22.sql 

 create hbase table hbase_staff ( 
 id smallint, name varchar(100))  
 COLUMN MAPPING ( 
 key mapped by (id), 
 cf:name mapped by (name) 
 ); 

 LOAD 
  USING JDBC CONNECTION URL 'jdbc:db2://imtebi1:50001/SAMPLE'      
  WITH PARAMETERS (user = 'dbinst1',password = 'password' ) 
  FROM TABLE STAFF COLUMNS (ID, NAME) 
  WHERE "  ID > 100 and NAME like 'S%'  "
  INTO hbase TABLE hbase_staff APPEND 
  WITH LOAD PROPERTIES (bigsql.load.num.map.tasks = 1); 

 biadmin@imtebi1:/opt/ibm/biginsights/bigsql/samples/queries> 
jsqsh --user=biadmin --password=password --server localhost --port 7052 --driver=bigsql -i db22.sql 
 0 rows affected (total: 5.58s) 
 3 rows affected (total: 1m34.47s) 

 biadmin@imtebi1:/opt/ibm/biginsights/bigsql/samples/queries> 
jsqsh --user=biadmin --password=password --server localhost --port 7052 --driver=bigsql 
 JSqsh Release 1.5-ibm, Copyright (C) 2007-2013, Scott C. Gray 
 Type help for available help topics. Using JLine. 
 [localhost][biadmin] 1> select * from hbase_staff1; 
 +-----+----------+ 
 |  id | name     | 
 +-----+----------+ 
 | 190 | Sneider  | 
 | 200 | Scoutten | 
 | 220 | Smith    | 
 +-----+----------+ 
 3 rows in results(first row: 1.70s; total: 1.70s)

 create hbase table hbase_staff1 ( 
 id smallint, name varchar(100), dept smallint, job string, years smallint, salary string, comm string) 
 COLUMN MAPPING ( 
 key mapped by (ID), 
 cf:name mapped by (name), 
 cf:dept mapped by (dept), 
 cf:job mapped by  (job), 
 cf:years mapped by  (years), 
 cf:salary mapped by (salary), 
 cf:comm mapped by(comm) 
 ); 

 LOAD 
  USING JDBC CONNECTION URL 'jdbc:db2://imtebi1:50001/SAMPLE'
  WITH PARAMETERS (user = 'db2inst1',password = 'password' ) 
  FROM SQL QUERY 'select * from staff where $CONDITIONS'
  INTO hbase TABLE hbase_staff1 APPEND 
  WITH LOAD PROPERTIES (bigsql.load.num.map.tasks = 1);

 GOSALESDW_Counts_With_Joins.sql 

 SELECT count(*) 
  FROM 
        GOSALESDW.SLS_ORDER_METHOD_DIM AS MD, 
        GOSALESDW.SLS_PRODUCT_DIM AS PD, 
         GOSALESDW.EMP_EMPLOYEE_DIM AS ED, 
         GOSALESDW.SLS_SALES_FACT AS SF 
  WHERE 
        PD.PRODUCT_KEY = SF.PRODUCT_KEY /*+ joinMethod = 'mapSideHash', buildTable = 'pd' +*/ 
        AND PD.PRODUCT_NUMBER > 10000 
    AND PD.BASE_PRODUCT_KEY > 30 
    AND MD.ORDER_METHOD_KEY = SF.ORDER_METHOD_KEY /*+ joinMethod = 'mapSideHash', buildTable = 'md' +*/ 
    AND ED.EMPLOYEE_KEY = SF.EMPLOYEE_KEY /*+ joinMethod = 'mapSideHash', buildTable = 'ed' +*/ 
    AND ED.MANAGER_CODE1 > 20 
    AND MD.ORDER_METHOD_CODE > 5 ;

 GOSALESDW_starSchemaJoin.sql 

 WITH 
  SALES AS 
     (SELECT SF.* 
        FROM GOSALESDW.SLS_ORDER_METHOD_DIM AS MD, 
             GOSALESDW.SLS_PRODUCT_DIM AS PD, GOSALESDW.EMP_EMPLOYEE_DIM AS ED, 
             GOSALESDW.SLS_SALES_FACT AS SF 
        WHERE PD.PRODUCT_KEY = SF.PRODUCT_KEY /*+ joinMethod = 'mapSideHash', buildTable = 'pd' +*/ 
        AND PD.PRODUCT_NUMBER > 10000 
    AND PD.BASE_PRODUCT_KEY > 30 
    AND MD.ORDER_METHOD_KEY = SF.ORDER_METHOD_KEY /*+ joinMethod = 'mapSideHash', buildTable = 'md' +*/ 
    AND MD.ORDER_METHOD_CODE > 5 
    AND ED.EMPLOYEE_KEY = SF.EMPLOYEE_KEY /*+ joinMethod = 'mapSideHash', buildTable = 'ed' +*/ 
    AND ED.MANAGER_CODE1 > 20), 
 INVENTORY AS 
     (SELECT IF.* 
        FROM GOSALESDW.GO_BRANCH_DIM AS BD, GOSALESDW.DIST_INVENTORY_FACT AS IF 
        WHERE IF.BRANCH_KEY = BD.BRANCH_KEY /*+ joinMethod = 'mapSideHash', buildTable = 'bd' +*/ 
        AND BD.BRANCH_CODE > 20) 
 SELECT SALES.PRODUCT_KEY AS PROD_KEY, SUM(CAST (INVENTORY.QUANTITY_SHIPPED AS BIGINT)) AS INV_SHIPPED, 
  SUM(CAST (SALES.QUANTITY AS BIGINT)) AS PROD_QUANTITY, RANK() OVER
 ( ORDER BY SUM(CAST (SALES.QUANTITY AS BIGINT)) DESC) AS PROD_RANK 
  FROM SALES, INVENTORY 
  WHERE SALES.PRODUCT_KEY = INVENTORY.PRODUCT_KEY 
  GROUP BY SALES.PRODUCT_KEY 
 Limit 20;

 GOSALESDW_rank.sql 

 SELECT EXTRACT(YEAR FROM CAST(CAST (order_day_key AS varchar(100)) AS timestamp)) AS year, 
 SUM (sale_total) AS total_sales, 
 RANK () OVER (ORDER BY SUM (sale_total) DESC) AS ranked_sales 
 FROM gosalesdw.sls_sales_fact 
 GROUP BY EXTRACT(YEAR FROM CAST(CAST (order_day_key AS varchar(100)) AS timestamp));

 [localhost][biadmin] 1>  select * from syscat.tables where tablename='sls_sales_order_dim'; 
 +------------+---------------------+ 
 | schemaname | tablename           | 
 +------------+---------------------+ 
 | gosalesdw  | sls_sales_order_dim | 
 +------------+---------------------+ 
 1 row in results(first row: 0.17s; total: 0.17s) 

 [localhost][biadmin] 1> select * from syscat.columns where tablename='sls_sales_order_dim'; 
 +------------+------------+-------------+------+-----------+-------+----------+ 
 | schemaname | tablename  | name        | type | precision | scale | fulltype | 
 +------------+------------+-------------+------+-----------+-------+----------+ 
 | gosalesdw  | sls_sales_ | sales_order | INT  |        10 |     0 | int   | 
 |            | order_dim  | _key        |      |           |       |          | 
 | gosalesdw  | sls_sales_ | order_detai | INT  |        10 |     0 | int      | 
 |            | order_dim  | l_code      |      |           |       |          | 
 | gosalesdw  | sls_sales_ | order_numbe | INT  |        10 |     0 | int      | 
 |            | order_dim  | r           |      |           |       |          | 
 | gosalesdw  | sls_sales_ | warehouse_b | INT  |        10 |     0 | int      | 
 |            | order_dim  | ranch_code  |      |           |       |          | 
 +------------+------------+-------------+------+-----------+-------+----------+ 
 4 rows in results(first row: 2.8s; total: 2.10s)

 CREATE INDEX index1 ON TABLE gosalesdw.SLS_HBASE_SALES_FACT(SALES_ORDER_KEY) AS 'HBASE'; 

 select count(*) from gosalesdw.sls_hbase_sales_fact 
/*+ rowcachesize=2000,useindex='index1' +*/ where sales_order_key<195000;

 SELECT c1,count(*) from T1 /*+ accessmode='local' +*/ group by c1;

 select * from gosalesdw.sls_sales_order_dim /*+ accessmode = 'mr' +*/ where sales_order_key<100020;

 set force local on; 
 select * from gosalesdw.sls_sales_order_dim where sales_order_key<100020;

 SELECT t1.c1, count(*) FROM t1 /*+ accessmode='local' +*/, t2 WHERE t1.c2 = t2.c2 GROUP BY t1.c1;

 SELECT c1, count(*) from T1, T2 where T1.c2 = T2.c2 GROUP BY c1;

 SELECT c1, count(*) FROM T1, T2 /*+ tablesize='small' +*/ where T1.c2 = T2.c2 GROUP BY c1;

 SELECT count(*) 
  FROM 
        GOSALESDW.GO_BRANCH_DIM  /*+ tablesize='large' +*/ AS BD , 
        GOSALESDW.DIST_INVENTORY_FACT AS IF 
  WHERE 
  IF.BRANCH_KEY = BD.BRANCH_KEY 
  AND BD.BRANCH_CODE > 20;

 SELECT BD.BRANCH_KEY,count(*) 
  FROM 
        GOSALESDW.GO_BRANCH_DIM  /*+ tablesize='small' +*/ AS BD , 
        GOSALESDW.DIST_INVENTORY_FACT AS IF 
  WHERE 
  IF.BRANCH_KEY = BD.BRANCH_KEY 
  AND BD.BRANCH_CODE > 20 
  GROUP BY BD.BRANCH_KEY;

 SELECT c1, c3 FROM hbase_tab /*+ rowcachesize=4000 +*/ WHERE c2 > 10;

 SELECT c1, c3 FROM hbase_tab /*+ colbatchsize=5 +*/ WHERE c2 > 10;

 CREATE INDEX index1 ON TABLE gosalesdw.SLS_HBASE_SALES_FACT(SALES_ORDER_KEY) AS 'HBASE'; 

 select count(*) from gosalesdw.sls_hbase_sales_fact 
/*+ rowcachesize=2000,useindex='index1' +*/ where sales_order_key<195000;

 SELECT a.c1 FROM T1 as a, T2 as b WHERE a.c2 
= b.c2 /*+ joinmethod='mapsidehash', buildtable='b' +*/ AND a.c3 = b.c3;

 SELECT * FROM T1 WHERE c2 IN (SELECT c2 FROM T2
 WHERE T2.c3 = T1.c3 AND t2.c4 = 'Fred');

 SELECT T1.* 
 FROM T1, (SELECT c2, c3 FROM T2 WHERE t2.c4 = 'Fred') as D0 
 WHERE T1.c3 = D0.c3;

 SELECT * FROM T1 WHERE c2 IN (/*+ rewrite=false 
+*/ SELECT c2 FROM T2 WHERE T2.c3 = T1.c3 AND t2.c4 = 'Fred');

 biadmin@imtebi1:/opt/ibm/biginsights> VERBOSE=true $BIGSQL_HOME/bin/bigsql status 
 Total memory on the machine = 3958600 KB 
 Max memory allocated to bigsql is: 1288m 
 BigSQL server is running (pid 8243)

 /opt/ibm/biginsights> export BIGSQL_CONF_INSTANCE_INITIAL_MEM=2g 

 /opt/ibm/biginsights> export BIGSQL_CONF_INSTANCE_MAX_MEM=4g 
 biadmin@imtebi1:/opt/ibm/biginsights> $BIGSQL_HOME/bin/bigsql stop 
 BigSQL pid 12611 stopped. 

 biadmin@imtebi1:/opt/ibm/biginsights> $BIGSQL_HOME/bin/bigsql start 
 BigSQL running, pid 12811. 

 /opt/ibm/biginsights> VERBOSE=true $BIGSQL_HOME/bin/bigsql status 
 Max memory allocated to bigsql is: 4g 
 BigSQL server is running (pid 12811)

 bigsql.reducers.autocalculate [true] 
默认值为 True，Big SQL 会自动计算 MapReduce 作业使用 reducers 的数量。

 bigsql.reducers.bytes.per.reducer [1GB] 
默认值为 1GB，指定每一个 reducer 可以处理的数据量。

 bigsql.memoryjoin.size [10MB] 
默认值为 10MB，当表的大小低于该值时，Big SQL 将使用 map-side memory join 方式。

 bigsql.localmode.size [200MB] 
默认值为 200MB，当所有表的数据量小于该值，Big SQL 将自动在服务器本地运行。

 biadmin@imtebi1:/opt/ibm/biginsights> $BIGSQL_HOME/bin/jsqsh -U biadmin -P password 
 JSqsh Release 1.5-ibm, Copyright (C) 2007-2013, Scott C. Gray 
 Type help for available help topics. Using JLine. 

 [localhost][biadmin] 1> set bigsql.reducers.byte.per.reducer = 209715200; 
 1 row affected (total: 0.8s) 

 [localhost][biadmin] 1> SELECT * FROM T1 WHERE c2 IN 
(SELECT c2 FROM T2 WHERE T2.c3 = T1.c3 AND t2.c4 = 'Fred'); 
 +----+----+----+ 
 | c1 | c2 | c3 | 
 +----+----+----+ 
 |  2 |  2 |  2 | 
 +----+----+----+ 
 1 row in results(first row: 14.69s; total: 14.69s)