Use Capella AI Functions

SELECT h.name AS hotel_name,
       r.author,
       r.content,
       default:ai_sentiment({
           "system_prompt": "Provide a sentiment analysis of the input text provided.",
           "text": r.content,
           "temperature": 0.3,
           "max_tokens": 50
       }) AS review_sentiment
FROM `travel-sample`.`inventory`.`hotel` AS h
UNNEST h.reviews AS r
LIMIT 1;

[
  {
    "hotel_name": "Medway Youth Hostel",
    "author": "Ozella Sipes",
    "content": "This was our 2nd trip here and we enjoyed it as much or more than last year. Excellent location across from the French Market and just across the street from the streetcar stop. Very convenient to several small but good restaurants. Very clean and well maintained. Housekeeping and other staff are all friendly and helpful. We really enjoyed sitting on the 2nd floor terrace over the entrance and \"people-watching\" on Esplanade Ave., also talking with our fellow guests. Some furniture could use a little updating or replacement, but nothing major.",
    "review_sentiment": [
      {
        "response": "The sentiment of the text is overwhelmingly positive. The reviewer expresses enjoyment of their stay, highlighting the excellent location, convenience, cleanliness, and the friendliness of the staff. They mention specific positive experiences, such as sitting on the terrace and interacting with other guests"
      }
    ]
  }
]

SELECT name,
       default:ai_summary({
           "text": description,
           "max_words": 20,
           "temperature": 0.4
       }) AS summarized_description
FROM `travel-sample`.`inventory`.`hotel`
WHERE description IS NOT NULL
LIMIT 2;

[
  {
    "name": "Medway Youth Hostel",
    "summarized_description": [
      {
        "response": "40-bed summer hostel in a converted Oast House, 3 miles from Gillingham, in a semi-rural area."
      }
    ]
  },
  {
    "name": "The Balmoral Guesthouse",
    "summarized_description": [
      {
        "response": "Modernized, affordable guesthouse near Gillingham station with cooking facilities, no meals provided."
      }
    ]
  }
]

SELECT name,
       callsign,
       default:ai_classification({
           "text": name,
           "labels": ["commercial", "charter", "cargo"],
           "temperature": 0.2,
           "max_tokens": 50
       }) AS airline_category
FROM `travel-sample`.`inventory`.`airline`
LIMIT 10;

[
  {
    "name": "40-Mile Air",
    "callsign": "MILE-AIR",
    "airline_category": [
      {
        "response": "charter"
      }
    ]
  },
  {
    "name": "Texas Wings",
    "callsign": "TXW",
    "airline_category": [
      {
        "response": "cargo"
      }
    ]
  },
  {
    "name": "Atifly",
    "callsign": "atifly",
    "airline_category": [
      {
        "response": "cargo"
      }
    ]
  },
  {
    "name": "Jc royal.britannica",
    "callsign": null,
    "airline_category": [
      {
        "response": "cargo"
      }
    ]
  },
  {
    "name": "Locair",
    "callsign": "LOCAIR",
    "airline_category": [
      {
        "response": "cargo"
      }
    ]
  },
  {
    "name": "SeaPort Airlines",
    "callsign": "SASQUATCH",
    "airline_category": [
      {
        "response": "charter"
      }
    ]
  },
  {
    "name": "Alaska Central Express",
    "callsign": "ACE AIR",
    "airline_category": [
      {
        "response": "cargo"
      }
    ]
  },
  {
    "name": "Astraeus",
    "callsign": "FLYSTAR",
    "airline_category": [
      {
        "response": "cargo"
      }
    ]
  },
  {
    "name": "Air Austral",
    "callsign": "REUNION",
    "airline_category": [
      {
        "response": "cargo"
      }
    ]
  },
  {
    "name": "Airlinair",
    "callsign": "AIRLINAIR",
    "airline_category": [
      {
        "response": "commercial"
      }
    ]
  }
]

SELECT name,
       content,
       default:ai_extraction({
           "text": content,
           "labels": ["organization", "location"],
           "temperature": 0.3,
           "max_tokens": 100
       }) AS extracted_entities
FROM `travel-sample`.`inventory`.`landmark`
WHERE content IS NOT NULL
LIMIT 1;

[
  {
    "name": "Royal Engineers Museum",
    "content": "Adult - £6.99 for an Adult ticket that allows you to come back for further visits within a year (children's and concessionary tickets also available). Museum on military engineering and the history of the British Empire. A quite extensive collection that takes about half a day to see. Of most interest to fans of British and military history or civil engineering. The outside collection of tank mounted bridges etc can be seen for free. There is also an extensive series of themed special event weekends, admission to which is included in the cost of the annual ticket.",
    "extracted_entities": [
      {
        "response": "{\"organization\": \"Museum on military engineering\", \"location\": \"British Empire\"}"
      }
    ]
  }
]

SELECT r.content AS original_review,
       default:ai_corrected_grammar({
           "text": r.content,
           "temperature": 0.2,
           "max_tokens": 60
       }) AS corrected_review
FROM `travel-sample`.`inventory`.`hotel` AS h
UNNEST h.reviews AS r
WHERE r.content IS NOT NULL
LIMIT 1;

[
  {
    "original_review": "This was our 2nd trip here and we enjoyed it as much or more than last year. Excellent location across from the French Market and just across the street from the streetcar stop. Very convenient to several small but good restaurants. Very clean and well maintained. Housekeeping and other staff are all friendly and helpful. We really enjoyed sitting on the 2nd floor terrace over the entrance and \"people-watching\" on Esplanade Ave., also talking with our fellow guests. Some furniture could use a little updating or replacement, but nothing major.",
    "corrected_review": [
      {
        "response": "This was our second trip here, and we enjoyed it as much, if not more, than last year. The location is excellent, across from the French Market, and just across the street from the streetcar stop, making it very convenient to several small but good restaurants. The property is very clean"
      }
    ]
  }
]

SELECT name,
       default:ai_generated_text({
           "prompt": "Create a hotel stay promo title for " || name,
           "temperature": 0.8,
           "max_tokens": 50
       }) AS promo_title
FROM `travel-sample`.`inventory`.`hotel`
WHERE vacancy = TRUE
LIMIT 2;

[
  {
    "name": "Medway Youth Hostel",
    "promo_title": [
      {
        "response": "Experience Unforgettable Nights at Medway Youth Hostel: 20% Off & Breakfast Included!"
      }
    ]
  },
  {
    "name": "The Robins",
    "promo_title": [
      {
        "response": "Luxury Escape: 30% Off Stay at The Robins – Indulge in Complimentary Amenities"
      }
    ]
  }
]

SELECT name,
       content AS original_text,
       default:ai_masked({
           "text": content,
           "labels": ["person", "location", "email", "phone", "website"],
           "temperature": 0.2,
           "max_tokens": 80
       }) AS masked_text
FROM `travel-sample`.`inventory`.`landmark`
WHERE content LIKE '%@%' OR content LIKE '%contact%'
LIMIT 1;

[
  {
    "name": "Isle of Kerrera",
    "original_text": "There are two great hiking trails on the island ([http://www.walkhighlands.co.uk/argyll/kerrera-gylen.shtml South] with '''Gylen Castle''' and [http://www.walkhighlands.co.uk/argyll/kerrera-hutcheson.shtml North] with '''Hutcheson's Monument'''). There is a tiny ferry terminal about 3 km South West of Oban on Gallanach Road and the ferry will bring you to the starting point of the two trails. The [http://www.kerrera-ferry.co.uk ferry] runs throughout the year with more frequent rides in summer (Easter - October) and less frequent ones in winter (October - Easter). £4.50 return for adults, £2.00 return for children, bicycles are free. The ferryman can be contacted by phone: +44 (0)1631 563665 or kerreraferry@hotmail.com. To attract the ferry on the mainland, you need to slide a wooden board to reveal a black surface before the scheduled timing.",
    "masked_text": [
      {
        "response": "There are two great hiking trails on the island ([******* South] with '''Gylen Castle''' and [******* North] with '''Hutcheson's Monument'''). There is a tiny ferry terminal about 3 km South West of ******* on Gallanach Road and the ferry will bring you to the starting point of the two trails. The [******* ferry] runs throughout the year with more frequent rides in summer (Easter - October) and less frequent ones in winter (October - Easter). £4.50 return for adults, £2.00 return for children, bicycles are free. The ferryman can be contacted by phone: ******* or *******. To attract the ferry on the mainland, you need to slide a wooden board to reveal a black surface before the scheduled timing."
      }
    ]
  }
]

SELECT h.name AS hotel_name,
       default:ai_similarity({
           "text1": r1.content,
           "text2": r2.content,
           "temperature": 0.0,
           "max_tokens": 10
       }) AS review_similarity
FROM `travel-sample`.`inventory`.`hotel` AS h
UNNEST h.reviews AS r1
UNNEST h.reviews AS r2
LIMIT 3;

[
  {
    "hotel_name": "Medway Youth Hostel",
    "review_similarity": [
      {
        "response": "1.0"
      }
    ]
  },
  {
    "hotel_name": "Medway Youth Hostel",
    "review_similarity": [
      {
        "response": "0.65"
      }
    ]
  },
  {
    "hotel_name": "Medway Youth Hostel",
    "review_similarity": [
      {
        "response": "0.45"
      }
    ]
  }
]

SELECT name,
       description,
       default:ai_translation({
           "system_prompt": "Provide a Spanish translation of the input text, keeping the original meaning intact.",
           "text": description,
           "to_language": "es",
           "temperature": 0.3,
           "max_tokens": 120
       }) AS description_in_spanish
FROM `travel-sample`.`inventory`.`hotel`
WHERE description IS NOT NULL
LIMIT 2;

[
  {
    "name": "Medway Youth Hostel",
    "description": "40 bed summer hostel about 3 miles from Gillingham, housed in a districtive converted Oast House in a semi-rural setting.",
    "description_in_spanish": [
      {
        "response": "Albergue de verano con 40 camas situado a aproximadamente 3 millas de Gillingham, alojado en una distintiva casa convertida de Oast en un entorno semi rural."
      }
    ]
  },
  {
    "name": "The Balmoral Guesthouse",
    "description": "A recently modernised basic but cheap guesthouse across the road from Gillingham station. No meals, but many rooms have a fridge and hob for cooking.",
    "description_in_spanish": [
      {
        "response": "Un hospedaje básico recientemente modernizado, pero económico, ubicado a pocas calles de la estación de Gillingham. No se ofrecen comidas, pero muchas habitaciones tienen un refrigerador y una estufa para cocinar."
      }
    ]
  }
]

SELECT name,
       default:ai_completion({
           "system_prompt": "You are a creative hotel marketing assistant.",
           "user_prompt": "Write a one-line catchy advertisement for the hotel: " || name,
           "temperature": 0.7,
           "max_tokens": 40
       }) AS ad_slogan
FROM `travel-sample`.`inventory`.`hotel`
LIMIT 5;

[
  {
    "name": "Medway Youth Hostel",
    "ad_slogan": [
      {
        "response": "Discover Adventure and Connection at Medway Youth Hostel – Where Every Stay is a New Story!"
      }
    ]
  },
  {
    "name": "The Balmoral Guesthouse",
    "ad_slogan": [
      {
        "response": "Experience timeless elegance and cozy charm at The Balmoral Guesthouse – where every stay feels like coming home!"
      }
    ]
  },
  {
    "name": "The Robins",
    "ad_slogan": [
      {
        "response": "Experience timeless elegance and modern comfort at The Robins—where every stay feels like coming home!"
      }
    ]
  },
  {
    "name": "Le Clos Fleuri",
    "ad_slogan": [
      {
        "response": "Escape to Le Clos Fleuri: Where Every Stay Blooms into a Memorable Experience!"
      }
    ]
  },
  {
    "name": "Glasgow Grand Central",
    "ad_slogan": [
      {
        "response": "Experience timeless elegance and modern luxury at Glasgow Grand Central – where your stay becomes a story worth sharing."
      }
    ]
  }
]