### OpenSearch SQL: Example Data Setup and Query

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-directquery-sql

Provides a complete SQL example demonstrating how to create a temporary view with sample employee data and then query it using various aggregate functions, including ordered-set functions with and without filters.

```sql
CREATE OR REPLACE TEMPORARY VIEW basic_pays AS SELECT * FROM VALUES
('Jane Doe','Accounting',8435),
('Akua Mansa','Accounting',9998),
('John Doe','Accounting',8992),
('Juan Li','Accounting',8870),
('Carlos Salazar','Accounting',11472),
('Arnav Desai','Accounting',6627),
('Saanvi Sarkar','IT',8113),
('Shirley Rodriguez','IT',5186),
('Nikki Wolf','Sales',9181),
('Alejandro Rosalez','Sales',9441),
('Nikhil Jayashankar','Sales',6660),
('Richard Roe','Sales',10563),
('Pat Candella','SCM',10449),
('Gerard Hernandez','SCM',6949),
('Pamela Castillo','SCM',11303),
('Paulo Santos','SCM',11798),
('Jorge Souza','SCM',10586)
AS basic_pays(employee_name, department, salary);
SELECT * FROM basic_pays;
SELECT
department,
percentile_cont(0.25) WITHIN GROUP (ORDER BY salary) AS pc1,
percentile_cont(0.25) WITHIN GROUP (ORDER BY salary) FILTER (WHERE employee_name LIKE '%Bo%') AS pc2,
percentile_cont(0.25) WITHIN GROUP (ORDER BY salary DESC) AS pc3,
percentile_cont(0.25) WITHIN GROUP (ORDER BY salary DESC) FILTER (WHERE employee_name LIKE '%Bo%') AS pc4,
percentile_disc(0.25) WITHIN GROUP (ORDER BY salary) AS pd1,
percentile_disc(0.25) WITHIN GROUP (ORDER BY salary) FILTER (WHERE employee_name LIKE '%Bo%') AS pd2,
percentile_disc(0.25) WITHIN GROUP (ORDER BY salary DESC) AS pd3,
percentile_disc(0.25) WITHIN GROUP (ORDER BY salary DESC) FILTER (WHERE employee_name LIKE '%Bo%') AS pd4
FROM basic_pays
GROUP BY department
ORDER BY department;
```

--------------------------------

### OpenSearch SQL Example: Creating and Inserting Data

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-directquery-sql

This example demonstrates how to create a 'person' table and insert sample data, which is then used in subsequent SORT BY examples.

```sql
CREATE TABLE person (zip_code INT, name STRING, age INT);
INSERT INTO person VALUES
(94588, 'Shirley Rodriguez', 50),
(94588, 'Juan Li', 18),
(94588, 'Anil K', 27),
(94588, 'John D', NULL),
(94511, 'David K', 42),
(94511, 'Aryan B.', 18),
(94511, 'Lalit B.', NULL);
```

--------------------------------

### Get Bit Length of String (SQL Example)

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-directquery-sql

Demonstrates calculating the bit length of a string, which represents the number of bits required to store it.

```sql
-- bit_length
SELECT bit_length('Feathers');
+---------------------+
|bit_length(Feathers)|
+---------------------+
|                   64|
+---------------------+
SELECT bit_length(x'537061726b2053514c');
```

--------------------------------

### PPL join command syntax and rewrite example

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-ppl

Illustrates the PPL syntax for the `join` command, which combines data from multiple sources. It also provides a rewritten example demonstrating how to perform a self-join and a left join to calculate average latency, similar to the SQL example.

```ppl
SEARCH source=<left-table>
| <other piped command>
| [joinType] JOIN
    [leftAlias]
    ON joinCriteria
    <right-table>
| <other piped command>      

```

```ppl
SEARCH source=otel-v1-apm-span-000001
| WHERE serviceName = 'order'
| JOIN left=t1 right=t2
    ON t1.traceId = t2.traceId AND t2.serviceName = 'order'
    otel-v1-apm-span-000001 -- self inner join
| EVAL s_name = t1.name -- rename to avoid ambiguous
| EVAL s_parentSpanId = t1.parentSpanId -- RENAME command would be better when it is supported
| EVAL s_durationInNanos = t1.durationInNanos 
| FIELDS s_name, s_parentSpanId, s_durationInNanos -- reduce colunms in join
| LEFT JOIN left=s1 right=t3
    ON s_name = t3.target.resource AND t3.serviceName = 'order' AND t3.traceGroupName = 'client_cancel_order'
    otel-v1-apm-service-map
| WHERE (s_parentSpanId IS NOT NULL OR (s_parentSpanId IS NULL AND s_name = 'client_cancel_order'))
| STATS avg(s_durationInNanos) -- no need to add alias if there is no ambiguous      

```

--------------------------------

### OpenSearch Service Search Request Example

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/integrations-s3-lambda

An example of a GET request to search for documents within the 'lambda-s3-index' index in Amazon OpenSearch Service. It demonstrates how to verify the ingested data.

```http
GET https://domain-name/lambda-s3-index/_search?pretty
{
  "hits" : {
    "total" : 2,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "lambda-s3-index",
        "_type" : "_doc",
        "_id" : "vTYXaWIBJWV_TTkEuSDg",
        "_score" : 1.0,
        "_source" : {
          "ip" : "12.345.678.91",
          "message" : "GET /some-file.jpg",
          "timestamp" : "10/Oct/2000:14:56:14 -0700"
        }
      },
      {
        "_index" : "lambda-s3-index",
        "_type" : "_doc",
        "_id" : "vjYmaWIBJWV_TTkEuCAB",
        "_score" : 1.0,
        "_source" : {
          "ip" : "12.345.678.90",
          "message" : "PUT /some-file.jpg",
          "timestamp" : "10/Oct/2000:13:55:36 -0700"
        }
      }
    ]
  }
}
```

--------------------------------

### OpenSearch Auto-Tune Started Notification Example

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/monitoring-events

This JSON event signifies that Auto-Tune has begun applying new settings to an OpenSearch Service domain. It includes the status 'Started' and details about the settings being applied and the start time.

```json
{
  "version": "0",
  "id": "3acb26c8-397c-4c89-a80a-ce672a864c55",
  "detail-type": "Amazon OpenSearch Service Auto-Tune Notification",
  "source": "aws.es",
  "account": "123456789012",
  "time": "2020-10-30T22:06:31Z",
  "region": "us-east-1",
  "resources": ["arn:aws:es:us-east-1:123456789012:domain/test-domain"],
  "detail": {
    "event": "Auto-Tune Event",
    "severity": "Informational",
    "status": "Started",
    "scheduleTime": "{iso8601-timestamp}",
    "startTime": "{iso8601-timestamp}",
    "description" : "Auto-Tune is applying the following settings to your domain: { JVM Heap size : 60%}."
  }
}
```

--------------------------------

### PPL Explain Command Examples

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-ppl

These examples illustrate the usage of the `explain` command in PPL, which helps in understanding query execution plans. Different modes like `simple`, `extended`, `codegen`, `cost`, and `formatted` are shown to provide various levels of detail about the query.

```ppl
explain simple | source = table | where a = 1 | fields                                     a,b,c
```

```ppl
explain extended | source = table
```

```ppl
explain codegen | source = table | dedup a | fields                                     a,b,c
```

```ppl
explain cost | source = table | sort a | fields                                 a,b,c
```

```ppl
explain formatted | source = table | fields -                                 a
```

```ppl
explain simple | describe table
```

--------------------------------

### PPL Query Examples with expand and stats

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-ppl

These examples demonstrate the use of the `expand` command to flatten nested fields and the `stats` command to perform aggregations like calculating the maximum salary by state and company. They also show aliasing fields with `as` and using `eval` to create new fields.

```ppl
source = table | expand employee | stats max(salary) as max                                     by state, company
```

```ppl
source = table | expand employee as worker | stats                                     max(salary) as max by state, company
```

```ppl
source = table | expand employee as worker | eval bonus =                                     salary * 3 | fields worker, bonus
```

```ppl
source = table | expand employee | parse description                                     '(?<email>.+@.+)' | fields employee, email
```

```ppl
source = table | eval array=json_array(1, 2, 3) | expand                                     array as uid | fields name, occupation, uid
```

```ppl
source = table | expand multi_valueA as multiA | expand                                     multi_valueB as multiB
```

--------------------------------

### PPL head command syntax and examples

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-ppl

Demonstrates the syntax for the PPL `head` command, which retrieves a specified number of results, optionally skipping a certain number of initial results. It includes examples for fetching the first 10 results, the first N results, and the first N results after an offset.

```ppl
head [<size>] [from <offset>]

```

```ppl
os> source=accounts | fields firstname, age | head;

```

```ppl
os> source=accounts | fields firstname, age | head 3;

```

```ppl
os> source=accounts | fields firstname, age | head 3 from 1;

```

--------------------------------

### Get ASCII Value of Character (SQL Example)

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-directquery-sql

Demonstrates retrieving the ASCII numerical value of a character or a string's first character.

```sql
-- ascii
SELECT ascii('222');
+----------+
|ascii(222)|
+----------+
|        50|
+----------+
SELECT ascii(2);
+--------+
|ascii(2)|
+--------+
|      50|
+--------+
```

--------------------------------

### Asynchronous Search Response Example

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/asynchronous-search

Provides an example of the JSON response received after initiating an asynchronous search. It includes the unique ID of the search, its current state ('RUNNING'), and timestamps for when the search started and when it is scheduled to expire.

```json
{
  "id" : "Fm9pYzJyVG91U19xb0hIQUJnMHJfRFEAAAAAAAknghQ1OWVBczNZQjVEa2dMYTBXaTdEagAAAAAAAAAB",
  "state" : "RUNNING",
  "start_time_in_millis" : 1609329314796,
  "expiration_time_in_millis" : 1609761314796
}
```

--------------------------------

### Batch Get Collections API

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/serverless-list

Retrieves detailed information about one or more specified collections.

```APIDOC
## POST /collections/batch-get

### Description
Retrieves detailed information for a list of collection IDs.

### Method
POST

### Endpoint
/collections/batch-get

### Request Body
- **ids** (array of strings) - Required - A list of collection IDs for which to retrieve details.

### Request Example
```bash
aws opensearchserverless batch-get-collection --ids ["07tjusf2h91cunochc", "1iu5usc4rame"]
```

### Success Response (200)
- **collectionsDetail** (array) - A list of detailed collection objects.
  - **arn** (string) - The ARN of the collection.
  - **id** (string) - The unique identifier of the collection.
  - **name** (string) - The name of the collection.
  - **status** (string) - The current status of the collection.
  - **collectionEndpoint** (string) - The OpenSearch endpoint for the collection.
  - **dashboardEndpoint** (string) - The OpenSearch Dashboards endpoint for the collection.

### Response Example
```json
{
  "collectionsDetail": [
    {
      "arn": "arn:aws:aoss:us-east-1:123456789012:collection/07tjusf2h91cunochc",
      "id": "07tjusf2h91cunochc",
      "name": "my-collection",
      "status": "ACTIVE",
      "collectionEndpoint": "https://07tjusf2h91cunochc.us-east-1.aoss.amazonaws.com",
      "dashboardEndpoint": "https://07tjusf2h91cunochc.us-east-1.aoss.amazonaws.com/_dashboards/"
    }
  ]
}
```
```

--------------------------------

### Window Functions Examples

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-directquery-sql

Demonstrates the usage of various window functions like MIN, LAG, LEAD, NTH_VALUE, FIRST_VALUE, and LAST_VALUE with sample data and queries.

```APIDOC
## Window Functions Examples

### Description
This section provides examples of using window functions in OpenSearch SQL, including `MIN`, `LAG`, `LEAD`, `NTH_VALUE`, `FIRST_VALUE`, and `LAST_VALUE`.

### Example 1: MIN with PARTITION BY

```sql
SELECT name, dept, salary, MIN(salary) OVER (PARTITION BY dept ORDER BY salary) AS min
FROM employees;
```

**Sample Data:**
```
|  Tom|Engineering|    33|
| Jane|  Marketing|    28|
| Jeff|  Marketing|    38|
|Helen|  Marketing|    40|
+-----+-----------+------+------------------+
```

**Result:**
```
+-----+-----------+------+-----+
| name|       dept|salary|  min|
+-----+-----------+------+-----+
| Lisa|      Sales| 10000|10000|
| Alex|      Sales| 30000|10000|
| Evan|      Sales| 32000|10000|
|Helen|  Marketing| 29000|29000|
| Jane|  Marketing| 29000|29000|
| Jeff|  Marketing| 35000|29000|
| Fred|Engineering| 21000|21000|
|  Tom|Engineering| 23000|21000|
|Chloe|Engineering| 23000|21000|
| Paul|Engineering| 29000|21000|
+-----+-----------+------+-----+
```

### Example 2: LAG and LEAD

```sql
SELECT name, salary, LAG(salary) OVER (PARTITION BY dept ORDER BY salary) AS lag, LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) AS lead
FROM employees;
```

**Result:**
```
+-----+-----------+------+-----+-----+ 
| name|       dept|salary|  lag| lead|
+-----+-----------+------+-----+-----+
| Lisa|      Sales| 10000|NULL |30000|
| Alex|      Sales| 30000|10000|32000|
| Evan|      Sales| 32000|30000|    0|
| Fred|Engineering| 21000| NULL|23000|
|Chloe|Engineering| 23000|21000|23000|
|  Tom|Engineering| 23000|23000|29000|
| Paul|Engineering| 29000|23000|    0|
|Helen|  Marketing| 29000| NULL|29000|
| Jane|  Marketing| 29000|29000|35000|
| Jeff|  Marketing| 35000|29000|    0|
+-----+-----------+------+-----+-----+
```

### Example 3: IGNORE NULLS with Window Functions

```sql
SELECT id, v, LEAD(v, 0) IGNORE NULLS OVER w lead, LAG(v, 0) IGNORE NULLS OVER w lag, NTH_VALUE(v, 2) IGNORE NULLS OVER w nth_value, FIRST_VALUE(v) IGNORE NULLS OVER w first_value, LAST_VALUE(v) IGNORE NULLS OVER w last_value
FROM test_ignore_null
WINDOW w AS (ORDER BY id)
ORDER BY id;
```

**Result:**
```
+--+----+----+----+---------+-----------+----------+
|id|   v|lead| lag|nth_value|first_value|last_value|
+--+----+----+----+---------+-----------+----------+
| 0|NULL|NULL|NULL|     NULL|       NULL|      NULL|
| 1|   x|   x|   x|     NULL|          x|         x|
| 2|NULL|NULL|NULL|     NULL|          x|         x|
| 3|NULL|NULL|NULL|     NULL|          x|         x|
| 4|   y|   y|   y|        y|          x|         y|
| 5|NULL|NULL|NULL|        y|          x|         y|
| 6|   z|   z|   z|        y|          x|         z|
| 7|   v|   v|   v|        y|          x|         v|
| 8|NULL|NULL|NULL|        y|          x|         v|
+--+----+----+----+---------+-----------+----------+
```
```

--------------------------------

### OpenSearch Service Data API Search Request Example

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/managedomains-signing-service-requests

Example of making a GET request to an OpenSearch Service data API endpoint to search an index. This requires manual signing using AWS Signature Version 4.

```APIDOC
## GET /{indexName}/_search

### Description
Searches documents within a specified index in an OpenSearch Service domain.

### Method
GET

### Endpoint
`https://<domain-id>.<region>.es.amazonaws.com/<indexName>/_search`

### Parameters
#### Path Parameters
- **indexName** (string) - Required - The name of the index to search within.

#### Query Parameters
- **q** (string) - Optional - The search query string.

#### Request Body
None for this specific example, but search requests can have a JSON body for more complex queries.

### Request Example
```
GET https://my-domain.us-east-1.es.amazonaws.com/movies/_search?q=thor
```

### Response
#### Success Response (200)
Returns search results matching the query.

#### Response Example
```json
{
  "took": 5,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1,
      "relation": "eq"
    },
    "max_score": 1.0,
    "hits": [
      {
        "_index": "movies",
        "_type": "_doc",
        "_id": "some_movie_id",
        "_score": 1.0,
        "_source": {
          "title": "Thor: Ragnarok",
          "genre": "Action, Adventure, Comedy"
          // ... other fields
        }
      }
    ]
  }
}
```
```

--------------------------------

### OpenSearch SQL EXPLAIN default output example

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-directquery-sql

Demonstrates the default output of the EXPLAIN statement when executed on a simple SQL query. The output shows the physical plan for the given statement.

```sql
-- Default Output
EXPLAIN select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
+----------------------------------------------------+
|                                                plan|
+----------------------------------------------------+
| == Physical Plan ==
*(2) HashAggregate(keys=[k#33], functions=[sum(cast(v#34 as bigint))])
+- Exchange hashpartitioning(k#33, 200), true, [id=#59]
+- *(1) HashAggregate(keys=[k#33], functions=[partial_sum(cast(v#34 as bigint))])
+- *(1) LocalTableScan [k#33, v#34]
|
+----------------------------------------------------
```

--------------------------------

### PPL Field Summary Command Examples

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-ppl

These examples showcase the `fieldsummary` command, which computes statistics for fields. They demonstrate how to include specific fields, specify whether to count null values, and combine `fieldsummary` with a `where` clause to analyze fields based on filtered data.

```ppl
source = t | fieldsummary includefields=status_code                                     nulls=false
```

```ppl
source = t | fieldsummary includefields= id, status_code,                                     request_path nulls=true
```

```ppl
source = t | where status_code != 200 | fieldsummary                                     includefields= status_code nulls=true
```

--------------------------------

### Get Tenants API - OpenSearch and Elasticsearch 7.x

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/fgac

Example of retrieving tenant information using the REST API for OpenSearch and Elasticsearch 7.x. In these versions, tenants are objects with their own URIs.

```http
GET _plugins/_security/api/tenants

{
  "global_tenant": {
    "reserved": true,
    "hidden": false,
    "description": "Global tenant",
    "static": false
  }
}
```

--------------------------------

### Example JSON Response for Getting a Specific OpenSearch Service Pipeline

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/list-pipeline

This is an example of the JSON output returned when retrieving details for a specific OpenSearch Service pipeline. It includes the pipeline's name, ARN, unit configuration, status, creation and update timestamps, ingest endpoint URLs, and the pipeline configuration body.

```json
{
    "Pipeline": {
        "PipelineName": "my-pipeline",
        "PipelineArn": "arn:aws:osis:us-east-1:123456789012:pipeline/my-pipeline",
        "MinUnits": 9,
        "MaxUnits": 10,
        "Status": "ACTIVE",
        "StatusReason": {
            "Description": "The pipeline is ready to ingest data."
        },
        "PipelineConfigurationBody": "log-pipeline:\n source:\n http:\n processor:\n - grok:\n match:\nlog: [ '%{COMMONAPACHELOG}' ]\n - date:\n from_time_received: true\n destination: \"@timestamp\"\n  sink:\n - opensearch:\n hosts: [ \"https://search-mdp-performance-test-duxkb4qnycd63rpy6svmvyvfpi.us-east-1.es.amazonaws.com\" ]\n index: \"apache_logs\"\n aws_sts_role_arn: \"arn:aws:iam::123456789012:role/my-domain-role\"\n  aws_region: \"us-east-1\"\n  aws_sigv4: true",,
        "CreatedAt": "2022-10-01T15:28:05+00:00",
        "LastUpdatedAt": "2022-10-21T21:41:08+00:00",
        "IngestEndpointUrls": [
            "my-pipeline-123456789012.us-east-1.osis.amazonaws.com"
        ]
    }
}
```

--------------------------------

### OpenSearch SQL EXPLAIN FORMATTED output example

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-directquery-sql

Shows the output when using the FORMATTED option with the EXPLAIN statement. This format provides a structured outline of the physical plan and detailed node information.

```sql
-- Using Formatted
EXPLAIN FORMATTED select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
+----------------------------------------------------+
|                                                plan|
+----------------------------------------------------+
| == Physical Plan ==
 * HashAggregate (4)
 +- Exchange (3)
    +- * HashAggregate (2)
       +- * LocalTableScan (1)
   
   
 (1) LocalTableScan [codegen id : 1]
 Output: [k#19, v#20]
        
 (2) HashAggregate [codegen id : 1]
 Input: [k#19, v#20]
        
 (3) Exchange
 Input: [k#19, sum#24L]
        
 (4) HashAggregate [codegen id : 2]
 Input: [k#19, sum#24L]
|
+----------------------------------------------------
```

--------------------------------

### Verify OpenSearch Document with GET Request

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/integrations-dynamodb

This example demonstrates how to verify that a document has been successfully indexed into the OpenSearch Service. It uses a GET request to retrieve the document with ID '00001' from the 'lambda-index' index. The response shows the document's metadata and its source content.

```http
GET https://domain-name/lambda-index/_doc/00001
{
    "_index": "lambda-index",
    "_type": "_doc",
    "_id": "00001",
    "_version": 1,
    "found": true,
    "_source": {
        "director": {
            "S": "Kevin Costner"
        },
        "id": {
            "S": "00001"
        },
        "title": {
            "S": "The Postman"
        }
    }
}

```

--------------------------------

### Create IAM Policy for OpenSearch Serverless

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/gsg-serverless-cli

Creates an IAM policy named 'TutorialPolicy' with necessary permissions for OpenSearch Serverless operations like listing and creating collections and security policies. This policy adheres to the principle of least privilege.

```bash
aws iam create-policy \
  --policy-name TutorialPolicy \
  --policy-document "{\"Version\": \"2012-10-17\",\"Statement\": [{\"Action\": [\"aoss:ListCollections\",\"aoss:BatchGetCollection\",\"aoss:CreateCollection\",\"aoss:CreateSecurityPolicy\",\"aoss:GetSecurityPolicy\",\"aoss:ListSecurityPolicies\",\"aoss:CreateAccessPolicy\",\"aoss:GetAccessPolicy\",\"aoss:ListAccessPolicies\"],\"Effect\": \"Allow\",\"Resource\": \"*\"}]}"
```

--------------------------------

### Example Synonyms File for Testing

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/custom-packages

A sample synonyms file in plain text format for testing custom dictionary packages in OpenSearch Service. This file can be saved as 'synonyms.txt' and uploaded to S3.

```text
danish, croissant, pastry
ice cream, gelato, frozen custard
sneaker, tennis shoe, running shoe
basketball shoe, hightop
```

--------------------------------

### Get Pipeline Details for VPC Endpoint Service

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/pipeline-security

This example shows how to retrieve pipeline details using the AWS CLI, specifically focusing on the `vpcEndpointService` which is needed for creating self-managed VPC endpoints.

```APIDOC
## GET /get-pipeline (AWS CLI Example)

### Description
Retrieves details for a specific OpenSearch Ingestion pipeline, including information required for self-managed VPC endpoints.

### Method
AWS CLI Command

### Endpoint
N/A (AWS CLI command)

### Parameters
#### Command Parameters
- **pipeline-name** (string) - Required - The name of the pipeline.

### Request Example
```bash
aws osis get-pipeline --pipeline-name vpc-pipeline
```

### Response
#### Success Response (200)
Returns a JSON object containing pipeline configuration details.

#### Response Example
```json
{
  "pipeline": {
    "pipelineName": "vpc-pipeline",
    "pipelineArn": "arn:aws:osis:us-east-1:123456789012:pipeline/vpc-pipeline",
    "pipelineStatus": "ACTIVE",
    "vpcEndpointService": "com.amazonaws.osis.us-east-1.pipeline-id-1234567890abcdef1234567890",
    "vpcEndpoints": [
      {
        "vpcId": "vpc-1234567890abcdef0",
        "vpcOptions": {
          "subnetIds": [
            "subnet-abcdef01234567890",
            "subnet-021345abcdef6789"
          ],
          "vpcEndpointManagement": "CUSTOMER"
        }
      }
    ]
  }
}
```
```

--------------------------------

### PPL Fields Command Examples

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-ppl

This section provides examples of the `fields` command, used to select, include, or exclude specific columns from the query results. It demonstrates selecting all fields, specific fields, including additional fields, excluding fields, and using `eval` in conjunction with `fields`.

```ppl
source = table
```

```ppl
source = table | fields a,b,c
```

```ppl
source = table | fields + a,b,c
```

```ppl
source = table | fields - b,c
```

```ppl
source = table | eval b1 = b | fields - b1,c
```

--------------------------------

### Example Failed Shard Lock Event

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/monitoring-events

This JSON event indicates that an OpenSearch Service domain is unhealthy due to unassigned shards, specifically encountering a `ShardLockObtainFailedException`. It directs users to a guide for resolving this in-memory shard lock issue.

```json
{
  "version":"0",
  "id":"01234567-0123-0123-0123-012345678901",
  "detail-type":"Amazon OpenSearch Service Notification",
  "source":"aws.es",
  "account":"123456789012",
  "time":"2017-12-01T13:12:22Z",
  "region":"us-east-1",
  "resources":["arn:aws:es:us-east-1:123456789012:domain/test-domain"],
  "detail":{
     "event":"Failed Shard Lock",
     "status":"Warning",
     "severity":"Medium",
     "description":"Your domain is unhealthy due to unassigned shards with [ShardLockObtainFailedException]. For more information, 
                    see https://docs.aws.amazon.com/opensearch-service/latest/developerguide/monitoring-events.html#monitoring-events-failed-shard-lock."
}
}
```

--------------------------------

### PPL Filters Command Examples

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-ppl

These examples demonstrate various filtering conditions using the `where` command in PPL. They cover equality, inequality, numerical comparisons, string matching, checking for presence, nullity, emptiness, blankness, and using functions like `case`, `length`, `not in`, `between`, and `cidrmatch`.

```ppl
source = table | where a = 1 | fields a,b,c
```

```ppl
source = table | where a >= 1 | fields                                 a,b,c
```

```ppl
source = table | where a < 1 | fields a,b,c
```

```ppl
source = table | where b != 'test' | fields                                 a,b,c
```

```ppl
source = table | where c = 'test' | fields a,b,c | head                                     3
```

```ppl
source = table | where ispresent(b)
```

```ppl
source = table | where isnull(coalesce(a, b)) | fields a,b,c                                     | head 3
```

```ppl
source = table | where isempty(a)
```

```ppl
source = table | where isblank(a)
```

```ppl
source = table | where case(length(a) > 6, 'True' else                                     'False') = 'True'
```

```ppl
source = table | where a not in (1, 2, 3) | fields                                     a,b,c
```

```ppl
source = table | where a between 1 and 4
```

```ppl
source = table | where b not between '2024-09-10' and                                     '2025-09-10'
```

```ppl
source = table | where cidrmatch(ip,                                     '***********/24')
```

```ppl
source = table | where cidrmatch(ipv6,                                     '2003:db8::/32')
```

```ppl
source = table | trendline sma(2, temperature) as                                     temp_trend
```

--------------------------------

### OpenSearch Service Software Update Unexecuted Event Example

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/monitoring-events

This event is triggered when OpenSearch Service is unable to initiate a scheduled service software update. The JSON includes the event details, status, severity, and a reason for why the update could not be started.

```json
{
  "version": "0",
  "id": "01234567-0123-0123-0123-012345678901",
  "detail-type": "Amazon OpenSearch Service Software Update Notification",
  "source": "aws.es",
  "account": "123456789012",
  "time": "2016-11-01T13:12:22Z",
  "region": "us-east-1",
  "resources": ["arn:aws:es:us-east-1:123456789012:domain/test-domain"],
  "detail": {
    "event": "Service Software Update",
    "status": "Unexecuted",
    "severity": "Informational",
    "description": "The scheduled service software update [R20200330-p1] cannot be started. Reason: [reason]"
  }
}
```

--------------------------------

### Example Custom Index Routing Warning Event

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/monitoring-events

This JSON event alerts about custom index routing settings that might cause blue-green deployments to get stuck while the domain is in a processing state. It advises verifying the applied settings for proper configuration.

```json
{
  "version":"0",
  "id":"01234567-0123-0123-0123-012345678901",
  "detail-type":"Amazon OpenSearch Service Notification",
  "source":"aws.es",
  "account":"123456789012",
  "time":"2017-12-01T13:12:22Z",
  "region":"us-east-1",
  "resources":["arn:aws:es:us-east-1:123456789012:domain/test-domain"],
  "detail":{
     "event":"Custom Index Routing Warning",
     "status":"Warning",
     "severity":"Medium",
     "description":"Your domain is in processing state and contains indice(s) with custom index.routing.allocation 
                    settings which can cause blue-green deployments to get stuck. Verify settings are applied properly.
                    For more information, see https://docs.aws.amazon.com/opensearch-service/latest/developerguide/monitoring-events.html#monitoring-events-index-routing."
  }
}
```

--------------------------------

### Create IAM Policy

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/gsg-serverless-cli

Creates an IAM policy named 'TutorialPolicy' with necessary permissions for OpenSearch Serverless operations.

```APIDOC
## POST /iam/create-policy

### Description
Creates an AWS Identity and Access Management (IAM) policy.

### Method
POST

### Endpoint
/iam/create-policy

### Parameters
#### Query Parameters
- **--policy-name** (string) - Required - The name for the new IAM policy.
- **--policy-document** (string) - Required - The policy document in JSON format.

### Request Example
```bash
aws iam create-policy \
  --policy-name TutorialPolicy \
  --policy-document "{\"Version\": \"2012-10-17\",\"Statement\": [{\"Action\": [\"aoss:ListCollections\",\"aoss:BatchGetCollection\",\"aoss:CreateCollection\",\"aoss:CreateSecurityPolicy\",\"aoss:GetSecurityPolicy\",\"aoss:ListSecurityPolicies\",\"aoss:CreateAccessPolicy\",\"aoss:GetAccessPolicy\",\"aoss:ListAccessPolicies\"],\"Effect\": \"Allow\",\"Resource\": \"*\"}]}"
```

### Response
#### Success Response (200)
- **Policy** (object) - Details of the created IAM policy.
  - **PolicyName** (string) - The name of the policy.
  - **PolicyId** (string) - The unique identifier for the policy.
  - **Arn** (string) - The Amazon Resource Name (ARN) of the policy.
  - **Path** (string) - The path to the policy.
  - **DefaultVersionId** (string) - The ID of the default version of the policy.
  - **AttachmentCount** (integer) - The number of entities (users, groups, roles) attached to the policy.
  - **PermissionsBoundaryUsageCount** (integer) - The number of entities using this policy as a permissions boundary.
  - **IsAttachable** (boolean) - Indicates if the policy can be attached.
  - **CreateDate** (timestamp) - The date and time when the policy was created.
  - **UpdateDate** (timestamp) - The date and time when the policy was last updated.

#### Response Example
```json
{
    "Policy": {
        "PolicyName": "TutorialPolicy",
        "PolicyId": "ANPAW6WRAECKG6QJWUV7U",
        "Arn": "arn:aws:iam::123456789012:policy/TutorialPolicy",
        "Path": "/",
        "DefaultVersionId": "v1",
        "AttachmentCount": 0,
        "PermissionsBoundaryUsageCount": 0,
        "IsAttachable": true,
        "CreateDate": "2022-10-16T20:57:18+00:00",
        "UpdateDate": "2022-10-16T20:57:18+00:00"
    }
}
```
```

--------------------------------

### OpenSearch Service Software Update Required Event Example

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/monitoring-events

This event notifies that a service software update is required for an OpenSearch Service domain. It includes details on the update, its severity, and information regarding automatic installation if no action is taken, along with a link for more details.

```json
{
  "version": "0",
  "id": "01234567-0123-0123-0123-012345678901",
  "detail-type": "Amazon OpenSearch Service Software Update Notification",
  "source": "aws.es",
  "account": "123456789012",
  "time": "2016-11-01T13:12:22Z",
  "region": "us-east-1",
  "resources": ["arn:aws:es:us-east-1:123456789012:domain/test-domain"],
  "detail": {
    "event": "Service Software Update",
    "status": "Required",
    "severity": "High",
    "description": "Service software update [R20200330-p1] available. Update 
                    will be automatically installed after [21st May 2023] if no
                    action is taken. Service Software Deployment Mechanism: Blue/Green.
                    For more information on deployment configuration, please see: 
                    https://docs.aws.amazon.com/opensearch-service/latest/developerguide/managedomains-configuration-changes.html"
  }
}
```

--------------------------------

### Initiate Manual Snapshot with curl

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/managedomains-snapshot-create

This command initiates a manual snapshot of your OpenSearch Service domain. Replace 'repository-name' and 'snapshot-name' with your specific repository and desired snapshot names. This is a basic snapshot command; for advanced configurations like including/excluding indexes, a request body is required.

```curl
curl -XPUT 'domain-endpoint/_snapshot/repository-name/snapshot-name'
```

--------------------------------

### Get Pipeline Details for VPC Endpoint Service (AWS CLI)

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/pipeline-security

This example demonstrates retrieving pipeline details, specifically the VPC endpoint service name and existing VPC endpoint configurations. This information is crucial for creating or managing self-managed VPC endpoints.

```json
"vpcEndpointService" : "com.amazonaws.osis.us-east-1.pipeline-id-1234567890abcdef1234567890",
"vpcEndpoints" : [
  {
    "vpcId" : "vpc-1234567890abcdef0",
    "vpcOptions" : {
      "subnetIds" : [ "subnet-abcdef01234567890", "subnet-021345abcdef6789" ],
      "vpcEndpointManagement" : "CUSTOMER"
    }
  }
]
```

--------------------------------

### Example JSON Response for Listing OpenSearch Service Pipelines

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/list-pipeline

This is an example of the JSON output returned when listing OpenSearch Service pipelines. It includes details for multiple pipelines, such as their creation and update times, unit configurations, ARNs, names, and statuses.

```json
{
    "NextToken": null,
    "Pipelines": [
        {
            "CreatedAt": 1.671055851E9,
            "LastUpdatedAt": 1.671055851E9,
            "MaxUnits": 4,
            "MinUnits": 2,
            "PipelineArn": "arn:aws:osis:us-west-2:123456789012:pipeline/log-pipeline",
            "PipelineName": "log-pipeline",
            "Status": "ACTIVE",
            "StatusReason": {
                "Description": "The pipeline is ready to ingest data."
            }
        },
            "CreatedAt": 1.671055851E9,
            "LastUpdatedAt": 1.671055851E9,
            "MaxUnits": 2,
            "MinUnits": 8,
            "PipelineArn": "arn:aws:osis:us-west-2:123456789012:pipeline/another-pipeline",
            "PipelineName": "another-pipeline",
            "Status": "CREATING",
            "StatusReason": {
                "Description": "The pipeline is being created. It is not able to ingest data."
            }
        }
    ]
}
```

--------------------------------

### OpenSearch SQL EXPLAIN EXTENDED output example

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-directquery-sql

Illustrates the output when using the EXTENDED option with the EXPLAIN statement. This provides a more detailed breakdown including parsed logical, analyzed logical, optimized logical, and physical plans.

```sql
-- Using Extended
EXPLAIN EXTENDED select k, sum(v) from values (1, 2), (1, 3) t(k, v) group by k;
+----------------------------------------------------+
|                                                plan|
+----------------------------------------------------+
| == Parsed Logical Plan ==
'Aggregate ['k], ['k, unresolvedalias('sum('v), None)]
 +- 'SubqueryAlias `t`
+- 'UnresolvedInlineTable [k, v], [List(1, 2), List(1, 3)]
   
 == Analyzed Logical Plan ==
 k: int, sum(v): bigint
 Aggregate [k#47], [k#47, sum(cast(v#48 as bigint)) AS sum(v)#50L]
 +- SubqueryAlias `t`
    +- LocalRelation [k#47, v#48]
   
 == Optimized Logical Plan ==
 Aggregate [k#47], [k#47, sum(cast(v#48 as bigint)) AS sum(v)#50L]
 +- LocalRelation [k#47, v#48]
   
 == Physical Plan ==
 *(2) HashAggregate(keys=[k#47], functions=[sum(cast(v#48 as bigint))], output=[k#47, sum(v)#50L])
+- Exchange hashpartitioning(k#47, 200), true, [id=#79]
   +- *(1) HashAggregate(keys=[k#47], functions=[partial_sum(cast(v#48 as bigint))], output=[k#47, sum#52L])
    +- *(1) LocalTableScan [k#47, v#48]
|
+----------------------------------------------------
```

--------------------------------

### Configure ISM Template for Automatic Policy Attachment

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/ism

This configuration allows an ISM policy to be automatically attached to newly created indices that match a specified pattern. The `ism_template` field defines the `index_patterns` and `priority` for matching. In this example, any index starting with 'log' will be matched to the policy 'my-policy-id'.

```json
PUT _plugins/_ism/policies/my-policy-id
{
  "policy": {
    "description": "Example policy.",
    "default_state": "...",
    "states": [...],
    "ism_template": {
      "index_patterns": ["log*"],
      "priority": 100
    }
  }
}
```

--------------------------------

### Calculate Field Statistics using PPL `fieldsummary`

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-ppl

Calculates basic statistics (count, distinct count, min, max, avg, stddev, nulls, type) for specified fields using the `fieldsummary` command. This example shows how to get statistics for 'status_code', including null values.

```PPL
os> source = t | where status_code != 200 | fieldsummary includefields= status_code nulls=true
```

--------------------------------

### Example DLQ File Name and Content

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/osis-features-overview

Provides an example of a dead-letter queue file name and the structure of the JSON data within it, showing events that failed to be written to the OpenSearch sink due to retry limits.

```text
dlq-v2-apache-log-pipeline-opensearch-2023-04-05T15:26:19.152938Z-e7eb675a-f558-4048-8566-dac15a4f8343

Record_0	
pluginId            "opensearch"
pluginName          "opensearch"
pipelineName        "apache-log-pipeline"
failedData	
index		  "logs"
indexId		null
status		  0
message		"Number of retries reached the limit of max retries (configured value 15)"
document	
log		    "sample log"
timestamp	    "2023-04-14T10:36:01.070Z"

Record_1	
pluginId            "opensearch"
pluginName          "opensearch"
pipelineName        "apache-log-pipeline"
failedData	
index               "logs"
indexId		null
status		  0
message		"Number of retries reached the limit of max retries (configured value 15)"
document	
log                 "another sample log"
timestamp           "2023-04-14T10:36:01.071Z"
```

--------------------------------

### PPL Field Summary Query

Source: https://docs.aws.amazon.com/opensearch-service/latest/developerguide/supported-ppl

An example PPL query to get a field summary, including count, distinct count, min, max, average, mean, standard deviation, nulls, and type for specified fields. It demonstrates how to use the 'fieldsummary' command with options like 'includefields' and 'nulls'.

```ppl
os> source = t | fieldsummary includefields= id, status_code, request_path nulls=true
```