You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by "yf-45 (via GitHub)" <gi...@apache.org> on 2024/01/16 16:48:26 UTC
[I] Got empty result from a Multi-valued column when using Lookup join between a fact table and dimension table [pinot]
yf-45 opened a new issue, #12272:
URL: https://github.com/apache/pinot/issues/12272
Hi team,
I tried to use lookup join a fact table with a dimension table, but I got empty result for a Multi-valued column from the dimension table, is it a bug? any workaround?
e.g.
SELECT
fact_id,
fact_col1,
LOOKUP('dimensiont_table', 'mutli_valued_column', 'fact_id', fact_id) AS col2, -- nothing returned
LOOKUP('dimensiont_table', 'single_value_column', 'fact_id', fact_id) AS col3 -- do have the value returned
FROM fact_table
thanks
--
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: commits-unsubscribe@pinot.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] Got empty result from a Multi-valued column when using Lookup join between a fact table and dimension table [pinot]
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr closed issue #12272: Got empty result from a Multi-valued column when using Lookup join between a fact table and dimension table
URL: https://github.com/apache/pinot/issues/12272
--
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: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] Got empty result from a Multi-valued column when using Lookup join between a fact table and dimension table [pinot]
Posted by "yf-45 (via GitHub)" <gi...@apache.org>.
yf-45 commented on issue #12272:
URL: https://github.com/apache/pinot/issues/12272#issuecomment-1898890977
@walterddr, sure
for fact_table the configuration is
```json
{
"OFFLINE": {
"tableName": "fact_table_OFFLINE",
"tableType": "OFFLINE",
"segmentsConfig": {
"replication": "1",
"schemaName": "fact_table",
"segmentPushFrequency": "DAILY",
"segmentPushType": "REFRESH",
"minimizeDataMovement": false
},
"tenants": {
"broker": "DefaultTenant",
"server": "DefaultTenant"
},
"tableIndexConfig": {
"invertedIndexColumns": [],
"rangeIndexVersion": 1,
"loadMode": "MMAP",
"enableDefaultStarTree": false,
"aggregateMetrics": false,
"nullHandlingEnabled": true,
"optimizeDictionary": false,
"enableJsonExtraScalaIndex": false,
"autoGeneratedInvertedIndex": false,
"enableDynamicStarTreeCreation": false,
"optimizeDictionaryForMetrics": false,
"noDictionarySizeRatioThreshold": 0.85,
"createInvertedIndexDuringSegmentGeneration": false
},
"metadata": {},
"isDimTable": false
}
}
```
for the dimension_table:
```json
{
"OFFLINE": {
"tableName": "dimension_table_OFFLINE",
"tableType": "OFFLINE",
"segmentsConfig": {
"replication": "1",
"schemaName": "dimension_table",
"segmentPushFrequency": "DAILY",
"segmentPushType": "REFRESH",
"minimizeDataMovement": false
},
"tenants": {
"broker": "DefaultTenant",
"server": "DefaultTenant"
},
"tableIndexConfig": {
"invertedIndexColumns": [],
"rangeIndexVersion": 1,
"loadMode": "MMAP",
"enableDefaultStarTree": false,
"aggregateMetrics": false,
"nullHandlingEnabled": true,
"optimizeDictionary": false,
"enableJsonExtraScalaIndex": false,
"autoGeneratedInvertedIndex": false,
"enableDynamicStarTreeCreation": false,
"optimizeDictionaryForMetrics": false,
"noDictionarySizeRatioThreshold": 0.85,
"createInvertedIndexDuringSegmentGeneration": false
},
"metadata": {},
"quota": {
"storage": "200M"
},
"isDimTable": true
}
}
```
--
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: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] Got empty result from a Multi-valued column when using Lookup join between a fact table and dimension table [pinot]
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr commented on issue #12272:
URL: https://github.com/apache/pinot/issues/12272#issuecomment-1900915759
confirm this is fixed in latest master.
--
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: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] Got empty result from a Multi-valued column when using Lookup join between a fact table and dimension table [pinot]
Posted by "yf-45 (via GitHub)" <gi...@apache.org>.
yf-45 commented on issue #12272:
URL: https://github.com/apache/pinot/issues/12272#issuecomment-1898378522
@walterddr , sorry, my bad. I have updated the configurations in the earlier comment.
So here is what I got multivalues is empty
but if you select directly from the dimension table, it has values
--
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: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] Got empty result from a Multi-valued column when using Lookup join between a fact table and dimension table [pinot]
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr commented on issue #12272:
URL: https://github.com/apache/pinot/issues/12272#issuecomment-1898782110
still can't reproduce it. can you share your table config for both?
--
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: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] Got empty result from a Multi-valued column when using Lookup join between a fact table and dimension table [pinot]
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr commented on issue #12272:
URL: https://github.com/apache/pinot/issues/12272#issuecomment-1894862762
hello @yf-45 do you have a minimum repro dataset we can use to identify the problem?
--
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: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] Got empty result from a Multi-valued column when using Lookup join between a fact table and dimension table [pinot]
Posted by "yf-45 (via GitHub)" <gi...@apache.org>.
yf-45 commented on issue #12272:
URL: https://github.com/apache/pinot/issues/12272#issuecomment-1895462167
thanks @walterddr for replying.
sure, for the dimension table schema is
`
{
"schemaName": "dimension_table",
"dimensionFieldSpecs": [
{
"name": "fact_id",
"dataType": "STRING"
},
{
"name": "single_value_column",
"dataType": "STRING"
},
{
"name": "multi_value_column",
"dataType": "STRING",
"singleValueField": false
}
],
"dateTimeFieldSpecs": [
{
"name": "last_updated_date",
"dataType": "LONG",
"format": "1:MILLISECONDS:EPOCH",
"granularity": "1:MILLISECONDS"
}
],
"primaryKeyColumns": [
"org_id"
]
}
`
for the fact table schema is:
`
{
"schemaName": "fact_table",
"dimensionFieldSpecs": [
{
"name": "fact_id",
"dataType": "STRING"
},
{
"name": "fact_col1",
"dataType": "STRING"
},
],
"dateTimeFieldSpecs": [
{
"name": "ts",
"dataType": "LONG",
"format": "1:MILLISECONDS:EPOCH",
"granularity": "1:SECONDS"
}
],
"primaryKeyColumns": [
"fact_id"
]
}
`
Data for dimension table:
`
[
{
"fact_id": "003387f5-ec7a-4231-8b36-42c4c438c725",
"single_value_column": "some single value data",
"multi_value_column": ["multi_value_1", "multi_value_2"],
"last_updated_date": "1690227815000"
}
]
`
Data for fact table:
`
[
{
"fact_id": "003387f5-ec7a-4231-8b36-42c4c438c725",
"fact_col1": "some value",
"ts": "1690227815000"
}
]
`
--
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: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
Re: [I] Got empty result from a Multi-valued column when using Lookup join between a fact table and dimension table [pinot]
Posted by "walterddr (via GitHub)" <gi...@apache.org>.
walterddr commented on issue #12272:
URL: https://github.com/apache/pinot/issues/12272#issuecomment-1896247233
i couldn't reproduce. there are several things that needs to be fixed in order to get this right
1. your dim table is on `"primaryKeyColumns": ["id", "fact_id"]` i had to make it to just `fact_id` in order to get your query running
2. there's a chance that you saw the default null value for the results that's not joined successfully.
LOOKUP is essentially a "LEFT JOIN" which will keep every row on the left table and put default null value to the columns that didn't match on the LOOKUP right table. the default value for multi-value-column is empty and the default value for int is INTEGER_MIN and the default value for string is `"null"`.
--
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: commits-unsubscribe@pinot.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org