# Revisiting "lookup table" concept **URL:** \ **Category:** Kibana\ **Tags:** painless\ **Created:** [May 2, 2022, 11:45pm UTC](https://discuss.elastic.co/t/revisiting-lookup-table-concept/303791 "2022-05-02T23:45:58Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![erik\_n](https://avatars.discourse-cdn.com/v4/letter/e/e8c25b/32.png) [@erik\_n](https://discuss.elastic.co/u/erik_n)\ **Post date:** [May 2, 2022, 11:45pm UTC](https://discuss.elastic.co/t/revisiting-lookup-table-concept/303791/1 "2022-05-02T23:45:58Z")
I've read all of the previous topics about this and done some searching, but I can't figure it out, so I'll ask specifically about my situation. I have one cluster that is explicitly for reporting and visualizations. I'd like to use data from a connected remote cluster for a visualization, and that's easy enough. But the data in the remote cluster cannot be denormalized, and we cannot re-index it, so the visualizations are working with IDs instead of names. I created another index on the local cluster where each document's ID matches the IDs from the remote cluster's documents, with a name field. Is there any way to script a dynamic field on the local cluster's index pattern so that visualizations can use the the names? I've looked through the painless documentation but could not find anything. Hopefully I'm just missing something, because I could unlock a lot of value with this simple ID lookup. We are at v7.16.3 ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [May 3, 2022, 12:19am UTC](https://discuss.elastic.co/t/revisiting-lookup-table-concept/303791/2 "2022-05-03T00:19:26Z")
What about [Runtime fields | Elasticsearch Guide [7.16] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/7.16/runtime.html)? ---
**Author:** ![erik\_n](https://avatars.discourse-cdn.com/v4/letter/e/e8c25b/32.png) [@erik\_n](https://discuss.elastic.co/u/erik_n)\ **Post date:** [May 3, 2022, 5:49pm UTC](https://discuss.elastic.co/t/revisiting-lookup-table-concept/303791/3 "2022-05-03T17:49:13Z")
A runtime field would be set up on the mapping of the index in the remote cluster, right? I would be able to populate the lookup index on that cluster as well, but I haven't been any to find any example of either a runtime or dynamic field to get the value from another index. I do have the ID of the document in the other index, so I'm hoping it should be something simple like emit(['/namelookup-example/\_doc/exampledocid123'].doc['Name'].value) or something! ---
**Author:** ![erik\_n](https://avatars.discourse-cdn.com/v4/letter/e/e8c25b/32.png) [@erik\_n](https://discuss.elastic.co/u/erik_n)\ **Post date:** [May 3, 2022, 10:59pm UTC](https://discuss.elastic.co/t/revisiting-lookup-table-concept/303791/4 "2022-05-03T22:59:59Z")
I realized what i probably want to do is store a parameterized painless script that will take an ID and return a Name, and use that script for the scripted field. And now I've run into another problem! when i run the base example in the docs, modifed for my index, of ```auto POST /_scripts/painless/_execute { "script": { "source": """ emit(doc['@timestamp'].value.dayOfWeekEnum.getDisplayName(TextStyle.FULL, Locale.ROOT)); """ }, "context": "keyword_field", "context_setup": { "index": "namelookup-corp", "document": { "@timestamp": "2020-04-30T14:31:43-05:00" } } } ``` i get ```auto { "error" : { "root_cause" : [ { "type" : "illegal_access_error", "reason" : "illegal_access_error: failed to access class org.objectweb.asm.Edge from class org.objectweb.asm.MethodWriter (org.objectweb.asm.Edge is in unnamed module of loader 'app'; org.objectweb.asm.MethodWriter is in unnamed module of loader java.net.FactoryURLClassLoader @4d8539de)" } ], "type" : "script_exception", "reason" : "compile error", "script_stack" : [], "script" : "\n emit(doc['@timestamp'].value.dayOfWeekEnum.getDisplayName(TextStyle.FULL, Locale.ROOT));\n ", "lang" : "painless", "caused_by" : { "type" : "illegal_access_error", "reason" : "illegal_access_error: failed to access class org.objectweb.asm.Edge from class org.objectweb.asm.MethodWriter (org.objectweb.asm.Edge is in unnamed module of loader 'app'; org.objectweb.asm.MethodWriter is in unnamed module of loader java.net.FactoryURLClassLoader @4d8539de)" } }, "status" : 400 } ``` We are aware of [Announcement 2021-001](https://alas.aws.amazon.com/announcements/2021-001.html) and have already masked log4j-cve-2021-44228-hotpatch and restarted all nodes in the cluster. I confirmed on an individual node that the service was masked and not running, restarted the node again, and run the script against localhost with curl, but still get the same error. I've even been able to run more basic painless emits as dynamic fields! This is not really kibana specific any more and perhaps I should start a new topic, but this has me baffled. ---
**Author:** ![erik\_n](https://avatars.discourse-cdn.com/v4/letter/e/e8c25b/32.png) [@erik\_n](https://discuss.elastic.co/u/erik_n)\ **Post date:** [May 3, 2022, 11:50pm UTC](https://discuss.elastic.co/t/revisiting-lookup-table-concept/303791/5 "2022-05-03T23:50:09Z")
> [@erik\_n](#): > > I realized what i probably want to do is store a parameterized painless script that will take an ID and return a Name And now that i've spent more time digging, I'm not sure how to do this, since scripts seem to always be executed in the context of a given index and document. (I've got years of experience with a bunch of elastic things, but obviously scripting is not one of those things!) ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [May 3, 2022, 11:58pm UTC](https://discuss.elastic.co/t/revisiting-lookup-table-concept/303791/6 "2022-05-03T23:58:22Z")
I don't believe you can run scripts where you pull data from one index into another, it's not something I have ever seen being done and I can think of a few good reasons why we wouldn't allow it anyway. Can you pull the data from the remote cluster in (eg CCS or CCS) and run a transform job or something similar, to create a new index with the data you want? ---
**Author:** ![erik\_n](https://avatars.discourse-cdn.com/v4/letter/e/e8c25b/32.png) [@erik\_n](https://discuss.elastic.co/u/erik_n)\ **Post date:** [May 4, 2022, 12:09am UTC](https://discuss.elastic.co/t/revisiting-lookup-table-concept/303791/7 "2022-05-04T00:09:06Z")
In some use cases for this, yes I can. In others I have billions of docs in hundreds of terabytes of storage so it isn't feasible, and I was REALLY hoping I could do this with the annotate-on-read concept. If I understand correctly, it seems like I wouldn't be able to do an update with script on this either, and would have to reindex to accomplish this. Does that sound right? ---
**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\ **Post date:** [May 4, 2022, 12:10am UTC](https://discuss.elastic.co/t/revisiting-lookup-table-concept/303791/8 "2022-05-04T00:10:23Z")
Yeah I think that's where you are needing to head unfortunately. ---
**Author:** ![erik\_n](https://avatars.discourse-cdn.com/v4/letter/e/e8c25b/32.png) [@erik\_n](https://discuss.elastic.co/u/erik_n)\ **Post date:** [May 4, 2022, 12:13am UTC](https://discuss.elastic.co/t/revisiting-lookup-table-concept/303791/9 "2022-05-04T00:13:13Z")
that is unfortunate, since names can change and that would make historical reporting weird. kibana may not be the one stop shop I was hoping for here. regardless, thank you for your time! I am still very curious what is happening with the `illegal_access_error`, but it doesn't seem like it's blocking me. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 1, 2022, 12:14am UTC](https://discuss.elastic.co/t/revisiting-lookup-table-concept/303791/10 "2022-06-01T00:14:04Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.