last changed: 25th of May, 2020
SAP (Sybase) IQ – the hidden treasure …
Blog: The Next Generation of SAP Adaptive Server Enterprise (SAP ASE) and SAP IQ
SAP News: SAP ASE and SAP IQ: The Next Generation (November 2019)
LinkedIn: SAP’s Commitment to On-Premise SAP IQ Customers (May 2020)
Sybase IQ was acquired in 2013 together with other Databases, e.g. ASE, the replication technology System Replication Server (REP) and the Event Streaming Platforms, which are incorporated in other SAP Products now. Since this time, the Document – SAP First Guidance – SAP NLS Solution with SAP IQ is available to ease the IQ implementation. So I would recommend the read this Document first, and then proceed with the SAP IQ Overview below.
When it comes to the standard method to move historical data from the primary database (especially not only HANA) the nearline storage (NLS) implementation based on SAP IQ is the defacto standard for all our customers.
unfortunately in the past years the main capabilities of IQ were hide behind the “big HANA wall”, so the majority of our SAP customers are not aware of the unique features which makes SAP (Sybase) IQ so special. the Maximum Limits of IQ also speak for themself!
The most probably reason, why IQ is treated as a black box or an unknown stanger (remember the not so successful launch of dynamic tiering) is the fact, that the installation is still not based on the software provisioning manager (SWPM).
This mayor gap was solved a while ago with Q the easy Installer for SAP IQ (based on SAP Hostagent and is available for Linux SLES and Red Hat). the used components can be downloaded from here and can be used on Hyperscalers or on-premise Linux VM’s.
Q – the easy SAP IQ database installer
The IQ DB Installation routine can be also taken as “the defacto standard” for native IQ based storage as well, which makes it as THE alternative for existing Hyperscaler storage types like S3, Swift, HDFS, ADLS or Spark/Vora.
The main Advantage here: You can implement the data storage on your own premises to be legal compliant while only the orchestration remains in the Hyperscaler, e.g. SAP Data Intelligence 3.0 based on Azure AKS.
column based compression – nothing beats IQ
when it comes to compression of raw or already compressed data by classical databases like Oracle, DB6, MSSQL or our own SAP HANA database, IQ is still able to reduce the data footprint even more.
- Fast data ingest: fully parallel bulk data loading
- Industry leading query engine: rich SQL query processing on complex, ad hoc workloads
- Data compression: pioneering compression and indexing technologies
- Scale out: dynamic scale-out for complex analytics on petabyte-scale data volumes
- Security: comprehensive security features
- Low TCO: commodity blade hardware deployments, with low administrative overhead
- Extensibility: in-database analytics framework
- Elasticity: separation of storage and compute
SAP IQ – a complete platform for XLDB analytics
IQ – a complete platform for XLDB analytics
IQ – versatile application services for XLDB analytics
IQ – query engine scale out – distributed query processing
Massively parallel processing
- Leader node:
Receives and initiates queries, including UDFs
Any node can be a leader, one leader per query, many concurrent leaders possible
Leader node may satisfy query within itself
- Worker node:
Nodes picking up work units from leader
any worker nodes per query, same worker node can serve multiple queries
Worker nodes are enlisted only if leader cannot satisfy query on its own
simplex server architecture fits all purpose
IQ simplex server architecture – SAP IQ database
unstructured content – structured analysis
one of the main concepts of SAP IQ is the storage of huge amount of unstructured data and the capability to analyze this data in a structured way
SAP IQ Administration: Unstructured Data Analytics
The Unstructured Data Analytics Option extends the capabilities of SAP IQ to allow storage, retrieval, and full text searching of binary large objects (BLOBs) and character large objects (CLOBs) within the database.
As data volumes increase, the need to store large object (LOB) data in a relational database also increases. LOB data may be either:
- Unstructured – the database simply stores and retrieves the data, or
- Semistructured (for example, text) – the database supports the data structure and provides supporting functions (for example, string functions).
data load management – you decide and not the RDBMS decides
SAP IQ Administration: Load Management
SAP IQ offers you a choice of methods for adding, changing, or deleting data.
- For efficient bulk loading of tables from flat files, use the SQL statement LOAD TABLE.
- To insert specified values into a table row by row, use the SQL statement INSERT with the VALUES option.
- To insert rows selected from a table (including a table residing in another database), use the SQL statement INSERT with a SELECT statement clause.
- To remove specific rows from a table, use the DELETE statement.
- To change existing rows in a table, use the UPDATE statement.
The IQ data extraction facility exports data in binary or ASCII format, which you can then load into another database. Use this facility for high-volume data movement, or when you need an output file that can be used for loads.
From Interactive SQL, you can export data to another database in a variety of formats, or produce a text file as output. You can also redirect the output of any command.
SAP IQ supports the loading of tables with Parquet format files – parquetjs
Parquet is an efficient, open-source, column-oriented format file designed for Apache Hadoop.
You can load tables in parquet format in the LOAD TABLE statement. See https://parquet.apache.org for more information on Parquet.
SAP question: can json files used to load SAP IQ 16.1
SAP IQ not only a database, it’s a multiple usage server
SAP IQ allows you to interact with your data in all kind of Formats, services or API’s. one of the important features is to use it an an OData server in combination with HTTP/HTTPS build-in support.
How to set up an OData server in SAP IQ
To enable the additional server capabilities simply add them to the startup file SAPIQDB.cfg and restart the SAP IQ server
-xs odata(port=1180)
-xs http(port=1080)
-xs https(port=1443;IDENTITY=/sapmnt/NLS/exe/sec/rsaserver.id; IDENTITY_PASSWORD=test;FIPS=yes)
test examples for /sapmnt/<SID>/SIQ<nr>/IQ-16_1/samples/certificates
/sapmnt/<SID>/SIQ<nr>/IQ-16_1/samples/sqlanywhere/http
/sapmnt/<SID>/SIQ<nr>/IQ-16_1/sdk/php/examplesFurthermore the in-build SQL Anywhere server (also the sql interface for SAP IQ) gives you various options to use the additional functions. A good start to discover all these features can be found in the
SAP Help Section – HTTP web service examples – OData server samples – IQ spatial features
Picture Gallery based on HTTP/OData service
to show quickly within minutes the unstructured data capabilities is the Gallery Example, a little application to store and analyze pictures of all kind. Here you are using the database server as a web client. within that directory you will find several more sql scripts, e.g. a JSON example. you can run all these sql scripts from the interactive SQL client for IQ, See Installing SAP IQ Client Software.
Not all Examples are explained in the SAP online help, and the examples are easy to understand and to test instantly. use the SAP IQ Cockpit to check your compiled Objects and create your own services in a easier way than the SAP help description
this is just the beginning, find out more …
there is so much more to find out how to use SAP IQ for your own purposes and include SAP IQ in hybrid scenarios like seen below.
Blog: Unified Data Integration for SAP
see how SAP Data Intelligence can orchestrate the use from and to SAP IQ for Big Data Challenges.
Intelligent Data Orchestration Platform
SAP Data Intelligence Integration
Roland Kramer, SAP Platform Architect for Intelligent Data & Analytics
@RolandKramer
“I have no special talent, I am only passionately curious.”
Original Article:
https://blogs.sap.com/2020/05/07/sap-sybase-iq-the-hidden-treasure-…/