by Mark Cusack, Chief Architect, RainStor, @markcusack
One of the questions I get asked most by prospects and customers is: ‘how can I federate queries across my data warehouse and RainStor archive?’
The archiving use-case is pretty straightforward. The data warehouse is kept topped up with the newest, hottest data, and older, colder records are periodically transferred to the archive. The customer’s happy because they’ve offloaded data from their primary EDW, buying back capacity as a result. At the same time, that historical data is fully accessible, and is stored in a more compact and cost-effective RainStor archive. All good stuff.
Not surprisingly, customers want to see a unified view of the data across the two stores; they don’t want to have to run separate reports against each system and then somehow aggregate the results to get the full picture. The good news for Teradata customers is that federating queries across Teradata and RainStor has never been easier, thanks to the Teradata QueryGrid™.
QueryGrid, formerly known as SQL-H, allows HCatalog-registered tables on a remote data source to be addressed within Teradata queries. You can execute a query on Teradata, and include references in that query to remote tables. Teradata and HCatalog deal with the task of combining the remote records with those on the Teradata system.
The RainStor Archiving App on Hadoop, which was released in June, supports HCatalog. RainStor tables register themselves as HCatalog tables with the Hive metastore on the Hadoop cluster that RainStor is running on, allowing third-party tools to access RainStor data in a standard way. With HCatalog support firmly in place, we are able to plug in to the QueryGrid, providing Teradata users with seamless access to their archived data in RainStor.
An overview of how the QueryGrid works is covered very nicely in Doug Frazier’s Teradata Magazine article, so I won’t go into the under-the-hood details here. In summary, the QueryGrid pushes query predicates down to the RainStor cluster at planning time, and pulls back matching partition files into Teradata at query execution time. The Teradata AMPs do the heavy lifting in terms of record processing – the RainStor-on-Hadoop cluster provides the storage and column/partition pruning.
To access RainStor data from within Teradata, a SQL query simply needs to reference the Teradata user-defined function, load_from_hcatalog. Here’s an example:
SELECT quote_date, SUM(bid_size*bid_price)/SUM(bid_size)
columns(‘quote_date, bid_size, bid_price’)
partitionfilter(‘symbol=”GOOG” and ask_exchange=”P”’)
) AS D1
GROUP BY quote_date;
This query generates a time-series of the volume-weighted average price of Google stock quotes stored within the remote RainStor ‘quotes’ table.
A couple of the arguments to the UDF are worth highlighting here. The columns argument allows fields to be pruned from the RainStor dataset. When each AMP on the Teradata system reaches over to HDFS to retrieve a partition, it is able to take advantage of RainStor’s unique row-columnar hybrid file format such that only the columns of interest will be streamed back across the network. The selected columns remain in their compressed form during transfer, helping to reduce the overhead of data movement.
The partitionfilter argument is critical to the viability of the QueryGrid, as it restricts the volume of RainStor records that are retrieved over the network. QueryGrid works by pulling remote records into Teradata temporary spool, where they can be combined with other records according to the query plan. Obviously, if the RainStor table contained Petabytes of data, landing the entire remote table into Teradata spool would be a non-starter. Typically, users constrain their queries to a period or domain of interest, and the partitionfilter argument helps them do that.
RainStor has particularly aggressive and efficient partition elimination. During query planning time, we use our own metastore, which contains minimum and maximum values for every field in every RainStor partition file, to determine which partitions can contribute to the final result set. Non-contributing partitions are dropped from the plan and are never loaded from HDFS.
Teradata pushes the partitionfilter expression down to RainStor during planning time, and RainStor returns a list of the partition files whose data satisfies the expression. Then, during query execution, AMPs only retrieve partitions on the returned list.
With RainStor’s QueryGrid plugin, the partitionfilter argument can be a complex expression featuring multiple columns, and can even include RainStor functions. What’s more, RainStor dynamic filters can be used here too, giving an additional level of partition elimination. Dynamic filters are Bloom filters that can be applied to one or more fields in a RainStor table, providing a probability-based decision on whether a partition needs to be loaded.
The RainStor plugin supports QueryGrid for Teradata 14.10. QueryGrid for Teradata 15 adds some neat new features, including the ability to append data to remote HCatalog tables, support for pass-through queries, and a more elegant syntax for defining and using remote tables. We’ll add support for these features in future versions of the RainStor QueryGrid plugin.