|
|
|
FairCom takes the lead in bridging the gap between SQL and the more-efficient direct record access.
Much of today's database industry news is filled with terms such as "Big Data" and NoSL and rest assured, FairCom maintains an active watch on these new data frontiers.
We regularly hear of datasets measured in hundreds of terabytes and even petabytes. Realizing the value of data, you've possibly accumulated thousands of files over the years and now you require an efficient query capability across those file.
c-treeACE SQL has the ability to "import" all your tables into a complete modern SQL interface. However, in many situations, traditional SQL views are simply not feasible due to the sheer number of tables involved.
Consider what your "big data" challenge may be:
"How do I sort through all of this accumulated data from my very successful c-treeACE application"?
FairCom enjoys the challenge and introduces FILESET, a broadened query capability bringing modern advanced query to your existing volumes of data.
Our new FILESET feature allows SQL queries directly over multiple external c-tree files. This new c-treeACE SQL extension brings advanced data access for BI, statistical analysis and other complex query and reporting needs.
Introducing Dynamic FILESET
FILESET allows you to operate on a number of files as a single source when making SQL queries. Realizing data was already "partitioned", FairCom engineers looked in the opposite direction and created partitioned tables on-the-fly!
A single master host table is defined using an established schema of existing tables. A new FILESET host creation utility is available allowing developers to point to an existing data table and create a fully functional host table with all required attributes.
At query time, this host table is referenced with a sequence of physical table names to produce a single FILESET™ table. Using dynamic partitioning techniques from our powerful partitioned file feature, c-treeACE automatically links together individual tables into a unified data view.
SELECT * FROM FILESET(custmaster_host, '+customer2013.dat+custmaster2014.dat+custmaster2015.dat');
With FairCom's new SQL FILESET™ feature, SQL statements can be defined with in-line tables built on-the-fly from specific physical data files. Dynamically assigning files to a host table, queries can be executed directly against these tables, including sophisticated joins of multiple dynamic host tables.
c-treeACE SQL grammar has been extended allowing a dynamic list of partitions to be specified when executing queries on dynamic partitioned files. c-treeACE SQL accesses necessary files and makes them appear as a single table to SQL, thereby eliminating the overhead of creating many SQL views over a large number of files. A FILESET is created dynamically—on-the-fly—such that SQL sees the results as a simple, static table.
Find your “Needle in a Haystack”
The FILESET concept can be used to simplify SQL queries. Rather than defining complex views or writing a complex query across multiple files—possibly thousands—FILESETs allow you to create a simple query as though you were searching only a single table. A new function allows you to define a list of dynamic partition members such that you can set the partition table dynamic members when using FILESETs.
Syntax
FILESET ( hosttablename, string_of_physical_files )
string_of_physical_files
[ separator_char
Example
SELECT name, zipcode, data
FROM FILESET(hostmaster, '+/home/data/volume1.dat+/home/data/volume2.dat+/home/data/volume3.dat')
WHERE data > 99
ORDER BY zipcode
