SQL é uma linguagem padrão para armazenar, manipular e recuperar dados em bancos de dados relacionais.
Neste tutorial, vou discutir como você pode conectar sua plataforma de dados IRIS a uma base de dados sql server.
Prerequisitos:
Olá! Eu expandi meu repositório de demonstração, andreas5588/demo-dbs-iris,para facilitar o teste dos recursosFOREIGN SERVER e FOREIGN TABLE no IRIS.
Para alcançar isso, criei um namespace chamado FEDERATION. A ideia é a seguinte:
O Script: demo-dbs-iris/src/sql/02_create_foreign_server.sql.png)
Há três coisas mais importantes para qualquer converrsa sobre performance SQL: Índices, TuneTable e Plano de Consulta. Os PDFs anexos incluem apresentações históricas sobre esses tópicos que cobrem os fundamentos desses 3 itens em um só lugar. Nossa documentação fornece mais detalhes sobre esses e outros tópicos de desempenho do SQL nos links abaixo. As opções de eLearning reforçam vários desses tópicos. Além disso, há vários artigos da Comunidade de Desenvolvedores que abordam o desempenho do SQL, e os links relevantes também estão listados.
Há uma quantidade considerável de repetição nas informações listadas abaixo. Os aspectos mais importantes do desempenho do SQL a serem considerados são:
Olá, gostaria de saber se existe uma maneira de desabilitar o modo de transação ao executar o delete de uma tabela com sql
exemplo:
&SQL(DELETE FROM tabela where data>=:dataInicio and data<=:dataFim)
nesse exemplo estou executando o comando
do $system.OBJ.SetTransactionMode(0)
Porém quando executo o DELETE o processo entra em modo de transação.
Eu criei uma classe que gera código para query customizada a partir de uma global qualquer em um determinado namespace, fazendo com que a global responda ao padrão SQL imediatamente.
Veja abaixo tela com exemplo de uma Global.
Na tela seguinte, como exemplo, executo método passando parâmetros (global e namespace):
Por último, acesso o portal de Administração e executo a query já com a Global respondendo em SQL
.png)
Os campos que são exibidos no SQL são correspondentes aos Pieces e níveis da global.
Exemplo:
Index1 -> o índice no primeiro nível da global
De acordo com o relatório OWASP Top Ten de 2021, um documento de referência na área de segurança de aplicações web, as injeções SQL ocupam a terceira posição entre os riscos mais críticos. Este relatório, disponível em OWASP Top 10: Injection, destaca a gravidade dessa ameaça e a necessidade de implementar medidas de proteção eficazes.
.png)
Uma injeção SQL ocorre quando um atacante malicioso consegue inserir código SQL não autorizado em uma consulta enviada a um banco de dados. Esse código, disfarçado nas entradas do usuário, pode então ser executado pelo banco de dados, causando ações indesejáveis como o roubo de dados confidenciais, a modificação ou a exclusão de informações sensíveis, ou ainda a interrupção do funcionamento da aplicação.
O FHIR (Fast Healthcare Interoperability Resources) é o padrão mais utilizado pelo mercado para interoperar e armazenar dados em saúde. Trata-se de um padrão que mapeia dezenas de recursos de dados (Pacientes, Observações, Medicações, Diagnósticos, Alergias, Vacinas, Faturamento, Provedores de Saúde, Atendimentos, dentre outros) e seus relacionamentos (Medicações do Paciente, por exemplo). O acesso a todas estas estruturas de dados se dá pelo uso de APIs REST em formato JSON ou XML. A princípio, a maioria dos fornecedores de soluções FHIR, não disponibiliza acesso aos dados no formato SQL.
Este é um modelo para um aplicativo FastAPI que pode ser implantado no IRIS como um aplicativo Web nativo.
git clone
cd iris-fastapi-template
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
docker-compose up
A URL base é http://localhost:53795/fastapi/.
/iris - Retorna um objeto JSON com as 10 principais classes presentes no namespace IRISAPP./interop - Um endpoint de ping para testar a estrutura de interoperabilidade do IRIS./posts - Um endpoint CRUD simples para um objeto Post./comments - Um endpoint CRUD simples para um objeto Comentário.Veja o artigo de introdução ao WSGI: wsgi-introduction.
TL;DR: Você pode ativar ou desativar o sinalizador DEBUG no portal de segurança para que as alterações sejam refletidas no aplicativo à medida que você desenvolve.
app.pyEste é o arquivo principal do aplicativo FastAPI. Ele contém o aplicativo FastAPI e as rotas.
from fastapi import FastAPI, Request
import iris
from grongier.pex import Director
# import models
from models import Post, Comment, init_db
from sqlmodel import Session,select
app = FastAPI()
# create a database engine
url = "iris+emb://IRISAPP"
engine = init_db(url)
from fastapi import FastAPI, Request - Importe a classe FastAPI e a classe Requestimport iris - Importe o módulo IRIS.from grongier.pex import Director: Importe a classe Director para vincular o aplicativo Flask ao framework de interoperabilidade do IRIS.from models import Post, Comment, init_db - Importe os modelos e a função init_db.from sqlmodel import Session,select - Importe a classe Session e a função select do módulo sqlmodel.app = FastAPI() - Crie um aplicativo FastAPI.url = "iris+emb://IRISAPP" -Defina o URL do namespace IRIS.engine = init_db(url) - Crie um mecanismo de banco de dados para o ORM sqlmodel..models.pyEste arquivo contém os modelos para o aplicativo.
from sqlmodel import Field, SQLModel, Relationship, create_engine
class Comment(SQLModel, table=True):
id: int = Field(default=None, primary_key=True)
post_id: int = Field(foreign_key="post.id")
content: str
post: "Post" = Relationship(back_populates="comments")
class Post(SQLModel, table=True):
id: int = Field(default=None, primary_key=True)
title: str
content: str
comments: list["Comment"] = Relationship(back_populates="post")
Não há muito a dizer aqui, apenas a definição dos modelos com chaves estrangeiras e relacionamentos.
A função init_db é usada para criar o mecanismo de banco de dados.
def init_db(url):
engine = create_engine(url)
# create the tables
SQLModel.metadata.drop_all(engine)
SQLModel.metadata.create_all(engine)
# initialize database with fake data
from sqlmodel import Session
with Session(engine) as session:
# Create fake data
post1 = Post(title='Post The First', content='Content for the first post')
...
session.add(post1)
...
session.commit()
return engine
engine = create_engine(url) - Crie um mecanismo de banco de dados.SQLModel.metadata.drop_all(engine) - Exclua todas as tabelas.SQLModel.metadata.create_all(engine) - Crie todas as tabelas.with Session(engine) as session: - Crie uma sessão para interagir com o banco de dados.post1 = Post(title='Post The First', content='Content for the first post')- Crie um objeto Post..session.add(post1) -Adicione o objeto Post à sessão.session.commit() -Confirme as alterações no banco de dados.return engine - Retorne o mecanismo de banco de dados./iris endpoint######################
# IRIS Query exemplo #
######################
@app.get("/iris")
def iris_query():
query = "SELECT top 10 * FROM %Dictionary.ClassDefinition"
rs = iris.sql.exec(query)
# Convert the result to a list of dictionaries
result = []
for row in rs:
result.append(row)
return result
@app.get("/iris") - Defina uma rota GET para o endpoint /iris .query = "SELECT top 10 * FROM %Dictionary.ClassDefinition" -Defina a consulta para obter as 10 principais classes no namespace IRIS.rs = iris.sql.exec(query) - Execute a consulta..result = [] - Crie uma lista vazia para armazenar os resultados.for row in rs: - Itere sobre o conjunto de resultados.result.append(row) - Adicione a linha à lista de resultados.return result - Retorne a lista de resultados./interop endpoint########################
# IRIS interop exemplo #
########################
bs = Director.create_python_business_service('BS')
@app.get("/interop")
@app.post("/interop")
@app.put("/interop")
@app.delete("/interop")
def interop(request: Request):
rsp = bs.on_process_input(request)
return rsp
bs = Director.create_python_business_service('BS') - Crie um business service Python.
-Deve ser criado fora da definição da rota para evitar múltiplas instâncias do serviço de negócios.@app.get("/interop") - Define uma rota GET para o endpoint /interop.@app.post("/interop") - Define uma rota POST para o endpoin /interop .def interop(request: Request): - Define o manipulador da rota.rsp = bs.on_process_input(request) - Chame o método on_process_input do business service.return rsp - Retorne a resposta./posts endpoint############################
# operações CRUD de posts #
############################
@app.get("/posts")
def get_posts():
with Session(engine) as session:
posts = session.exec(select(Post)).all()
return posts
@app.get("/posts/{post_id}")
def get_post(post_id: int):
with Session(engine) as session:
post = session.get(Post, post_id)
return post
@app.post("/posts")
def create_post(post: Post):
with Session(engine) as session:
session.add(post)
session.commit()
return post
Este endpoint é usado para realizar operações CRUD no objeto Post.
Não há muito a dizer aqui, apenas a definição das rotas para obter todos os posts, obter um post por ID e criar um post.
Tudo é feito usando o ORM sqlmodel.
/comments endpoint############################
# operações CRUD de comments #
############################
@app.get("/comments")
def get_comments():
with Session(engine) as session:
comments = session.exec(select(Comment)).all()
return comments
@app.get("/comments/{comment_id}")
def get_comment(comment_id: int):
with Session(engine) as session:
comment = session.get(Comment, comment_id)
return comment
@app.post("/comments")
def create_comment(comment: Comment):
with Session(engine) as session:
session.add(comment)
session.commit()
return comment
Este endpoint é usado para realizar operações CRUD no objeto Comment.
Não há muito a dizer aqui, apenas a definição das rotas para obter todos os comentários, obter um comentário por ID e criar um comentário.
Tudo é feito usando o ORM sqlmodel.
Você sempre pode executar um aplicativo Flask autônomo com o seguinte comando:
python3 /irisdev/app/community/app.py
Observação: você deve estar dentro do contêiner para executar este comando.
docker exec -it iris-fastapi-template-iris-1 bash
Fique no modo DEBUG, faça várias chamadas para o aplicativo e as alterações serão refletidas no aplicativo.
Você pode acessar o Portal de Gerenciamento do IRIS acessando
http://localhost:53795/csp/sys/UtilHome.csp.
Para isso, você precisa ter o IRIS instalado em sua máquina.
Em seguida, você precisa criar um namespace chamado IRISAPP.
Instale os requisitos.
Instale IoP :
#init iop
iop --init
# load production
iop -m /irisdev/app/community/interop/settings.py
# start production
iop --start Python.Production
Configure o aplicativo no portal de segurança.
Este é um modelo para um aplicativo Django que pode ser implantado no IRIS como um aplicativo Web nativo.
git clone
cd iris-django-template
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
docker-compose up
A URL base é http://localhost:53795/django/.
/iris - Retorna um objeto JSON com as 10 principais classes presentes no namespace IRISAPP./interop - Um endpoint de ping para testar a estrutura de interoperabilidade do IRIS./api/posts -Um endpoint CRUD simples para um objeto Post./api/comments - Um endpoint CRUD simples para um objeto Comment.Veja o artigo de introdução ao WSGI: wsgi-introduction.
TL;DR: Você pode ativar ou desativar o sinalizador DEBUG no portal de segurança para que as alterações sejam refletidas no aplicativo à medida que você desenvolve.
A aplicação Django é estruturada como se segue:
app - Pasta do projeto Django
app - Pasta da aplicação Django para configuração
settings.py - Arquivo de definições Djangourls.py - Arquivo de configuração de URL Django para conectar as visualizações às URLswsgi.py - Arquivo do Django WSGIasgi.py - Arquivo do Django AGIcommunity - Pasta da aplicação Django para o aplicativo da comunidade, com CRUD nos objetos de Post e Comment
models.py - Arquivo de modelos do Djando para os objetos Post e Commentviews.py - Arquivo de visualizações Django para cessar os objetos Post e Commentserializers.py - Arquivo Django de serializadores para os objetos Post e Comentário. * admin.py - Arquivo Django de administração para adicionar CRUD à interface administrativa.migrations - Pasta Django de migrações para construir o banco de dados.fixtures - Pasta Django de fixtures com dados de demonstraçãosqloniris - Pasta do aplicativo Django para o aplicativo SQL no IRIS.
views.py - Arquivo Django de views para consultar o namespace IRISAPP.apps.py - Arquivo de configuração do aplicativo Django.interop - Pasta do aplicativo Django para o aplicativo de interoperabilidade.
views.py - Arquivo Django de views para testar a estrutura de interoperabilidade.apps.py - Arquivo de configuração do aplicativo Django.manage.py - Arquivo de gerenciamento Django.app/settings.pyEste arquivo contém as configurações Django para o aplicativo.
...
# Definição de aplicação
INSTALLED_APPS = [
'django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'community',
'sqloniris',
'interop',
'rest_framework'
]
...
REST_FRAMEWORK = {
# Use as permissões padrão do Django `django.contrib.auth` ,
# ou permita acesso de apenas leitura para usuários não autenticados
'DEFAULT_PERMISSION_CLASSES': [
'rest_framework.permissions.DjangoModelPermissionsOrAnonReadOnly'
],
'DEFAULT_PAGINATION_CLASS': 'rest_framework.pagination.LimitOffsetPagination',
'PAGE_SIZE': 20
}
...
DATABASES = {
"default": {
"ENGINE": "django_iris",
"EMBEDDED": True,
"NAMESPACE": "IRISAPP",
"USER":"SuperUser",
"PASSWORD":"SYS",
}
}
Algumas definições importantes para notar:
INSTALLED_APPS - Contém a lista de aplicativos instalados no projeto Django.community - O aplicativo Django para as operações CRUD nos objetos Post e Comentário.sqloniris - TO aplicativo Django para as operações SQL no IRIS.interop - O aplicativo Django para as operações de interoperabilidade.rest_framework - O framework Django REST para a API REST.REST_FRAMEWORK - Contém as configurações para o framework Django REST.
DEFAULT_PERMISSION_CLASSES - Somente usuários autenticados podem realizar operações CRUD.DEFAULT_PAGINATION_CLASS - A classe de paginação para a API REST.DATABASES - Contém as configurações para a conexão com o banco de dados IRIS.
django_iris para conectar ao banco de dados IRIS.app/urls.pyEste arquivo contém a configuração de URL para o aplicativo Django.
from django.contrib import admin
from django.urls import path,include
from rest_framework import routers
from community.views import PostViewSet, CommentViewSet
from sqloniris.views import index
from interop.views import index as interop_index
router = routers.DefaultRouter()
router.register(r'posts', PostViewSet)
router.register(r'comments', CommentViewSet)
urlpatterns = [
path('admin/', admin.site.urls),
path('api/', include(router.urls)),
path('iris/', index),
path('interop/', interop_index)
]
router - Contém o roteador padrão para a API REST.routeer.register - Registra os viewsets Post e Comentário no roteador.urlpatterns - Contém os padrões de URL para o aplicativo Django
/admin/ - A interface administrativa Django./api/ -A API REST para os objetos Post e Comentário./iris/ - O endpoint SQL no IRIS./interop/ - O endpoint de interoperabilidade.app/wsgi.pyEste arquivo contém a configuração WSGI para o aplicativo Django.
Este é o arquivo que temos que fornecer ao IRIS para executar o aplicativo Django.
Na seção Security->Applications->Web Applications, temos que fornecer o caminho para este arquivo.
app.wsgiapplication/irisdev/app/appcommunity/models.pyEste arquivo contém os modelos Django para os objetos Post e Comentário.
from django.db import models
# Create your models here.
class Post(models.Model):
title = models.CharField(max_length=100)
content = models.TextField()
class Comment(models.Model):
content = models.TextField()
post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name='comments')
Post - O modelo para o objeto Post.
title - O título do post.content - O conteúdo do post.Comment - O modelo para o objeto Comentário.
content - O conteúdo do comentário.post - A chave estrangeira para o objeto Post.related_name - O nome relacionado para os comentários.community/seializers.pyEste arquivo contém os serializadores Django para os objetos Post e Comentário.
Usando o framework Django REST, podemos serializar os modelos Django em objetos JSON.
from rest_framework import serializers
from community.models import Post, Comment
class PostSerializer(serializers.ModelSerializer):
class Meta:
model = Post
fields = ('id', 'title', 'content', 'comments')
class CommentSerializer(serializers.ModelSerializer):
class Meta:
model = Comment
fields = ('id', 'content', 'post')
PostSerializer -O serializador para o objeto Post.CommentSerializer -O serializador para o objeto Comentário.fields - Os campos a serem serializados.community/views.pyEste arquivo contém as views Django para os objetos Post e Comentário.
Usando o framework Django REST, podemos criar operações CRUD para os modelos Django.
from django.shortcuts import render
from rest_framework import viewsets
# Import the Post and Comment models
from community.models import Post, Comment
# Import the Post and Comment serializers
from community.serializers import PostSerializer, CommentSerializer
# Create your views here.
class PostViewSet(viewsets.ModelViewSet):
queryset = Post.objects.all()
serializer_class = PostSerializer
class CommentViewSet(viewsets.ModelViewSet):
queryset = Comment.objects.all()
serializer_class = CommentSerializer
PostViewSet - O viewset para o objeto Post.CommentViewSet - O viewset para o objeto Comentário.queryset - O queryset para o viewset.serializer_class - A classe de serializador para o viewset.sqloniris/views.pyEste arquivo contém as views Django para as operações SQL no IRIS.
from django.http import JsonResponse
import iris
def index(request):
query = "SELECT top 10 * FROM %Dictionary.ClassDefinition"
rs = iris.sql.exec(query)
# Convert the result to a list of dictionaries
result = []
for row in rs:
result.append(row)
return JsonResponse(result, safe=False)
index - A view para a operação SQL no IRIS.query - A consulta SQL a ser executada no banco de dados IRIS.rs - O conjunto de resultados da consulta.result - A lista de listas do conjunto de resultados.JsonResponse - A resposta JSON para a view, safe é definido como False para permitir lista de listas.interop/views.pyEste arquivo contém as views Django para as operações de interoperabilidade.
from django.http import HttpResponse
from grongier.pex import Director
bs = Director.create_python_business_service('BS')
def index(request):
result = bs.on_process_input(request)
return HttpResponse(result, safe=False)
bs - O objeto de serviço de negócios criado usando a classe Director .index -A view para a operação de interoperabilidade.result - A view para a operação de interoperabilidade.Observação: não usamos JsonResponse para simplificar o código, podemos usá-lo se quisermos retornar um objeto JSON.
Para executar o aplicativo Django em modo autônomo, podemos usar o seguinte comando:
cd /irisdev/app/app
python3 manage.py runserver 8001
Isso executará o aplicativo Django na porta padrão 8001.
Observação: você deve estar dentro do contêiner para executar este comando.
docker exec -it iris-django-template-iris-1 bash
Esteja no modo DEBUG, faça várias chamadas para o aplicativo e as alterações serão refletidas no aplicativo.
Você pode acessar o Portal de Gerenciamento do IRIS acessando http://localhost:53795/csp/sys/UtilHome.csp.
Para isso, você precisa ter o IRIS instalado em sua máquina.
Em seguida, você precisa criar um namespace chamado IRISAPP.
Instale os requisitos.
# Move to the app directory
cd /irisdev/app/app
# python manage.py flush --no-input
python3 manage.py migrate
# create superuser
export DJANGO_SUPERUSER_PASSWORD=SYS
python3 manage.py createsuperuser --no-input --username SuperUser --email admin@admin.fr
# load demo data
python3 manage.py loaddata community/fixtures/demo.json
# collect static files
python3 manage.py collectstatic --no-input --clear
# init iop
iop --init
# load production
iop -m /irisdev/app/app/interop/settings.py
# start production
iop --start Python.Production
Para servir os arquivos estáticos no aplicativo Django, podemos usar o seguinte comando:
cd /irisdev/app
python3 manage.py collectstatic
Isso coletará os arquivos estáticos do aplicativo Django e os servirá no diretório /irisdev/app/static.
Para publicar os arquivos estáticos no IRIS, configure a seçãoSecurity->Applications->Web Applications.
Este é um modelo para um aplicativo Flask que pode ser implantado no IRIS como um aplicativo Web nativo.
git clone
cd iris-flask-template
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
docker-compose up
A URL de base http://localhost:53795/flask/.
/iris - Retorna um objeto JSON com as 10 principais classes presentes no namespace IRISAPP./interop - Um endpoint de ping para testar o framework de interoperabilidade do IRIS/posts - Um simples enpoint CRUD para um objeto de Post/comments - Um enpoint simples de CRUD para o objeto de comentárioVeja o artigo de introdução ao WSGI wsgi-introduction.
TL;DR: Você pode ativar ou desativar o sinalizador DEBUG no portal de segurança para que as alterações sejam refletidas no aplicativo à medida que você desenvolve.
app.pyEste é o arquivo principal do aplicativo. Ele contém o aplicativo Flask e os endpoints.
from flask import Flask, jsonify, request
from models import Comment, Post, init_db
from grongier.pex import Director
import iris
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'iris+emb://IRISAPP'
db = init_db(app)
from flask import Flask, jsonify, request: Importa a livraria Flaskfrom models import Comment, Post, init_db: Importa os modelos e a função de inicialização de base de dadosfrom grongier.pex import Director: Importa a classe Director para vincular o app flask à framework de interoperabilidade do IRISimport iris: Importa a livraria IRISapp = Flask(__name__): Cria uma aplicação Flaskapp.config['SQLALCHEMY_DATABASE_URI'] = 'iris+emb://IRISAPP': Define o URI da base de dados ao namespace IRISAPP
iris+emb é usado para conectar ao IRIS como uma conexão embutida (sem necessidade de uma instância IRIS separadadb = init_db(app): Inicialize a base de dados com aplicação Flask.models.pyO arquivo contem os modelos SQLAlchemy para a aplicação.
from dataclasses import dataclass
from typing import List
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()
@dataclass
class Comment(db.Model):
id:int = db.Column(db.Integer, primary_key=True)
content:str = db.Column(db.Text)
post_id:int = db.Column(db.Integer, db.ForeignKey('post.id'))
@dataclass
class Post(db.Model):
__allow_unmapped__ = True
id:int = db.Column(db.Integer, primary_key=True)
title:str = db.Column(db.String(100))
content:str = db.Column(db.Text)
comments:List[Comment] = db.relationship('Comment', backref='post')
Não há muito o que dizer aqui, os modelos são definidos como classes de dados e são subclasses da classe db.Model
O uso do atributo __allow_unmapped_ é necessário para permitir a criação do objeto Post sem o atributo comments
dataclasses são usadas para ajudar com a serialização de objetos ao JSON
A função init_db inicializa a base de dados com a aplicação Flask.
def init_db(app):
db.init_app(app)
with app.app_context():
db.drop_all()
db.create_all()
# Create fake data
post1 = Post(title='Post The First', content='Content for the first post')
...
db.session.add(post1)
...
db.session.commit()
return db
db.init_app(app): Inicializa a base de dados com a aplicação Flaskwith app.app_context(): Cria um contexto para a aplicaçãodb.drop_all(): Descarta todas as tabelas na base de dadosdb.create_all(): Cria todas as tabelas na base de dados/iris endpoint######################
# IRIS Query exemplo#
######################
@app.route('/iris', methods=['GET'])
def iris_query():
query = "SELECT top 10 * FROM %Dictionary.ClassDefinition"
rs = iris.sql.exec(query)
# Converte o resultado em uma lista de dicionários
result = []
for row in rs:
result.append(row)
return jsonify(result)
Esse endpoint executa uma query na base de dados IRIS e retorna as top 10 classes presentes no namespace IRISAPP
/interop endpoint########################
# IRIS interop exemplo #
########################
bs = Director.create_python_business_service('BS')
@app.route('/interop', methods=['GET', 'POST', 'PUT', 'DELETE'])
def interop():
rsp = bs.on_process_input(request)
return jsonify(rsp)
Este endpoint é usado para testar a estrutura de interoperabilidade do IRIS. Ele cria um objeto de Serviço de Negócio e o vincula ao aplicativo Flask.
Observação: O objeto bs deve estar fora do escopo da solicitação para mantê-lo ativo.
bs = Director.create_python_business_service('BS'): Cria um objeto Business Service chamado 'BS'rsp = bs.on_process_input(request): Chama o método on_process_input do objeto Business Service com o objeto de requisição como um argumento/posts endpoint############################
# operações CRUD para posts #
############################
@app.route('/posts', methods=['GET'])
def get_posts():
posts = Post.query.all()
return jsonify(posts)
@app.route('/posts', methods=['POST'])
def create_post():
data = request.get_json()
post = Post(title=data['title'], content=data['content'])
db.session.add(post)
db.session.commit()
return jsonify(post)
@app.route('/posts/<int:id>', methods=['GET'])
def get_post(id):
...
Este endpoint é usado para realizar operações CRUD no objeto Post
Graças ao módulo dataclasses, o objeto Post pode ser facilmente serializado para JSON.
Aqui, usamos o método query do sqlalchemy para obter todos os posts e os métodos add e commit para criar um novo post
/comments endpoint############################
# operações CRUD para comentários #
############################
@app.route('/comments', methods=['GET'])
def get_comments():
comments = Comment.query.all()
return jsonify(comments)
@app.route('/comments', methods=['POST'])
def create_comment():
data = request.get_json()
comment = Comment(content=data['content'], post_id=data['post_id'])
db.session.add(comment)
db.session.commit()
return jsonify(comment)
@app.route('/comments/<int:id>', methods=['GET'])
def get_comment(id):
...
Este endpoint é usado para realizar operações CRUD no objeto Comment.
O objeto Comment está vinculado ao objeto Post por uma chave estrangeira.
Você sempre pode executar um aplicativo Flask autônomo com o seguinte comando:
python3 /irisdev/app/community/app.py
Nota: você deve estar dentro do container para rodar este comando
docker exec -it iris-flask-template-iris-1 bash
Esteja no modo DEBUG, faça várias chamadas para o aplicativo e as alterações serão refletidas no aplicativo.
Você pode acessar o Portal de Gerenciamento do IRIS acessandohttp://localhost:53795/csp/sys/UtilHome.csp.
Para isso, você precisa ter o IRIS instalado em sua máquina.
Em seguida, você precisa criar um namespace chamado IRISAPP.
Instale os requisitos.
Instale IoP :
#init iop
iop --init
# carregue a produção
iop -m /irisdev/app/community/interop/settings.py
# iniicie a produção
iop --start Python.Production
Configure a aplicação no portal de Segurança
Projeto 4 – SQL Inbound Adapter
Vamos montar nossa próxima integração utilizando o adaptador SQL Inbound Adapter. Este adaptador permite acessar uma tabela externa ao IRIS e consumir seus registros.
No nosso exemplo iremos ler uma tabela via SQL externa ao IRIS através de uma conexão ODBC e armazenar as informações desejadas em uma global.
O primeiro passo é criar as mensagens da integração. Vamos criar o Request e o Response que iremos utilizar para trafegar as mensagens pelo barramento:
Em ObjectScript, você tem uma ampla coleção de funções que retornam algum valor tipicamente:
set variable = $somefunction(param1,param2, ...)
Não há nada de especial nisso.
Mas há um conjunto de funções que classifico como Funções de Lado Esquerdo
A especialidade delas é que você também pode usá-las à esquerda do operador igual como um alvo no comando SET:
set $somefunction(param1,param2, ...) = value
O motivo para levantar esse assunto é que com o IRIS 2024.1 há depois de muitos anos um "novo garoto nessa vizinhança"
Ao compilar minha classe Utils.Persistence.Menu no Studio ele apresenta a seguinte mensagem
ERROR #5356: Compiled storage class '%Storage.SQL doesnot exist
ERROR #5030: An error occurred while compiling class 'Utils.Persistence.Menu'
essa classe foi alterada no IRIS 2024 e após essa alteração começou a apresentar esse erro no CACHE 2018
no IRIS ela compila sem erros.
Alguém sabe como resolver?
No SQL, dados NULL e a string vazia ('') são dados diferentes. O método para definir e checar cada uma é como se segue
(1) dado NULL
[SQL]
insertintotest(a) values(NULL)
select * fromtestwhere a ISNULL[InterSystems ObjectScript]
setx=##class(User.test).%New()
setx.a=""(2) String Vazia ('')
[SQL]
insertintotest(a) values('')
select * fromtestwhere a = ''[InterSystems ObjectScript]
setx=##class(User.test).%New()
setx.a=$C(0)Para mais informações, por favor veja os seguintes documentos:
Isso pode ser conseguido usando o procedimento CSV() da classe %SQL.Util.Procedures .
Abaixo está o exemplo de uso do código. (Assumindo que o arquivo test.csv está em c:\temp.)
Se você encontrou o problema de que nem todas as linhas do seu resultado estão retornando no seu snapshot, você está no lugar certo.
Ao usar snapshots, existe uma informação não trivial de que eles têm um máximo de linhas que vem por padrão como 250.
Para mudar isso é muito simples. Ao invés de enviar o snapshot como referência direto no seu método de execução de query ou procedure, vamos inicializá-lo antes e definir um valor na propriedade MaxRowsToGet.
Um experimento sobre como usar a framework LangChain, Busca por Vetor IRIS e LLMs para gerar SQL compatível com IRIS de prompts de usuários.
Esse artigo foi baseado neste notebook. Você pode rodar com um ambiente pronto para uso com esta aplicação no OpenExchange.
Primeiro, precisamos instalar as livrarias necessárias:
!pip install --upgrade --quiet langchain langchain-openai langchain-iris pandas
Em seguida, importamos os módulos requeridos e definimos o ambiente:
import os
import datetime
import hashlib
from copy import deepcopy
from sqlalchemy import create_engine
import getpass
import pandas as pd
from langchain_core.prompts import PromptTemplate, ChatPromptTemplate
from langchain_core.example_selectors import SemanticSimilarityExampleSelector
from langchain_openai import OpenAIEmbeddings, ChatOpenAI
from langchain.docstore.document import Document
from langchain_community.document_loaders import DataFrameLoader
from langchain.text_splitter import CharacterTextSplitter
from langchain_core.output_parsers import StrOutputParser
from langchain.globals import set_llm_cache
from langchain.cache import SQLiteCache
from langchain_iris import IRISVector
Vamos usar SQLiteCache para manter as chamadas de LLM em cache:
# Cache for LLM calls
set_llm_cache(SQLiteCache(database_path=".langchain.db"))
Defina os parâmetros da conexão da base de dados IRIS:
# IRIS database connection parameters
os.environ["ISC_LOCAL_SQL_HOSTNAME"] = "localhost"
os.environ["ISC_LOCAL_SQL_PORT"] = "1972"
os.environ["ISC_LOCAL_SQL_NAMESPACE"] = "IRISAPP"
os.environ["ISC_LOCAL_SQL_USER"] = "_system"
os.environ["ISC_LOCAL_SQL_PWD"] = "SYS"
Se a chave da OpenAI API não está definida, peça ao usuário para fornecê-la:
if not "OPENAI_API_KEY" in os.environ:
os.environ["OPENAI_API_KEY"] = getpass.getpass()
Crie a string de conxão para a base de dados IRIS:
# IRIS database connection string
args = {
'hostname': os.getenv("ISC_LOCAL_SQL_HOSTNAME"),
'port': os.getenv("ISC_LOCAL_SQL_PORT"),
'namespace': os.getenv("ISC_LOCAL_SQL_NAMESPACE"),
'username': os.getenv("ISC_LOCAL_SQL_USER"),
'password': os.getenv("ISC_LOCAL_SQL_PWD")
}
iris_conn_str = f"iris://{args['username']}:{args['password']}@{args['hostname']}:{args['port']}/{args['namespace']}"
Estabeleça a conexão para a base de dados IRIS:
# Connection to IRIS database
engine = create_engine(iris_conn_str)
cnx = engine.connect().connection
Prepare um dicionário para guardar informações de contexto para o prompt do sistema:
# Dict for context information for system prompt
context = {}
context["top_k"] = 3
Para transformar inputs de usuário em consultas SQL compatíveis com a base de dados IRIS, precisamos criar um prompt efetivo para o modelo de linguagem. Começamos com um prompt inicial que fornece instruções básicas para gerar queries SQL. O template é derivado de prompts default do LangChain's para MSSQL e customizado para a base de dados IRIS.
# Basic prompt template with IRIS database SQL instructions
iris_sql_template = """
Você é um expert em InterSystems IRIS. Dada uma questão como entrada, crie primeiro uma consulta com sintaxe correta para rodar e retorne a resposta para a questão de entrada.
A não ser que o usuário especifique na questão um número específico de exemplos a obter, pesquise por no máximo {top_k} resultados usando a cláusula TOP para o InterSystems IRIS. Você pode ordenar resultados para os dados mais informativos na base de dados.
Nunca pesquise todas as colunas de uma tabela. Você deve consultar apenas as colunas necessárias para responder a questão. Envolva cada nome de coluna em aspas simples para deontar que são identificadores delimitados.
Preste atenção para usar apenas nomes de colunas que você pode ver nas tabelas abaixo. Cuidado para não consultar colunas que não existem. Além disso, preste atenção em qual coluna está em qual tabela.
Atente-se em usar a função CAST(CURRENT_DATE as date) para buscar a data atual, se a questão envolve "hoje".
Use aspas duplas para delimitar identificadores de colunas.
Retorne apenas SQL puro; não aplique nenhum tipo de formatação.
"""
Esse prompt básico configura o modelo de linguagem (LLM) para funcionar como um expert em SQL com guia específico para a base de dados IRIS. Em seguida, fornecemos um prompt auxiliar com informação sobre a base de dados para evitar exageros.
# SQL template extension for including tables context information
tables_prompt_template = """
Only use the following tables:
{table_info}
"""
Para melhorar a acurácia das respostas LLM, usamos uma técnica chamada few-shot prompting. Isso envolve apresentar alguns exemplos para a LLM.
# SQL template extension for including few shots
prompt_sql_few_shots_template = """
Below are a number of examples of questions and their corresponding SQL queries.
{examples_value}
"""
Definimos o template para exemplos few-shot:
# Few shots prompt template
example_prompt_template = "User input: {input}\nSQL query: {query}"
example_prompt = PromptTemplate.from_template(example_prompt_template)
Construímos o prompt de usuário usando o template few-shot:
# User prompt template
user_prompt = "\n" + example_prompt.invoke({"input": "{input}", "query": ""}).to_string()
Finalmente, podemos compor todos os prompts para criar o prompt final:
# Complete prompt template
prompt = (
ChatPromptTemplate.from_messages([("system", iris_sql_template)])
+ ChatPromptTemplate.from_messages([("system", tables_prompt_template)])
+ ChatPromptTemplate.from_messages([("system", prompt_sql_few_shots_template)])
+ ChatPromptTemplate.from_messages([("human", user_prompt)])
)
prompt
Esse prompt espera as variáveis examples_value, input, table_info, etop_k.
É assim que o prompt é estruturado:
ChatPromptTemplate(
input_variables=['examples_value', 'input', 'table_info', 'top_k'],
messages=[
SystemMessagePromptTemplate(
prompt=PromptTemplate(
input_variables=['top_k'],
template=iris_sql_template
)
),
SystemMessagePromptTemplate(
prompt=PromptTemplate(
input_variables=['table_info'],
template=tables_prompt_template
)
),
SystemMessagePromptTemplate(
prompt=PromptTemplate(
input_variables=['examples_value'],
template=prompt_sql_few_shots_template
)
),
HumanMessagePromptTemplate(
prompt=PromptTemplate(
input_variables=['input'],
template=user_prompt
)
)
]
)
Para visualizar como o prompt será enviado a LLM, podemos usar valores no espaço reservado para as variáveis requeridas:
prompt_value = prompt.invoke({
"top_k": "<top_k>",
"table_info": "<table_info>",
"examples_value": "<examples_value>",
"input": "<input>"
})
print(prompt_value.to_string())
Sistema:
Você é um expert em InterSystems IRIS. Dada uma questão como entrada, crie primeiro uma consulta com sintaxe correta para rodar e retorne a resposta para a questão de entrada.
A não ser que o usuário especifique na questão um número específico de exemplos a obter, pesquise por no máximo {top_k} resultados usando a cláusula TOP para o InterSystems IRIS. Você pode ordenar resultados para os dados mais informativos na base de dados.
Nunca pesquise todas as colunas de uma tabela. Você deve consultar apenas as colunas necessárias para responder a questão. Envolva cada nome de coluna em aspas simples para deontar que são identificadores delimitados.
Preste atenção para usar apenas nomes de colunas que você pode ver nas tabelas abaixo. Cuidado para não consultar colunas que não existem. Além disso, preste atenção em qual coluna está em qual tabela.
Atente-se em usar a função CAST(CURRENT_DATE as date) para buscar a data atual, se a questão envolve "hoje".
Use aspas duplas para delimitar identificadores de colunas.
Retorne apenas SQL puro; não aplique nenhum tipo de formatação.
Sistema:
Use apenas as tabelas a seguir:
<table_info>
Sistema:
Abaixo, seguem alguns exemplos de questões e suas consultas SQL correspondentes.
<examples_value>
Humano:
Input do usuário: <input>
Consulta SQL:
Agora estamos prontos para enviar esse prompt para o LLM providenciando as variáveis necessárias. Vamos seguir para o próximo passo quando estiver pronto.
Para criar consultas SQL precisas, precisamos fornecer ao modelo de linguagem (LLM) informações detalhadas sobre as tabelas das bases de dados. Sem essa informação, o LLM deve gerar consultas que parecem plausíveis mas são incorretas devido a exageros. Então, nosso primeiro passo é criar uma função que retorna definições de tabelas da base de dados IRIS.
A função a seguir consulta a variável INFORMATION_SCHEMA para buscar as definições de tabela para um schema (esquema) específico. Se uma tabela específica for fornecida, ela retorna a definição daquela tabela; caso contrário, retorna definições para todas as tabelas naquele esquema.
def get_table_definitions_array(cnx, schema, table=None):
cursor = cnx.cursor()
# Base query to get columns information
query = """
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, PRIMARY_KEY, null EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = %s
"""
# Parameters for the query
params = [schema]
# Adding optional filters
if table:
query += " AND TABLE_NAME = %s"
params.append(table)
# Execute the query
cursor.execute(query, params)
# Fetch the results
rows = cursor.fetchall()
# Process the results to generate the table definition(s)
table_definitions = {}
for row in rows:
table_schema, table_name, column_name, column_type, is_nullable, column_default, column_key, extra = row
if table_name not in table_definitions:
table_definitions[table_name] = []
table_definitions[table_name].append({
"column_name": column_name,
"column_type": column_type,
"is_nullable": is_nullable,
"column_default": column_default,
"column_key": column_key,
"extra": extra
})
primary_keys = {}
# Build the output string
result = []
for table_name, columns in table_definitions.items():
table_def = f"CREATE TABLE {schema}.{table_name} (\n"
column_definitions = []
for column in columns:
column_def = f" {column['column_name']} {column['column_type']}"
if column['is_nullable'] == "NO":
column_def += " NOT NULL"
if column['column_default'] is not None:
column_def += f" DEFAULT {column['column_default']}"
if column['extra']:
column_def += f" {column['extra']}"
column_definitions.append(column_def)
if table_name in primary_keys:
pk_def = f" PRIMARY KEY ({', '.join(primary_keys[table_name])})"
column_definitions.append(pk_def)
table_def += ",\n".join(column_definitions)
table_def += "\n);"
result.append(table_def)
return result
Para esse exemplo, usamos o esquema Aviation (aviação), que está disponível aqui.
# Retrieve table definitions for the Aviation schema
tables = get_table_definitions_array(cnx, "Aviation")
print(tables)
Essa função retorna os predicados CREATE TABLE para todas as tabelas no esquema Aviation:
[
'CREATE TABLE Aviation.Aircraft (\n Event bigint NOT NULL,\n ID varchar NOT NULL,\n AccidentExplosion varchar,\n AccidentFire varchar,\n AirFrameHours varchar,\n AirFrameHoursSince varchar,\n AirFrameHoursSinceLastInspection varchar,\n AircraftCategory varchar,\n AircraftCertMaxGrossWeight integer,\n AircraftHomeBuilt varchar,\n AircraftKey integer NOT NULL,\n AircraftManufacturer varchar,\n AircraftModel varchar,\n AircraftRegistrationClass varchar,\n AircraftSerialNo varchar,\n AircraftSeries varchar,\n Damage varchar,\n DepartureAirportId varchar,\n DepartureCity varchar,\n DepartureCountry varchar,\n DepartureSameAsEvent varchar,\n DepartureState varchar,\n DepartureTime integer,\n DepartureTimeZone varchar,\n DestinationAirportId varchar,\n DestinationCity varchar,\n DestinationCountry varchar,\n DestinationSameAsLocal varchar,\n DestinationState varchar,\n EngineCount integer,\n EvacuationOccurred varchar,\n EventId varchar NOT NULL,\n FlightMedical varchar,\n FlightMedicalType varchar,\n FlightPhase integer,\n FlightPlan varchar,\n FlightPlanActivated varchar,\n FlightSiteSeeing varchar,\n FlightType varchar,\n GearType varchar,\n LastInspectionDate timestamp,\n LastInspectionType varchar,\n Missing varchar,\n OperationDomestic varchar,\n OperationScheduled varchar,\n OperationType varchar,\n OperatorCertificate varchar,\n OperatorCertificateNum varchar,\n OperatorCode varchar,\n OperatorCountry varchar,\n OperatorIndividual varchar,\n OperatorName varchar,\n OperatorState varchar,\n Owner varchar,\n OwnerCertified varchar,\n OwnerCountry varchar,\n OwnerState varchar,\n RegistrationNumber varchar,\n ReportedToICAO varchar,\n SeatsCabinCrew integer,\n SeatsFlightCrew integer,\n SeatsPassengers integer,\n SeatsTotal integer,\n SecondPilot varchar,\n childsub bigint NOT NULL DEFAULT $i(^Aviation.EventC("Aircraft"))\n);',
'CREATE TABLE Aviation.Crew (\n Aircraft varchar NOT NULL,\n ID varchar NOT NULL,\n Age integer,\n AircraftKey integer NOT NULL,\n Category varchar,\n CrewNumber integer NOT NULL,\n EventId varchar NOT NULL,\n Injury varchar,\n MedicalCertification varchar,\n MedicalCertificationDate timestamp,\n MedicalCertificationValid varchar,\n Seat varchar,\n SeatbeltUsed varchar,\n Sex varchar,\n ShoulderHarnessUsed varchar,\n ToxicologyTestPerformed varchar,\n childsub bigint NOT NULL DEFAULT $i(^Aviation.AircraftC("Crew"))\n);',
'CREATE TABLE Aviation.Event (\n ID bigint NOT NULL DEFAULT $i(^Aviation.EventD),\n AirportDirection integer,\n AirportDistance varchar,\n AirportElevation integer,\n AirportLocation varchar,\n AirportName varchar,\n Altimeter varchar,\n EventDate timestamp,\n EventId varchar NOT NULL,\n EventTime integer,\n FAADistrictOffice varchar,\n InjuriesGroundFatal integer,\n InjuriesGroundMinor integer,\n InjuriesGroundSerious integer,\n InjuriesHighest varchar,\n InjuriesTotal integer,\n InjuriesTotalFatal integer,\n InjuriesTotalMinor integer,\n InjuriesTotalNone integer,\n InjuriesTotalSerious integer,\n InvestigatingAgency varchar,\n LightConditions varchar,\n LocationCity varchar,\n LocationCoordsLatitude double,\n LocationCoordsLongitude double,\n LocationCountry varchar,\n LocationSiteZipCode varchar,\n LocationState varchar,\n MidAir varchar,\n NTSBId varchar,\n NarrativeCause varchar,\n NarrativeFull varchar,\n NarrativeSummary varchar,\n OnGroundCollision varchar,\n SkyConditionCeiling varchar,\n SkyConditionCeilingHeight integer,\n SkyConditionNonCeiling varchar,\n SkyConditionNonCeilingHeight integer,\n TimeZone varchar,\n Type varchar,\n Visibility varchar,\n WeatherAirTemperature integer,\n WeatherPrecipitation varchar,\n WindDirection integer,\n WindDirectionIndicator varchar,\n WindGust integer,\n WindGustIndicator varchar,\n WindVelocity integer,\n WindVelocityIndicator varchar\n);'
]
Com essas definições de tabela, podemos seguir para o próximo passo, que é integrá-las ao nosso prompt para o LLM. Isso assegura que o LLM tem informações precisas e compreensivas sobre o esquema da base de dados ao gerar consultas SQL.
Ao trabalhar com bases de dados, especialmente as maiores, enviar a linguagem de definição de dados (Data Definition Language ou DDL) para todas as tabelas em um prompt pode ser impraticável. Ao passo que essa abordagem pode funcionar para bases de dados menores, as bases de dados reais frequentemente contém centenas ou milhares de tabelas, sendo ineficiente processá-las por completo.
No entanto, é improvável que um modelo de linguagem precise saber cada tabela numa base de dados para gerar consultas SQL efetivamente. Para endereçar esse desafio, podemos nivelar as capacidades da procura semântica para selecionar apenas tabelas relevantes baseadas na consulta do usuário.
Nós podemos conseguir isso usando a procura semântica com a Procura de Vetor IRIS. Note que este método é mais efetivo se os seus identificadores de elementos SQL (como tabelas, campos e chaves) têm nomes significativos. Se seus identificadores forem códigos abstratos, consiere usar um dicionário de dados no lugar.
Primeiro, vamos extrair as definições de tabelas para um DataFrame pandas:
# Retrieve table definitions into a pandas DataFrame
table_def = get_table_definitions_array(cnx=cnx, schema='Aviation')
table_df = pd.DataFrame(data=table_def, columns=["col_def"])
table_df["id"] = table_df.index + 1
table_df
O DataFrame (table_df) deve se parecer com algo assim:
| col_def | id | |
|---|---|---|
| 0 | CREATE TABLE Aviation.Aircraft (\n Event bigi... | 1 |
| 1 | CREATE TABLE Aviation.Crew (\n Aircraft varch... | 2 |
| 2 | CREATE TABLE Aviation.Event (\n ID bigint NOT... | 3 |
Em seguida, divida as definições de tabelas em documentos LangChain. Esse passo é crucial para lidar com grandes conjuntos de texto e extrair textos embutidos:
loader = DataFrameLoader(table_df, page_content_column="col_def")
documents = loader.load()
text_splitter = CharacterTextSplitter(chunk_size=400, chunk_overlap=20, separator="\n")
tables_docs = text_splitter.split_documents(documents)
tables_docs
A lista resultante tables_docs contém documentos divididos com metadados, como o seguinte:
[Document(metadata={'id': 1}, page_content='CREATE TABLE Aviation.Aircraft (\n Event bigint NOT NULL,\n ID varchar NOT NULL,\n ...'),
Document(metadata={'id': 2}, page_content='CREATE TABLE Aviation.Crew (\n Aircraft varchar NOT NULL,\n ID varchar NOT NULL,\n ...'),
Document(metadata={'id': 3}, page_content='CREATE TABLE Aviation.Event (\n ID bigint NOT NULL DEFAULT $i(^Aviation.EventD),\n ...')]
Agora, use a classe IRISVector do langchain-iris para extrair vetores embutidos e guardá-los:
tables_vector_store = IRISVector.from_documents(
embedding=OpenAIEmbeddings(),
documents=tables_docs,
connection_string=iris_conn_str,
collection_name="sql_tables",
pre_delete_collection=True
)
Nota: A bandeira pre_delete_collection é definida como True (verdadeiro) para propósitos de demonstração, para assegurar uma coleção nova em cada rodada de teste. Em um ambinte produtivo, essa bandeira geralmente deve ser definida como False (falso).
Com tabela de embutidos guardada, você agora pode consultar tabelas relevantes baseadas no input do usuário:
input_query = "List the first 2 manufacturers"
relevant_tables_docs = tables_vector_store.similarity_search(input_query, k=3)
relevant_tables_docs
Por exemplo, consultar por manufaturas pode retornar:
[Document(metadata={'id': 1}, page_content='GearType varchar,\n LastInspectionDate timestamp,\n ...'),
Document(metadata={'id': 1}, page_content='AircraftModel varchar,\n AircraftRegistrationClass varchar,\n ...'),
Document(metadata={'id': 3}, page_content='LocationSiteZipCode varchar,\n LocationState varchar,\n ...')]
Para os metadados, você pode ver que apenas o ID 1 da tabela (Aviation.Aircraft) é relevante, que linha com a consulta.
Apesar desta abordagem ser geralmente efetiva, pode não ser sempre perfeita. Por exemplo, consultar por áreas de queda pode retornar tabelas menos relevantes:
input_query = "List the top 10 most crash sites"
relevant_tables_docs = tables_vector_store.similarity_search(input_query, k=3)
relevant_tables_docs
Os resultados podem incluir:
[Document(metadata={'id': 3}, page_content='LocationSiteZipCode varchar,\n LocationState varchar,\n ...'),
Document(metadata={'id': 3}, page_content='InjuriesGroundSerious integer,\n InjuriesHighest varchar,\n ...'),
Document(metadata={'id': 1}, page_content='CREATE TABLE Aviation.Aircraft (\n Event bigint NOT NULL,\n ID varchar NOT NULL,\n ...')]
Apesar de retornar a tabela correta Aviation.Event duas vezes, a tabela Aviation.Aircraft também pode aparecer, o que pode ser melhorado com filtros adicionais ou limitações. Isso está além do escopo deste exemplo e vai ser deixado para implementações futuras.
Para automatizar este processo, defina uma função para filtrar e retornar as tabelas relevantes baseado no input do usuário:
def get_relevant_tables(user_input, tables_vector_store, table_df):
relevant_tables_docs = tables_vector_store.similarity_search(user_input)
relevant_tables_docs_indices = [x.metadata["id"] for x in relevant_tables_docs]
indices = table_df["id"].isin(relevant_tables_docs_indices)
relevant_tables_array = [x for x in table_df[indices]["col_def"]]
return relevant_tables_array
Essa função vai ajudar em retornar de maneira eficiente apenas as tabelas relevantes a enviar ao LLM, reduzindo o tamanho do prompt e melhorando a performance da consulta num geral.
Ao trabalhar com modelos de linguagem (LLMs), fornecer exemplos relevantes ajuda a assegurar respostas precisas e contextualmente apropriadas. Esses exemplos, referidos como exemplos "few-shot", guiam o LLM para entender a estrutura e contexto das consultas que deve manusear.
No nosso caso, precisamos popular a variável examples_value com um conjunto diverso de consultas SQL que cobrem um espectro vasto de sinitaxe IRIS SQL e as tabelas disponíveis na base de dados. Isso ajuda a prevenir que o LLM gere consultas incorretas ou irrelevantes.
Abaixo, uma lista de queries de exemplo desenhadas para ilustrar várias operações SQL:
examples = [
{"input": "List all aircrafts.", "query": "SELECT * FROM Aviation.Aircraft"},
{"input": "Find all incidents for the aircraft with ID 'N12345'.", "query": "SELECT * FROM Aviation.Event WHERE EventId IN (SELECT EventId FROM Aviation.Aircraft WHERE ID = 'N12345')"},
{"input": "List all incidents in the 'Commercial' operation type.", "query": "SELECT * FROM Aviation.Event WHERE EventId IN (SELECT EventId FROM Aviation.Aircraft WHERE OperationType = 'Commercial')"},
{"input": "Ache o número total de incidentes.", "query": "SELECT COUNT(*) FROM Aviation.Event"},
{"input": "Liste todos os incidentes que ocorreram no 'Canadá'.", "query": "SELECT * FROM Aviation.Event WHERE LocationCountry = 'Canada'"},
{"input": "Quantos incidentes estão associados com a aeronave com AircraftKey 5?", "query": "SELECT COUNT(*) FROM Aviation.Aircraft WHERE AircraftKey = 5"},
{"input": "Ache o número total de diferentes aeronaves envolvidas em incidentes.", "query": "SELECT COUNT(DISTINCT AircraftKey) FROM Aviation.Aircraft"},
{"input": "Liste todos os incidentes que ocorreram após 5 PM.", "query": "SELECT * FROM Aviation.Event WHERE EventTime > 1700"},
{"input": "Quem são os top 5 operadores por número de incidentes?", "query": "SELECT TOP 5 OperatorName, COUNT(*) AS IncidentCount FROM Aviation.Aircraft GROUP BY OperatorName ORDER BY IncidentCount DESC"},
{"input": "Quais incidentes ocorreram no ano 2020?", "query": "SELECT * FROM Aviation.Event WHERE YEAR(EventDate) = '2020'"},
{"input": "Qual foi o mês com mais incidentes no ano 2020?", "query": "SELECT TOP 1 MONTH(EventDate) EventMonth, COUNT(*) EventCount FROM Aviation.Event WHERE YEAR(EventDate) = '2020' GROUP BY MONTH(EventDate) ORDER BY EventCount DESC"},
{"input": "Quantos membros de tripulação foram envolvidos em incidentes?", "query": "SELECT COUNT(*) FROM Aviation.Crew"},
{"input": "Liste todos os incidentes com informações detalhadas da aeronave para os incidentes que ocorreram no ano 2012.", "query": "SELECT e.EventId, e.EventDate, a.AircraftManufacturer, a.AircraftModel, a.AircraftCategory FROM Aviation.Event e JOIN Aviation.Aircraft a ON e.EventId = a.EventId WHERE Year(e.EventDate) = 2012"},
{"input": "Ache todos os incidentes onde houve mais de 5 feridos e inclua a fabricante da aeronave e modelo.", "query": "SELECT e.EventId, e.InjuriesTotal, a.AircraftManufacturer, a.AircraftModel FROM Aviation.Event e JOIN Aviation.Aircraft a ON e.EventId = a.EventId WHERE e.InjuriesTotal > 5"},
{"input": "Liste todos os membros da tripulação envolvidos em incidentes com machucados sérios, junto da da data de incidente e localização.", "query": "SELECT c.CrewNumber AS 'Crew Number', c.Age, c.Sex AS Gender, e.EventDate AS 'Event Date', e.LocationCity AS 'Location City', e.LocationState AS 'Location State' FROM Aviation.Crew c JOIN Aviation.Event e ON c.EventId = e.EventId WHERE c.Injury = 'Serious'"}
]
[ Nota do tradutor: as frases foram traduzidas para fins de compreensão, mas os as informações de colunas nas frases deveriam idealmente ser deixadas em inglês para o interpretador melhor associar com as colunas na tabela, que estão em inglês. Segue um breve glossário: Aviation.Aircraft = Aviação.Aeronave key = chave month = mês count = contagem OpertationType = tipo de operação OperatorName = nome do operador LocationCountry = país de localização State = estado City = cidade Crew = Tripulação Injury = ferimento ]
Dada a lista de exemplos que vai sempre expandir, não é uma boa ideia fornecer todas elas ao LLM. Ao invés disso, vamos usar a Busca Vetorial IRIS com a classe SemanticSimilarityExampleSelector to identify the most relevant examples based on user prompts.
example_selector = SemanticSimilarityExampleSelector.from_examples(
examples,
OpenAIEmbeddings(),
IRISVector,
k=5,
input_keys=["input"],
connection_string=iris_conn_str,
collection_name="sql_samples",
pre_delete_collection=True
)
Nota: A bandeira pre_delete_collection é usada aqui para propósitos de demonstração para assegura uma coleção nova em cada rodada de teste. Em um ambiente produtivo, essa bandeira deve ser definida como False para evitar deleções desnecessárias.
Para buscar os exemplos mais relevantes para uma dada entrada, use o seletor como se segue:
input_query = "Busque todos os eventos em 2010 informando o Event Id e date, location city e state, aircraft manufacturer e model."
relevant_examples = example_selector.select_examples({"input": input_query})
Os resultados devem parecer como algo assim:
[{'input': 'Liste todos os incidents com informações detalhadas sobre a Aircraft para os incidentes que ocorreram no year 2012.', 'query': 'SELECT e.EventId, e.EventDate, a.AircraftManufacturer, a.AircraftModel, a.AircraftCategory FROM Aviation.Event e JOIN Aviation.Aircraft a ON e.EventId = a.EventId WHERE Year(e.EventDate) = 2012'},
{'input': "Busque todos os incidents para a Aircraft de ID 'N12345'.", 'query': "SELECT * FROM Aviation.Event WHERE EventId IN (SELECT EventId FROM Aviation.Aircraft WHERE ID = 'N12345')"},
{'input': 'Ache todos os incidents onde houve mais de 5 injuries e inclua o aircraft manufacturer e model.', 'query': 'SELECT e.EventId, e.InjuriesTotal, a.AircraftManufacturer, a.AircraftModel FROM Aviation.Event e JOIN Aviation.Aircraft a ON e.EventId = a.EventId WHERE e.InjuriesTotal > 5'},
{'input': 'Liste todas as aircrafts.', 'query': 'SELECT * FROM Aviation.Aircraft'},
{'input': 'Ache o número total de aircrafts distintas envolvidas em incidents.', 'query': 'SELECT COUNT(DISTINCT AircraftKey) FROM Aviation.Aircraft'}]
Se você especificamente precisa de exemplos relacionados a quantidades, você pode procurar o seletor conforme:
input_query = "Qual foi o número de incidentes envolvendo a aircraft Boeing."
quantity_examples = example_selector.select_examples({"input": input_query})
A saída será:
[{'input': 'Quantos incidents estão associados à aircraft de AircraftKey 5?', 'query': 'SELECT COUNT(*) FROM Aviation.Aircraft WHERE AircraftKey = 5'},
{'input': 'Busque o número total de aircrafts distintas envolvidas em incidents.', 'query': 'SELECT COUNT(DISTINCT AircraftKey) FROM Aviation.Aircraft'},
{'input': 'Quantos membros de tripulação foram envolvidos em incidents?', 'query': 'SELECT COUNT(*) FROM Aviation.Crew'},
{'input': 'Ache todos os incidents onde houve mais de 5 ferimentos e inclua a aircraft manufacturer e model.', 'query': 'SELECT e.EventId, e.InjuriesTotal, a.AircraftManufacturer, a.AircraftModel FROM Aviation.Event e JOIN Aviation.Aircraft a ON e.EventId = a.EventId WHERE e.InjuriesTotal > 5'},
{'input': 'Liste todos os incidents com informação detalhada sobre as aircrafts para incidents que ocorreram no ano 2012.', 'query': 'SELECT e.EventId, e.EventDate, a.AircraftManufacturer, a.AircraftModel, a.AircraftCategory FROM Aviation.Event e JOIN Aviation.Aircraft a ON e.EventId = a.EventId WHERE Year(e.EventDate) = 2012'}]
A saída inclui exemplos que especificamente buscam contagens e quantidades.
Apesar do SemanticSimilarityExampleSelector ser poderoso, é importante nota que nem todos os exemplos serão perfeitos. Melhorias futuras podem incluir adição de filtros e limites para excluir resultados menos relevantes, assegurando que apenas os exemplos mais apropriados sejam fornecidos ao LLM.
Para acessar a performance do prompt e da geração de consultas SQL, precisamos definir e rodar uma série de testes. O objetivo é avaliar se a LLM gera de maneira ótima as consultas SQL baseadas em inputs de usuários e sem o uso dos few shots baseados em exemplos.
Nós começamos definindo uma função que usa LLM para gerar consultas SQL baseadas no contexto providenciado, prompt, entrada do usuários e outros parâmetros:
def get_sql_from_text(context, prompt, user_input, use_few_shots, tables_vector_store, table_df, example_selector=None, example_prompt=None):
relevant_tables = get_relevant_tables(user_input, tables_vector_store, table_df)
context["table_info"] = "\n\n".join(relevant_tables)
examples = example_selector.select_examples({"input": user_input}) if example_selector else []
context["examples_value"] = "\n\n".join([
example_prompt.invoke(x).to_string() for x in examples
])
model = ChatOpenAI(model="gpt-3.5-turbo", temperature=0)
output_parser = StrOutputParser()
chain_model = prompt | model | output_parser
response = chain_model.invoke({
"top_k": context["top_k"],
"table_info": context["table_info"],
"examples_value": context["examples_value"],
"input": user_input
})
return response
Teste o prompt com e sem exemplos:
# Prompt execution **with** few shots
input = "Busque todos os eventos em 2010 informando o Event Id e date, location city e state, aircraft manufacturer e model."
response_with_few_shots = get_sql_from_text(
context,
prompt,
user_input=input,
use_few_shots=True,
tables_vector_store=tables_vector_store,
table_df=table_df,
example_selector=example_selector,
example_prompt=example_prompt,
)
print(response_with_few_shots)
SELECT e.EventId, e.EventDate, e.LocationCity, e.LocationState, a.AircraftManufacturer, a.AircraftModel
FROM Aviation.Event e
JOIN Aviation.Aircraft a ON e.EventId = a.EventId
WHERE Year(e.EventDate) = 2010
# Prompt execution **without** few shots
input = "Busque todos os eventos em 2010 informando o Event Id e date, location city e state, aircraft manufacturer e model."
response_with_no_few_shots = get_sql_from_text(
context,
prompt,
user_input=input,
use_few_shots=False,
tables_vector_store=tables_vector_store,
table_df=table_df,
)
print(response_with_no_few_shots)
SELECT TOP 3 "EventId", "EventDate", "LocationCity", "LocationState", "AircraftManufacturer", "AircraftModel"
FROM Aviation.Event e
JOIN Aviation.Aircraft a ON e.ID = a.Event
WHERE e.EventDate >= '2010-01-01' AND e.EventDate < '2011-01-01'
Utility Functions for Testing
Para testar as queries geradas por SQL, definimos algumas funções úteis:
def execute_sql_query(cnx, query):
try:
cursor = cnx.cursor()
cursor.execute(query)
rows = cursor.fetchall()
return rows
except:
print('Error running query:')
print(query)
print('-'*80)
return None
def sql_result_equals(cnx, query, expected):
rows = execute_sql_query(cnx, query)
result = [set(row._asdict().values()) for row in rows or []]
if result != expected and rows is not None:
print('Result not as expected for query:')
print(query)
print('-'*80)
return result == expected
# SQL test for prompt **with** few shots
print("SQL is OK" if not execute_sql_query(cnx, response_with_few_shots) is None else "SQL is not OK")
SQL is OK
# SQL test for prompt **without** few shots
print("SQL is OK" if not execute_sql_query(cnx, response_with_no_few_shots) is None else "SQL is not OK")
error on running query:
SELECT TOP 3 "EventId", "EventDate", "LocationCity", "LocationState", "AircraftManufacturer", "AircraftModel"
FROM Aviation.Event e
JOIN Aviation.Aircraft a ON e.ID = a.Event
WHERE e.EventDate >= '2010-01-01' AND e.EventDate < '2011-01-01'
--------------------------------------------------------------------------------
SQL is not OK
Defina um conjunto de casos de teste e rode eles:
tests = [{
"input": "Quais são os 3 anos com mais eventos gravados?",
"expected": [{128, 2003}, {122, 2007}, {117, 2005}]
},{
"input": "Quantos incidentes envolveram a aircraft Boeing.",
"expected": [{5}]
},{
"input": "Quantos incidentes resultaram em fatalidades.",
"expected": [{237}]
},{
"input": "Liste o event Id e date e, crew number, age e gender para incidents que ocorreram em 2013.",
"expected": [{1, datetime.datetime(2013, 3, 4, 11, 6), '20130305X71252', 59, 'M'},
{1, datetime.datetime(2013, 1, 1, 15, 0), '20130101X94035', 32, 'M'},
{2, datetime.datetime(2013, 1, 1, 15, 0), '20130101X94035', 35, 'M'},
{1, datetime.datetime(2013, 1, 12, 15, 0), '20130113X42535', 25, 'M'},
{2, datetime.datetime(2013, 1, 12, 15, 0), '20130113X42535', 34, 'M'},
{1, datetime.datetime(2013, 2, 1, 15, 0), '20130203X53401', 29, 'M'},
{1, datetime.datetime(2013, 2, 15, 15, 0), '20130218X70747', 27, 'M'},
{1, datetime.datetime(2013, 3, 2, 15, 0), '20130303X21011', 49, 'M'},
{1, datetime.datetime(2013, 3, 23, 13, 52), '20130326X85150', 'M', None}]
},{
"input": "Ache o total de incidents ocorridos em United States.",
"expected": [{1178}]
},{
"input": "Liste todos as coordenadas de latitude e longitude de incidentes que resultaram em mais de 5 feridos em 2010.",
"expected": [{-78.76833333333333, 43.25277777777778}]
},{
"input": "Busque todos os incidentes em 2010 informando o Event Id e date, location city e state, aircraft manufacturer e model.",
"expected": [
{datetime.datetime(2010, 5, 20, 13, 43), '20100520X60222', 'CIRRUS DESIGN CORP', 'Farmingdale', 'New York', 'SR22'},
{datetime.datetime(2010, 4, 11, 15, 0), '20100411X73253', 'CZECH AIRCRAFT WORKS SPOL SRO', 'Millbrook', 'New York', 'SPORTCRUISER'},
{'108', datetime.datetime(2010, 1, 9, 12, 55), '20100111X41106', 'Bayport', 'New York', 'STINSON'},
{datetime.datetime(2010, 8, 1, 14, 20), '20100801X85218', 'A185F', 'CESSNA', 'New York', 'Newfane'}
]
}]
Rode os testes e calcule a precisão:
def execute_tests(cnx, context, prompt, use_few_shots, tables_vector_store, table_df, example_selector, example_prompt):
tests_generated_sql = [(x, get_sql_from_text(
context,
prompt,
user_input=x['input'],
use_few_shots=use_few_shots,
tables_vector_store=tables_vector_store,
table_df=table_df,
example_selector=example_selector if use_few_shots else None,
example_prompt=example_prompt if use_few_shots else None,
)) for x in deepcopy(tests)]
tests_sql_executions = [(x[0], sql_result_equals(cnx, x[1], x[0]['expected']))
for x in tests_generated_sql]
accuracy = sum(1 for i in tests_sql_executions if i[1] == True) / len(tests_sql_executions)
print(f'Accuracy: {accuracy}')
print('-'*80)
# Testes de precisão para prompts executados **sem** few shots
use_few_shots = False
execute_tests(
cnx,
context,
prompt,
use_few_shots,
tables_vector_store,
table_df,
example_selector,
example_prompt
)
error on running query:
SELECT "EventDate", COUNT("EventId") as "TotalEvents"
FROM Aviation.Event
GROUP BY "EventDate"
ORDER BY "TotalEvents" DESC
TOP 3;
--------------------------------------------------------------------------------
error on running query:
SELECT "EventId", "EventDate", "C"."CrewNumber", "C"."Age", "C"."Sex"
FROM "Aviation.Event" AS "E"
JOIN "Aviation.Crew" AS "C" ON "E"."ID" = "C"."EventId"
WHERE "E"."EventDate" >= '2013-01-01' AND "E"."EventDate" < '2014-01-01'
--------------------------------------------------------------------------------
result not expected for query:
SELECT TOP 3 "e"."EventId", "e"."EventDate", "e"."LocationCity", "e"."LocationState", "a"."AircraftManufacturer", "a"."AircraftModel"
FROM "Aviation"."Event" AS "e"
JOIN "Aviation"."Aircraft" AS "a" ON "e"."ID" = "a"."Event"
WHERE "e"."EventDate" >= '2010-01-01' AND "e"."EventDate" < '2011-01-01'
--------------------------------------------------------------------------------
accuracy: 0.5714285714285714
--------------------------------------------------------------------------------
# Testes de precisão para prompts executados **com** few shots
use_few_shots = True
execute_tests(
cnx,
context,
prompt,
use_few_shots,
tables_vector_store,
table_df,
example_selector,
example_prompt
)
error on running query:
SELECT e.EventId, e.EventDate, e.LocationCity, e.LocationState, a.AircraftManufacturer, a.AircraftModel
FROM Aviation.Event e
JOIN Aviation.Aircraft a ON e.EventId = a.EventId
WHERE Year(e.EventDate) = 2010 TOP 3
--------------------------------------------------------------------------------
accuracy: 0.8571428571428571
--------------------------------------------------------------------------------
A acurácia das consultas SQL geradas com exemplos (few shots) é aproximadamente 49% maior se comparada àquelas geradas sem exemplos (85% vs. 57%).
Olá a todos!
Trabalho há muitos anos com Excel e, ultimamente, foquei no tratamento de bases de dados.
Realmente minha experiencia com Excel foi para trabalhos financeiros, não tanto analíticos de dados em si, mas em um projeto recente pude trabalhar muito com SQL e me interessei um pouco pelo tema (não sou nenhuma uma especialista, já aviso!)
Me perguntei como poderia unir vários excels em um para, por exemplo, entregá-lo à Análise de dados utilizando a tecnologia InterSystems. Juntei toda a informação em um pequeno artigo. Espero que seja útil e, é claro, estou aberta a correções.
Para desabilitar esse timeout, defina o timeout da query como desabilitado nas configurações de DSN (Data Source Name - Nome da fonte de dados):
Painel de controle Windows > Ferramentas de Administração > Fontes de Dados (ODBC) > Configuração de DSN do sistema
Se você marcar "Desabiltar o timeout de query", o timeout será desabilitado.
Se quiser mudar isso no lado da aplicação, você pode definir no nível da ODBC API.
Defina o atributo SQL_ATTR_QUERY_TIMEOUT quando chamar a função ODBC SQLSetStmtAttr antes de conectar à fonte de dados.
Olá Comunidade,
Esse é um passo a passo detalhado e claro sobre a plataforma IRIS AI Studio. Eu penso alto enquanto tento diferentes exemplos, alguns dos quais falham em devolver os resultados esperados - o que eu acho que é uma necessidade para que um plataforma desse tipo explore diferentes modelos, configurações e limitações. Esse vídeo será útil se está interessado em como construir o "Chat with PDF" (conversa com PDF) ou sistema de recomendações de dados usando a IRIS DB (DataBase - base de dados) e modelos LLM (Large Language Model - grande modelo de lingugagem).
No artigo anterior, vimos detalhes a respeito dos conectores, que permitem que o usuário carregue o arquivo, o converta para incorporações e armazene na IRIS DB. Nesse artigo, vamos explorar opções diferentes de recuperações que o Studio IRIS AI oferece: Semantic Search (pesquisa semântica), Chat (conversa), Recommender (recomendação) e Similarity (similaridade).
DNA Similarity and Classification é uma API REST utilizando a tecnologia InterSystems Vector Search para investigar semelhanças genéticas e classificar eficientemente sequências de DNA. Este é um aplicativo que utiliza técnicas de inteligência artificial, como aprendizado de máquina, aprimorado por recursos de pesquisa vetorial, para classificar famílias genéticas e identificar DNAs semelhantes conhecidos a partir de um DNA de entrada desconhecido.
Análise K-mer: Fundamentos em Análise de Sequência de DNA
O nosso sistema funciona normalmente com a versão Iris 2023.2, porém foi necessário mudarmos de serviço de servidor cloud e ao migrar o banco para esse novo servidor e subir nossa aplicação começamos a receber alguns erros, que seguem abaixo:
Temos o prazer de anunciar o lançamento de uma nova versão da extensão SQLTools VS Code.
SQLTools conecta usuários do VS Code aos bancos de dados mais usados usando drivers, incluindo InterSystems IRIS. Com mais de 3,5 milhões de downloads, ajuda os usuários a trabalhar com seus dados com muito mais facilidade.
Temos um delicioso conjunto de dados com receitas escritas por vários usuários do Reddit, porém, a maioria das informações é texto livre, como o título ou a descrição de um post. Vamos descobrir como carregar o conjunto de dados facilmente, extrair algumas características e analisá-las usando recursos do modelo de linguagem grande da OpenAI no Embedded Python e no framework Langchain.
Primeiro de tudo, precisamos carregar o conjunto de dados ou podemos só nos conectar a ele?
Há diferentes maneiras de alcançar isso: por exemplo, é possível usar o CSV Record Mapper em uma produção de interoperabilidade ou até em aplicativos legais do OpenExchange, como csvgen.
Vamos usar o Foreign Tables. Um recurso bastante útil para projetar dados armazenados fisicamente em outro lugar no IRIS SQL. Podemos usar isso para ter uma primeira visão dos arquivos do conjunto de dados.
Criamos um Foreign Server:
CREATE FOREIGN SERVER dataset FOREIGN DATA WRAPPER CSV HOST '/app/data/'
E, em seguida, uma Foreign Table que se conecta ao arquivo CSV:
CREATE FOREIGN TABLE dataset.Recipes (
CREATEDDATE DATE,
NUMCOMMENTS INTEGER,
TITLE VARCHAR,
USERNAME VARCHAR,
COMMENT VARCHAR,
NUMCHAR INTEGER
) SERVER dataset FILE 'Recipes.csv' USING
{
"from": {
"file": {
"skip": 1
}
}
}
É isso, imediatamente podemos executar consultas SQL em "dataset.Recipes":
## Quais dados são necessários? O conjunto de dados é interessante e estamos com fome. No entanto, se quisermos decidir uma receita para cozinhar, será preciso mais algumas informações que possamos usar para análise.
Vamos trabalhar com duas classes persistentes (tabelas):
Podemos agora carregar nossas tabelas "yummy.data*" com o conteúdo do conjunto de dados:
do ##class(yummy.Utils).LoadDataset()
Parece bom, mas ainda precisamos descobrir como vamos gerar os dados para os campos Score, Difficulty, Ingredients, PreparationTime e CuisineType.
## Analise as receitas Queremos processar cada título e descrição de receita e:
Vamos usar o seguinte:
LLM (modelos de linguagem grande) são realmente uma ótima ferramenta para processar linguagem natural.
LangChain está pronto para uso no Python, então podemos usá-lo diretamente no InterSystems IRIS usando o Embedded Python.
A classe "SimpleOpenAI" completa fica assim:
/// Análise simples da OpenAI para receitas
Class yummy.analysis.SimpleOpenAI Extends Analysis
{
Property CuisineType As %String;
Property PreparationTime As %Integer;
Property Difficulty As %String;
Property Ingredients As %String;
/// Execute
/// Você pode tentar isto a partir de um terminal:
/// set a = ##class(yummy.analysis.SimpleOpenAI).%New(##class(yummy.data.Recipe).%OpenId(8))
/// do a.Run()
/// zwrite a
Method Run()
{
try {
do ..RunPythonAnalysis()
set reasons = ""
// meus tipos de culinária favoritos
if "spanish,french,portuguese,italian,korean,japanese"[..CuisineType {
set ..Score = ..Score + 2
set reasons = reasons_$lb("It seems to be a "_..CuisineType_" recipe!")
}
// não quero passar o dia todo cozinhando :)
if (+..PreparationTime < 120) {
set ..Score = ..Score + 1
set reasons = reasons_$lb("You don't need too much time to prepare it")
}
// bônus para ingredientes favoritos!
set favIngredients = $listbuild("kimchi", "truffle", "squid")
for i=1:1:$listlength(favIngredients) {
set favIngred = $listget(favIngredients, i)
if ..Ingredients[favIngred {
set ..Score = ..Score + 1
set reasons = reasons_$lb("Favourite ingredient found: "_favIngred)
}
}
set ..Reason = $listtostring(reasons, ". ")
} catch ex {
throw ex
}
}
/// Atualize a receita com o resultado da análise
Method UpdateRecipe()
{
try {
// chame a implementação de classe mãe primeiro
do ##super()
// adicione resultados de análises da OpenAI específicos
set ..Recipe.Ingredients = ..Ingredients
set ..Recipe.PreparationTime = ..PreparationTime
set ..Recipe.Difficulty = ..Difficulty
set ..Recipe.CuisineType = ..CuisineType
} catch ex {
throw ex
}
}
/// Execute a análise usando o embedded Python + Langchain
/// do ##class(yummy.analysis.SimpleOpenAI).%New(##class(yummy.data.Recipe).%OpenId(8)).RunPythonAnalysis(1)
Method RunPythonAnalysis(debug As %Boolean = 0) [ Language = python ]
{
# load OpenAI APIKEY from env
import os
from dotenv import load_dotenv, find_dotenv
_ = load_dotenv('/app/.env')
# account for deprecation of LLM model
import datetime
current_date = datetime.datetime.now().date()
# date after which the model should be set to "gpt-3.5-turbo"
target_date = datetime.date(2024, 6, 12)
# set the model depending on the current date
if current_date > target_date:
llm_model = "gpt-3.5-turbo"
else:
llm_model = "gpt-3.5-turbo-0301"
from langchain.chat_models import ChatOpenAI
from langchain.prompts import ChatPromptTemplate
from langchain.chains import LLMChain
from langchain.output_parsers import ResponseSchema
from langchain.output_parsers import ResponseSchema
# init llm model
llm = ChatOpenAI(temperature=0.0, model=llm_model)
# prepare the responses we need
cuisine_type_schema = ResponseSchema(
name="cuisine_type",
description="What is the cuisine type for the recipe? \
Answer in 1 word max in lowercase"
)
preparation_time_schema = ResponseSchema(
name="preparation_time",
description="How much time in minutes do I need to prepare the recipe?\
Anwer with an integer number, or null if unknown",
type="integer",
)
difficulty_schema = ResponseSchema(
name="difficulty",
description="How difficult is this recipe?\
Answer with one of these values: easy, normal, hard, very-hard"
)
ingredients_schema = ResponseSchema(
name="ingredients",
description="Give me a comma separated list of ingredients in lowercase or empty if unknown"
)
response_schemas = [cuisine_type_schema, preparation_time_schema, difficulty_schema, ingredients_schema]
# get format instructions from responses
output_parser = StructuredOutputParser.from_response_schemas(response_schemas)
format_instructions = output_parser.get_format_instructions()
analysis_template = """\
Interprete and evaluate a recipe which title is: {title}
and the description is: {description}
{format_instructions}
"""
prompt = ChatPromptTemplate.from_template(template=analysis_template)
messages = prompt.format_messages(title=self.Recipe.Title, description=self.Recipe.Description, format_instructions=format_instructions)
response = llm(messages)
if debug:
print("======ACTUAL PROMPT")
print(messages[0].content)
print("======RESPONSE")
print(response.content)
# populate analysis with results
output_dict = output_parser.parse(response.content)
self.CuisineType = output_dict['cuisine_type']
self.Difficulty = output_dict['difficulty']
self.Ingredients = output_dict['ingredients']
if type(output_dict['preparation_time']) == int:
self.PreparationTime = output_dict['preparation_time']
return 1
}
}
O método "RunPythonAnalysis" é onde tudo relacionado a OpenAI acontece :). Você pode executá-lo diretamente no terminal para uma receita específica:
do ##class(yummy.analysis.SimpleOpenAI).%New(##class(yummy.data.Recipe).%OpenId(12)).RunPythonAnalysis(1)
Vamos obter uma saída assim:
USER>do ##class(yummy.analysis.SimpleOpenAI).%New(##class(yummy.data.Recipe).%OpenId(12)).RunPythonAnalysis(1)
======ACTUAL PROMPT
Interprete and evaluate a recipe which title is: Folded Sushi - Alaska Roll
and the description is: Craving for some sushi but don't have a sushi roller? Try this easy version instead. It's super easy yet equally delicious!
[Video Recipe](https://www.youtube.com/watch?v=1LJPS1lOHSM)
# Ingredients
Serving Size: \~5 sandwiches
* 1 cup of sushi rice
* 3/4 cups + 2 1/2 tbsp of water
* A small piece of konbu (kelp)
* 2 tbsp of rice vinegar
* 1 tbsp of sugar
* 1 tsp of salt
* 2 avocado
* 6 imitation crab sticks
* 2 tbsp of Japanese mayo
* 1/2 lb of salmon
# Recipe
* Place 1 cup of sushi rice into a mixing bowl and wash the rice at least 2 times or until the water becomes clear. Then transfer the rice into the rice cooker and add a small piece of kelp along with 3/4 cups plus 2 1/2 tbsp of water. Cook according to your rice cookers instruction.
* Combine 2 tbsp rice vinegar, 1 tbsp sugar, and 1 tsp salt in a medium bowl. Mix until everything is well combined.
* After the rice is cooked, remove the kelp and immediately scoop all the rice into the medium bowl with the vinegar and mix it well using the rice spatula. Make sure to use the cut motion to mix the rice to avoid mashing them. After thats done, cover it with a kitchen towel and let it cool down to room temperature.
* Cut the top of 1 avocado, then slice into the center of the avocado and rotate it along your knife. Then take each half of the avocado and twist. Afterward, take the side with the pit and carefully chop into the pit and twist to remove it. Then, using your hand, remove the peel. Repeat these steps with the other avocado. Dont forget to clean up your work station to give yourself more space. Then, place each half of the avocado facing down and thinly slice them. Once theyre sliced, slowly spread them out. Once thats done, set it aside.
* Remove the wrapper from each crab stick. Then, using your hand, peel the crab sticks vertically to get strings of crab sticks. Once all the crab sticks are peeled, rotate them sideways and chop them into small pieces, then place them in a bowl along with 2 tbsp of Japanese mayo and mix until everything is well mixed.
* Place a sharp knife at an angle and thinly slice against the grain. The thickness of the cut depends on your preference. Just make sure that all the pieces are similar in thickness.
* Grab a piece of seaweed wrap. Using a kitchen scissor, start cutting at the halfway point of seaweed wrap and cut until youre a little bit past the center of the piece. Rotate the piece vertically and start building. Dip your hand in some water to help with the sushi rice. Take a handful of sushi rice and spread it around the upper left hand quadrant of the seaweed wrap. Then carefully place a couple slices of salmon on the top right quadrant. Then place a couple slices of avocado on the bottom right quadrant. And finish it off with a couple of tsp of crab salad on the bottom left quadrant. Then, fold the top right quadrant into the bottom right quadrant, then continue by folding it into the bottom left quadrant. Well finish off the folding by folding the top left quadrant onto the rest of the sandwich. Afterward, place a piece of plastic wrap on top, cut it half, add a couple pieces of ginger and wasabi, and there you have it.
A saída deve ser um fragmento de código markdown formatado no seguinte esquema, incluindo o "```json" e "```" à esquerda e à direita:
json
{
"cuisine_type": string // What is the cuisine type for the recipe? Answer in 1 word max in lowercase
"preparation_time": integer // How much time in minutes do I need to prepare the recipe? Anwer with an integer number, or null if unknown
"difficulty": string // How difficult is this recipe? Answer with one of these values: easy, normal, hard, very-hard
"ingredients": string // Give me a comma separated list of ingredients in lowercase or empty if unknown
}
======RESPONSE
json
{
"cuisine_type": "japanese",
"preparation_time": 30,
"difficulty": "easy",
"ingredients": "sushi rice, water, konbu, rice vinegar, sugar, salt, avocado, imitation crab sticks, japanese mayo, salmon"
}
Tudo bem. Parece que nosso prompt da OpenAI é capaz de retornar algumas informações úteis. Vamos executar toda a classe da análise no terminal:
set a = ##class(yummy.analysis.SimpleOpenAI).%New(##class(yummy.data.Recipe).%OpenId(12))
do a.Run()
zwrite a
USER>zwrite a
a=37@yummy.analysis.SimpleOpenAI ; <OREF>
+----------------- general information ---------------
| oref value: 37
| class name: yummy.analysis.SimpleOpenAI
| reference count: 2
+----------------- attribute values ------------------
| CuisineType = "japanese"
| Difficulty = "easy"
| Ingredients = "sushi rice, water, konbu, rice vinegar, sugar, salt, avocado, imitation crab sticks, japanese mayo, salmon"
| PreparationTime = 30
| Reason = "It seems to be a japanese recipe!. You don't need too much time to prepare it"
| Score = 3
+----------------- swizzled references ---------------
| i%Recipe = ""
| r%Recipe = "30@yummy.data.Recipe"
+-----------------------------------------------------
## Analisando todas as receitas! Naturalmente, você gostaria de executar a análise em todas as receitas que carregamos.
Você pode analisar uma variedade de IDs de receitas desta forma:
USER>do ##class(yummy.Utils).AnalyzeRange(1,10)
> Recipe 1 (1.755185s)
> Recipe 2 (2.559526s)
> Recipe 3 (1.556895s)
> Recipe 4 (1.720246s)
> Recipe 5 (1.689123s)
> Recipe 6 (2.404745s)
> Recipe 7 (1.538208s)
> Recipe 8 (1.33001s)
> Recipe 9 (1.49972s)
> Recipe 10 (1.425612s)
Depois disso, verifique novamente sua tabela de receitas e confira os resultados
select * from yummy_data.Recipe
Acho que eu poderia tentar a pizza de abóbora ou o tofu com kimchi coreano e porco :). Vou precisar conferir novamente em casa de qualquer forma :)
Encontre o exemplo completo em https://github.com/isc-afuentes/recipe-inspector
Com esse exemplo simples, aprendemos a usar as técnicas de LLM para adicionar recursos ou analisar partes dos seus dados no InterSystems IRIS.
Com esse ponto de partida, você pode pensar em:
O que mais você pode tentar? Me diga o que você acha!
Você sabia que pode obter dados JSON diretamente das suas tabelas SQL?
Vou apresentar duas funções SQL úteis que são usadas para recuperar dados JSON de consultas SQL - JSON_ARRAY e JSON_OBJECT.
Você pode usar essas funções na instrução SELECT com outros tipos de itens select, e elas podem ser especificadas em outros locais onde uma função SQL pode ser usada, como em uma cláusula WHERE
A função JSON_ARRAY aceita uma lista separada por vírgula de expressões e retorna um array JSON com esses valores.
SELECT TOP 3Name,
JSON_ARRAY(%ID,%TABLENAME,UCASE(Name),Age,Home_State) "JSON data"
FROM Sample.PersonSELECT TOP 3Name,
JSON_ARRAY(%ID,%TABLENAME,UCASE(Name),Age,Home_State ABSENT ONNULL
) "JSON data"
FROM Sample.Person.png)
Para obter essas propriedades de valores nulos, use "NULL ON NULL" em vez disso (e, como isso é o padrão, você pode só não o adicionar como no primeiro exemplo).
Imagem obtendo todos os telefones de pacientes/clientes em um array JSON sem construi-lo:
SELECT JSON_ARRAY(HomePhone,WorkPhone,Mobile) "JSON data"FROM Test.Phones["055-8263371","052-4957286","054-4951066"]A função JSON_OBJECT aceita uma lista separada por vírgulas de pares de chave:valor e retorna um objeto JSON com esses valores.
Você pode especificar qualquer string entre aspas simples como um nome de chave, mas observe que isso não impõe nenhuma convenção de nomenclatura ou verificação de exclusividade para nomes de chaves.
Para o valor, você pode especificar um nome de coluna ou outra expressão. Além disso, observe que ele não aceita a sintaxe de asterisco (*) como uma forma de especificar todos os campos de uma tabela.
SELECT TOP 3 JSON_OBJECT('title':'Person from’,'country’:UCASE(Home_State),'table':%TABLENAME,'name':Name,'id':%ID,'age':Age) "JSON data"
FROM Sample.Person.png)
Para omitir as propriedades de valores nulos, basta adicionar "ABSENT ON NULL" à função:
SELECT TOP 3 JSON_OBJECT('title':'Person from’,'country’:UCASE(Home_State),'table':%TABLENAME,'name':Name,'id':%ID,'age':Age ABSENT ONNULL) "JSON data"
FROM Sample.Person.png)
Para obter essas propriedades de valores nulos, use "NULL ON NULL" em vez disso (e, como isso é o padrão, você pode só não o adicionar).
Atualmente, há bastante conversa sobre o LLM, a IA etc. Os bancos de dados vetoriais fazem um pouco parte disso, e já há várias realizações diferentes para o suporte no mundo fora do IRIS.
Por que o vetor?
E vários outros motivos.
Então, para este concurso de python, decidi tentar implementar esse suporte. Infelizmente, não conseguir terminar a tempo, e explicarei abaixo porquê.
Algumas coisas importantes precisam ser feitas para que fique completo
createtable items(embedding vector(3));
insertinto items (embedding) values ('[1,2,3]');
insertinto items (embedding) values ('[4,5,6]');
-- Euclidean distanceselect embedding, vector.l2_distance(embedding, '[9,8,7]') distance from items orderby distance;
-- Cosine similarityselect embedding, vector.cosine_distance(embedding, '[9,8,7]') distance from items orderby distance;
-- Inner productselect embedding, -vector.inner_product(embedding, '[9,8,7]') distance from items orderby distance;
.png)
O vetor deve ser um array de valores numéricos, que podem ser inteiros ou floats, bem como assinados ou não. No IRIS, podemos armazená-lo simplesmente como $listbuild, ele tem uma boa representação, já é compatível, só é preciso implementar a conversão de ODBC para lógica.
Em seguida, os valores podem ser inseridos como texto simples usando drivers externos, como ODBC/JDBC ou somente dentro do IRIS com ObjectScript
insertinto items (embedding) values ('[1,2,3]');set rs = ##class(%SQL.Statement).%ExecDirect(, "insert into test.items (embedding) values ('[1,2,3]')")
set rs = ##class(%SQL.Statement).%ExecDirect(, "insert into test.items (embedding) values (?)", $listbuild(2,3,4))
&sql(insertinto test.items (embedding) values ('[1,2,3]'))
set val = $listbuild(2,3,4)
&sql(insertinto test.items (embedding) values (:val))
Sempre será armazenado como $lb() e retornado em formato textual em ODBC
Em seguida, descobri que o JDBC usa Inserções Rápidas por padrão. Nesse caso, ele armazena os dados inseridos diretamente nos globais, então precisei desativá-lo manualmente
No DBeaver, selecione optfastSelect no campo FeatureOption
.png)
Os vetores são especialmente necessários para o suporte ao cálculo da distância entre dois vetores
Para o concurso, eu precisava usar o Embedded Python, e este é o problema: como operar com $lb no Embedded Python. Há um método ToList em %SYS.Class, mas ele não é integrado ao pacote Python do iris e precisa ser chamado com ObjectScript
ClassMethod l2DistancePy(v1 As dc.vector.type, v2 As dc.vector.type) As%Decimal(SCALE=10) [ Language = python, SqlName = l2_distance_py, SqlProc ]
{
import iris
import math
vector_type = iris.cls('dc.vector.type')
v1 = iris.cls('<span class="hljs-built_in">%SYS.Python</span>').ToList(vector_type.Normalize(v1))
v2 = iris.cls('<span class="hljs-built_in">%SYS.Python</span>').ToList(vector_type.Normalize(v2))
<span class="hljs-keyword">return</span> math.sqrt(sum([(val1 - val2) ** <span class="hljs-number">2</span> <span class="hljs-keyword">for</span> val1, val2 in zip(v1, v2)]))
}
Não parece nada certo. Eu preferiria se $lb pudesse ser interpretado on the fly como lista no python ou nas funções builtin de lista to_list e from_list
Outro problema é quando tento testar essa função de diferentes maneiras. Usando o SQL do Embedded Python que utiliza a Função SQL escrita no Embedded Python, ele falha. Então, também precisei adicionar as funções do ObjectScript.
ModuleNotFoundError: No module named 'dc' SQL Function VECTOR.NORM_PY failed with error: SQLCODE=-400,%msg=ERROR #5002: ObjectScript error: <OBJECT DISPATCH>%0AmBm3l0tudf^%sqlcq.USER.cls37.1 *python object not found
Funções implementadas no momento para calcular a distância, em Python e ObjectScript
[SQL]_system@localhost:USER> select embedding, vector.l2_distance_py(embedding, '[9,8,7]') distance from items orderby distance;
+-----------+----------------------+
| embedding | distance |
+-----------+----------------------+
| [4,5,6] | 5.91607978309961613 |
| [1,2,3] | 10.77032961426900748 |
+-----------+----------------------+
2 rows in setTime: 0.011s
[SQL]_system@localhost:USER> select embedding, vector.l2_distance(embedding, '[9,8,7]') distance from items orderby distance;
+-----------+----------------------+
| embedding | distance |
+-----------+----------------------+
| [4,5,6] | 5.916079783099616045 |
| [1,2,3] | 10.77032961426900807 |
+-----------+----------------------+
2 rows in setTime: 0.012s[SQL]_system@localhost:USER> select embedding, vector.cosine_distance(embedding, '[9,8,7]') distance from items orderby distance;
+-----------+---------------------+
| embedding | distance |
+-----------+---------------------+
| [4,5,6] | .034536677566264152 |
| [1,2,3] | .11734101007866331 |
+-----------+---------------------+
2 rows in setTime: 0.034s
[SQL]_system@localhost:USER> select embedding, vector.cosine_distance_py(embedding, '[9,8,7]') distance from items orderby distance;
+-----------+-----------------------+
| embedding | distance |
+-----------+-----------------------+
| [4,5,6] | .03453667756626421781 |
| [1,2,3] | .1173410100786632659 |
+-----------+-----------------------+
2 rows in setTime: 0.025s[SQL]_system@localhost:USER> select embedding, vector.inner_product_py(embedding, '[9,8,7]') distance from items orderby distance;
+-----------+----------+
| embedding | distance |
+-----------+----------+
| [1,2,3] | 46 |
| [4,5,6] | 118 |
+-----------+----------+
2 rows in setTime: 0.035s
[SQL]_system@localhost:USER> select embedding, vector.inner_product(embedding, '[9,8,7]') distance from items orderby distance;
+-----------+----------+
| embedding | distance |
+-----------+----------+
| [1,2,3] | 46 |
| [4,5,6] | 118 |
+-----------+----------+
2 rows in setTime: 0.032sAlém disso, as funções matemáticas implementadas, ad, sub, div e mult. que a InterSystems aceita criam as próprias funções agregadas. Assim, seria possível somar todos os vetores ou encontrar a média. Mas, infelizmente, a InterSystems não oferece suporte ao uso do mesmo nome e precisa de um nome exclusivo (e esquema) para funcionar. No entanto, o resultado não numérico da função agregada não é compatível
Função vector_add simples, que retorna a soma de dois vetores
.png)
Quando usada como uma agregação, ela mostra 0, e o vetor esperado também
.png)
Não consegui concluir essa parte devido a alguns obstáculos que tive durante a realização.
Abri 11 issues com o Embedded Python que encontrei durante o trabalho, então a maior parte do tempo procurei alternativas para solucionar problemas. Com a ajuda do projeto iris-dollar-listde @Guillaume Rongier, consegui resolver alguns problemas.
De qualquer maneira, ainda está disponível e pode ser instalado com IPM, até mesmo usado com funcionalidade limitada
zpm "install vector"Ou no modo de desenvolvimento com o docker-compose
git clone https://github.com/caretdev/iris-vector.git
cd iris-vector
docker-compose up -dOlá Comunidade,
Reproduza o video no InterSystems Developers YouTube:
Deixe-me apresentar meu novo projeto, que é o irissqlcli, um REPL (Read-Eval-Print Loop) para o InterSystems IRIS SQL
Instale com o pip
pipinstallirissqlcliOu execute com o docker
dockerrun-itcaretdev/irissqlcliirissqlcliiris://_SYSTEM:SYS@host.docker.internal:1972/USERConecte ao IRIS
$ irissqlcli iris://_SYSTEM@localhost:1972/USER -W
Password for _SYSTEM:
Server: InterSystems IRIS Version 2022.3.0.606 xDBC Protocol Version 65
Version: 0.1.0
[SQL]_SYSTEM@localhost:USER> select $ZVERSION
+---------------------------------------------------------------------------------------------------------+
| Expression_1 |
+---------------------------------------------------------------------------------------------------------+
| IRIS for UNIX (Ubuntu Server LTS for ARM64 Containers) 2022.3 (Build 606U) Mon Jan 30202309:05:12 EST |
+---------------------------------------------------------------------------------------------------------+
1 row in set
Time: 0.063s
[SQL]_SYSTEM@localhost:USER> help
+----------+-------------------+------------------------------------------------------------+
| Command | Shortcut | Description |
+----------+-------------------+------------------------------------------------------------+
| .exit | \q | Exit. |
| .mode | \T | Change the table format used to output results. |
| .once | \o [-o] filename | Append next result to an output file (overwrite using -o). |
| .schemas | \ds | List schemas. |
| .tables | \dt [schema] | List tables. |
| \e | \e | Edit command with editor (uses $EDITOR). |
| help | \? | Show this help. |
| nopager | \n | Disable pager, print to stdout. |
| notee | notee | Stop writing results to an output file. |
| pager | \P [command] | Set PAGER. Print the query results via PAGER. |
| prompt | \R | Change prompt format. |
| quit | \q | Quit. |
| tee | tee [-o] filename | Append all results to an output file (overwrite using -o). |
+----------+-------------------+------------------------------------------------------------+
Time: 0.012s
[SQL]_SYSTEM@localhost:USER>$ irissqlcli--helpUsage: irissqlcli[OPTIONS][URI][USERNAME]Options:
-h, --hostTEXTHostaddressoftheIRISinstance.
-p, --portINTEGERPortnumberatwhichtheIRISinstanceislistening.
-U, --usernameTEXTUsernametoconnecttotheIRISinstance.
-u, --userTEXTUsernametoconnecttotheIRISinstance.
-W, --passwordForcepasswordprompt.
-v, --versionVersionofirissqlcli.
-n, --nspaceTEXTnamespacenametoconnectto.
-q, --quietQuietmode, skipintroonstartupandgoodbyeonexit.
-l, --logfileFILENAMELogeveryqueryanditsresultstoafile.
--irissqlclircFILELocationofirissqlclircfile.
--auto-vertical-outputAutomaticallyswitchtoverticaloutputmodeiftheresultiswiderthantheterminalwidth.
--row-limitINTEGERSetthresholdforrowlimitprompt. Use 0 todisableprompt.
-t, --tableDisplaybatchoutputintableformat.
--csvDisplaybatchoutputinCSVformat.
--warn / --no-warnWarnbeforerunningadestructivequery.
-e, --executeTEXTExecutecommandandquit.
--helpShowthismessageandexit.
ou no modo Python Embedded (%Service_CallIn precisa estar ativada)
$ irissqlcliiris+emb:///USERServer: IRISforUNIX (UbuntuServerLTSforARM64Containers) 2022.2 (Build 368U) FriOct 21 2022 16:39:41EDTVersion: 0.1.0[SQL]irisowner@/usr/irissys/:USER>
Ele é compatível com o stdin, então você pode criar um pipe de um arquivo SQL com várias consultas SQL e comandos irissqcli. Por exemplo, este comando produzirá 3 arquivos em formatos diferentes (de mais de 20 formatos disponíveis)
$ cat <<EOF | irissqlcli iris://_SYSTEM:SYS@localhost:1972/USER
.mode csv;
tee -o test.csv;
select top 10 TABLE_SCHEMA,TABLE_NAME
from information_schema.tables
orderby TABLE_SCHEMA,TABLE_NAME;
notee;
.mode latex;
tee -o test.tex;
select top 10 TABLE_SCHEMA,TABLE_NAME
from information_schema.tables
orderby TABLE_SCHEMA,TABLE_NAME;
notee;
.mode html;
tee -o test.html;
select top 10 TABLE_SCHEMA,TABLE_NAME
from information_schema.tables
orderby TABLE_SCHEMA,TABLE_NAME;
notee;
EOF
Além disso, é possível executar um terminal da Web com o docker
docker run -d --name irissqlcli \
--restart always \
-p 7681:7681\
caretdev/irissqlcli-web irissqlcli iris://_SYSTEM:SYS@host.docker.internal:1972/USER.png)
E com docker-compose
version: '3'
services:
iris:
image: intersystemsdc/iris-community
ports:
- 1972
- 52773
command:
- -a
- '##class(Security.Users).UnExpireUserPasswords("*")'
cli:
image: caretdev/irissqlcli-web
ports:
- 7681:7681
environment:
- IRIS_HOSTNAME:iris
- IRIS_PORT=1972
- IRIS_NAMESPACE=USER
- IRIS_USERNAME=_SYSTEM
- IRIS_PASSWORD=SYS
Por favor, vote no projeto no concurso
Olá, comunidade,
Neste artigo, listei 5 funções SQL úteis com explicações e exemplos de consultas👇🏻
Estas são as 5 funções:
Vamos começar com a função COALESCE
A função COALESCE avalia uma lista de expressões na ordem da esquerda para a direita e retorna o valor da primeira expressão que não é NULL. Se todas as expressões forem avaliadas como NULL, é retornado NULL.
A declaração a seguir retorna o primeiro valor que não é nulo, ou seja, "intersystems"
SELECTCOALESCE(NULL, NULL, NULL,'intersystems', NULL,'sql').png)
Vamos criar a tabela abaixo como outro exemplo
CREATETABLE EXPENSES(
TDATE DATENOTNULL,
EXPENSE1 NUMBERNULL,
EXPENSE2 NUMBERNULL,
EXPENSE3 NUMBERNULL,
TTYPE CHAR(30) NULL).png)
Agora vamos inserir alguns dados falsos para testar nossa função
INSERTINTO sqluser.expenses (tdate, expense1,expense2,expense3,ttype )
SELECT {d'2023-01-01'}, 500,400,NULL,'Present'
UNION ALL
SELECT {d'2023-01-01'}, NULL,50,30,'SuperMarket'
UNION ALL
SELECT {d'2023-01-01'}, NULL,NULL,30,'Clothes'
UNION ALL
SELECT {d'2023-01-02'}, NULL,50,30 ,'Present'
UNION ALL
SELECT {d'2023-01-02'}, 300,500,NULL,'SuperMarket'
UNION ALL
SELECT {d'2023-01-02'}, NULL,400,NULL,'Clothes'
UNION ALL
SELECT {d'2023-01-03'}, NULL,NULL,350 ,'Present'
UNION ALL
SELECT {d'2023-01-03'}, 500,NULL,NULL,'SuperMarket'
UNION ALL
SELECT {d'2023-01-04'}, 200,100,NULL,'Clothes'
UNION ALL
SELECT {d'2023-01-06'}, NULL,NULL,100,'SuperMarket'
UNION ALL
SELECT {d'2023-01-06'}, NULL,100,NULL,'Clothes' .png)
Selecione os dados.png)
Agora, usando a função COALESCE, vamos recuperar o primeiro valor que não é NULL nas colunas expense1, expense2 e expense3
SELECT TDATE,
COALESCE(EXPENSE1,EXPENSE2,EXPENSE3),
TTYPE
FROM sqluser.expenses ORDERBY2 .png)
No SQL, há várias maneiras de atribuir uma classificação a uma linha, que vamos analisar com um exemplo. Considere novamente o mesmo exemplo acima, mas agora queremos saber quais são as despesas mais altas.
Queremos saber onde eu gasto mais dinheiro. Há diferentes maneiras de fazer isso. Podemos usar todas as ROW_NUMBER() , RANK() e DENSE_RANK() . Vamos ordenar a tabela anterior usando todas as três funções e ver quais são as principais diferenças entre elas usando a seguinte consulta:
Confira nossa consulta abaixo:
.png)
A principal diferença entre as três funções é a forma como lidamos com os vínculos. Vamos analisar mais a fundo as diferenças:
ROW_NUMBER()retorna um número único para cada linha começando em 1. Quando há vínculos, ele atribui arbitrariamente um número se o segundo critério não estiver definido.RANK()retorna um número único para cada linha começando em 1, exceto quando há vínculos, porque ele atribui o mesmo número. Além disso, uma lacuna segue a classificação duplicada.DENSE_RANK() não deixa lacunas depois de uma classificação duplicada.O total corrente é provavelmente uma das funções de janela mais úteis, principalmente quando você quer visualizar o crescimento. Usando uma função de janela com SUM(), podemos calcular uma agregação cumulativa.
Para fazer isso, só precisamos somar uma variável usando o agregador SUM() , mas ordenar essa função usando uma coluna TDATE.
É possível observar esta consulta correspondente:
.png)
Como você pode observar na tabela acima, agora temos a agregação acumulada da quantidade de dinheiro gasto conforme passam as datas.
O SQL é ótimo. As funções usadas acima podem ser úteis ao lidar com análise de dados, ciência de dados e qualquer outro campo relacionado a dados.
Por isso, você deve continuar a melhorar suas habilidades de SQL.
Obrigado