Using SQL Gateway with Python, Vector Search, and Interoperability in InterSystems Iris

Part 2 – Python and Vector Search

Since we have access to the data from our external table, we can use everything that Iris has to offer with this data. Let's, for example, read the data from our external table and generate a polynomial regression with it.

For more information on using python with Iris, see the documentation available at https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=AFL_epython

Let's now consume the data from the external database to calculate a polynomial regression. To do this, we will use a python code to run a SQL that will read our MySQL table and turn it into a pandas dataframe:

4 0
0 75

Using SQL Gateway with Python, Vector Search, and Interoperability in InterSystems Iris

Part 1 - SQL Gateway

Hello

In this article we will look at the use of SQL Gateway in Iris. SQL Gateway allows Iris to have access to tables from other (external) database via ODBC or JDBC. We can access Tables or Views from various databases, such as Oracle, PostgreSQL, SQL Server, MySQL and others.

4 0
0 94

In the WRC, we frequently see customers contact us because their Web Gateway is unable to serve web pages. This article will explain a frequent reason why these errors can occur, and explain some tools which can be used to debug the problem. This explanation is focused on the Web Gateway serving InterSystems IRIS instances, but the same explanation should apply to the CSP Gateway serving Caché instances as well.

8 1
3 2.8K

The IKO allows for sidecars. The idea behind them is to have direct access to a specific instance of IRIS. If we have mirrored data nodes, the web gateway will (correctly) only give us access to the primary node. But perhaps we need access to a specific instance. The sidecar is the solution.

Building on the example from the previous article, we introduce the sidecar by using a mirrored data node and of course arbiter.

7 1
1 340

Learning LLM Magic

The world of Generative AI has been pretty inescapable for a while, commercial models running on paid Cloud instances are everywhere. With your data stored securely on-prem in IRIS, it might seem daunting to start getting the benefit of experimentation with Large Language Models without having to navigate a minefield of Governance and rapidly evolving API documentation. If only there was a way to bring an LLM to IRIS, preferably in a very small code footprint....

19 0
5 298

In this article, exceptions are covered.

Working with Exceptions

Instead of returning a %Status response, you can raise and throw an Exception. You are then responsible for catching the exception and validating it. IRIS provides five main classes to handle exceptions effectively. Additionally, you can create custom exception class definition based on your needs.

3 0
0 154
Article
· Feb 7 4m read
IRIS %Status and Exceptions

You may encounter errors during any point of program execution, and there are several ways to raise and handle these exceptions. In this article, we'll explore how exceptions are handled efficiently in IRIS.

One of the most commonly used return types is %Status, which is used by methods to indicate success or failure. Let's begin by discussing %Status values.

Working with %Status

4 0
1 165
Article
· Feb 17, 2023 2m read
Returning values with python

Why am I writting this?

Last year I made an article for starters on using embedded python. Later, it started a little discussion on how to return values with python and I found some interesting observations that are worth writing a little article. Also, hopefully I can reach more people by writing this.

Possible situations

There are two things you'll need to care about when returning a value with python. The first is the type you're trying to return and the second is where you're returning it.

3 1
0 558

Interoperability of systems ensures smooth workflow and management of data in today's connected digital world. InterSystems IRIS extends interoperability a notch higher with its Embedded Python feature, which lets developers seamlessly integrate Python scripts into the IRIS components, like services, operations, and custom functions.

3 4
0 250

From the previous article, we identified some issues when working with JSON in SQL.

IRIS offers a dedicated feature for handling JSON documents, called DocDB.

InterSystems IRIS® data platform DocDB is a facility for storing and retrieving database data. It is compatible with, but separate from, traditional SQL table and field (class and property) data storage and retrieval. It is based on JSON (JavaScript Object Notation) which provides support for web-based data exchange. InterSystems IRIS provides support for developing DocDB databases and applications in REST and in ObjectScript, as well as providing SQL support for creating or querying DocDB data.

By its nature, InterSystems IRIS Document Database is a schema-less data structure. That means that each document has its own structure, which may differ from other documents in the same database. This has several benefits when compared with SQL, which requires a pre-defined data structure.

The word “document” is used here as a specific industry-wide technical term, as a dynamic data storage structure. “Document”, as used in DocDB, should not be confused with a text document, or with documentation.

Let's explore how DocDB can help store JSON in the database and integrate it into projects that rely solely on xDBC protocols.

18 1
1 121
Article
· Feb 3 3m read
SQL Host Variables missing ?

Host Variables are a rather common programming feature in many implementations of SQL.
A recent question in DC made me aware that in IRIS, Caché, Ensemble, ...
host variables just exist within embedded SQL

> You can supply host variables for Embedded SQL queries only. <

Related examples are included in the available Documentation

This is a description for a workaround if you don't / can't use embedded SQL.

9 0
0 126

All pods are assigned a Quality of Service (QoS). These are 3 levels of priority pods are assigned within a node.

The levels are as following:

1) Guaranteed: High Priority

2) Burstable: Medium Priority

3) BestEffort: Low Priority

It is a way of telling the kubelet what your priorities are on a certain node if resources need to be reclaimed. This great GIF below by Anvesh Muppeda explains it.

5 0
1 92

My main goal of this article was to prove the use of InterSystems IRIS for Health for REST FHIR interoperability between multiple applications. In this use case, some initiating application makes a REST call to IRIS for Health (which is merely a passthrough for REST calls) to retrieve FHIR data from an Oracle Health R4 FHIR repository. Ideally, it simplifies the syntax for calling the Oracle Health APIs.

9 0
2 248
Article
· Jan 22 4m read
JSON Support in IRIS SQL

While working on getting JSON support for some Python libraries, I discovered some capabilities IRIS provided.

  • JSON_OBJECT - A conversion function that returns data as a JSON object.
  • JSON_ARRAY - A conversion function that returns data as a JSON array.
  • IS JSON - Determines if a data value is in JSON format.
  • JSON_TABLE function returns a table that can be used in a SQL query by mapping JSON.
  • JSONPath support - is a query language for querying values in JSON.

4 3
0 184

When you deploy code from a repo, class (file) deletion might not be reflected by your CICD system.
Here's a simple one-liner to automatically delete all classes in a specified package that have not been imported. It can be easily adjusted for a variety of adjunct tasks:

set packages = "USER.*,MyCustomPackage.*"
set dir = "C:\InterSystems\src\"
set sc = $SYSTEM.OBJ.LoadDir(dir,"ck", .err, 1, .loaded)
set sc = $SYSTEM.OBJ.Delete(packages _ ",'" _ $LTS($LI($LFS(loaded_",",".cls,"), 1, *-1), ",'"),, .err2)

The first command compiles classes and also returns a list of loaded classes. The second command deletes all classes from specified packages, except for the classes loaded just before that.

9 8
2 226

Not sure there are many that connect to MS SQL to execute queries, stored procedures, etc, but our Healthsystem has many different MS SQL based databases we use within the Interoperability environment for various reasons.

With the push to moving from on-prem to the Cloud we ran into some difficulties with our SQL Gateway connections and knowing how to config them to use Microsoft Entra for Active Directory Authentication.

1 0
1 169

We now get to make use of the IKO.

Below we define the environment we will be creating via a Custom Resource Definition (CRD). It lets us define something outside the realm of what the Kubernetes standard knows (this is objects such as your pods, services, persistent volumes (and claims), configmaps, secrets, and lots more). We are building a new kind of object, an IrisCluster object.

9 1
2 511

In your Interoperability Production you could always have a Business Operation that is an HTTP client, that uses OAuth 2.0 for authentication, but you had to customize the Operation for this authentication methodology. Since v2024.3, which was lately released, there is a new capability, providing new settings, to handle this more easily.

4 0
0 92
Article
· Mar 2, 2024 4m read
IKO - Lessons Learned (Part 1 - Helm)

The IKO documentation is robust. A single web page, that consists of about 50 actual pages of documentation. For beginners that can be a bit overwhelming. As the saying goes: how do you eat an elephant? One bite at a time. Let's start with the first bite: helm.

What is Helm?

Helm is to Kubernetes what the InterSystems Package Manager (IPM, formerly ObjectScript Package Manager - ZPM) is to IRIS.

14 2
5 565

In this tutorial, I will discuss how can you connect your IRIS data platform to sql server db .

Prereq:

1 2
0 156
Article
· May 24, 2024 15m read
VIP in GCP

If you're running IRIS in a mirrored configuration for HA in GCP, the question of providing a Mirror VIP (Virtual IP) becomes relevant. Virtual IP offers a way for downstream systems to interact with IRIS using one IP address. Even when a failover happens, downstream systems can reconnect to the same IP address and continue working.

The main issue, when deploying to GCP, is that an IRIS VIP has a requirement of IRIS being essentially a network admin, per the docs.

To get HA, IRIS mirror members must be deployed to different availability zones in one subnet (which is possible in GCP as subnets always span the entire region). One of the solutions might be load balancers, but they, of course, cost extra, and you need to administrate them.

In this article, I would like to provide a way to configure a Mirror VIP without using Load Balancers suggested in most other GCP reference architectures.

7 3
1 484

I'm glad to announce the new version of IoP, which by the way is not just a command line. I'm saying because the new AI search engine still thinks that IoP is just a command line. But it's not. It's a whole framework for building applications on top of the interoperability framework of IRIS with a python first approach.

The new version of IoP: 3.2.0 has a lot of new features, but the most important one is the support of DTL . 🥳

For both IoP messages and jsonschema. 🎉

image

DTL Support

Starting with version 3.2.0, IoP supports DTL transformations.

DTL the Data Transformation Layer in IRIS Interoperability.

DTL transformations are used to transform data from one format to another with a graphical editor.
It supports also jsonschema structures.

0 0
0 106

The Lo-Code Challenge

Imagine the scene. You are working happily at Widgets Direct, the internet's premier retailer of Widgets and Widget Accessories. Your boss has some devastating news, some customers might not be fully happy with their widgets, and we need a helpdesk application to track these complaints. To makes things interesting, he wants this with a very small code footprint and challenges you to deliver an application in less than 150 lines of code using InterSystems IRIS. Is this even possible?

19 10
8 884