Sybase IQ

From Wikipedia, the free encyclopedia
Jump to: navigation, search

Sybase IQ is a column-based, relational database software system used for business intelligence, data warehousing, and data marts. Produced by Sybase Inc., a SAP company, its primary function is to analyze large amounts of data in a low-cost, high-availability environment. It can be deployed on Windows, Unix and Linux Operating Systems.[1] Sybase IQ is often credited[2] with pioneering the commercialization of column-store technology.

Since SAP AG’s acquisition of Sybase in July 2010, Sybase IQ is now part of the broader database technology portfolio.

History[edit]

In the early 1990s, Waltham, Massachusetts-based Expressway Technologies, Inc. developed the Expressway 103, a column-oriented database engine optimized for analytics, that would eventually become Sybase IQ. Sybase acquired Expressway and re-introduced the product in 1995 as IQ Accelerator, then renamed it shortly thereafter to Sybase IQ, giving it version number 11.0.[3]

By offering the IQ product as part of a collection of related technologies often found in a data warehouse (including Sybase Adaptive Server Enterprise, Replication Server, PowerDesigner, and SQL Anywhere), Sybase became one of the first mainstream companies to acknowledge the need for specialized products for the data warehouse market.[1]

With version 12.0, Sybase replaced the loosely coupled query interface from Adaptive Server Enterprise with a tight coupling with SQL Anywhere.

Sybase IQ won the Waters Rankings’ Best Data Management Solution in 2008, 2009, 2010, and 2011.[4]

  • Version 15.4: Released November, 2011 (Enhanced PlexQ® technology framework for Big Data analytics with enhanced in-database analytics framework to support a native MapReduce API)[5]
  • Version 15.3: Released June, 2011 (Added PlexQ® Technology framework, Massively Parallel Processing [MPP] grid architecture with a shared-everything approach)[6]
  • Version 15.2: Released June, 2010 (Added query federation, ability to analyze structured and unstructured text data)[7]
  • Version 15.1: Released July, 2009 (Added in-database analytics for running external C++ code inside the engine)[8]
  • Version 15.0: Released March, 2009 (Revamped the query and loading foundation to take advantage of new multi-core chip architectures and multi-tier storage technologies)[9]
  • Version 12.7: Released August 2006
  • Version 12.6: Released December 2004
  • Version 12.5: Released October 2002
  • Version 12.4.3: Released May 2001
  • Version 12.4.2: Released June 2000
  • Version 12.4: Released September 1999 (as Sybase IQ)
  • Version 12: Released February, 1999 (as Adaptive Server IQ; included integration with SQL Anywhere)
  • Version 11: Released December 1994 (as IQ Accelerator)

Markets[edit]

Sybase IQ is primarily targeted to three different use cases: high-performance DBMS engine for business reporting, advanced analytics, and enterprise data warehousing. Sybase targets these use cases for select verticals, including financial services, telecommunications, information providers and government. The company has published case studies that show market penetration across the globe with large installations concentrated in North America and Asia Pacific.

Technology[edit]

To a user, Sybase IQ looks just like any relational DBMS with a SQL-based language layer accessible via ODBC/JDBC drivers. However, inside, Sybase IQ is a column-oriented DBMS, which stores data tables as sections of columns of data rather than as rows of data like most transactional databases.

Column-orientation has a number of advantages.[10] If a search is being done for items matching a particular value in a column of data, only the storage objects corresponding to that data column within the table need to be accessed. A traditional row-based database would have to read the whole table, top to bottom. Another advantage is that when indexed correctly, a value that would have to be stored once in each row of data in a traditional database is stored only once, and a bitwise index is used to access the data.[1]

Additionally, the column-based storage enables IQ to compress data efficiently on the fly; as each column is made up of a number of records of the same data type and size, compression can be very efficient and rapid. [11]

Sybase IQ has a massively parallel processing (MPP) framework based on a shared-everything environment called PlexQ®. Most other products capable of MPP tend to be based on shared-nothing environments. The benefit of shared-everything is that it's more flexible in terms of the variety of queries that can be optimized—especially for balancing the needs of many concurrent users. The downside is that in extreme cases, competition among processors to access a shared pool of storage (usually a storage-area network), can lead to I/O contention, which affects query performance.[12] However, the PlexQ® framework of Sybase IQ allows compute and storage layers to scale out independently of each other and also allows these resources to be provisioned on-demand for better utilization.

In July 2007, at Sun Microsystems' request, InfoSizing verified the population and performance[13] of the world's largest data warehouse implemented in history, consisting of over one petabyte (1,000 terabytes) of structured and unstructured data, designed and implemented by BMMSoft using Solaris 10 OS and Sybase IQ. Sun Microsystems and Sybase claim that the system architecture used in this benchmark is highly efficient, yielding a reduction in equipment and processing needed and thus a reduction in electric energy consumption.[14][15]

Since databases with limited domains (e.g., that contain state names like VA, MD, WV, NJ, etc.) and high row counts are incidentally compressed by storage in column form (as only one example of each value in the domain is stored), those types of databases can be stored using less space than those employing more traditional row storage. Sybase claimed that in the case of the BMMSoft data warehouse, their one petabyte of raw transactional data (or 500 terabytes of transactional and 72 terabytes of multimedia) was represented using only 260 terabytes of storage.

Sybase IQ has an open interface approach towards its ecosystem. Most of the popular commercial and open source business intelligence and data integration tools are certified to work with Sybase IQ. Moreover, now as part of SAP, Sybase IQ is also integrated with SAP’s Business Intelligence portfolio of products to form an end-to-end business analytics software stack.

Sybase IQ also supports plugging in external algorithms written in C++ and Java. SQL queries can call these algorithms, allowing for the execution of in-database analytics, which provides better performance and scalability. Additionally, Sybase IQ also provides drivers for access via languages such as PHP, Perl, Python, and Ruby on Rails.

Sybase IQ supports most major operating system platforms, including:

Customers[edit]

Sybase claims that Sybase IQ is currently installed in over 2000 customer sites. Notable customers include comScore Inc.,[16] CoreLogic, Investment Technology Group (ITG),[17] and the U.S. Internal Revenue Service (IRS).[18]

While Sybase IQ has been widely used for focused, data-mart-style deployments,[12] it has also been deployed as an enterprise data warehouse.

External links[edit]

References[edit]

  1. ^ a b c Moore, Trevor (2010), Sybase IQ Survival Guide, p. 16, ISBN 978-1-4466-5758-4 
  2. ^ C-Store: A column-oriented DBMS, Stonebraker et al., Proceedings of the 31st VLDB Conference, Trondheim, Norway, 2005
  3. ^ Cole, Barb (November 7, 1994), Sybase makes a data warehousing play via acquisition, Network World 
  4. ^ Daly, Rob (July 14, 2011), Waters Rankings 2011 – All the Winners and Write-Ups, Waters Technology 
  5. ^ Kanaracus, Chris (November 1, 2011), Sybase IQ 15.4 Features 'Big Data' Theme, Support for Hadoop, MapReduce, PC World 
  6. ^ Kanaracus, Chris (July 7, 2011), SAP’s Sybase adds scalability to IQ analytic database, ComputerWorld 
  7. ^ Henschen, Doug (May 25, 2010), Sybase IQ Takes On Text Analysis, Information Week 
  8. ^ Five Minutes of News: Sybase IQ 15.1, The IT-Finance Connection, August 3, 2009 
  9. ^ Babcock, Charles (February 24, 2009), Sybase Updates Its Analytic Server IQ 15, Information Week 
  10. ^ MacNicol, Roger; French, Blaine (August 2004), Sybase IQ Multiplex – Designed For Analytics, Proceedings of the 31st VLDB Conference, Trondheim, Norway 
  11. ^ accessed September 11, 2007
  12. ^ a b Henschen, Doug (July 12, 2011), Sybase IQ Gains Beefier Analysis Capabilities, Information Week 
  13. ^ http://www.sun.com/service/refarch/datawarehouse/Ready-Time-Report_R1.2.pdf | Sun-Sybase-BMSoft solution Auditing
  14. ^ http://www.sybase.com/detail?id=1054047 | Sybase IQ Powers World's Largest Green Data Warehouse Including Unstructured Data
  15. ^ http://www.sun.com/service/refarch/datawarehouse/WLDWSolutionBrief.pdf | World's Largest Data Warehouse Solution Brief
  16. ^ Henschen, Doug (November 24, 2010), ComScore’s Big Data Deployment In Detail, Information Week 
  17. ^ Clark, Don (November 18, 2007), Start-Ups Mine Database Field — Nimble Software Helps Make Sense Of Information Tide, Wall Street Journal 
  18. ^ Lai, Eric (March 22, 2008), Been audited lately? Blame the IRS's massive, superfast data warehouse, ComputerWorld