Get OpenSearch text fields into Quicksight

Question & Answer
1

I am trying to get data from OpenSearch into Quicksight. I successfully added a data source and could see my indices. When I try to create a dataset, I get an error that “fields were skipped because they use unsupported data types.” These are all text fields; they are set up as multi-fields, so they also have a keyword version (for example, account_name.raw).

I have tried adding a keyword version of the field with copy_to. The field appears in the field list, but has no data; it seems this will require re-indexing everything.

I also tried creating an OpenSearch dataset with custom SQL to cast the text fields to keyword types. A query like this returns results in the OpenSearch console:

select account, CAST(account_name as string) as account_name from bill-issues

The results show the account_name field being returned as keyword:

  "schema": [
    {
      "name": "account",
      "type": "keyword"
    },
    {
      "name": "CAST(account_name as string)",
      "alias": "account_name",
      "type": "keyword"
    },

Creating the dataset through boto3 (create_data_set with the query in CustomSql) returned a 200 (success), and I can see the dataset in the Quicksight UI. But when I edit the dataset, I get a popup that says

Can’t perform this action
Something went wrong

with no other information. If I close the popup, I can see and edit my SQL, but even select * from bill-issues gives the same error.

How can I get OpenSearch text fields converted to string / keyword fields so that I can use them in Quicksight?

2

Update: looking at browser Dev Tools, I saw this in the suggestions endpoint return value

validation: {
  errorCode: "TRANSFORM_FAILED"
  errorString: "CustomSQL is not supported for source type AMAZON_OPENSEARCH"

Is there any other way to get OpenSearch text field data (with a keyword multi-field) into Quicksight?

To be clear, these are not giant text fields; they are fields like account name, where we want OpenSearch to do both full text search and aggregations.

3

One thing to try if you haven’t is to create the dataset like you first had it, then go into the dataset and change the datatype of the fields in question.

image
image968×668 80.9 KB

I’m not super well versed in OpenSearch but can you make an indice or view in OpenSearch that has custom SQL? If you can I would try that.

1 Like
4

Thanks for the suggestion, but the text fields don’t get imported Quicksight dataset at all: “fields were skipped because they use unsupported data types.” The skipped fields are not in the field list on the left side.

5

Hmm. As I look at the limitations it might not be possible with OpenSearch.

image
image1544×374 46.4 KB

One thing I can think of is using Athena.

Custom SQL is allowed with Athena and is really helpful for JSON structured objects.

6

Support for TEXT data types is something the team is looking to add to the roadmap. Unfortunately we cannot share any timelines at this stage.

7

Thank you both for your responses. Using Quicksight with OpenSearch isn’t really practical if I need to copy all of the data to a different format (either Athena or reindexing).

For what it’s worth, I don’t need general support for text fields. I do have a keyword/string field (account_name.raw); I need a way to tell Quicksight how to get at / recognize that field.

8

I ran into this problem as well and the only solution I could come up with was adding new keyword fields to the index for the text fields I wanted to report on and then populating them in my ingestion pipeline