#general
@ayush.jha: @ayush.jha has joined the channel
@tiger: Is stream ingestion with Upsert compatible with the RealtimeToOfflineSegmentsTask? I plan to have a realtime table that uses the upsert functionality, and then have older segments moved to an offline table to make a hybrid table.
@npawar: No, they do not work together
@bobby.richard: So there's no way to move data from an upsert enabled RT table to an offline table?
@mayanks: @bobby.richard today you can do so, as in, Pinot won’t stop you. But upserts will be limited to real-time table only
@bobby.richard: Ah ok. I may have misunderstood the original question.
@mayanks: Let me clarify - Upsert functionality is limited to realtime-only tables. If you did have an offline table as well (in case of managed offline flow), then upsert functionality won't work across realtime and offline. But if users were OK with this limitation, as in limit upsert to the realtime part, then they can use it in that mode (there are cases doing so).
@nicholas.yu2: @nicholas.yu2 has joined the channel
@chris.jayakumar: @chris.jayakumar has joined the channel
#random
@ayush.jha: @ayush.jha has joined the channel
@nicholas.yu2: @nicholas.yu2 has joined the channel
@chris.jayakumar: @chris.jayakumar has joined the channel
#troubleshooting
@ayush.jha: @ayush.jha has joined the channel
@ayush.jha: Hi all the download link for 0.8.0 and 0.7.1 is not
@xiangfu0: I updated all the download links. It should reflect soon.
@bagi.priyank: thank you!
@mark.needham: Can you try 0.9.0 in the meantime?
@alinoorrahman: Hi, I’m trying to improve the performance of a select count(distinct col_a) query, it’s taking several minutes at the moment before failing (out of memory, box has 64gb ram). There are about 50 million unique values from about 700 millions rows. The DistinctCountHLL and DistinctCountThetaSketch estimates are fast enough but not accurate enough. What can I do improve the performance of the count(distinct col_a) query?
@richard892: what's the type of the values?
@alinoorrahman: string
@alinoorrahman: the query will often have a group by and where clause in it, these tend to be string values as well
@richard892: it's kind of a hard problem then, even if the strings are relatively short, say 20 bytes, 50M of them requires ~1GB RAM to deduplicate
@richard892: do you really need an exact result?
@alinoorrahman: Ideally, yes. The estimates are off by 10's of thousands, often by 100's of thousands, which is too much.
@alinoorrahman: Can I use any of the indexes in Pinot to help? The data doesn't change very often, a few times a month, so willing to accept longer ingest time for better query performance.
@richard892: can you share the table config for the field you are running distinct count over?
@richard892: I think we can probably improve distinct count to use indexes and dictionaries where available, but I don't think this is possible right now
@alinoorrahman: A specific example of the numbers I'm getting at the moment (with a group by on another column) Actual 15476256 distinctCountThetaSketch 15327774 DistinctCountHLL 14866424
@alinoorrahman: ``` { "tableName": "op_test", "tableType": "OFFLINE", "segmentsConfig": { "segmentPushType": "APPEND", "segmentAssignmentStrategy": "BalanceNumSegmentAssignmentStrategy", "replication": "1" }, "tenants": {}, "tableIndexConfig": { "enableDefaultStarTree": true, "loadMode": "MMAP", "invertedIndexColumns": [ "gender" ] }, "metadata": { "customConfigs": {} }```
@richard892: I think you can do better with theta sketch by setting the nominal entries in the query
@alinoorrahman: Could explain with an example please?
@richard892: by default nominal entries is 4096, but you can set it like so: ```DISTINCT_COUNT_THETA_SKETCH(col, 'nominalEntries=8192')``` and you can go even higher
@richard892: the error decreases with nominal entries or `k`
@richard892: so basically you should be able to get more accurate results by increasing it, but it will use more memory, though not as much as an exact distinct count
@richard892: can you give it a try for a few values (e.g. 8192, 16384) and see if you get better results?
@alinoorrahman: 'nominalEntries=16384', gives a count of 15426624, takes about 15 seconds.
@richard892: ok, the size of the sketch increases with that parameter so an increase in latency is to be expected
@alinoorrahman: actual: 15476256 'nominalEntries=65536', count = 15472132 'nominalEntries=131072', count = 15489003
@alinoorrahman: still takes about 15 seconds
@richard892: how long was it without specifying nominal entries?
@alinoorrahman: the same, about 15 seconds without nominal entries
@alinoorrahman: I wasn't expecting 'nominalEntries=131072', to be less accurate than 'nominalEntries=65536'.
@richard892: I think that's larger than the sketch authors really intended, there may be bugs assuming you won't go that large
@alinoorrahman: Ah
@richard892: what are the strings being counted? Could they be transformed to numbers on ingestion?
@alinoorrahman: I guess they could be. They are IDs...example: ```9IPX6Q4ZD9TT8QD K7IL68L885FWT07 HINHAWWUCYUADZB```
@richard892: you're getting pretty good relative error from theta sketch, ((15476256 - 15472132) / 15476256) * 100 = 0.03%
@richard892: at 16384 ((15476256 - 15426624) / 15476256) * 100 = 0.3%
@alinoorrahman: ideally the results need to be accurate to less than 5
@richard892: most sketches don't have absolute error guarantees though
@richard892: is the column with the IDs raw or does it have a dictionary?
@alinoorrahman: What do you mean?
@richard892: can you share the schema definition for the ID column?
@richard892: I think you should try sorting on the ID column and see where that gets you
@alinoorrahman: ``` ....{ "name": "heid", "dataType": "STRING" },...```
@richard892: so ```{ "tableName": "op_test", "tableType": "OFFLINE", "segmentsConfig": { "segmentPushType": "APPEND", "segmentAssignmentStrategy": "BalanceNumSegmentAssignmentStrategy", "replication": "1" }, "tenants": {}, "tableIndexConfig": { "enableDefaultStarTree": true, "loadMode": "MMAP", "invertedIndexColumns": [ "gender" ], "sortedColumn": ["heid"] }, "metadata": { "customConfigs": {} }```
@alinoorrahman: Once I've made the config change, I'll have to ingest the data again?
@richard892: yes, and I'm not 100% sure it's going to help, but I think it might because what the distinct count aggregation function does is build a compressed bitmap from the internal integer representation of `heid`
@richard892: however, if you ever want to filter on `heid` sorting on it will really help, it's generally good to sort on high cardinality attributes
@alinoorrahman: Thanks Richard, I'll try it out. Will take a few hours to ingest the data.
@richard892: if you can, take a profile of the query with `jcmd <server pid> JFR.start duration=60s filename=distinctcount.jfr` and send it to me and I'll take a look to be sure what I think is happening is happening, but do that before reingesting
@richard892: I also don't want you to spend hours doing that if I'm wrong, and having a profile would help
@alinoorrahman: That's fine, I'm doing a PoC on Pinot and the ingest can run while I'm doing other things.
@alinoorrahman: On a side note, when I set `segmentCreationJobParallelism: 14` from 1, some of the segments/data is missing when the ingest is completed. I couldn't find anything in github issues about it...nor any errors in the logs. Any ideas what could be going wrong?
@richard892: Can we create another thread for that? I'm not too hot on ingestion topics
@richard892: by the way do you have the query and the response metadata? Replace the aggregation function with count so it comes back quicker, maybe adding some indexes will help.
@g.kishore: If you really need distinctCount accurate and also speed.. you can partition data by these ids and use partitioneddiatinctcount
@g.kishore: Partitioned DistinctCount
@alinoorrahman: @richard892, could you explain what you meant by 'Replace the aggregation function with count so it comes back quicker, maybe adding some indexes will help.'
@alinoorrahman: @g.kishore, how do I setup Partitioned DistinctCount, could you point me to the docs for it? Thanks
@richard892: rather than putting a distinctcount in the query, put a count, looking at the response metadata would be helpful to figure out if you need some indexes
@richard892: the biggest problem right now will be that exact distinctcount is such an expensive thing to compute though
@alinoorrahman: The count comes back fairly quick from what I can recall. I get back to you with metadata once the data has been ingested.
@g.kishore: @jackie.jxt ^^
@jackie.jxt: What's the latency of `distinctCountHll`?
@jackie.jxt: You can increase the `log2m` for `distinctCountHll` to get better accuracy, e.g. `distinctCountHll(col_a, 15)`
@jackie.jxt: You can also try `distinctCountBitmap(col_a)` which counts the accurate unique hash values with java `String.hashCode()`
@alinoorrahman: ```redshift count: 15476256 time: 525.429 seconds select distinctCountHll(heid) timeUsedMs: 18107 count: 14866424 select distinctCountHll(heid, 15) timeUsedMs: 17140 count: 15375247 select distinctCountHll(heid, 30) OutOfMemoryError select distinctCountBitmap(heid) timeUsedMs: 43089 count: 15448390``` the table config for this table is slightly different than previous: ``` "tableIndexConfig": { "loadMode": "MMAP", "invertedIndexColumns": [ "gender", "heid" ] },```
@alinoorrahman: the response metadata your referring to above, is that just the response json when running the query through the pinot query console?
@g.kishore: Jackie, @alinoorrahman is looking for partitioned distinct count function. I remember building this for folks from Target, do we have any docs for that?
@alinoorrahman: @richard892, regarding the jcmd, I was using the slack web client earlier which didn’t render some of your message. also, the data I’m querying is sensitive, not sure if the dump will contain any of the data.
@jackie.jxt: @alinoorrahman here you can find the documentation of the partitioned distinct count function:
@jackie.jxt: It is very fast and can get accurate result, but requires the column to be partitioned per segment (i.e. no common value across segments).
@jackie.jxt: I feel `select distinctCountHll(heid, 15)` is giving quite close result with low latency
@richard892: @alinoorrahman JFR was designed to send diagnostics back to Oracle and avoids any kind of PII for the reasons you're concerned about
@richard892: the method profiling tab would show you where the time is being spent
@diogo.baeder: Hi folks! First of all, congratulations on the work on 0.9.0! However, bumping up from 0.8.0 in my docker compose image references makes Pinot not work anymore for me when running it locally on my computer. For reference, here's my compose file: ```version: "3.3" services: zookeeper: image: zookeeper:latest container_name: zookeeper hostname: zookeeper environment: ZOOKEEPER_CLIENT_PORT: 2181 ZOOKEEPER_TICK_TIME: 2000 restart: unless-stopped ports: - "2181:2181" volumes: - ./pinot-volumes/zookeeper/data:/data - ./pinot-volumes/zookeeper/datalog:/datalog kafka: image: wurstmeister/kafka:latest container_name: kafka restart: unless-stopped depends_on: - zookeeper ports: - 9094:9094 environment: KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1 # Topic will have 10 partitions and 1 replica KAFKA_CREATE_TOPICS: "bb8_api_logs:10:1,bb8_analyses_logs:10:1,bb8_search_logs:10:1,bb8_visits_logs:10:1" # Other configs KAFKA_BROKER_ID: 1 KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181 KAFKA_ADVERTISED_LISTENERS: INSIDE://:9092,
@diogo.baeder: @trustokoroego seems to be having the same problem as me, right Trust?
@trustokoroego: @diogo.baeder Yes, I face the issue too. My docker compose file is similar to yours.
@diogo.baeder: BTW it would be cool if there was an official docker-compose file, maintained by the Pinot dev team, for testing purposes...
@mark.needham: what's the error?
@mark.needham: is it that it shuts down each component as soon as they start up?
@mark.needham: if so, there was some code added to the PinotAdministrator that has it do a `System.exit(0)` as soon as commands have been executed
@mayanks: @ken @jackie.jxt ^^
@mark.needham: @mayanks I chatted with @xiangfu0 about this. I'm gonna do a PR so that it'll not `System.exit(0)` for 'long running commands' while still exiting cleanly for other commands. @xiangfu0 did already add an exception for the QuickStartCommand, but we need it for a few others too (e.g. StartBroker, StartController, etc)
@mayanks: @mark.needham does 0.9.0 has an issue then?
@ken: Are Docker containers part of the 0.9.0 release? I hadn’t thought they were (didn’t see anything about testing with Docker as part of validating a PR).
@mayanks: I think there are folks who may be starting pinot components using pinot-admin (not using docker). But if that has an exception, then we should be ok
@ken: @mark.needham - the change was to set the system exit flag to true by default, when the pinot admin tool is built (in the pom.xml). The other code changes in `PinotAdministrator` were just to add missing sets for the _status flag.
@ken: @mayanks - ouch, sorry…didn’t realize people were using the pinot admin tool to launch persistent Pinot components. If so, then I should have added that to the release note. It would probably be worthwhile for me to enhance the docs in any case.
@mark.needham: @ken got it. I think that change works great for commands that run and then exit e.g. AddTableCommand, DeleteClusterCommand. But then for other commands (QuickStartCommand, StartBrokerCommand, etc) they need to be left running
@mayanks: cc @xiangfu0 ^^
@ken: @mark.needham - right, so a reasonable fix would be to have an implicit flag value for every command, and the ability to override it using the existing system property.
@ken: A bit clunky, but it works
@xiangfu0: I see ,in this case, let’s modify the doc meanwhile, patch the flags for long running subcommands
@mark.needham: I can work on the patch flags PR tomorrow morning UK. I'll do that unless one of you beats me to it :slightly_smiling_face:
@mayanks: @xiangfu0 by patch you mean patching 0.9.1? I feel docs might not prevent the issue.
@xiangfu0: if it’s docker image, I can modify the ENV_VAR to make it with `-Dpinot.admin.system.exit=false` by default
@xiangfu0: for pinot-admin.sh, we can modify the docs to add the instruction
@mark.needham: I am working around that by overriding the env var: ``` environment: JAVA_OPTS: "-Dpinot.admin.system.exit=false"```
@mark.needham: ```version: '3.7' services: zookeeper: image: zookeeper:3.5.6 hostname: zookeeper container_name: manual-zookeeper ports: - "2181:2181" environment: ZOOKEEPER_CLIENT_PORT: 2181 ZOOKEEPER_TICK_TIME: 2000 pinot-controller: image: apachepinot/pinot:0.9.0-SNAPSHOT-d1606cd0f-20211116-jdk11 command: "StartController -zkAddress manual-zookeeper:2181" container_name: "manual-pinot-controller" volumes: - ./config:/config - ./data:/data ports: - "9000:9000" environment: JAVA_OPTS: "-Dpinot.admin.system.exit=false" depends_on: - zookeeper pinot-broker: image: apachepinot/pinot:0.9.0-SNAPSHOT-d1606cd0f-20211116-jdk11 command: "StartBroker -zkAddress manual-zookeeper:2181" container_name: "manual-pinot-broker" volumes: - ./config:/config - ./data:/data ports: - "8099:8099" environment: JAVA_OPTS: "-Dpinot.admin.system.exit=false" depends_on: - pinot-controller pinot-server: image: apachepinot/pinot:0.9.0-SNAPSHOT-d1606cd0f-20211116-jdk11 command: "StartServer -zkAddress manual-zookeeper:2181" container_name: "manual-pinot-server" volumes: - ./config:/config - ./data:/data restart: on-failure environment: JAVA_OPTS: "-Dpinot.admin.system.exit=false" depends_on: - pinot-broker```
@diogo.baeder: Interesting, I'll try that, thanks!
@mark.needham: btw how does the `restart: unless-stopped` on your broker/server work
@mark.needham: is that to work around the issue where the controller might not have yet written metadata to ZK before the broker/server startup?
@diogo.baeder: I don't really know, that part was not written by me, but I'll try to find out. By the way, your trick with the env vars works like a charm :slightly_smiling_face:
@mark.needham: If you want to see what exactly changed in the code, it's this commit -
@mark.needham: oh cool! That's good :smile:
@mark.needham: that env var was always there, it just started defaulting to 'true' since 0.9.0
@mark.needham: if it's set to false then as soon as e.g. 'StartBroker' has completed, it does a sys.exit, which triggers the shutdown hook!
@diogo.baeder: Got it... makes sense, thanks for the info!
@diogo.baeder: @trustokoroego the trick above works for me, maybe you'll wanna try it yourself too
@trustokoroego: @diogo.baeder Thanks. it worked for me too.
@diogo.baeder: Awesome, good to know! :slightly_smiling_face: (^ @mark.needham)
@bagi.priyank: Ha, that explains why I can't get 0.9.0 working via launcher scripts. Thank you. Can we please update the documentation for it?
@mark.needham: where exactly in the documentation shall we update to indicate this? Just checking where you would look so that we have a good chance of other people finding it
@bagi.priyank:
@bagi.priyank: i am following this to do a poc...i think it is a good start....and probably similarly in the docker setup instructions as well
@mark.needham: alright cool, will update there. It affects all the commands that are long running in nature i.e shouldn't sys exit once they get to the end
@mark.needham: so I think for the next release we'll make it so the long running commands don't exit like that
@bagi.priyank: yeah i am using the launcher script ones but it affects the docker ones too. thanks mark.
@xiangfu0: I updated all the download links. It should reflect soon.
@alihaydar.atil: Hello everyone, is there anyway to do join operation on real-time tables?
@mayanks: Pinot currently supports only the lookup_join feature which is supported for realtime tables too
@mayanks:
@nicholas.yu2: @nicholas.yu2 has joined the channel
@chris.jayakumar: @chris.jayakumar has joined the channel
#getting-started
@bagi.priyank: both
@mark.needham: I guess @xiangfu0 might know what's happened with those. In the meantime you could always use 0.9.0 -
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
