Cómo leer los datos de AWS S3 sobre el COVID como tablas SQL en IRIS

¡Hola Desarroladores!

IRIS External Table es un proyecto de código abierto de la comunidad de InterSystems, que permite utilizar archivos almacenados en el sistema de archivos local y almacenar objetos en la nube como AWS S3 y tablas SQL.

Se puede encontrar en GitHub https://github.com/intersystems-community/IRIS-ExternalTable Open Exchange https://openexchange.intersystems.com/package/IRIS-External-Table y está incluido en el administrador de paquetes InterSystems Package Manager (ZPM).

Para instalar External Table desde GitHub, utilice:

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 instalarlo con el ZPM Package Manager, utilice:

USER>zpm "install external-table"

Cómo trabajar con archivos locales

Crearemos un archivo simple que tiene este aspecto:

a1,b1
a2,b2

Abra su editor favorito y cree el archivo o utilice solo una línea de comandos en Linux/Mac:

echo $'a1,b1\na2,b2' > /tmp/test.txt

Cree una tabla SQL en IRIS para representar este archivo:

create table test (col1 char(10),col2 char(10))

Convierta la tabla para utilizar el almacenamiento externo:

CALL EXT.ConvertToExternal(
    'test',
    '{
        "adapter":"EXT.LocalFile",
        "location":"/tmp/test.txt",
        "delimiter": ","
    }')

Y finalmente, consulte la tabla:

select * from test

Si todo funciona según lo previsto, debería ver el resultado de la siguiente forma:

col1    col2
a1  b1
a2  b2

Ahora regrese al editor, modifique el contenido del archivo y ejecute nuevamente la consulta SQL. ¡¡¡Tarán!!! Está leyendo nuevos valores de su archivo local en SQL.

col1    col2
a1  b1
a2  b99

Cómo leer los datos desde S3

En https://covid19-lake.s3.amazonaws.com/index.html puede acceder a los datos de la COVID que se actualizan constantemente, estos se almacenan por AWS en el lago de datos públicos.

Intentaremos acceder a una de las fuentes de datos en este lago de datos: s3://covid19-lake/rearc-covid-19-nyt-data-in-usa/csv/us-states

Si tiene instalada la herramienta de línea de comandos para AWS, puede repetir los siguientes pasos. Si no es así, vaya directamente a la parte de SQL. No es necesario que tenga ningún componente específico de AWS instalado en su equipo para continuar con la parte de 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

Por lo tanto, tenemos un archivo con una estructura bastante simple y cinco campos delimitados.

Para mostrar esta carpeta S3 como en External Table, primero necesitamos crear una tabla “regular” con la estructura deseada:

-- create external table
create table covid_by_state (
    "date" DATE, 
    "state" VARCHAR(20),
    fips INT,
    cases INT,
    deaths INT
)

Tenga en cuenta que algunos campos de datos como “Date” son palabras reservadas en el SQL de IRIS y deben escribirse entre comillas dobles. Entonces, necesitamos convertir esta tabla “regular” en la tabla “externa”, basada en el bucket AWS S3 y con el 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
    }' 
)

Si observa detenidamente, en EXT.ExternalTable los argumentos de los procedimientos son el nombre de la tabla y luego la cadena JSON, además contiene varios parámetros como la ubicación para buscar archivos, el adaptador para utilizarlos, un delimitador, etc. Además, External Table de AWS S3 es compatible con el almacenamiento de Azure BLOB, Google Cloud Buckets y el sistema de archivos local. El repositorio de GitHub contiene referencias para la sintaxis y opciones que son compatibles con todos los formatos.

Y finalmente, consulte la tabla:

-- 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

Es comprensible que se necesite más tiempo para consultar los datos de la tabla remota, que para consultar la tabla “nativa de IRIS” o la tabla basada en el global, pero esta se almacena y actualiza completamente en la nube, y en segundo plano se extrae a IRIS.

Exploremos un par de funciones adicionales de External Table.

%PATH y las tablas basadas en varios archivos

La carpeta de nuestro ejemplo, que se encuentra en el bucket, contiene solo un archivo. Lo más común es que tenga varios archivos de la misma estructura, donde el nombre del archivo identifique tanto al registro de la hora como al identificador de algún otro atributo que queramos utilizar en nuestras consultas.

El campo %PATH se agrega automáticamente a cada tabla externa y contiene la ruta completa hacia el archivo de donde se recuperó la fila.

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

Puede utilizar el campo %PATH en sus consultas SQL como cualquier otro campo.

De datos ETL a “tablas regulares”

Si su tarea es cargar datos de S3 en una tabla IRIS, puede utilizar External Table como una herramienta ETL. Simplemente haga lo siguiente:

INSERT INTO internal_table SELECT * FROM external_table

En nuestro caso, si queremos copiar los datos COVID de S3 a la tabla 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

UNIÓN entre IRIS, tablas nativas y externas. Consultas federadas

External Table es una tabla SQL. Se puede unir con otras tablas, utilizarse en subconsultas y sistemas de archivos tipo UNION. Incluso puede combinar la tabla “Regular” de IRIS y dos o más tablas externas que provengan de diferentes fuentes en la misma consulta SQL.

Intente crear una tabla regular, por ejemplo, haga coincidir los nombres de los estados con sus códigos como en el caso de Washington y WA. Y únalos con nuestra tabla basada en 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

Cambie “join” por “left join” para incluir aquellas filas donde el código del estado no esté definido. Como puede ver, el resultado es una combinación de datos provenientes de S3 y su tabla nativa de IRIS.

Acceso seguro a la información

El lago de datos Covid en AWS es público. Cualquier persona puede leer los datos que provengan de esta fuente sin la necesidad de tener alguna autenticación o autorización. En la vida real seguramente quiere acceder a sus datos de una forma segura, donde se evite que extraños echen un vistazo a sus archivos. Los detalles completos sobre AWS Identity y Access Management (IAM) están fuera del alcance de este artículo. Pero lo mínimo que debe saber es que necesita por lo menos la clave de acceso a la cuenta y la información confidencial de AWS para acceder a los datos privados de su cuenta.

AWS utiliza la autenticación de claves/información confidencial de la cuenta para firmar las solicitudes. https://docs.aws.amazon.com/general/latest/gr/aws-sec-cred-types.html#ac...

Si está ejecutando IRIS External Table en una instancia de EC2, la forma recomendada de lidiar con la autenticación es utilizando las funciones que se encuentran en la instancia de EC2 https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/iam-roles-for-amazon.... De este modo, IRIS External Table podría utilizar los permisos de esa función. No se requiere ninguna configuración adicional.

En una instancia local o que no sea de EC2 es necesario especificar AWS_ACCESS_KEY_ID y AWS_SECRET_ACCESS_KEY, ya sea con la ayuda de variables de entorno o mediante la instalación y configuración del cliente CLI de AWS.

export AWS_ACCESS_KEY_ID=AKIAEXAMPLEKEY
export AWS_SECRET_ACCESS_KEY=111222333abcdefghigklmnopqrst

Asegúrese de que la variable de entorno sea visible dentro del proceso de IRIS. Puede verificarlo al ejecutar:

USER>write $system.Util.GetEnviron("AWS_ACCESS_KEY_ID")

Esto debería emitir el valor de la clave.

O instale el CLI de AWS, mediante instrucciones que se encuentran aquí: https://docs.aws.amazon.com/cli/latest/userguide/install-cliv2-linux.html y ejecutar:

aws configure

Entonces External Table podrá leer las credenciales desde los archivos de configuración para el CLI de AWS. Posiblemente su shell interactivo y el proceso de IRIS estén ejecutándose en cuentas diferentes, asegúrese de ejecutar aws configure con la misma cuenta que su proceso de IRIS.