New HP Vertica: Dragline 7.1

    image
    On August 8, 2014, the new version of HP Vertica 7.1 was released. The team of Michael Stonebreaker continues to claim that working with big data is akin to BAM and continues to issue new titles with a construction theme. So, with a Bulldozer (version 6), the data was leveled up according to the tables, on top they were attached with unstructured data to the Flex zone (version 7.0), it was time for the big Excavator to turn the river back. Meet the version of Dragline 7.1! In this article I will describe what has changed in the new version.

    Projection Functionality Extensions


    Let me remind you for those who are in the know and tell for those who do not know: the projection in Vertica is the materialization of table data. A table in Vertica is a description of table structure (columns), constraints, and partitions. And directly the data is stored in the projections that are created on the tables. Projections are somewhat similar to indexes; they store data on all or not all columns of a table. There can be more than one projection per table, projections can store segmented and sorted data according to different rules. Data in all projections is automatically updated when table entries are updated. In fact, projections contain table data for all columns or partially defined columns. Cluster server disk space is sacrificed, but fetching for different query groups is significantly accelerated.

    Projection Expressions

    Prior to the new version, only projection columns could be specified in projections. This imposed certain restrictions on the use of projections. For example, if queries in the filter often used an expression by the columns of the table, searching for this filter was not as effective as the projection could not specify the sorting of stored data by expression. Sorting by expression columns is unlikely to help improve performance. This could result in a rather serious problem. As a solution, you would need to add a new column to the table in which you can save the calculation result. It was also required to change the algorithm for loading source data into this table to fill in the calculated column during loading. I would also have to overload the entire table, to fill in the added field. If there are tens and hundreds of billions of records in the table and it is constantly being downloaded, such a solution would be physically impossible.

    The new version for projections introduced the ability to specify both columns and expressions:
    CREATE PROJECTION sales_proj (sale_id, sale_count,  sale_price, sale_value) AS
      SELECT sale_id, sale_count, sale_price, sale_count * sale_price
      FROM sales 
      ORDER BY sale_count * sale_price
      SEGMENTED BY HASH(sale_id) ALL NODES KSAFE 1;
    


    The following query for the created table projection:
    SELECT *
    FROM sales_proj_b0
    WHERE value > 1000000
    ORDER BY value;
    

    when executed, it will actually instantly return the result using sorting the expression.

    The following restrictions are imposed on such projections:
    • You cannot use functions that can change the result (for example, the TO_CHAR function, since it will return a different result depending on the client encoding set)
    • Do not use utility meta functions
    • Table records cannot be updated with the MERGE statement (UPDATE and DELETE are allowed)


    Projections of this type can be created and rebuilt on a table at any time, without stopping users from working with it and loading data. Thus, the problem of including a calculated column in sorting to improve query performance is no longer relevant.

    Top-K projection

    This is a new type of projection in Vertica. The task of such projections is to speed up the execution of TOP queries in fact as much as possible. Let me give you a simple example, let’s say you often need to monitor the last five counters:
    SELECT meter_id, reading_date, reading_value FROM ( 
       SELECT meter_id, reading_date, reading_value, ROW_NUMBER()
       OVER (PARTITION BY meter_id ORDER BY reading_date DESC) rn FROM readings) sq 
       WHERE  rn <= 5;
    


    For a table with many records, the query will take resources and time. Top-K projections allow you to materialize and store TOP values ​​by moving the time for calculating TOPs from the moment queries are executed to the moment of adding data to the table:
    CREATE PROJECTION readings_topk (meter_id, recent_date, recent_value) AS
      SELECT meter_id, reading_date, reading_value 
      FROM readings
      LIMIT 5 OVER (PARTITION BY meter_id ORDER BY reading_date DESC)
      KSAFE 1;
    


    It is worth noting that in order to simplify obtaining TOP data values ​​in Vertica, the SQL syntax has been specially expanded and data queries can now be written like this:
    SELECT meter_id, reading_date, reading_value 
    FROM readings LIMIT 5 OVER (PARTITION BY meter_id ORDER BY reading_date DESC);
    


    To quickly access the TOP of counters, it’s now enough to write a query to the created projection of the table:
    SELECT * FROM readings_topk;
    


    The restriction on this type of projection is the prohibition of changing or deleting records in tables for which such projections exist. Thus, this type of projection is only suitable for tables in which data is always only added (insert only).

    When inserting new records into the table of meter readings, the created projection will automatically update its TOP values:
    image

    Live aggregate projections

    Also a new type of projection. By analogy with TOP-K projections, this type is designed to materialize data from fact tables. The objective of this type of projection is to speed up simple aggregate queries using the aggregate functions COUNT, MIN, MAX and SUM:
    CREATE PROJECTION clicks_agg (user_id, page_id, click_date, num_clicks) AS 
       SELECT user_id, page_id, click_time::DATE click_date, COUNT(*) num_clicks 
       FROM clicks 
       GROUP BY user_id, page_id, click_time::DATE;
    


    After creating and updating this projection, in the future instead of performing such aggregate queries on the clicks table, you can simply take data from the projection, instead of calculating them every time the query is executed:
    SELECT user_id, page_id, click_date, num_clicks
    FROM clicks_agg;
    


    The restriction on this type of projection is the prohibition of changing or deleting data on tables for which such projections were created. It is also forbidden to change the name of the table, its columns and their types, which they participate in a live aggregate projection.

    PS Please note that on Top-K and live aggregate projections you cannot specify segmentation and sorting when creating. They are automatically set based on the GROUP BY projection request. The enumeration of fields in GROUP BY has a role, since it is used for sorting and segmenting a projection.

    Moving partitions between tables


    In an ideal storage, data is mostly added (facts) and sometimes changed (dimensions). Alas, ideal repositories do not exist. Therefore, the operations of changing facts in the data warehouse are a fairly common and necessary thing. The Vertica architecture is primarily focused on collecting data from multiple sources in close to real time and quickly performing analytical queries on large amounts of data. For such an architecture, changing and deleting reasonable volumes of records for measurement tables is a regular and fast operation for Vertica. However, changing a huge number of fact records for Vertica is an operation that can lead to a decrease in the performance of the entire cluster, especially if it is made often and thoughtlessly. The optimal solution here is the option when two tables are created. The records of the active period are stored in one table, in which records are added and changed. The second table contains records of closed periods that can no longer be changed. Such tables are then combined in a view through UNION ALL, which is then used by end users. In this method, the problem is the very moment of closing the period when you want to transfer records from the active to the historical table. To do this with INSERT / DELETE queries within a single transaction is long and not effective if there is a lot of data in the closing period (hundreds of millions or billions). which is then used by end users. In this method, the problem is the very moment of closing the period when you want to transfer records from the active to the historical table. To do this with INSERT / DELETE queries within a single transaction is long and not effective if there is a lot of data in the closing period (hundreds of millions or billions). which is then used by end users. In this method, the problem is the very moment of closing the period when you want to transfer records from the active to the historical table. To do this with INSERT / DELETE queries within a single transaction is long and not effective if there is a lot of data in the closing period (hundreds of millions or billions).

    The function MOVE_PARTITIONS_TO_TABLE has appeared in Vertica 7.0.1, which solves the sounded problem:
    SELECT MOVE_PARTITIONS_TO_TABLE ( 'fact_active', 2012, 2013, 'fact_history');
    

    In this example, the 2012 and 2013 partitions are transferred from the fact_active table to the fact_history table. The operation is atomic and transparent for users of these tables. If the user uses the view from UNION ALL to these tables, then even at the time the query is executed, when this function works, he will get the correct result. The transfer operation is low-cost, since data is transferred in blocks at the physical level of ROS containers, rather than logical records.

    In the new version 7.1 there is a function that allows you to swap partitions between two tables:
    SELECT SWAP_PARTITIONS_BETWEEN_TABLES( 'fact_active', 2012, 2013, 'fact_history');
    

    In this case, the records of portable partitions of the fact_active table are transferred to the fact_history table, and the partitions of the fact_history table are transferred to fact_active.

    COPY extension


    LZO has been added to existing support for the BZIP and GZIP compression formats.
    The support for distributed file uploads with the COPY command has also been improved. In version 6.1, file loading has already been improved by parallelizing file loading on the node by simultaneously loading the file in chunks. Vertica can now distribute portions of the downloaded file between multiple nodes in the cluster, speeding up the download of oversized files.

    Support for working with nested arrays and nested map data in Flex tables


    A fairly important extension, since working with JSON structures already implies the frequent use of nested arrays. To solve the problems of such a circle, Vertica expanded the functionality of the parser for loading data from JSON into Flex tables and introduced a number of functions that allow parsing on-the-fly embedded arrays and cartographic data in CSV and JSON formats.

    New projection column encodings


    In version 7 of Vertica, new column types long binary and long varchar were added, allowing you to store large blobs. These columns were not encoded in any way, although it was requested that they be compressed to save disk space and reduce disk operations. In the new version, the encoding types BZIP_COMP and GZIP_COMP were added for this.

    Active backup nodes


    The new version of Vertica adds the ability to connect redundant nodes to the cluster, which are connected to the cluster and work, but do not store data and do not perform any calculations. In the event of a server dropping out of the cluster and exceeding the recovery time limit in the cluster, Vertica automatically switches the backup node to active mode and replaces the broken server with it. The backup server transfers to itself all the data that was stored on the mirror of the neighbor of the broken node and begins full-fledged work in the cluster. If necessary, the administrator can explicitly specify to replace the fallen server with a backup one, without waiting until its automatic replacement begins. When choosing a server replacement, Vertica will focus on the description of the Fail Group cluster. If the servers are distributed in groups (racks), then Vertica will try to find and start the backup server located in the same group (rack) with the crashed one. If no suitable servers are found or the cluster is not divided into server groups, the first suitable server will be taken. After the server returns, the administrator can switch it back to the cluster. In this case, the backup server will give its data and will go back into standby mode again.

    This feature allows you to simplify the management of a large cluster, allowing companies to play safe from loss of productivity in case of problems with hardware and reducing the requirements for continuous monitoring of administrators on the operation of the cluster.

    REST API Support


    There was an opportunity through https to receive information from the Vertica server on the cluster status, licenses and manage the server and intercept events. The list of functionality actually duplicates everything that is implemented in the Management Console web console and allows, if necessary, to integrate server management and monitoring into its own systems.

    Dynamically moving running queries between resource pools


    For those who are not in the know, the resource pool in Vertica is an area of ​​resources that has its own memory, parameters of competitive work and priorities. Each user is attached to a specific resource pool and his requests are executed within his pool. Very often there are situations where the user is a priority, his requests are not "long", but should be executed as soon as possible, regardless of the total cluster load. As an example, call center operators (Centers) with users with problems hanging on their phones. Operators should receive information on the work of these users in the data warehouse as soon as possible. It is optimal for operators to make their own resource pool with the highest priority. But what to do, if the operator needed to look at the user more information than planned? For example, he could not determine the origins of the problem from the information of the current year and requested data from previous years. Such a request will no longer be “short”, it will take a longer time, drawing up pool resources and a place in the execution queue, thus slowing down the overall work of CC operators. In previous versions of Vertica, the only way out was to forbid the operator to fulfill requests for such a large amount of data and to offer him to execute the request under a different login, which is tied to a lower-priority and productive pool. It is already clear here that having requested so much data, the operator and his client are ready to wait and the instant execution of the request is not expected. He could not determine the source of the problem from the information of the current year and requested data from previous years. Such a request will no longer be “short”, it will take a longer time, drawing up pool resources and a place in the execution queue, thus slowing down the overall work of CC operators. In previous versions of Vertica, the only way out was to forbid the operator to fulfill requests for such a large amount of data and to offer him to execute the request under a different login, which is tied to a lower-priority and productive pool. It is already clear here that having requested so much data, the operator and his client are ready to wait and the instant execution of the request is not expected. He could not determine the source of the problem from the information of the current year and requested data from previous years. Such a request will no longer be “short”, it will take a longer time, drawing up pool resources and a place in the execution queue, thus slowing down the overall work of CC operators. In previous versions of Vertica, the only way out was to forbid the operator to fulfill requests for such a large amount of data and to offer him to execute the request under a different login, which is tied to a lower-priority and productive pool. It is already clear here that having requested so much data, the operator and his client are ready to wait and the instant execution of the request is not expected. In previous versions of Vertica, the only way out was to forbid the operator to fulfill requests for such a large amount of data and to offer him to execute the request under a different login, which is tied to a lower-priority and productive pool. It is already clear here that having requested so much data, the operator and his client are ready to wait and the instant execution of the request is not expected. In previous versions of Vertica, the only way out was to forbid the operator to fulfill requests for such a large amount of data and to offer him to execute the request under a different login, which is tied to a lower-priority and productive pool. It is already clear here that having requested so much data, the operator and his client are ready to wait and the instant execution of the request is not expected.

    In version 7.1, this problem was solved by the ability to set the dynamic movement of the query execution to another pool when the execution time was exceeded:
    CREATE RESOURCE POOL userOverflow RUNTIMECAP '5 minutes';
    CREATE RESOURCE POOL user RUNTIMECAP '1 minutes' CASCADE TO userOverflow;
    CREATE USER "user1" RESOURCE POOL user;
    

    Two pools are created in this script, user requests are executed in the user pool, however, if they last more than 1 minute, the request is moved to the userOverflow pool for execution, in which the request is allowed to run for up to 5 minutes.

    For those who know English, I give a block diagram of the operation of requests with pools:
    image

    Management Console Update


    The HP Vertica web console has finally been finalized to a solid level of a complete utility for administrators. Now it allows you to fully monitor and manage the operation of clusters.

    The design has been significantly redesigned and the interface’s usability has been improved:
    image

    Now you can monitor requests that are executed, in progress, and pending in the queue:
    image
    For requests, you can get statistics on their execution (profiling), see the query plan, or interrupt work.

    Also, it became possible to monitor work and administer resource pools, analyze queries to build more optimal projections on tables, evaluate the performance of Spread and monitor the general state of projections of tables in the database.

    SQL extension


    Added support for ROLLUP in aggregate queries, as well as functions for working with ROLLUP: GROUPING, GROUPING_ID and GROUP_ID.

    Materialization WITH


    Despite the fact that Vertica has temporary tables with which you can optimize query execution by storing intermediate results in them, it is not always possible to use them. For example, not every BI tool is able to maintain the nuances of Vertica, and during operation, break complex queries into several ones while storing intermediate results in a temporary table. From what I know, Microstrategy and Tableau can do this, but it is possible that Oracle BI and other BI tools can. To help optimize the performance of complex queries with subqueries, Vertica introduced support for automatically materializing the queries described in the WITH section for SELECT. Turning materialization on and off is set by the administrator in the database options.

    The following example in normal operation will perform an aggregate query on one table twice:
    WITH 
      revenue AS (
          SELECT vendor_key, SUM(total_order_cost) AS total_revenue
          FROM store.store_orders_fact
          GROUP BY vendor_key ORDER BY 1)
    -- End defining WITH clause statement
    -- Begin main primary query
    SELECT vendor_name,
           vendor_address,
           vendor_city,
           total_revenue
    FROM vendor_dimension v, revenue r
    WHERE v.vendor_key = r.vendor_key AND total_revenue = (
    SELECT MAX(total_revenue)
    FROM revenue)
    ORDER BY vendor_name;
    

    If you enable the WITH materialization option, then the Vertica server will automatically create a session local temporary table, save the result of the revenue query from the WITH section in it, and execute the main query using this temporary table instead of a subquery.

    Storing server data on HDFS in Apache Hadoop


    Prior to the current version, server data could only be stored on the local drives of its servers. Quite often there is a situation when data is stored in the repository that must be stored, but which is rarely used by users. Not the best solution to occupy such expensive data on fast local server disks.

    For this purpose, version 7.1 introduced the ability to store rarely used data in Apache Hadoop HDFS. As with other disks, it has become possible to register the HDFS directory as STORAGE and use it to store ROS containers as a full disk array. There are several options for moving rarely used data to HDFS:
    • Create a similar table with the location on the connected HDFS source and transfer partition data to it using the MOVE_PARTITIONS_TO_TABLE function
    • Set the data storage policy for the table using the SET_OBJECT_STORAGE_POLICY function, for which you want to automatically transfer records to the HDFS source if the partition keys of the records are less than the specified value


    Optimization of the HDFS connector


    Downloading data from HDFS to Vertica previously had a performance issue when retrieving large file sizes. The Vertica server sent a request to read the file, and if it was distributed among many HDFS servers, you had to wait until it was assembled into a single file, which was then taken. Now Vertica can determine that the file is stored in a distributed form and will independently connect to the servers where it is stored by reading and downloading all pieces of the file in parallel. Additionally, the connector can now control the speed of receiving data from the HDFS node and, if the speed drops below a predetermined threshold, they will automatically switch to another node, trying to get the necessary data from it at a higher speed.

    Full Text Indexes


    For the first time, indexes appeared in Vertica. Initially, the Vertica architecture was designed so that projections could more efficiently work with big data than indexes would. And it succeeded. However, progress does not stop, the appearance in Vertica of long lines (LONG VARCHAR) for storing arrays of texts, has led to the fact that full-text search in such fields has become popular. Projection in this case is a less effective means of working with texts than full-text indexes. In addition to the text field being indexed, the index requires indicating unique fields by which the key of the record containing the words specified in the search will be collected. For example, there is a table t_log in which there is a unique PK ID field, a date-time field for writing to the DATE log, and a TEXT text field.
    CREATE TEXT INDEX text_index ON t_log (id, text);
    


    Search for ID of log entries in which the word "WARNING" is case insensitive:
    SELECT doc_id, word 
    FROM text_index 
    WHERE word = TxtIndex.Stemmer(LOWER('WARNING'));
    


    The output of all log entries in which the word "WARNING" is case insensitive:
    SELECT * 
    FROM t_log 
    WHERE id IN (
      SELECT doc_id 
      FROM text_index 
      WHERE word = TxtIndex.Stemmer(LOWER('WARNING'))
    );
    

    A full-text search can take into account when searching for similar meanings of words to the search term. For example, for the above examples, the word “WARNINGS” may be returned by index.

    Python 3 support


    Added Python 3.3.4 support for pyodbc version 3.0.7

    Extensions


    Separately released extension packages for Vertica:
    • HP Vertica Place is a geodata package. It contains many functions that allow you to work with coordinates, polygons, calculate distances and sizes of objects, calculate overlap areas, etc.
    • HP Vertica Pulse is a complete semantic text analysis package. Unfortunately, only English is supported, so for our country this package is most likely not useful.


    Резюме


    The new version received a fairly impressive package of changes. I am glad that the developers do not focus on marketing chips and it is felt that the development of the product is managed by the customers themselves, whose requirements form the direction of development. As an EasyData architect for Vertica projects, I can say from personal experience that the new version covers almost all the wishes of our projects and our customers. You can also feel the powerful movement towards further integration with Hadoop, the continued development of the principles of zero administration on super large server clusters and the continuous optimization of storage and access to super large data. You can clearly feel the breath of Facebook, correcting the development of Vertica on its cluster and volumes, I don’t think that there are so many companies in the world, who would care about managing thousands of servers and petabytes of data. In any case, the innovations are very useful in our current and future projects, I hope Vertica will not slow down and continue its powerful continuous development, taking into account the tasks of customers, and not for the sake of marketing.

    PS Currently version 7.1 is available only to Enterprise version clients from my.vertica.com. The free Community version will be released a bit later when Dragline is officially unveiled on August 11 in Boston at the annual HP conference. In fact, this article presents this version for Russia before it happens in the States. I am sure that we deserved the first presentation in Russian, showing the best dynamics of the development of projects on Vertica in our country compared to other countries of the world.

    The request to inform me the found errors in the text of the article and inaccuracies in Habr's PM.
    Regards, Alexey.

    Also popular now: