SQL é uma linguagem padrão para armazenar, manipular e recuperar dados em bancos de dados relacionais.
Nos bons velhos tempos (tm), determinar o tamanho dos dados, fluxos e índices para uma classe / tabela era fácil - você apenas executava %GSIZE e verificava as globais D, S e I respectivamente.
No entanto, hoje em dia a fragmentação, os nomes globais otimizados e os índices em globais separados produzem a saída% GSIZE parecida com esta:
As consultas utilizando intervalo de datas estão muito lentas para você? O desempenho do SQL te desanima? Eu tenho um estranho truque que pode te ajudar! (Desenvolvedores de SQL odeiam isso!)*
Se você tiver uma classe que registra os timestamps quando os dados são adicionados, esses dados estarão em sequência com seus valores IDKEY - isto é, TimeStamp1 < TimeStamp2 se e somente se ID1 < ID2 para todos os IDs e valores de timestamps na tabela - então, você pode usar esse conhecimento para aumentar o desempenho de consultas para intervalos de timestamps. Considere a seguinte tabela:
Class User.TSOrder extends %Persistent
{
Property TS as %TimeStamp;
Property Data as %String (MAXLEN=100, MINLEN=200);
Index TSIdx on TS;
Index Extent [type=bitmap, extent];
}Popular isso com 30.000.000 de linhas aleatórias com datas dos últimos 30 dias, resultará em 1.000.000 de linhas por dia. Agora, se quisermos consultar as informações de um determinado dia, você pode escrever o seguinte
SELECT ID, TS, Data
FROM TSOrder
WHERE
TS >= '2016-07-01 00:00:00.00000' AND
TS <= '2016-07-01 23:59:59.999999'
Uma consulta razoável, com certeza. No meu sistema, no entanto, isso realizou 2.172.792 referências a globais em 7,2 segundos. Mas, sabendo que os IDs e TimeStamps estão na mesma ordem, podemos usar os TimeStamps para obter um intervalo de ID. Considere a seguinte consulta:
SELECT ID, TS, Data
FROM TSOrder
WHERE
ID >= (SELECT TOP 1 ID FROM TSOrder WHERE TS >='2016-07-01 00:00:00.00000' ORDER BY TS ASC) AND
ID <= (SELECT TOP 1 ID FROM TSOrder WHERE TS <='2016-07-01 23:59:59.999999' ORDER BY TS DESC)
A nova consulta é concluída em 5,1 segundos e realiza apenas 999.985 referências a globais**!
Essa técnica pode ser aplicada de forma mais pragmática a tabelas com mais campos indexados e consultas que possuem várias cláusulas WHERE. O intervalo de ID gerado a partir das subconsultas pode ser colocado no formato de bitmap, gerando uma velocidade incrível quando você tem uma solução de vários índices. A tabela Ens.MessageHeader é um ótimo exemplo onde você pode colocar para funcionar esse truque.
Vamos ser claros - este é um EXEMPLO de uma vitória. Se você tiver muitas condições na cláusula WHERE na mesma tabela (e elas estão indexadas), Então esta técnica pode lhe dar GRANDES vitórias! Experimente em suas consultas!
** Ao testar as consultas que retornam muitas linhas, o SMP não consegue lidar com isso, e a maior parte do tempo é gasto na exibição dos dados. A maneira adequada de testar é com o SQL integrado ou dinâmico, examinando os resultados, mas gerando a saída deles por um tempo, e usando o SQL Shell para suas contagens de globais. Você também pode usar o SQL Stats para isso.
IRIS External Table é um projeto de código aberto da comunidade InterSystems, que permite usar arquivos armazenados no sistema de arquivos local e armazenamento de objetos em nuvem, como o AWS S3, como tabelas SQL. 
Ele pode ser encontrado no GitHub https://github.com/intersystems-community/IRIS-ExternalTable, Open Exchange https://openexchange.intersystems.com/package/IRIS-External-Table e está incluído no InterSystems Package Manager, ZPM.
Para instalar o External Table a partir do GitHub, use:
git clone https://github.com/antonum/IRIS-ExternalTable.git
iris session iris
USER>set sc = ##class(%SYSTEM.OBJ).LoadDir("<path-to>/IRIS-ExternalTable/src", "ck",,1)
Para instalar usando o ZPM Package Manager:
USER>zpm "install external-table"
Vamos criar um arquivo simples parecido com este:
a1,b1
a2,b2
Abra seu editor favorito e crie o arquivo ou apenas use uma linha de comando no linux/mac:
echo $'a1,b1\na2,b2' > /tmp/test.txt
No IRIS SQL, crie uma tabela para representar este arquivo:
create table test (col1 char(10),col2 char(10))
Converta a tabela para usar o armazenamento externo:
CALL EXT.ConvertToExternal(
'test',
'{
"adapter":"EXT.LocalFile",
"location":"/tmp/test.txt",
"delimiter": ","
}')
E finalmente, consulte a tabela:
select * from test
Se tudo funcionar conforme o esperado, você verá uma saída como esta:
col1 col2
a1 b1
a2 b2
Agora volte ao editor, altere o conteúdo do arquivo e execute novamente a consulta SQL. Uau!!! Você está lendo os novos valores de seu arquivo local no SQL.
col1 col2
a1 b1
a2 b99
Em https://covid19-lake.s3.amazonaws.com/index.htmlhttps:você pode obter acesso a dados atualizados constantemente sobre o COVID, armazenados pela AWS no data lake público.
Vamos tentar acessar uma das fontes de dados neste data lake: s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states
Se você tiver a ferramenta de linha de comando AWS instalada, pode repetir as etapas abaixo. Caso contrário, vá direto para a parte SQL. Você não precisa de usar um AWS específico instalado em sua máquina para acompanhar a parte SQL.
$ aws s3 ls s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/
2020-12-04 17:19:10 510572 us-states.csv
$ aws s3 cp s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv .
download: s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv to ./us-states.csv
$ head us-states.csv
date,state,fips,cases,deaths
2020-01-21,Washington,53,1,0
2020-01-22,Washington,53,1,0
2020-01-23,Washington,53,1,0
2020-01-24,Illinois,17,1,0
2020-01-24,Washington,53,1,0
2020-01-25,California,06,1,0
2020-01-25,Illinois,17,1,0
2020-01-25,Washington,53,1,0
2020-01-26,Arizona,04,1,0
Portanto, temos um arquivo com uma estrutura bastante simples. Com cinco campos delimitados.
Para expor esta pasta S3 como um External Table, primeiro, precisamos criar uma tabela "regular" com a estrutura desejada:
-- create external table
create table covid_by_state (
"date" DATE,
"state" VARCHAR(20),
fips INT,
cases INT,
deaths INT
)
Observe que alguns nomes de campo como “Date” são palavras reservadas no IRIS SQL e precisam ser colocados entre aspas duplas. Em seguida, precisamos converter esta tabela “regular” para a tabela “externa”, com base no bucket AWS S3 e tipo CSV.
-- convert table to external storage
call EXT.ConvertToExternal(
'covid_by_state',
'{
"adapter":"EXT.AWSS3",
"location":"s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/",
"type": "csv",
"delimiter": ",",
"skipHeaders": 1
}'
)
Se você observar com atenção, os argumentos dos procedimentos EXT.ExternalTable são o nome da tabela e a string JSON, contendo vários parâmetros, como localização para procurar por arquivos, adaptador, delimitador, etc. Além da AWS S3, o External Table oferece suporte ao armazenamento BLOB do Azure, Cloud Buckets e o sistema de arquivos local. O GitHub Repo contém referências para a sintaxe e as opções suportadas em todos os formatos.
E finalmente, consulte a tabela:
-- query the table
select top 10 * from covid_by_state order by "date" desc
[SQL]USER>>select top 10 * from covid_by_state order by "date" desc
2. select top 10 * from covid_by_state order by "date" desc
date state fips cases deaths
2020-12-06 Alabama 01 269877 3889
2020-12-06 Alaska 02 36847 136
2020-12-06 Arizona 04 364276 6950
2020-12-06 Arkansas 05 170924 2660
2020-12-06 California 06 1371940 19937
2020-12-06 Colorado 08 262460 3437
2020-12-06 Connecticut 09 127715 5146
2020-12-06 Delaware 10 39912 793
2020-12-06 District of Columbia 11 23136 697
2020-12-06 Florida 12 1058066 19176
Compreensivelmente, leva mais tempo para consultar dados da tabela remota, do que na tabela "IRIS nativa" ou com base global, porém, ela é completamente armazenada e atualizada em nuvem e está sendo puxada para o IRIS "nos bastidores".
Vamos explorar mais alguns recursos do External Table.
Em nossa pasta de exemplo, o bucket contém apenas um arquivo. Mais frequentemente, ele teria vários arquivos com a mesma estrutura, onde nome do arquivo identifica o carimbo de data/hora ou deviceid de algum outro atributo que desejaremos usar em nossas consultas.
O campo %PATH é adicionado automaticamente a cada External Table e contém o caminho completo para o arquivo de onde a linha foi recuperada.
select top 5 %PATH,* from covid_by_state
%PATH date state fips cases deaths
s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv 2020-01-21 Washington 53 1 0
s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv 2020-01-22 Washington 53 1 0
s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv 2020-01-23 Washington 53 1 0
s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv 2020-01-24 Illinois 17 1 0
s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states/us-states.csv 2020-01-24 Washington 53 1 0
Você pode usar este campo %PATH em suas consultas SQL como em qualquer outro campo.
Se sua tarefa é carregar dados do S3 em uma tabela IRIS, você pode usar o External Table como uma ferramenta ETL. Apenas faça:
INSERT INTO internal_table SELECT * FROM external_table
No nosso caso, se quisermos copiar os dados sobre COVID do S3 para a tabela local:
--create local table
create table covid_by_state_local (
"date" DATE,
"state" VARCHAR(100),
fips INT,
cases INT,
deaths INT
)
--ETL from External to Local table
INSERT INTO covid_by_state_local SELECT TO_DATE("date",'YYYY-MM-DD'),state,fips,cases,deaths FROM covid_by_state
External Table é uma tabela SQL. Ele pode ser unido a outras tabelas, usado em subseleções e UNIONs. Você pode até combinar a tabela IRIS “Regular” e duas ou mais tabelas externas de fontes diferentes na mesma consulta SQL.
Tente criar uma tabela regular, como os nomes de estado correspondendo com códigos de estado, como por exemplo, Washington – WA. E junte-a com nossa tabela baseada em S3.
create table state_codes (name varchar(100), code char(2))
insert into state_codes values ('Washington','WA')
insert into state_codes values ('Illinois','IL')
select top 10 "date", state, code, cases from covid_by_state join state_codes on state=name
Altere 'join' para 'left join' para incluir linhas para as quais o código de estado não foi definido. Como você pode ver, o resultado é uma combinação de dados do S3 e sua tabela IRIS nativa.
A AWS Covid Data Lake é público. Qualquer pessoa pode ler dados dele sem qualquer autenticação ou autorização. Na vida real, você desejará acessar seus dados de forma segura, evitando que estranhos espiem seus arquivos. Os detalhes completos da AWS Identity and Access Management (IAM) estão fora do escopo deste artigo. Mas o mínimo que você precisa saber é que você precisa de pelo menos a chave de acesso e a chave secreta da conta da AWS para acessar dados privados em sua conta. https:
AWS usa autenticação de chave/segredo de conta para assinar solicitações. https://docs.aws.amazon.com/general/latest/gr/aws-sec-cred-types.html#access-keys-and-secret-access-keys
Se você estiver executando o IRIS External Table na instância EC2, a maneira recomendada de lidar com a autenticação é usar as funções da instância EC2 https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/iam-roles-for-amazon-ec2.html. O IRIS External Table será capaz de usar as permissões dessa função. Nenhuma configuração extra é necessária.
Em uma instância local/não EC2, você precisa especificar o AWS_ACCESS_KEY_ID e AWS_SECRET_ACCESS_KEY, especificando variáveis de ambiente ou instalando e configurando o cliente AWS CLI.
export AWS_ACCESS_KEY_ID=AKIAEXAMPLEKEY
export AWS_SECRET_ACCESS_KEY=111222333abcdefghigklmnopqrst
Certifique-se de que a variável de ambiente esteja visível em seu processo IRIS. Você pode verificá-lo executando:
USER>write $system.Util.GetEnviron("AWS_ACCESS_KEY_ID")
Ela deve retornar o valor da chave.
ou instale o AWS CLI, seguindo as instruções aqui https://docs.aws.amazon.com/cli/latest/userguide/install-cliv2-linux.html e execute:
aws configure
O External Table poderá, então, ler as credenciais dos arquivos de configuração do aws cli. Seu shell interativo e o processo IRIS podem estar sendo executados em contas diferentes. Certifique-se de executar aws configure na mesma conta do seu processo IRIS.
Este é um exemplo de codificação funcionando no IRIS 2020.1 e no Caché 2018.1.3
Ele não será sincronizado com as novas versões
E também NÃO é atendido pelo Suporte da InterSystems!
Globais no Caché / Ensemble / IRIS são normalmente invisíveis ao acessar o SQL
Este exemplo mostra como contornar esse limite.
Globais são apresentadas como conteúdo de uma tabela, com seus subscritos, e o conteúdo armazenado.
A global a ser visualizada é passada ao SQL por uma condição estática WHERE que
requer 3 parâmetros:
Apenas fornecer o nome da global resulta em um despejo (dump) da global completo.
As globais também podem ser fornecidas com a referência estendida e, como esta é uma tabela SQL, todos os tipos de condições adicionais se aplicam.
Tome cuidado. Colocar as aspas corretamente entre SQL e Caché / Ensemble / IRIS pode ser um desafio
Exemplo:
select * from zrcc_G.dump where zrcc_G.Dump('^|"CACHE"|Sample.PersonD',2,4)=1
ID Global Subscrito Valor
1 ^|"CACHE"|Sample.PersonD (2) $lb("",792533244,"GlobaDynamics Holdings Inc.",64256,"C1787","Y5365","A5","A658","R1770","","Ironhorse,Alice D.","T3710","O3","I4011","W8367","557-37-6758",83059958205089661,"1841-01-02 00:00:00")
2 ^|"CACHE"|Sample.PersonD (3) $lb("",862705606,"TeleLateral Associates",34553,"V8155","T8918","X9","V8732","K1167","","Eisenstien,Peter E.","H208","C8","Q2015","Q3357","702-46-8467",57275722714358892,"2020-06-23 13:27:18")
3 ^|"CACHE"|Sample.PersonD (4) $lb("",677194559,"RoboSoft Group Ltd.",52738,"F4851","Z364","S8","O6888","O4367","","Eagleman,Clint C.","C8051","R6","V1659","C9814","664-33-8809",-53705244349891319,"2020-06-23 13:27:18")
select TOP 15 * from zrcc_G.dump where zrcc_G.Dump('^%SYS','"JOURNAL"')=1
ID Global Subscrito Valor
1 ^%SYS ("JOURNAL") 0
2 ^%SYS ("JOURNAL","ALTDIR") "C:\InterSystems\IRIS\altjournal\"
3 ^%SYS ("JOURNAL","CURDIR") "C:\InterSystems\IRIS\mgr\journal\"
4 ^%SYS ("JOURNAL","CURRENT") "1^C:\InterSystems\IRIS\mgr\journal\20200801.009"
5 ^%SYS ("JOURNAL","EXPSIZE") 0
6 ^%SYS ("JOURNAL","LAST") "1^C:\InterSystems\IRIS\mgr\journal\20200801.009"
7 ^%SYS ("JOURNAL","LIFESPAN","FILE") "2,2"
8 ^%SYS ("JOURNAL","MAXSIZE") 1073741824
9 ^%SYS ("JOURNAL","PREFIX") ""
10 ^%SYS ("JOURNAL","PURGED","c:\intersystems\iris\mgr\journal\20191104.001") "2019-11-07 17:38:30"
11 ^%SYS ("JOURNAL","PURGED","c:\intersystems\iris\mgr\journal\20191104.002") "2019-11-07 17:38:30"
12 ^%SYS ("JOURNAL","PURGED","c:\intersystems\iris\mgr\journal\20191104.003") "2019-11-07 17:38:30"
13 ^%SYS ("JOURNAL","PURGED","c:\intersystems\iris\mgr\journal\20191104.004") "2019-11-07 17:38:30"
14 ^%SYS ("JOURNAL","PURGED","c:\intersystems\iris\mgr\journal\20191104.005") "2019-11-08 08:39:47"
15 ^%SYS ("JOURNAL","PURGED","c:\intersystems\iris\mgr\journal\20191105.001") "2019-11-08 08:39:47"
Desde o Caché 2017, o mecanismo SQL inclui um novo conjunto de estatísticas. Ele registra o número de vezes que uma consulta é executada e o tempo que leva para executá-la.
Esta é uma mina de ouro para qualquer pessoa que está monitorando e tentando otimizar o desempenho de uma aplicação que inclui muitas instruções SQL, mas que não é tão fácil de acessar os dados como algumas pessoas desejam.
É um prazer anunciar que o DBeaver suporta a plataforma de dados InterSystems IRIS nativamente desde a versão 7.2.4. Você não precisa realizar uma configuração manual mais, basta identificar o ícone do IRIS na lista de conexões.
Todos os campos necessários já vem preenchidos mas, não se esqueça de colocar seu usuário e senha !!!!