5. Materialisation and Cache

5. Materialisation and Cache

For a virtualized table/view to be queried faster, it can be materialized into any RDBMS. Zetaris Cloud Data Fabric also supports cache capabilities which loads  all data into memory.

Materialisation 

The following query materializes all data from a RESTful Service to USER_FOR_COPY table in fusion db.

INSERT INTO FUSIONDB.USERS_FOR_COPY

SELECT uid, gender, age, job, ts FROM SAFC.SAFC_USERS


Cache/Uncache

HubSpot Video
HubSpot Video

A user can load/unload all data into main memory by leveraging cache/uncache command.

CACHE TABLE AWS_S3.pref;

CACHE TABLE ORCL.movies;

The pref table in aws s3 bucket and movies table in oracle are now cached into memory, and the following query performs a lot better :

SELECT movies_from_oracle.title, hdfs_pref.count, hdfs_pref.min, hdfs_pref.max, hdfs_pref.avg

FROM (

  SELECT iid, count(*) count, min(pref) min, max(pref) max, avg(pref) avg

  FROMAWS_S3.pref

GROUPBY iid

) AS hdfs_pref, ORCL.movies movies_from_oracle

WHERE movies_from_oracle.iid = hdfs_pref.iid

And, uncache :

UNCACHE TABLE AWS_S3.pref

UNCACHE TABLE ORCL.movies