Thinking about the Kylin result cache

Origin

Apache Kylin Positioning is a second-level SQL query engine with large data volumes, based on the fact that all possible combinations of dimensions are expected to be stored in hbase, when the query resolves the SQL to get the dimension and metric information, and then the scan from hbase gets the data back. Personally, Kylin is the most powerful place in the implementation of the SQL engine, if the use of custom formatted query language can also complete the corresponding data access operations, nothing more than the dimension of the specified query, measures, aggregation functions, filtering conditions, sequence and so on.

However, this description is much weaker than SQL, and SQL is flexible in translating some complex semantics, such as the statement in Kylin that does not support select XXX where xx in (Selext xxx), but can be implemented by a subquery join. This limitation leads to a lot of complex SQL. In addition, due to the large number of cardinality that may exist in Kylin, the use of this column for group by will result in the need to read large numbers of records from HBase for aggregation operations or even sorting. The SQL engine in Kylin uses calcite for SQL parsing, optimization, and some operator operations, and calcite's calculations are completely memory-based, so memory becomes a bottleneck when a query in Kylin needs to fetch a large number of records from HBase.

OLAP queries are often based on historical data, the most important feature of historical data is immutable, even if occasionally due to program bugs cause the data need to repair, to this infrequently changing data query, and each query may consume a lot of resources, the cache is the most common and most effective way to improve performance.

Current Situation

Kylin does not cache query results, and each query determines whether the results are cached based on whether the total number of records scanned by the query exceeds the threshold (in this sense, the cache of values). However, this part of the cache is based on native memory, and is not shared between instances, and the general Kylin query server is the schema of multiple independent servers through the previous load Balancer request forwarding, such as, so this part of the cache is not shared. There are several drawbacks to this kylin caching mechanism:

• Cached in native memory, the most needed memory resource for queries is a consumption.
• The inability to share between machines causes queries to be fast and slow, and creates unnecessary waste of resources.
• Cannot automatically expire, the build needs to be cleaned up manually, otherwise the result may be an error. For example, the query selects the Count (1) from XXX; When a new segment build is completed and the results of the two queries are the same, the second is clearly cached, and the build succeeds without cleaning up the cache and requires manual cleanup.

Improved

Understanding the current cache of the Kylin, for the above two points to improve, the most direct solution is to move the cache to the external cache, the preferred Key-value cache is of course redis, for example, according to the design of the cache now, you can query the SQL and the project as the key, The query results and statistics in some queries are cached as value. However, the 3rd need to design a specific automatic expiration plan for the implementation of Kylin.

kylin data calculation and query flow

Kylin is based on precomputed calculations, which calculate the aggregated results (SUM, count, and so on) of all defined dimension combinations, since it is necessary to accumulate a certain amount of data for aggregation, kylin by defining one or two (new version, minute-level granularity) partitioning fields when cube is created. Based on this field to obtain the input data interval for each estimate, the results of each interval calculation in Kylin are called a segment, and the expected results are stored in a table in HBase. Typically, this partition field corresponds to the partition field in hive, with days as an example, and one day of data per estimate. This process is called build.

In addition to the new data calculation for each time interval forward or backward in the build, there are two ways to handle the computed data that has been completed. The first is called refresh, and when the original data (hive) of a data interval changes, the expected result is inconsistent, so the segment of the interval needs to be refreshed, that is, recalculated. The second is called the merge, because each input interval corresponds to a segment, the results are stored in a htable, over time there will be a large number of htable, if a query involving a long span will lead to many of the table scan, performance degradation, This can therefore be optimized by merging multiple segment into a single large segment. However, merge does not make any changes to the existing data.

To say a digression, in the Kylin can set the time interval of the merge, the default is 7, 28, indicating that the data of the day before the build will automatically do a merge, the 7 days of data into a segment, when the last 28 days of data calculation is completed again after the merge To reduce the number of htable scanned. However, it is not possible to set up the data that is often required for refresh, because once it is merged, it is a waste of time to refresh the entire merged segment.

Having finished the data calculation, let's talk about how this part of the estimated data is being used, calcite the parsing of the SQL and callback the Kylin callback function to complete the record of each operator parameter, here we only need to care about how the query is positioned to the scanned htable. When a query involves partitioning fields that are used in building a cube, each scan needs to scan the entire partition because the partition field is typically a dimension field. So here's the indication that the field appears in the WHERE clause or the GROUP BY clause, which is discussed in several different situations (assuming the partition field is DT):

• There are no partitioning fields in SQL, such as select Country, Count (1) from table group by country, which requires a statistical data design for all the estimated time periods and future calculated time intervals, So it needs to scan all the htable to finish. In this case, the build and refresh calculations will cause the results to change.
• The WHERE clause in SQL uses a partition field, such as select Country, Count (1) from table where dt >= ' 2016-01-01 ' and DT < ' 2016-02-01 ' this case can be based on the time zone Only part of the htable is scanned. In this case, only the refresh operation of this time interval will change the query results, and if the filtered time interval contains a future time, the build operation will cause the result to change.
• The partition field is used in the group by in SQL, which, like 1, requires scanning all htable to get the result.
• Partition fields are used in both the group by and where fields in SQL, which is similar to 2, after all, SQL execution is performed first where the group by is executed.

Analysis

Since data calculations and query results have this relationship, you can use this relationship to solve one of the most difficult problems in caching-cache expiration, which is problem 3 in the status quo, but queries are often very complex, such as multiple subquery joins, multiple sub-query unions, and not easily get group The contents of the by and where clauses, fortunately, this query or each subquery design information is saved in the Olapcontext object during Kylin each query, and one query may generate multiple Olapcontext objects, which are stored in a stack. And this stack is a thread-local variable, so you can get all the information in this query by traversing the contents of the stack, including the cube used, the group by which columns, all the filtering conditions, and so on.

Cache Design

• cached Key: Query request, including SQL, project, limit, offset, ispartial parameter, that information
• Cached value: The query returns results, including the result data and metadata, as well as other statistics, including which cubes are used, and what the time partition filter for each cube is. And the time the cache was added.
• Add cache : After the query obtains the result to join the Key-value to the cache, the Kylin native cache caches the exception, does not cache here, mainly because the Kylin internal exception mainly divides into three kinds: accessdeny, syntax error and other data access exception , the first native cache will not be stored, the second exception query metadata can be judged and no need to cache; The third is a system exception, and the cache may cause the repaired query to continue to appear in error. Therefore, only queries that execute successfully are stored in the cache.
• Cache Expiration Time : Can be configured, in fact, this value setting does not matter, just in order to be long time not to query the key to delete it, you can set a longer.
• Cache invalidation : Based on the above analysis, both build and refresh in the data calculation may change the results in the cache, so this part of the cache needs to be automatically invalidated, check the way cache is invalidated by two kinds, an active type, At the end of each data calculation task to traverse the entire cache value to determine whether it is invalid, the other is passive, when the cache is actually read to determine whether it is invalid, the non-read invalid cache will be automatically deleted as the expiration time arrives. The former requires the support of the Redis Keys command, which simply executes a single record based on the last statistic executed after reading the value, and it is obvious that the passive approach is more appropriate in this case. The logic of the judgment is as follows: When each data calculation task is completed (either build, refresh, or merge), the last update time of the segment is recorded (when the cube metadata is updated), and the cache content is first retrieved based on key when looking for the cache pair. That is, the cached value, and then look at the partition field filter interval for each cube used, to see if the segment of that interval has the last update time greater than the cache add time. If there is a description that the data has been updated, the cache is freed, otherwise the data is not updated and the current cache can continue to be used as a result.

Conditions and Description

• An identical SQL two query uses the same cube.
• The last update time for each segment is always valid and does not occur at the wrong time.
  * The metadata for each query server is the same, and there may be a short time error between different machines in real-world situations.
• Only the filter period of the first-level time partition (days) is counted, if no filtering occurs in the Where condition (case 1,3) then the filter interval is [0, Long.max_value]
• Although the merge does not result in data modification, one of the segment is either build or refresh before merge, and this information is not available, so the merge needs to be treated the same as refresh.

Optimized

• Cache as a lock, the same query can be ordered through the external cache, if the cache can be hit directly from the cache to get results, otherwise the query first joined as a key, the value is set to IsRunning for lock, after the same query waits before the return of the query results, You can determine whether the first query is completed by subscribing to the publish mode or by polling the isrunning identity, and placing multiple identical queries at the same time.
• SQL standardization, the query of SQL may exist only a single space, although the semantics are exactly the same, but as key is not the same, you can trim the SQL in the extra space (except in the string) to complete the standardization of SQL syntax, more fully utilize the cache.
• Only the results of successful query execution are cached. Do not cache exceptions.
• Manually clear the exception interface.

Pseudo Code

//首先检查key是否存在if exists key     value = get key     //如果key存在可能存在两种情况：已经有结果或者正在查询     if value is running          waiting for finish(loop and check)  //轮询是个更好的办法，效率稍低     else           //即使有结果也可能存在结果已经失效的情况          if value is valid               return value          else                //为了保证互斥，使用setnx语句设置               set if exist the value is running               if return true  run the query               else  wait for finish(loop and check)else     //不存在则直接表示running     set if exist the value is running     if return true run the query     eles waiting for finish(loop and check)

Summary

Based on the analysis of the existing cache strategy in Kylin, this paper proposes a scheme using external caching, which can be coded and tested based on this, hoping to achieve better results.

Thinking about the Kylin result cache