IBM Db2 Big SQL简介
Big SQL是IBM推出的通过SQL访问Hadoop平台的工具,它是一个融合的、高性能MPP引擎,支持绝大多数数据源:如HDFS、RDBMS、NoSQL数据库、对象存储和WebHDFS。Big SQL提供了低延迟查询、数据安全、SQL兼容性、企业级数仓联邦能力。通过Big SQL,企业数据从而发挥最大价值。
Big SQL强大的引擎可以执行包括关系型数据和Hadoop数据的复杂查询。Big SQL提供了一个高级的SQL编译器和一个高效的基于成本的查询优化器。结合MPP引擎,将查询执行分布到集群的各个节点。
为什么需要Big SQL?
Big SQL提供业界最佳的扩展能力和高性能,在以下场景尤为适用:
EDW减压和负载接管
Big SQL兼容业界常用关系型数据库的语法,如Oracle/Db2/Netezza等,从而可以将现在存放在这些数据库中的企业数据仓库或数据集市中的数据负载迁移到Big SQL平台,而且原有的应用无需改变。
联邦关系型数据库
对于不能迁移到Hadoop的数据,Big SQL提供联邦存取的能力,支持多种关系型数据库和NoSQL数据库。只需要一个数据库连接,就可以存取所有的Hadoop以及关系型/NoSQL数据,包括云上的或本地的。Big SQL提供数据虚拟能力,在一个查询语句里查询各种异构数据。
另外Big SQL还能:
弹性扩展能力,支持更细粒度的资源使用,无需增加内存和CPU即可提高性能。
高性能表扫描、插入、更新和删除
相比其他SQL-on-Hadoop技术,更好的深度集成Spark 2.1
同一安全模型下的Spark机器学习或者图分析能力
兼容和支持ODPi组织内的各成员的Hadoop平台
高级的,兼容ANSI的SQL查询能力
IBM Db2 Big SQL快速上手
本教程是针对Big SQL初学者快速上手的。通过本教程,可以获得如下能力:
设置运行Big SQL查询的基本工具
在分布式文件系统上创建存放样例数据的目录
存取Big SQL服务器上的样例数据
创建样例表,装载样例数据
接下来让我们开始具体的学习步骤:
学习模块1(入门)
1. 开启Big SQL学习环境
从Ambari查看Big SQL是否启动成功
也可以使用JSqsh, 先确保安装了JDBC或ODBC驱动的JSqsh客户端能访问BigSQL服务器
2. 在HDFS上创建目录
登录进入linux terminal
su bigsql
cd
hdfs dfs -ls/user/bigsql/
hdfs dfs -mkdir/user/bigsql/bi_sample_data
3. 获取样例数据
a)创建java配置文件bigsql.prop
user=bigsql
password=bigsql
b)上传到HDFS
hdfs dfs -copyFromLocalbigsql.prop /user/bigsql
hdfs dfs -ls /user/bigsql
c)查看权限
-rw-r--r-- 3 bigsql hadoop 28 2017-11-21 15:18 /user/bigsql/bigsql.prop
样例数据存放在$BIGSQL_HOME/samples,缺省$BIGSQL_HOME是/usr/ibmpacks/current/bigsql/bigsql.
dir $BIGSQL_HOME/samples/queries
本练习需以下3个SQL文件
GOSALESDW_ddl.sql
GOSALESDW_drop.sql
GOSALESDW_load.sql
将样例SQL文件上传至HDFS
hdfs dfs -put $BIGSQL_HOME/samples/queries /user/bigsql/bi_sample_data
本练习只需要执行以下语句装载相关数据
hdfs dfs -put $BIGSQL_HOME/samples/data/GOSALESDW.DIST_INVENTORY_FACT.txt/user/bigsql/bi_sample_data
hdfs dfs -put $BIGSQL_HOME/samples/data/GOSALESDW.EMP_EMPLOYEE_DIM.txt/user/bigsql/bi_sample_data
hdfs dfs -put $BIGSQL_HOME/samples/data/GOSALESDW.GO_BRANCH_DIM.txt/user/bigsql/bi_sample_data
hdfs dfs -put $BIGSQL_HOME/samples/data/GOSALESDW.GO_REGION_DIM.txt/user/bigsql/bi_sample_data
hdfs dfs -put $BIGSQL_HOME/samples/data/GOSALESDW.SLS_ORDER_METHOD_DIM.txt/user/bigsql/bi_sample_data
hdfs dfs -put $BIGSQL_HOME/samples/data/GOSALESDW.SLS_PRODUCT_BRAND_LOOKUP.txt/user/bigsql/bi_sample_data
hdfs dfs -put $BIGSQL_HOME/samples/data/GOSALESDW.SLS_PRODUCT_DIM.txt/user/bigsql/bi_sample_data
hdfs dfs -put $BIGSQL_HOME/samples/data/GOSALESDW.SLS_PRODUCT_LINE_LOOKUP.txt/user/bigsql/bi_sample_data
hdfs dfs -put $BIGSQL_HOME/samples/data/GOSALESDW.SLS_PRODUCT_LOOKUP.txt/user/bigsql/bi_sample_data
hdfs dfs -put $BIGSQL_HOME/samples/data/GOSALESDW.SLS_SALES_FACT.txt/user/bigsql/bi_sample_data
4.创建表,装载数据
样例数据是虚构的一家户外公司三年七个月里的数据,从2004/01/01到2007/07/31,我们来分析这43个月的历史数据。
a)先设置Jsqsh
/usr/ibmpacks/common-utils/current/jsqsh/bin/jsqsh --setup
b) 输入C定义连接
c) 选择相应的数字回车,比如:
Name Driver Host Port
--------------------------------------
1 bigsql db2 abc.com 32051
d)如果相应的信息需要修改,选择相应的部分进行修改
e)输入T进行联机的测试,输入S,提供一个配置名称比如bigsql,保存配置。
f)输入quit退出连接配置.
g)现在继续使用JSqsh,就可以使用配置名bigsql,比如
/usr/ibmpacks/common-utils/current/jsqsh/bin/jsqsh bigsql
h)删除相关表。如果没有创建过相关表,跳过此步.
\eval $BIGSQL_HOME/samples/queries/GOSALESDW_drop.sql
i) 创建样例表:运行脚本 GOSALESDW_ddl.sql
\eval $BIGSQL_HOME/samples/queries/GOSALESDW_ddl.sql
GOSALESDW_ddl.sql文件里包含了创建schema和表的SQL语句。第一行创建了GOSALESDW schema. 使用USE来使用这个schema,缺省的schema是UserID。
j)装载数据:GOSALESDW_load.sql包含了装载数据的语句。必须编辑这个文件来使用正确的文件URL。
\eval $BIGSQL_HOME/samples/queries/GOSALESDW_load.sql
Tip
如果需要节省空间,可以删除导入成功的源数据。
hdfs dfs -rm -r/user/bigsql/bi_sample_data/data/
k)如果前面的创建表和加载数据都成功了,现在可以来列这些表了。\showtables. 也可以查询语句来查询具体的数据。
SELECT * FROM gosalesdw.go_region_dim FETCH FIRST 5 ROWS ONLY;
SELECT * FROMgosalesdw.sls_sales_fact FETCH FIRST 5 ROWS ONLY;
学习模块2(进阶)
本模块将学习Big SQL的基本语句,创建Big SQL对象,以及运行查询。学习目标:
通过JSqsh命令行运行查询
创建运行Big SQL语句的脚本
创建视图
创建分析这家户外公司财务数据的查询
样例户外公司使用GOSALESDW这个schema,具有如下的事实表:
Distribution
Finance
Geography
Marketing
Organization
Personnel
Products
Retailers
Sales
Time
1. 简单查询语句
su – bigsql
vi aFirstFile.sql
SELECT * FROM GOSALESDW.GO_REGION_DIM;
保存文件
有如下输出
21 rows in results(first row: 0.25s; total: 0.34s)
加入一些限定语句
在JSqsh中,输入vi
SELECT * FROM gosalesdw.go_region_dim WHERE region_en LIKE'Amer%';
:w! fullpath>/aFirstFile.sql
可以通过如下的语句来查看某张表的结构:
2. 使用视图view
创建新的文件GOSALESDW_viewddl.sql
CREATE SCHEMA myschema;
在JSqsh里执行
查询视图信息
在视图上执行查询
3. 使用连接和限定条件来了解户外公司的产品构成和客户预定的渠道
a) 编辑companyInfo.sql,需要连接两张表以获得所需信息
执行companyInfo.sql
查看结果,就可以了解哪些产品卖出了,客户是通过什么渠道下订单的。
统计符合条件的记录条数,使用count(*)
结果是446,023 rows.
b)加入订单方法的限定条件
结果是15,842 rows.
c)通过GROUP BY语句了了解哪种订单渠道获得了最多的订单数量。方法是GROUP BYpll.product_line_en,md.order_method_en。这里用 SUM统计函数来获得产品和渠道的订单总数,通过加AS,来将输出给出指定名称。
md.order_method_en AS Order_method, SUM(sf.QUANTITY) AStotal FROM gosalesdw.sls_order_method_dim AS md, gosalesdw.sls_product_dim AS pd, gosalesdw.sls_product_line_lookup AS pll, gosalesdw.sls_product_brand_lookup AS pbl, gosalesdw.sls_sales_fact AS sf
pd.product_key = sf.product_key AND md.order_method_key= sf.order_method_key AND pll.product_line_code = pd.product_line_code AND pbl.product_brand_code = pd.product_brand_code GROUP BY pll.product_line_en, md.order_method_en;
结果35 rows.
4.使用表达式、统计函数、排名等来分析仓库中产品的数量,产品的销售数量
编辑advanced.sql
(SELECT sf.* FROM gosalesdw.sls_order_method_dim AS md, gosalesdw.sls_product_dim AS pd, gosalesdw.emp_employee_dimAS ed, gosalesdw.sls_sales_fact AS sfAND pd.product_number >10000 AND pd.base_product_key >30 AND md.order_method_key =sf.order_method_key AND md.order_method_code >5 AND ed.employee_key =sf.employee_key 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 AND bd.branch_code > 20)
SUM(CAST(inventory.quantity_shipped AS BIGINT)) AS INV_SHIPPED, SUM(CAST (sales.quantity ASBIGINT)) AS PROD_QUANTITY,
FROM sales, inventory WHERE sales.product_key =inventory.product_key GROUP BY sales.product_key;
这里使用WITH定义了一个共用表表达式commontable expression,定义了两张表 (salesandinventory) ,然后就可以在FROM中引用,可以嵌套.使用WITH避免了创建view,也可以在不同的fullselect中共享。
选择JOIN两张表的先后的原则,应该尽早减少行,即把加了能过滤掉越多行的限定条件的表或者去除了记录的表放在查询的前面。这些表被认为是高度可选择的。这样的先后顺序写法减少了在接下来的查询中必须访问的记录条数。
执行这个文件的结果是165行。
关于JSqsh,参考JavaSQL Shell (JSqsh).
领取专属 10元无门槛券
私享最新 技术干货