Partitioning for Impala Tables

By default, all the data files for a table are located in a single directory. Partitioning is a technique for physically dividing the data during loading, based on values from one or more columns, to speed up queries that test those columns. For example, with a school_records table partitioned on a year column, there is a separate data directory for each different year value, and all the data for that year is stored in a data file in that directory. A query that includes a WHERE condition such as YEAR=1966, YEAR IN (1989,1999), or YEAR BETWEEN 1984 AND 1989 can examine only the data files from the appropriate directory or directories, greatly reducing the amount of data to read and test.

See Attaching an External Partitioned Table to an HDFS Directory Structure for an example that illustrates the syntax for creating partitioned tables, the underlying directory structure in HDFS, and how to attach a partitioned Impala external table to data files stored elsewhere in HDFS.

Parquet is a popular format for partitioned Impala tables because it is well suited to handle huge data volumes. See Query Performance for Impala Parquet Tables for performance considerations for partitioned Parquet tables.

See NULL for details about how NULL values are represented in partitioned tables.

See Using Impala with Amazon S3 Object Store for details about setting up tables where some or all partitions reside on the Amazon Simple Storage Service (S3).

When to Use Partitioned Tables

Partitioning is typically appropriate for:

  • Tables that are very large, where reading the entire data set takes an impractical amount of time.
  • Tables that are always or almost always queried with conditions on the partitioning columns. In our example of a table partitioned by year, SELECT COUNT(*) FROM school_records WHERE year = 1985 is efficient, only examining a small fraction of the data; but SELECT COUNT(*) FROM school_records has to process a separate data file for each year, resulting in more overall work than in an unpartitioned table. You would probably not partition this way if you frequently queried the table based on last name, student ID, and so on without testing the year.
  • Columns that have reasonable cardinality (number of different values). If a column only has a small number of values, for example Male or Female, you do not gain much efficiency by eliminating only about 50% of the data to read for each query. If a column has only a few rows matching each value, the number of directories to process can become a limiting factor, and the data file in each directory could be too small to take advantage of the Hadoop mechanism for transmitting data in multi-megabyte blocks. For example, you might partition census data by year, store sales data by year and month, and web traffic data by year, month, and day. (Some users with high volumes of incoming data might even partition down to the individual hour and minute.)
  • Data that already passes through an extract, transform, and load (ETL) pipeline. The values of the partitioning columns are stripped from the original data files and represented by directory names, so loading data into a partitioned table involves some sort of transformation or preprocessing.

SQL Statements for Partitioned Tables

In terms of Impala SQL syntax, partitioning affects these statements:

  • [CREATE TABLE]($909d3d3a24c1f854.md#create_table): you specify a PARTITIONED BY clause when creating the table to identify names and data types of the partitioning columns. These columns are not included in the main list of columns for the table.
  • In Impala 2.5 and higher, you can also use the PARTITIONED BY clause in a CREATE TABLE AS SELECT statement. This syntax lets you use a single statement to create a partitioned table, copy data into it, and create new partitions based on the values in the inserted data.

  • [ALTER TABLE]($990fa0038acb5944.md#alter_table): you can add or drop partitions, to work with different portions of a huge data set. You can designate the HDFS directory that holds the data files for a specific partition. With data partitioned by date values, you might “age out” data that is no longer relevant.

    Note: If you are creating a partition for the first time and specifying its location, for maximum efficiency, use a single ALTER TABLE statement including both the ADD PARTITION and LOCATION clauses, rather than separate statements with ADD PARTITION and SET LOCATION clauses.

  • [INSERT]($f1c3126d187707ee.md#insert): When you insert data into a partitioned table, you identify the partitioning columns. One or more values from each inserted row are not stored in data files, but instead determine the directory where that row value is stored. You can also specify which partition to load a set of data into, with INSERT OVERWRITE statements; you can replace the contents of a specific partition but you cannot append data to a specific partition.

    By default, if an INSERT statement creates any new subdirectories underneath a partitioned table, those subdirectories are assigned default HDFS permissions for the impala user. To make each subdirectory have the same permissions as its parent directory in HDFS, specify the ‑‑insert_inherit_permissions startup option for the impalad daemon.

  • Although the syntax of the [SELECT]($304128e61b9f6805.md#select) statement is the same whether or not the table is partitioned, the way queries interact with partitioned tables can have a dramatic impact on performance and scalability. The mechanism that lets queries skip certain partitions during a query is known as partition pruning; see Partition Pruning for Queries for details.

  • In Impala 1.4 and later, there is a SHOW PARTITIONS statement that displays information about each partition in a table. See SHOW Statement for details.

Static and Dynamic Partitioning Clauses

Specifying all the partition columns in a SQL statement is called static partitioning, because the statement affects a single predictable partition. For example, you use static partitioning with an ALTER TABLE statement that affects only one partition, or with an INSERT statement that inserts all values into the same partition:

  1. insert into t1 partition(x=10, y='a') select c1 from some_other_table;

When you specify some partition key columns in an INSERT statement, but leave out the values, Impala determines which partition to insert. This technique is called dynamic partitioning:

  1. insert into t1 partition(x, y='b') select c1, c2 from some_other_table;
  2. -- Create new partition if necessary based on variable year, month, and day; insert a single value.
  3. insert into weather partition (year, month, day) select 'cloudy',2014,4,21;
  4. -- Create new partition if necessary for specified year and month but variable day; insert a single value.
  5. insert into weather partition (year=2014, month=04, day) select 'sunny',22;

The more key columns you specify in the PARTITION clause, the fewer columns you need in the SELECT list. The trailing columns in the SELECT list are substituted in order for the partition key columns with no specified value.

Refreshing a Single Partition

The REFRESH statement is typically used with partitioned tables when new data files are loaded into a partition by some non-Impala mechanism, such as a Hive or Spark job. The REFRESH statement makes Impala aware of the new data files so that they can be used in Impala queries. Because partitioned tables typically contain a high volume of data, the REFRESH operation for a full partitioned table can take significant time.

In Impala 2.7 and higher, you can include a PARTITION (partition_spec) clause in the REFRESH statement so that only a single partition is refreshed. For example, REFRESH big_table PARTITION (year=2017, month=9, day=30). The partition spec must include all the partition key columns. See REFRESH Statement for more details and examples of REFRESH syntax and usage.

Permissions for Partition Subdirectories

By default, if an INSERT statement creates any new subdirectories underneath a partitioned table, those subdirectories are assigned default HDFS permissions for the impala user. To make each subdirectory have the same permissions as its parent directory in HDFS, specify the ‑‑insert_inherit_permissions startup option for the impalad daemon.

Partition Pruning for Queries

Partition pruning refers to the mechanism where a query can skip reading the data files corresponding to one or more partitions. If you can arrange for queries to prune large numbers of unnecessary partitions from the query execution plan, the queries use fewer resources and are thus proportionally faster and more scalable.

For example, if a table is partitioned by columns YEAR, MONTH, and DAY, then WHERE clauses such as WHERE year = 2013, WHERE year < 2010, or WHERE year BETWEEN 1995 AND 1998 allow Impala to skip the data files in all partitions outside the specified range. Likewise, WHERE year = 2013 AND month BETWEEN 1 AND 3 could prune even more partitions, reading the data files for only a portion of one year.

Checking if Partition Pruning Happens for a Query

To check the effectiveness of partition pruning for a query, check the EXPLAIN output for the query before running it. For example, this example shows a table with 3 partitions, where the query only reads 1 of them. The notation #partitions=1/3 in the EXPLAIN plan confirms that Impala can do the appropriate partition pruning.

  1. [localhost:21000] > insert into census partition (year=2010) values ('Smith'),('Jones');
  2. [localhost:21000] > insert into census partition (year=2011) values ('Smith'),('Jones'),('Doe');
  3. [localhost:21000] > insert into census partition (year=2012) values ('Smith'),('Doe');
  4. [localhost:21000] > select name from census where year=2010;
  5. +-------+
  6. | name  |
  7. +-------+
  8. | Smith |
  9. | Jones |
  10. +-------+
  11. [localhost:21000] > explain select name from census where year=2010;
  12. +------------------------------------------------------------------+
  13. | Explain String                                                   |
  14. +------------------------------------------------------------------+
  15. | PLAN FRAGMENT 0                                                  |
  16. |   PARTITION: UNPARTITIONED                                       |
  17. |                                                                  |
  18. |   1:EXCHANGE                                                     |
  19. |                                                                  |
  20. | PLAN FRAGMENT 1                                                  |
  21. |   PARTITION: RANDOM                                              |
  22. |                                                                  |
  23. |   STREAM DATA SINK                                               |
  24. |     EXCHANGE ID: 1                                               |
  25. |     UNPARTITIONED                                                |
  26. |                                                                  |
  27. |   0:SCAN HDFS                                                    |
  28. |      table=predicate_propagation.census #partitions=1/3 size=12B |
  29. +------------------------------------------------------------------+

For a report of the volume of data that was actually read and processed at each stage of the query, check the output of the SUMMARY command immediately after running the query. For a more detailed analysis, look at the output of the PROFILE command; it includes this same summary report near the start of the profile output.

What SQL Constructs Work with Partition Pruning

Impala can even do partition pruning in cases where the partition key column is not directly compared to a constant, by applying the transitive property to other parts of the WHERE clause. This technique is known as predicate propagation, and is available in Impala 1.2.2 and later. In this example, the census table includes another column indicating when the data was collected, which happens in 10-year intervals. Even though the query does not compare the partition key column (YEAR) to a constant value, Impala can deduce that only the partition YEAR=2010 is required, and again only reads 1 out of 3 partitions.

  1. [localhost:21000] > drop table census;
  2. [localhost:21000] > create table census (name string, census_year int) partitioned by (year int);
  3. [localhost:21000] > insert into census partition (year=2010) values ('Smith',2010),('Jones',2010);
  4. [localhost:21000] > insert into census partition (year=2011) values ('Smith',2020),('Jones',2020),('Doe',2020);
  5. [localhost:21000] > insert into census partition (year=2012) values ('Smith',2020),('Doe',2020);
  6. [localhost:21000] > select name from census where year = census_year and census_year=2010;
  7. +-------+
  8. | name  |
  9. +-------+
  10. | Smith |
  11. | Jones |
  12. +-------+
  13. [localhost:21000] > explain select name from census where year = census_year and census_year=2010;
  14. +------------------------------------------------------------------+
  15. | Explain String                                                   |
  16. +------------------------------------------------------------------+
  17. | PLAN FRAGMENT 0                                                  |
  18. |   PARTITION: UNPARTITIONED                                       |
  19. |                                                                  |
  20. |   1:EXCHANGE                                                     |
  21. |                                                                  |
  22. | PLAN FRAGMENT 1                                                  |
  23. |   PARTITION: RANDOM                                              |
  24. |                                                                  |
  25. |   STREAM DATA SINK                                               |
  26. |     EXCHANGE ID: 1                                               |
  27. |     UNPARTITIONED                                                |
  28. |                                                                  |
  29. |   0:SCAN HDFS                                                    |
  30. |      table=predicate_propagation.census #partitions=1/3 size=22B |
  31. |      predicates: census_year = 2010, year = census_year          |
  32. +------------------------------------------------------------------+

If a view applies to a partitioned table, any partition pruning considers the clauses on both the original query and any additional WHERE predicates in the query that refers to the view. Prior to Impala 1.4, only the WHERE clauses on the original query from the CREATE VIEW statement were used for partition pruning.

In queries involving both analytic functions and partitioned tables, partition pruning only occurs for columns named in the PARTITION BY clause of the analytic function call. For example, if an analytic function query has a clause such as WHERE year=2016, the way to make the query prune all other YEAR partitions is to include PARTITION BY year in the analytic function call; for example, OVER (PARTITION BY year,other_columns other_analytic_clauses).

Dynamic Partition Pruning

The original mechanism uses to prune partitions is static partition pruning, in which the conditions in the WHERE clause are analyzed to determine in advance which partitions can be safely skipped. In Impala 2.5 and higher, Impala can perform dynamic partition pruning, where information about the partitions is collected during the query, and Impala prunes unnecessary partitions in ways that were impractical to predict in advance.

For example, if partition key columns are compared to literal values in a WHERE clause, Impala can perform static partition pruning during the planning phase to only read the relevant partitions:

  2. -- The query only needs to read 3 partitions whose key values are known ahead of time.
  3. -- That's static partition pruning.
  4. SELECT COUNT(*) FROM sales_table WHERE year IN (2005, 2010, 2015);

Dynamic partition pruning involves using information only available at run time, such as the result of a subquery. The following example shows a simple dynamic partition pruning.

  2. CREATE TABLE yy (s STRING) PARTITIONED BY (year INT);
  3. INSERT INTO yy PARTITION (year) VALUES ('1999', 1999), ('2000', 2000),
  4.   ('2001', 2001), ('2010', 2010), ('2018', 2018);
  5. COMPUTE STATS yy;
  7. CREATE TABLE yy2 (s STRING, year INT);
  8. INSERT INTO yy2 VALUES ('1999', 1999), ('2000', 2000), ('2001', 2001);
  9. COMPUTE STATS yy2;
  11. -- The following query reads an unknown number of partitions, whose key values
  12. -- are only known at run time. The runtime filters line shows the
  13. -- information used in query fragment 02 to decide which partitions to skip.
  15. EXPLAIN SELECT s FROM yy WHERE year IN (SELECT year FROM yy2);
  16. +--------------------------------------------------------------------------+
  17. | PLAN-ROOT SINK                                                           |
  18. | |                                                                        |
  19. | 04:EXCHANGE [UNPARTITIONED]                                              |
  20. | |                                                                        |
  21. | 02:HASH JOIN [LEFT SEMI JOIN, BROADCAST]                                 |
  22. | |  hash predicates: year = year                                          |
  23. | |  runtime filters: RF000 <- year                                   |
  24. | |                                                                        |
  25. | |--03:EXCHANGE [BROADCAST]                                               |
  26. | |  |                                                                     |
  27. | |  01:SCAN HDFS [default.yy2]                                            |
  28. | |     partitions=1/1 files=1 size=620B                                   |
  29. | |                                                                        |
  30. | 00:SCAN HDFS [default.yy]                                                |
  31. |    partitions=5/5 files=5 size=1.71KB                               |
  32. |    runtime filters: RF000 -> year                                        |
  33. +--------------------------------------------------------------------------+
  35. SELECT s FROM yy WHERE year IN (SELECT year FROM yy2); -- Returns 3 rows from yy
  36. PROFILE;

In the above example, Impala evaluates the subquery, sends the subquery results to all Impala nodes participating in the query, and then each impalad daemon uses the dynamic partition pruning optimization to read only the partitions with the relevant key values.

The output query plan from the EXPLAIN statement shows that runtime filters are enabled. The plan also shows that it expects to read all 5 partitions of the yy table, indicating that static partition pruning will not happen.

The Filter summary in the PROFILE output shows that the scan node filtered out based on a runtime filter of dynamic partition pruning.

  1. Filter 0 (1.00 MB):
  2.  - Files processed: 3
  3.  - Files rejected: 1 (1)
  4.  - Files total: 3 (3)

Dynamic partition pruning is especially effective for queries involving joins of several large partitioned tables. Evaluating the ON clauses of the join predicates might normally require reading data from all partitions of certain tables. If the WHERE clauses of the query refer to the partition key columns, Impala can now often skip reading many of the partitions while evaluating the ON clauses. The dynamic partition pruning optimization reduces the amount of I/O and the amount of intermediate data stored and transmitted across the network during the query.

When the spill-to-disk feature is activated for a join node within a query, Impala does not produce any runtime filters for that join operation on that host. Other join nodes within the query are not affected.

Dynamic partition pruning is part of the runtime filtering feature, which applies to other kinds of queries in addition to queries against partitioned tables. See Runtime Filtering for Impala Queries (Impala 2.5 or higher only) for full details about this feature.

Partition Key Columns

The columns you choose as the partition keys should be ones that are frequently used to filter query results in important, large-scale queries. Popular examples are some combination of year, month, and day when the data has associated time values, and geographic region when the data is associated with some place.

  • For time-based data, split out the separate parts into their own columns, because Impala cannot partition based on a TIMESTAMP column.

  • The data type of the partition columns does not have a significant effect on the storage required, because the values from those columns are not stored in the data files, rather they are represented as strings inside HDFS directory names.

  • In Impala 2.5 and higher, you can enable the OPTIMIZE_PARTITION_KEY_SCANS query option to speed up queries that only refer to partition key columns, such as SELECT MAX(year). This setting is not enabled by default because the query behavior is slightly different if the table contains partition directories without actual data inside. See OPTIMIZE_PARTITION_KEY_SCANS Query Option (Impala 2.5 or higher only) for details.

  • Partitioned tables can contain complex type columns. All the partition key columns must be scalar types.

  • Remember that when Impala queries data stored in HDFS, it is most efficient to use multi-megabyte files to take advantage of the HDFS block size. For Parquet tables, the block size (and ideal size of the data files) is 256 MB in Impala 2.0 and later. Therefore, avoid specifying too many partition key columns, which could result in individual partitions containing only small amounts of data. For example, if you receive 1 GB of data per day, you might partition by year, month, and day; while if you receive 5 GB of data per minute, you might partition by year, month, day, hour, and minute. If you have data with a geographic component, you might partition based on postal code if you have many megabytes of data for each postal code, but if not, you might partition by some larger region such as city, state, or country. state

If you frequently run aggregate functions such as MIN(), MAX(), and COUNT(DISTINCT) on partition key columns, consider enabling the OPTIMIZE_PARTITION_KEY_SCANS query option, which optimizes such queries. This feature is available in Impala 2.5 and higher. See OPTIMIZE_PARTITION_KEY_SCANS Query Option (Impala 2.5 or higher only) for the kinds of queries that this option applies to, and slight differences in how partitions are evaluated when this query option is enabled.

Setting Different File Formats for Partitions

Partitioned tables have the flexibility to use different file formats for different partitions. (For background information about the different file formats Impala supports, see How Impala Works with Hadoop File Formats.) For example, if you originally received data in text format, then received new data in RCFile format, and eventually began receiving data in Parquet format, all that data could reside in the same table for queries. You just need to ensure that the table is structured so that the data files that use different file formats reside in separate partitions.

For example, here is how you might switch from text to Parquet data as you receive data for different years:

  1. [localhost:21000] > create table census (name string) partitioned by (year smallint);
  2. [localhost:21000] > alter table census add partition (year=2012); -- Text format;
  4. [localhost:21000] > alter table census add partition (year=2013); -- Text format switches to Parquet before data loaded;
  5. [localhost:21000] > alter table census partition (year=2013) set fileformat parquet;
  7. [localhost:21000] > insert into census partition (year=2012) values ('Smith'),('Jones'),('Lee'),('Singh');
  8. [localhost:21000] > insert into census partition (year=2013) values ('Flores'),('Bogomolov'),('Cooper'),('Appiah');

At this point, the HDFS directory for year=2012 contains a text-format data file, while the HDFS directory for year=2013 contains a Parquet data file. As always, when loading non-trivial data, you would use INSERT ... SELECT or LOAD DATA to import data in large batches, rather than INSERT ... VALUES which produces small files that are inefficient for real-world queries.

For other file types that Impala cannot create natively, you can switch into Hive and issue the ALTER TABLE ... SET FILEFORMAT statements and INSERT or LOAD DATA statements there. After switching back to Impala, issue a REFRESH table_name statement so that Impala recognizes any partitions or new data added through Hive.

Managing Partitions

You can add, drop, set the expected file format, or set the HDFS location of the data files for individual partitions within an Impala table. See ALTER TABLE Statement for syntax details, and Setting Different File Formats for Partitions for tips on managing tables containing partitions with different file formats.

Note: If you are creating a partition for the first time and specifying its location, for maximum efficiency, use a single ALTER TABLE statement including both the ADD PARTITION and LOCATION clauses, rather than separate statements with ADD PARTITION and SET LOCATION clauses.

What happens to the data files when a partition is dropped depends on whether the partitioned table is designated as internal or external. For an internal (managed) table, the data files are deleted. For example, if data in the partitioned table is a copy of raw data files stored elsewhere, you might save disk space by dropping older partitions that are no longer required for reporting, knowing that the original data is still available if needed later. For an external table, the data files are left alone. For example, dropping a partition without deleting the associated files lets Impala consider a smaller set of partitions, improving query efficiency and reducing overhead for DDL operations on the table; if the data is needed again later, you can add the partition again. See Overview of Impala Tables for details and examples.

Using Partitioning with Kudu Tables

Kudu tables use a more fine-grained partitioning scheme than tables containing HDFS data files. You specify a PARTITION BY clause with the CREATE TABLE statement to identify how to divide the values from the partition key columns.

See Partitioning for Kudu Tables for details and examples of the partitioning techniques for Kudu tables.

Keeping Statistics Up to Date for Partitioned Tables

Because the COMPUTE STATS statement can be resource-intensive to run on a partitioned table as new partitions are added, Impala includes a variation of this statement that allows computing statistics on a per-partition basis such that stats can be incrementally updated when new partitions are added.

Important:

For a particular table, use either COMPUTE STATS or COMPUTE INCREMENTAL STATS, but never combine the two or alternate between them. If you switch from COMPUTE STATS to COMPUTE INCREMENTAL STATS during the lifetime of a table, or vice versa, drop all statistics by running DROP STATS before making the switch.

When you run COMPUTE INCREMENTAL STATS on a table for the first time, the statistics are computed again from scratch regardless of whether the table already has statistics. Therefore, expect a one-time resource-intensive operation for scanning the entire table when running COMPUTE INCREMENTAL STATS for the first time on a given table.

In Impala 3.0 and lower, approximately 400 bytes of metadata per column per partition are needed for caching. Tables with a big number of partitions and many columns can add up to a significant memory overhead as the metadata must be cached on the catalogd host and on every impalad host that is eligible to be a coordinator. If this metadata for all tables exceeds 2 GB, you might experience service downtime. In Impala 3.1 and higher, the issue was alleviated with an improved handling of incremental stats.

The COMPUTE INCREMENTAL STATS variation computes statistics only for partitions that were added or changed since the last COMPUTE INCREMENTAL STATS statement, rather than the entire table. It is typically used for tables where a full COMPUTE STATS operation takes too long to be practical each time a partition is added or dropped. See impala_perf_stats.html#perf_stats_incremental for full usage details.

  1. -- Initially the table has no incremental stats, as indicated
  2. -- 'false' under Incremental stats.
  3. show table stats item_partitioned;
  4. +-------------+-------+--------+----------+--------------+---------+------------------
  5. | i_category  | #Rows | #Files | Size     | Bytes Cached | Format  | Incremental stats
  6. +-------------+-------+--------+----------+--------------+---------+------------------
  7. | Books       | -1    | 1      | 223.74KB | NOT CACHED   | PARQUET | false
  8. | Children    | -1    | 1      | 230.05KB | NOT CACHED   | PARQUET | false
  9. | Electronics | -1    | 1      | 232.67KB | NOT CACHED   | PARQUET | false
  10. | Home        | -1    | 1      | 232.56KB | NOT CACHED   | PARQUET | false
  11. | Jewelry     | -1    | 1      | 223.72KB | NOT CACHED   | PARQUET | false
  12. | Men         | -1    | 1      | 231.25KB | NOT CACHED   | PARQUET | false
  13. | Music       | -1    | 1      | 237.90KB | NOT CACHED   | PARQUET | false
  14. | Shoes       | -1    | 1      | 234.90KB | NOT CACHED   | PARQUET | false
  15. | Sports      | -1    | 1      | 227.97KB | NOT CACHED   | PARQUET | false
  16. | Women       | -1    | 1      | 226.27KB | NOT CACHED   | PARQUET | false
  17. | Total       | -1    | 10     | 2.25MB   | 0B           |         |
  18. +-------------+-------+--------+----------+--------------+---------+------------------
  20. -- After the first COMPUTE INCREMENTAL STATS,
  21. -- all partitions have stats. The first
  22. -- COMPUTE INCREMENTAL STATS scans the whole
  23. -- table, discarding any previous stats from
  24. -- a traditional COMPUTE STATS statement.
  25. compute incremental stats item_partitioned;
  26. +-------------------------------------------+
  27. | summary                                   |
  28. +-------------------------------------------+
  29. | Updated 10 partition(s) and 21 column(s). |
  30. +-------------------------------------------+
  31. show table stats item_partitioned;
  32. +-------------+-------+--------+----------+--------------+---------+------------------
  33. | i_category  | #Rows | #Files | Size     | Bytes Cached | Format  | Incremental stats
  34. +-------------+-------+--------+----------+--------------+---------+------------------
  35. | Books       | 1733  | 1      | 223.74KB | NOT CACHED   | PARQUET | true
  36. | Children    | 1786  | 1      | 230.05KB | NOT CACHED   | PARQUET | true
  37. | Electronics | 1812  | 1      | 232.67KB | NOT CACHED   | PARQUET | true
  38. | Home        | 1807  | 1      | 232.56KB | NOT CACHED   | PARQUET | true
  39. | Jewelry     | 1740  | 1      | 223.72KB | NOT CACHED   | PARQUET | true
  40. | Men         | 1811  | 1      | 231.25KB | NOT CACHED   | PARQUET | true
  41. | Music       | 1860  | 1      | 237.90KB | NOT CACHED   | PARQUET | true
  42. | Shoes       | 1835  | 1      | 234.90KB | NOT CACHED   | PARQUET | true
  43. | Sports      | 1783  | 1      | 227.97KB | NOT CACHED   | PARQUET | true
  44. | Women       | 1790  | 1      | 226.27KB | NOT CACHED   | PARQUET | true
  45. | Total       | 17957 | 10     | 2.25MB   | 0B           |         |
  46. +-------------+-------+--------+----------+--------------+---------+------------------
  48. -- Add a new partition...
  49. alter table item_partitioned add partition (i_category='Camping');
  50. -- Add or replace files in HDFS outside of Impala,
  51. -- rendering the stats for a partition obsolete.
  52. !import_data_into_sports_partition.sh
  53. refresh item_partitioned;
  54. drop incremental stats item_partitioned partition (i_category='Sports');
  55. -- Now some partitions have incremental stats
  56. -- and some do not.
  57. show table stats item_partitioned;
  58. +-------------+-------+--------+----------+--------------+---------+------------------
  59. | i_category  | #Rows | #Files | Size     | Bytes Cached | Format  | Incremental stats
  60. +-------------+-------+--------+----------+--------------+---------+------------------
  61. | Books       | 1733  | 1      | 223.74KB | NOT CACHED   | PARQUET | true
  62. | Camping     | -1    | 1      | 408.02KB | NOT CACHED   | PARQUET | false
  63. | Children    | 1786  | 1      | 230.05KB | NOT CACHED   | PARQUET | true
  64. | Electronics | 1812  | 1      | 232.67KB | NOT CACHED   | PARQUET | true
  65. | Home        | 1807  | 1      | 232.56KB | NOT CACHED   | PARQUET | true
  66. | Jewelry     | 1740  | 1      | 223.72KB | NOT CACHED   | PARQUET | true
  67. | Men         | 1811  | 1      | 231.25KB | NOT CACHED   | PARQUET | true
  68. | Music       | 1860  | 1      | 237.90KB | NOT CACHED   | PARQUET | true
  69. | Shoes       | 1835  | 1      | 234.90KB | NOT CACHED   | PARQUET | true
  70. | Sports      | -1    | 1      | 227.97KB | NOT CACHED   | PARQUET | false
  71. | Women       | 1790  | 1      | 226.27KB | NOT CACHED   | PARQUET | true
  72. | Total       | 17957 | 11     | 2.65MB   | 0B           |         |
  73. +-------------+-------+--------+----------+--------------+---------+------------------
  75. -- After another COMPUTE INCREMENTAL STATS,
  76. -- all partitions have incremental stats, and only the 2
  77. -- partitions without incremental stats were scanned.
  78. compute incremental stats item_partitioned;
  79. +------------------------------------------+
  80. | summary                                  |
  81. +------------------------------------------+
  82. | Updated 2 partition(s) and 21 column(s). |
  83. +------------------------------------------+
  84. show table stats item_partitioned;
  85. +-------------+-------+--------+----------+--------------+---------+------------------
  86. | i_category  | #Rows | #Files | Size     | Bytes Cached | Format  | Incremental stats
  87. +-------------+-------+--------+----------+--------------+---------+------------------
  88. | Books       | 1733  | 1      | 223.74KB | NOT CACHED   | PARQUET | true
  89. | Camping     | 5328  | 1      | 408.02KB | NOT CACHED   | PARQUET | true
  90. | Children    | 1786  | 1      | 230.05KB | NOT CACHED   | PARQUET | true
  91. | Electronics | 1812  | 1      | 232.67KB | NOT CACHED   | PARQUET | true
  92. | Home        | 1807  | 1      | 232.56KB | NOT CACHED   | PARQUET | true
  93. | Jewelry     | 1740  | 1      | 223.72KB | NOT CACHED   | PARQUET | true
  94. | Men         | 1811  | 1      | 231.25KB | NOT CACHED   | PARQUET | true
  95. | Music       | 1860  | 1      | 237.90KB | NOT CACHED   | PARQUET | true
  96. | Shoes       | 1835  | 1      | 234.90KB | NOT CACHED   | PARQUET | true
  97. | Sports      | 1783  | 1      | 227.97KB | NOT CACHED   | PARQUET | true
  98. | Women       | 1790  | 1      | 226.27KB | NOT CACHED   | PARQUET | true
  99. | Total       | 17957 | 11     | 2.65MB   | 0B           |         |
  100. +-------------+-------+--------+----------+--------------+---------+------------------