你好,游客 登录 注册 搜索
背景:
阅读新闻

利用InfoSphere BigInsights中的Big SQL处理大数据

[日期:2015-07-02] 来源:IBM  作者: [字体: ]
  SQL 是一个实用查询语言,但它具有一定的局限性。Big SQL 让您能够在非表格式数据上运行复杂查询,并使用与 SQL 类似的语言查询它。Big SQL 的与众不同之处在于,您可以访问非表格式数据,并且这些数据实际上并不是以典型的 SQL 数据库结构为基础。使用 Big SQL,您可以导入和处理大量数据集,包括读取 InfoSphere BigInsights? 中其他处理作业已处理的输出,将该信息转换为易于查询的数据。在本文中,我们探讨如何用 Big SQL 替代您现有的基础架构和查询,以及如何采用更复杂的查询并转换它们,以便利用您的 Big SQL 环境。

  Big SQL 是 IBM 的 SQL 接口,用于连接到基于 Hadoop 的平台 InfoSphere BigInsights。Big SQL 旨在为 SQL 开发人员提供一个查询由 Hadoop 管理的数据的简单途径。它使得数据管理员能够为存储在 Apache Hive、HBase 或他们的 InfoSphere BigInsights 分布式文件系统中的数据创建新表。

  导入要在 Big SQL 中使用的数据

  InfoSphere BigInsights Quick Start Edition

  InfoSphere BigInsights Quick Start Edition 是一个免费的、可下载的 InfoSphere BigInsights 版本,是 IBM 基于 Hadoop 的产品。使用 Quick Start Edition,您可以尝试 IBM 为了提高开源 Hadoop 的价值而构建的特性,比如 Big SQL、文本分析和 BigSheets。为了让您的体验尽可能顺利,我们提供了引导式学习,包括分步的自学教程和视频,帮助您开始让 Hadoop 为您工作。没有时间或数据的限制,您可以自己选择时间使用大量数据进行实验。观看视频,遵循这些教程(PDF),并 立刻下载 BigInsights Quick Start Edition。

  与任何大数据处理任务一样,您要做的第一步是加载数据,然后才能开始查询它。与 Hadoop 和 InfoSphere BigInsights 环境的其他领域相似,加载要在 Big SQL 中使用的数据有两个步骤:

  在 Big SQL 中创建逻辑表

  将逻辑结构连接到原始数据,或者将原始数据加载到逻辑表中

  Big SQL 环境的其中一个与众不同之处在于,用户在任何方向都可以执行进程:可以通过 Hive 加载数据,并通过 Big SQL 访问数据,或者,可以在 Big SQL 中创建表,然后由 Big SQL 将数据加载到表中。

  同样,与其他大部分这类工具相似,系统的设计思路是为查询数据批量加载大数据块。虽然 Big SQL 是覆盖在 Hadoop 功能之上的 SQL,但它仍然是一个只能追加的(append-only)数据库,而且它不支持 INSERT 和 UPDATE 语句。

  数据本身可以是任何典型格式(例如,CSV 或制表符分隔),Big SQL 还可以直接利用 Hive 和 HBase 表,在此数据上应用 SQL 或经过转换的 MapReduce。如果这些方法都无法满足您的需求,那么您还可以使用自定义的串行器/解串行器,例如,处理 JSON 内容或串行化的二进制对象。

  在使用 InfoSphere BigInsights 时,要加载数据,您可以使用 Web 界面直接将数据加载到现有集群。您可以通过多种方法做到这一点,但在有本地数据文件时,客户端是最便捷的方法:$ /opt/ibm/biginsights/bigsql/bin/bigsql client。

  在启动 bigsql 客户端后,您必须单独连接到 Big SQL:bigsql > connect jdbc:bigsql://localhost:7052/default:user=biadmin;password=biadmin。

  在连接到 Big SQL 数据库引擎后,您可以使用 SHOW DATABASES、SHOW TABLES 和大部分传统 SQL 命令行界面都很熟悉的其他命令:create table chicago_bus (timelog timestamp, region int, buscount int, logreads int, speed float) row format delimited fields terminated by ',' lines terminated by "\n"。

  如果您熟悉 Hive,基本的 DDL 结构与之完全相同,迁移现有的表或导入工具对您来说都应该没有问题。

  请记住,Big SQL 支持的数据类型是有所不同的。导致问题的主要差异往往是日期存储的限制。在 Big SQL 中,日期存储被限制为一个时间戳,其格式为:YYYY-MM-DD hh:mm:ss.nnnnn。

  这种格式被要求严格执行。只有日期,只有时间,12 小时的时间,而且不支持时区。不过,一系列灵活的内置函数让您能够在处理过程中将时间戳转换成您更熟悉的年月格式,以及其他时间值。这些函数是有限的,但是,我们稍后在讨论查询构建时会回到这个主题。尝试避免使用整数作为 epoch 值,也不要在 string 数据类型中使用整数,因为这些值不能在内部进行转换,甚至使用 Big SQL 中的函数也不能对其进行转换。

  string 数据类型和更大的基于字节的数据也是有限制的,这是在处理 BLOB 数据类型或很长的 text 类型时要考虑到的一个重要限制。内置的 string 和 VARCHAR 类型都被限制为 32K。如果您需要处理这种类型的数据,更高效的做法可能是,在文本处理框架内处理这些信息,并使用流程输出来构建将在 Big SQL 中使用的表。

  支持大部分其他固定的类型,比如 integers、floating point values 和 binary types,而且这些类型基本上保持不变,具体情况请参阅相关文档。

  一旦创建了表,就可以将数据加载到这个表结构中:bigsql> load hive data local inpath '/home/biadmin/chicago-bigsql.csv' into table chicago_bus。

  前面的示例根究要加载的数据指定了 CSV 文件在本地文件系统中的位置。您也可以访问外部的、预先加载的数据,例如,来自某个 Hive 作业或现有 HBase 表的输出的数据。正如我们将在后面将要看到,如果您想将不同的组件处理连起来送入 Big SQL,那么此选项会很有用。

  在加载数据时,Big SQL 不会抱怨任何格式问题;只有在执行查询时它才会处理数据。执行简单的查询,这一直是一个不错的主意,比如:bigaql> SELECT * from chicago_bus LIMIT 5。

  一定要检查并确认所有格式问题。如果有问题,则更新源文件的格式或结构(如果有可能),并通过使用 OVERWRITE INTO 在加载时覆盖 Big SQL 表:bigsql> load hive data local inpath '/home/biadmin/chicago-bigsql.csv' overwrite into table chicago_bus 。

  请注意,如果您不使用 OVERWRITE 并且重复导入,则会追加新的数据。请注意,不要复制两次(或三次)信息,或不小心覆盖现有数据。

  因为 Big SQL 是在 Hadoop 核心内容之上的一个层次,所以您可以从 Big SQL 内部连接到外部数据库,并运行查询来加载特定的表数据,对它们进行处理。例如,使用清单 1 从包含所有 chicago 数据的 DB2 表加载数据。

  清单 1. 从 DB2 表加载数据

  bigsql> LOAD

  USING JDBC CONNECTION URL 'jdbc:db2://db2-server:50000/CHICAGO'

  WITH PARAMETERS (user = 'user',password='password')

  FROM TABLE CHICAGO

  INTO TABLE chicago

  但通过添加一个 WHERE 子句,加载规范也可以是一个查询。

  清单 2. 添加 WHERE 子句

  bigsql> LOAD

  USING JDBC CONNECTION URL 'jdbc:db2://db2-server:50000/CHICAGO'

  WITH PARAMETERS (user = 'user',password='password')

  FROM TABLE CHICAGO

  WHERE REGION > 6

  INTO TABLE chicago

  这种类型的数据加载较少见。更常见的是,从集群中的其他部分加载数据,例如,在处理 MapReduce 作业的输出时。


  导出内部处理作业,以便在 Big SQL 中使用它

  如上所述,使用 Big SQL 作为查询处理的解决方案很有吸引力。通过提供由独立 Hadoop 进程生成的查询界面,它的确有过人之处。

  在大多数情况下,Big SQL 提供了相对于传统分布式存储和处理的优势。这使得您能够直接使用 Big SQL 查询来处理数据,或通过在核心的分布式处理之上提供个覆盖层,让您能够查询内容,从而间接地处理数据。

  使用 Big SQL 作为覆盖层的一个示例是,用标准的 MapReduce 查询来处理文本,MapReduce 查询将提取的数据输出成为一种 CSV 格式,这种格式适合将数据读回 Big SQL,以便对它们进行处理。

  使用来自从 Stanford 的电影评论数据,Stanford 从 Amazon 提供了有关电影评论的数据,我运行一个 MapReduce 进程来查找特定的关键字,比如 "love"、"hate"、"good"、"best" 和 "worst",将它们与实际评分进行比较,以确定其准确性。

  MapReduce 作业对该信息的处理生成解析数据,并将这些数据备份到 Hadoop Distributed File System (HDFS) 中的一个文件,但我也可以直接将它们写出到一个 Hive 表。

  要导入该数据,您可以在 Big SQL 中将文件加载到之前创建的 Hive 表:bigsql> load hive data local inpath '/host:port/amazon/reviews-analysed.csv' into reviews。或者,如果要直接访问文件,可以使用清单 3 中的代码。

  清单 3. 直接访问文件

  create external table azreviews

  (productid string, userid string, score float, confidence int)

  row format delimited

  fields terminated by ','

  stored as textfile

  location '/amazon/reviews-analysed.csv'

  这将在底层的 CSV 文件中创建一个隐含链接,作为执行查询时的表数据的数据源。这节省了加载阶段和处理,并且使得 Big SQL 可以访问实时表数据,甚至可以与数据处理同时进行。

  构建 Big SQL 查询

  另一种选择是用对应的 Big SQL 查询完全替代 MapReduce 作业,并完全不用手写的 MapReduce 查询。此选择要求掌握有关 Big SQL 如何执行查询和处理的一些基础知识,然后才能从 Big SQL 执行的隐式处理中获得最好的结果。具有讽刺意味的是,大多数解决方案已从 SQL 迁移出来,而且 Big SQL 将权利交了回去,带有一些警告。

  Big SQL 和传统 SQL 查询之间的差异其实很小。您在传统 SQL 查询中熟悉的大多数基本结构和顺序都可以在 Big SQL 中使用,Big SQL 对基本查询结构没进行多少修改。但是,因为 Big SQL 工作方式的不同,也有一些查询类型和转换需要更多的考虑和准备。

  大多数简单的查询由 Big SQL 服务器完全在内部执行处理。对于较大的处理,特别是那些涉及聚合的处理,Big SQL 将查询转换成与 Hive 的转换结果类似的查询。

  但是,对于如何正确构建可以有效执行的语句,有一些规则。

  基本查询

  诸如 SELECT(包含或不包含 WHERE 子句)之类的基本查询可以被轻松处理。

  清单 4. 基本查询

  bigsql > select title,servings from recipe limit 10

  +----------------------------------------------------------------+----+

  |title |servings |

  +----------------------------------------------------------------+----+

  |varchar |int |

  +----------------------------------------------------------------+----+

  |Creamy egg and leek special |4 |

  |Chakchouka |4 |

  |Mozzarella and olive pizza |4 |

  |Savoury pancakes |8 |

  |Seafood filling for pancakes |8 |

  |Pesto\ |- |

  |Easter egg cakes |12 |

  |Stilton walnut mousse |8 |

  |Easy omelette |- |

  |Spanish omelette |6 |

  +----------------------------------------------------------------+----+

  利用 WHERE 子句可以提高性能,只需加上 WHERE 子句,首先限制大部分数据。在数据减少时,该子句会从内存中缓冲的行中删除更多的数据,而且可以更容易地删除更多的行。

  例如,在限制类型和日期时,首先选择根据其值在逻辑上减少最多表数据的项目。

  聚合

  聚合 是大多数 MapReduce 查询中的减少步骤的逻辑等价物,但应小心确保有效地对数据运行聚合。Big SQL 已经为您将聚合转换成了一个 reduce 函数。

  聚合(如 COUNT、SUM 和 STDDEV)的工作原理与传统 SQL 相同,但根据已经讨论过的处理技巧,如果以这种方式构建查询,将对处理的整体负载减少到特定水平,性能可以得到提高。例如,通过 Big SQL 语句对芝加哥的公交车数据执行 AVG() 或 SUM() 语句就和通过传统 SQL 执行这些语句一样简单:SELECT region,AVG(speed) FROM chicago_bus GROUP BY region。

  与正常 MapReduce 查询一样,您应该在应用聚合之前尝试优化数据。

  优化联接

  在处理任何 JOIN 时,可以通过指定联接,减少在查询中累积的整体数据集,从而改进其 MapReduce 性质。例如,在处理为特定日志事件标记了日期并联接到错误条件的日志信息时,可以得到这种优化。错误类型表是一个联接,供参考之用,但日志实例(即日期信息存放的地方)上的联接通过相关的 WHERE 子句充当一个减少程序。

  在清单 5 所示的查询中,Big SQL 首先通过将错误描述联接到原始表来处理这一点。如果有 1 亿行数据,则需要有 1 亿个隐含联接。

  清单 5. 优化联接

  SELECT date,status,errordesc FROM access_log

  JOIN errortypes on (access_log.errortype = errortypes.id)

  JOIN access_instance on (access_log.instanceid = access_instance.id)

  WHERE access_instance.date > NOW()

  第二个联接通过联接附加日期信息,并通过 WHERE 子句来减少整体数据内容。

  通过反转联接语句(如清单 6 所示),首先执行日期选择,在将数据联接到错误信息之前先减少数据。生成查询的速度会快得多。

  清单 6. 反转联接语句

  SELECT date,status,errordesc FROM access_log

  JOIN access_instance on (access_log.instanceid = access_instance.id)

  JOIN errortypes on (access_log.errortype = errortypes.id)

  WHERE access_instance.date > NOW()
  
  将标准 SQL 语句转换为 Big SQL

  大多数 SQL 查询可以被直接处理,但也可以修改一些查询,以便适合在 Big SQL 中使用它们,从而将它们用于不同的环境。

  日期处理

  虽然与所编写的 SQL 语句不是严格相关,但也应注意不同的数据类型和值。我们已经看到 Big SQL 所支持的内置数据类型中的核心差异,特别是日期的差异,这些差异可能会带来一些问题。

  依赖于高级日期处理才可以工作的特定查询将会变得更加难以处理。清单 7 显示,按日期对信息进行分组可能是低效的。

  清单 7. 对信息进行分组可能是低效的

  SELECT diskusage, YEAR(statdate) AS year, MONTH(statdate) AS month FROM statmon

  GROUP BY year,month WHERE statdate _YEARS_BETWEEN(statdate,

  _ADD_YEARS(statdate,2))

  如果使用 Big SQL,也可以让同样的查询正常工作,但性能可能会受到影响,特别是在包括控制极限值的 WHERE 子句的时候。在这种情况下,使用子选择可能更高效。

  清单 8. 使用子选择可能更高效

  SELECT diskusage, YEAR(statdate) AS year, MONTH(statdate) AS month FROM

  (SELECT from diskusage, statedate FROM statmon WHERE statdate _YEARS

  _BETWEEN(statdate, _ADD_YEARS(statdate,2))) GROUP BY year,month

  这将执行子查询来选择日期,然后从日期中提取年份和月份。

  组

  在执行 GROUP 语句时要小心。Big SQL 的操作假设查询的 SELECT 子句中列出的列也将位于 group by 查询中。请考虑下面的查询,这在传统 SQL 环境中是完全有效的(尽管毫无意义,因为 opdata 是从数据集中随机选择的):SELECT operation,opdata FROM access_log GROUP BY operation。Big SQL 的要求很严格,您必须明确指定字段:SELECT operation,opdata FROM access_log GROUP BY operation,opdata。

  链接 SQL

  如果将涉及大型、复杂联接的查询转换成产生中间表的一系列离散的查询和处理作业,那么可以更高效地处理它们。这种方法类似于在传统 SQL 环境中使用视图或临时表,但在 Big SQL 中没有明确的方法可以这样做。

  使用离散的查询是一个优势,特别是在查询依赖于大量数据的子查询、子选择或者多级聚合的时候。这一招对于某些任务是有悖常理的,原因是 MapReduce 处理本身的特性,它通常允许在单一通道中执行多层次的处理。通过链接查询,可以从这些数据获得再次处理的数据点。

  例如,执行一个查询来收集某段时间内跨机器集群的磁盘使用情况的信息,其核心查询特别复杂。

  清单 9. 复杂的核心查询

  SELECT d.statid,d.used,class,subclass,name FROM (select min(statid)

  as matchid from statmon_du where recorded >= (date(subdate(now(),28)))

  group by pathid) AS X INNER JOIN statmon_du as d on d.statid = x.matchid join

  (statmon_du_locs) on (d.pathid = statmon_du_locs.id) where statmon_du_locs.

  active = 1 ORDER BY name,subclass

  这个代码包含一些内部和外部联接,可以通过执行内部的 SELECT 来简化这些联接。

  清单 10. 通过执行内部的 SELECT 来实现简化

  CREATE TABLE month_statmon (statid) ROW FORMAT DELIMITED FIELDS

  TERMINATED BY '\t' AS (SELECT min(statid) as matched from statmon_du

  where recorded >= (date(subdate(now(),28))) group by pathid)

  然后,您可以从这个表联接到较大的查询,作为直接联接,而不是作为一个显式的内部联接。尽管其他数据集可能有所不同,但 Big SQL 可在此实例中更高效地使用所联接的表。

  帮助 Big SQL 处理

  在幕后,对于复杂的查询,Big SQL 将 SQL 转换成一个 MapReduce 进程,然后必须在内容上运行此进程,以便产生所需的输出。在大多数情况下,转换会正常工作,但格式和处理偶尔可以得到改善。这是不同于典型 SQL 的一个方面,标准 SQL 解析器和优化引擎已针对处理数据进行了高度优化。

  由于这些限制,向 SQL 解析器提供提示是一个好主意,这使得它能够更好地决定应该如何处理或联接信息。这些提示对于处理速度有明显的影响,有时对于处理输出的内容(和质量)也有明显影响。

  从 SQL 解析进程的顶部开始,最简单的提示是,告诉 Big SQL 应该在内部(即,在 Big SQL 内存空间中)还是通过一个 MapReduce 进程来处理数据。例如,通过计数或求和执行聚合时,如果只有较少的组,即使对于非常大的数据集,这种方式的速度也可能更快:accessmode='local'。

  相反,您可以强制一个简单的查询使用 MapReduce:accessmode='mapreduce'。如果可提供相应的内存(本地)和资源,这些提示将被兑现。

  不管数据集的大小,运行查询,强制执行这些提示并对其计时,这是一个不错的主意。您可能对时间的差异感到惊讶,我已经看到了每种类型的相同语句之间存在略低于 7 秒和 4 分钟的差异,有时人们倾向于其中一个解决方案。但不要在重新启动 Big SQL 之前按顺序运行查询,因为查询已经被缓存了。

  它可以帮助对复合聚合语句的后续层次执行本地模式。此选项将在较大的数据上执行 MapReduce,但在只需处理几百或几千行代码时,它会使用内存中聚合。
  

  结束语

  Big SQL 试图填补在数据上编写一个复杂的 MapReduce 进程的差距,它用一个类似 SQL 的接口包装该进程。它并不完美,因为它将这些查询转换成了内部流程或 MapReduce 作业,您必须更小心地推敲 SQL。与传统 SQL 环境不同,Big SQL 没有办法优化查询、利用索引或理解数据的结构来优化查询本身。在本文中,您已经看到了一些经典示例,了解了查询的一点小修改会如何对性能产生很大的影响,以及如何更改或编写现有的查询和 MapReduce 作业,使之适用于 Big SQL 环境。




收藏 推荐 打印 | 录入:Cstor | 阅读:
本文评论   查看全部评论 (3)
表情: 表情 姓名: 字数
点评:
       
评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款