> Ken, what is the extra detail you were hoping to see in the docs? > More examples of SQL idioms that do/don't result in runtime filters?
Yep, you got it: because my partitioning depends on multiple columns, typically often used together, I really need to use a join rather than subquery. And at this point, assuming that the dynamic runtime filters work well & consistently I'll probably shift our standard partitioning columns away from year, month, day to date_id - a foreign key into. Unless anyone can recommend a reason not to. On Fri, Apr 8, 2016 at 12:53 PM, John Russell <[email protected]> wrote: > Ken, what is the extra detail you were hoping to see in the docs? More > examples of SQL idioms that do/don't result in runtime filters? (Like in > your example, I could imagine being curious about INNER JOIN as opposed to > other kinds of joins, ON clause with several ANDed predicates, and BETWEEN > in the WHERE clause.) > > We're planning to add more such examples in upcoming doc refreshes. Just > want to make sure we cover the kinds of areas you're curious about. Once > 5.7 is installed somewhere to try out, it's straightforward to verify for > particular combinations of tables and queries using EXPLAIN and looking for > the extra lines representing filters. > > John > > > On Apr 8, 2016, at 11:39 AM, Mostafa Mokhtar <[email protected]> > wrote: > > Hi Ken, > > Yes the query provided should work, I validated that the query below works > as expected which is fairly similar to the one listed above. > > select > count(*) > from > store_sales_mp, > date_dim > where > ss_sold_date_sk = d_date_sk > and ss_store_sk = d_dow > and d_year = 1999 > and d_moy = 1 > > > store_sales_mp is partitioned on two columns : > | # col_name | data_type > | ss_sold_date_sk > | bigint > | ss_store_sk | bigint > > > > The query execution summary shows only a small number of rows from > store_sales_mp qualify the scan > > > +-----------------+--------+----------+----------+--------+------------+----------+---------------+--------------------------------------------+ > | Operator | #Hosts | Avg Time | Max Time | #Rows | Est. #Rows | > Peak Mem | Est. Peak Mem | Detail | > > +-----------------+--------+----------+----------+--------+------------+----------+---------------+--------------------------------------------+ > | 06:AGGREGATE | 1 | 160.66ms | 160.66ms | 1 | 1 | > 88.00 KB | -1 B | FINALIZE | > | 05:EXCHANGE | 1 | 137.98us | 137.98us | 19 | 1 | 0 > B | -1 B | UNPARTITIONED | > | 03:AGGREGATE | 19 | 151.01ms | 183.65ms | 19 | 1 | > 8.04 MB | 10.00 MB | | > | 02:HASH JOIN | 19 | 101.27ms | 185.51ms | 4.82M | 43.20B | > 2.02 MB | 3.89 KB | INNER JOIN, BROADCAST | > | |--04:EXCHANGE | 19 | 14.54us | 25.36us | 589 | 181 | 0 > B | 0 B | BROADCAST | > | | 01:SCAN HDFS | 1 | 20.39ms | 20.39ms | 31 | 181 | > 1.82 MB | 64.00 MB | tpcds_15000_decimal_parquet.date_dim | > | 00:SCAN HDFS | 19 | 37.54ms | 81.18ms | 45.00M | 43.20B | > 9.84 MB | 0 B | tpcds_15000_decimal_parquet.store_sales_mp | > > +-----------------+--------+----------+----------+--------+------------+----------+---------------+--------------------------------------------+ > > > > Finally from the query profile > > HDFS_SCAN_NODE (id=0):(Total: 37.539ms, non-child: 37.539ms, % > non-child: 100.00%) > - AverageHdfsReadThreadConcurrency: 0.00 > - AverageScannerThreadConcurrency: 0.93 > - BytesRead: 489.47 KB (501221) > - BytesReadDataNodeCache: 0 > - BytesReadLocal: 468.42 KB (479663) > - BytesReadRemoteUnexpected: 21.05 KB (21557) > - BytesReadShortCircuit: 468.42 KB (479663) > - DecompressionTime: 0.000ns > - MaxCompressedTextFileLength: 0 > - NumColumns: 0 (0) > - NumDisksAccessed: 4 (4) > - NumRowGroups: 0 (0) > - NumScannerThreadsStarted: 5 (5) > - PeakMemoryUsage: 9.35 MB (9799572) > - PerReadThreadRawHdfsThroughput: 901.98 MB/sec > - RemoteScanRanges: 0 (0) > - RowsRead: 2.37M (2368521) > - RowsReturned: 2.37M (2368521) > - RowsReturnedRate: 62.23 M/sec > - ScanRangesComplete: 3.21K (3207) > - ScannerThreadsInvoluntaryContextSwitches: 26 (26) > - ScannerThreadsTotalWallClockTime: 420.999ms > - MaterializeTupleTime(*): 471.000ns > - ScannerThreadsSysTime: 82.142ms > - ScannerThreadsUserTime: 65.777ms > - ScannerThreadsVoluntaryContextSwitches: 5.41K (5413) > - TotalRawHdfsReadTime(*): 1.940ms > - TotalReadThroughput: 136.84 KB/sec > Filter 0: > - Files processed: 3.18K (3184) > - Files rejected: 3.13K (3130) > - Files total: 3.18K (3184) > - RowGroups processed: 0 (0) > - RowGroups rejected: 0 (0) > - RowGroups total: 0 (0) > - Rows processed: 0 (0) > - Rows rejected: 0 (0) > - Rows total: 0 (0) > - Splits processed: 4 (4) > - Splits rejected: 0 (0) > - Splits total: 4 (4) > Filter 1: > - Files processed: 54 (54) > - Files rejected: 49 (49) > - Files total: 54 (54) > - RowGroups processed: 0 (0) > - RowGroups rejected: 0 (0) > - RowGroups total: 0 (0) > - Rows processed: 0 (0) > - Rows rejected: 0 (0) > - Rows total: 0 (0) > - Splits processed: 4 (4) > - Splits rejected: 0 (0) > - Splits total: 4 (4) > > > > Thanks > Mostafa > > > On Fri, Apr 8, 2016 at 10:04 AM, Ken Farmer <[email protected]> wrote: > >> First off - congrats on this new release. It looks like a lot of hard >> work went into it. >> >> I'm really excited about dynamic runtime filters, but have a question >> about whether it would apply to the following scenario, which seems oddly >> missing from the documentation. >> >> Assume 2 tables: >> 1. fact_ids table with 50 billion rows partitioned by year, month, day, >> sensor_id >> 2. dim_date dimension table with 500 rows, 1 row/day with date_id, and >> date_name, year, month, day columns >> >> Query that works fine today: >> SELECT f.col1, >> SUM(somecount) >> FROM fact_ids f >> WHERE f.year=2016 AND f.month=4 AND f.day=2 >> GROUP BY 1 ORDER BY 1 >> >> >> Query that I hope works with 5.7: >> SELECT f.col1, >> SUM(somecount) >> FROM fact_ids f >> INNER JOIN dim_date d >> ON f.year = d.year >> AND f.month = d.month >> AND f.day = d.day >> WHERE d.date_name BETWEEN '2016-03-02' AND '2016-04-01' >> GROUP BY 1 ORDER BY 1 >> >> Can anyone confirm that 5.7 will perform dynamic runtime filtering on >> this second example above? >> >> Anything else I should be aware of for cases like this? Or limitations >> of dynamic runtime filtering? >> >> >> Thanks! >> >> >> >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Impala User" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> > > > -- > You received this message because you are subscribed to the Google Groups > "Impala User" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > > > -- > You received this message because you are subscribed to the Google Groups > "Impala User" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. >
