You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "pandalanax (via GitHub)" <gi...@apache.org> on 2023/07/05 06:08:32 UTC

[I] dir/*.parquet UNION ALL dir/*.json reading slow (drill)

pandalanax opened a new issue, #2814:
URL: https://github.com/apache/drill/issues/2814

   **Describe the bug**
   We have a 20GB parquet file which we save in HDFS and regenerate monthly at the moment. The file is stored in 128MB parts so ~160 .parquet files. This is called our `history_data`
   We UNION these files with .json files which have newer data. Example given:
   
   Parquet file ranges from 2020-01-31 up to 2023-06-29.
   Json files ranges from 2023-07-01 up to current date.
   
   
   When issuing a query like: 
   ```sql 
   SELECT   * FROM  dfs.co2meter.sensor_data_v3 order by `timestamp` limit 10
   ```
    we observed that the Major Fragment 03-xx-xx containing:
   - JSON_SUB_SCAN
   - UNION_ALL
   - PROJECT
   - PROJECT
   - PARQUET_ROW_GROUP_SCAN
   only acts with min(# parquet files, # json files) threads/processes.
   E.g.:
   160 parquet files & 9 Json files == 9/9 Minor Fragments Reporting (super slow)
   160 parquet files & 160 Json files == 160/160 Minor Fragments Reporting (fastest)
   160 parquet files & 320 Json files == 160/160 Minor Fragments Reporting (also ok fast)
   
   Is there a config for this? 
   
   
   
   **To Reproduce**
   
   
   **Expected behavior**
   A clear and concise description of what you expected to happen.
   
   **Error detail, log output or screenshots**
   Prefer character data over screenshots for error messages and log output.
   
   **Drill version**
   1.17.0
   
   **Additional context**
   4 Drillbits.
   
   
   160/160 Minor Fragments Reporting
   ![image](https://github.com/apache/drill/assets/38329801/0f3cb9fb-2c00-4c6c-80df-fc3d4a1777ae)
   
   9/9 Minor Fragments Reporting
   ![image](https://github.com/apache/drill/assets/38329801/00d784e7-05f1-4146-8d1f-ca0c5f6629ed)
   
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] dir/*.parquet UNION ALL dir/*.json reading slow (drill)

Posted by "pandalanax (via GitHub)" <gi...@apache.org>.
pandalanax commented on issue #2814:
URL: https://github.com/apache/drill/issues/2814#issuecomment-1630227543

   We created a workaround for the time being while we are upgrading the Drill version in our cluster.  
   Workaround:
   create `n` empty json files via 
   ```bash
   for n in {1..160}; do hdfs dfs -touch /path/to/json/files/tmp${n}.json; done
   ``` 
   with `n` then matching the number of parquet files. Will close for now and reopen if the issue persists with 1.21. Will also try out Drill metastore, thanks for the suggestion!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] dir/*.parquet UNION ALL dir/*.json reading slow (drill)

Posted by "cgivre (via GitHub)" <gi...@apache.org>.
cgivre commented on issue #2814:
URL: https://github.com/apache/drill/issues/2814#issuecomment-1621795160

   Hi @pandalanax   Can you please try with an updated version of Drill to see if this issue still exists?  Drill 1.17 is several years old at this point and there have been MANY improvements to it since then.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] dir/*.parquet UNION ALL dir/*.json reading slow (drill)

Posted by "pandalanax (via GitHub)" <gi...@apache.org>.
pandalanax closed issue #2814: dir/*.parquet UNION ALL dir/*.json reading slow
URL: https://github.com/apache/drill/issues/2814


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] dir/*.parquet UNION ALL dir/*.json reading slow (drill)

Posted by "pandalanax (via GitHub)" <gi...@apache.org>.
pandalanax commented on issue #2814:
URL: https://github.com/apache/drill/issues/2814#issuecomment-1621306114

   to summarize:
   If ``` planner.width.max_per_query > $(actual # of files for one part of union)```
   
   the `planner.width.max_per_query` shrinks to `$(actual # of files for one part of union)`.
   This could make sense now that i think about it as there is only an amount of parallel reading possible per file. 
   
   However: the files are replicated throughout the HDFS Cluster (also 4 nodes, replication is 4). In my understanding it should be possible to read one file via >= 1 thread. Am i wrong here?
   
   Another idea: 
   Is it maybe possible to split `JSON_SUB_SCAN` and `PARQUET_ROW_GROUP_SCAN` into own Major fragments when doing UNION ALL? (same with KAFKA_SUB_SCAN btw)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org